人群包穿透 AT 服务 · 数据回收报告

-- 曝光（点击把 impress 换成 click 表即可）
SELECT COUNT(DISTINCT guid) AS exposed_uv
FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt BETWEEN '2026-06-06' AND '2026-06-10'
  AND type='AT' AND scene='Popup'
  AND get_json_object(extra,'$.at_high_value_package_user')='true'
  AND get_json_object(extra,'$.at_high_value_package_user_style')='6.30.10-2'
  AND get_json_object(extra,'$.lever.at_popup')='Off';   -- ★ 真穿透关键过滤

SELECT COUNT(DISTINCT z.guid) AS ctrl_uv FROM (
  SELECT ns.guid, get_json_object(ns.extra,'$.reason') AS reason
  FROM v_dwd_cp_log_impress_other_log_di ns
  INNER JOIN (SELECT DISTINCT guid FROM dws_dmp_guid_group_di
              WHERE group_id=100000414) p ON p.guid=ns.guid     -- 全包；abtest=1 则加 AND abtest_group_id=1
  WHERE ns.source=1 AND ns.pt BETWEEN '2026-06-06' AND '2026-06-10'
    AND ns.action='notshow' AND ns.category='at_popup'
) z WHERE z.reason='AT Popup Lever Off';
-- 优化：先 JOIN 人群包(仅 3356 guid)再解析 JSON，避开 notshow 单日 209 万行全表 JSON 超时

SELECT COUNT(DISTINCT i.guid) AS success_uv
FROM (  -- 穿透曝光的 (guid,商家,pt)
  SELECT DISTINCT guid, domain, pt FROM v_dwd_log_parse_extension_service_impress_di
  WHERE pt BETWEEN '2026-06-02' AND '2026-06-10' AND type='AT' AND scene='Popup'
    AND get_json_object(extra,'$.at_high_value_package_user')='true'
    AND get_json_object(extra,'$.at_high_value_package_user_style')='6.30.10-2'
    AND get_json_object(extra,'$.lever.at_popup')='Off') i
INNER JOIN (  -- 过滤后的测券成功
  SELECT DISTINCT client_id, domain, pt FROM v_dwd_log_parse_testing_log_di
  WHERE pt BETWEEN '2026-06-02' AND '2026-06-10' AND source=1 AND business=1
    AND (get_json_object(extra,'$.clicked_from')!='Auto_Testing' OR get_json_object(extra,'$.clicked_from') IS NULL)
    AND get_json_object(extra,'$.clicked_from')!='ATAutoSaved_Popup'
    AND (saved_amount>0 OR (save_type>0 AND save_type<100))) t
  ON t.client_id=i.guid AND t.domain=i.domain AND t.pt=i.pt;   -- ★ 同日(pt)匹配，绑定穿透当天
-- 测券 UV：去掉 saved_amount/save_type/clicked_from 过滤即可

SELECT
  COUNT(CASE WHEN o.is_at_order=1 THEN 1 END) AS at_ord,
  ROUND(SUM(CASE WHEN o.is_at_order=1 THEN o.commission END),2) AS at_comm,
  COUNT(CASE WHEN o.is_cb_order=1 THEN 1 END) AS cb_ord,
  ROUND(SUM(CASE WHEN o.is_cb_order=1 THEN o.commission END),2) AS cb_comm,
  COUNT(DISTINCT o.guid) AS order_uv, ROUND(SUM(o.commission),2) AS total_comm
FROM dwd_trd_user_orders_di o
INNER JOIN (<队列 guid 子查询>) c ON c.guid=o.guid
WHERE o.created_time >= '2026-06-06 00:00:00' AND o.created_time < '2026-06-11 00:00:00'  -- 订单表无 pt，用 created_time
  AND (o.is_at_order=1 OR o.is_cb_order=1)
  AND o.domain IN (<商家集>);   -- 同商家=144交集 / 各自全量=自身 522 或 662

-- 实验穿透商家
SELECT DISTINCT domain FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt BETWEEN '2026-06-06' AND '2026-06-10' AND type='AT' AND scene='Popup'
  AND get_json_object(extra,'$.at_high_value_package_user')='true'
  AND get_json_object(extra,'$.at_high_value_package_user_style')='6.30.10-2'
  AND get_json_object(extra,'$.lever.at_popup')='Off';        -- → 522

-- 对照 leveroff 商家：上面②的子查询取 DISTINCT domain → 662
-- 交集（同商家）：两者 INNER JOIN ON domain → 144

-- 实验组按标识取的 AT 弹窗，按 lever.at_popup 拆 → 证明"标识≠穿透"
SELECT get_json_object(extra,'$.lever.at_popup') AS at_popup_lever,
       COUNT(DISTINCT guid) AS uv
FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt BETWEEN '2026-06-02' AND '2026-06-11' AND type='AT' AND scene='Popup'
  AND get_json_object(extra,'$.at_high_value_package_user')='true'
  AND get_json_object(extra,'$.at_high_value_package_user_style')='6.30.10-2'
GROUP BY 1;   -- On=940(正常弹窗) / Off=690(真穿透) → 不加过滤会把曝光从 690 虚高到 1224