“活动用户指标”的差异Firebase Analytics仪表板和BigQuery导出之间 [英] Discrepancies on "active users metric" between Firebase Analytics dashboard and BigQuery export

问题描述

根据Firebase Analytics文档（ https://support.google.com / firebase / answer / 6317517＃活动用户数），活动用户数是在给定日期发起会话的唯一用户数。同样根据文档，每次会话开始时都会发送一个名为 session_start 名字的事件。我试图使用BigQuery的导出来获取这个指标，但是我的查询给了我不同的结果（BigQuery 15636，FB分析14908）

我也试过转换不同的时区，看看是否可能是这个问题，但无论我尝试哪个时区，我从来没有得到相同的（或类似的）结果

我应该运行哪个查询来获取我在Firebase Analytics仪表板上为活跃用户获得了相同的结果？

我的查询是

 WHERE DATE（event_dim.timestamp_micros）='2016-11-27'
 AND event_dim.name ='session_start'
  

谢谢

更新

在 @djabi 的答案后，我更改了我的查询以使用 user_engagement 而不是 session_start ，现在效果更好。还有一些细微的差别，（从16K以下，根据日期，从十岁以下50不等）。

我再次尝试不同的时区与 DATE（date_add（event_dim.timestamp_micros，1，'hour'））但我从来没有得到我在Firebase Analytics仪表板上得到的确切数字。

新的数字足够被认为是统计上可以接受的，但是想知道是否有人有改善查询的建议并得到确切的结果？

当前查询是：
$ b

  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 ，以防万一建议可能会出现这个问题

解决方案

您需要wai t完整的3天，以便上传离线设备的数据。您的查询根据事件时间戳正确过滤事件，并从3天内提取数据，但是从今天起只有一天半的时间，这足以让所有数据上传。尝试包括从昨天3天。

也尝试使用user_engagement事件，而不是session_start。我相信积极的用户数是基于user_engagement而不是session_start事件。

此外，FB报告需要一些处理，所以你想要和第二天检查FB报告。

FB账户上的时区已经完成，事件使用UTC时间戳，因此FB报表中的日期与UTC日历日不同。你想控制这种差异，以获得匹配的数字。

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

Which query should I run to get the same results I get on Firebase Analytics dashboard for active users?

My query is

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'  

Thanks

Update

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).

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?

The current query is:

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 )

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

解决方案

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.

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.

Also FB reports take a bit to process so you wight want and check the FB reports the next day.

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屋！