GA4与BigQuery页面平均参与时间数值差异排查求助

你的查询结果偏小的核心原因有两个：

• 多次UNNEST event_params 存在潜在逻辑问题（虽然后续过滤可能抵消了笛卡尔积影响，但写法不规范，易引发统计误差）
• 分母使用了所有浏览页面的用户数，而GA4 UI中计算平均参与时间的分母是该页面的活跃用户数（即产生过user_engagement事件的用户）

修正后的查询语句 #

WITH page_engagement AS (
  -- 计算每个用户在目标页面的总参与时间
  SELECT
    user_pseudo_id,
    -- 直接从event_params提取页面地址，避免多次UNNEST
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,
    SUM((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec')) AS total_engagement_msec
  FROM
    `XXXXXX` -- 替换为你的GA4事件表路径
  WHERE
    event_name = 'user_engagement'
    -- 筛选文章类型页面
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_post_type') = 'post'
    AND event_date = '20230926'
    -- 排除无有效页面地址的事件
    AND (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') IS NOT NULL
  GROUP BY
    user_pseudo_id, page_location
),
page_active_users AS (
  -- 统计每个页面的活跃用户数（对齐GA4 UI的活跃用户定义）
  SELECT
    page_location,
    COUNT(DISTINCT user_pseudo_id) AS active_user_count
  FROM
    page_engagement
  GROUP BY
    page_location
)
-- 计算平均参与时间：总参与时间 / 活跃用户数
SELECT
  pe.page_location AS article_url,
  pau.active_user_count,
  COALESCE(SAFE_DIVIDE(SUM(pe.total_engagement_msec), pau.active_user_count) / 1000, 0) AS avg_engagement_time_seconds_per_user
FROM
  page_engagement pe
JOIN
  page_active_users pau ON pe.page_location = pau.page_location
GROUP BY
  pe.page_location, pau.active_user_count
ORDER BY
  pau.active_user_count DESC
LIMIT 5;

关键修正点说明 #

1. 避免多次UNNEST event_params
   原查询中同时UNNEST两次event_params会产生交叉连接，即使通过WHERE条件过滤出目标参数，也可能让单条事件数据被重复处理，导致参与时间被错误拆分或漏算。改用子查询直接提取单参数的写法更安全可靠。

2. 对齐GA4 UI的分母定义
   GA4 UI中页面平均参与时间的计算公式是：该页面总参与时间 / 该页面活跃用户数，其中活跃用户指的是在该页面产生过user_engagement事件的用户。原查询使用pageviews表的用户数作为分母，包含了大量未触发user_engagement的用户（比如快速关闭页面的访客），导致分母过大，结果被大幅拉低。

3. 直接从user_engagement事件统计
   不需要关联pageviews表，因为user_engagement事件本身携带了page_location和page_post_type参数，直接从事件表统计即可保证数据逻辑和GA4 UI一致。

内容的提问来源于stack exchange，提问作者Kuba Zábran