“活跃用户指标"的差异在 Firebase Analytics 仪表板和 BigQuery 导出之间 [英] Discrepancies on "active users metric" between Firebase Analytics dashboard and BigQuery export
问题描述
根据 Firebase Analytics 文档 (https://support.google.com/firebase/answer/6317517#active-users)，活跃用户数是在特定日期发起会话的唯一用户数.同样根据文档，每次启动会话时都会发送一个带有 session_start 名称的事件.我正在尝试使用 BigQuery 的导出来获取该指标，但我的查询给出了不同的结果(BigQuery 为 15636，FB 分析为 14908)
According to Firebase Analytics docs (https://support.google.com/firebase/answer/6317517#active-users), the active number of users is the number of unique users who initiated sessions on a given day. Also according to the docs, every time a session is started an event with session_start name is sent. I am trying to get that metric using BigQuery's export, but my query is giving me different results (15636 on BigQuery, 14908 on FB analytics)
我也尝试转换到不同的时区，看看这是否可能是问题所在，但无论我尝试哪个时区，我都不会得到相同(或相似)的结果
I have also tried converting to different timezones to see if that might be the issue, but no matter which timezone I try I never get the same (or similar) results
我应该运行哪个查询才能获得在 Firebase Analytics 仪表板上为活跃用户获得的相同结果?
Which query should I run to get the same results I get on Firebase Analytics dashboard for active users?
我的查询是
SELECT EXACT_COUNT_DISTINCT(user_dim.app_info.app_instance_id)
FROM table_date_range([XXXXX.app_events_], timestamp('2016-11-26'), timestamp('2016-11-29'))
WHERE DATE(event_dim.timestamp_micros) = '2016-11-27'
AND event_dim.name ='session_start'
谢谢
更新
在 @djabi 的回答之后，我将查询更改为使用 user_engagement 而不是 session_start，现在效果更好了.尽管如此，仍然存在一些细微差别(它们的范围从 16K 中的不到 10 到不到 50，具体取决于日期).
After @djabi's answer I changed my query to use user_engagement rather than session_start and it works much better now. Still some minor differences though (they range from under ten to under 50 out of 16K, depending on the date).
我通过使用 DATE(date_add(event_dim.timestamp_micros,1,'hour')) 再次尝试使用不同的时区，但我从来没有得到我在 Firebase Analytics 仪表板上得到的确切数字.
I have tried once again using different timezones by playing around with DATE(date_add(event_dim.timestamp_micros,1,'hour')) but I never got the exact number I get on Firebase Analytics dashboard.
新数字足以被认为在统计上是可接受的，但想知道是否有人建议改进查询并获得准确结果?
The new numbers are good enough to be considered statistically acceptable, but wondering if anyone has a suggestion to improve the query and get exact results?
当前查询是:
SELECT
COUNT(*) AS active_users
FROM (
SELECT
COALESCE(user_dim.user_id, user_dim.app_info.app_instance_id) AS user_id
FROM
TABLE_DATE_RANGE([XXXXX.app_events_], TIMESTAMP('2016-11-24'), TIMESTAMP('2016-11-29'))
WHERE
DATE(event_dim.timestamp_micros) = '2016-11-25'
AND event_dim.name ='user_engagement'
GROUP BY
user_id )
注意:目前我们不发送 user_id，因此 COALESCE 将始终返回 app_instance_id，以防有人建议这可能是问题
Note: At the moment we are not sending user_id, so the COALESCE will always return the app_instance_id, in case anyone was going to suggest that could be the problem
推荐答案
您需要等待整整 3 天才能从离线设备上传数据.您的查询根据事件时间戳正确过滤事件，您从 3 天开始提取数据，但距离今天只有一天半，这足以上传所有数据.尝试包括从昨天算起的 3 天.
You need to wait for full 3 days for data from offline devices to be uploaded. Your query correctly filter the events based on the event timestamp and you pull data from 3 days but that is only day and half from today and that is enough for all data to be uploaded. Try including 3 days from yesterday.
还可以尝试使用 user_engagement 事件而不是 session_start.我相信活跃用户数是基于 user_engagement 而不是 session_start 事件.
Also try using user_engagement event instead of session_start. I believe active user count is based on user_engagement and not on session_start events.
此外，FB 报告需要一些时间来处理，因此您可能需要并在第二天查看 FB 报告.
Also FB reports take a bit to process so you wight want and check the FB reports the next day.
FB 报告是在帐户的时区上完成的，并且事件以 UTC 时间戳记，因此 FB 报告中的日期与 UTC 日历日不同.您还想控制这种差异以获得匹配的数字.
FB reports are done on the time zone on the account and events are timestamped in UTC so the day in FB reports is different from UTC calendar day. You want to control for that discrepancy as well to get matching numbers.
这篇关于“活跃用户指标"的差异在 Firebase Analytics 仪表板和 BigQuery 导出之间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
