#1 - New website, updated queries, premium tutorials, GA4 x dbt
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:
- How to create a low-cost, lightning fast GA4 Looker Studio dashboard powered by the BigQuery export (full post temporarily accessible for free subscribers)
- How to flatten the GA4 BigQuery export schema for usage in relational databases
- How to combine public global weather and Google Analytics data in BigQuery (GA4)
- How to query multiple 'events' tables with 'table_suffix' (GA4)
- How to customise the default engaged session definition (GA4)
- How to deal with string, integer and float event parameter values using the coalesce function (GA4)
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
- 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!