Coupert SP 用户分层完整分析

0 · TL;DR — 核心结论一屏

📊 R×F 8 桶大盘（按 GMV 占比排序）

1 · 分析目标与背景

1.1 目标

把 Coupert 里"会在 CPS 商家实际购物"的 SP 用户，按营销可运营的维度分成若干桶，供后续差异化营销素材、Push 频次、邮件策略、返现补贴使用。

1.2 4 维度

1.3 为什么加"主商家类目"

2 · 数据基础与口径

2.1 主数据源：soar_dw.dws_log_sp_user_shopping_journey_detail_di

Coupert SP（Shopping Success）用户成单明细宽表。每一行 = 一次 SP journey（一个 checkout 页触发的下单流程）。

2.2 过滤条件（5 层）

2.3 基础统计

2.4 基础 SQL

-- 基础用户表 SQL
SELECT guid, sp_id, domain, order_value, create_time
FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di
WHERE pt BETWEEN DATE '2026-04-09' AND DATE '2026-07-07'
  AND cps_score >= 40
  AND COALESCE(extra_session_first_page, '0') != '1'
  AND COALESCE(extra_no_checkout, '0') != '1'

3 · Step 1：主商家 9 类聚类

3.1 为什么需要主商家类目

用户 SP 商家跨度大 —— 有人只买 amazon，有人在 booking / doordash / mi.com 都买。要给用户打"主类目"标签，就必须先给每个 domain 打上类目标签，再按用户 SP 次数投票选出主类目。

3.2 类目源探查

数仓里有多张商家类目表，chensu 指定用 soar_dw.v_dwd_store_merchant_ai_audit_df（AI 判定的商家审核表）。

audit 表字段结构

DESCRIBE soar_dw.v_dwd_store_merchant_ai_audit_df;
-- 核心字段：
-- pt (datetime), store_id, domain, category (varchar 256),
-- shopping_type, is_merchant (0/1), ai_model, ai_prompt_version
-- 每个 domain 只在被审核的那天出现一次（非每日快照），所以取 domain 最新记录用 ROW_NUMBER

3.3 覆盖率验证

3.4 34 类 → 9 大类合并映射

3.5 关键问题：Uncategorized 占 35% 流量，怎么办？

Top 20 未分类 domain（按 SP 流量倒序）

3.6 手工兜底 91 domain 分类分配

3.7 兜底前 vs 兜底后（Before / After）

3.8 完整聚类 SQL

-- ═══ Step 1: 用户主商家 9 类聚类 ═══
WITH audit_uniq AS (
  -- 取每个 domain 最新一次 AI 审核记录
  SELECT domain, category FROM (
    SELECT domain, category,
           ROW_NUMBER() OVER (PARTITION BY domain ORDER BY pt DESC, id DESC) AS rn
    FROM soar_dw.v_dwd_store_merchant_ai_audit_df
    WHERE domain IS NOT NULL AND domain != ''
  ) t1 WHERE rn = 1
),
manual_map AS (
  -- 手工兜底 91 个高流量 domain
  SELECT * FROM (VALUES
    ('amazon.com','1_Ecom'), ('amazon.es','1_Ecom'), ...(38 Ecom)
    ('booking.com','3_Travel'), ...(10 Travel)
    ('doordash.com','4_Food'), ...(14 Food)
    ('mi.com','5_Electronics'), ...(9 Electronics)
    ('docmorris.de','7_Health'), ...(5 Health)
    ... (17 Other, 3 Home, 3 Business)
  ) AS mm(domain, cat_manual)
),
sp AS (
  SELECT guid, domain
  FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di
  WHERE pt BETWEEN DATE '2026-04-09' AND DATE '2026-07-07'
    AND cps_score >= 40
    AND COALESCE(extra_session_first_page, '0') != '1'
    AND COALESCE(extra_no_checkout, '0') != '1'
),
tagged AS (
  -- 每条 SP 记录打上 9 类标签：优先手工兜底，其次 AI 类目，最后 Uncategorized
  SELECT
    s.guid,
    COALESCE(
      m.cat_manual,
      CASE
        WHEN a.category = 'E-commerce & Shopping' THEN '1_Ecom'
        WHEN a.category IN ('Travel & Tourism','Tickets') THEN '3_Travel'
        WHEN a.category = 'Food & Drink' THEN '4_Food'
        WHEN a.category IN ('Computers Electronics & Technology',...) THEN '5_Electronics'
        WHEN a.category = 'Home & Garden' THEN '6_Home'
        WHEN a.category IN ('Health','Health & Fitness') THEN '7_Health'
        WHEN a.category IN ('Business & Consumer Services','Finance',...) THEN '8_Business'
        WHEN a.category IS NULL OR a.category = '' THEN '2_Uncategorized'
        ELSE '9_Other'
      END
    ) AS cat9
  FROM sp s
  LEFT JOIN audit_uniq a ON a.domain = s.domain
  LEFT JOIN manual_map m ON m.domain = s.domain
),
user_cat AS (-- 每 user × 每类目 SP 次数
  SELECT guid, cat9, COUNT(*) AS c FROM tagged GROUP BY guid, cat9
),
user_cat_rn AS (
  SELECT guid, cat9, ROW_NUMBER() OVER (PARTITION BY guid ORDER BY c DESC, cat9) AS rn
  FROM user_cat
)
-- 主类目 = 该用户 SP 次数最多的类目
SELECT guid, cat9 AS main_cat FROM user_cat_rn WHERE rn = 1;

4 · Step 2：R/F/M 分布探查

4.1 为什么先看分布

按数据实际分布选切分点，避免"拍脑袋" 25%/50%/75% 分位。分布决定切分逻辑。

4.2 R (Recency) — 最近一次 SP 距 2026-07-07 天数

4.3 F (Frequency) — 90d 内 SP 次数

4.4 M (Monetary) — 90d 累计 GMV (USD)

4.5 分布探查 SQL

WITH ug AS (
  SELECT
    guid,
    DATEDIFF(DATE '2026-07-07', CAST(MAX(create_time) AS DATE)) AS r_days,
    COUNT(DISTINCT sp_id) AS f,
    SUM(CASE WHEN order_value BETWEEN 0.01 AND 10000 THEN order_value ELSE 0 END) AS m
  FROM sp
  GROUP BY guid
)
SELECT
  'R (recency days)' AS metric,
  SUM(CASE WHEN r_days <= 7 THEN 1 ELSE 0 END) AS b1_le7,
  SUM(CASE WHEN r_days BETWEEN 8 AND 14 THEN 1 ELSE 0 END) AS b2_8_14,
  ... (类似 for 15-30, 31-60, 61-90)
FROM ug
UNION ALL
SELECT 'F (sp count)', ... 类似 F 分档
UNION ALL
SELECT 'M (gmv usd)', ... 类似 M 分档

5 · Step 3：R×F 8 桶大盘分层

5.1 8 桶定义（经典 RFM 变体）

5.2 8 桶大盘分布（完整版）

5.3 幂律可视化：用户占比 vs GMV 占比

5.4 8 桶分层 SQL（核心逻辑）

-- ═══ Step 3: R×F 8 桶分层 ═══
WITH user_rfm AS (
  SELECT
    guid,
    DATEDIFF(DATE '2026-07-07', CAST(MAX(create_time) AS DATE)) AS r_days,
    COUNT(DISTINCT sp_id) AS f,
    SUM(CASE WHEN order_value BETWEEN 0.01 AND 10000 THEN order_value ELSE 0 END) AS m
  FROM tagged  -- 来自 Step 1 的 tagged CTE
  GROUP BY guid
),
user_seg AS (
  SELECT guid, r_days, f, m,
    CASE
      WHEN r_days <= 14 AND f >= 5              THEN 'B1_Champions'
      WHEN r_days <= 30 AND f BETWEEN 2 AND 4 THEN 'B2_Loyal'
      WHEN r_days <= 14 AND f = 1              THEN 'B3_NewBuyer'
      WHEN r_days > 30 AND f >= 5              THEN 'B4_AtRisk'
      WHEN r_days > 30 AND f BETWEEN 2 AND 4  THEN 'B5_Hibernating'
      WHEN r_days > 30 AND f = 1               THEN 'B6_Lost'
      WHEN r_days BETWEEN 15 AND 30 AND f = 1  THEN 'B7_MidNew'
      WHEN r_days BETWEEN 15 AND 30 AND f >= 5 THEN 'B8_MidActive'
      ELSE 'B9_Other'
    END AS rf_bucket
  FROM user_rfm
)
SELECT
  rf_bucket,
  COUNT(*) AS users,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) AS pct,
  ROUND(AVG(r_days), 1) AS avg_r,
  ROUND(AVG(f), 2) AS avg_f,
  ROUND(AVG(m), 0) AS avg_m,
  SUM(m) AS total_gmv,
  ROUND(100.0 * SUM(m) / SUM(SUM(m)) OVER (), 2) AS gmv_pct
FROM user_seg
GROUP BY rf_bucket
ORDER BY rf_bucket;

6 · Step 4：R×F × 主商家 交叉画像（Heatmap）

6.1 每桶内主类目分布（占比 %，每行加起来 100%）

6.2 三大交叉洞察

6.3 交叉画像 SQL

SELECT
  rf_bucket,
  SUM(CASE WHEN main_cat='1_Ecom' THEN 1 ELSE 0 END) AS ecom,
  SUM(CASE WHEN main_cat='2_Uncategorized' THEN 1 ELSE 0 END) AS uncat,
  SUM(CASE WHEN main_cat='3_Travel' THEN 1 ELSE 0 END) AS travel,
  SUM(CASE WHEN main_cat='4_Food' THEN 1 ELSE 0 END) AS food,
  SUM(CASE WHEN main_cat='5_Electronics' THEN 1 ELSE 0 END) AS elec,
  -- ... home / health / biz / other 类似
  COUNT(*) AS total
FROM user_seg s
JOIN user_main u ON u.guid = s.guid  -- 来自 Step 1
GROUP BY rf_bucket
ORDER BY rf_bucket;

7 · 最终 10 个营销分层桶

综合 R×F 8 桶 + 主商家类目 heatmap，输出 10 个可运营的营销分层桶。按 GMV 权重 × 可营销性 排序。

8 · 三大结论 + 后续建议

结论 A：GMV 幂律严重，17% 用户贡献 50% 收益

结论 B：Amazon 是 Coupert 生命线

结论 C：47 万 Lost 用户不要浪费预算

后续建议 · 下一步深挖方向

1. 每桶 top 商家清单 — 每个营销桶输出 top 20 商家 domain，做营销素材选品用
2. Amazon 用户深度画像 — 单独拆 Amazon 忠粉的 AOV/子品类/复购频次
3. 服务偏好维度（svc%）— 加第 3 维：每桶用户对 Coupert CB / AT / PCP 弹窗的偏好差异，用来定弹窗策略
4. 桶迁移路径分析 — 90d 前的用户现在在哪个桶？B4→B1 是否可能？B3→B6 转化率？
5. Amazon 服务健康 dashboard — 监控 Amazon 出站成功率 / 返现兑现率 / AT 弹窗准确率

9 · 附录：字段释义 & 完整 SQL 集

9.1 用户级聚合字段释义

9.2 主要数据表说明

9.3 完整端到端 SQL（复制即可运行）

-- ═══════════════════════════════════════════════
-- Coupert SP 用户分层完整 SQL · 2026-07-09
-- ═══════════════════════════════════════════════

WITH audit_uniq AS (
  -- 每个 domain 最新审核结果
  SELECT domain, category FROM (
    SELECT domain, category,
           ROW_NUMBER() OVER (PARTITION BY domain ORDER BY pt DESC, id DESC) AS rn
    FROM soar_dw.v_dwd_store_merchant_ai_audit_df
    WHERE domain IS NOT NULL AND domain != ''
  ) t1 WHERE rn = 1
),

manual_map AS (
  -- 91 个 top domain 手工兜底
  SELECT * FROM (VALUES
    -- Ecom 综合电商 (38)
    ('amazon.com','1_Ecom'),('amazon.es','1_Ecom'),('amazon.it','1_Ecom'),
    ('amazon.com.br','1_Ecom'),('amazon.pl','1_Ecom'),('amazon.in','1_Ecom'),
    ('amazon.com.au','1_Ecom'),('amazon.com.be','1_Ecom'),
    ('ebay.com','1_Ecom'),('etsy.com','1_Ecom'),('target.com','1_Ecom'),
    ('macys.com','1_Ecom'),('kohls.com','1_Ecom'),('belk.com','1_Ecom'),
    ('bedbathandbeyond.com','1_Ecom'),('groupon.com','1_Ecom'),
    ('newlook.com','1_Ecom'),('kogan.com','1_Ecom'),('aliexpress.us','1_Ecom'),
    ('1688.com','1_Ecom'),('wilko.com','1_Ecom'),('barnesandnoble.com','1_Ecom'),
    ('jcrew.com','1_Ecom'),('boohoo.com','1_Ecom'),('desigual.com','1_Ecom'),
    ('placedestendances.com','1_Ecom'),('intimissimi.com','1_Ecom'),
    ('pandora.net','1_Ecom'),('laredoute.co.uk','1_Ecom'),('oakley.com','1_Ecom'),
    ('birkenstock.com','1_Ecom'),('bonds.com.au','1_Ecom'),('momox.de','1_Ecom'),
    ('rebuy.de','1_Ecom'),('avon.com','1_Ecom'),('eyebuydirect.com','1_Ecom'),
    ('carters.com','1_Ecom'),('centauro.com.br','1_Ecom'),

    -- Travel 旅游 (10)
    ('booking.com','3_Travel'),('priceline.com','3_Travel'),('transavia.com','3_Travel'),
    ('tripadvisor.com','3_Travel'),('etihad.com','3_Travel'),('ita-airways.com','3_Travel'),
    ('jettours.com','3_Travel'),('promosejours.com','3_Travel'),
    ('decolar.com','3_Travel'),('ticketmaster.fr','3_Travel'),

    -- Food 食品 (14)
    ('doordash.com','4_Food'),('lieferando.de','4_Food'),('lieferando.at','4_Food'),
    ('pyszne.pl','4_Food'),('dominos.co.uk','4_Food'),('just-eat.ch','4_Food'),
    ('takeaway.com','4_Food'),('foodlion.com','4_Food'),('e.leclerc','4_Food'),
    ('monoprix.fr','4_Food'),('carrefour.com.br','4_Food'),('woolworths.com.au','4_Food'),
    ('totalwine.com','4_Food'),('nescafe-dolcegusto.com.br','4_Food'),

    -- Electronics 电子 (9)
    ('mi.com','5_Electronics'),('notebooksbilliger.de','5_Electronics'),
    ('bestbuy.ca','5_Electronics'),('farnell.com','5_Electronics'),
    ('tineco.com','5_Electronics'),('waves.com','5_Electronics'),
    ('terabyteshop.com.br','5_Electronics'),('alza.hu','5_Electronics'),('alza.de','5_Electronics'),

    -- Home 家居 (3)
    ('leroymerlin.es','6_Home'),('bueroshop24.de','6_Home'),('yeti.com','6_Home'),

    -- Health 健康 (5)
    ('docmorris.de','7_Health'),('pharmacy2u.co.uk','7_Health'),
    ('drmax.cz','7_Health'),('drogal.com.br','7_Health'),('esn.com','7_Health'),

    -- Business 商务 (3)
    ('myperfectresume.com','8_Business'),('onfastspring.com','8_Business'),('lebara.co.uk','8_Business'),

    -- Other 其他 (17): 宠物+运动+汽配+工具
    ('chewy.com','9_Other'),('petsmart.com','9_Other'),('petsmart.ca','9_Other'),
    ('petco.com','9_Other'),('hoka.com','9_Other'),('dickssportinggoods.com','9_Other'),
    ('anacondastores.com','9_Other'),('decathlon.com.br','9_Other'),
    ('nike.com.br','9_Other'),('crocs.com','9_Other'),('jdsports.fr','9_Other'),
    ('topps.com','9_Other'),('motointegrator.de','9_Other'),('motointegrator.fr','9_Other'),
    ('oscaro.com','9_Other'),('acmetools.com','9_Other'),('zoro.com','9_Other')
  ) AS mm(domain, cat_manual)
),

sp AS (
  SELECT guid, sp_id, domain, order_value, create_time
  FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di
  WHERE pt BETWEEN DATE '2026-04-09' AND DATE '2026-07-07'
    AND cps_score >= 40
    AND COALESCE(extra_session_first_page, '0') != '1'
    AND COALESCE(extra_no_checkout, '0') != '1'
),

tagged AS (
  SELECT s.guid, s.sp_id, s.order_value, s.create_time,
    COALESCE(
      m.cat_manual,
      CASE
        WHEN a.category = 'E-commerce & Shopping' THEN '1_Ecom'
        WHEN a.category IN ('Travel & Tourism','Tickets') THEN '3_Travel'
        WHEN a.category = 'Food & Drink' THEN '4_Food'
        WHEN a.category IN ('Computers Electronics & Technology',
                             'Computers, Electronics & Technology') THEN '5_Electronics'
        WHEN a.category = 'Home & Garden' THEN '6_Home'
        WHEN a.category IN ('Health','Health & Fitness') THEN '7_Health'
        WHEN a.category IN ('Business & Consumer Services','Finance',
                             'Science & Education','Legal Services & Government',
                             'Jobs & Career','Real Estate') THEN '8_Business'
        WHEN a.category IS NULL OR a.category = '' THEN '2_Uncategorized'
        ELSE '9_Other'
      END
    ) AS cat9
  FROM sp s
  LEFT JOIN audit_uniq a ON a.domain = s.domain
  LEFT JOIN manual_map m ON m.domain = s.domain
),

user_cat AS (
  SELECT guid, cat9, COUNT(*) AS c
  FROM tagged GROUP BY guid, cat9
),
user_cat_rn AS (
  SELECT guid, cat9,
         ROW_NUMBER() OVER (PARTITION BY guid ORDER BY c DESC, cat9) AS rn
  FROM user_cat
),
user_main AS (
  SELECT guid, cat9 AS main_cat FROM user_cat_rn WHERE rn = 1
),

user_rfm AS (
  SELECT guid,
    DATEDIFF(DATE '2026-07-07', CAST(MAX(create_time) AS DATE)) AS r_days,
    COUNT(DISTINCT sp_id) AS f,
    SUM(CASE WHEN order_value BETWEEN 0.01 AND 10000 THEN order_value ELSE 0 END) AS m
  FROM tagged GROUP BY guid
),

user_seg AS (
  SELECT r.guid, u.main_cat, r.r_days, r.f, r.m,
    CASE
      WHEN r.r_days <= 14 AND r.f >= 5              THEN 'B1_Champions'
      WHEN r.r_days <= 30 AND r.f BETWEEN 2 AND 4 THEN 'B2_Loyal'
      WHEN r.r_days <= 14 AND r.f = 1              THEN 'B3_NewBuyer'
      WHEN r.r_days > 30 AND r.f >= 5              THEN 'B4_AtRisk'
      WHEN r.r_days > 30 AND r.f BETWEEN 2 AND 4  THEN 'B5_Hibernating'
      WHEN r.r_days > 30 AND r.f = 1               THEN 'B6_Lost'
      WHEN r.r_days BETWEEN 15 AND 30 AND r.f = 1  THEN 'B7_MidNew'
      WHEN r.r_days BETWEEN 15 AND 30 AND r.f >= 5 THEN 'B8_MidActive'
      ELSE 'B9_Other'
    END AS rf_bucket
  FROM user_rfm r JOIN user_main u ON u.guid = r.guid
)

-- 最终查询：R×F × 主类目 交叉画像
SELECT
  rf_bucket, main_cat,
  COUNT(*) AS users,
  ROUND(AVG(r_days), 1) AS avg_r,
  ROUND(AVG(f), 2) AS avg_f,
  ROUND(AVG(m), 0) AS avg_m,
  SUM(m) AS total_gmv
FROM user_seg
GROUP BY rf_bucket, main_cat
ORDER BY rf_bucket, main_cat;