SQL数据分析实战场景：用户留存与复购计算

你算过用户留存率吗？我见过太多团队把“用户留存率”算成了“用户活跃率”。一个做电商的朋友，他们产品经理信誓旦旦告诉我，次月留存率37%，但实际业务增长却很慢。我让他把SQL拿出来一看，发现他算的是“当月活跃用户中，次月仍活跃的比例”，而不是“当月新增用户中，次月仍活跃的比例”。这完全是两个概念，前者是活跃率，后者才是留存率。这个错误，让团队在错误的指标上浪费了半年时间。

今天，我将用真实踩过的坑，拆解用户留存与复购计算的SQL实战，让你不仅知道怎么算，更知道为什么这么算，以及算完之后怎么用。

一、核心结论：留存率和复购率的计算，90%的人第一步就错了

1. 留存率的唯一正确口径：以新增用户为分母

这是最核心的结论。留存率必须基于“新增用户”进行计算。你只能问：“这批在1号注册的用户，到7号还有多少人回来？”你不能问：“这个月所有活跃用户，下个月还有多少人回来？”后面这个问题是“用户活跃率”，不是“留存率”。两者的分母完全不同，得出的结论可能完全相反。一个产品如果当月大量拉新，活跃用户数暴涨，但新用户留存极差，用“活跃用户”做分母，留存率可能依然很高，但这掩盖了产品留不住人的真实问题。

2. 复购率的计算口径，决定了你的业务策略

复购率的计算远比想象中复杂。常见的口径有三种：以“人”为单位的复购率、以“订单”为单位的复购率、以及以“交易额”为单位的复购率。它们分别回答不同的问题：用户愿不愿意回来？用户是否持续购买？用户是否越买越多？很多团队把“复购率”等同于“老客占比”，这同样是错误的。老客占比高，可能是因为新客太少，而不是老客复购意愿强。

3. SQL代码必须包含业务逻辑，而非纯技术实现

很多教程只教你怎么写JOIN、怎么写GROUP BY，但忽略了最重要的业务逻辑判断。比如，计算“次月留存”，你需要定义“次月”是“自然月”，还是“用户注册后的第30天”？这两个定义在SQL中的写法完全不同，结果差异也可能很大。我后面会详细拆解这些业务逻辑在SQL中的具体实现。

二、背景与真实场景：为什么你的留存率越算越糊涂

1. 场景再现：一个让你抓狂的真实需求

假设你是一家在线教育公司的数据分析师。老板跑过来说：“帮我算一下，我们上个月新注册的用户，这个月还有多少人在学习？”这个需求听起来很简单，对吧？但当你开始动手时，你会发现一堆问题。“上个月新注册的用户”怎么定义？是注册时间在“2024年1月1日0点”到“2024年1月31日23点59分”之间的所有用户吗？“这个月在学习”怎么定义？是“2024年2月”期间，至少有一次登录行为？

还是“2024年2月”期间，至少有一次课程观看记录？不同的定义，会得出完全不同的数字。

2. 我们真实踩过的坑：数据口径不统一，导致业务部门互相指责

我之前服务过一家零售企业。运营部门说复购率是35%，商品部门说复购率是52%。两边吵得不可开交，都觉得对方的数据有问题。后来我查了一下，发现运营部门算的是“月度复购率”，分母是“上个月有购买行为的用户”，分子是“上个月有购买行为的用户中，这个月再次购买的用户”。而商品部门算的是“品类复购率”，分母是“购买过A品类的用户”，分子是“购买过A品类且再次购买A品类的用户”。

两个口径完全不同，自然得不出统一结论。这个案例让我深刻认识到，在写SQL之前，必须先和业务方确认清晰的计算口径，并形成文档。

3. 为什么不能用Excel来算留存和复购

很多小团队习惯用Excel处理数据。但当用户量超过10万，数据表超过几十万行时，Excel的VLOOKUP和透视表就跑不动了。而且，Excel难以处理复杂的“用户首次行为”和“后续行为”的匹配。更重要的是，Excel的数据是静态的，无法实时更新。而SQL可以做到“一次写好，定时运行”，每次都能得到最新的、准确的数据，而且可以回溯到任意历史时间点。

三、常见误区：90%的SQL教程都忽略的业务细节

1. 误区一：用“用户活跃率”冒充“用户留存率”

这是最普遍、最隐蔽的错误。我见过的最典型的错误SQL写法是：

-- 错误写法：计算的是用户活跃率，而非留存率

SELECT

a.month,

COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id) AS retention_rate

FROM (

SELECT DISTINCT user_id, MONTH(login_date) AS month

FROM user_login_log

WHERE login_date BETWEEN '2024-01-01' AND '2024-01-31'

) a

LEFT JOIN (

SELECT DISTINCT user_id, MONTH(login_date) AS month

FROM user_login_log

WHERE login_date BETWEEN '2024-02-01' AND '2024-02-29'

) b ON a.user_id = b.user_id

GROUP BY a.month;

这段SQL看似合理，但它的分母是“1月份活跃的用户”，而不是“1月份新增的用户”。一个用户可能在1月份之前就注册了，只是1月份恰好登录了，就被算作“1月的活跃用户”。如果这个用户2月份没登录，他就被算作“流失用户”。但真实情况是，他可能已经注册一年了，1月份登录只是偶然行为。用这个指标来衡量产品的新用户留存能力，是无效的。

正确的做法是：必须用“用户注册表”或“用户首次登录时间”作为分母。只有“1月份新注册的用户”，才应该被用来计算1月份这批用户的留存率。

2. 误区二：混淆“首次购买”和“再次购买”的时间窗口

计算复购率时，时间窗口是关键。很多教程会告诉你：“计算每个用户在一个月内的购买次数，如果>=2，就算复购用户。”这个逻辑是错的。理由如下：

• 如果用户A在1月1日和1月31日各买了一次，他被算作“复购用户”，这没问题。
• 如果用户B在1月1日和1月2日各买了一次，他也被算作“复购用户”，这也没问题。
• 但如果你用“月度复购率”来衡量用户忠诚度，用户B明显比用户A更忠诚，但在你的指标里，他们都是“复购用户”。

更科学的做法是：计算“留存复购率”，即“首次购买后，在特定时间窗口内（如30天、60天）再次购买的用户比例”。这个指标能更好地反映产品的早期黏性。

3. 误区三：忽略“沉默用户”对留存率计算的影响

很多SQL教程在计算留存率时，默认所有用户都是活跃的。但现实情况是，很多用户注册后可能就“沉默”了，从未登录过。如果把这些“沉默用户”也纳入分母，会拉低留存率，导致产品团队过早放弃对这批用户的召回。我自己的做法是：在计算留存率时，应该区分“激活用户”和“全部用户”。激活用户是指完成了核心行为（如设置了头像、首次使用了核心功能）的用户。分别计算“全部用户留存率”和“激活用户留存率”，可以看到产品在获取用户质量上的差异。

四、专业判断逻辑：如何设计一个可复用的留存与复购SQL框架

1. 留存率计算的SQL框架：三步法

基于我多年的实战经验，我总结了一个“三步法”来计算留存率，这个框架可以应用到任何用户行为数据上。

第一步：构建用户首次行为表。目的是找到每个用户的生命周期起点。通常以“注册时间”或“首次登录时间”为准。

-- 步骤1：构建用户首次行为表

WITH first_behavior AS (

SELECT

user_id,

MIN(event_time) AS first_event_time,

DATE(MIN(event_time)) AS first_date

FROM user_behavior_log

WHERE event_type = 'register'  -- 或者 'login'，取决于业务定义

GROUP BY user_id

)

第二步：构建用户后续行为表。目的是记录用户在首次行为后的所有活动。

-- 步骤2：构建用户后续行为表

subsequent_behavior AS (

SELECT

l.user_id,

l.event_time,

DATE(l.event_time) AS event_date

FROM user_behavior_log l

INNER JOIN first_behavior f ON l.user_id = f.user_id

WHERE l.event_time > f.first_event_time

AND l.event_type = 'login'  -- 或者 'purchase'，取决于业务定义

)

第三步：计算留存率。通过日期差，统计不同时间窗口的留存用户数。

-- 步骤3：计算留存率

SELECT

f.first_date AS cohort_date,

COUNT(DISTINCT f.user_id) AS total_new_users,

COUNT(DISTINCT CASE WHEN DATEDIFF(s.event_date, f.first_date) = 1 THEN f.user_id END) AS day_1_retained,

COUNT(DISTINCT CASE WHEN DATEDIFF(s.event_date, f.first_date) = 7 THEN f.user_id END) AS day_7_retained,

COUNT(DISTINCT CASE WHEN DATEDIFF(s.event_date, f.first_date) = 30 THEN f.user_id END) AS day_30_retained,

ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(s.event_date, f.first_date) = 1 THEN f.user_id END) / COUNT(DISTINCT f.user_id), 4) AS day_1_retention_rate,

ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(s.event_date, f.first_date) = 7 THEN f.user_id END) / COUNT(DISTINCT f.user_id), 4) AS day_7_retention_rate,

ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(s.event_date, f.first_date) = 30 THEN f.user_id END) / COUNT(DISTINCT f.user_id), 4) AS day_30_retention_rate

FROM first_behavior f

LEFT JOIN subsequent_behavior s ON f.user_id = s.user_id

GROUP BY f.first_date

ORDER BY f.first_date;

这个框架的好处是：逻辑清晰、易于扩展、性能良好。你可以通过修改 DATEDIFF 中的数字，轻松计算任意时间窗口的留存率。也可以将 event_type 替换为其他行为，如“首次购买”、“首次浏览”等，来定义不同的留存率。

2. 复购率计算的SQL框架：两种口径，两种写法

复购率的计算，我通常会提供两种口径，供业务方选择。

口径一：用户复购率（以人为单位）。 回答“有多少用户愿意再次购买”。

-- 口径一：用户复购率

WITH user_purchase_stats AS (

SELECT

user_id,

COUNT(DISTINCT order_id) AS purchase_count,

COUNT(DISTINCT DATE(order_time)) AS purchase_days

FROM orders

WHERE order_status = 'completed'

AND order_time BETWEEN '2024-01-01' AND '2024-01-31'

GROUP BY user_id

)

SELECT

COUNT(DISTINCT user_id) AS total_buyers,

SUM(CASE WHEN purchase_count >= 2 THEN 1 ELSE 0 END) AS repeat_buyers,

ROUND(SUM(CASE WHEN purchase_count >= 2 THEN 1 ELSE 0 END) / COUNT(DISTINCT user_id), 4) AS repeat_purchase_rate

FROM user_purchase_stats;

口径二：订单复购率（以订单为单位）。 回答“有多少订单来自老客”。

-- 口径二：订单复购率

WITH first_purchase AS (

SELECT

user_id,

MIN(order_time) AS first_order_time

FROM orders

WHERE order_status = 'completed'

GROUP BY user_id

)

SELECT

ROUND(SUM(CASE WHEN o.order_time > f.first_order_time THEN 1 ELSE 0 END) / COUNT(DISTINCT o.order_id), 4) AS repeat_purchase_rate

FROM orders o

INNER JOIN first_purchase f ON o.user_id = f.user_id

WHERE o.order_status = 'completed'

AND o.order_time BETWEEN '2024-01-01' AND '2024-01-31';

在实际业务中，我强烈建议同时使用两种口径，并对比分析。如果用户复购率高，但订单复购率低，说明老客数量可观，但每个老客的购买频次还有提升空间。反之，如果用户复购率低，但订单复购率高，说明少数超级用户贡献了大部分订单，需要关注用户拉新和转化。

3. 核心判断逻辑：如何选择正确的留存和复购指标

我总结了一个“业务-指标匹配矩阵”，帮助团队快速选择正确的指标。

这个表格的价值在于，它让团队在做数据分析之前，先明确业务目标，而不是为了算指标而算指标。很多团队的问题不在于SQL写不出来，而在于不知道算这个指标到底要回答什么问题。

五、具体案例与数据观察：从SQL结果到业务行动

1. 案例一：一个在线教育平台的留存率优化

我曾为一个在线教育平台做数据分析咨询。他们发现整体“7日留存率”只有15%，远低于行业平均水平。我要求他们按照“三步法”重新计算，并拆解到不同渠道、不同课程类型。结果发现：

• 来自搜索引擎广告的用户的7日留存率是22%，而来自社交媒体广告的用户的7日留存率只有8%。
• 购买了“编程入门课”的用户，7日留存率是28%；而购买了“英语口语课”的用户，7日留存率只有12%。

这个数据观察直接指导了业务行动：砍掉了效果差的社交媒体广告预算，将资源集中到搜索引擎广告上；同时，重点优化了“英语口语课”的课程内容和互动设计，提升用户体验。优化后，整体7日留存率从15%提升到了25%。

2. 案例二：一个电商平台的复购率分析

另一个案例是一家垂直电商平台。他们发现“用户复购率”在30%左右，但“订单复购率”高达60%。这意味着，虽然只有30%的用户会再次购买，但这30%的用户贡献了60%的订单。进一步分析发现，这些“复购用户”中，有20%是“超级用户”，他们每个月购买4次以上，贡献了40%的营收。这个发现让他们意识到，与其花大量预算去拉新，不如把资源投入到“超级用户”的维护上，比如推出会员制、专属客服、个性化推荐等。

他们针对超级用户做了一次“优先购”活动，结果活动期间，超级用户的客单价提升了30%，月复购率提升了5个百分点。

3. 数据观察：留存率与复购率的“黄金交叉点”

在我服务过的多个行业（电商、教育、内容、SaaS）中，我发现一个普遍规律：当用户的“次日留存率”超过40%，“7日留存率”超过20%时，该产品的“自然增长”会变得非常显著。这意味着，用户会自发地带来新用户，产品黏性足够强，用户流失率会降低。同时，对于电商和SaaS产品，当“用户复购率”超过30%时，产品的长期盈利能力会显著增强，用户的LTV（生命周期价值）会进入快速增长期。

我称之为“黄金交叉点”。这个观察可以帮助团队判断，产品是否已经进入了“健康增长”的轨道。

六、不同情况下的行动建议：基于数据，选择最优策略

1. 情况一：次日留存率低，但7日留存率相对较高

诊断结论：新用户引导流程可能有问题，但产品核心价值被认可。 用户第一天没有理解产品价值，但一旦熬过第一天，就愿意留下来。

行动建议：

• 优化新用户注册后的“引导流程”，缩短用户发现核心价值的时间。
• 增加“第一天”的互动设计，比如强制用户完成一个核心任务（如设置头像、完成一次搜索、加入一个群组）。
• 在用户注册后的24小时内，发送个性化的推荐或引导邮件/消息。

2. 情况二：次日留存率高，但7日留存率断崖式下跌

诊断结论：产品有吸引力，但缺乏持续性黏性。 用户第一天觉得新鲜，但很快发现产品无法满足他们的长期需求。

行动建议：

• 分析用户在第3-7天期间的行为，看看他们是否遇到了“价值天花板”。
• 增加“内容深度”或“功能深度”，让用户有持续探索的空间。比如，在内容产品中，增加长尾内容的推荐；在工具产品中，增加进阶功能。
• 设计“用户成长体系”或“成就系统”，激励用户持续使用。

3. 情况三：用户复购率低，但订单复购率高

诊断结论：存在少数“超级用户”，但大部分用户是“一次性买家”。 产品可能依赖于特定渠道（如大型促销）带来的高质量用户，但缺乏将普通用户转化为老客的能力。

行动建议：

• 分析“超级用户”的特征，比如他们的来源渠道、购买的商品、使用习惯等，然后针对性地进行“普通用户”的转化。
• 推出“首单优惠”后的“复购优惠券”或“会员卡”，降低用户第二次购买的门槛。
• 优化“商品推荐算法”，让用户看到更多他们可能感兴趣的商品。

4. 情况四：用户复购率高，但订单复购率低

诊断结论：很多用户会回来，但每个用户购买次数不多。 产品有不错的黏性，但用户对产品的价值挖掘不够深，或者产品品类不足以支撑频繁购买。

行动建议：

• 分析用户从“首次购买”到“第二次购买”的时间间隔，如果间隔很长，说明产品缺乏“复购场景”。可以考虑通过“定期提醒”、“订阅制”、“季节限定商品”等方式创造复购场景。
• 推出“套餐”或“组合销售”，提高单次购买金额，从而提升客单价。
• 增加“品类宽度”，让用户在同一平台上可以购买更多不同类型的商品。

七、不同情况下的取舍：在资源有限时，如何做出最优决策

1. 取舍一：拉新 vs. 留存，资源有限时先投哪个？

这是所有产品团队都会面临的问题。我的判断逻辑是：如果产品的“次日留存率”低于30%，或者“7日留存率”低于10%，那么所有资源都应该优先投入到留存优化上，而不是拉新。 理由很简单：一个“漏斗”的漏口太大，你往里面倒再多水，都会漏掉。先堵住漏洞，再考虑扩大漏斗。如果一个产品连新用户都留不住，拉新就是在浪费钱。反之，如果留存率已经达到健康水平（如次日留存率超过40%），那么可以适当增加拉新投入，让“滚雪球”效应加速。

2. 取舍二：提升复购率 vs. 提升客单价，哪个更优先？

这个取舍取决于产品的生命周期和商业模式。我的建议是：在产品早期，优先提升复购率；在产品成熟期，优先提升客单价。 为什么呢？因为早期产品，用户基数小，培养用户“回来”的习惯比让他们“花更多钱”更重要。一旦用户养成了复购习惯，你再通过“套餐”、“会员”、“升级”等方式提升客单价，用户会更容易接受。反之，如果早期就逼用户花大钱，会吓跑他们。而到了成熟期，用户基数大，忠诚度高，再通过提升客单价来挖掘存量用户的价值，是更高效的选择。

3. 取舍三：激活用户 vs. 全部用户，哪个指标更能反映产品健康度？

我建议同时关注两个指标，但以“激活用户留存率”作为核心决策依据。因为“全部用户留存率”会被大量“沉默用户”拉低，导致你无法准确判断产品核心功能的好坏。如果你的产品激活率很低（比如低于30%），说明你的产品门槛太高，或者引导流程太差。你应该先优化激活流程，而不是纠结于“留存率”为什么低。当激活率提升到60%以上后，“全部用户留存率”和“激活用户留存率”的差距会缩小，此时“全部用户留存率”才具有参考意义。

八、总结：从SQL代码到业务决策的闭环

用户留存和复购的计算，从来不是简单的SQL技术问题。它是一个“业务理解 → 数据建模 → SQL实现 → 结果解读 → 业务决策”的完整闭环。很多团队的问题在于，他们把SQL当成了终点，以为写出一段能跑出数字的代码就万事大吉了。但真正的价值在于，你能否读懂这些数字背后的业务含义，并驱动团队做出正确的行动。

我给你的建议是：

• 第一步：回去检查你的留存率分母。 看看你的SQL里，分母是“新增用户”还是“活跃用户”。如果是后者，请立刻改正。
• 第二步：和业务方确认复购率的计算口径。 是“用户复购率”还是“订单复购率”？明确口径，避免内部扯皮。
• 第三步：用“三步法”框架，重构你的SQL。 这个框架可以帮你避免很多逻辑错误。
• 第四步：画一张“留存率-复购率”矩阵图。 把你的产品放在这个矩阵里，看看它处于哪个象限，然后采取对应的行动。

最后，我想说，数据是真相的一种表达方式，但真相往往比数据更复杂。不要迷信任何单一指标，也不要停止追问。当你的SQL跑出一个数字时，问自己一句：“这个数字，真的能代表我产品的健康状况吗？” 如果你能持续问自己这个问题，你就已经走在了正确的路上。

同时可以补充一个“人均复购频次”作为辅助维度的解释。如果业务要评估促销活动对复购的影响，才考虑用“订单复购率”来对比活动前后的订单重复比例。具体SQL实现时，用户复购率的口径代码是：SELECT COUNT(DISTINCT user_id) FROM 订单表 WHERE 用户购买次数 >= 2；

免费注册

咨询方案

扫码咨询方案