SQL 留存分析别把日期口径写成隐形陷阱一、留存看起来简单口径很容易错留存分析是数据团队的高频任务。新增用户次日是否回来活动用户七日后是否继续使用付费用户一个月后是否复购这些问题都离不开留存。但留存 SQL 里最容易埋坑的地方往往不是 join而是日期口径。如果注册日期、活跃日期、自然日、业务日、时区和去重规则没有说清楚同一份数据可以算出几种完全不同的留存。为什么日期口径是留存分析的隐形杀手留存率回访用户数/基准用户数这个公式本身简单到不需要解释。但基准用户是谁、回访发生在哪天这两个定义只要偏一点结果就天差地别。举个例子一个用户 7 月 1 日 23:58 注册7 月 2 日 00:02 打开 App。如果按 UTC 算注册是 7 月 1 日回访也是 7 月 2 日——次日留存Day1命中。但如果你把次日解释为自然日 1注册日 7 月 1 日回访日 7 月 2 日也命中。看起来一致对吧但如果用户 7 月 1 日 23:58 注册7 月 2 日 00:01 打开 App只隔了 3 分钟你算不算次日留存这 3 分钟的回访跟用户回来了完全是两码事但 SQL 不会区分。所以同一个需求写出来的留存 SQL结果差 20% 并不奇怪——差的是口径不是代码。二、先画清楚计算关系flowchart TD A[用户基准 cohort] -- B[基准日期] B -- C[目标观察窗口] C -- D[回访行为] D -- E[留存率]留存的第一步是定义 cohort也就是基准人群。新增留存用注册日做基准活跃留存用活跃日做基准付费留存用首次支付日做基准。基准不同解释完全不同。retention_definition: cohort: first_active_date return_event: app_open day_n: 7 timezone: Asia/Shanghai deduplicate_by: user_id这些配置最好写在指标文档里而不是藏在 SQL 注释里。三、SQL 要显式表达窗口with cohort as ( select user_id, min(date(event_time)) as cohort_date from user_event where event_name app_open group by user_id ), returns as ( select distinct user_id, date(event_time) as active_date from user_event where event_name app_open ) select c.cohort_date, count(distinct c.user_id) as cohort_users, count(distinct r.user_id) as retained_users from cohort c left join returns r on c.user_id r.user_id and r.active_date date_add(c.cohort_date, interval 7 day) group by c.cohort_date;这段 SQL 的重点是把基准人群和回访行为拆开。不要在一个子查询里混着写否则很难检查口径。为什么 CTEWITH拆开写比一个巨大的嵌套子查询好维护留存 SQL 天然有基准人群和回访事件两个独立的数据集它们各自有自己的过滤条件基准可能只要新用户、回访只算 App 打开。写在同一个子查询里一条 WHERE 条件改了基准可能会误伤回访的过滤逻辑。拆成 CTE 的好处是每个 CTE 可以独立验证——先跑 cohort 看基准人群对不对再跑 returns 看回访事件对不对最后 JOIN。修改口径时只改对应的 CTE不影响其他部分。复杂 SQL 的第一原则不是快而是对。四、要处理数据延迟和边界留存指标天然有观察窗口。今天不能完整计算七日留存因为还没到第七天。BI 看板如果不隐藏未成熟日期就会让最近几天的留存看起来异常低。where cohort_date date_sub(current_date, interval 7 day)还要处理时区。跨境产品或多端日志经常用 UTC 存储如果直接date(event_time)可能把本地凌晨的行为算到前一天。更稳妥的做法是先统一转换业务时区再取日期。留存还要说明是否允许同日回访、是否排除作弊用户、是否合并多端账号。尤其是账号体系复杂时一个自然人可能有多个 user_id如果业务解释的是用户行为就要确认是否需要统一身份。最后留存分析要配合样本量。某个 cohort 只有几十个用户留存率从 20% 到 30% 看起来涨了 10 个百分点但实际波动可能没有参考价值。看板上最好同时显示人数和置信区间。样本量不足时留存率波动没有任何统计意义。假设某个 cohort 只有 50 个人其中 10 个人在第 7 天回来了——留存率 20%。下周另一个 cohort 有 50 人5 个人回来——留存率 10%。表面上看留存率腰斩了但实际上 50 人的样本量下20% vs 10% 的差异可能完全由抽样误差造成95% 置信区间能宽到 ±15%。另一个容易被忽略的是未成熟数据——今天是 7 月 4 日7 月 1 日的新增用户还没到第 7 天如果你在看板上展示近 7 日留存7 月 1 日的 7 日留存其实是空的不能算。但很多 BI 工具默认会把 NULL 当成 0 来处理结果近 7 日留存看起来暴跌实际上只是数据没到——这就是为什么WHERE cohort_date date_sub(current_date, interval 7 day)必须加在 SQL 里。踩坑提醒坑1回访日期用了 DATE(event_time) 但没有先转时区— 如果日志是 UTC 存储的北京时间 7 月 1 日 01:00 在 UTC 是 6 月 30 日 17:00。直接用DATE(event_time)会把回访算到前一天留存率偏低。正确做法DATE(CONVERT_TZ(event_time, UTC, Asia/Shanghai))。坑2LEFT JOIN 去重逻辑不一致导致分子虚高— cohort 用COUNT(DISTINCT user_id)去重了returns 也用 DISTINCT 去重了但 JOIN 条件缺少日期比较导致一个用户在多个日期出现结果 retained_users 大于 cohort_users留存率超过 100%明显错误。坑3没有排除当日回访导致次日留存虚高— 注册当天打开 App 叫首日活跃不叫次日留存。如果你的 SQL 没有r.active_date c.cohort_date的限制注册当天的活跃也会被算成次日留存数字会好看但没意义。五、总结SQL 留存分析的难点不是写出一个能跑的 join而是把 cohort、观察窗口、时区、去重和数据成熟度定义清楚。日期口径越显式留存结论越可靠。别让隐形日期陷阱替业务做决定。