#1 - New website, updated queries, premium tutorials, GA4 x dbt

Nov 22, 2022 — Johan van de Werken

Hi there,

You're reading the first GA4BigQuery newsletter sent from our renewed website. GA4BigQuery.com. Looking at the past two years of our existence I see a steady increase in visitors looking for information on how to get a grip on GA4 and the BigQuery export.

Because there is a clear need for reliable information, I decided to (re)start this newsletter to update you on a monthly basis on the latest developments on the sweet spot where GA4 and BigQuery overlap:

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

Refreshed look and updated queries

While designing the course Query GA4 Data In Google BigQuery (together with Simo Ahava) I've learned a lot about best practices to create queries for GA4. I updated a lot of existing articles to reflect these improvements: dimensions & metrics. This is the core of our platform and this content will always be free accessible for everyone.

As you might see I tweaked the visual style of the site. Being a bit of nerd quite often, I'm very pleased that I found a website theme that supports dark mode. You can turn the switch on the top menu if you prefer so.

New: weekly premium tutorials (+ early bird discount)

To be able to deliver more valuable content in the future, I'm introducing a new concept: I'll be sending a weekly detailed tutorial for premium subscribers only. These posts will be delivered in your inbox when they get published, so you'll never miss new content. The website version of the article is only one click away.

To give you an idea what these tutorials might entail, and we're just getting started:

Premium subscribers will have full access to the tutorial archive as well, as long as your subscription is valid. You can choose between a monthly or annual pricing plan. In both cases 15% of your subscription fee will be donated to highly efficient charities (distributed by GiveWell.org).

Existing subscribers like you can use the early bird discount offer: 20% off on a yearly plan, forever. The offer is valid until December 31st 2022.

Community initiative: GA4 x BigQuery x dbt

While browsing the Measure Slack channels I saw Adam Ribaudo mentioning that he started an open source dbt package for GA4. When I read that he wants this project to become a community backed standard for GA4 data modeling, he definitely got my attention.

I asked him what his plans are. Adam:

I created dbt-ga4 to be a home for communal knowledge around GA4 and BigQuery. Most commonly, best practices are captured in one-off blog posts and Slack messages. With this repository, we have the opportunity to incrementally build up a library of tools and techniques that accelerate projects and save practitioners hundreds of hours. That won't happen without contributors! So please reach out and contribute however you can (open a PR, open an issue, or just send ideas).

As a frequent user of dbt I love this initiative. Read all about this awesome project and contribute if you can!

New BigQuery feature: query execution graph

After running a query you might have seen a new BigQuery feature, that is now in preview. The query execution graph is designed to bring you insights about how the performance of your query. Select the Execution Graph tab after running a query to see a graphical representation of each stage of the query. Read more about the query execution graph feature and query performance optimisation.

Relevant blog posts from the community

The GA4 BigQuery export is becoming even more relevant, now that Google has turned on quota limiting for the Looker (f.k.a. Data) Studio GA4 connector. But don't make this costly mistake by connecting Looker Studio directly to the event tables. This tutorial shows you a better approach.
It can be a pain to grab session level traffic source data from the BigQuery export. This blog post describes how to create a lookup table containing the last non-direct source of traffic for each unique session id.
Nice little how-to on calculating DAU/MAU in BigQuery.
A student of our course wrote a guest post for Simo Ahava about measuring a blog's efectiveness, using the GA4 BigQuery export, amongst other tools.
Is it a bug or a feature? With GA4 you never know. this community powered GA4 bug list also contains a few BigQuery related issues, like batch event timestamps, missing session-scope traffic source information, the infamous google/cpc misattributed as google/organic when a gclid is available.
Need to change the table expiration settings for dozens of event tables after upgrading your BigQuery account from the Sandbox? Simmer's latest (video)blog saves you a lot of trouble.

That's it for now. Thanks for reading and make sure to catch the early bird discount!

Best regards,
Johan