5 月 CP 收入增量分析 — 新签 + 三批运营干预

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-06-01'
),
joined AS (
    SELECT o.id, o.commission, t.domain, t.aff_id, t.country AS aff_country,
      ROW_NUMBER() OVER (
        PARTITION BY o.id
        ORDER BY
          CASE WHEN t.country = o.user_country THEN 0 ELSE 1 END,   -- 精确 country 优先
          t.aff_created_at                                                          -- 平局取最早入库
      ) AS rn
    FROM soar_dw.dwd_trd_user_orders_di o
    JOIN new_triplets t
      ON LOWER(o.root_domain) = t.domain AND o.aff_id = t.aff_id
      AND (t.country = 'GLOBAL' OR o.user_country = t.country)
      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-06-01'
)
SELECT domain, aff_id, aff_country,
       COUNT(DISTINCT id) AS orders,
       ROUND(SUM(commission), 2) AS commission
FROM joined WHERE rn = 1     -- 每订单只算一次
GROUP BY domain, aff_id, aff_country
ORDER BY commission DESC;

WITH agg AS (
  SELECT
      LOWER(root_domain) AS domain,
      SUM(CASE WHEN create_date >= '2026-04-01' AND create_date < '2026-05-01'
               THEN commission ELSE 0 END) AS apr,
      SUM(CASE WHEN create_date >= '2026-05-01' AND create_date < '2026-06-01'
               THEN commission ELSE 0 END) AS may
  FROM soar_dw.dwd_trd_user_orders_di
  WHERE bu = 'CP' AND click_type != 'AD_Vio'
    AND create_date >= '2026-04-01' AND create_date < '2026-06-01'
    AND LOWER(root_domain) IN (
        'xfinity.com', 'allegro.pl', 'shopee.com.br', 'uber.com',
        'just-eat.co.uk', 'onthatass.com', 'plaud.ai', 'udemy.com',
        'fc-moto.de', 'motatos.de', 'birminghamairport.co.uk', 'vrbo.com',
        'etsy.com', 'elcorteingles.es', 'carters.com', 'genspark.ai',
        'whatnot.com', 'doordash.com', 'kiwi.com', 'mgmresorts.com',
        'cisalfasport.it', 'parcel2go.com', 'bahn.de'
    )
  GROUP BY LOWER(root_domain)
)
SELECT domain, apr, may, (may - apr) AS delta,
       CASE WHEN apr > 0 THEN ROUND((may-apr)/apr*100, 1) END AS delta_pct
FROM agg
WHERE may > apr        -- 只看 5月 > 4月
ORDER BY delta DESC;

WITH agg AS (
  SELECT
      LOWER(root_domain) AS domain,
      SUM(CASE WHEN create_date >= '2026-04-01' AND create_date < '2026-05-01'
               THEN commission ELSE 0 END) AS apr,
      SUM(CASE WHEN create_date >= '2026-05-01' AND create_date < '2026-06-01'
               THEN commission ELSE 0 END) AS may
  FROM soar_dw.dwd_trd_user_orders_di
  WHERE bu = 'CP' AND click_type != 'AD_Vio'
    AND create_date >= '2026-04-01' AND create_date < '2026-06-01'
    AND LOWER(root_domain) IN (
        'agelessrx.com','aimn.com.au','all.accor.com','amnutrition.fr','ankerkraut.de',
        'apexminecrafthosting.com','armani-beauty.ca','atida.fr','atlascoffeeclub.com',
        'audiobooks.com','backcountry.com','bananamoon.com','bark.us','basspro.com',
        'beer52.com','bonobos.com','booking.com','bouyguestelecom.fr','broderft.com',
        'buyee.jp','cabelas.com','carbethplants.co.uk','centauro.com.br','chemicalguys.com',
        'chewy.com','compressport.com','costway.co.uk','couteauxduchef.com','cpap.com',
        'crocs.com','cyberport.de','dailyom.com','dasher.doordash.com','daylesford.com',
        'de.lassie.co','dell.com','delsey.com','digitalrepublic.ch','dilling.de',
        'ding.com','diy.com','dransay.com','espaceplaisir.fr','etam.be',
        'etravelsim.com','fandiem.com','fast-growing-trees.com','fc-moto.de','fetchpet.com',
        'fitadium.com','fitnessboutique.fr','flipmylifenow.com','floraandfauna.com.au',
        'floresonline.com.br','flycorsair.com','forhers.com','fr.peugeot-saveurs.com',
        'fr.ryobitools.eu','francoisesaget.com','fromrebel.com','frostbuddy.com',
        'fruitbouquets.com','gameseal.com','gametime.co','gaystore.fr','goldcar.es',
        'gotprint.com','guthaben.de','habitatetjardin.com','hellowisp.com','hims.com',
        'hotelcollection.com','i-run.be','i-run.de','i-run.fr','idgarages.com',
        'instantfunding.com','instanthydration.com','insurify.com','interflora.es',
        'jcpenney.com','joules.com','kiehls.ca','kinderkraft.fr','kitandkin.com',
        'lampen24.be','lomax.dk','lotto24.de','loveamika.com','lovehoney.com.au',
        'luxuryflooring.co.uk','maison-lascours.fr','maxaroma.com','mazumamobile.com',
        'medi-market.be','metrobyt-mobile.com','milan-jeunesse.com','mintmobile.com',
        'monbento.com','moneyhero.com.hk','moneywalkie.com','monlapinrose.com',
        'monoprix.fr','morgandetoi.fr','musely.com','myperfectresume.com',
        'noughtsandkisses.co.uk','odisseias.com','oliveyoung.com','ollie.com',
        'onthatass.com','oscaro.com','patisserie-valerie.co.uk','petco.com',
        'pistoladenim.com','quince.com','raja.fr','recommerce.com',
        'reservation.parcasterix.fr','resume-now.com','rhodeskin.com','ro.co',
        'rougier-ple.fr','rugsusa.com','sallybeauty.com','sanscomplexe.com',
        'sarenza.com','segurospromo.com.br','shein.co.uk','shein.com.mx','shipt.com',
        'skinceuticals.ca','skinnymixes.com','sowell.fr','stc-nutrition.fr',
        'stonehengehealth.com','t-mobile.com','taskrabbit.com','tchibo.de',
        'thecardzoo.com','thewineflyer.co.uk','totalwine.com','tractorsupply.com',
        'tripadvisor.co.uk','ubereats.com','visible.com','vitaminexpress.org',
        'walmart.com','wayfair.com','weedmaps.com','welcomepickups.com',
        'wowtv.de','xxlnutrition.com','yesoulfitness.com','yslbeauty.ca','zumub.com'
    )
  GROUP BY LOWER(root_domain)
)
SELECT domain, apr, may, (may - apr) AS delta
FROM agg WHERE may > apr
ORDER BY delta DESC;

WITH agg AS (
  SELECT
      LOWER(root_domain) AS domain,
      user_country,
      SUM(CASE WHEN create_date >= '2026-04-01' AND create_date < '2026-05-01'
               THEN commission ELSE 0 END) AS apr,
      SUM(CASE WHEN create_date >= '2026-05-01' AND create_date < '2026-06-01'
               THEN commission ELSE 0 END) AS may
  FROM soar_dw.dwd_trd_user_orders_di
  WHERE bu = 'CP' AND click_type != 'AD_Vio'
    AND create_date >= '2026-04-01' AND create_date < '2026-06-01'
    AND (
        -- GLOBAL 组（不限 user_country，CSV 里 country=GLOBAL）
        LOWER(root_domain) IN (
            'skyscanner.net','skyscanner.com','picsart.com','skyscanner.es',
            'allegro.cz','12go.asia','nordpass.com','cyberghostvpn.com',
            'surfshark.com','malwarebytes.com','avira.com','macpaw.com',
            'newspapers.com','g-portal.com','perlego.com','lalal.ai',
            'wondershare.com','veepn.com','onthatass.com','youfone.nl',
            'findmypast.co.uk','iobit.com','joyn.de','wondershare.de',
            'nrjmobile.fr','cdon.se','airdroid.com','nazwa.pl',
            'checkdomain.de','wondershare.fr','tenorshare.com','wingo.ch',
            'bebeboutik-prive.fr','manual.com.br','farmaciaindiana.com.br',
            'epidemicsound.com','lapostemobile.fr','cdon.dk','getmailbird.com',
            'wps.com','flexclip.com','trendmicro.com','healthlabs.com'
        )
        -- 精确 country 组（CSV 里指定了具体 country 且无对应 GLOBAL 行）
        OR (LOWER(root_domain)='nordvpn.com'      AND user_country='AU')
        OR (LOWER(root_domain)='momondo.com'      AND user_country='US')
        OR (LOWER(root_domain)='hellofresh.fr'    AND user_country='FR')
        OR (LOWER(root_domain)='pdffiller.com'    AND user_country='FR')
        OR (LOWER(root_domain)='boostmobile.com'  AND user_country='US')
        OR (LOWER(root_domain)='justanswer.co.uk' AND user_country='US')
        OR (LOWER(root_domain)='tello.com'        AND user_country='US')
        OR (LOWER(root_domain)='idmobile.co.uk'   AND user_country='GB')
        OR (LOWER(root_domain)='billetreduc.com'  AND user_country='FR')
        OR (LOWER(root_domain)='sling.com'        AND user_country='US')
    )
  GROUP BY LOWER(root_domain), user_country
)
SELECT domain, user_country, apr, may, (may - apr) AS delta
FROM agg WHERE may > apr
ORDER BY delta DESC;

-- A + B 数据源：不限 country
SELECT
    DATE(create_date) AS d,
    LOWER(root_domain) AS domain,
    ROUND(SUM(commission), 2) AS commission
FROM soar_dw.dwd_trd_user_orders_di
WHERE bu = 'CP' AND click_type != 'AD_Vio'
  AND create_date >= '2026-04-01' AND create_date < '2026-06-01'
  AND LOWER(root_domain) IN (
      'etsy.com','kiwi.com','fc-moto.de','doordash.com',
      'allegro.pl','xfinity.com','shopee.com.br','parcel2go.com',
      'birminghamairport.co.uk','whatnot.com','all.accor.com'
  )
GROUP BY DATE(create_date), LOWER(root_domain)
ORDER BY domain, d;

-- C 数据源 GLOBAL：不限 country
SELECT
    DATE(create_date) AS d,
    LOWER(root_domain) AS domain,
    ROUND(SUM(commission), 2) AS commission
FROM soar_dw.dwd_trd_user_orders_di
WHERE bu = 'CP' AND click_type != 'AD_Vio'
  AND create_date >= '2026-04-01' AND create_date < '2026-06-01'
  AND LOWER(root_domain) IN (
      'skyscanner.net','malwarebytes.com','veepn.com','manual.com.br'
  )
GROUP BY DATE(create_date), LOWER(root_domain)
ORDER BY domain, d;

-- 1. 活动覆盖 user 集合（看到活动弹窗的 24,150 user）
SELECT DISTINCT guid
FROM v_dwd_log_parse_extension_service_impress_di
WHERE pt >= '2026-04-24' AND pt < '2026-05-23'
  AND substring(get_json_string(extra, '$.activity_key'), 1, 3) = '18.'
  AND get_json_string(extra, '$.sp_order_cashback') = '1'
  AND scene IN ('Popup','Icon')
  AND type IN ('CB');

-- 2. 4/5 月分窗对比订单 + commission
SELECT
  o.root_domain, o.visit_country,
  SUM(CASE WHEN o.create_date BETWEEN '2026-04-01' AND '2026-04-22' THEN 1 ELSE 0 END) AS apr_orders,
  SUM(CASE WHEN o.create_date BETWEEN '2026-05-01' AND '2026-05-22' THEN 1 ELSE 0 END) AS may_orders,
  ROUND(SUM(CASE WHEN o.create_date BETWEEN '2026-04-01' AND '2026-04-22' THEN commission ELSE 0 END), 2) AS apr_comm,
  ROUND(SUM(CASE WHEN o.create_date BETWEEN '2026-05-01' AND '2026-05-22' THEN commission ELSE 0 END), 2) AS may_comm
FROM dwd_trd_user_orders_di o
WHERE o.create_date BETWEEN '2026-04-01' AND '2026-05-22'
  AND o.root_domain IN (  -- 25 商家 V4 清单
    'target.com','ebay.com','doordash.com','samsclub.com','chewy.com',
    'etsy.com','lowes.com','macys.com','ihg.com','ulta.com',
    'vistaprint.com','priceline.com','sephora.com','just-eat.co.uk','shein.com',
    'cvs.com','bestbuy.com','walgreens.com','michaels.com','jcpenney.com',
    'kohls.com','officedepot.com','michaelkors.com','adidas.com','petco.com'
  )
  AND o.guid IN ( -- 活动覆盖 24,150 user 子查询，同上 1. )
GROUP BY o.root_domain, o.visit_country;

-- 严格 6 环节漏斗（B 实验组）
SELECT
  'B_exp' 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 ( -- 曝光 + 看到文案
  SELECT DISTINCT guid FROM v_dwd_log_parse_extension_service_impress_di
  WHERE pt BETWEEN '2026-05-05' AND '2026-05-11'
    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')='6.19.90-2'
    AND get_json_object(extra,'$.core_tips_enabled')='1'
) i
LEFT JOIN ( -- 点击 / 激活 / SP / 订单 同结构 join, 见原报告附录 A.2 ) ...
;

SELECT
  Domain AS subdomain,
  multiIf(
    -- 第一批提升 11 家（剔除 medimops + agoda）
    (Domain='cvs.com' AND VisitCountry='US')
      OR (Domain='dickssportinggoods.com' AND VisitCountry='US')
      OR (Domain='shopee.com.br' AND VisitCountry='BR')
      OR (Domain='ocado.com' AND VisitCountry='GB')
      OR (Domain='qvcuk.com') OR (Domain='lidl.fr' AND VisitCountry='FR')
      OR (Domain='kohls.com' AND VisitCountry='US')
      OR (Domain='thriftbooks.com') OR (Domain='morenutrition.de' AND VisitCountry='DE')
      OR (Domain='levi.com' AND VisitCountry='US') OR (Domain='aegeanair.com'),
    '一批_提升',
    Domain IN ('getyourguide.com','viator.com'), '一批_降低',
    -- 第二批提升 18 家 + 第二批降低 25 家见原报告 A.3
    'Other'
  ) AS adj_group,
  CASE WHEN CreatedTime < '2026-04-22 00:00:00' THEN 'PRE_14d' ELSE 'POST_14d' END AS period,
  countIf(positionUTF8(ClickType, 'CB') > 0) AS cb_orders,
  countIf(positionUTF8(ClickType, 'AT') > 0) AS at_orders,
  round(sumIf(Commission, positionUTF8(ClickType, 'CB') > 0), 2) AS cb_commission,
  round(sumIf(Commission, positionUTF8(ClickType, 'AT') > 0), 2) AS at_commission
FROM user_orders
WHERE (CreatedTime >= '2026-04-08 00:00:00' AND CreatedTime < '2026-04-22 00:00:00')
   OR (CreatedTime >= '2026-05-08 00:00:00' AND CreatedTime < '2026-05-22 00:00:00')
GROUP BY Domain, adj_group, period
HAVING adj_group != 'Other';

-- SP 池过滤（关键）+ aff_id=1166 + 4/5 月 14d 对比
SELECT
  phase, days,
  COUNT(*) AS orders_total,
  ROUND(COUNT(*)*1.0/days, 1) AS orders_pd,
  ROUND(SUM(COALESCE(o.commission,0)), 2) AS comm_total,
  ROUND(SUM(COALESCE(o.commission,0))/days, 2) AS comm_pd
FROM (
  SELECT
    CASE
      WHEN o2.create_date BETWEEN '2026-04-08' AND '2026-04-21' THEN 'PRE_Apr_08-21'
      WHEN o2.create_date BETWEEN '2026-05-08' AND '2026-05-21' THEN 'POST_May_08-21'
    END AS phase,
    14 AS days, o2.*
  FROM dwd_trd_user_orders_di o2
  JOIN (                                            -- 🔑 SP 池过滤
    SELECT DISTINCT guid FROM dws_log_sp_user_shopping_journey_detail_di
    WHERE pt BETWEEN '2026-03-01' AND '2026-05-21'
      AND aff_id = 1166
      AND cps_score > 40
  ) sp ON o2.guid = sp.guid
  LEFT JOIN dwd_trd_order_cashback_det_d c
    ON CAST(o2.order_id AS CHAR) = CAST(c.order_id AS CHAR)
   AND c.aff_id = 1166
  WHERE o2.aff_id = 1166
    AND (o2.create_date BETWEEN '2026-04-08' AND '2026-04-21'
         OR o2.create_date BETWEEN '2026-05-08' AND '2026-05-21')
    AND COALESCE(o2.is_fraudulent, 0) = 0
    AND COALESCE(o2.is_canceld, 0) = 0
    AND COALESCE(o2.activity_id, 0) = 0      -- 剔活动
    AND COALESCE(c.has_activity, 0) = 0      -- 剔活动
) o
WHERE phase IS NOT NULL
GROUP BY phase, days
ORDER BY phase;

⚠️ ocado.com PRE 期 465 单仅 $28（单笔 $0.06）疑似数据/配置异常；morenutrition.de 见 §8.3 商家级事故诊断。

注：all.accor.com 和 skyscanner.net 在 SP journey 表中无记录（按 1.0 处理）。bestbuy.com（ratio=3.22）和 birminghamairport.co.uk（ratio=0.18）为异常值，需单独核查数仓口径。
商家级 ratio 而非全局 ratio：每个 domain 用自己的 SP 流量变化校准账面 delta，避免被全局平均 +0.7% 掩盖实际个体差异。

-- 全局 CP SP 总用户数
SELECT
  CASE WHEN pt BETWEEN '2026-04-01' AND '2026-04-30' THEN 'Apr_30d'
       WHEN pt BETWEEN '2026-05-01' AND '2026-05-31' THEN 'May_31d' END AS period,
  COUNT(DISTINCT guid) AS sp_users
FROM dws_log_sp_user_shopping_journey_detail_di
WHERE (pt BETWEEN '2026-04-01' AND '2026-04-30' OR pt BETWEEN '2026-05-01' AND '2026-05-31')
GROUP BY period;

-- 各 domain SP ratio
SELECT
  domain,
  COUNT(DISTINCT CASE WHEN pt BETWEEN '2026-04-01' AND '2026-04-30' THEN guid END) AS apr_sp,
  COUNT(DISTINCT CASE WHEN pt BETWEEN '2026-05-01' AND '2026-05-31' THEN guid END) AS may_sp,
  ROUND(COUNT(DISTINCT CASE WHEN pt BETWEEN '2026-05-01' AND '2026-05-31' THEN guid END) * 1.0 /
        NULLIF(COUNT(DISTINCT CASE WHEN pt BETWEEN '2026-04-01' AND '2026-04-30' THEN guid END), 0), 3) AS sp_ratio
FROM dws_log_sp_user_shopping_journey_detail_di
WHERE pt BETWEEN '2026-04-01' AND '2026-05-31'
  AND domain IN ( /* 上涨 domain 清单，见 §4.3 */ )
GROUP BY domain
ORDER BY sp_ratio DESC;