AFF-SP · SP 用户价值分析（OV × 成单CR / CPA 直算佣金）

① 三类用户价值（总数据）

验证：真实估算 B（$3.49M）≈ 已实现 C（$3.22M），差 6% → OV×CR×成单率 建模成立。预估 A（$34.87M）= 已实现 10.8×，是可激活天花板。

② 按佣金区间的用户分布（三口径对照）

已实现：66.6%（100.8 万）零佣金；顶 2.2%（20+ 档 3.3 万人）= 53.5% 佣金，50+ 档 9,115 人(0.6%)独占 31%。

③ 每个区间 ≥3 个用户的详细计算过程（按 A 预估分桶）

公式：普通商家 事件est = OV × CR、事件modeled = OV × CR × 成单率；CPA 商家（CR>1）事件est = 事件modeled = cp_commission；三类均"对该用户所有 SP 事件求和"。下示每用户关键事件，全部可用上面公式核对。

⚠️ 从样例可见两个真相：①booking 等旅游商家 CR 不低(4.2%)但成单率极低(0.2%) → 预估虚高、真实估算/已实现极低；②amazon/att 等"缺销售额"商家被判为 CPA → 直接用 cp_commission（多为 0）→ 高 OV 的 amazon 大户在预估里几乎不计分。

③′ 单用户完整拆解示例（逐字段定义 + 计算）

以 0–0.5 桶用户 0000b9508a7c6b0c247023daed8d7b41 为例，完整演示每个值怎么来的、定义是什么。该用户 90 天内有 2 次 SP（2 个 sp_id）：

字段定义 + 来源

关键：CR / orate / is_cpa 是"商家的平均水平"（全体用户算出来的），套到这个人的 OV 上；OV / cp_comm 才是这个人自己的。

逐事件计算（is_cpa=0 → 用 OV×CR）

用户总值 = 两次 SP 求和

所以 “lidl.fr OV=0 → est=$0.209” 的意思是：lidl 这条贡献 0，用户的 est 全部来自 thetrainline = $0.209。

三个值对这个人意味着什么

• 预估 $0.209：若 thetrainline 那笔 $10.25 订单按其平均佣金率(2.04%)成交并归因，Coupert 能赚 ~$0.21——上限/天花板。
• 真实估算 $0.025：但 thetrainline 的 SP 只有 12.1% 真正成单，期望佣金 ≈ $0.209 × 12.1% ≈ $0.025——按转化概率折算的建模真实值。
• 已实现 $0.000：他这两次 SP 实际都没成单（无归因订单）→ Coupert 真实到手 $0。

一句话：低价值、未转化用户——逛了 trainline($10) 和 lidl 但都没成单，真实贡献 0，潜在也就 ~$0.21。

④ 完整计算逻辑（逐步）

• Step 1 人群：journey 表（一行=一次 SP，已验证 sp_id 唯一）· 90 天 · aff_id>0 AND cps_score>40 · JOIN dmp first_install_time ≤ 2026-03-18。
• Step 2 每 domain 的 CR / 成单率 / CPA 标记：CR=Σcp_commission/Σcp_sales；成单率=成单事件/总事件；CPA = (CR>1)。不剔除，CPA 单独处理。
• Step 3 OV：用 order_value（USD 归一、每 SP 一个、完成时刻值）。
• Step 4 三类价值（每用户求和）：A 预估=普通 OV×CR / CPA 直接 cp_commission；B 真实估算=普通 OV×CR×成单率 / CPA cp_commission；C 已实现=cp_commission。
• Step 5：按佣金区间分桶，统计每桶用户量、总价值，并抽样验证（见 ③）。

⑧ 价值分层：低 / 中 / 高（90天 · B 真实估算）

分类逻辑：B 价值分布极度右偏（长尾/帕累托），用绝对佣金阈值切——$1≈一笔小单期望佣金、$5≈攒够一次可感知返利。这样切出来正好对应 价值贡献 7:23:70 的帕累托结构。

⑨ 分层 × 使用频率 × 同行强度

使用频率（90天 SP 次数）× 价值层 × 用过同行%

频率档定义：单次=偶遇/试用（无法判断习惯）；低频2-4=偶发（月均<1.5次）；高频5+=习惯性（≈每两周≥1次）。价值↔频率强正相关：高价值 63% 是高频，低价值仅 17%。

同行使用强度 × 价值层（本群分布：5.4%没用 · 中位39次 · p90=274 · avg 137）

"用没用过同行"区分度太低（90-99% 全员用过），改看强度：价值越高同行用得越凶（重度占比 低6.6%→中13.5%→高19.8%，3×）。高价值·同行重度 28,499 人、人均 $21.00（全表最高）= 被同行争夺最激烈的金矿。

⑩ 服务使用率分层（service_used 点击 · ⚠️口径已校正）

⚠️ 口径校正（2026-06-18）：权威 Service Used Rate (%SP) = 点击人数 / SP人数，来自报表源表 rpt_sp_..._summary_d（AFF_SP_CPS_STORE），大盘 ≈ 46%/天（已逐日复现，见下表）。本节早先用本明细表 service_used='1' 得到的 60%（90d 累计覆盖，"90 天内点过任一次"即算，天然高于日级）与 34%（单日）均不能直接对标大盘 46%：本明细表 _detail_di 的 SP 用户分母比官方 sp_user_cnt 系统性宽 ~40%（06-08：77,723 vs 54,301），官方 SP 池口径走的 canonical journey 表本账号无权限精确还原。故下方分桶仅作"价值层间相对比较"，绝对率不代表大盘。

是否用过服务（点过 vs 从不点 · 90d 累计覆盖口径，仅层间相对比较）

单调正相关（结论稳健，不受分母口径影响）：价值越高点过服务比例越高（低50%→中79%→高85%）；新用户(install<90d)累计覆盖 65% 略>老用户 60%。注：此处为 90d 累计覆盖率，非日级；绝对值勿与大盘 46% 比。

使用深度（用户自己的点击率，对标本明细表事件级 32.7%，非官方大盘；avg_ev=人均SP次数）

⑪ 时间窗推算 + 高价值集中商家 + 新用户

30/60/90 天窗口（install≥90d · 窗口越长越多用户被"招募"进高价值层）

月度建模佣金 run-rate ≈ $1.1-1.2M；高价值人均跨窗几乎不变（$15.6→$16.9）——延长观察主要是多招募高价值用户而非把老用户拉更高。

高价值用户集中商家 TOP（按高价值用户数）

真正可变现集中区：aliexpress/ebay (CB) + 旅游 booking/agoda/getyourguide (高OV) + 品牌鞋服 (adidas/puma)。amazon 足迹最广但 CPA、已实现极低。

安装 <90 天新用户（同方法 · 90d 窗口）

新用户高价值层人均 $19.64 > 老用户 $16.86——新进高价值更值钱（高意图）。高价值贡献新用户 71% 价值。获客时优先锁定上述市场/旅游/品牌鞋服类目。

⑫ 每层用户案例 + 可执行优先级

服务使用 × 同行 交叉出的可执行优先级

⑬ 应用：AT 测券失败 → 商家专属券补贴（选品 / 券面 / 成本 ROI）

机制：用户 AT 自动测券失败（找不到可用券）时，发一张包装成"Coupert 该商家专属券 满X减Y"的补贴——本质是固定金额直接到用户账户（不走 CB、不按 CB 费率）。24h 有效；需经 Coupert 在该商家完成 SP 下单；订单确认后 3 天补贴到账余额；订单取消则券收回。只补高价值用户。

① 补贴人群 + B 计算逻辑

人群 = 高价值 B≥$5（人均 $16.86/90d）∩ 运行时在白名单商家 AT 测券失败。B = 用户每次 SP 抓到的 OV × 该商家成单 CR × 该商家成单率(orate)，对所有 SP 求和（CPA 商家直接用 cp_commission）。A/B 人群包 = 全部高价值用户（B≥$5 ≈ 14.4 万），五五分每臂 7 万+ 充足；触发量靠测试期内 AT 失败累积。

② 用户量级口径（⚠️ 易高估）

铁律：成本测算用精准口径（就在该商家失败 = 出券基数），别用松口径（会把预算高估一倍）。

③ 券面计算逻辑（2 条线，取大）+ 指标定义

满减门槛 X = 中位客单 ×1.05 → 上浮到整数档（"冲一冲能凑到"）。补贴面额 Y 取两条线的大者，封 $2–$15：

• 线1（吸引力）：Y = 门槛的 10%，封顶 $5（小单别给太多）。
• 线2（自负盈亏多给）：当该单佣金 ≥ $6，Y = round(0.95×该单佣金)（只留 5% 利润），封顶 $15。

3 案例：aliexpress 中位$14→满$20，CR4.3%→佣金$0.86，线1=$2、线2不触发→满$20减$2（10%，LTV）；woolworths $176→满$200，CR4.3%→佣金$8.7，线2=0.95×8.7≈$8→满$200减$8（自负，65%提现下单笔正）；agoda $228→满$250，CR5.0%→佣金$12.4，线2=0.95×12.4≈$12→满$250减$12（自负，单券ROI +59%）。

④ 成本 / ROI / 回本（提现率 65% 实际值）

口径：10 家 · 1 人 1 券（去冷却期）· 出券基数 = 去重触发用户 16,837（B≥$5 真口径；DMP 近似 14,940 偏低；纯 AT 失败不限价值 = 75,478）· 领取率45%×下单率30% = 转化13.5% · 提现率65%(实测)。计算公式见下方 ⑦ ROI 计算逻辑。

领券限制：1 个用户只能领 1 张（去冷却期；按用户首次 AT 失败的商家发券）→ 出券基数 = 去重触发用户 16,837，无重复放大。提现率 65% 即时 −11.5%、50% 转正 +15.1%。每唤回 1 人净亏 ~$0.24、~1.5 天复购佣金补平。每日领取上限 500 张/天（全局；日均新触发 ~561、领取 ~250，500 给 ~2× 余量，后台可调）。

现金为正5家(woolworths/macys/sephora/debenhams/asos) 单笔正、子集 ROI +50.0%；现金为负5家靠 LTV、子集 −32.2%。合计即时 −11.5%，每唤回 1 人净亏仅 $0.24，高价值 $4.59/月 → ~1.5 天回本（前提订单为增量）。

⑤ 补贴区间 · 用户量级 + 占比（10 家 · 1 人 1 券 = 16,837）

68.8% 用户在减$2/$3（小客单 LTV，aliexpress 主导）；大客单自负盈亏档（减$8 = woolworths）仅 6.4% 用户但每单盈利。合计 16,837 = 10 家 · 1 人 1 券去重触发用户（B≥$5）。

⑥ 查询 SQL

-- 1) 补贴人群包：高价值 B≥$5（基于 SP OV 预估真实人均价值）
WITH dstat AS (
  SELECT domain, SUM(cp_commission)/NULLIF(SUM(cp_sales),0) cr,
    SUM(CASE WHEN cp_sales>0 THEN 1 ELSE 0 END)*1.0/COUNT(*) orate,
    CASE WHEN SUM(cp_commission)/NULLIF(SUM(cp_sales),0)>1 THEN 1 ELSE 0 END is_cpa
  FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di
  WHERE pt BETWEEN '2026-03-18' AND '2026-06-16' AND aff_id>0 AND cps_score>40 GROUP BY domain),
uval AS (
  SELECT j.guid, SUM(CASE WHEN d.is_cpa=1 THEN j.cp_commission
    ELSE j.order_value*COALESCE(d.cr,0)*COALESCE(d.orate,0) END) AS B
  FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di j
  LEFT JOIN dstat d ON j.domain=d.domain
  WHERE j.pt BETWEEN '2026-03-18' AND '2026-06-16' AND j.aff_id>0 AND j.cps_score>40 GROUP BY j.guid)
SELECT guid FROM uval WHERE B>=5;            -- 高价值（≈14.4万）

-- 2) 商家选品：高价值用户集中商家（按高价值 SP 用户数排序）
WITH hv AS (SELECT guid FROM soar_dw.dws_dmp_guid_tag_di WHERE `30d_high_value_flag`=1)
SELECT j.domain, COUNT(DISTINCT j.guid) hv_users,
  ROUND(SUM(j.cp_commission)/NULLIF(SUM(j.cp_sales),0)*100,2) cr_pct,
  ROUND(percentile_approx(CASE WHEN j.order_value>0 THEN j.order_value END,0.5),0) med_cart
FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di j JOIN hv ON j.guid=hv.guid
WHERE j.pt BETWEEN '2026-03-18' AND '2026-06-16' AND j.aff_id>0 AND j.cps_score>40
GROUP BY j.domain ORDER BY hv_users DESC;
-- 再过滤 AT-On(见#3) 且 CR<10(非CPA)，选 6 正 + 8 负 = 14 家（已去 ebay.co.uk）；1 人 1 券=按用户首次 AT 失败商家发券(row_number() over (partition by guid order by data_date) = 1)

-- 3) AT 服务开关核实（须 Popup=On）
SELECT sub_domain, scene, level FROM soar_dw.dim_store_service_switch_new_d
WHERE type='AT' AND platform='pc' AND scene='Popup'
  AND pt=(SELECT MAX(pt) FROM soar_dw.dim_store_service_switch_new_d)
  AND sub_domain IN (<白名单>) GROUP BY 1,2,3;

-- 4) 精准用户量级：高价值 ∩ 就在该商家 AT 测券失败过
WITH hv AS (SELECT guid FROM soar_dw.dws_dmp_guid_tag_di WHERE `30d_high_value_flag`=1)
SELECT t.domain, COUNT(DISTINCT t.guid) u
FROM soar_dw.dws_log_sp_at_test_info_d t JOIN hv ON t.guid=hv.guid
WHERE t.data_date BETWEEN '2026-05-18' AND '2026-06-16'
  AND t.domain IN (<白名单>) AND t.test_cnt>t.test_succ_cnt        -- 在该商家失败过
GROUP BY t.domain;

数据源：journey=`dws_log_sp_user_shopping_journey_detail_di`(OV/CR/orate) · AT成功率=`dws_log_domain_platform_country_testing_kpis_d` · AT失败明细=`dws_log_sp_at_test_info_d` · AT开关=`dim_store_service_switch_new_d` · 高价值标签=`dws_dmp_guid_tag_di.30d_high_value_flag`。提现率 65% 实测；领取率/下单率为假设，需小流量实测校准。

⑦ ROI 计算逻辑（加权出券基数 + 单券 / 聚合 ROI）

提现率按 65%（实测）：券是到账户的，只有被提现的部分才是真现金支出，35% 沉淀不出账、不计成本。领取率45%×下单率30%=转化13.5%（假设，需小流量校准）。

(1) 加权出券基数 N = 该商家「精准 AT 失败用户数」（30 天去重、高价值、就在这家失败）= 这家券能被触发的盘子。把多家 ROI 合成一个数时，每家按 N 占比加权 —— aliexpress N=7,871 权重 35.3%、bambulab N=42 权重 0.19%。聚合 ROI 是按出券量加权，不是 N 家简单平均。

(2) 单券 ROI = 单笔佣金 ÷ (券面Y × 0.65) − 1，其中 单笔佣金 = 门槛X × CR。与领取率/下单率无关（它们同时放大收入和成本，比值里约掉）。

(3) 聚合 ROI = 收益基 ÷ 成本基 − 1；收益基 = Σ(N×佣金)，成本基 = Σ(N×券面) × 0.65。以前20 为例：收益基 $31,398 ÷ 成本基 $44,713 − 1 = −29.8%。

(4) 转化率约分：完整式 收益 = ΣN×13.5%×佣金、成本 = ΣN×13.5%×券面×0.65，算比值时 13.5% 上下约掉 → ROI 只由「佣金 vs 券面×0.65」结构决定。绝对净额才把 13.5% 乘回来：实际出券 = N×13.5%，月净 = (收益基−成本基)×13.5%。

⑧ 聚合 ROI（提现率 65%）：前20 vs 10家（1人1券）

10 家 = 现金为正 5 家 + 现金为负 5 家（逐步去 ebay.co.uk + agoda 机酒 + g2a/kinguin/eldorado 游戏点卡）。出券量按 1 人 1 券：每用户只发 1 张、归到首次 AT 失败的商家；出券基数 = B≥$5 真口径去重触发 16,837（下表 N = 各家分得用户数，按 DMP 分布缩放到真口径）。

读数：10 家合计 −11.5%，比 14 家（−13.1%）、前20（−29.8%）进一步改善——逐步剔除 ebay.co.uk + agoda + 游戏点卡（深亏/易薅）。日均亏仅 −$18；提现率若为 50% 整体转正 +15.1%。5 家正 ROI 占出券 12.3%（2,077/16,837），大盘仍由 aliexpress(8,638) 主导。AB 数据量充足：30d 触发 16,837 → 每臂 ~8,418，可检出 order rate +1.5pp。

⑤ SQL（核心）

WITH dstat AS (   -- 每 domain：CR / 成单率 / CPA 标记（CR>1）；不剔除
  SELECT domain,
    SUM(cp_commission)/NULLIF(SUM(cp_sales),0) AS cr,
    SUM(CASE WHEN cp_sales>0 THEN 1 ELSE 0 END)*1.0/COUNT(*) AS orate,
    CASE WHEN SUM(cp_commission)/NULLIF(SUM(cp_sales),0) > 1 THEN 1 ELSE 0 END AS is_cpa
  FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di
  WHERE pt BETWEEN '2026-03-18' AND '2026-06-16' AND aff_id>0 AND cps_score>40
  GROUP BY domain
),
uval AS (         -- 每用户三类价值；CPA 直接用 cp_commission
  SELECT j.guid,
    SUM(CASE WHEN d.is_cpa=1 THEN j.cp_commission
             ELSE j.order_value*COALESCE(d.cr,0) END)                       AS est,
    SUM(CASE WHEN d.is_cpa=1 THEN j.cp_commission
             ELSE j.order_value*COALESCE(d.cr,0)*COALESCE(d.orate,0) END)   AS modeled,
    SUM(j.cp_commission)                                                    AS realized
  FROM soar_dw.dws_log_sp_user_shopping_journey_detail_di j
  JOIN soar_dw.dws_dmp_guid_tag_di g ON j.guid=g.guid
  LEFT JOIN dstat d ON j.domain=d.domain
  WHERE j.pt BETWEEN '2026-03-18' AND '2026-06-16'
    AND j.aff_id>0 AND j.cps_score>40
    AND g.first_install_time <= '2026-03-18' AND g.first_install_time IS NOT NULL
  GROUP BY j.guid
)
SELECT metric, bucket, COUNT(*) users, ROUND(SUM(v),0) total_val
FROM (
  SELECT guid,'A_预估' metric, est v, /* CASE 分桶 */ ... FROM uval
  UNION ALL SELECT guid,'B_真实估算', modeled, ... FROM uval
  UNION ALL SELECT guid,'C_已实现',  realized, ... FROM uval
) z GROUP BY metric,bucket ORDER BY metric,bucket;
-- 明细核对：把样例 guid 回 join dstat，逐事件输出 OV/CR/is_cpa/orate/cp_commission 与 est/modeled。

⑥ CPA / 缺销售额商家（CR>1，不剔除、直接用 commission）

CR>1 的商家分两类，OV×CR 对它们无效，统一改用每笔订单的 cp_commission：

• 真 CPA / lead 型（固定 bounty）：directv(110×) · accountable.eu(92×) · vodafone.co.uk(30×) · o2online.de(23×) · contabo · ionos · hostgator · ccleaner · networksolutions · spokeo · trivago … 销售额是 $1 占位、佣金是固定 bounty。
• 缺销售额型（Type A）：amazon 系（cr≈19.8）· amazon.co.uk(14.5) · att.com(23.7) 等——佣金是真实 % 但 sales 字段缺失，CR 失真。

这两类用 cp_commission 直算后，其价值=实际到手佣金（很多 SP 事件 cp_comm=0）。影响：amazon 等高 OV 商家在"预估"里几乎不计分（因无有效 CR），其价值只体现在"已实现"。

⑦ 口径与数据质量（已校验）

• 一次 SP 一个 OV：journey 一个 sp_id 严格一行（重复=0），order_value=完成时刻 OV，无 SP 内重复。
• order_value 已 USD 归一：实测 eufy £198.99→$271.30、temu €13.27→$15.65；本地币原值在 original_subtotal。
• 覆盖率：order_value ~66–82%；cp_sales（成单）~5%（sp 级成单率 10.3%）。
• 预估是上限不是真实（旅游商家成单率仅 0.2% 是典型）；真实价值看 C/B。
• 用户合计 1,513,324（同一批人，三口径分别落桶，不可跨"区间"当独立群）。