留存率为什么对不上：同期群留存与滚动留存的口径陷阱及一套可复现的 SQL 口径

一、现象与背景

过去一年我在三家团队都撞过同一堵墙：产品说「7 日留存只有 18%」，增长说「我们 7 日留存稳定在 27%」，数据看板上一周前是 21%。三个数都来自同一张用户表、同一个埋点，差异不是噪声，而是口径。

规模上这件事很贵：一个 DAU 约 42 万的工具类产品，留存数字每差 3 个百分点，对应的「活跃用户」估算就差约 1.2 万人，直接影响投放预算分配和季度 OKR 的达成判断。更隐蔽的是，口径错配会让一个真实在变好的指标看起来在变差——这是最危险的情况，因为它会引导团队做出错误的产品决策。

二、原理与根因分析

「7 日留存」这个短语至少对应两种完全不同的计算方式，这是所有混乱的根源：

1）同期群留存（Cohort Retention） 定义：把「首次激活日 = 某一天」的用户聚成一个队列（cohort），看这个队列在第 N 天（相对激活日）是否回访。分子分母都是固定的一个队列。

• 分母：激活日为 D0 的全部新用户
• 分子：这批人在 D0+N 当天活跃的人数

2）滚动留存（Rolling / DAU-based Retention） 定义：看「今天活跃的用户里，有多少人在 N 天前也活跃过」。分母是今天的活跃用户，分子是今天活跃且 N 天前也活跃的交集。

两者的本质差异在于分母不同：

| 维度 | 同期群留存 | 滚动留存 | |---|---|---| | 分母 | 固定队列（某天新用户） | 当前活跃用户 | | 分子 | 队列在 D0+N 的活跃 | 当前活跃 ∩ N 天前活跃 | | 对老用户敏感 | 否 | 是（老用户回访会拉高） | | 对新增速度敏感 | 是 | 否 | | 典型用途 | 评估新用户质量/产品粘性 | 评估整体盘子健康度 |

一个反直觉的机制：当产品新增放缓但老用户粘性上升时，同期群留存会下降（每个新队列本身质量未必变差，但被老用户稀释效应掩盖），而滚动留存会上升。反之，投放拉新猛、新用户质量差时，同期群留存暴跌，滚动留存却可能因为老用户基本盘稳定而看起来还行。这就是为什么「两个数都对，但结论相反」会成为团队内部冲突的常态。

三、具体做法

以下 SQL 基于 DuckDB（本地分析）/ 兼容 BigQuery 的语法，假设有一张 user_events(user_id, event_ts) 和一张 user_first_seen(user_id, first_date)。

同期群 7 日留存（按周聚合）：

WITH cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('week', first_date) AS cohort_week,
    first_date
  FROM user_first_seen
),
active AS (
  SELECT DISTINCT user_id, DATE(event_ts) AS d
  FROM user_events
  WHERE event_name = 'app_open'
),
joined AS (
  SELECT
    c.cohort_week,
    c.user_id,
    DATEDIFF('day', c.first_date, a.d) AS day_offset
  FROM cohort c
  JOIN active a ON c.user_id = a.user_id
)
SELECT
  cohort_week,
  COUNT(DISTINCT CASE WHEN day_offset = 0  THEN user_id END) AS d0,
  COUNT(DISTINCT CASE WHEN day_offset = 7  THEN user_id END) AS d7,
  ROUND(
    COUNT(DISTINCT CASE WHEN day_offset = 7 THEN user_id END) * 1.0 /
    NULLIF(COUNT(DISTINCT CASE WHEN day_offset = 0 THEN user_id END), 0),
    4
  ) AS d7_retention
FROM joined
GROUP BY cohort_week
ORDER BY cohort_week;

滚动 7 日留存（今天活跃用户里 7 天前也活跃的占比）：

WITH act AS (
  SELECT DISTINCT user_id, DATE(event_ts) AS d
  FROM user_events
  WHERE event_name = 'app_open'
)
SELECT
  a.d AS today,
  COUNT(DISTINCT a.user_id) AS dau,
  COUNT(DISTINCT b.user_id) AS retained,
  ROUND(COUNT(DISTINCT b.user_id) * 1.0 / NULLIF(COUNT(DISTINCT a.user_id),0), 4) AS rolling_d7
FROM act a
LEFT JOIN act b
  ON a.user_id = b.user_id
 AND b.d = DATE_ADD(a.d, INTERVAL -7 DAY)
GROUP BY a.d
ORDER BY a.d;

关键配置/规范建议：

1. 看板必须双指标并列，不允许只展示一个「7 日留存」字段。我们团队最终的做法是给每个留存 KPI 加后缀：retention_cohort_d7 与 retention_rolling_d7，字段名即口径。
2. 活跃定义要显式：我们最终把「活跃」收窄为 app_open + 至少 1 次核心业务动作（submit_order / create_doc），排除纯后台心跳事件，否则 DAU 虚高约 14%。
3. 时区固定：所有 DATE(event_ts) 统一转 Asia/Shanghai，避免 UTC 边界日错配。
4. 口径版本化：在数据字典表里为每个指标打 spec_version，任何口径变更必须 bump 版本并通知下游。

四、踩过的坑与反直觉之处

坑 1：把「注册」当「激活」。 我们把 first_date 定义从「注册」改成「完成新手引导」后，同期群 D7 留存从 22% 跳到 31%——分母里那批「注册完就再没打开」的用户本来就不该算进留存队列。这个调整让指标更贴近真实，但也意味着历史数据全部不可比，必须在新旧口径之间留一个过渡期并行跑 3 周。

坑 2：滚动留存在节假日失真。 春节那周，滚动 D7 留存跌到 9%（因为 7 天前是春节，活跃基数极低），但同期群留存只跌到 24%。如果我们只盯滚动指标，会误判产品出问题了。

坑 3：COUNT(DISTINCT) 在大表上被优化器展开。 在 8 亿行事件表上，上面的 SQL 直接跑要 4 分 32 秒；改成先物化 act 临时表（约 6200 万行）再 join，降到 38 秒。这是典型的「写起来一样、跑起来差 7 倍」的坑。

坑 4：跨时区用户的「激活日」漂移。 一批海外用户（约占 6%）的 UTC 日期和本地日期差 1 天，导致他们在两个 cohort 里各出现一次。修复方法是强制所有日期计算走业务时区字段 biz_tz，而不是系统时区。

反直觉点： 同期群留存永远 ≤ 滚动留存（在新增为正的前提下）。如果哪天你的同期群比滚动还高，十有八九是 SQL 里 JOIN 写错了，或者时区没对齐。我们内部把它当成一个免费的自校验断言。

五、结论与可复用清单

1. 不要用一个词描述两种口径。「7 日留存」必须带前缀：cohort_d7 或 rolling_d7。
2. 看板双指标并列，让读者自己选口径，而不是替读者决定。
3. 活跃定义显式化，并把「注册」与「激活」分开，必要时并行跑 2–4 周过渡。
4. 时区、口径、版本三件套：所有指标带 spec_version，变更走评审。
5. 用「同期群 ≤ 滚动」做免费自校验，出现反例先查 SQL 而不是先查业务。
6. 大表先物化活跃表，再 join，性能差可达一个数量级。

指标口径不是技术问题，是沟通问题。把口径写进字段名，等于把歧义消灭在命名层，这比任何文档都管用。