用队列留存回答客户会不会回来

复购次数告诉我们客户买了几次，却没有把不同首次购买时间的客户放在同一起点。队列留存按首次购买月份分组，再观察首购后的第 1、2、3 个月是否继续购买。

找到每位客户的首购月份

SELECT
    customer_id,
    MIN(order_month) AS cohort_month
FROM v_sales_detail
GROUP BY customer_id
ORDER BY cohort_month, customer_id;

cohort_month 是客户进入队列的时间。这里按首次完成订单划分，而不是注册时间，因为课程要分析购买后的留存。

队列分析解决的是“起点不一致”问题。假设甲在 1 月首购、3 月复购，乙在 2 月首购、3 月复购；按自然月看，两人都出现在 3 月，但按队列月龄看，甲是 M2，乙是 M1。只有把每个人的首次购买设为 M0，不同批次的复购节奏才可比较。

计算活动月份与首购月份的距离

SQLite 没有直接计算月份差的函数，可以把年份差换成月数，再加上月份差：

WITH first_month AS (
    SELECT
        customer_id,
        MIN(order_month) AS cohort_month
    FROM v_sales_detail
    GROUP BY customer_id
), activity AS (
    SELECT DISTINCT
        customer_id,
        order_month AS active_month
    FROM v_sales_detail
)
SELECT
    f.customer_id,
    f.cohort_month,
    a.active_month,
    (
        CAST(substr(a.active_month, 1, 4) AS INTEGER)
        - CAST(substr(f.cohort_month, 1, 4) AS INTEGER)
    ) * 12
    + CAST(substr(a.active_month, 6, 2) AS INTEGER)
    - CAST(substr(f.cohort_month, 6, 2) AS INTEGER)
      AS month_number
FROM first_month AS f
JOIN activity AS a USING (customer_id)
ORDER BY f.customer_id, a.active_month;

首购当月的 month_number 是 0，下一个月是 1。activity 使用 DISTINCT，同一客户一个月买多次仍只算一位活跃客户。

月份差不能用“日期相减再除以 30”。1 月 31 日到 2 月 28 日不到 30 天，却已经跨了一个自然月；队列分析关心的是月桶，不是精确天数。公式先把年份差换成月数，再加月份差。例如 2024-11 到 2025-02：

若查询出现负的 month_number，说明活动日期早于被认定的首购日期，通常是过滤条件不一致或时间字段异常。

计算队列人数和留存率

WITH first_month AS (
    SELECT customer_id, MIN(order_month) AS cohort_month
    FROM v_sales_detail
    GROUP BY customer_id
), activity AS (
    SELECT DISTINCT customer_id, order_month AS active_month
    FROM v_sales_detail
), cohort_activity AS (
    SELECT
        f.cohort_month,
        (
            CAST(substr(a.active_month, 1, 4) AS

2024-01 队列有 28 位首购客户，第 1 月有 2 位回来，留存率是 7.14%；第 5 月有 18 位活跃，留存率是 64.29%。本项目是规则生成的数据，所以周期会比较明显；真实业务应继续检查促销、订阅周期和数据缺口。

这里统计的是“月度回访留存”：客户只要在某个月龄发生过一次完成购买，就算该月活跃。它不是生存分析意义上的连续存活，所以 M2 为 0 的客户仍可能在 M5 回来，曲线也不要求单调下降。解释热力图时必须把这个定义写清楚。

先做四条不变量检查：

• 每个队列的 M0 留存率必须是 100%。
• retained_customers 不能大于 cohort_customers。
• 留存率必须处在 0 到 1 之间。
• 所有队列人数之和必须等于 120 位客户。

如果 M0 小于 100%，常见原因是首购表包含一套订单口径，而活动表用了另一套。本查询既在活动表去重，又在聚合时使用 COUNT(DISTINCT a.customer_id)，重复明细不会增加留存人数；如果别的实现改用 COUNT(*)，忘记按“客户 + 月份”去重才可能把人数放大。出现人数超过队列规模时，还要检查队列连接和分母是否使用了同一批客户。

正确看待空白和零

如果某个队列在第 2 月无人购买，当前查询不会生成一行。用于 Excel 热力图前，通常需要一张 0 到 6 的月份序列表，与留存结果左连接，再把缺失留存人数填成 0。

“没有生成记录”和“生成记录且人数为 0”在展示上可以相同，但在数据处理上不是同一件事。补齐周期时要保留这种区别，避免误以为查询已经覆盖所有组合。

还要区分“已经观察到但无人回来”和“数据还没走到那个时间”。假设数据只到 2025-06，2025-05 队列的 M2 尚未发生，应该留空而不是填 0；0 会把未知的未来误判为流失。本项目用于 M0–M6 热力图的队列都已经拥有完整六个月观察窗，所以可以比较同样宽度的区域。

把长表转成 Excel 热力图

SQL 输出采用长表结构：一行是一个队列与一个月龄。Excel 数据透视表可以把 cohort_month 放到行、month_number 放到列、retention_rate 放到值区域，再使用条件格式色阶。值区域要确认汇总方式是“求和”而不是“计数”：每个队列与月龄只有一条比例记录，求和才会保留这个比例。不要用总计行解释整体留存，不同队列的分母并不相同。若数据含尚未观察的未来月份，优先使用下面保留 NULL 的宽表，并实际检查空白导入结果；不要用“空单元格显示为 0”把未来涂成红色。

若希望直接导出宽表，也可以用条件聚合把月龄转成列。下面的查询可以直接执行：

WITH first_month AS (
    SELECT customer_id, MIN(order_month) AS cohort_month
    FROM v_sales_detail
    GROUP BY customer_id
), activity AS (
    SELECT DISTINCT customer_id, order_month AS active_month
    FROM v_sales_detail
), cohort_activity AS (
    SELECT
        f.cohort_month,
        (
            CAST(substr(a.active_month, 1, 4) AS

month_numbers 列出 M0–M6，grid 把每个队列与这些月龄配成完整网格。target_month 是该单元格对应的自然月；这里约定数据已完整观察到 2025 年 6 月，所以截止月份写成 2025-06-01。目标月已经完整观察却没有活动记录，留存人数才补为 0；目标月超过截止月份时，CASE 返回 NULL，表示尚无法观察。真实报表应把这个截止月份设为已确认的数据参数，不用今天日期代替数据完整性。

MAX(CASE...) 不是在比较留存率大小。每个“队列 + 月龄”现在恰好一行，CASE 只在目标月龄返回一个值，MAX 负责把它提到对应列。本项目八个队列的 M6 都已被完整观察，但没有完成购买，因此是 0%，不是空白。

热力图的第一列应全部是 100%，因为第 0 月就是首购当月。如果不是，先检查队列人数和活动人数是否使用了相同的完成订单口径。

下一节不再增加新的 SQL 分析，而是把这些结果安全地交给 Excel，并处理中文 CSV 最容易出现的编码问题。

换一组数据自己核对

下面的练习使用小型变式。请先按正文的粒度和口径作答，再展开核对。

练习 1：按首购月份对齐月龄

甲 2024-12 首购、2025-02 回来；乙 2025-01 首购、2025-02 回来。两人的 2 月活动分别是哪个月龄？为什么不能都叫 M1？

练习 2：月度回访使用去重人数

一个队列有 4 人，M1 中甲买了三次、乙买了一次，M2 只有丙购买。M1 与 M2 留存率各是多少？能说 M2 活跃者必须在 M1 活跃吗？

练习 3：补齐观察网格区分零和未来

数据已完整到 2025-06：2025-05 队列 M1 无人回来，M2 尚未发生。两个单元格该填什么？把截止改成 2025-05 后，M1 又该怎样显示？