如何在Google BigQuery中识别GA4关键事件以准确计算参与会话？

一、直接识别关键事件的方法 #

GA4标记的关键事件在BigQuery导出数据中，会在event_params里包含is_key_event参数（布尔类型，值为true），需要正确展开参数才能提取。你可以用以下SQL查询验证：

SELECT
  event_name,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'is_key_event') AS is_key_event
FROM
  `your-project-id.analytics_xxxxxx.events_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'

如果你的数据里确实没有这个参数，可以通过GA4管理API批量拉取各媒体资源的关键事件列表：

• 调用properties.keyEvents.list接口，获取每个媒体资源的关键事件名称
• 将返回的事件名称导入BigQuery作为临时表
• 关联主事件表，匹配出对应关键事件

二、无法识别关键事件时计算关联参与会话的方法 #

如果无法直接识别关键事件，可利用参与会话的定义反向推导：

参与会话 = 会话时长>10秒 OR 包含关键事件 OR 页面/屏幕浏览数>=2

已知session_engaged = 1代表该会话为参与会话，因此可以筛选出session_engaged = 1的会话后，排除掉会话时长<=10秒且页面浏览数<2的会话，剩余部分就是由关键事件触发的参与会话。

对应的SQL示例：

WITH session_stats AS (
  SELECT
    user_pseudo_id,
    session_id,
    MAX(session_engaged) AS session_engaged,
    TIMESTAMP_DIFF(MAX(event_timestamp), MIN(event_timestamp), SECOND) AS session_duration,
    COUNTIF(event_name IN ('page_view', 'screen_view')) AS page_screen_views
  FROM
    `your-project-id.analytics_xxxxxx.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20240101' AND '20240131'
  GROUP BY
    user_pseudo_id, session_id
)
SELECT
  COUNT(DISTINCT CONCAT(user_pseudo_id, session_id)) AS key_event_engaged_sessions
FROM
  session_stats
WHERE
  session_engaged = 1
  AND session_duration <= 10
  AND page_screen_views < 2

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