Firebase与BigQuery单屏幕平均互动时间计算结果差异求助

1. 指标定义不一致（核心原因） #

Firebase控制台的「单屏幕平均互动时间」，计算逻辑是该屏幕的总用户停留时长 ÷ 该屏幕的访问次数（即screen_view事件的触发次数）。而你的代码计算的是每个user_engagement事件的平均活跃时长，两者统计维度完全不同：

• 一次屏幕访问（触发一次screen_view）期间，可能会触发多次user_engagement事件（Firebase默认每10秒上报一次活跃事件，或在会话结束时上报）。比如用户在某屏幕停留30秒，会生成3次user_engagement事件，每次上报10000毫秒活跃时间。控制台会计算为「30秒 ÷ 1次访问 = 30秒/次」，而你的代码会计算「(10+10+10) ÷3 =10秒/次」，结果自然存在倍数级差异。

2. 数据关联方式的潜在问题 #

你的代码通过两次unnest(event_params)关联firebase_screen和engagement_time_msec，虽然过滤后每条事件最终生成1条记录，但写法存在逻辑冗余，且可能引入异常数据：

• 如果某个user_engagement事件同时包含多个firebase_screen参数（极端异常场景），会导致数据重复计算，拉低或拉高平均值。正确的关联方式应该在一次unnest中提取参数，或用嵌套查询直接获取：

  select 
    event_date,
    (select value.string_value from unnest(event_params) where key='firebase_screen') as screen_name,
    avg((select value.int_value from unnest(event_params) where key='engagement_time_msec')/60000) as avg_engagement_time_min
  FROM `table_name`
  where event_name = 'user_engagement'
    AND event_date BETWEEN @DS_START_DATE AND @DS_END_DATE
    and exists(select 1 from unnest(event_params) where key='firebase_screen')
    and exists(select 1 from unnest(event_params) where key='engagement_time_msec')
  group by 1,2
  order by 1,avg_engagement_time_min desc 
  

  注：该修正仅解决数据关联的潜在问题，无法改变指标定义不一致的核心差异。

3. 数据范围与过滤逻辑差异 #

• 时区差异：BigQuery的event_date是UTC时区的日期，而Firebase控制台默认使用项目设置的本地时区，若@DS_START_DATE和@DS_END_DATE为UTC时间，会与控制台统计的日期范围错位，导致数据量差异。
• 异常数据处理：Firebase控制台会自动过滤无效数据（如测试设备数据、异常短时长的互动），而你的代码未做类似过滤，可能包含无效的user_engagement事件数据。

4. 时长统计逻辑差异 #

Firebase控制台的屏幕停留时间是从用户进入屏幕（screen_view触发）到离开的完整间隔，包含屏幕前台的活跃和非活跃时段；而user_engagement事件的engagement_time_msec仅统计用户实际与App交互的时长（如触摸、滑动操作），不包含后台或无操作的前台停留时间，这也会导致总时长统计的差异。

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