如何用SQL创建月度留存Cohort？附分析需求与数据集

一、Cohort留存统计SQL代码 #

针对新手做了详细注释，直接在BigQuery控制台运行即可：

-- 步骤1：获取2022年首次完成订单的用户及对应的Cohort月份（首次购买年月）
WITH user_cohorts AS (
  SELECT
    user_id,
    -- 将首次购买日期截断到月份，作为Cohort的唯一标识
    DATE_TRUNC(MIN(created_at), MONTH) AS cohort_month
  FROM
    `bigquery-public-data.thelook_ecommerce.orders`
  WHERE
    EXTRACT(YEAR FROM created_at) = 2022
    AND status = 'Complete' -- 排除取消/退款订单，确保数据准确性
  GROUP BY
    user_id
),

-- 步骤2：关联用户所有后续完成订单，计算购买日期与Cohort的月份间隔
user_purchase_activity AS (
  SELECT
    uc.user_id,
    uc.cohort_month,
    -- 计算当前购买月份与Cohort月份的差值，范围限定为1-6个月
    DATE_DIFF(DATE_TRUNC(o.created_at, MONTH), uc.cohort_month, MONTH) AS month_offset
  FROM
    user_cohorts uc
  JOIN
    `bigquery-public-data.thelook_ecommerce.orders` o
    ON uc.user_id = o.user_id
  WHERE
    o.status = 'Complete'
    AND DATE_DIFF(DATE_TRUNC(o.created_at, MONTH), uc.cohort_month, MONTH) BETWEEN 1 AND 6
)

-- 步骤3：按Cohort分组，统计各后续月份的留存用户数（去重统计）
SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN month_offset = 1 THEN user_id END) AS 1_month_retention,
  COUNT(DISTINCT CASE WHEN month_offset = 2 THEN user_id END) AS 2_month_retention,
  COUNT(DISTINCT CASE WHEN month_offset = 3 THEN user_id END) AS 3_month_retention,
  COUNT(DISTINCT CASE WHEN month_offset = 4 THEN user_id END) AS 4_month_retention,
  COUNT(DISTINCT CASE WHEN month_offset = 5 THEN user_id END) AS 5_month_retention,
  COUNT(DISTINCT CASE WHEN month_offset = 6 THEN user_id END) AS 6_month_retention
FROM
  user_purchase_activity
GROUP BY
  cohort_month
ORDER BY
  cohort_month;

二、输出格式说明 #

运行后会得到如下结构的结果（示例数据）：

• cohort_month：用户首次购买的月份
• N_month_retention：该Cohort用户在首次购买后第N个月仍有下单的用户数量

三、留存洞察分析 #

1. 普遍趋势：所有Cohort的留存数均随时间递减，这是电商行业的正常现象——用户首次购买后的复购意愿会随时间推移逐渐降低，需要通过运营手段激活沉默用户。
2. 季度差异：年末（Q4）的Cohort通常第1个月留存数更高，原因是黑五、圣诞等购物季带来的用户本身有更强的购物需求，且平台可能配套了复购优惠券等激励措施。
3. 异常波动排查：如果某月份Cohort的留存突然大幅下跌，比如2022年7月，可重点排查：
   • 当月是否出现平台系统故障、物流延误等负面事件
   • 竞品是否推出了针对性的低价促销或拉新活动
   • 平台当月的运营活动是否效果不佳
4. 高价值Cohort复用：对于留存表现突出的Cohort（比如2022年2月），可进一步分析该月用户的特征（年龄段、性别、偏好品类），复制对应的获客渠道和运营策略，提升整体留存。

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