CODEWITHSUNDEEP
firebasegoogle-bigqueryfirebase-analytics
Marisol Prieto
Marisol Prieto

Reputation: 11

Firebase vs BigQuery: distinct users does not match

I can't understand the differences counting distinct users between firebase console and BigQuery, please can you help me to understand them?

           select x.daytime, count(distinct x.user_pseudo_id) from (
                                        select _TABLE_SUFFIX daytime, user_pseudo_id 
                                        from `analytics_186900506.events_*`
                                           where _TABLE_SUFFIX BETWEEN '20200501' AND '20200531'
                                               and event_name="session_start"  
                                               and platform ='ANDROID'
                                     ) x
             group by x.daytime
             order by x.daytime

I detect differences of 0-5% in the number of different users per day over a period that a priori is no longer modified, they are more than 1 week old. Generally the results in bigquery either match Firebase or are slightly higher. Seeing the data in bigquery, nor does it fit me that it is due to the difference in time zone. Is there an error in the query?

Upvotes: 1

Views: 758

Answers (1)

Tlaquetzal
Tlaquetzal

Reputation: 2850

Your query seems fine; however, it's not recommended to make comparisons using session_start events:

Never compare events like session_start or user_engagement that are triggered quite often.

In the same answer there's also interesting information about the count method in Firebase that could explain the difference:

Also, Google Analytics for Firebase is using HyperLogLog (HLL) proximation algorithm to calculate the user count. This provides more flexibility when filtering user metrics by user properties and audiences, but could cause the discrepancy in the event count.

If you are looking to count active users, maybe using "user_engagement" event can help reduce further the discrepancy.

Finally, in case you want to dig deeper in the differences you're observing, I suggest reaching out directly to Firebase support

Upvotes: 1

Related Questions