如何用BigQuery导出数据复现GA4用户参与度？结果较API偏高3%

你用BigQuery导出的GA4数据计算用户参与度时，结果比GA4 API高3%，核心原因是BigQuery原始数据包含了GA4 API计算时自动过滤的内容，以下是具体分析和修正方案：

差异根源 #

GA4官方明确用户参与度基于engagement_time_msec计算，但API会自动过滤三类数据：非参与事件、内部/测试流量、未完成处理的临时数据，而你的原始SQL直接汇总了所有事件中的该参数，导致数值偏高。

修正后的SQL查询 #

1. 仅统计参与事件的互动时长 #

GA4的用户参与度仅统计「参与事件」的互动时长，比如session_start、user_engagement、scroll、click等，修正SQL如下：

SELECT
  SUM((
    SELECT
      value.int_value/1000
    FROM
      UNNEST(event_params)
    WHERE
      KEY = 'engagement_time_msec')) AS total_engagement_time_sec
FROM
  `BI_1.analytics_331127371.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230301' AND '20230315'
  -- 仅保留参与事件，可根据你的GA4配置补充其他参与事件
  AND event_name IN ('session_start', 'user_engagement', 'scroll', 'click', 'video_start')

2. 同步过滤内部/测试流量 #

如果GA4后台设置了内部IP或测试用户过滤，需要在BigQuery中同步规则：

SELECT
  SUM((
    SELECT
      value.int_value/1000
    FROM
      UNNEST(event_params)
    WHERE
      KEY = 'engagement_time_msec')) AS total_engagement_time_sec
FROM
  `BI_1.analytics_331127371.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230301' AND '20230315'
  AND event_name IN ('session_start', 'user_engagement', 'scroll', 'click', 'video_start')
  -- 过滤内部IP段，替换为你的实际规则
  AND NOT geo_network.network_ip LIKE '192.168.%'
  -- 过滤测试用户的伪ID，替换为你的测试用户ID
  AND user_pseudo_id NOT IN ('test_user_001', 'test_user_002')

3. 匹配GA4数据处理窗口 #

GA4 API的数据会经过24-48小时的最终处理，每日导出的BigQuery数据可能包含未完成的临时会话，可通过以下条件排除：

SELECT
  SUM((
    SELECT
      value.int_value/1000
    FROM
      UNNEST(event_params)
    WHERE
      KEY = 'engagement_time_msec')) AS total_engagement_time_sec
FROM
  `BI_1.analytics_331127371.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20230301' AND '20230315'
  AND event_name IN ('session_start', 'user_engagement', 'scroll', 'click', 'video_start')
  -- 排除未结束的会话
  AND EXISTS (SELECT 1 FROM UNNEST(event_params) WHERE key = 'session_id')

验证步骤 #

1. 取单天数据对比：用修正后的SQL查询某一天的结果，和GA4 API的同天数值比对，缩小差异范围
2. 同步GA4后台规则：检查GA4的「数据过滤」「数据保留」设置，确保BigQuery查询完全匹配这些规则

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