SQL实战：AB 测试中，从数据采集到运营策略落地的实战指南

一、AB 测试核心逻辑与 SQL 应用场景
AB 测试的核心是通过 “随机分组、单一变量、统计显著性” 验证方案效果，而 SQL 的核心作用的是：
提取实验用户的全链路行为数据（曝光、点击、转化、留存等）；
按实验分组计算核心指标，排除数据偏差；
辅助完成显著性检验的数据预处理；
落地策略时的用户分层与精准触达。
前提假设：本文以电商产品 “下单按钮颜色优化” 为例（A 组：红色按钮；B 组：绿色按钮），实验周期 7 天，核心指标为 “点击转化率”（CTR）、“下单转化率”，辅助指标为 “人均下单金额”“3 日留存率”。
二、第一步：实验设计与 SQL 用户分组校验
实验前需确保用户分组的随机性与独立性，避免样本偏差（如 A 组多为高活跃用户），这一步需用 SQL 完成校验。

实验用户圈选 SQL（基于用户 ID 随机分组）

-- 基于用户ID哈希值随机分组（确保分组稳定，不随时间变化）
SELECT
  user_id,
  -- 哈希函数+取模，将用户分为A/B两组（可扩展为多组）
  CASE WHEN MOD(ABS(HASH(user_id)), 2) = 0 THEN 'A组_红色按钮'
       ELSE 'B组_绿色按钮' END AS experiment_group,
  register_time, -- 用户注册时间（用于后续过滤新老用户）
  user_level -- 用户等级（用于分层分析）
FROM
  user_base_info
-- 过滤实验目标用户（如近30天活跃、未屏蔽按钮功能的用户）
WHERE
  last_active_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  AND is_shield = 0 -- 未屏蔽功能
  AND user_level IN ('新用户', '老用户') -- 覆盖核心用户群
LIMIT 100000; -- 控制样本量（根据统计功效计算，通常每组≥5000）

分组随机性校验 SQL（关键！避免样本偏差）
需验证两组用户的核心属性分布一致（如注册时间、用户等级、历史活跃度）：

-- 校验两组用户的新老用户占比是否一致
SELECT
  experiment_group,
  COUNT(DISTINCT user_id) AS user_count, -- 每组用户数
  -- 新用户占比（注册时间≤7天）
  ROUND(COUNT(DISTINCT CASE WHEN register_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN user_id END)/COUNT(DISTINCT user_id),3) AS new_user_rate,
  -- 高活跃用户占比（近7天活跃≥3次）
  ROUND(COUNT(DISTINCT CASE WHEN active_days_7d >=3 THEN user_id END)/COUNT(DISTINCT user_id),3) AS high_active_rate
FROM
  experiment_user_group -- 第一步生成的实验用户表
GROUP BY
  experiment_group;

校验标准：两组的新用户占比、高活跃用户占比差值≤1%，否则需重新分组（如调整哈希取模逻辑）。
三、第二步：SQL 数据采集与清洗（核心环节）
实验期间需采集用户的 “曝光 - 点击 - 下单” 全链路数据，并用 SQL 清洗异常值（如作弊数据、误操作数据），确保数据准确性。

全链路行为数据采集 SQL

-- 合并曝光、点击、下单表，提取实验相关行为
WITH experiment_exposure AS (
  -- 曝光数据：用户看到按钮的记录
  SELECT
    user_id,
    event_time AS exposure_time,
    'button_exposure' AS event_type
  FROM
    user_event_log
  WHERE
    event_name = 'button_exposure' -- 埋点事件名
    AND event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-07 23:59:59' -- 实验周期
),
experiment_click AS (
  -- 点击数据：用户点击按钮的记录
  SELECT
    user_id,
    event_time AS click_time,
    'button_click' AS event_type
  FROM
    user_event_log
  WHERE
    event_name = 'button_click'
    AND event_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-07 23:59:59'
),
experiment_order AS (
  -- 下单数据：用户点击后下单的记录
  SELECT
    user_id,
    order_time,
    pay_amount, -- 下单金额
    'order_success' AS event_type
  FROM
    order_info
  WHERE
    order_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-07 23:59:59'
    AND order_status = 'paid' -- 仅统计已支付订单
)
-- 合并三表，获取用户全链路行为
SELECT
  e.user_id,
  g.experiment_group,
  CASE WHEN ex.exposure_time IS NOT NULL THEN 1 ELSE 0 END AS is_exposed, -- 是否曝光
  CASE WHEN c.click_time IS NOT NULL THEN 1 ELSE 0 END AS is_clicked, -- 是否点击
  CASE WHEN o.order_time IS NOT NULL THEN 1 ELSE 0 END AS is_ordered, -- 是否下单
  o.pay_amount
FROM
  experiment_user_group g
LEFT JOIN experiment_exposure ex ON g.user_id = ex.user_id
LEFT JOIN experiment_click c ON g.user_id = c.user_id
LEFT JOIN experiment_order o ON g.user_id = o.user_id
ORDER BY
  g.experiment_group, e.user_id;

数据清洗 SQL（过滤异常值）
需排除 “曝光未点击却下单”“重复点击”“作弊用户” 等异常数据：

-- 基于全链路数据，清洗异常值
SELECT
  user_id,
  experiment_group,
  is_exposed,
  is_clicked,
  -- 过滤异常：仅统计曝光后点击的用户（避免无曝光下单的干扰）
  CASE WHEN is_exposed = 1 AND is_clicked = 1 AND is_ordered = 1 THEN 1 ELSE 0 END AS is_valid_ordered,
  -- 过滤重复点击：同一用户多次点击仅算1次有效点击
  CASE WHEN is_exposed = 1 AND c.click_count = 1 THEN 1 ELSE 0 END AS is_valid_clicked,
  pay_amount
FROM (
  -- 子查询：统计用户点击次数
  SELECT
    user_id,
    experiment_group,
    is_exposed,
    is_clicked,
    is_ordered,
    pay_amount,
    COUNT(DISTINCT click_time) OVER (PARTITION BY user_id) AS click_count
  FROM
    experiment_full_link_data -- 上一步合并的全链路数据
  -- 过滤作弊用户：同一IP下用户数>5的视为作弊
  WHERE
    user_id NOT IN (
      SELECT user_id FROM user_ip_info GROUP BY ip HAVING COUNT(DISTINCT user_id) >5
    )
) t;

四、第三步：SQL 核心指标计算（验证实验效果）
清洗后的数据需计算核心指标，对比两组差异，这是运营策略制定的核心依据。

核心指标汇总 SQL（CTR、下单转化率等）

-- 按实验分组计算核心指标
SELECT
  experiment_group,
  COUNT(DISTINCT user_id) AS total_user, -- 每组总用户数
  -- 曝光率（实际看到按钮的用户占比）
  ROUND(COUNT(DISTINCT CASE WHEN is_exposed = 1 THEN user_id END)/total_user,3) AS exposure_rate,
  -- 点击转化率（CTR=点击用户数/曝光用户数）
  ROUND(COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_exposed = 1 THEN user_id END),3) AS ctr,
  -- 下单转化率（下单用户数/点击用户数）
  ROUND(COUNT(DISTINCT CASE WHEN is_valid_ordered = 1 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END),3) AS order_conversion_rate,
  -- 人均下单金额（仅统计下单用户）
  ROUND(AVG(CASE WHEN is_valid_ordered = 1 THEN pay_amount ELSE NULL END),2) AS avg_pay_amount,
  -- 3日留存率（点击后3天内再次活跃的用户占比）
  ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(again_active_time, click_time) 3 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END),3) AS retention_3d
FROM
  experiment_clean_data -- 清洗后的数据表
GROUP BY
  experiment_group;

分层指标计算（精准定位策略适用人群）
除整体指标外，需按用户分层（新老用户、用户等级）计算指标，避免 “整体效果掩盖局部差异”：

-- 按用户类型分层计算CTR和下单转化率
SELECT
  experiment_group,
  user_type, -- 新用户/老用户
  ROUND(COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_exposed = 1 THEN user_id END),3) AS ctr,
  ROUND(COUNT(DISTINCT CASE WHEN is_valid_ordered = 1 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END),3) AS order_conversion_rate
FROM
  experiment_clean_data
LEFT JOIN (
  SELECT user_id, CASE WHEN register_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN '新用户' ELSE '老用户' END AS user_type
  FROM user_base_info
) t ON experiment_clean_data.user_id = t.user_id
GROUP BY
  experiment_group, user_type;

五、第四步：SQL 辅助显著性检验（验证结果可靠性）
指标差异需通过显著性检验（如 Z 检验、T 检验）验证是否 “统计显著”（通常 p 值 5），SQL 需完成检验前的统计量计算：

-- 计算显著性检验所需统计量（以CTR为例）
SELECT
  experiment_group,
  -- 样本量（曝光用户数）
  COUNT(DISTINCT CASE WHEN is_exposed = 1 THEN user_id END) AS n,
  -- 成功次数（点击用户数）
  COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END) AS success_count,
  -- 成功率（CTR）
  ROUND(COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_exposed = 1 THEN user_id END),4) AS p
FROM
  experiment_clean_data
GROUP BY
  experiment_group;

输出结果示例：
experiment_group
n
success_count
p
A 组_红色按钮
8520
1278
0.1500
B 组_绿色按钮
8480
1526
0.1800

将上述统计量代入 Python 的scipy.stats库进行 Z 检验（代码略），若 p 值 05，则说明 B 组 CTR 显著高于 A 组。
六、第五步：基于 SQL 的运营策略落地
实验验证效果后，需通过 SQL 实现策略的精准落地，避免 “一刀切” 推广。

全量推广策略（若 B 组整体效果显著）

-- 提取所有用户，将按钮颜色更新为绿色（通过用户ID关联产品配置表）
UPDATE
  product_config
SET
  button_color = 'green' -- B组方案
WHERE
  config_type = 'order_button';

-- 同步更新用户端配置（可选，用于缓存刷新）
INSERT INTO user_config_sync (user_id, config_key, config_value, sync_time)
SELECT
  user_id,
  'order_button_color',
  'green',
  CURRENT_TIMESTAMP()
FROM
  user_base_info
WHERE
  is_shield = 0;

分层推广策略（若仅新用户效果显著）

-- 仅给新用户配置绿色按钮，老用户保留红色按钮
INSERT INTO user_config_sync (user_id, config_key, config_value, sync_time)
SELECT
  user_id,
  'order_button_color',
  CASE WHEN register_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN 'green' ELSE 'red' END,
  CURRENT_TIMESTAMP()
FROM
  user_base_info
WHERE
  is_shield = 0;

-- 后续监控分层策略效果的SQL（对比新老用户的指标变化）
SELECT
  user_type,
  DATE_FORMAT(event_time, '%Y-%m-%d') AS stat_date,
  ROUND(COUNT(DISTINCT CASE WHEN is_valid_clicked = 1 THEN user_id END)/COUNT(DISTINCT CASE WHEN is_exposed = 1 THEN user_id END),3) AS ctr
FROM
  user_event_log
LEFT JOIN (
  SELECT user_id, CASE WHEN register_time >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) THEN '新用户' ELSE '老用户' END AS user_type
  FROM user_base_info
) t ON user_event_log.user_id = t.user_id
WHERE
  event_name IN ('button_exposure', 'button_click')
  AND stat_date >= '2024-01-08' -- 策略上线日期
GROUP BY
  user_type

posted @ 2025-12-11 15:29 code_paio 阅读(109) 评论(0) 收藏举报

刷新页面返回顶部

cmggcode2025

SQL实战：AB 测试中，从数据采集到运营策略落地的实战指南

公告