如何在BigQuery中计算应用用户的停留时长（engagement_time）

你的现有计算方式存在的问题 #

直接全局对所有事件下的engagement_time_msec求和的方式是错误的，会导致统计结果远高于实际值，核心问题有两个：

• engagement_time_msec会在screen_view、user_engagement等多个Firebase自动上报事件中同时携带，直接全表扫描求和会将同一段活跃时长重复累加
• 没有过滤异常上报的野值，部分因为应用异常、会话未正常结束上报的超大时长数值会拉高总统计结果

正确的计算方案 #

计算用户总前台停留时长，最合适的统计对象是user_engagement事件下的engagement_time_msec参数。user_engagement是Firebase专门用来上报用户活跃间隔的自动事件，其携带的engagement_time_msec代表上一次事件上报到本次上报之间的前台活跃时长，不存在重复统计问题。

计算单日期总停留时长（秒）的SQL示例 #

SELECT 
  SUM(x.value.int_value)/1000 AS total_engagement_seconds
FROM 
  `你的数据集表名`,
  UNNEST(event_params) AS x
WHERE 
  -- 仅统计user_engagement事件的对应参数，避免重复
  event_name = 'user_engagement' 
  AND x.key = 'engagement_time_msec'
  -- 过滤超过1小时的异常上报值，可根据业务场景调整阈值
  AND x.value.int_value < 3600000
  -- 按需求限定统计日期，格式为YYYYMMDD
  AND event_date = '20240101'

计算人均停留时长的SQL示例 #

如果需要统计人均值，建议先按会话维度聚合再计算，避免异常值影响：

SELECT 
  SUM(session_engagement)/1000 AS total_engagement_seconds,
  COUNT(DISTINCT user_pseudo_id) AS active_user_count,
  SUM(session_engagement)/1000/COUNT(DISTINCT user_pseudo_id) AS avg_per_user_seconds
FROM (
  -- 先聚合每个用户单会话的总活跃时长
  SELECT 
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    SUM(x.value.int_value) AS session_engagement
  FROM 
    `你的数据集表名`,
    UNNEST(event_params) AS x
  WHERE 
    event_name = 'user_engagement'
    AND x.key = 'engagement_time_msec'
    AND x.value.int_value < 3600000
    AND event_date = '20240101'
  GROUP BY 1,2
)

补充说明 #

如果你的业务需要统计会话总时长（包含用户切后台挂起应用的时间），可以改为计算每个会话的最早事件和最晚事件的时间戳差值，但是该方式的统计结果会高于实际用户活跃时长。

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