如何正确查询Big Query数据以重构GA4的Google Analytics仪表盘？

问题根源 #

你当前的SQL在FROM子句中直接UNNEST(event_params)，导致单条事件记录被拆分为多条（每个参数对应一行）。后续分组统计时，原本的单条事件被重复计数，直接造成KPI数值被夸大。同时，子查询提取参数的写法冗余，GA4事件中同一参数key不会重复出现，没必要用DISTINCT+GROUP BY。

修正后的查询语句 #

SELECT
  event_date,
  event_timestamp,
  event_name,
  user_pseudo_id,
  device.category,
  -- 提取自定义参数：用LIMIT 1确保只取单个值（GA4同key参数不会重复）
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='author' LIMIT 1) AS author,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='campaign' LIMIT 1) AS campaign,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='categories' LIMIT 1) AS categories,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='clientid' LIMIT 1) AS clientid,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='duration' LIMIT 1) AS duration,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='eventactions' LIMIT 1) AS eventactions,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='eventcategory' LIMIT 1) AS eventcategory,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='eventlabel' LIMIT 1) AS eventlabel,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='mediatype' LIMIT 1) AS mediatype,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='pagetitle' LIMIT 1) AS pagetitle,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='pagetype' LIMIT 1) AS pagetype,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='source' LIMIT 1) AS source,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='sourceurl' LIMIT 1) AS sourceurl,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='srclink' LIMIT 1) AS srclink,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='status' LIMIT 1) AS status,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='title' LIMIT 1) AS title,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key='user_clientid' LIMIT 1) AS user_clientid,
  traffic_source.source AS User_Source,
  -- 统计KPI：基于单条事件行，无重复计数
  COUNT(1) AS eventCount,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN (SELECT value.string_value FROM UNNEST(event_params) WHERE key='session_engaged' LIMIT 1) = '1' THEN CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id' LIMIT 1)) END),
    COUNT(DISTINCT CONCAT(user_pseudo_id, (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id' LIMIT 1)))
  ) AS engagement_rate,
  COUNT(DISTINCT user_pseudo_id) AS Unique_Users,
  COUNT(DISTINCT CASE WHEN 
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec' LIMIT 1) > 0 
    OR (SELECT value.string_value FROM UNNEST(event_params) WHERE key='session_engaged' LIMIT 1) = '1' 
  THEN user_pseudo_id END) AS active_users,
  COUNT(DISTINCT CASE WHEN (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_number' LIMIT 1) = 1 THEN user_pseudo_id END) AS new_users,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM
  `zngly-corporate.analytics_315869392.events_*`
GROUP BY
  1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23

额外优化建议 #

• 如果需要处理多值参数（同一key对应多个值），可以用ARRAY_AGG(value.string_value)替代LIMIT 1，将多值合并为数组
• 对于数值型参数（比如engagement_time_msec），改用value.int_value或value.float_value，避免类型转换错误
• 可以用CTE（公共表表达式）提前提取所有参数，让查询更易读：

WITH event_data AS (
  SELECT
    event_date,
    event_timestamp,
    event_name,
    user_pseudo_id,
    device.category,
    traffic_source.source AS User_Source,
    -- 提取常用参数到CTE
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='session_engaged' LIMIT 1) AS session_engaged,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_id' LIMIT 1) AS ga_session_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='engagement_time_msec' LIMIT 1) AS engagement_time_msec,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key='ga_session_number' LIMIT 1) AS ga_session_number,
    -- 自定义参数
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='author' LIMIT 1) AS author,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key='campaign' LIMIT 1) AS campaign
    -- 其他参数同理补充
  FROM `zngly-corporate.analytics_315869392.events_*`
)
SELECT
  event_date,
  event_timestamp,
  event_name,
  user_pseudo_id,
  category,
  author,
  campaign,
  User_Source,
  COUNT(1) AS eventCount,
  SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN session_engaged = '1' THEN CONCAT(user_pseudo_id, ga_session_id) END),
    COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id))
  ) AS engagement_rate,
  COUNT(DISTINCT user_pseudo_id) AS Unique_Users,
  COUNT(DISTINCT CASE WHEN engagement_time_msec > 0 OR session_engaged = '1' THEN user_pseudo_id END) AS active_users,
  COUNT(DISTINCT CASE WHEN ga_session_number = 1 THEN user_pseudo_id END) AS new_users,
  COUNT(DISTINCT user_pseudo_id) AS users
FROM event_data
GROUP BY 1,2,3,4,5,6,7,8

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