GA4与BigQuery会话数不一致：Google源会话数差异排查

1. 对齐会话统计的逻辑基础 #

GA4 UI的会话以session_start事件为核心标记，直接对所有事件的user_pseudo_id+session_id去重会包含无效的事件关联。正确的统计应该基于session_start事件：

SELECT 
  COUNT(DISTINCT CONCAT(user_pseudo_id, '.', CAST(session_id AS STRING))) AS sessions
FROM `your-project.analytics_XXXXXX.events_*`
WHERE 
  _TABLE_SUFFIX BETWEEN '20221112' AND '20221209'
  AND event_name = 'session_start'
  AND traffic_source.source IN ('google', 'google.com')

2. 匹配流量源的统计范围 #

GA4 UI中“Google源”通常包含自然搜索、付费搜索等细分渠道，单一过滤traffic_source.source='google'可能遗漏部分流量。此外，会话的流量源以首次触发事件的来源为准，而非任意事件的来源：

WITH session_first_touch AS (
  SELECT
    user_pseudo_id,
    session_id,
    -- 取会话中第一个事件的流量源
    FIRST_VALUE(traffic_source.source) OVER (
      PARTITION BY user_pseudo_id, session_id 
      ORDER BY event_timestamp ASC
    ) AS first_source,
    FIRST_VALUE(traffic_source.medium) OVER (
      PARTITION BY user_pseudo_id, session_id 
      ORDER BY event_timestamp ASC
    ) AS first_medium
  FROM `your-project.analytics_XXXXXX.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20221112' AND '20221209'
),
unique_sessions AS (
  SELECT DISTINCT user_pseudo_id, session_id, first_source, first_medium
  FROM session_first_touch
)
SELECT COUNT(*) AS sessions
FROM unique_sessions
-- 匹配GA4 UI中Google源的覆盖范围
WHERE first_source LIKE '%google%'
  AND first_medium IN ('organic', 'cpc', 'none')

3. 修正日期时区的偏差 #

GA4 UI使用你设置的报表时区，而BigQuery导出表的_TABLE_SUFFIX基于UTC日期。如果时区不一致，需要用event_timestamp转换成本地时间过滤：

WHERE 
  -- 替换为你的GA4报表时区
  TIMESTAMP_MICROS(event_timestamp) BETWEEN 
    TIMESTAMP('2022-11-12 00:00:00', 'Asia/Shanghai') 
    AND TIMESTAMP('2022-12-10 00:00:00', 'Asia/Shanghai')
  AND event_name = 'session_start'

同时确认是否包含events_intraday_*表，避免遗漏未合并的实时数据。

4. 核对过滤规则一致性 #

• 检查GA4 UI视图是否设置了内部流量排除或测试设备过滤，这些规则不会同步到BigQuery，需要在BQ查询中手动添加对应过滤（比如排除特定IP段的流量）。
• 确认GA4 UI是否启用了数据采样（右上角查看是否有“采样”标识），估算值和BQ全量数据会存在差异。

5. 验证会话ID的完整性 #

部分情况下，session_id可能为空或格式异常，可添加过滤条件排除无效会话：

AND session_id IS NOT NULL
AND CAST(session_id AS STRING) != ''

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