数仓需求：SP Monetization 报表加 4 类新字段

后台 SP 漏斗有 2 个核心报表页：

现状缺口：

1. AT 失败补贴：用户点了 AT 但试码全失败时，发 reason=220 到钱包，但这部分用户没被 at_test_success_user_cnt 算进去 → 实际 AT 价值兑现率被低估
2. CB Unclaimed：订单已产生但还未结算的 cashback，在 dwd_trd_order_cashback_det_d 之外（在 v_dwd_trd_order_unclaimed_cashback_detail_df），目前完全没纳入 cb_service_success_user_cnt
3. 悬浮球（XF / FloatWin）：曝光/点击数据在 v_dwd_cp_log_impress_other_log_di，与 dws_log_domain_guid_extension_impress_kpis_d 不同表，目前 summary 报表没有这部分

配套（可选，方便算 CTR）：xf_impr_cnt（PV，曝光次数不去重）、xf_click_cnt（PV，点击次数不去重）

INSERT INTO rpt_sp_user_monetization_daily_performance_summary_d
(date, platform, country, store_type, at_test_success_user_cnt_with_subsidy)
SELECT date,
       coalesce(platform,'ALL') AS platform,
       coalesce(country,'ALL') AS country,
       store_type,
       count(distinct t1.guid) AS at_test_success_user_cnt_with_subsidy
FROM sp_guids t1
JOIN (
  -- 路径 A: AT 试码成功（沿用现有 ETL 行 481-526 口径）
  SELECT DISTINCT
    client_id AS guid, domain, pt,
    'is_at_test' AS type
  FROM v_dwd_log_parse_testing_log_di
  WHERE pt >= '${before30d_date}' AND pt < '${end_date}'
    AND source = 1 AND business = 1
    AND (get_json_string(extra,'$.clicked_from') != 'Auto_Testing'
         OR get_json_string(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))

  UNION ALL

  -- 路径 B: AT 失败补贴（新增）
  -- wallet_log 没 domain，补贴只能按 (guid, pt) 关联 sp_guids
  SELECT DISTINCT
    gm.guid AS guid,
    NULL AS domain,  -- 补贴无 domain 维度
    date(w.created_at) AS pt,
    'is_at_subsidy' AS type
  FROM dwd_user_wallet_log_d w
  JOIN dwd_user_guid_map_df gm ON gm.user_id = w.user_id
  WHERE date(w.created_at) >= '${before30d_date}'
    AND date(w.created_at) < '${end_date}'
    AND w.reason = '220'
) t2
ON  t1.guid = t2.guid
AND t1.date = t2.pt
AND (t1.domain = t2.domain OR t2.domain IS NULL)  -- 补贴放宽到 (guid, date)
GROUP BY grouping sets (
  (date, store_type, platform, country),
  (date, store_type, platform),
  (date, store_type, country),
  (date, store_type)
);

INSERT INTO rpt_sp_user_monetization_daily_performance_summary_d
(date, platform, country, store_type, cb_service_success_user_cnt_with_unclaimed)
SELECT date,
       coalesce(platform,'ALL') AS platform,
       coalesce(country,'ALL') AS country,
       store_type,
       count(distinct t1.guid) AS cb_service_success_user_cnt_with_unclaimed
FROM sp_guids t1
JOIN (
  -- 路径 A: CB 已结算（沿用现有 ETL 行 509-516 口径，含 g7k2_guid 反推）
  SELECT DISTINCT
    CASE WHEN coalesce(guid,'') = '' THEN get_json_object(extra,'$.g7k2_guid')
         ELSE guid END AS guid,
    domain,
    date(ordered_at) AS pt,
    'is_cashback' AS type
  FROM dwd_trd_order_cashback_det_d
  WHERE ordered_at >= '${before30d_date}'
    AND ordered_at < '${end_date}'

  UNION ALL

  -- 路径 B: CB Unclaimed（新增）
  SELECT DISTINCT
    guid, domain,
    date(ordered_at) AS pt,
    'is_unclaimed' AS type
  FROM v_dwd_trd_order_unclaimed_cashback_detail_df
  WHERE ordered_at >= '${before30d_date}'
    AND ordered_at < '${end_date}'
    AND cashback_status IN (1, 3)  -- 1=pending, 3=expired
) t2
ON  t1.domain = t2.domain
AND t1.date   = t2.pt
AND t1.guid   = t2.guid
GROUP BY grouping sets (
  (date, store_type, platform, country),
  (date, store_type, platform),
  (date, store_type, country),
  (date, store_type)
);

INSERT INTO rpt_sp_user_monetization_daily_performance_summary_d
(date, platform, country, store_type, service_success_user_cnt_with_boost)
SELECT date,
       coalesce(platform,'ALL') AS platform,
       coalesce(country,'ALL') AS country,
       store_type,
       count(distinct t1.guid) AS service_success_user_cnt_with_boost
FROM sp_guids t1
JOIN (
  -- 4 个 UNION ALL（复用字段 1/2 的子查询）
  SELECT client_id AS guid, domain, pt, 'is_at_test' AS type
  FROM v_dwd_log_parse_testing_log_di
  WHERE pt >= '${before30d_date}' AND pt < '${end_date}'
    AND source = 1 AND business = 1
    AND (saved_amount > 0 OR (save_type > 0 AND save_type < 100))

  UNION ALL

  SELECT gm.guid, NULL AS domain, date(w.created_at), 'is_at_subsidy'
  FROM dwd_user_wallet_log_d w
  JOIN dwd_user_guid_map_df gm ON gm.user_id = w.user_id
  WHERE w.reason = '220'

  UNION ALL

  SELECT CASE WHEN coalesce(guid,'') = '' THEN get_json_object(extra,'$.g7k2_guid')
              ELSE guid END, domain, date(ordered_at), 'is_cashback'
  FROM dwd_trd_order_cashback_det_d
  WHERE ordered_at >= '${before30d_date}' AND ordered_at < '${end_date}'

  UNION ALL

  SELECT guid, domain, date(ordered_at), 'is_unclaimed'
  FROM v_dwd_trd_order_unclaimed_cashback_detail_df
  WHERE ordered_at >= '${before30d_date}' AND ordered_at < '${end_date}'
    AND cashback_status IN (1, 3)
) t2
ON t1.guid = t2.guid AND t1.date = t2.pt
AND (t1.domain = t2.domain OR t2.domain IS NULL)
GROUP BY grouping sets (
  (date, store_type, platform, country),
  (date, store_type, platform),
  (date, store_type, country),
  (date, store_type)
);

INSERT INTO rpt_sp_user_monetization_daily_performance_summary_d
(date, platform, country, store_type, xf_impr_user_cnt, xf_impr_cnt)
SELECT date,
       coalesce(platform,'ALL') AS platform,
       coalesce(country,'ALL') AS country,
       store_type,
       count(distinct t1.guid) AS xf_impr_user_cnt,
       count(*) AS xf_impr_cnt
FROM sp_guids t1
JOIN (
  SELECT guid, domain, date(pt) AS pt
  FROM v_dwd_cp_log_impress_other_log_di
  WHERE pt >= '${before30d_date}' AND pt < '${end_date}'
    AND category = 'float_win'
    AND label = 'FloatWin_Icon'
    AND action = 'impression'
) t2
ON  t1.domain = t2.domain
AND t1.date   = t2.pt
AND t1.guid   = t2.guid
GROUP BY grouping sets (
  (date, store_type, platform, country),
  (date, store_type, platform),
  (date, store_type, country),
  (date, store_type)
);

INSERT INTO rpt_sp_user_monetization_daily_performance_summary_d
(date, platform, country, store_type, xf_click_user_cnt, xf_click_cnt)
SELECT date,
       coalesce(platform,'ALL') AS platform,
       coalesce(country,'ALL') AS country,
       store_type,
       count(distinct t1.guid) AS xf_click_user_cnt,
       count(*) AS xf_click_cnt
FROM sp_guids t1
JOIN (
  SELECT guid, domain, pt
  FROM dwd_log_v2_parse_extension_service_click_di
  WHERE pt >= '${before30d_date}' AND pt < '${end_date}'
    AND click_type = 'XF'
    AND scene = 'FloatWin'
) t2
ON  t1.domain = t2.domain
AND t1.date   = t2.pt
AND t1.guid   = t2.guid
GROUP BY grouping sets (
  (date, store_type, platform, country),
  (date, store_type, platform),
  (date, store_type, country),
  (date, store_type)
);

detail 表 rpt_sp_user_monetization_daily_performance_d 使用同一份 sp_guids，只是 grouping sets 多了 store（domain）维度。

GROUP BY grouping sets (
  (date, t1.domain, store_type, platform, country),
  (date, t1.domain, store_type, platform),
  (date, t1.domain, store_type, country),
  (date, t1.domain, store_type)
)

字段类型、SQL 逻辑与 summary 表完全一致，只是按 store 切分输出。

每个 store 一行，新增同样 5 个字段列。

• 在 summary + detail 表加 5 个字段（DDL）
• 改 ETL SQL，按上面逻辑回灌近 30 天历史数据
• 跟 PM 对 5-10 验证数据（at_subsidy ≈ 37, cb_unclaimed ≈ 1,825）

• SPUserMonetizationDailyController 把新字段加入 response

• overview 页加 3 个指标卡 + 悬浮球区块
• segment-by-store 页加 5 列