如何通过BigQuery正确计算GA4平均互动时长？

• 需求：基于BigQuery构建GA4会话级数据表，需覆盖session_id、user_pseudo_id及其他业务所需指标、维度字段
• 异常：自行编写SQL计算得到的平均互动时长为19分钟，与GA4后台展示的30分钟结果存在明显偏差，需确认可准确对齐官方口径的BigQuery计算方案
• 当前使用的存在偏差的SQL代码：

SELECT
    DISTINCT ( SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec')) as timeOnSite_ms 
FROM tableXXXX 

你的计算逻辑存在两个核心错误，直接导致结果偏低：

1. 对engagement_time_msec字段的含义理解错误

该参数记录的是当前事件触发时，距离上一次判定用户活跃的互动时长增量，不是从会话启动到当前事件的累计总时长。举个例子：用户打开页面停留10秒触发page_view，上报10000毫秒；后续停留20秒触发user_engagement事件，上报20000毫秒，该会话实际总互动时长为30000毫秒，直接取最大值仅能拿到20000毫秒，天然存在漏算。

2. 会话唯一标识粒度错误
   仅使用ga_session_id标记会话会出现跨用户ID重复的问题，必须组合user_pseudo_id + ga_session_id才能唯一标记单个会话，否则会出现不同用户会话数据串算的问题，进一步拉低统计结果。

对齐GA4官方口径的计算规则如下：

• 单会话总互动时长 = 同一会话下所有事件携带的合法engagement_time_msec值的总和，需过滤掉参数为空、数值小于0的异常脏数据
• 平均互动时长 = 所有有效互动会话（总互动时长>0）的总互动时长之和 / 有效互动会话总数，零互动会话不计入分母
• 原始参数单位为毫秒，转换为分钟需除以60000

可直接复现后台结果的参考SQL：

WITH session_engagement AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    SUM(
      CASE
        WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec') > 0
          THEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')
        ELSE 0
      END
    ) AS total_eng_time_ms
  FROM tableXXXX
  WHERE (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  -- 计算平均互动时长，单位：分钟
  SUM(total_eng_time_ms) / COUNT(DISTINCT CONCAT(user_pseudo_id, '_', session_id)) / 60000 AS avg_engagement_time_minute
FROM session_engagement
WHERE total_eng_time_ms > 0

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