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

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';
-- → 371 单 / 330 UV / GMV $1,080.82 / Commission $54.43

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-04'   -- D+10 时跑通
  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-06-04'
  );
-- → 42 单 / 36 UV / GMV $872.15 / Commission $9.89

-- 20 天扩展版会超时，需要按 10 天分段查询后合并去重

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 精确订单归因