如何在Google BigQuery中查询会话浏览器及选取GA浏览器维度？

针对Universal Analytics（UA）数据集 #

UA导出到BigQuery的会话表以ga_sessions_YYYYMMDD命名，浏览器信息嵌套在device字段下，直接提取device.browser就能对应GA界面里的「浏览器」维度。

1. 查询单一会话的浏览器详情

SELECT
  -- 生成会话唯一ID（UA中由访客ID+访问ID组合）
  CONCAT(fullVisitorId, CAST(visitId AS STRING)) AS session_id,
  device.browser AS browser,
  device.browserVersion AS browser_version,
  -- 可选：附加会话相关字段
  visitNumber AS visit_number,
  totals.pageviews AS pageviews_per_session
FROM
  `你的项目ID.analytics_XXXXXX.ga_sessions_*`
-- 按需过滤日期范围
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
  session_id, browser, browser_version, visit_number, pageviews_per_session

2. 按浏览器聚合统计会话数

SELECT
  device.browser AS browser,
  COUNT(DISTINCT CONCAT(fullVisitorId, CAST(visitId AS STRING))) AS session_count
FROM
  `你的项目ID.analytics_XXXXXX.ga_sessions_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
  browser
ORDER BY
  session_count DESC

针对Google Analytics 4（GA4）数据集 #

GA4的BigQuery导出是事件级表，会话需通过user_pseudo_id+session_id标识，浏览器信息同样在device.browser字段中。

1. 查询单一会话的浏览器详情

SELECT
  -- 生成GA4会话唯一ID
  CONCAT(user_pseudo_id, session_id) AS session_id,
  device.browser AS browser,
  device.browser_version AS browser_version,
  -- 可选：计算会话时长（微秒，可自行转换为秒/分钟）
  MAX(event_timestamp) - MIN(event_timestamp) AS session_duration_microseconds,
  COUNT(DISTINCT event_name) AS unique_events_per_session
FROM
  `你的项目ID.analytics_XXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
  session_id, browser, browser_version

2. 按浏览器聚合统计会话数

SELECT
  device.browser AS browser,
  COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS session_count
FROM
  `你的项目ID.analytics_XXXXXX.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
GROUP BY
  browser
ORDER BY
  session_count DESC

关键说明 #

device.browser字段完全对应GA界面中的「浏览器」维度，若需要更细分的维度（比如浏览器+版本组合），可同时搭配device.browser_version字段使用。

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