统计用户的N日留存

N日留存的准确定义

用户的N日留存是衡量用户粘性和产品吸引力的重要指标，计算公式如下：

N日留存率 = （该同期群用户中，在 “首次打开日期 + N天” 当天有活跃行为的用户数） ÷ 该同期群总新增用户数 × 100%

具体例子：

• 同期群：2024 年 5 月 1 日首次打开应用的新增用户（共 1000 人）。

• 7 日留存计算：追踪这 1000 人中，在 2024 年 5 月 8 日（5 月 1 日 + 7 天） 当天有活跃行为（如打开应用、触发user_engagement事件）的用户数。

• 若 5 月 8 日有 300 人活跃，则该同期群的 7 日留存率为 30%。

与 “最近N日有活跃” 的区别

维度	Firebase 7 日留存	“最近 7 日有活跃”
统计对象	特定同期群（某一天的新增用户）	所有用户（新增 + 老用户）
活跃时间要求	必须在 “首次打开 + 7 天”当天活跃	过去 7 天内任意一天活跃即可
业务意义	衡量新增用户 “第 7 天是否仍留存”（用户粘性）	衡量用户 “近 7 天内是否有过互动”（活跃度）

计算N日留存率的SQL示例

这个示例将计算每天新增用户（首次出现的用户）在 7 天后的留存情况：

-- 步骤1：确定每个用户的首次访问日期（即新增日期）
WITH first_visit AS (
  SELECT
    user_id,
    DATE(MIN(event_time)) AS first_visit_date  -- 取用户首次出现的日期
  FROM user_events
  GROUP BY user_id
),

-- 步骤2：获取所有用户的活跃日期
user_active_dates AS (
  SELECT
    user_id,
    DATE(event_time) AS active_date  -- 取用户每天的活跃日期
  FROM user_events
  GROUP BY user_id, DATE(event_time)  -- 去重，每个用户每天只算一次活跃
),

-- 步骤3：关联首次访问日期和活跃日期，计算留存情况
retention_data AS (
  SELECT
    fv.first_visit_date AS cohort_date,  -- 同期群日期（新增日期）
    COUNT(DISTINCT fv.user_id) AS total_new_users,  -- 当日新增用户总数
    -- 计算7日留存用户数：首次访问后第7天活跃的用户
    COUNT(DISTINCT CASE 
      WHEN uad.active_date = DATE_ADD(fv.first_visit_date, INTERVAL 7 DAY) 
      THEN fv.user_id 
      ELSE NULL 
    END) AS day7_retention_users
  FROM first_visit fv
  LEFT JOIN user_active_dates uad 
    ON fv.user_id = uad.user_id
  GROUP BY fv.first_visit_date
)

-- 步骤4：计算7日留存率
SELECT
  cohort_date,
  total_new_users,
  day7_retention_users,
  -- 留存率 = 7日留存用户数 / 新增用户总数
  ROUND(day7_retention_users * 100.0 / total_new_users, 2) AS day7_retention_rate
FROM retention_data
ORDER BY cohort_date;

• first_visit：确定每个用户的首次访问日期，用于划分同期群

• user_active_dates：提取每个用户每天的活跃记录（去重）

• retention_data：关联用户的首次访问日期和活跃日期，统计每日新增用户总数和 7 日留存用户数

• 最终查询：计算并展示每日的 7 日留存率，保留两位小数