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

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

找到每位客户的首购月份

sql

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 没有直接计算月份差的函数，可以把年份差换成月数，再加上月份差：

sql

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：

(2025-2024)\times12+(2-11)=3

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

计算队列人数和留存率

sql

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 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,
        COUNT(DISTINCT a.customer_id) AS retained_customers
    FROM first_month AS f
    JOIN activity AS a USING (customer_id)
    GROUP BY f.cohort_month, month_number
), cohort_size AS (
    SELECT
        cohort_month,
        COUNT(*) AS cohort_customers
    FROM first_month
    GROUP BY cohort_month
)
SELECT
    c.cohort_month,
    c.month_number,
    s.cohort_customers,
    c.retained_customers,
    ROUND(
        1.0 * c.retained_customers / s.cohort_customers,
        4
    ) AS retention_rate
FROM cohort_activity AS c
JOIN cohort_size AS s USING (cohort_month)
WHERE c.month_number BETWEEN 0 AND 6
ORDER BY c.cohort_month, c.month_number;

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%，常见原因是首购表包含一套订单口径，而活动表用了另一套；如果留存人数大于队列人数，通常是活动表没有按“客户 + 月份”去重。

正确看待空白和零

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

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

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

把长表转成 Excel 热力图

SQL 输出采用长表结构：一行是一个队列与一个月龄。Excel 数据透视表再把 cohort_month 放到行、month_number 放到列、retention_rate 放到值区域，最后使用条件格式色阶。

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

sql

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 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,
        COUNT(DISTINCT a.customer_id) AS retained_customers
    FROM first_month AS f
    JOIN activity AS a USING (customer_id)
    GROUP BY f.cohort_month, month_number
), cohort_size AS (
    SELECT cohort_month, COUNT(*) AS cohort_customers
    FROM first_month
    GROUP BY cohort_month
), retention AS (
    SELECT
        c.cohort_month,
        c.month_number,
        1.0 * c.retained_customers / s.cohort_customers AS retention_rate
    FROM cohort_activity AS c
    JOIN cohort_size AS s USING (cohort_month)
    WHERE c.month_number BETWEEN 0 AND 6
)
SELECT
    cohort_month,
    ROUND(MAX(CASE WHEN month_number = 0 THEN retention_rate END), 4) AS M0,
    ROUND(MAX(CASE WHEN month_number = 1 THEN retention_rate END), 4) AS M1,
    ROUND(MAX(CASE WHEN month_number = 2 THEN retention_rate END), 4) AS M2,
    ROUND(MAX(CASE WHEN month_number = 3 THEN retention_rate END), 4) AS M3,
    ROUND(MAX(CASE WHEN month_number = 4 THEN retention_rate END), 4) AS M4,
    ROUND(MAX(CASE WHEN month_number = 5 THEN retention_rate END), 4) AS M5,
    ROUND(MAX(CASE WHEN month_number = 6 THEN retention_rate END), 4) AS M6
FROM retention
GROUP BY cohort_month
ORDER BY cohort_month;

MAX(CASE...) 不是在比较留存率大小。每个“队列 + 月龄”最多一行，CASE 只在目标月龄返回一个数值，MAX 负责把这个值提到对应列。这里没有使用 COALESCE(..., 0)，因此尚未出现的月龄仍能保留为空白。

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

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