悬浮球展示返利比例 V1.0 — AB 实验数据回收报告

一、核心结论

• 分流均衡：曝光/hover 两组基本 50/50，AB 分流正常。
• 各环节实验组 ≤ 对照组：无论全量样本还是 CB 商家样本，点击 CTR 实验组都略低于对照组，激活率基本持平；未观测到"展示返利比例"对点击/激活的正向拉动。
• hover CTR 不是功能敏感指标：hover 发生在比例露出之前，两组理论上应持平，实验组 +0.6pp 属噪声。
• 悬浮球只是 CB 激活的一条小支路：CB 弹窗大量为自动弹出（不经过球），经"点球→弹窗→激活"的人数极少。
• 样本量不足 + 仅 2 天：点击 14~51/组、激活 1~13/组，方向不可信，需等长周期。

二、数据源与口径

分组字段

分组标记在 extra.style（不是空的 ab_group_id）："style":"6.30.30-1"=对照、"style":"6.30.30-2"=实验。 is_cashback_ball=1 表示该次 hover 真实露出了返利比例。
已验证 client_id(CB日志) == guid(曝光日志)，可直接打通；对照组 is_cashback_ball 恒为 0，反向验证了分组口径无误。

指标定义

• hoverCTR = hoverUV / 曝光UV
• 点击CTR1 = 点击UV / hoverUV（看到 hover 后对悬浮球的点击）
• 点击CTR2 = 点击UV / 曝光UV
• 看到 / 未看到返利UV = hover 事件中 is_cashback_ball =1 / =0 的去重人数（各自独立计、不互斥）
• 激活UV = 点击悬浮球的人中、又点了弹窗"激活CB"按钮的人（嵌套在点击球之下）
• 激活率 = 激活UV / 点击UV

三、数据结果

3.1 全量样本（不限商家，6-13）

说明：全量样本含 ~70% 非 CB 商家（Amazon 系等），功能对这部分无作用，会稀释效果。实验组仅 ~30% hover 用户真看到比例。

3.2 CB 商家样本（支持CB的商家×国家，最新快照口径，不限页面）

CB 商家 = 配置表最新 etl_time 快照 is_cash_back='YES' 的 (sub_domain×country)，共 14,895 对。70% 非 CB 商家两组同时排除，消除稀释。

6-13

6-14（曝光表未落，曝光UV / 点击CTR2 待 ETL）

汇总（6-13~6-14 去重）

¹ 曝光UV / 点击CTR2 仅含 6-13（曝光表 6-14 未落）；hover/点击/激活为 2 天去重。

四、附录：查询与统计逻辑（SQL）

引擎：Metabase ／ 库 35 (cp_dw, MySQL)。分组用 extra LIKE 而非 JSON 函数，以对脏 JSON 更稳并规避关键字拦截。

SELECT
  CASE WHEN extra LIKE '%"style":"6.30.30-2"%' THEN 'exp_实验'
       WHEN extra LIKE '%"style":"6.30.30-1"%' THEN 'ctrl_对照' END AS grp,
  COUNT(DISTINCT client_id) AS hover_uv,
  COUNT(DISTINCT CASE WHEN extra LIKE '%"is_cashback_ball":1%' THEN client_id END) AS saw_uv
FROM v_dwd_log_cp_click_cb_log_di
WHERE pt BETWEEN '2026-06-12' AND '2026-06-14'
  AND scene='FloatWin' AND action='mouseenter'
  AND (extra LIKE '%6.30.30-1%' OR extra LIKE '%6.30.30-2%')
GROUP BY grp;   -- 对照组 saw_uv 必为 0

SELECT g.grp, ex.expo_uv AS 曝光UV, g.hover_uv AS hoverUV,
  ROUND(g.hover_uv*100.0/ex.expo_uv,2)            AS hoverCTR,
  g.click_uv AS 点击UV,
  ROUND(g.click_uv*100.0/g.hover_uv,2)            AS 点击CTR1,
  ROUND(g.click_uv*100.0/ex.expo_uv,2)            AS 点击CTR2,
  g.saw_uv AS 看到UV, g.notsaw_uv AS 未看到UV,
  g.saw_click_uv AS 看到且点击, g.notsaw_click_uv AS 未看到且点击
FROM
 (SELECT CASE WHEN h.grp_num=2 THEN 'exp_实验' ELSE 'ctrl_对照' END grp,
    COUNT(*) hover_uv,
    SUM(CASE WHEN c.client_id IS NOT NULL THEN 1 ELSE 0 END) click_uv,
    SUM(saw_flag) saw_uv, SUM(notsaw_flag) notsaw_uv,
    SUM(CASE WHEN saw_flag=1    AND c.client_id IS NOT NULL THEN 1 ELSE 0 END) saw_click_uv,
    SUM(CASE WHEN notsaw_flag=1 AND c.client_id IS NOT NULL THEN 1 ELSE 0 END) notsaw_click_uv
  FROM (SELECT client_id,
          MAX(CASE WHEN extra LIKE '%6.30.30-2%' THEN 2 ELSE 1 END) grp_num,
          MAX(CASE WHEN extra LIKE '%"is_cashback_ball":1%' THEN 1 ELSE 0 END) saw_flag,
          MAX(CASE WHEN extra LIKE '%"is_cashback_ball":0%' THEN 1 ELSE 0 END) notsaw_flag
        FROM v_dwd_log_cp_click_cb_log_di
        WHERE pt='2026-06-13' AND scene='FloatWin' AND action='mouseenter'
          AND (extra LIKE '%6.30.30-1%' OR extra LIKE '%6.30.30-2%')
        GROUP BY client_id) h
  LEFT JOIN (SELECT DISTINCT client_id FROM v_dwd_log_cp_click_cb_log_di
             WHERE pt='2026-06-13' AND scene='FloatWin' AND action='click') c
    ON h.client_id=c.client_id
  GROUP BY grp) g
JOIN
 (SELECT CASE WHEN extra LIKE '%"style":"6.30.30-2"%' THEN 'exp_实验' ELSE 'ctrl_对照' END grp,
    COUNT(DISTINCT guid) expo_uv
  FROM v_dwd_cp_log_impress_other_log_di
  WHERE pt='2026-06-13' AND label='FloatWin_Icon'
    AND (extra LIKE '%6.30.30-1%' OR extra LIKE '%6.30.30-2%')
  GROUP BY grp) ex ON g.grp=ex.grp;

-- 全表最新快照 = MAX(etl_time) = 2026-06-14 03:47
-- 必须取最新快照，否则历史"曾YES现NO"的商家(如 aboutyou.de)会被误纳
SELECT DISTINCT sub_domain, country
FROM dwd_coupert_base_store_country_extra_df
WHERE is_cash_back='YES' AND etl_time >= '2026-06-14 00:00:00';   -- 14,895 对

SELECT f.pt,
  CASE WHEN f.grp_num=2 THEN 'exp_实验' ELSE 'ctrl_对照' END AS grp,
  ex.expo_uv AS 曝光UV, f.hover_uv AS hoverUV, f.click_uv AS 点击UV,
  ROUND(f.click_uv*100.0/NULLIF(f.hover_uv,0),2) AS 点击CTR1,
  ROUND(f.click_uv*100.0/NULLIF(ex.expo_uv,0),2) AS 点击CTR2,
  f.saw_uv AS 看到UV, f.notsaw_uv AS 未看到UV,
  f.activate_uv AS 激活UV,
  ROUND(f.activate_uv*100.0/NULLIF(f.click_uv,0),2) AS 激活率
FROM
 (SELECT hv.pt, hv.grp_num, COUNT(*) hover_uv,
    SUM(CASE WHEN bc.client_id IS NOT NULL THEN 1 ELSE 0 END) click_uv,
    SUM(CASE WHEN bc.client_id IS NOT NULL AND ac.client_id IS NOT NULL THEN 1 ELSE 0 END) activate_uv,
    SUM(hv.saw_flag) saw_uv, SUM(hv.notsaw_flag) notsaw_uv
  FROM
   (SELECT mo.pt, mo.client_id,
      MAX(CASE WHEN mo.extra LIKE '%6.30.30-2%' THEN 2 ELSE 1 END) grp_num,
      MAX(CASE WHEN mo.extra LIKE '%"is_cashback_ball":1%' THEN 1 ELSE 0 END) saw_flag,
      MAX(CASE WHEN mo.extra LIKE '%"is_cashback_ball":0%' THEN 1 ELSE 0 END) notsaw_flag
    FROM v_dwd_log_cp_click_cb_log_di mo
    JOIN (SELECT DISTINCT sub_domain,country FROM dwd_coupert_base_store_country_extra_df
          WHERE is_cash_back='YES' AND etl_time >= '2026-06-14 00:00:00') cb
      ON mo.sub_domain=cb.sub_domain AND mo.country=cb.country
    WHERE mo.pt IN ('2026-06-13','2026-06-14') AND mo.scene='FloatWin' AND mo.action='mouseenter'
      AND (mo.extra LIKE '%6.30.30-1%' OR mo.extra LIKE '%6.30.30-2%')
    GROUP BY mo.pt, mo.client_id) hv
  LEFT JOIN
   (SELECT DISTINCT cl.pt, cl.client_id FROM v_dwd_log_cp_click_cb_log_di cl
    JOIN (SELECT DISTINCT sub_domain,country FROM dwd_coupert_base_store_country_extra_df
          WHERE is_cash_back='YES' AND etl_time >= '2026-06-14 00:00:00') cb
      ON cl.sub_domain=cb.sub_domain AND cl.country=cb.country
    WHERE cl.pt IN ('2026-06-13','2026-06-14') AND cl.scene='FloatWin' AND cl.action='click') bc
    ON hv.pt=bc.pt AND hv.client_id=bc.client_id
  LEFT JOIN
   (SELECT DISTINCT ac.pt, ac.client_id FROM v_dwd_log_cp_click_cb_log_di ac
    JOIN (SELECT DISTINCT sub_domain,country FROM dwd_coupert_base_store_country_extra_df
          WHERE is_cash_back='YES' AND etl_time >= '2026-06-14 00:00:00') cb
      ON ac.sub_domain=cb.sub_domain AND ac.country=cb.country
    WHERE ac.pt IN ('2026-06-13','2026-06-14') AND ac.scene='Popup'
      AND ac.action='click' AND ac.business='2') ac
    ON hv.pt=ac.pt AND hv.client_id=ac.client_id
  GROUP BY hv.pt, hv.grp_num) f
LEFT JOIN
 (SELECT i.pt, CASE WHEN i.extra LIKE '%"style":"6.30.30-2"%' THEN 2 ELSE 1 END grp_num,
    COUNT(DISTINCT i.guid) expo_uv
  FROM v_dwd_cp_log_impress_other_log_di i
  JOIN (SELECT DISTINCT sub_domain,country FROM dwd_coupert_base_store_country_extra_df
        WHERE is_cash_back='YES' AND etl_time >= '2026-06-14 00:00:00') cb
    ON i.sub_domain=cb.sub_domain AND i.country=cb.country
  WHERE i.pt IN ('2026-06-13','2026-06-14') AND i.label='FloatWin_Icon'
    AND (i.extra LIKE '%6.30.30-1%' OR i.extra LIKE '%6.30.30-2%')
  GROUP BY i.pt, grp_num) ex ON f.pt=ex.pt AND f.grp_num=ex.grp_num
ORDER BY f.pt, grp;
-- 汇总版：去掉 hv 的 pt 与 GROUP BY pt（按 client_id 跨天去重），其余相同