Firebase BigQuery每月事件计数 [英] Firebase BigQuery Monthly Event Counts

问题描述

为了快速跟踪Flurry和Firebase事件报告之间的每月比较，我在下面编写了这个简单的脚本，以获取每月1）事件计数和2）触发这些事件的唯一用户计数。

理论上，我从BigQuery获得的数据（链接到相同的Firebase帐户）应该提供与下面的Firebase Analytics控制台相同的输出。我只添加了一个随机示例屏幕截图，以指示我正在讨论哪些事件。

。

年，Analytics（分析）推出了 HLL 的用户指标，以便为用户过滤用户时提供更大的灵活性用户属性和受众群体的指标。因此，尽管您的活动数量可能与BigQuery和Google Analytics报告之间的确切比例相符，但您的用户数量可能会略有不同。

in an effort to fast-track a monthly comparison between Flurry and Firebase event-reporting, I wrote this simple script below to get the monthly 1) Event Count and 2) Count of Unique Users that triggered these events.

In theory the data I get from BigQuery (linked to the same Firebase account), should give the same output as the Firebase Analytics Console below. I only added a random example Screenshot to indicate which events I am talking about.

# My Standard SQL - Script
SELECT 
  event.name as event_name,
  COUNT(event.name) as event_count,
  COUNT(DISTINCT user_dim.app_info.app_instance_id) as users
FROM `project_id.com_game_example_IOS.app_events_*`,
  UNNEST(event_dim) as event
WHERE (_TABLE_SUFFIX BETWEEN '20170701' AND '20170731')
------ Inclusive for both the start-date and end-date.
GROUP BY event_name
ORDER BY event_count DESC
LIMIT 10;

Results:

• The event_count from the BigQuery output is exactly the same as the output in my console, but the distinct user_count is out by about 5K-10K for most of the events. In theory, the Firebase Analytics Console's users data should line up exactly with the BigQuery-output, but it does not.

My Questions:

• Is my query not correct, or is it that the Analytics Console outputs the wrong data ?
• Can my query be even more optimised ?
• Am I perhaps not considering something like Timestamps?

Thanks!

解决方案

There are a couple of things potentially at play here.

1. See the answer/comments about COUNT( DISTINCT ) here.

2. Earlier this year, Analytics launched HLL-based user metrics in order to provide customers with more flexibility when filtering user metrics by user properties and audiences. And so, although your event counts may match exactly between BigQuery and your Analytics reports, your user counts may differ slightly.

这篇关于Firebase BigQuery每月事件计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！