Reply To: The Discrepancy Between BigQuery and Google Analytics 4 for Page Views: Seeking Explanations

[John]

Sure, I can break it down more casually!

So, the hiccup seems to be coming from the way you’re trying to count pageviews in the GA4 data on BigQuery. Here’s your code, right?

select count(distinct case when event_name = 'page_view' then concat (user_pseudo_id, cast(event_timestamp as string)) end) as pageviews

At a guess, I’d say it’s got its knickers in a knot because you’re asking it to count distinct combination of ‘user_pseudo_id’ and ‘event_timestamp,’ which isn’t always unique. To spin it another way, imagine you’re trying to identify distinct visitors by their hats and coats. The problem pops up if two folks show up wearing the same hat and coat combo – they get counted as one.

So, to avoid this wardrobe confusion, you could add the ‘session_id’ to the mix, like adding different colored boots to your hat and coat tracking. That’ll help to distinguish each visitor more accurately and should hopefully get your data in line with what GA4’s showing. Give it a try and see how it goes!