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。坑2LEFT 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 的日期标灰或隐藏避免误导业务决策。五、总结SQL 复购分析的重点是 cohort 和观察窗口。先固定首购日期再统计相对窗口内的后续购买未成熟 cohort 要标记或排除。边界条件、退款口径和分组拆解都要写清楚。复购率不是一个孤立数字它只有在口径稳定时才值得拿来做决策。