I’m trying to find out how many distinct user records there are for an event between certain dates with BigQuery.

The results of BigQuery query does not match with the Google Analytics and Firebase reports for all events.

As an example, for one particular day, Firebase reports ~44k user count and ~65k event count. However, BigQuery result shows ~40k user count and ~65k event count. Event counts are exactly the same but user counts are completely different. And for bigger span of time, user counts differ drastically.

My BigQuery query looks like this:

SELECT
count(distinct user_pseudo_id) AS user_count,
count(*) AS event_count
FROM
my_table
WHERE
event_name = ‘ViewVerticalSelectionPage’
AND event_date = ‘20240623’;

Why is there such difference? Any help would be greatly appreciated.

I checked for any null user_pseudo_id values. All event has user_pseudo_id in BigQuery