月度留存率计算结果异常偏低，求问题排查及替代计算方案

你的查询结果不符合预期，核心问题出在逻辑偏差：

1. 日期逻辑错误：DATE_SUB(obs_date, INTERVAL 1 MONTH) 是取当前日期的上月同一天，但月度留存关注的是用户在自然月维度的活跃情况，而非固定日期的匹配。
2. 关联逻辑错误：用curr.obs_date = prev.prev_date关联，本质是匹配「当前日期用户」和「上月同一天用户」，完全不符合月度留存的定义——月度留存是指「某周期新增/活跃用户，在下一周期仍活跃的比例」。
3. 重复数据干扰：原CTE保留了用户每天的事件记录，后续虽用COUNT(DISTINCT)去重，但基础数据的冗余会导致关联逻辑混乱。

方法1：新增用户月度留存（最常用） #

统计某月份首次活跃的用户中，在下一个月至少有一次活跃的比例，是衡量产品拉新质量的核心指标。

WITH user_first_month AS (
  -- 确定每个用户的首次活跃自然月
  SELECT
    user_pseudo_id,
    DATE_TRUNC(MIN(PARSE_DATE('%Y%m%d', event_date)), MONTH) AS first_active_month
  FROM
    `rayn-deen-app.analytics_317927526.events_*`
  GROUP BY
    user_pseudo_id
),
user_active_months AS (
  -- 提取每个用户所有有活跃记录的自然月（去重）
  SELECT
    DISTINCT
    user_pseudo_id,
    DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), MONTH) AS active_month
  FROM
    `rayn-deen-app.analytics_317927526.events_*`
),
retention_stats AS (
  -- 关联首次活跃月与次月活跃记录，计算留存
  SELECT
    EXTRACT(YEAR FROM uf.first_active_month) AS year,
    EXTRACT(MONTH FROM uf.first_active_month) AS month,
    COUNT(DISTINCT uf.user_pseudo_id) AS total_new_users,
    COUNT(DISTINCT ua.user_pseudo_id) AS returning_users
  FROM
    user_first_month uf
  LEFT JOIN
    user_active_months ua
  ON
    uf.user_pseudo_id = ua.user_pseudo_id
    AND DATE_ADD(uf.first_active_month, INTERVAL 1 MONTH) = ua.active_month
  GROUP BY
    year, month
)
SELECT
  year,
  month,
  total_new_users,
  returning_users,
  ROUND((returning_users * 100.0 / total_new_users), 2) AS monthly_retention_rate
FROM
  retention_stats
ORDER BY
  year, month;

方法2：月度活跃用户留存（整体粘性衡量） #

统计某月份所有活跃用户中，在下一个月仍活跃的比例，适合评估整体用户的粘性。

WITH monthly_active_users AS (
  -- 统计每个自然月的活跃用户列表（去重）
  SELECT
    DATE_TRUNC(PARSE_DATE('%Y%m%d', event_date), MONTH) AS active_month,
    DISTINCT user_pseudo_id AS user_id
  FROM
    `rayn-deen-app.analytics_317927526.events_*`
)
SELECT
  EXTRACT(YEAR FROM curr.active_month) AS year,
  EXTRACT(MONTH FROM curr.active_month) AS month,
  COUNT(DISTINCT curr.user_id) AS total_monthly_users,
  COUNT(DISTINCT prev.user_id) AS returning_users,
  ROUND((COUNT(DISTINCT prev.user_id) * 100.0 / COUNT(DISTINCT curr.user_id)), 2) AS monthly_retention_rate
FROM
  monthly_active_users curr
LEFT JOIN
  monthly_active_users prev
ON
  curr.user_id = prev.user_id
  AND DATE_ADD(curr.active_month, INTERVAL 1 MONTH) = prev.active_month
GROUP BY
  year, month
ORDER BY
  year, month;

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