BigQuery查询Firebase Analytics用户会话时长异常问题求助

原SQL存在的问题 #

当前的计算逻辑不符合会话时长的定义，核心问题有两个：

• 分组逻辑错误：按用户、日期、事件名分组，完全没有会话维度的区分，会把用户全天所有同类型事件的时间差算成单会话时长
• 未使用Firebase内置的会话标识：Firebase导出到BigQuery的事件数据默认自带ga_session_id字段，用于标识同一会话的所有事件，默认会话超时规则为30分钟无操作即生成新的会话ID

正确查询SQL（基于内置ga_session_id） #

直接使用user_pseudo_id+ga_session_id作为唯一会话标识统计即可，user_pseudo_id是Firebase生成的用户匿名唯一标识，如果你对接了账号体系也可以替换为你自己的user_id：

SELECT
  -- 匿名用户ID，可替换为你自己的user_id
  user_pseudo_id,
  -- 会话ID
  (SELECT value.int_value FROM UNNEST(user_properties) WHERE key = 'ga_session_id') AS ga_session_id,
  -- 会话起始日期
  MIN(event_date) AS session_start_date,
  -- 会话时长（单位：秒）
  TIMESTAMP_DIFF(
    MAX(TIMESTAMP_MICROS(event_timestamp)),
    MIN(TIMESTAMP_MICROS(event_timestamp)),
    SECOND
  ) AS session_duration_sec,
  -- 会话内事件总数
  COUNT(*) AS session_event_count
FROM
  -- 替换为你自己的BigQuery表名
  `your_project.your_dataset.your_table_*`
WHERE
  -- 替换为你需要的查询日期范围
  _TABLE_SUFFIX BETWEEN '20210917' AND '20210920'
  -- 过滤无效会话
  AND (SELECT value.int_value FROM UNNEST(user_properties) WHERE key = 'ga_session_id') IS NOT NULL
GROUP BY
  user_pseudo_id,
  ga_session_id
-- 可选：过滤时长过短的无效会话
HAVING session_duration_sec > 0

自定义会话超时规则的查询方案 #

如果你需要自定义会话超时时间（比如默认30分钟不符合你的业务需求，要改成15分钟），可以自行划分会话，SQL如下：

WITH user_events AS (
  SELECT
    user_pseudo_id,
    TIMESTAMP_MICROS(event_timestamp) AS event_time
  FROM
    `your_project.your_dataset.your_table_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20210917' AND '20210920'
),
session_flag AS (
  SELECT
    user_pseudo_id,
    event_time,
    -- 相邻事件间隔超过15分钟则标记为新会话
    IF(
      TIMESTAMP_DIFF(event_time, LAG(event_time) OVER(PARTITION BY user_pseudo_id ORDER BY event_time), MINUTE) > 15,
      1, 0
    ) AS is_new_session
  FROM user_events
),
session_id_gen AS (
  SELECT
    user_pseudo_id,
    event_time,
    -- 生成自定义会话ID
    CONCAT(user_pseudo_id, '-', SUM(is_new_session) OVER(PARTITION BY user_pseudo_id ORDER BY event_time)) AS custom_session_id
  FROM session_flag
)
SELECT
  user_pseudo_id,
  custom_session_id,
  TIMESTAMP_DIFF(MAX(event_time), MIN(event_time), SECOND) AS session_duration_sec
FROM session_id_gen
GROUP BY user_pseudo_id, custom_session_id
HAVING session_duration_sec > 0

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