如何在关联GA4的Google BigQuery中统计页面浏览量与退出次数？

问题描述 #

在关联GA4的Google BigQuery环境中，需要统计页面浏览量和退出次数（退出次数指会话最后一个事件发生在特定页面的次数，即会话结束于该页面路径的次数）。目前已实现页面浏览量统计，但无法完成退出次数统计，现有代码及数据Schema如下：

现有页面浏览量查询代码 #

SELECT
  event_params.value.string_value AS page_path,
  COUNT(*) AS page_views
FROM
  `MY_ga4_dataset.events_*`,
  UNNEST(event_params) AS event_params
WHERE
  _table_suffix BETWEEN '20230207' AND '20230207'
  AND event_name = 'page_view'
  AND event_params.key = 'page_location'
GROUP BY
  page_path
ORDER BY
  page_views DESC

GA4 BigQuery 数据Schema #

解决方案 #

要统计退出次数，核心是先识别每个会话的最后一个事件，再判断该事件是否为page_view并关联对应的页面路径。以下是合并页面浏览量和退出次数的完整查询：

WITH session_last_events AS (
  -- 获取每个会话的最后一个事件时间戳
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    MAX(event_timestamp) AS last_event_timestamp
  FROM
    `MY_ga4_dataset.events_*`
  WHERE
    _table_suffix BETWEEN '20230207' AND '20230207'
  GROUP BY
    user_pseudo_id, session_id
),
exit_pages AS (
  -- 匹配会话最后事件对应的页面路径（仅统计最后事件为page_view的情况）
  SELECT
    ep.value.string_value AS page_path,
    COUNT(*) AS exits
  FROM
    `MY_ga4_dataset.events_*` e
  JOIN
    session_last_events sle
    ON e.user_pseudo_id = sle.user_pseudo_id
    AND (SELECT value.int_value FROM UNNEST(e.event_params) WHERE key = 'ga_session_id') = sle.session_id
    AND e.event_timestamp = sle.last_event_timestamp
  LEFT JOIN
    UNNEST(e.event_params) ep
    ON ep.key = 'page_location'
  WHERE
    _table_suffix BETWEEN '20230207' AND '20230207'
    AND e.event_name = 'page_view'
  GROUP BY
    page_path
),
page_views AS (
  -- 复用原有页面浏览量统计逻辑
  SELECT
    event_params.value.string_value AS page_path,
    COUNT(*) AS page_views
  FROM
    `MY_ga4_dataset.events_*`,
    UNNEST(event_params) AS event_params
  WHERE
    _table_suffix BETWEEN '20230207' AND '20230207'
    AND event_name = 'page_view'
    AND event_params.key = 'page_location'
  GROUP BY
    page_path
)
-- 合并浏览量与退出次数，确保所有页面都能展示数据
SELECT
  COALESCE(p.page_path, e.page_path) AS page_path,
  COALESCE(p.page_views, 0) AS page_views,
  COALESCE(e.exits, 0) AS exits
FROM
  page_views p
FULL OUTER JOIN
  exit_pages e
  ON p.page_path = e.page_path
ORDER BY
  page_views DESC

逻辑说明 #

1. session_last_events：通过user_pseudo_id（用户标识）和ga_session_id（GA4会话ID）分组，提取每个会话的最后事件时间戳。
2. exit_pages：将原始事件表与会话最后事件表关联，筛选出会话最后一个事件是page_view的记录，统计每个页面的退出次数。
3. page_views：直接复用你原有的页面浏览量统计逻辑。
4. 最后通过全外连接合并两个结果集，确保所有页面都能展示浏览量和退出次数（无对应数据时显示0）。

注意事项 #

• 确认ga_session_id参数存在：GA4默认会在事件中携带该参数，若未找到需检查事件采集配置。
• 统一时间范围：所有CTE中的_table_suffix过滤条件需保持一致，避免数据不匹配。

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