BigQuery与Firebase Analytics的Average session time数值差异咨询

我的Firebase仪表盘： #

我用于计算平均会话时长的代码： #

with masterdata as

(

select user_eng_time, session_len.user_pseudo_id
from
(SELECT avg(params.value.int_value) as user_eng_time, user_pseudo_id
  from `rayn-deen-app.analytics_317927526.events_*`, unnest(event_params) as params
  where (event_name = "user_engagement") and (params.key = 'engagement_time_msec') 
   
   AND DATE_TRUNC(EXTRACT(DATE from TIMESTAMP_MICROS(event_timestamp)), DAY)
   BETWEEN DATETIME('2023-03-22 00:00:00') AND DATETIME('2023-05-21 00:00:00')

  GROUP BY user_pseudo_id
) as session_len

)


select avg(user_eng_time/1000) from masterdata

该代码计算结果为97.2秒。

问题：为何BigQuery与Firebase Analytics中的Average session time数值存在显著差异？ #

核心差异原因： #

• 计算逻辑完全不同：Firebase的平均会话时长是每个会话总时长的平均值，而你的代码是先算每个用户的平均engagement事件时长，再对所有用户的这个值取平均，属于「用户维度的二次平均」，和Firebase的「会话维度直接平均」逻辑偏差极大。
• 未关联会话ID：Firebase通过session_id标识独立会话，你的代码没有按session_id分组，无法区分同一用户的多次会话，会把用户所有engagement事件混在一起计算，导致结果失真。
• 时间范围统计口径不一致：你的代码用DATE_TRUNC配合DATETIME范围，可能和Firebase仪表盘的时区、日期闭区间/开区间规则不匹配，导致统计的事件集合不同。
• 事件过滤范围偏窄：Firebase会统计所有符合会话定义的会话（包括未触发user_engagement的短会话），但你的代码只筛选了user_engagement事件，遗漏了这类短会话，拉高了平均时长。

修正后的代码（对齐Firebase逻辑）： #

SELECT AVG(session_duration_sec) AS average_session_time
FROM (
  SELECT
    user_pseudo_id,
    params_session.value.string_value AS session_id,
    SUM(IF(params_engage.key = 'engagement_time_msec', params_engage.value.int_value, 0)) / 1000 AS session_duration_sec
  FROM `rayn-deen-app.analytics_317927526.events_*`,
       UNNEST(event_params) AS params_session,
       UNNEST(event_params) AS params_engage
  WHERE
    DATE(TIMESTAMP_MICROS(event_timestamp)) BETWEEN DATE('2023-03-22') AND DATE('2023-05-21')
    AND params_session.key = 'session_id'
    AND params_engage.key = 'engagement_time_msec'
  GROUP BY user_pseudo_id, session_id
)

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