SQL计算用户留存率时的常见错误

在开始留存分析之前，我们首先需要明确一个问题，到底什么样的用户，才是我们要分析的新用户？

很多技术和运营人员的第一反应是，注册了账号的用户就是新用户。

这个理解本身没有问题，但放在运营分析里，这个定义还不够严谨。因为在数据库里，一个用户通常不会只有一条数据记录，而是会产生多条行为数据记录，例如：

• 多次登录
• 多次访问
• 多次进行业务操作

这些数据混在一起，使我们无法直接判断用户第一次来到平台的时间。

为了统一分析口径，我们先对“活跃行为”进行定义：

• 注册＝用户完成首次活跃行为
• 登录＝用户完成后续活跃行为

因此，我们把“用户第一次注册的时间”，定义为该用户留存分析的起点。

在实际业务中，我们需要的数据往往不会集中存储在单张数据表里，为了提升数据库性能与存储效率，开发人员通常会采用分表设计，将数据分散存储在两张或多张关联数据表中。在本案例中，我们将通过以下两张数据表，完成次日留存分析。

1、用户注册表

表1-1 用户注册表(reg)

这张表告诉我们：

• user_id：用户是谁？（用户ID）
• register_time：用户什么时候来的（注册时间）
• Channel：用户从哪里来的（注册渠道）

2、用户行为表

表1-2 用户行为表(act)

字段说明：

• user_id：用户是谁？（用户ID）
• login_time：用户什么时候回来过（登录时间）

需要注意的是，这两张表都有一个共同的字段，就是user_id，后面我们可以通过它，将两张表连接(JOIN)起来，从而得到完整的数据字段。

在动手写SQL之前，我们先理清次日留存的计算逻辑：

1. 从注册表(reg)中，筛选出某一天的所有新增注册用户
2. 从行为表(act)中，判断这些用户在注册次日是否有登录行为
3. 用“次日登录用户数”/“当天新增注册用户数”，得到次日留存率

继续延申一下，7日留存的计算逻辑：

1. 从注册表(reg)中，筛选出某一天的所有新增注册用户
2. 从行为表(act)中，判断这些用户在注册后第七天是否有登录行为
3. 用“第七天登录用户数”/“当天新增注册用户数”，得到七日留存率

这里有一个常见的误区，很多人会把“7日留存率”和“7日内留存率”混淆，认为二者是同一个指标，这是非常错误的。

我们可以通过一个简单的类比来理解它们的不同。

7日留存率，是衡量用户是否形成了较为稳定的行为，它考量的是用户在第7天当天是否活跃。这就好比一个学校，每周五都会定期进行测验考试，周五就是一个固定的考试日子。

7日内活跃率（7日内留存率），衡量的是新用户初期的活跃情况，只要一周内有过登录行为就算，它衡量的是新注册用户池的初始质量。比如出勤率，就是看一段时间内的出勤次数。

而7日留存率的数值一定小于或等于7日内活跃率。

举例来说，有100个新注册用户，其中50人在7天内有过登录行为，但只有10个人在第7天当天有登录行为，则：

• 7日内活跃率 ＝ 50/100 ＝ 50%
• 7日留存率 = 10/100 ＝ 10%

如果用50％的7日内留存率去对标行业7日留存率，会得出乐观且致命的错误结论，严重影响整体运营决策。

因此，几乎所有的行业报告、数据分析平台和专业运营团队，提到“N日留存”时，都严格指“第N日当日”的活跃情况。只有这样，才能与行业标准进行有意义的对比，也才能绘制出标准的用户留存曲线。