I'm trying to replicate GA4/Firebase traffic & user acquisition reports for mobile apps using BigQuery.
I'm using this simple query to check the number of users, new users and purchasers for a specific day.
select
traffic_source.medium,
count(distinct user_pseudo_id) users,
count(distinct case when event_name= "first_open" then user_pseudo_id end ) as new_users,
count(case when event_name= "purchase" then user_pseudo_id end ) as purchasers
from
'mytable.events_*`
where platform='ANDROID'
group by 1
order by 1
Here are my BigQuery results:
However, they do not match the data from GA4 explorer reports:
The number of new users matches well, however, the distribution of purchasers is completely off.
Does anybody know what the issue might be?
Many thanks:)