如何基于交易数据通过SQL计算每日新增客户的30天留存率

基础数据说明 #

交易数据表包含以下字段：

• PK_Order_nr：订单号
• PK_Order_item_number：订单内商品条目编号
• Amount：交易金额
• Purchase_date：购买日期
• Customer_ID：客户ID
  单条订单可对应多条商品条目记录，同订单的多条记录属于同一笔消费。

原有代码问题梳理 #

之前的代码存在三个核心问题，导致无法得到正确结果：

1. 没有对同订单的重复行去重：同一订单的多条商品条目会被识别为多笔独立订单，干扰后续下单日期的判断
2. DATE_DIFF参数顺序错误：用首次订单日期减去后续订单日期会得到负数，永远无法满足<=30的判断条件
3. 逻辑仅支持查询单日新客户留存，无法批量输出全量日期的留存率结果，也无法适配留存矩阵的统计需求

修正后可直接运行的SQL代码 #

-- 步骤1：去重客户每日订单，避免同订单多商品条目导致的重复统计
WITH customer_daily_unique_order AS (
    SELECT DISTINCT
        Customer_ID,
        DATE(Purchase_date) AS order_date
    FROM `xxx-xxx.sales_order`
),
-- 步骤2：计算每个客户的首次购买日期，确定新客获取日
customer_first_purchase AS (
    SELECT
        Customer_ID,
        MIN(order_date) AS first_purchase_date
    FROM customer_daily_unique_order
    GROUP BY Customer_ID
),
-- 步骤3：标记每个新客是否在首购后30天内有复购
retention_flag AS (
    SELECT
        cfp.first_purchase_date,
        cfp.Customer_ID,
        MAX(
            CASE WHEN cduo.order_date > cfp.first_purchase_date 
                AND DATE_DIFF(cduo.order_date, cfp.first_purchase_date, DAY) <= 30
            THEN 1 ELSE 0 END
        ) AS is_30d_retention
    FROM customer_first_purchase cfp
    LEFT JOIN customer_daily_unique_order cduo
        ON cfp.Customer_ID = cduo.Customer_ID
    GROUP BY cfp.first_purchase_date, cfp.Customer_ID
)
-- 步骤4：按首购日期汇总，计算每日新客的30天留存率
SELECT
    first_purchase_date AS 新客获取日期,
    COUNT(Customer_ID) AS 当日新客总数,
    SUM(is_30d_retention) AS 30天留存客户数,
    ROUND(SUM(is_30d_retention)/COUNT(Customer_ID)*100, 2) AS 30天留存率(%)
FROM retention_flag
GROUP BY first_purchase_date
ORDER BY first_purchase_date

留存矩阵适配说明 #

如果需要生成按首购日期、后续天数分层的留存矩阵，只需要在第三步中计算DATE_DIFF(cduo.order_date, cfp.first_purchase_date, DAY)得到间隔天数，再做行列转换即可得到目标样式。

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