Firebase BigQuery Monthly Calculations

Question

to speed up tracking the monthly comparison of Flurry and Firebase events, I wrote this simple script below to get the monthly 1) number of events and 2) the number of unique users who triggered these events.

Theoretically, data retrieved from BigQuery (associated with the same Firebase account) should produce the same result as the Firebase Analytics console. I just added a random screenshot example to indicate what events I'm 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 file is exactly the same as the output in my console, but a separate user account for most events is around 5K-10K. Theoretically, the user data for the Firebase Analytics console should exactly match the output of BigQuery, but it is not.

My questions:

• Is my request invalid or does this mean that the Google Analytics console is displaying incorrect data?
• Can I still optimize my request?
• Perhaps I am not considering something like Timestamps ?

Thanks!

Answer 1

There are a few things that can be played here.

• See answer / comments about COUNT (DISTINCT) here .

• Earlier this year, Google Analytics launched HLL for custom metrics to give customers greater flexibility in filtering custom metrics by user and audience properties. And therefore, although the number of your events may exactly match between BigQuery and your Analytics reports, your user account may be slightly different.