积分安全交互保障 · 数据回收报告

SELECT
  i.arm, i.impr_users,
  COALESCE(c.click_users, 0) AS click_users,
  ROUND(100.0 * COALESCE(c.click_users, 0) / NULLIF(i.impr_users, 0), 2) AS click_rate_pct
FROM (
  SELECT
    CASE WHEN get_json_object(extra, '$.core_tips_style') = '6.19.90-2' THEN 'B_exp'
         WHEN get_json_object(extra, '$.core_tips_style') = '6.19.90-1' THEN 'A_ctrl' END AS arm,
    count(distinct guid) AS impr_users
  FROM v_dwd_log_parse_extension_service_impress_di
  WHERE pt BETWEEN '2026-05-05' AND '2026-06-14'
    AND domain IN ('tesco.com','allegro.pl','samsclub.com','kohls.com','target.com',
                   'marksandspencer.com','agoda.com','rewe.de','argos.co.uk')
    AND type = 'CB' AND scene IN ('Popup','FirstPage','Icon')
    AND get_json_object(extra, '$.core_tips_style') IN ('6.19.90-1','6.19.90-2')
  GROUP BY 1
) i
LEFT JOIN (
  -- 同结构换 v_dwd_log_parse_extension_service_click_di
  SELECT
    CASE WHEN get_json_object(extra, '$.core_tips_style') = '6.19.90-2' THEN 'B_exp'
         WHEN get_json_object(extra, '$.core_tips_style') = '6.19.90-1' THEN 'A_ctrl' END AS arm,
    count(distinct guid) AS click_users
  FROM v_dwd_log_parse_extension_service_click_di
  WHERE pt BETWEEN '2026-05-05' AND '2026-06-14'
    AND domain IN (...同上...)
    AND type = 'CB' AND scene IN ('Popup','FirstPage','Icon')
    AND get_json_object(extra, '$.core_tips_style') IN ('6.19.90-1','6.19.90-2')
  GROUP BY 1
) c ON i.arm = c.arm
ORDER BY i.arm;

SELECT i.arm, i.impr_users, COALESCE(c.click_users,0) AS click_users,
       ROUND(100.0*COALESCE(c.click_users,0)/NULLIF(i.impr_users,0),2) AS click_rate_pct
FROM (
  SELECT CASE WHEN get_json_object(extra,'$.core_tips_style')='6.19.90-2' THEN 'B_exp'
              WHEN get_json_object(extra,'$.core_tips_style')='6.19.90-1' THEN 'A_ctrl' END AS arm,
         count(distinct guid) AS impr_users
  FROM v_dwd_log_parse_extension_service_impress_di
  WHERE pt BETWEEN '2026-05-29' AND '2026-06-14'
    AND domain IN ('sephora.com','doordash.com','gap.com','diy.com','shop-apotheke.com',
                   'ocado.com','moonpig.com','just-eat.co.uk','mediaexpert.pl',
                   'vistaprint.com','westlotto.de')
    AND type='CB' AND scene IN ('Popup','FirstPage','Icon')
    AND get_json_object(extra,'$.core_tips_style') IN ('6.19.90-1','6.19.90-2')
    AND (cast(substring_index(version,'.',1) AS unsigned)*10000
       + cast(substring_index(substring_index(version,'.',2),'.',-1) AS unsigned)*100
       + cast(substring_index(version,'.',-1) AS unsigned)) >= 62060
  GROUP BY 1
) i
LEFT JOIN (
  -- 同结构换 click 表
) c ON i.arm=c.arm
ORDER BY i.arm;

SELECT
  'B_exp' AS arm, i.domain,
  count(distinct i.guid) AS impr_users,
  count(distinct case when c.guid is not null then i.guid end) AS click_users,
  count(distinct case when c.guid is not null AND o.guid is not null then i.guid end) AS order_users,
  sum(case when c.guid is not null AND o.guid is not null then o.cb_orders else 0 end) AS orders,
  ROUND(sum(case when c.guid is not null AND o.guid is not null then o.commission else 0 end),2) AS commission
FROM (
  -- 看到了文案的用户 (style=2 AND enabled=1)
  SELECT DISTINCT guid, domain FROM v_dwd_log_parse_extension_service_impress_di
  WHERE pt BETWEEN '2026-05-05' AND '2026-06-11'
    AND domain IN (... 9 家 ...)
    AND type='CB' AND scene IN ('Popup','FirstPage','Icon')
    AND get_json_object(extra,'$.core_tips_style')='6.19.90-2'
    AND get_json_object(extra,'$.core_tips_enabled')='1'
) i
LEFT JOIN (
  -- 点击了带文案的 popup
  SELECT DISTINCT guid, domain FROM v_dwd_log_parse_extension_service_click_di
  WHERE pt BETWEEN '2026-05-05' AND '2026-06-11'
    AND domain IN (... 9 家 ...)
    AND type='CB' AND scene IN ('Popup','FirstPage','Icon')
    AND get_json_object(extra,'$.core_tips_style')='6.19.90-2'
    AND get_json_object(extra,'$.core_tips_enabled')='1'
) c ON i.guid=c.guid AND i.domain=c.domain
LEFT JOIN (
  -- CB 订单 GROUP BY guid 算佣金
  SELECT guid, domain, count(1) AS cb_orders, sum(commission) AS commission
  FROM dwd_trd_user_orders_di
  WHERE create_date BETWEEN '2026-05-05' AND '2026-06-11'
    AND domain IN (... 9 家 ...)
    AND bu='CP' AND is_cb_order=1
  GROUP BY guid, domain
) o ON i.guid=o.guid AND i.domain=o.domain
GROUP BY i.domain
ORDER BY i.domain;

-- A 对照组同结构, 去掉 enabled=1 过滤, style 改为 '6.19.90-1'

-- 与 SQL 3 同结构, 增加版本过滤,domain 改 11 家 Phase 2,日期改 5/29 起
SELECT
  'B_exp' AS arm, i.domain,
  count(distinct i.guid) AS impr_users,
  count(distinct case when c.guid is not null then i.guid end) AS click_users,
  count(distinct case when c.guid is not null AND o.guid is not null then i.guid end) AS order_users,
  sum(case when c.guid is not null AND o.guid is not null then o.cb_orders else 0 end) AS orders,
  ROUND(sum(case when c.guid is not null AND o.guid is not null then o.commission else 0 end),2) AS commission
FROM (
  SELECT DISTINCT guid, domain FROM v_dwd_log_parse_extension_service_impress_di
  WHERE pt BETWEEN '2026-05-29' AND '2026-06-11'
    AND domain IN ('sephora.com','doordash.com','gap.com','diy.com','shop-apotheke.com',
                   'ocado.com','moonpig.com','just-eat.co.uk','mediaexpert.pl',
                   'vistaprint.com','westlotto.de')
    AND type='CB' AND scene IN ('Popup','FirstPage','Icon')
    AND get_json_object(extra,'$.core_tips_style')='6.19.90-2'
    AND get_json_object(extra,'$.core_tips_enabled')='1'
    AND (cast(substring_index(version,'.',1) AS unsigned)*10000
       + cast(substring_index(substring_index(version,'.',2),'.',-1) AS unsigned)*100
       + cast(substring_index(version,'.',-1) AS unsigned)) >= 62060
) i
LEFT JOIN ( -- click 表同结构 ) c ON i.guid=c.guid AND i.domain=c.domain
LEFT JOIN ( -- order 表 GROUP BY guid,domain ) o ON i.guid=o.guid AND i.domain=o.domain
GROUP BY i.domain
ORDER BY i.domain;

SELECT '<arm>' AS arm,
       count(distinct i.guid) AS impr_users,
       count(distinct case when c.guid is not null then i.guid end) AS click_users,
       count(distinct case when c.guid is not null AND ob.client_id is not null then i.guid end) AS act_users,
       count(distinct case when c.guid is not null AND ob.client_id is not null AND sp.guid is not null then i.guid end) AS sp_users,
       count(distinct case when c.guid is not null AND ob.client_id is not null AND sp.guid is not null AND o.guid is not null then i.guid end) AS order_users,
       sum(case when c.guid is not null AND ob.client_id is not null AND sp.guid is not null AND o.guid is not null then o.cb_orders else 0 end) AS orders
FROM (impress style=2 AND enabled=1) i
LEFT JOIN (click 同 tag) c ON i.guid=c.guid
LEFT JOIN (
  -- outbound: 激活成功
  SELECT DISTINCT client_id FROM dwd_log_event_outbound_result_det_d
  WHERE access_at >= '2026-05-05' AND access_at < '2026-06-12'  -- 注意:用 access_at 不是 pt
    AND domain IN (...) AND business='2' AND outbound_state='1'
    AND NOT (outbound_state_reason_type='0' AND outbound_state_reason='1')
) ob ON i.guid=ob.client_id
LEFT JOIN (
  SELECT DISTINCT guid FROM dws_log_sp_user_shopping_journey_detail_di
  WHERE pt BETWEEN '2026-05-05' AND '2026-06-11' AND domain IN (...)
) sp ON i.guid=sp.guid
LEFT JOIN (
  SELECT guid, count(1) AS cb_orders FROM dwd_trd_user_orders_di
  WHERE create_date BETWEEN '2026-05-05' AND '2026-06-11' AND domain IN (...)
    AND bu='CP' AND is_cb_order=1 GROUP BY guid
) o ON i.guid=o.guid;

-- 3 段式版本号数值化: major×10000 + minor×100 + patch >= 62060
AND (
  cast(substring_index(version, '.', 1) AS unsigned) * 10000
  + cast(substring_index(substring_index(version, '.', 2), '.', -1) AS unsigned) * 100
  + cast(substring_index(version, '.', -1) AS unsigned)
) >= 62060

-- 示例:
--   '6.20.60' → 6*10000 + 20*100 + 60 = 62060 ✓
--   '6.20.69' → 62069 ✓
--   '6.30.9'  → 6*10000 + 30*100 + 9 = 63009 ✓
--   '6.19.89' → 61989 ✗ 不符合