What is GA4BigQuery all about?
GA4BigQuery is a digital guide with tips, ideas, example queries and tutorials on how to query Google Analytics data in BigQuery & rock your digital marketing analytics 🤘
In 2019 I published 'How to query and calculate Google Analytics data in BigQuery'.
When I first started querying Google Analytics data in BigQuery, I had a hard time interpreting the ‘raw’ hit-level data hiding in the ga_sessions_ export tables. Because I could not find a noob-proof guide on how to calculate Google Analytics metrics in BigQuery, I decided to write one myself. I provide lots of example queries so you don’t have to reinvent the wheel and hopefully you can save yourself some valuable time.
At the moment of writing this article received 100k views, an indication that I was not alone in my journey.
My early 2020 article 'How to query and calculate GA4 event data in BigQuery' is getting a lot of viewers too. As the BigQuery export for Google Analytics 4 (previously App + Web) is free to use for everyone, not only for very expensive GA360 accounts, I expect the demand for practical knowledge to grow in the coming years.
This platform will serve both Universal Analytics and GA4 with tips, ideas, example queries and tutorials.
Do you offer online courses?
I'm happy to announce that I'll be collaborating with Mari and Simo Ahava to deliver an extensive online course on Google Analytics 4 (GA4) and BigQuery on Simmer, a brand new technical marketing learning platform. Read all about it here.
Can I subscribe to get email updates?
Yes! Join the GA4BigQuery newsletter and get regular updates (max 1x/week) about fresh published articles and/or new features delivered straight in your inbox. You can unsubscribe from this newsletter anytime.
Can I use RSS to stay updated?
Of course, please find the feed here: rss-feed.
What is the difference between UA and GA4?
UA stands for Universal Analytics. This has been the Google's analytics flag ship since 2012. The BigQuery export for UA is only available for enterprise customers. GA4 is the successor of UA. Read all about it here. Main differences: all BigQuery export data is event based and available for all, including free accounts.
Which version of SQL should I use?
By default all queries on this platform are written in BigQuery standard SQL, unless stated otherwise. I'd suggest to use
I assume you have a basic understanding of SQL as a querying language and BigQuery as a database tool. If not, I suggest you follow a SQL introduction course first (this one, or this one), as I will not go into details about the SQL syntax, but will focus on how to get your (custom) Google Analytics reports out of BigQuery for analysing purposes.
How do I set up the Google Analytics export for BigQuery?
That depends on your Google Analytics property:
I'm about to cry. Can you fix my query?
Generally I like helping others, but as time is limited I decided to focus on writing more content for this platform instead. Please post your question on StackOverflow or join the kind community at Measure Slack. I'll be hanging around there too.
Your queries suck. Mine are better / faster / smarter / easier.
You're probably right. I am learning everyday, please feel free to drop a line and share your remarks and suggestions. Or convince me why I should invite you as a writer on this platform 😉
I have another question / suggestion.
Let me know what's on your mind.