5 月新增商家在 5 月的 CP 收入

统计逻辑

看「2026-05-01 ~ 2026-05-25 期间新增的 (domain, aff_id, country) 三元组」在 5 月给 CP 业务带来的 commission。 口径四要素：bu='CP'（排除 CPM/GCB/LB）、click_type ≠ 'AD_Vio'（排除违规）、 country JOIN with GLOBAL 通配（GLOBAL 三元组匹配任意 user_country，非 GLOBAL 精确匹配）、 反污染（订单 create_date ≥ 三元组首次入库时间）。

SQL

WITH new_triplets AS (
    SELECT LOWER(domain) AS domain, aff_id, country,
           MIN(created_at) AS aff_created_at
    FROM v_dim_pub_store_aff_country_df
    WHERE aff_id > 0
    GROUP BY LOWER(domain), aff_id, country
    HAVING MIN(created_at) >= '2026-05-01'
       AND MIN(created_at) <  '2026-05-26'
)
SELECT
    COUNT(DISTINCT CONCAT(t.domain,'|',CAST(t.aff_id AS VARCHAR),'|',t.country)) AS triplets_with_orders,
    COUNT(DISTINCT o.id) AS orders,
    ROUND(SUM(o.commission), 2) AS commission
FROM new_triplets t
JOIN soar_dw.dwd_trd_user_orders_di o
    ON LOWER(o.root_domain) = t.domain
    AND o.aff_id = t.aff_id
    AND (t.country = 'GLOBAL' OR o.user_country = t.country)   -- country JOIN
    AND o.create_date >= DATE(t.aff_created_at)                  -- 反污染
WHERE o.bu = 'CP' AND o.click_type != 'AD_Vio'
  AND o.create_date >= '2026-05-01' AND o.create_date < '2026-05-26';

结果

TOP 3 Domain 日度收入

TOP 3 小结