SP-R1&R2 根因与策略分析

主动 snooze（click 真口径）

SELECT client_id, COUNT(*) AS snz_cnt
FROM v_dwd_log_parse_event_analytics_log_di
WHERE pt BETWEEN '2026-04-28' AND '2026-05-27'
  AND event='snooze' AND scene='snooze now'
  AND event_type='click'   -- ⭐关键:只计真点击,不计 impress(面板被展示),否则 49× 膨胀(8,303 vs 405,886/天)
GROUP BY client_id;

被动 stand_down

SELECT DISTINCT client_id
FROM v_dwd_log_parse_event_analytics_log_di
WHERE pt BETWEEN '2026-04-28' AND '2026-05-27' AND event='stand_down';
-- stand_down ≠ reason=9(仅 4.2% 重叠) → 统计被动让位必须用 event='stand_down',用 reason=9 会漏 95.8%

not_impression reason（优先级唯一归类，按天拉再本地合并）

SELECT client_id,
  MIN(CASE WHEN r=9 THEN 1 WHEN r IN (8,13) THEN 2 WHEN r=2 THEN 3
           WHEN r=6 THEN 4 WHEN r=11 THEN 5 WHEN r=16 THEN 6 ELSE 7 END) AS rk
FROM (
  SELECT client_id, get_json_int(properties,'$.reason') AS r
  FROM v_dwd_log_parse_event_analytics_log_di
  WHERE pt='<逐日>' AND event='not_impression'
) t
GROUP BY client_id;
-- 按天 GROUP BY 再本地取最小 rk(最高优先级),避免 30d 一次扫描超 1 亿行上限
-- 优先级: r9归因 > r8/13联盟静默 > r2业务关 > r6新用户/国家 > r11频控 > r16 SP回访 > 其他

SELECT CASE
  WHEN url LIKE '%cjevent%'                          THEN 'CJ'
  WHEN url LIKE '%irclickid%' OR url LIKE '%irgwc%'   THEN 'Impact'
  WHEN LOWER(url) LIKE '%m_pl=rakuten%' OR url LIKE '%ranMID%' THEN 'Rakuten'
  WHEN url LIKE '%awc=%' OR url LIKE '%awinaffid%'    THEN 'Awin'
  WHEN url LIKE '%clickref%'                          THEN 'Partnerize'
  WHEN url LIKE '%gclid%'                             THEN 'Google广告'
  ELSE '无明显联盟参数' END AS aff,
  COUNT(DISTINCT client_id) AS uv
FROM v_dwd_log_parse_event_analytics_log_di
WHERE pt='2026-05-27' AND event='stand_down' AND url IS NOT NULL
GROUP BY 1 ORDER BY uv DESC;
-- 不同联盟参数不同(CJ=cjevent/Impact=irclickid/Awin=awc/Rakuten=m_pl/Partnerize=clickref)
-- 无参数 46.7% = cookie 持久化型(上次访问留下竞品 cookie,本次 URL 无参) = dollar_cookie 卡死

WITH snz AS (
  SELECT client_id, sub_domain, MIN(access_at) AS snz_t
  FROM v_dwd_log_parse_event_analytics_log_di
  WHERE pt='2026-05-27' AND event='snooze' AND scene='snooze now'
    AND event_type='click' AND page_type IN ('cart','checkout','order','pdp')
  GROUP BY client_id, sub_domain
)
SELECT COUNT(DISTINCT s.client_id) AS 真打断_UV
FROM snz s
JOIN dwd_log_event_outbound_result_det_d o
  ON s.client_id=o.client_id AND s.sub_domain=o.subdomain
 AND o.access_at > s.snz_t
 AND o.access_at <= DATE_ADD(s.snz_t, INTERVAL 30 MINUTE);
-- snooze@深漏斗页 → 同商家 30min 内 Coupert outbound = 真打断(~10%); 其余=假打断(~90%)
-- outbound 表无 pt 分区,必须用 access_at 范围;client_id IN(snooze集) 预过滤避免超 1 亿行扫描