留存、漏斗、分群及实验指标

本章目录

本章学习目标

定义留存分母与成熟观察窗
按同会话时间顺序构建漏斗
按实验分配用户汇总指标并识别推断边界

先写指标合同

分析指标先确定实体、时间范围、分子、分母与排除条件。课程注册日来自 users.signup_date，活跃来自 events 中任意事件，按用户日期去重；观察截至二零二六年一月三十一日结束。D7 留存表示注册后第七个自然日有事件，不是七天内任意一次活跃，也不是第七天及以后回访。自然日定义与精确一百六十八小时不同。

分母只能包含已经完整观察到第七日的注册用户，所以注册日最晚一月二十四日。二十五日及以后注册的人尚未成熟，不可当作未留存。按注册月份分群时，一月行实际包含该月成熟部分，报告必须显示 eligible_users。把未成熟用户塞进分母，会让近期 cohort 看起来更差，并制造虚假的增长恶化结论。

用小表演算留存

甲一月一日注册，八日有两事件，D7 分子计一次；乙二日注册，八日有事件但九日没有，D7 不计；丙二十八日注册，即使目前没事件也不能进入 D7 分母。此时成熟分母二、留存分子一、比率二分之一。COUNT 事件会把甲计两次，预去重或 EXISTS 可以避免放大。

示例把用户日期事件去重后连接注册日加七天，并以月为 cohort。空事件日期代表没有当天活动，不等于数据库没有记录这个用户。应核对分子不超过分母，成熟日期符合截止窗，并把 D1、D7、D30 分别用各自成熟范围计算。不同成熟范围直接横向比较需要说明样本差异。

漏斗必须满足同会话顺序

浏览、加购、购买三个行为都出现，并不自动意味着完成漏斗。乙先购买再浏览，没有满足顺序；甲一会话浏览、另一会话购买，也没有完成本章的会话漏斗。课程按 user_id 与 session_id 共同识别会话，先取最早 view，再找其后最早 cart，最后找其后 purchase。时间严格递增，相同时间不推断顺序。

漏斗分母是包含浏览的会话数，不是用户数；同用户两次会话可以贡献两次。示例规定完成必须在一月同一会话内，跨观察窗的行为不计。实际系统若会话标识为空、跨端或跨日，需要先明确身份与会话划分。三个事件各自取最早时间再比较并不总正确：购买后先加购、再浏览、再加购，也可能存在有效后半段链路，因此每一步都要限定在前一步之后。

分群与实验的分母纪律

assignments 一用户一行，A/B 分组在 assigned_at 生效。实验转化率以全部分配用户为分母，仅统计分配后且截止日前的 purchase，未活跃用户也要保留。不能从事件表出发只数活跃用户，否则条件化在处理后行为上，改变实验口径。收入指标同样先算每分配用户收入，再按 variant 求均值。

SQL 展示样本数、转化人数和比例，不凭比例高低就声称显著或因果。随机化完整性、样本比例、观测窗、多个指标和统计不确定性仍需检查。课程合成数据只用于练习，不是业务证据。渠道与国家分群是描述统计，也不能直接解释为渠道导致收入变化，因为人群差异可能混杂。

从结果到验证

阅读一段查询时，先写下一行结果代表什么，再列出来源表的一行代表什么。把过滤、连接、聚合和排序分别检查，比仅凭输出看起来合理更可靠。对于金额应同时保留订单数量和用户数量，以便发现连接引起的重复累计；对于比率应同时展示分子与分母，以便发现筛选范围不同。结果少并不等于正确，结果多也不等于错误，判断依据是预先确定的业务口径。

先用纸笔构造三个用户、两三个订单的小表，再用数据库运行对应查询。至少包含没有订单的用户、同一用户多笔订单、一个未知值，以及筛选后没有记录的情形。用这些反例解释每个子句造成的变化，再扩大到课程数据。未写 ORDER BY 的结果没有稳定顺序；需要核对输出时总要明确排序。课程默认 MySQL 8.4，开启严格分组规则，不要通过关闭规则掩盖数据粒度问题。

完成练习后，先自行解释结果再查看答案。记录的检查标准应是行数、键是否唯一、是否有空值、金额是否守恒，以及日期边界是否符合定义，而不只是查询没有语法错误。答案中的每个条件都应能追溯到题目口径；如果口径改变，先调整定义再改查询。这样形成的推理过程可以直接用于面试中解释思路和实际工作中复核分析报告。

完整示例与实际输出

WITH active_days AS (
  SELECT DISTINCT user_id,DATE(event_time) day FROM events WHERE event_time<'2026-02-01'
)
SELECT DATE_FORMAT(u.signup_date,'%Y-%m') cohort,COUNT(*) eligible_users,
  COUNT(a.user_id) retained_d7,1.0*COUNT(a.user_id)/NULLIF(COUNT(*),0) retention_d7
FROM users u LEFT JOIN active_days a ON a.user_id=u.user_id AND a.day=DATE_ADD(u.signup_date,INTERVAL 7 DAY)
WHERE u.signup_date<='2026-01-24'
GROUP BY DATE_FORMAT(u.signup_date,'%Y-%m') ORDER BY cohort;
WITH e AS (SELECT * FROM events WHERE event_time>='2026-01-01' AND event_time<'2026-02-01'),
v AS (SELECT user_id,session_id,MIN(event_time) viewed FROM e WHERE event_type='view' GROUP BY user_id,session_id),
c AS (SELECT v.user_id,v.session_id,v.viewed,MIN(e.event_time) carted FROM v LEFT JOIN e
  ON e.user_id=v.user_id AND e.session_id=v.session_id AND e.event_type='cart' AND e.event_time>v.viewed
  GROUP BY v.user_id,v.session_id,v.viewed),
p AS (SELECT c.user_id,c.session_id,c.carted,MIN(e.event_time) purchased FROM c LEFT JOIN e
  ON e.user_id=c.user_id AND e.session_id=c.session_id AND e.event_type='purchase' AND e.event_time>c.carted
  GROUP BY c.user_id,c.session_id,c.carted)
SELECT COUNT(*) viewed_sessions,COUNT(carted) carted_sessions,COUNT(purchased) purchased_sessions FROM p;
SELECT a.variant,COUNT(*) assigned_users,
  SUM(EXISTS(SELECT 1 FROM events e WHERE e.user_id=a.user_id AND e.event_type='purchase'
    AND e.event_time>=a.assigned_at AND e.event_time<'2026-02-01')) converters,
  AVG(EXISTS(SELECT 1 FROM events e WHERE e.user_id=a.user_id AND e.event_type='purchase'
    AND e.event_time>=a.assigned_at AND e.event_time<'2026-02-01')) conversion_rate
FROM assignments a WHERE a.assigned_at<'2026-02-01' GROUP BY a.variant ORDER BY a.variant;

实际运行通过Actual MySQL 8.4.11 execution; strict grouping mode.

cohort	eligible_users	retained_d7	retention_d7
2026-01	30	22	0.73333

viewed_sessions	carted_sessions	purchased_sessions
97	64	32

variant	assigned_users	converters	conversion_rate
A	15	5	0.3333
B	15	5	0.3333

不能，还需随机化、样本与统计不确定性检查；合成数据不提供业务因果证据。

两项动手任务

任务 1 · 计算截止一月三十一日的总体 D1 留存。

提示

分母注册日不晚于三十日。

完整参考解答与检查方式

SELECT COUNT(*) eligible_users,SUM(EXISTS(SELECT 1 FROM events e WHERE e.user_id=u.user_id AND DATE(e.event_time)=DATE_ADD(u.signup_date,INTERVAL 1 DAY) AND e.event_time<'2026-02-01')) retained,AVG(EXISTS(SELECT 1 FROM events e WHERE e.user_id=u.user_id AND DATE(e.event_time)=DATE_ADD(u.signup_date,INTERVAL 1 DAY) AND e.event_time<'2026-02-01')) rate FROM users u WHERE u.signup_date<='2026-01-30'; EXISTS 防止重复事件计数。

任务 2 · 按实验组计算分配后每分配用户平均支付净额。

提示

分母保留零订单者。

完整参考解答与检查方式

SELECT a.variant,COUNT(DISTINCT a.user_id) assigned_users,COALESCE(SUM(o.amount-o.refund_amount),0)/NULLIF(COUNT(DISTINCT a.user_id),0) net_per_assigned FROM assignments a LEFT JOIN orders o ON o.user_id=a.user_id AND o.order_date>=a.assigned_at AND o.order_date<'2026-02-01' AND o.status IN ('paid','refunded') WHERE a.assigned_at<'2026-02-01' GROUP BY a.variant ORDER BY a.variant; 这里唯一 assignments 用户用 DISTINCT 防止多订单放大分母，金额来自订单粒度。

官方资料与掌握状态

完成两项任务后，按独立完成程度记录。状态会尽量保存在当前浏览器，可导出备份。

动手任务记录

记录两项任务的方法、检查结果和卡点

掌握状态与复习

关掉资料独立完成后记 A；提示后完成记 B；需要重学记 C。