FCB 人群包 100000404 · 效果回收报告

SELECT g.guid,
  CASE
    WHEN g.g1_hit_cnt_l30d >= 6 AND g.g1_hit_distinct_domain_cnt_l30d >= 3 THEN 'L4'
    WHEN g.g1_hit_cnt_l30d >= 2 AND g.g1_hit_distinct_domain_cnt_l30d >= 2 THEN 'L3'
    WHEN g.g1_hit_cnt_l30d >= 2 AND g.g1_hit_distinct_domain_cnt_l30d  = 1 THEN 'L2'
    WHEN g.g1_hit_cnt_l30d  = 1 AND g.g1_hit_distinct_domain_cnt_l30d  = 1 THEN 'L1'
    ELSE 'Other' END AS g1_level
FROM rpt_user_behavior_g1_hit_summary_d g
INNER JOIN rpt_cp_usr_user_trace_info t ON g.guid = t.guid
WHERE g.pt = '2026-06-13'
  AND t.first_cb_click_time IS NOT NULL;

SELECT
  COUNT(DISTINCT e.guid) AS fcb_impr_uv,
  COUNT(DISTINCT CASE WHEN c.guid IS NOT NULL THEN e.guid END) AS click_uv,
  COUNT(DISTINCT CASE WHEN c.guid IS NOT NULL AND ob.guid IS NOT NULL THEN e.guid END) AS activate_uv,
  COUNT(DISTINCT CASE WHEN c.guid IS NOT NULL AND ob.guid IS NOT NULL AND sp.guid IS NOT NULL THEN e.guid END) AS sp_uv
FROM (
  SELECT i.guid FROM (
    SELECT DISTINCT guid FROM v_dwd_log_parse_extension_service_impress_di
    WHERE pt BETWEEN '2026-05-27' AND '2026-06-14' AND type='CB' AND scene='FirstPage'
      AND get_json_object(extra,'$.fcb_enabled')='true'
  ) i
  INNER JOIN (
    SELECT g.guid FROM rpt_user_behavior_g1_hit_summary_d g
    INNER JOIN rpt_cp_usr_user_trace_info t ON g.guid=t.guid
    WHERE g.pt='2026-06-13' AND t.first_cb_click_time IS NOT NULL
  ) p ON p.guid=i.guid
) e
LEFT JOIN (
  SELECT DISTINCT guid FROM v_dwd_log_parse_extension_service_click_di
  WHERE pt BETWEEN '2026-05-27' AND '2026-06-14' AND type='CB' AND scene='FirstPage'
) c ON c.guid=e.guid
LEFT JOIN (
  SELECT DISTINCT client_id AS guid FROM dwd_log_event_outbound_result_det_d
  WHERE business='2' AND outbound_state='1'
    AND NOT (outbound_state_reason_type='0' AND outbound_state_reason='1')
    AND access_at >= '2026-05-27 00:00:00' AND access_at < '2026-06-15 00:00:00'
) ob ON ob.guid=e.guid
LEFT JOIN (
  SELECT DISTINCT guid FROM dws_log_sp_user_shopping_journey_detail_di
  WHERE pt BETWEEN '2026-05-27' AND '2026-06-14'
) sp ON sp.guid=e.guid;

-- 足迹：FCB 点击用户的所有 CB 订单（任意商家）
SELECT COUNT(DISTINCT o.guid) order_users, COUNT(1) orders,
       ROUND(SUM(o.sales),2) gmv, ROUND(SUM(o.commission),2) commission
FROM (
  SELECT guid, root_domain, sales, commission FROM dwd_trd_user_orders_di
  WHERE bu='CP' AND is_cb_order=1
    AND created_time >= '2026-05-27 00:00:00' AND created_time < '2026-06-15 00:00:00'
) o
INNER JOIN (
  SELECT DISTINCT cl.guid, cl.sub_domain FROM (
    SELECT DISTINCT guid, sub_domain FROM v_dwd_log_parse_extension_service_click_di
    WHERE pt BETWEEN '2026-05-27' AND '2026-06-14' AND type='CB' AND scene='FirstPage'
  ) cl
  INNER JOIN (
    SELECT g.guid FROM rpt_user_behavior_g1_hit_summary_d g
    INNER JOIN rpt_cp_usr_user_trace_info t ON g.guid=t.guid
    WHERE g.pt='2026-06-13' AND t.first_cb_click_time IS NOT NULL
  ) p ON p.guid=cl.guid
) fc ON fc.guid=o.guid
-- 严格同商家：上句末尾加  AND o.root_domain = fc.sub_domain

-- 4a. 人群包整体卸载（按分块跑，避免 19 天扫描超时）
SELECT COUNT(DISTINCT p.guid) AS uninstall_uv
FROM (
  SELECT DISTINCT client_id AS guid FROM v_dwd_log_parse_event_analytics_log_di
  WHERE pt BETWEEN '2026-05-27' AND '2026-06-01'
    AND scene='uninstall_feedback_after' AND event_type='impress'
) u
INNER JOIN (
  SELECT g.guid FROM rpt_user_behavior_g1_hit_summary_d g
  INNER JOIN rpt_cp_usr_user_trace_info t ON g.guid=t.guid
  WHERE g.pt='2026-06-13' AND t.first_cb_click_time IS NOT NULL
) p ON p.guid=u.guid;

-- 4b. FCB 直接卸载：卸载时间 − 最近一次 FCB 曝光时间 ≤ 阈值
SELECT COUNT(*) exposed_uninstallers,
  SUM(CASE WHEN min_gap_min <= 5  THEN 1 ELSE 0 END) le_5min,
  SUM(CASE WHEN min_gap_min <= 30 THEN 1 ELSE 0 END) le_30min
FROM (
  SELECT u.guid, MIN((unix_timestamp(u.ut)-unix_timestamp(f.ft))/60) AS min_gap_min
  FROM (
    SELECT client_id AS guid, MIN(access_at) AS ut FROM v_dwd_log_parse_event_analytics_log_di
    WHERE pt BETWEEN '2026-05-27' AND '2026-06-01'
      AND scene='uninstall_feedback_after' AND event_type='impress' GROUP BY client_id
  ) u
  INNER JOIN (
    SELECT g.guid FROM rpt_user_behavior_g1_hit_summary_d g
    INNER JOIN rpt_cp_usr_user_trace_info t ON g.guid=t.guid
    WHERE g.pt='2026-06-13' AND t.first_cb_click_time IS NOT NULL
  ) p ON p.guid=u.guid
  INNER JOIN (
    SELECT guid, create_time AS ft FROM v_dwd_log_parse_extension_service_impress_di
    WHERE pt BETWEEN '2026-05-27' AND '2026-06-01' AND type='CB' AND scene='FirstPage'
      AND get_json_object(extra,'$.fcb_enabled')='true'
  ) f ON f.guid=u.guid AND f.ft <= u.ut
  GROUP BY u.guid
) z;

-- 弹窗序列（impress 全类型）
SELECT create_time, type, scene, sub_domain, url
FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt BETWEEN '<day>' AND '<day+1>' AND guid='<GUID>' ORDER BY create_time;
-- 动作流（含 view_cashback / snooze / report_an_issue / stand_down / uninstall_*）
SELECT access_at, event, scene, sub_domain
FROM v_dwd_log_parse_event_analytics_log_di
WHERE pt BETWEEN '<day>' AND '<day+1>' AND client_id='<GUID>' ORDER BY access_at;

-- 6a. 大盘 AFF-SP 日总量
SELECT date, SUM(service_impr_user_cnt) impr, SUM(service_click_user_cnt) click, SUM(order_user_cnt) ord
FROM rpt_sp_user_monetization_daily_performance_summary_d
WHERE date BETWEEN '2026-05-27' AND '2026-06-14'
  AND store_type='AFF_SP_CPS_STORE' AND platform='ALL' AND country='ALL'
GROUP BY date ORDER BY date;

-- 6b. 点击真实拉升 = FCB 点击用户 ∩ SP 池，且当天无其它 service 点击
SELECT COUNT(DISTINCT b.guid) fcb_clicker_in_sp_pool,
  COUNT(DISTINCT CASE WHEN oc.guid IS NULL THEN b.guid END) click_lift_upper
FROM (
  SELECT s.guid FROM (
    SELECT DISTINCT cl.guid FROM (
      SELECT DISTINCT guid FROM v_dwd_log_parse_extension_service_click_di
      WHERE pt BETWEEN '2026-05-27' AND '2026-06-14' AND type='CB' AND scene='FirstPage'
    ) cl
    INNER JOIN (SELECT g.guid FROM rpt_user_behavior_g1_hit_summary_d g
      INNER JOIN rpt_cp_usr_user_trace_info t ON g.guid=t.guid
      WHERE g.pt='2026-06-13' AND t.first_cb_click_time IS NOT NULL) p ON p.guid=cl.guid
  ) fcbc
  INNER JOIN (SELECT DISTINCT guid FROM dws_log_sp_user_shopping_journey_detail_di
    WHERE pt BETWEEN '2026-05-27' AND '2026-06-14'
      AND CAST(aff_id AS UNSIGNED)>0 AND cps_score>40) s ON s.guid=fcbc.guid
) b
LEFT JOIN (
  SELECT DISTINCT guid FROM v_dwd_log_parse_extension_service_click_di
  WHERE pt BETWEEN '2026-05-27' AND '2026-06-14' AND type IN ('AT','CB','CC','DD')
    AND NOT (type='CB' AND scene='FirstPage') AND scene NOT IN ('Store_Traffic','Vio_Injected')
) oc ON oc.guid=b.guid;
-- 曝光真实拉升：把 click 表换成 impress 表 + fcb_enabled 过滤，同结构