我正在尝试针对链接到BigQuery的Firebase Analytics数据开发查询，以重现Firebase Analytics信息中心的“每日用户互动”图表（包含在Google Data Studio报告中） .

根据Firebase Help documentation，每日用户参与定义为"Average daily engagement per user for the date range, including the fluctuation by percentage from the previous date range."因此，我的尝试是将 engagement_time_msec （自上次user_engagement事件以来根据https://support.google.com/firebase/answer/7061705?hl=en的额外参与时间（ms））与 user_engagement 事件相加，除以用户数（由 user_dim.app_info.app_instance_id ）每天 . 查询如下所示：

SELECT ((total_engagement_time_msec / 1000) / users) as average_engagement_time_sec, date FROM
(SELECT
  SUM(params.value.int_value) as total_engagement_time_msec,
  COUNT(DISTINCT(user_dim.app_info.app_instance_id)) as users,
  e.date
FROM `com_artermobilize_alertable_IOS.app_events_*`, UNNEST(event_dim) as e, UNNEST(e.params) as params
WHERE e.name = 'user_engagement'
AND params.key = 'engagement_time_msec'
GROUP BY e.date)
ORDER BY date desc

结果与每日用户互动的Firebase控制台图表中显示的结果非常接近，但我的查询中的值始终高出几秒钟（左侧显示BigQuery结果，右侧显示Firebase控制台图表值） .

Results from BigQuery

Results in Firebase Console

要注意，我们没有设置 user_dim.user_id 并且没有使用IDFA，所以我的理解是正确/唯一的计算方法"users"是 user_dim.app_info.app_instance_id ，我想Firebase控制台也是如此 .

Can anyone suggest what might be different between how I'm determining the average engagement time from BigQuery, and how that's being determined in the Firebase console graph?

需要注意的是，我看到了一个类似的问题here，但我没有't believe the suggested answer applies for my query since 1) the discrepancies are present over multiple days, 2) I' m已经在查询user_engagement事件，3）查询中使用的事件日期是基于你的应用程序的注册时区（根据this） ） .

firebase google-bigquery firebase-analytics