当前位置：首页 > news >正文

SQL 复购分析：时间窗口写错，结论会完全变样

news 2026/7/6 5:09:46

SQL 复购分析：时间窗口写错，结论会完全变样

一、复购率不是一个简单的 count distinct

复购分析经常用于判断用户粘性。很多 SQL 写法会直接统计“买过两次及以上的用户占比”。这个指标看似简单，实际很容易因为时间窗口写错而失真。用户在统计期前已经购买过，统计期内又买一次，算不算复购？统计期内首次购买，后续 30 天复购，又该归到哪一天？

复购率必须先定义 cohort。按首购日期分组，再观察后续窗口内是否再次购买。没有 cohort，复购分析就会把不同生命周期的用户混在一起。

为什么复购分析最怕"回头客"和"新买家"混在一起算？假设你直接跑了SELECT COUNT(DISTINCT user_id) FROM orders WHERE ... GROUP BY user_id HAVING COUNT(*) >= 2，看看"买过两次及以上"的用户。这个结果里，去年就开始买的老用户和本周才开始买的新用户被无差别对待——但老用户有 365 天积累 2 次购买，新用户只有 5 天。不按 cohort（首购日期）分组的话，复购率天然偏向"早期用户"。更隐蔽的问题是，如果你只统计"买过两次及以上"却没限定时间窗口，那今天注册今天买两次的用户也算复购，但这和"用户过了一周还记得你，又回来买了"完全是两码事。所以复购分析的第一步必须回答："从哪个时间点开始算起？观察多长时间？"

二、先固定首购，再观察窗口内行为

复购分析链路可以拆成首购用户、观察窗口、复购事件和汇总结果。关键是观察窗口相对首购日期，而不是相对报表日期。

flowchart TD A[订单明细] --> B[计算首购日期] B --> C[按首购日建 cohort] A --> D[查找后续订单] C --> E[限定观察窗口] D --> E E --> F[计算复购人数] F --> G[输出复购率]

如果观察窗口不统一，早期 cohort 会有更长观察时间，复购率自然更高。这不是业务变好，而是口径不公平。

为什么观察窗口统一比复购率数字本身更重要？假设你追踪复购率已经做了 6 个月，前 3 个月的 30 天复购率是 25%，后 3 个月降到 19%——老板问"复购率是不是跌了？"在你回答之前，先确认一件事：后 3 个月的首购用户有完整的 30 天观察窗口吗？如果今天是 7 月 5 日，6 月 20 日首购的用户只过了 15 天，还没到 30 天，直接把他们的复购率跟前 3 个月的完整窗口数据比，结论就是错的。很多 BI 看板在这件事上栽跟头——没对"未成熟 cohort"做标记，最近 30 天的复购率看起来暴跌，实际只是因为"窗口还没走完"。这就是为什么WHERE cohort_date <= current_date - 30是复购 SQL 里最重要的一个过滤条件。

三、用窗口函数写清首购和复购

下面 SQL 以 30 天复购为例。实际表名和字段可以替换，但逻辑要保留。

WITH first_order AS ( SELECT user_id, MIN(order_time) AS first_order_time FROM fact_order WHERE pay_status = 'paid' GROUP BY user_id ), repurchase AS ( SELECT f.user_id, DATE(f.first_order_time) AS cohort_date, COUNT(o.order_id) AS repurchase_orders FROM first_order f LEFT JOIN fact_order o ON f.user_id = o.user_id AND o.pay_status = 'paid' AND o.order_time > f.first_order_time AND o.order_time <= f.first_order_time + INTERVAL '30 day' GROUP BY f.user_id, DATE(f.first_order_time) ) SELECT cohort_date, COUNT(*) AS first_buyers, SUM(CASE WHEN repurchase_orders > 0 THEN 1 ELSE 0 END) AS repurchase_users, SUM(CASE WHEN repurchase_orders > 0 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS repurchase_rate FROM repurchase GROUP BY cohort_date;

这个写法把首购和复购窗口放在同一个逻辑里，减少了口径漂移。注意>和<=的边界，少一个等号都可能改变结果。

为什么>和<=的边界如此敏感？回到那个例子：用户 7 月 1 日 10:00 首购，o.order_time > f.first_order_time保证了不把首购本身算成复购——如果用的是>=，同一笔首购订单就会被重复统计，复购人数虚高。< f.first_order_time + INTERVAL '30 day'和<=的区别在 30 天窗口内影响不大（差 1 秒），但如果你的窗口是 7 天，<=和<的区别是"第七天的回访算不算"。这些细节在写 SQL 时写错一个字符，看板上就是另一个结论，但没人知道。把边界条件写进代码注释里，未来接手的人才能理解为什么这么写。

四、复购分析要说明未成熟 cohort

最近 30 天内首购的用户，还没有完整观察窗口。直接展示他们的 30 天复购率，会天然偏低。报表应标记未成熟 cohort，或者只展示已经完整观察的日期。

还要考虑退款和取消订单。复购分析通常只看支付成功订单，但如果后续退款比例高，复购质量也要单独看。可以增加净支付金额、有效订单数等辅助指标。

最后，复购率要和业务动作绑定。只看数字上涨没有意义，还要拆渠道、品类和新老客来源。拆解时仍要保持同一套首购口径，不要每张报表各算各的。

踩坑提醒

坑1：首购日期用了 MIN(order_time) 但没有处理支付状态— 如果订单表里有取消、退款、待支付等多种状态，直接MIN(order_time)可能取到一个"已取消"订单的时间作为首购日期，这个用户被计入 cohort 但实际根本没成交。正确做法：首购 SQL 里必须WHERE pay_status = 'paid'。
坑2：LEFT JOIN 复购订单时没有限制订单时间— 如果JOIN ... ON f.user_id = o.user_id不加时间条件，会把用户所有历史的订单都算成复购，一个 5 年前注册的用户可能有 100 条"复购"记录，这会把复购率拉得离谱地高。必须限制o.order_time > f.first_order_time。
坑3：按日计算复购率时没有足够的样本量就下结论— 某天只有 10 个首购用户，其中 2 个复购了，复购率 20%。下一天 50 首购 5 复购，复购率 10%。这种波动是噪声不是信号。在报表里对首购人数小于 100 的日期标灰或隐藏，避免误导业务决策。