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 测试失败补贴测算（补给谁 · 在哪补 · 补多少 · 多久回本）

背景：用户 AT（自动试券）失败时，发 CB 返利券（满X减Y，到 cashback 余额）挽回。本节用前面的价值分析定**补给谁/在哪补**，并测算**补贴金额、成本、回本**。触发人群 = AT 测试失败用户 ≈ 8,646/天（30d 日均，AT 点击 16,478 − 测试成功 7,832，源自报表 summary_d）。

一、谁是"有价值"用户（补给谁）— 各层真实佣金 run-rate

二、补贴单位经济（在哪补 + 补多少的天花板）

铁律：只在非 CPA 商家发券。一般人群补贴建议 $2/单（约佣金 50%，留 margin 给"非每个领券者都新增"）。

三、高价值用户每单佣金分布 → "补多少"的精算

四、补贴成本测算（补贴 $2 · 领券下单率假设 25%）

领券下单率 25% 是规划假设，是最大未知数，需小流量实测校准。

五、多久回本（两个口径）

① 单笔即回本（保守）：每个补贴成交单净 +$(佣金−补贴)，只要补贴<佣金，campaign 当天正向——前提该单是"新增"。② LTV 回本（把补贴当一次性投入，看用户后续佣金 run-rate）：

六、关键前提与风险

• 增量性是成败核心：补给"本来就会下单"的人是纯成本，只有转化"AT 失败后本会流失"的人才算新增。AT 失败=没拿到券=流失概率高，增量空间天然大，但必须用 holdout/DiD 实测，不能拍脑袋。
• 两个假设待校准：(a) AT 失败用户价值分布借用了大盘 33%，实际可能偏移；(b) 领券下单率 25% 是规划值。建议先「高价值 + 非CPA商家 + 满$100减$3」小流量灰度，实测后放量。
• AT 失败用户购物车更大（前期测算均值 $314），若成立则每单佣金可达 ~$6-8，补贴空间和回本更好，值得单独验证。

数据源：AT 漏斗取自 rpt_sp_..._summary_d（30d 日均）；每单佣金 / 价值分层取自 dws_log_sp_user_shopping_journey_detail_di（90d，install≥90d）。

⑤ 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（同一批人，三口径分别落桶，不可跨"区间"当独立群）。