SQL Server电商客户留存分析：计算首购日期与月间隔

需求说明 #

需生成包含以下字段的数据集用于计算客户留存率：

• customer_id（数据库原有字段）
• order_purchase_timestamp（数据库原有字段）
• first_purchase（计算字段：客户首单日期，仅保留年月格式，如2016-09）
• age_by_month（计算字段：首单年月到当前订单日期的月份差值，首单当月为0）

额外规则：

• 同一客户同月的多笔订单仅保留一条记录
• 数据范围限定为2016-10-01至2018-09-30
• 结果按order_purchase_timestamp排序

示例输入输出 #

输入数据 #

输出结果 #

原有按季度处理的SQL #

SELECT 
    customer_id, order_purchase_timestamp
FROM
    orders
WHERE  
    (order_purchase_timestamp BETWEEN '2016-10-01' AND '2016-12-31') 
    OR (order_purchase_timestamp BETWEEN '2017-01-01' AND '2017-03-31') 
    OR (order_purchase_timestamp BETWEEN '2017-04-01' AND '2017-06-30') 
    OR (order_purchase_timestamp BETWEEN '2017-07-01' AND '2017-09-30') 
    OR (order_purchase_timestamp BETWEEN '2017-10-01' AND '2017-12-31') 
    OR (order_purchase_timestamp BETWEEN '2018-01-01' AND '2018-03-31') 
    OR (order_purchase_timestamp BETWEEN '2018-04-01' AND '2018-06-30') 
    OR (order_purchase_timestamp BETWEEN '2018-07-01' AND '2018-09-30')
ORDER BY 
    order_purchase_timestamp

改造后的按月统计SQL #

WITH CustomerFirstPurchase AS (
    -- 计算每个客户的首单日期及年月格式
    SELECT 
        customer_id,
        FORMAT(MIN(order_purchase_timestamp), 'yyyy-MM') AS first_purchase,
        MIN(order_purchase_timestamp) AS first_purchase_dt
    FROM orders
    GROUP BY customer_id
),
CustomerMonthlyOrders AS (
    -- 去重：同一客户同月仅保留最早的订单记录
    SELECT 
        customer_id,
        MIN(order_purchase_timestamp) AS order_purchase_timestamp
    FROM orders
    WHERE order_purchase_timestamp BETWEEN '2016-10-01' AND '2018-09-30'
    GROUP BY customer_id, FORMAT(order_purchase_timestamp, 'yyyy-MM')
)
SELECT 
    cmo.customer_id,
    cfp.first_purchase,
    -- 计算首单到当前订单的月份差值
    DATEDIFF(MONTH, cfp.first_purchase_dt, cmo.order_purchase_timestamp) AS age_by_month,
    cmo.order_purchase_timestamp
FROM CustomerMonthlyOrders cmo
JOIN CustomerFirstPurchase cfp ON cmo.customer_id = cfp.customer_id
ORDER BY cmo.order_purchase_timestamp;

关键改造说明 #

1. 首单计算：通过CustomerFirstPurchase CTE获取每个客户的首单日期，并用FORMAT函数转换为年月格式
2. 订单去重：CustomerMonthlyOrders CTE按客户+订单年月分组，保留当月最早的订单，避免同一客户同月多条记录
3. 月份差值计算：使用DATEDIFF(MONTH, ...)直接计算首单到当前订单的月数差，精准满足age_by_month需求
4. 简化日期筛选：将原有的多个季度区间合并为一个BETWEEN条件，代码更简洁

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