Firebase与BigQuery留存计算结果不一致原因及对齐方案咨询

两种计算方式的核心逻辑差异 #

• 新用户群组（cohort）判定规则差异
  你当前代码仅取first_open事件发生在目标日前后2天内的用户，Firebase会接收最长14天内的延迟上报first_open事件，漏统计的延迟上报用户会直接拉低你的统计基数；同时Firebase新用户判定默认遵循项目设置的时区，你代码硬编码了Etc/GMT+8，如果和项目时区不匹配会直接导致日期划分错误。
• 活跃用户判定规则差异
  你代码中仅将触发了user_engagement事件的用户算为活跃，Firebase留存的活跃判定标准是用户当日有任意有效事件上报即算活跃，很多短会话、后台启动的场景不会上报user_engagement，但会被Firebase计入活跃，这是你统计的用户数偏小的核心原因。
• 无效流量过滤规则差异
  Firebase会自动过滤作弊流量、测试设备产生的无效事件、异常上报的垃圾数据，你当前的BigQuery代码没有添加对应过滤规则，也会导致统计结果偏差。
• 用户身份去重逻辑差异
  如果你开启了Firebase的用户身份合并功能，Firebase会将同一个用户的多个user_pseudo_id（比如卸载重装、换设备产生的）合并为同一个用户统计，你当前代码仅用user_pseudo_id去重，也会产生统计差异。

对齐Firebase计算结果的修改方案 #

按照以下规则修改代码，即可让BigQuery统计结果和Firebase内置留存几乎一致：

1. 修正新用户群组逻辑：

• 确认Firebase项目的默认时区，替换代码里的硬编码时区参数
• 扩大_TABLE_SUFFIX的范围到目标日前后14天，覆盖所有可能的延迟上报first_open事件
• 如果开启了用户身份合并，需要结合user_id和user_pseudo_id做联合去重

2. 修正活跃用户判定逻辑：

• 去掉event_name = 'user_engagement'的过滤条件，只要用户当日有任意有效事件上报即判定为活跃

3. 补充无效流量过滤规则：

• 添加和Firebase对齐的过滤条件，例如排除测试设备、作弊标记事件、无效安装来源的事件

4. 修正日期匹配逻辑：

• 计算活跃用户的_TABLE_SUFFIX范围要根据时区做偏移，避免跨时区的事件被遗漏

修改后的参考代码 #

#standardSQL
-- 替换为你的Firebase项目时区，此处示例为Asia/Shanghai即GMT+8
DECLARE PROJECT_TIMEZONE DEFAULT "Asia/Shanghai";
DECLARE COHORT_DATE DEFAULT "20180901";

####################################################################
# PART 1: 定义9月1日的新用户群组
####################################################################
WITH 
new_user_cohort AS (  
  SELECT DISTINCT user_pseudo_id as new_user_id
  FROM 
    `projectId.analytics_YOUR_TABLE.events_*`
  WHERE
   event_name = 'first_open'
   -- 过滤无效事件，和Firebase自动过滤逻辑对齐
   AND app_info.install_source IS NOT NULL
   AND FORMAT_TIMESTAMP("%Y%m%d", TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY, PROJECT_TIMEZONE)) = COHORT_DATE
   -- 扩大表后缀范围覆盖14天延迟上报
   AND _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(PARSE_DATE("%Y%m%d", COHORT_DATE), INTERVAL 14 DAY)) 
      AND FORMAT_DATE("%Y%m%d", DATE_ADD(PARSE_DATE("%Y%m%d", COHORT_DATE), INTERVAL 14 DAY))
),
num_new_users AS (
  SELECT count(*) as num_users_in_cohort  FROM new_user_cohort
),

####################################################################
# PART 2: 统计群组用户的每日活跃情况
####################################################################
engaged_user_by_day AS (  
  SELECT 
    FORMAT_TIMESTAMP('%Y%m%d', TIMESTAMP_TRUNC(TIMESTAMP_MICROS(event_timestamp), DAY, PROJECT_TIMEZONE)) as event_day, 
    COUNT (DISTINCT user_pseudo_id) as num_engaged_users
  FROM 
    `projectId.analytics_YOUR_TABLE.events_*` 
    INNER JOIN new_user_cohort on new_user_id = user_pseudo_id 
  WHERE
    -- 去掉user_engagement过滤，只要有任意有效事件就算活跃
    app_info.install_source IS NOT NULL
    AND _TABLE_SUFFIX BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(PARSE_DATE("%Y%m%d", COHORT_DATE), INTERVAL 14 DAY)) 
      AND FORMAT_DATE("%Y%m%d", DATE_ADD(PARSE_DATE("%Y%m%d", COHORT_DATE), INTERVAL 10 DAY))
  GROUP BY event_day
)

####################################################################
# PART 3: 计算每日留存率
####################################################################
SELECT 
  event_day, 
  num_engaged_users, 
  num_users_in_cohort, 
  ROUND((num_engaged_users / num_users_in_cohort), 3) as retention_rate
FROM engaged_user_by_day 
CROSS JOIN num_new_users
ORDER BY event_day

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