月度留存率计算问题：新年1月留存为0及修改后留存偏低

初始问题与代码 #

我原本用下面的代码计算月度留存率，其他月份结果大体正确，但核心问题是新年1月的月度留存率显示为0：

WITH monthly_user_counts AS (
  SELECT
    EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', event_date)) AS year,
    EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d', event_date)) AS month,
    user_pseudo_id
  FROM
    `table`
),
returning_users AS (
  SELECT
    curr.year,
    curr.month AS current_month,
    COUNT(DISTINCT prev.user_pseudo_id) AS returning_user_count,
    COUNT(DISTINCT curr.user_pseudo_id) AS total_users
  FROM
    monthly_user_counts curr
  LEFT JOIN
    monthly_user_counts prev
  ON
    curr.year = prev.year AND curr.month - 1 = prev.month AND curr.user_pseudo_id = prev.user_pseudo_id
  GROUP BY
    curr.year, current_month
),
inter as (
SELECT
  year,
  current_month,
  returning_user_count,
  total_users,
  (returning_user_count * 100.0 / total_users) AS monthly_return_percentage
FROM
  returning_users
ORDER BY
  year, current_month
)
,
inter2 as (
SELECT
  inter.*,
  CASE 
    WHEN current_month = 1 THEN 'January'
    WHEN current_month = 2 THEN 'February'
    WHEN current_month = 3 THEN 'March'
    WHEN current_month = 4 THEN 'April'
    WHEN current_month = 5 THEN 'May'
    WHEN current_month = 6 THEN 'June'
    WHEN current_month = 7 THEN 'July'
    WHEN current_month = 8 THEN 'August'
    WHEN current_month = 9 THEN 'September'
    WHEN current_month = 10 THEN 'October'
    WHEN current_month = 11 THEN 'November'
    WHEN current_month = 12 THEN 'December'
    ELSE 'Unknown'
  END AS month,
  from inter)

  select year,month,total_users,returning_user_count, monthly_return_percentage from inter2  

更新后的代码与新问题 #

根据建议修改代码后，1月留存率为0的问题解决了，但留存率比原查询结果低很多，想确认当前的查询逻辑是否合理：

WITH monthly_user_counts AS (
  SELECT
    PARSE_DATE('%Y%m%d',event_date) AS obs_date,
    DATE_SUB(PARSE_DATE('%Y%m%d',event_date), INTERVAL 1 MONTH) AS prev_date,
    user_pseudo_id
  FROM
    `rayn-deen-app.analytics_317927526.events_*`
),
returning_users AS (
  SELECT
    EXTRACT(YEAR FROM curr.obs_date) AS year,
    EXTRACT(MONTH FROM curr.obs_date) AS month,
    COUNT(DISTINCT prev.user_pseudo_id) AS returning_user_count,
    COUNT(DISTINCT curr.user_pseudo_id) AS total_users
  FROM
    monthly_user_counts curr
  LEFT JOIN
    monthly_user_counts prev
  ON
    curr.obs_date = prev.prev_date AND curr.user_pseudo_id = prev.user_pseudo_id
  GROUP BY
    year, month
),
inter as (
SELECT
  year,
  month,
  returning_user_count,
  total_users,
  (returning_user_count * 100.0 / total_users) AS monthly_return_percentage
FROM
  returning_users
ORDER BY
year, month
)

SELECT * FROM inter 

逻辑问题分析 #

你的新查询逻辑存在核心偏差，直接导致留存率计算偏低：

1. 初始代码的正确逻辑：

   • 先按年、月聚合去重用户，确保每个用户在每个月仅出现一次
   • 关联逻辑是「当前年=同年/去年，当前月=上月+1」，统计的是上月活跃过的用户中本月仍活跃的数量，这是标准的月度留存计算逻辑（即M1留存）

2. 新代码的错误点：

   • 未按月份去重用户：monthly_user_counts保留了用户每天的记录（直接用原始event_date转换的日期，未按年月聚合），一个用户在同一个月会有多条重复记录
   • 关联条件curr.obs_date = prev.prev_date完全偏离月度留存逻辑：它统计的是「恰好在上个月同一天活跃的用户，本月当天也活跃的数量」，而非整个上月活跃用户中本月活跃的数量，范围被极大缩小，因此留存率远低于真实值

修正方案 #

要同时解决跨年问题和留存率偏低问题，应该在初始代码基础上修改关联条件，而非重构日期逻辑：

WITH monthly_user_counts AS (
  -- 关键：按年、月去重用户，确保每个用户每月仅一条记录
  SELECT
    EXTRACT(YEAR FROM PARSE_DATE('%Y%m%d', event_date)) AS year,
    EXTRACT(MONTH FROM PARSE_DATE('%Y%m%d', event_date)) AS month,
    user_pseudo_id
  FROM
    `table`
  GROUP BY year, month, user_pseudo_id
),
returning_users AS (
  SELECT
    curr.year,
    curr.month AS current_month,
    COUNT(DISTINCT prev.user_pseudo_id) AS returning_user_count,
    COUNT(DISTINCT curr.user_pseudo_id) AS total_users
  FROM
    monthly_user_counts curr
  LEFT JOIN
    monthly_user_counts prev
  ON
    -- 修正跨年关联条件：1月关联去年12月，其他月份关联同年上月
    (
      (curr.year = prev.year AND curr.month - 1 = prev.month)
      OR (curr.month = 1 AND prev.year = curr.year - 1 AND prev.month = 12)
    )
    AND curr.user_pseudo_id = prev.user_pseudo_id
  GROUP BY
    curr.year, current_month
),
inter AS (
  SELECT
    year,
    current_month,
    returning_user_count,
    total_users,
    ROUND((returning_user_count * 100.0 / total_users), 2) AS monthly_return_percentage,
    CASE current_month
      WHEN 1 THEN '一月'
      WHEN 2 THEN '二月'
      WHEN 3 THEN '三月'
      WHEN 4 THEN '四月'
      WHEN 5 THEN '五月'
      WHEN 6 THEN '六月'
      WHEN 7 THEN '七月'
      WHEN 8 THEN '八月'
      WHEN 9 THEN '九月'
      WHEN 10 THEN '十月'
      WHEN 11 THEN '十一月'
      WHEN 12 THEN '十二月'
      ELSE '未知'
    END AS month_name
  FROM returning_users
  ORDER BY year, current_month
)

SELECT year, month_name, total_users, returning_user_count, monthly_return_percentage FROM inter

修正点说明 #

• 保留按年月去重用户的逻辑，避免重复统计
• 修改关联条件，完美处理跨年场景（1月关联去年12月）
• 回归标准月度留存计算逻辑：统计上月活跃用户中本月仍活跃的比例

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