What is GA4BigQuery all about?

GA4BigQuery is a digital guide with example queries and tutorials on how to query Google Analytics data in BigQuery.

Some background

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 150k 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' got a lot of viewers as well. As the BigQuery export for Google Analytics 4 (previously App + Web) is free to use for everyone, not only for enterprise GA360 accounts, I expected the demand for practical knowledge to grow in the coming years. Especially after the announcement that Universal Analytics will be deprecated, the amount of users on this platform searching for GA4 and BigQuery related content is increasing heavily.

Do you offer online courses?

I'm happy to announce that I have been 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.

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 creating more content instead. One of the perks of purchasing the course is that you'll get access to the Simmer community, where Simo, myself and the other community members can help you out. Of course you can also post your question on StackOverflow or join the kind community at Measure Slack.

Can I subscribe to get email updates?

Yes! Become a member and get new content 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, the latter will be deprecated in 2023. 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 #standardSQL only.

SQ-what?!

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:

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.

I have another question / suggestion.

Let me know what's on your mind.