月度新客统计正常但流失客统计异常的SQL查询问题排查

核心逻辑梳理 #

流失客户判定必须满足两个核心条件：

1. 用户最后下单日期早于统计月份的前4/5个月（例：统计2021-06的流失客户，按4个月规则，最后下单日期需≤2021-01；按5个月规则则≤2021-02）
2. 用户在最后下单日期后无任何后续订单
3. 由于数据集首单始于2021-01，2021年1-5月（4个月规则）或1-6月（5个月规则）不可能产生流失客户——没有足够时间窗口满足「满4/5个月无订单」的要求

分步解决代码 #

1. 生成用户维度关键指标 #

先计算每个用户（按生成的唯一ID）的首次/最后下单日期：

WITH user_order_dates AS (
    SELECT
        CONCAT(FirstName, LastName, DOB, Solution) AS user_id,
        Solution AS project_type,
        MIN(OrderDate) AS first_order_date,
        MAX(OrderDate) AS last_order_date
    FROM invoices
    GROUP BY user_id, project_type
),

2. 生成月度统计维度表 #

如果没有现成日期表，用递归生成覆盖所有统计月份：

date_dim AS (
    SELECT DATE '2021-01-01' AS stat_month
    UNION ALL
    SELECT ADD_MONTHS(stat_month, 1)
    FROM date_dim
    WHERE stat_month < CURRENT_DATE
)

3. 月度流失客户统计（以4个月规则为例） #

SELECT
    d.stat_month,
    u.project_type,
    COUNT(DISTINCT u.user_id) AS churned_customers
FROM date_dim d
LEFT JOIN user_order_dates u
    ON u.last_order_date <= ADD_MONTHS(d.stat_month, -4)
    AND u.first_order_date <= ADD_MONTHS(d.stat_month, -4) -- 排除新用户误判
WHERE
    -- 过滤无流失可能的早期月份
    d.stat_month >= ADD_MONTHS((SELECT MIN(first_order_date) FROM user_order_dates), 4)
GROUP BY d.stat_month, u.project_type
ORDER BY d.stat_month, u.project_type;

原有查询错误原因 #

常见问题点：

• 未明确限定统计月份与最后下单日期的时间窗口，误将早期下单用户直接判定为流失
• 未排除首次下单时间过近的用户（如2021-01下单用户，2021-02统计时不满4个月，不应计入）
• 错误以单订单日期而非用户最后下单日期做判定，导致维度误判

新客户+流失客户完整报表 #

如果需要合并两类统计：

WITH user_order_dates AS (
    SELECT
        CONCAT(FirstName, LastName, DOB, Solution) AS user_id,
        Solution AS project_type,
        MIN(OrderDate) AS first_order_date,
        MAX(OrderDate) AS last_order_date
    FROM invoices
    GROUP BY user_id, project_type
),
date_dim AS (
    SELECT DATE '2021-01-01' AS stat_month
    UNION ALL
    SELECT ADD_MONTHS(stat_month, 1)
    FROM date_dim
    WHERE stat_month < CURRENT_DATE
)
SELECT
    d.stat_month,
    u.project_type,
    -- 新客户：首次下单日期落在统计月的用户
    COUNT(DISTINCT CASE WHEN DATE_TRUNC('month', u.first_order_date) = d.stat_month THEN u.user_id END) AS new_customers,
    -- 流失客户：最后下单日期早于统计月前4个月的用户
    COUNT(DISTINCT CASE WHEN u.last_order_date <= ADD_MONTHS(d.stat_month, -4) THEN u.user_id END) AS churned_customers
FROM date_dim d
LEFT JOIN user_order_dates u
    ON DATE_TRUNC('month', u.first_order_date) <= d.stat_month
GROUP BY d.stat_month, u.project_type
ORDER BY d.stat_month, u.project_type;

逻辑验证 #

2021-01至2021-04的统计月份中，churned_customers字段必然为0——最早首单2021-01，满4个月后最早流失统计月为2021-05，完全符合数据集实际情况。

内容的提问来源于stack exchange，提问作者Mr.Cox