You've successfully subscribed to GA4BigQuery
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

#2 - SQL query support from ChatGPT, GA4 count approximation demystified

#2 - SQL query support from ChatGPT, GA4 count approximation demystified

Join 1,800+ other professionals and receive a monthly newsletter packed with GA4 & BigQuery news, tips & tricks

Great! Check your inbox and click the link to confirm your subscription
Please enter a valid email address!
Already have an account? Sign in

Hi there,

You are reading the second GA4BigQuery newsletter.

The response to the relaunch of the platform has been amazing. Every month we welcome more unique website visitors and the amount of subscribers (both free and premium) has been overwhelming.

My goal is still the same: provide valuable content to data analysts, marketeers and engineers who seek guidance on how to query the GA4 BigQuery export data set.

New content on GA4BigQuery

Since the last newsletter I've added 5 new articles:

ChatGPT vs. GA4 data export

Did you try ChatGPT? The natural language model that shocked the tech world does seem to have some knowledge about GA4, but doesn't (yet) handle requests that involve the GA4 BigQuery export. When asked, it will likely present you queries based on the Universal Analytics (GA3) data export model. This is understandable, because the ChatGPT model is only trained until 2021.

However, when you ask ChatGPT to support you in creating queries for the Firebase data export (GA4 has adopted the Firebase event model), it shows a glimpse of the future.

It might still be far from flawless in the specific context of GA4 data, but it can be a very helpful tool to fix general syntax problems in your SQL code.

GA4's unique count approximation demystified

One of the reasons that your BigQuery results don't match with the GA4 user interface is the usage of count approximation in the latter.

You can imagine I was very pleased to see a Google official source actively sharing a detailed blog post (reload the page if you get the message Service Unavailable) about this topic. It includes instructions on how to replicate those counts in BigQuery, using the HLL++ algorithm.

Using this approach will make your results in BigQuery less precise, but if you need your tables to match with the GA4 user interface (or API), at least now you know how to accomplish that. One can only hope more in-depth articles will follow!

Relevant blog posts from the community

That's it for now. Thanks for reading and happy holidays!

Best,
Johan