在BigQuery中重建UA高级细分时数据匹配异常求助

原查询的核心问题 #

1. 会话标识不唯一：仅用visitId作为排除依据是错误的——不同用户的visitId可能重复，会误删无关用户的会话，导致结果偏低。
2. 事件类别大小写不匹配：UA的事件类别是大小写敏感的，原代码用'Booking'，若实际数据中是小写'booking'，会漏排除部分触发了事件的会话。
3. 冗余逻辑：totals.visits在GA会话数据中默认值为1（每行对应一个会话），IF(totals.visits=1, ...)属于多余判断。

修正后的查询代码 #

WITH Excluded_Sessions AS (
  -- 生成触发过booking事件的完整会话ID（fullVisitorId + visitId）
  SELECT 
    CONCAT(fullVisitorId, CAST(visitId AS STRING)) AS session_id
  FROM my_data.ga_sessions_*, UNNEST(hits) AS hits
  -- 注意匹配实际UA数据中的事件类别大小写，这里改为小写'booking'
  WHERE hits.eventInfo.eventCategory = 'booking'
)

SELECT
  -- 简化年月格式化逻辑
  FORMAT_DATE("%Y-%m", PARSE_DATE("%Y%m%d", date)) AS year_month,
  -- 直接统计唯一会话数，无需多余判断
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitId AS STRING))) AS sessions
FROM my_data.ga_sessions_* s
LEFT JOIN Excluded_Sessions e 
  ON CONCAT(s.fullVisitorId, CAST(s.visitId AS STRING)) = e.session_id
WHERE
  -- 仅保留未被排除的会话
  e.session_id IS NULL
  -- 保留美国境内会话
  AND geoNetwork.Country = 'United States'
GROUP BY year_month

额外检查点 #

如果需求是排除所有曾触发过booking事件的用户的全部会话（而非仅触发事件的那个会话），则需要修改排除逻辑：

WITH Excluded_Users AS (
  SELECT DISTINCT fullVisitorId
  FROM my_data.ga_sessions_*, UNNEST(hits) AS hits
  WHERE hits.eventInfo.eventCategory = 'booking'
)

SELECT
  FORMAT_DATE("%Y-%m", PARSE_DATE("%Y%m%d", date)) AS year_month,
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitId AS STRING))) AS sessions
FROM my_data.ga_sessions_* s
LEFT JOIN Excluded_Users e 
  ON s.fullVisitorId = e.fullVisitorId
WHERE
  e.fullVisitorId IS NULL
  AND geoNetwork.Country = 'United States'
GROUP BY year_month

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