基于Google BigQuery计算客户流失量的SQL查询需求

需求说明：计算历史有下单记录但当月未下单的流失客户数，输出月份与对应流失数的表格。示例规则：1月的数值指「截至去年12月有下单记录，但1月未下单」的客户数；2月的数值指「截至1月有下单记录，但2月未下单」的客户数。

现有数据表字段：customer_id（客户ID）、delivery_date（下单送达时间，时间戳格式）、order_id（订单ID），仅统计状态为「已送达」的订单。

完整SQL代码 #

WITH customer_monthly_orders AS (
    -- 获取每个客户每个月的有效下单记录（已送达订单）
    SELECT
        DATE_TRUNC(delivery_date, MONTH) AS month,
        customer_id,
        COUNT(DISTINCT order_id) AS orders
    FROM orders
    WHERE LOWER(order_status) = 'delivered'
    GROUP BY 1, 2
),
all_months AS (
    -- 生成覆盖所有订单时间范围的月份序列
    SELECT
        month
    FROM UNNEST(
        GENERATE_DATE_ARRAY(
            (SELECT MIN(month) FROM customer_monthly_orders),
            (SELECT MAX(month) FROM customer_monthly_orders),
            INTERVAL 1 MONTH
        )
    ) AS month
),
customer_monthly_activity AS (
    -- 关联所有月份与客户，标记每月是否有下单
    SELECT
        am.month,
        cmo.customer_id,
        CASE WHEN cmo.orders IS NOT NULL THEN 1 ELSE 0 END AS has_order
    FROM all_months am
    CROSS JOIN (SELECT DISTINCT customer_id FROM customer_monthly_orders) all_customers
    LEFT JOIN customer_monthly_orders cmo
        ON am.month = cmo.month AND all_customers.customer_id = cmo.customer_id
),
customer_churn_flags AS (
    -- 判断客户当月是否流失（历史有下单，当月无下单）
    SELECT
        month,
        customer_id,
        CASE
            WHEN has_order = 0
                AND EXISTS (
                    SELECT 1 FROM customer_monthly_activity cma_prev
                    WHERE cma_prev.customer_id = cma.customer_id
                        AND cma_prev.month < cma.month
                        AND cma_prev.has_order = 1
                )
            THEN 1
            ELSE 0
        END AS is_churned
    FROM customer_monthly_activity cma
)
-- 按月份聚合统计流失客户数
SELECT
    month,
    SUM(is_churned) AS churned_customers_count
FROM customer_churn_flags
GROUP BY month
ORDER BY month;

关键逻辑说明 #

• customer_monthly_orders：筛选已送达订单，按月份+客户分组，得到每个客户每月的下单情况
• all_months：生成完整的月份序列，确保每个时间节点都能被统计
• customer_monthly_activity：为每个客户匹配所有月份，标记当月是否有下单行为
• customer_churn_flags：核心判断逻辑——客户当月无下单，但之前有过下单记录，则标记为流失客户
• 最终按月份聚合，输出各月的流失客户总数

内容的提问来源于stack exchange，提问作者Fahad Hameed