如何在BigQuery中从Google Analytics events_*表计算会话时长？

你的问题核心是GA4导出的events_*表中计算会话时长时出现NULL值，这主要是因为部分会话未触发user_engagement事件，或者事件参数缺失导致的。以下是修正后的方案：

原因分析 #

• 部分短会话仅触发session_start事件（比如用户打开页面立即关闭），没有后续的user_engagement，导致原SQL无法捕获这些会话。
• 个别事件的ga_session_id或engagement_time_msec参数缺失，直接SUM会返回NULL。
• ga_session_id仅在用户范围内唯一，需结合user_pseudo_id避免不同用户的会话ID冲突。

修正后的SQL #

SELECT
  user_pseudo_id,
  session_id,
  COALESCE(SUM(engagement_time_msec) / 1000, 0) AS session_length_seconds
FROM (
  -- 先提取所需参数，过滤无效数据
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    -- 处理参数缺失的情况，默认返回0
    COALESCE((SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'engagement_time_msec'), 0) AS engagement_time_msec
  FROM `project_id.dataset_id.events_*`
  WHERE
    -- 包含会话开始和用户交互事件，确保捕获所有会话
    event_name IN ('session_start', 'user_engagement')
    -- 过滤无会话ID的无效事件
    AND (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') IS NOT NULL
)
GROUP BY user_pseudo_id, session_id

关键说明 #

1. 包含session_start事件：确保捕获所有会话，哪怕是没有用户交互的短会话。
2. COALESCE处理NULL：将缺失的engagement_time_msec转为0，避免SUM后返回NULL。
3. 关联user_pseudo_id：避免不同用户的ga_session_id重复导致统计错误。
4. 过滤无会话ID的事件：移除无法关联到会话的无效数据，保证结果准确性。

官方逻辑参考 #

GA4中会话时长的计算逻辑是累加会话内所有user_engagement事件的engagement_time_msec总和，会话结束的触发条件为：用户30分钟无交互、关闭页面或离开网站。如果需要更精确的会话结束时间，可以结合event_timestamp计算会话的时间跨度（从session_start到最后一个事件的时间差），但这种方式需要处理跨天会话的情况。

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