Skip to main content
GA4 | newsletter

#6 - New fields in GA4 export & SQL query generators

— Johan van de Werken

#6 - New fields in GA4 export & SQL query generators

Hi there,

A warm welcome to the 834 new subscribers who joined since the last newsletter!

It has been a while, so this edition contains A LOT of relevant news and updates about GA4 and BigQuery.

But first...

...a personal note

I am excited to announce that after 8+ years of working as a web/data analyst for various employers, I have decided to embark on a new journey as an independent data analyst.

I look forward to be able to spend more time to extend my platform GA4BigQuery.com. Next to that, I am available to work on various freelance data analytics projects. If you are interested in working with me or know someone who might be, please do not hesitate to reach out!

New article on GA4BigQuery

Since the last newsletter I've added 1 new article:

New fields in GA4 BigQuery export schema

In his great technical marketing newsletter (make sure to subscribe!), Simo Ahava shared a sneak peak of two new features in the GA4 BigQuery export. One is already available to some of you (it will eventually roll out to everyone): event-scoped traffic source data.

You'll see a new field in your export data: collected_traffic_source. This repeated record can be queried without the need to unnest event parameters and contains the following information:

  • manual_campaign_id  
  • manual_campaign_name
  • manual_source   
  • manual_medium
  • manual_term
  • manual_content
  • gclid (Google click identifier)
  • dclid (display click identifier)
  • srsltid (a Google Merchant Center identifier)

Most, if not all of these were already available as event parameters, so nothing to see here really. Because the traffic source data is still not sessionized, we'll still have to build our own attribution models. And unfortunately, in one of my data sets where the data is available already, it still contains the nasty GA4 misattribution bug.

What about the second feature, Simo?

There will be an entirely new export (and associated data table) for user-scoped data. (...) a handy feature, because we can now build a separate user store in BigQuery, which can then be joined with the rest of the GA4 data or augmented with other sources (such as your CRM data).

To the rescue: GA4 SQL query generators

Good news for those of you that don't feel like mastering SQL. Using these two SQL query generators you will still be able to query some data from the GA4 export in BigQuery. Both are free to use (at the moment).

  • GA4 SQL by Ahmed Ali is a web app that has generated 20k queries by now, so it looks like some of you have tried it already.
  • GA4BQ by Assaf Terfikant is a Chrome extension that can be used while working in the BigQuery editor.

Are they any good? That depends on who you ask. Tools like these can be very helpful to pull reports from the GA4 BigQuery export. But if you need to work with this data set more often and want to customize queries for your specific use case, I would strongly advise to invest some time and resources to learn SQL in the context of GA4. You will benefit from this the rest of your professional career.

BigQuery updates

  • Earlier we shared an official Google article about expected differences between BigQuery and the GA4 user interface. This new support article is a great addition to that.
  • In the BigQuery editor, you can now sort your query results by using the sort menu next to a column name.
  • The BigQuery Data Transfer Service for Google Ads supports the new Google Ads API. The Google Ads connector supports Performance Max and Discovery campaigns
  • Dataform is generally available in Google Cloud. It promises to simplify the development, management, and deployment of SQL pipelines in BigQuery. Dataform is a tool like dbt, you can build and deploy pipelines to process and analyze (GA4 export) data, ensuring data accuracy and accessibility without the need for extensive technical knowledge or infrastructure management.

Relevant blog posts from the community

That's it for now. Thanks for reading and happy querying!

Best regards,
Johan