新增用户按用户首次关键行为时间归因，需用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，而是对齐业务定义：新增以哪个事件为准？留存从哪天开始算？流失的冷静期设几天？这些一旦没对齐，再漂亮的查询结果也是误导。
