留存需要哪两张表
留存率是新用户在首次行为后的第 N 天仍然活跃的比例，计算需要两张基础表：首日集合表记录每个用户的首次行为日，活跃明细表按日记录活跃用户。关键约束是活跃定义必须统一，登录、下单、浏览三种定义算出的留存差异很大；首日集合也不应被追数改写，否则历史留存曲线会整体位移，看起来像业务发生了突变。
基本信息
| 首日集合 | 首次行为日，形成 cohort |
|---|---|
| 活跃明细 | 每日活跃用户，可带活跃类型 |
| 口径 | 活跃行为定义与去重键 |
| 观察点 | 次日、7 日、30 日留存 |
| 稳定性 | 首日集合不应被追数改写 |
典型使用场景
- 评估获客质量：不同渠道拉来的用户次日留存差异明显
- 功能上线后对比前后 cohort 的留存曲线，衡量改动效果
- 标记低质量渠道：留存长期低于基准的渠道需要复核投放
- 补数后重算 cohort，确认历史曲线是否发生整体位移
- 统一各业务线的活跃定义，让留存数字跨团队可比
- 定位留存断点：找出用户在第几天开始集中流失，指导召回策略
- 首日行为与渠道交叉，看哪种首日行为组合带来更高的长期留存
次日留存
次日留存
-- 首日集合左连次日活跃，分别统计分子分母
WITH first_day AS (
SELECT user_id, min(dt) AS cohort_dt
FROM dwd_active_user
GROUP BY user_id
)
SELECT f.cohort_dt,
count(DISTINCT f.user_id) AS cohort_uv,
count(DISTINCT a.user_id) AS d1_uv,
count(DISTINCT a.user_id) / count(DISTINCT f.user_id) AS d1_retention
FROM first_day f
LEFT JOIN dwd_active_user a
ON a.user_id = f.user_id AND a.dt = date_add(f.cohort_dt, 1)
GROUP BY f.cohort_dt;结果读法
| 要素 | 含义 | 异常信号 |
|---|---|---|
| cohort_dt | 用户首次活跃日 | 整体位移说明首日集合被改写 |
| cohort_uv | 首日新增用户数 | 与注册数差异大说明活跃定义过松 |
| d1_uv | 次日仍活跃用户数 | 断崖变化提示埋点或任务异常 |
| 留存率 | 次日活跃除以首日集合 | 超过百分之百说明关联存在重复 |
活跃定义不同会让留存差异显著：跨团队对比前先对齐定义，并把定义写进指标卡，避免各说各话、会议上比对不成。
新用户与回流用户要分开统计：把长期未活跃后回来的用户算作新增，会人为抬高留存曲线，掩盖真实的获客质量问题。