What is GA4BigQuery all about?

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

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

About me

My name is Johan van de Werken. I have 8+ years hands-on experience in marketing and data analytics. I thrive best at the sweet spot between data, business & tech. By day, I work at New10 as a Digital Analyst.

On the off-hours, however, I work on the GA4BigQuery platform. I live in the Netherlands (Utrecht area) with my wife and two daughters. I like my beer cold, my music loud, and whenever possible I unite these two hobbies by playing bass guitar and attending concerts.

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 start with a SQL introduction course (this one, or this one) or purchase our GA4 focused SQL course where you'll get up to speed with the SQL syntax while working with the GA4 data export and BigQuery. On this platform I will not go into too much detail about the SQL syntax. Don't feel like learning SQL yourself? Generate out of the box queries at GA4SQL.com.

Can I subscribe for free?

Yes! Become a free member and get a monthly newsletter packed with GA4 & BigQuery news, tips & tricks in your inbox. You can unsubscribe from this newsletter anytime.

Why should I become a premium subscriber?

Main reasons: to get zero-to-hero tutorials in your inbox and gain full access to the tutorial and report archive. On top of that 10% of your subscription fee will be donated to highly efficient charity. See the pricing plans for all perks and details.

What happens if my subscription period ends?

As long as your payment method is valid, your subscription will be automatically renewed. The price of an existing premium membership will always remain the same, even if the subscription prices for new members have been increased in the mean time.

Do you offer an online SQL course?

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. This course is recommended if you're not comfortable with SQL and/or BigQuery (yet).

Access to the Simmer course is not included in the GA4BigQuery membership. By subscribing to our premium plan you will unlock a 15% discount for the Simmer course. The discount is not stackable with other discounts.

Can I get a discount on the GA4BigQuery membership if I'm enrolled in the Simmer course?

Absolutely! Simmer can - on request - provide you a link to get 15% off our annual subscription. The 15% discount will remain valid if your subscription is renewed.

Can I use RSS to stay updated?

Of course, please find the feed here: rss-feed.

Why do results from your queries not match the GA4 user interface?

It is expected that the results for most queries on this website will not match with the GA4 user interface. This can occur for a variety of reasons.

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 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.

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.

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.

How do I set up the Google Analytics export for BigQuery?

That depends on your Google Analytics property:

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 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.

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.