G2 SuperDeals 引导弹窗 V1.0 上线 19 天效果回收报告

SELECT
  pt,
  COUNT(DISTINCT IF(event_type='impression' AND element_id='sp_super_deals_popup', client_id, NULL)) AS popup_impr_uv,
  COUNT(DISTINCT IF(event_type='click'      AND element_id='explore_super_deals',  client_id, NULL)) AS explore_click_uv,
  COUNT(DISTINCT IF(event_type='click'      AND element_id='product_card',         client_id, NULL)) AS product_click_uv,
  COUNT(DISTINCT IF(event_type='close'      AND element_id='close_button',         client_id, NULL)) AS close_uv,
  COUNT(DISTINCT IF(event_type='close'      AND element_id='maybe_next_time',      client_id, NULL)) AS snooze_uv
FROM v_dwd_log_parse_event_analytics_log_di
WHERE scene = 'Superdeals_Guide'
  AND version >= '6.20.59'
  AND pt BETWEEN '2026-05-26' AND '2026-06-14'
GROUP BY pt
ORDER BY pt;

-- 前 10 天累计曝光 UV
SELECT COUNT(DISTINCT client_id) AS popup_impr_uv
FROM v_dwd_log_parse_event_analytics_log_di
WHERE scene = 'Superdeals_Guide'
  AND event_type='impression' AND element_id='sp_super_deals_popup'
  AND version >= '6.20.59' AND pt BETWEEN '2026-05-26' AND '2026-06-04';
-- → 44,854

-- 后 10 天累计曝光 UV
SELECT COUNT(DISTINCT client_id) AS popup_impr_uv
FROM v_dwd_log_parse_event_analytics_log_di
WHERE scene = 'Superdeals_Guide'
  AND event_type='impression' AND element_id='sp_super_deals_popup'
  AND version >= '6.20.59' AND pt BETWEEN '2026-06-05' AND '2026-06-14';
-- → 53,083

-- 20 天联合查询会 504/fetch failed

-- 商品卡 extra.click_id ↔ 订单 tracking_id 严格关联
-- 20 天窗口必超时，按 5 天分段后累加（订单/GMV/Commission 可加，UV 段间有重复）

SELECT '5/26-5/30' AS window_, COUNT(*) AS orders, COUNT(DISTINCT guid) AS uv,
  SUM(sales) AS gmv, SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-05-26' AND '2026-06-14'
  AND tracking_id IN (
    SELECT SUBSTRING(extra, LOCATE('"click_id": "', extra) + 13, 32) AS click_id
    FROM v_dwd_log_parse_event_analytics_log_di
    WHERE scene = 'Superdeals_Guide'
      AND event_type = 'click'
      AND element_id = 'product_card'
      AND version >= '6.20.59'
      AND pt BETWEEN '2026-05-26' AND '2026-05-30'  -- 改这里跑其他段
  );
-- → 122 单 / 107 UV / GMV $107.59 / Commission $8.85
-- 其他 4 段 SQL 改 pt 范围即可

-- 限定 Explore 点击用户 + click_type='WEB_DEALS_RECOMMEND' 反查
-- 因为 Explore interacted_id 不会成为订单 tracking_id（落地页无 click_id 字段）
-- 只能用用户级反查

SELECT '5/26-5/30 B' AS window_, COUNT(*) AS orders, COUNT(DISTINCT guid) AS uv,
  SUM(sales) AS gmv, SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-05-26' AND '2026-06-14'
  AND click_type = 'WEB_DEALS_RECOMMEND'
  AND guid IN (
    SELECT DISTINCT client_id FROM v_dwd_log_parse_event_analytics_log_di
    WHERE scene='Superdeals_Guide' AND event_type='click' AND element_id='explore_super_deals'
      AND version >= '6.20.59' AND pt BETWEEN '2026-05-26' AND '2026-05-30'  -- 改这里
  );
-- → 27 单 / 20 UV / GMV $87.36 / Commission $4.88
-- 其他 3 段 SQL 改 pt 范围即可

-- 实测 Explore SuperDeals click 的 interacted_id 在订单表 0 行匹配
SELECT * FROM dwd_trd_user_orders_di
WHERE tracking_id IN (
  '2e68b9e771122bf1765c3ea2c3685ebb',
  '01a1ef829dac1fd882da77aa92ddae1f',
  '193ad2d3bc27db8ea7923fc12f9c8458'
);
-- → 0 行（3 个具体 Explore interacted_id 全无匹配）

-- 原因：Explore 跳 coupert.com/super-deals 自家页面不出站
-- 落地页 product_card click 才出站，但 extra 无 click_id 字段
-- click_id 链路断在中间

SELECT
  COUNT(*) AS orders,
  COUNT(DISTINCT guid) AS converter_uv,
  SUM(sales) AS gmv,
  SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-05-26' AND '2026-06-14'
  AND click_type = 'AD_SP_RECOMMEND_WINDOW'
  AND domain NOT LIKE '%amazon%';
-- → 61 单 / 38 UV / GMV $1,080.82 / Commission $30.57

-- 单日处理组的卸载严格时序归因（每个曝光日单独跑后累加）
SELECT
  '2026-05-29' AS popup_day,
  COUNT(DISTINCT impr.guid) AS treatment_uv,
  COUNT(DISTINCT u.client_id) AS all_uninstall,
  COUNT(DISTINCT IF(u.access_at > impr.first_popup_at, u.client_id, NULL)) AS strict_attr_uninstall
FROM (
  SELECT client_id AS guid, MIN(access_at) AS first_popup_at
  FROM v_dwd_log_parse_event_analytics_log_di
  WHERE scene='Superdeals_Guide' AND event_type='impression' AND element_id='sp_super_deals_popup'
    AND version >= '6.20.59' AND pt='2026-05-29'
  GROUP BY client_id
) impr
LEFT JOIN v_dwd_log_parse_event_analytics_log_di u
  ON impr.guid = u.client_id
 AND u.scene = 'uninstall_feedback_after' AND u.event_type='impress'
 AND u.pt >= '2026-05-26';

-- 实测样本：5/29 严格归因 4/7 (57%) / 5/31 6/9 (67%) / 6/01 7/8 (88%) → 均值 ~70%
-- 20 天累计 46 × 70% ≈ 32 严格归因卸载 / 44,854 = 0.071%

-- DiD 对照组卸载率：546（G2 命中卸载）- 32 = 514 / 369,700 = 0.139%

SELECT pt, COUNT(DISTINCT guid) AS impr_uv
FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt BETWEEN '2026-05-19' AND '2026-06-04'
  AND type IN ('CB','AT')
GROUP BY pt
ORDER BY pt;

-- 同理换 v_dwd_log_parse_extension_service_click_di 跑点击

SELECT
  client_id,
  access_at,
  SUBSTRING(extra, LOCATE('"click_id": "', extra) + 13, 32) AS click_id
FROM v_dwd_log_parse_event_analytics_log_di
WHERE scene = 'Superdeals_Guide'
  AND event_type = 'click'
  AND element_id = 'product_card'
  AND version >= '6.20.59'
  AND pt >= '2026-05-26';

-- 商品卡 click_id ↔ dwd_log_event_outbound_result_det_d.tracking_id 精确归因
-- click_id ↔ dwd_trd_user_orders_di.tracking_id 精确订单归因

-- 含 Amazon
SELECT COUNT(*) AS orders, COUNT(DISTINCT guid) AS uv,
  SUM(sales) AS gmv, SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-06-01' AND '2026-06-25'
  AND click_type = 'AD_SP_RECOMMEND_WINDOW';
-- → 433 单 / 354 UV / GMV $1,634.64 / Commission $70.13

-- 排 Amazon
SELECT COUNT(*) AS orders, COUNT(DISTINCT guid) AS uv,
  SUM(sales) AS gmv, SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-06-01' AND '2026-06-25'
  AND click_type = 'AD_SP_RECOMMEND_WINDOW'
  AND domain NOT LIKE '%amazon%';
-- → 115 单 / 68 UV / GMV $1,634.64 / Commission $40.62

-- B 路径限定 Explore，按 5 天 click pt 分段（25 天全窗口 IN 子查询必 504）

SELECT '6/01-6/05 B' AS w, COUNT(*) AS orders, COUNT(DISTINCT guid) AS uv,
  SUM(sales) AS gmv, SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-06-01' AND '2026-06-25'
  AND click_type = 'WEB_DEALS_RECOMMEND'
  AND guid IN (
    SELECT DISTINCT client_id FROM v_dwd_log_parse_event_analytics_log_di
    WHERE scene='Superdeals_Guide' AND event_type='click' AND element_id='explore_super_deals'
      AND version >= '6.20.59' AND pt BETWEEN '2026-06-01' AND '2026-06-05'  -- 改这里跑其他段
  );
-- 其他 4 段 SQL 改 pt 范围即可

-- 排 Amazon 版加 AND domain NOT LIKE '%amazon%'

SELECT domain, COUNT(*) AS orders, SUM(commission) AS commission
FROM dwd_trd_user_orders_di
WHERE create_date BETWEEN '2026-06-01' AND '2026-06-25'
  AND click_type = 'AD_SP_RECOMMEND_WINDOW'
  AND domain NOT LIKE '%amazon%'
GROUP BY domain
ORDER BY commission DESC
LIMIT 20;

-- 弹窗前 14 天用过 SuperDeals 落地页（6/15 曝光用户为基准）
SELECT
  COUNT(DISTINCT impr.guid) AS popup_impr_uv,
  COUNT(DISTINCT lp.client_id) AS used_before_popup_uv
FROM (
  SELECT DISTINCT client_id AS guid
  FROM v_dwd_log_parse_event_analytics_log_di
  WHERE scene='Superdeals_Guide' AND event_type='impression' AND element_id='sp_super_deals_popup'
    AND version >= '6.20.59' AND pt = '2026-06-15'
) impr
LEFT JOIN v_dwd_log_parse_event_analytics_log_di lp
  ON impr.guid = lp.client_id
 AND lp.url LIKE '%coupert.com/super-deals%'
 AND lp.pt BETWEEN '2026-06-01' AND '2026-06-14';
-- → 8,336 / 620 (7.4%)

-- 弹窗后 10 天访问 SuperDeals 落地页（同基准，改 lp.pt 窗口）
-- AND lp.pt BETWEEN '2026-06-15' AND '2026-06-25'
-- AND lp.access_at > impr.first_popup_at  -- 严格时序
-- → 8,336 / 595 (7.1%)