"次日留存率"分母到底用谁？——留存类指标的口径陷阱

## 变量1: 分母（基准用户群）
- 新增留存: 分母 = 当日首次注册/激活的用户
- 活跃留存: 分母 = 当日所有DAU（含老用户）
- 注册留存: 分母 = 当日注册成功的用户（不一定激活）
 
## 变量2: 分子（什么算留下来）
- 登录留存: 子方有登录行为
- 活跃留存: 子方有有效互动（如时长>30秒、有点击）
- 付费留存: 子方有付费行为
 
## 变量3: 时间窗口
- 自然日: 1月1日的用户在1月2日 00:00-23:59是否活跃
- 24小时: 1月1日 14:00注册的用户在1月2日 14:00之前24h是否活跃
 
## 变量4: 时间口径（区间留存 vs 节点留存）
- 节点留存: 第N天必须活跃才算（更严格，业界默认）
- 区间留存: 第1到第N天任意一天活跃就算（更宽松）

-- 派生指标表：dws_user_retention_summary
CREATE TABLE dws_user_retention_summary (
  cohort_dt   STRING,            -- cohort日（用户的「起始日」）
  user_type   STRING,            -- new/active/registered
  cohort_size BIGINT,            -- 分母（cohort人数）
  -- 不同口径的留存数
  retain_d1_login   BIGINT,
  retain_d1_active  BIGINT,
  retain_d1_pay     BIGINT,
  retain_d7_login   BIGINT,
  retain_d7_active  BIGINT,
  retain_d30_login  BIGINT
) PARTITIONED BY (cohort_dt);
 
-- BI层算具体留存率，根据业务场景选字段
SELECT cohort_dt, retain_d1_active * 1.0 / cohort_size AS retention_rate
FROM dws_user_retention_summary WHERE user_type='new';

方法二：留存矩阵的SQL实现

留存计算的标准做法是"用户行为日历表 + cohort分组"。这是业界通用模板：

-- 1. 用户行为日历（每用户每天一行，记录是否活跃）
WITH user_calendar AS (
  SELECT user_id,
         dt AS active_dt,
         MIN(dt) OVER (PARTITION BY user_id) AS register_dt
  FROM dws_user_action_1d
  WHERE is_active = 1
),
-- 2. 计算每个cohort的留存数
cohort_retention AS (
  SELECT register_dt AS cohort_dt,
         COUNT(DISTINCT user_id) AS cohort_size,
         COUNT(DISTINCT CASE WHEN active_dt = DATE_ADD(register_dt, 1)
                            THEN user_id END) AS retain_d1,
         COUNT(DISTINCT CASE WHEN active_dt = DATE_ADD(register_dt, 7)
                            THEN user_id END) AS retain_d7,
         COUNT(DISTINCT CASE WHEN active_dt = DATE_ADD(register_dt, 30)
                            THEN user_id END) AS retain_d30
  FROM user_calendar GROUP BY register_dt
)
SELECT cohort_dt,
       retain_d1 * 1.0 / cohort_size AS retention_d1,
       retain_d7 * 1.0 / cohort_size AS retention_d7,
       retain_d30 * 1.0 / cohort_size AS retention_d30
FROM cohort_retention ORDER BY cohort_dt;

方法三：滚动留存（业务更关心的版本）

节点留存有个问题——用户第N天没活跃就被算流失，但他可能第N+1天回来了。业务实际更关心"近30天有过活跃"——这就是滚动留存。

-- 滚动30日留存：cohort新增用户中，最近30天内任一天活跃的比例
WITH cohort AS (
  SELECT user_id, register_dt
  FROM dws_user_action_1d WHERE is_new = 1 AND dt = '${cohort_date}'
), latest_active AS (
  SELECT DISTINCT user_id
  FROM dws_user_action_1d
  WHERE dt BETWEEN DATE_ADD('${cohort_date}', 1) AND DATE_ADD('${cohort_date}', 30)
    AND is_active = 1
)
SELECT '${cohort_date}' AS cohort_dt,
       COUNT(c.user_id) AS cohort_size,
       COUNT(l.user_id) AS rolling_retain_d30,
       COUNT(l.user_id) * 1.0 / COUNT(c.user_id) AS rolling_retention_d30
FROM cohort c LEFT JOIN latest_active l USING(user_id);

面试高频追问

追问一：什么是「魔法数字」？跟留存有什么关系？

魔法数字（Magic Number / Aha Moment）是用户在新用户期内做了某个行为后，长期留存率显著拉高。比如Facebook的「7天加10个好友」、Twitter的「关注30个人」。和留存的关系是：找到魔法数字 = 找到留存的杠杆点。在出海硬件品牌做留存归因时，我用决策树+人群分组找到"新用户首日完成2次内容创作"是我们的魔法数字，30日留存能从22%拉到58%。这个分析是Senior级别。

追问二：留存率突然下降10%，怎么排查？

分四步：①先确认是cohort整体下降还是某个分群下降——切渠道、切版本、切设备 ②看是"新用户质量下降"还是"老用户流失加剧"——前者看渠道cac/质量、后者看产品体验/竞品 ③看埋点变化——很多时候是产品上线新版本埋点改了 ④看SQL逻辑——比如DWS表上游字段含义变了。我在元宇宙App遇到过留存陡降25%，最后查到是埋点上报字段从「device_id」改成「device_uuid」，去重维度变了导致cohort算错。这种归因能力面试问起来加分。

追问三：实时计算留存能做吗？

可以但有限制。核心问题是「留存」需要等N天才能算出来——次留要等次日，30留要等30天。所以实时计算的留存只能算"已知cohort的当前留存进度"，比如7日前那批cohort的实时7留进展。具体做法是Flink里维护两个State：①cohort_state（用户首次活跃日）②active_state（用户最近活跃日），上线后业务能看到"当前7留 = 截止此刻活跃用户/cohort人数"，分钟级更新。在元宇宙App用过这套方案给运营做留存监控，挺好用。

实战踩坑：把跨自然日留存当成24h留存导致广告投放优化失败

出海硬件品牌做某次广告归因时，市场部用「次日留存」作为投放质量评估指标。我们用的是自然日口径——T日0:00-23:59注册的用户在T+1日0:00-23:59是否活跃。结果广告团队发现"晚上8点后投放的次留低很多"，疯狂砍夜间预算。后来才发现：晚上8点后注册的用户，次日的「自然日」窗口只剩4小时（凌晨0:00之后），自然次留低不是用户质量差，是窗口短。

教训：做广告归因时必须用24小时窗口而不是自然日。我们专门给市场部做了一份「24h窗口版次留」，发现夜间用户次留其实和白天差不多。市场部恢复夜间投放后CAC降了18%。这是口径选择直接影响业务决策的典型case。

总结

回答这道题的高分结构：先点出问题（同一个次留可有5种口径）→ 列出4个变量（分母/分子/时间/口径）→ 给治理方案（命名+独立字段+魔法数字分析）→ 最后讲业务影响（24h窗口救回投放）。能聊到Cohort分析、魔法数字、滚动留存就是Senior，能讲到实时留存计算和广告归因的口径影响就是Staff。

下一篇聊跨业务线场景：多业务线都要"订单金额"，但定义不一样——多租户/多业务的指标怎么统一管理？

你们公司次留分母用谁？魔法数字找到了吗？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长