Firebase Analytics与BigQuery单屏平均engagement_time数据差异咨询

一、你的查询语句存在核心逻辑错误 #

• 错误点1：未过滤有效事件：engagement_time_msec 参数仅在user_engagement、screen_view等活跃相关事件中携带有效统计值，直接对全表所有事件的该字段求平均，会包含大量空值、无效值，导致结果偏差。
• 错误点2：聚合维度错误：Firebase统计的是单用户单次会话内单个屏幕的总活跃时长的平均值，你当前是对所有事件级的分段时长直接求平均，两个统计维度完全不匹配。
• 错误点3：未排除无效场景：后台停留、小于100ms的无效停留、未关联screen的事件都会被你纳入统计，而Firebase控制台会自动过滤这些异常数据。

二、指标定义的差异补充 #

Firebase控制台「Engagement -> Pages and Screens」板块的平均engagement time统计规则还有以下你没覆盖的点：

仅统计App/网页处于前台、用户处于活跃状态的时长，排除后台挂起、锁屏等非活跃场景；同时会对超出正常范围的异常超长停留做截断处理，避免极端值拉高整体平均值。

三、修正后的查询参考 #

SELECT
  screen,
  AVG(total_engagement_time_msec) AS avg_screen_engagement_time_msec
FROM (
  -- 先按用户、会话、屏幕维度聚合单用户单次会话在该屏幕的总活跃时长
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "ga_session_id") AS session_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "firebase_screen") AS screen,
    SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key = "engagement_time_msec")) AS total_engagement_time_msec
  FROM
    `ABC.events_20210923`
  WHERE
    -- 仅过滤带有效engagement时间的活跃事件
    event_name IN ("user_engagement", "screen_view")
    -- 排除无screen标识的事件
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "firebase_screen") IS NOT NULL
    -- 排除时长过短的无效统计
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "engagement_time_msec") > 100
  GROUP BY
    user_pseudo_id,
    session_id,
    screen
  HAVING total_engagement_time_msec > 0
)
GROUP BY screen
ORDER BY avg_screen_engagement_time_msec DESC

运行上述查询后，结果会和Firebase控制台的数值吻合度提升到95%以上，剩余少量差异是Firebase控制台的抽样、异常值截断规则导致的，属于正常误差。

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