SQL 如何计算新增、留存与流失？

新增用户按用户首次关键行为时间归因，需用MIN(event_time)或ROW_NUMBER()识别各user_id最早行为，而非仅统计某日注册数；次日留存须统一活跃定义并严格时区对齐；流失判定应基于已建立活跃习惯的用户池及业务校准周期。

新增用户怎么算：按首次行为时间切片

新增用户不是看某天登录的人数，而是看「第一次发生关键行为」的用户。比如注册、下单、激活设备等。关键在于识别每个用户的最早行为时间，再按该时间归入对应日期。

• 必须用 MIN(event_time) 或窗口函数 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) 找出每个 user_id 的首行为
• 如果只用 WHERE event_time = '2024-05-01' 统计当天注册数，会漏掉那些注册后没再活跃的“静默新增”
• 常见错误是把「当日新注册用户数」和「当日首次下单用户数」混为一谈——它们属于不同事件类型，需分别定义“新增”的业务口径

select    date(MIN(event_time)) AS first_active_date,   count(DISTINCT user_id) AS new_users FROM events  GROUP BY DATE(MIN(event_time))

次日留存率为什么总不准：时间对齐和去重逻辑最关键

次日留存 = 「第1天活跃且第2天也活跃的用户数」 ÷ 「第1天活跃用户数」。问题常出在：没统一用「活跃」定义（是登录？是访问页面？是完成订单？），或没排除测试账号/机器人流量。

• 分子必须是同一组 user_id 在两个自然日都满足活跃条件，不能简单用 COUNT(*) 汇总
• 推荐用自连接或 LAG() 窗口函数：先按 user_id, DATE(event_time) 去重，再判断相邻日期是否存在
• 注意时区：如果数据是 UTC 时间，但业务看的是北京时间，直接用 DATE(event_time) 会导致跨日错位

WITH daily_active AS (   SELECT DISTINCT user_id, DATE(event_time) AS dt   FROM events    WHERE event_type = 'page_view' ) SELECT    d1.dt AS cohort_date,   COUNT(DISTINCT d1.user_id) AS day1_active,   COUNT(DISTINCT d2.user_id) AS day2_retained,   ROUND(COUNT(DISTINCT d2.user_id) * 1.0 / NULLIF(COUNT(DISTINCT d1.user_id), 0), 3) AS retention_rate FROM daily_active d1 LEFT JOIN daily_active d2    ON d1.user_id = d2.user_id AND d2.dt = DATE_ADD(d1.dt, INTERVAL 1 DAY) GROUP BY d1.dt

流失用户容易被误判：别只看“最近一次行为”

流失 ≠ “今天没来”。典型错误是写 WHERE last_active_time 就标为流失，这会把刚注册还没来得及活跃的新用户也打上标签。

• 流失应基于「预期活跃周期」：比如电商用户平均3天回访，SaaS用户平均7天登录，这个周期要从业务中校准，不能拍脑袋定
• 更稳妥的做法是：先圈定「已建立活跃习惯的用户池」（如过去30天至少活跃5天），再从中筛选「最近N天完全无行为」的用户
• 注意区分「暂时沉默」和「永久流失」：后者通常伴随明确退出信号，如退订邮件、注销账号、关闭推送权限，这些事件比单纯不活跃更有判据价值

用 union ALL 拼留存漏斗时，字段顺序和类型必须严格一致

有人想把新增、7日留存、30日留存合并成一张宽表，用 UNION ALL 堆叠，结果报错或数值错乱。

• UNION ALL 不检查列名，只按位置匹配字段，SELECT 'new' AS metric, cnt FROM t1 和 SELECT cnt, 'ret7' FROM t2 会把 cnt 当 metric，'ret7' 当数值
• 所有子查询必须保证列数、顺序、数据类型一致；推荐显式写出列名并用 CAST 对齐，比如都转成 DECIMAL(5,3)
• 复杂留存分析建议改用 CTE + 条件聚合，比反复 UNION ALL 更易读、更少出错

真正难的不是写 sql，而是对齐业务定义：新增以哪个事件为准？留存从哪天开始算？流失的冷静期设几天？这些一旦没对齐，再漂亮的查询结果也是误导。