GA4与BigQuery中屏幕每会话平均互动时间数值差异排查求助

核心差异原因分析 #

1. 重复UNNEST导致笛卡尔积，计算结果被放大 #

你的代码中同时两次UNNEST(event_params)，这会让同一个事件下的firebase_screen和engagement_time_msec参数产生笛卡尔积关联。如果事件包含多个其他参数，会生成大量重复匹配的记录，直接拉高平均互动时间的计算结果。

2. 计算维度错误：事件级平均≠会话级平均 #

GA4的「每会话平均互动时间」是会话维度的统计：先计算每个会话的总互动时间，再对所有会话取平均值；而你的代码是直接对单个user_engagement事件的engagement_time_msec取平均。user_engagement事件默认每隔10秒触发一次，一个会话会产生多个该事件，你当前计算的是「单个互动事件的平均时长」，而非「每会话的平均互动时长」。

3. 屏幕与会话的关联逻辑不匹配 #

GA4中屏幕级的「每会话平均互动时间」，是统计用户在该屏幕上产生的互动时间总和后关联到对应会话；而你的代码是把每个user_engagement事件的屏幕与该事件的互动时间绑定后取平均，没有按会话维度聚合，无法反映单会话内该屏幕的总互动时长。

4. 时间范围筛选的潜在问题 #

你用FORMAT_TIMESTAMP('%Y%m%d', DATE_TRUNC(TIMESTAMP_MICROS(event_timestamp), month))筛选月份，虽逻辑可行，但更简洁准确的方式是直接用日期类型比较，避免格式转换可能带来的误差。

修正后的代码示例 #

以下代码按会话维度聚合，对齐GA4的统计逻辑，计算屏幕级每会话平均互动时间：

WITH session_screen_engagement AS (
  SELECT
    DATE_TRUNC(TIMESTAMP_MICROS(event_timestamp), MONTH) AS trunc_month,
    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_name,
    SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec')) AS session_screen_engagement_msec
  FROM
    `table_name`
  WHERE
    event_name = 'user_engagement'
    AND DATE_TRUNC(TIMESTAMP_MICROS(event_timestamp), MONTH) BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE) AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
  GROUP BY 1,2,3,4
)
SELECT
  trunc_month,
  screen_name,
  AVG(session_screen_engagement_msec / 1000) AS avg_engagement_time_sec -- 转为秒，对齐GA4单位
FROM session_screen_engagement
WHERE screen_name IS NOT NULL
GROUP BY 1,2
ORDER BY 1, avg_engagement_time_sec DESC;

注意：若数据中无ga_session_id，需从session_start事件的对应参数提取，或通过user_pseudo_id结合时间截断的方式近似会话划分，优先使用官方提供的会话ID保证准确性。

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