Creating Default and Custom Channel Grouping in BigQuery - Part I: Preparation

The BigQuery export, as you probably know, is very generous with information on the raw event level, providing almost everything (and sometimes more) than what the GA4 frontend provides. There are a few exceptions, among which are the default and custom channel grouping: these are not provided in the raw export on the event level.

Let's imagine a situation where we want to report on conversion events recorded in GA4, mapped back to their originating channel. Or, for any reason, you want to do your own attibution of the different traffic source columns, and in the end you want to roll them up to a single channel grouping that is consistent with how GA4 categorizes them.

(We’ll dive into attribution in a later article, but in this case let’s assume the online sales process is rather quick, and the source channel directly attributed to the event in GA4 does the job.)

A little background

Since last summer, new fields have been available that behave as session-scoped, finally giving us the ability to provide channel attribution numbers much closer aligning to what the GA4 frontend shows. These fields are stored within the session_traffic_source_last_click column collection (a “STRUCT”, to be precise), and on the session level, one of the underlying STRUCTs have a default channel grouping which you can use in downstream data logic. Namely, these two columns in particular:

• session_traffic_source_last_click.cross_channel_campaign.default_channel_group
• session_traffic_source_last_click.cross_channel_campaign.primary_channel_group

Sometimes though, you may have to create a default channel grouping on the event level. For example, in situations where you want to “honor” the change of attributed channel mid-session, which the session level default channel grouping does not do.

Coding up a full-fledged channel grouping mechanism

If you have enough experience with GA4 data in BigQuery, you can piece together a comprehensive channel grouping logic from the following sources:

• Google’s documentation on the default channel grouping rules
• Potentially the GA4Dataform logic to give you a head-start instead of having to write the whole code from scratch (optional)
• You’ll need to set up the BigQuery Data Transfer for Google Ads. A guide is coming soon, but it is not overly difficult and you can just set it up yourself (if you have not already done so yet). From here, you’ll need to take the ads_ClickStats_<customer_id> and ads_Campaign_<customer_id> tables (plus it may be beneficial to use ads_AdGroup_<customer_id> and ads_Ad_<customer_id> as well). These Google Ads tables (views) can be joined to GA4 data to provide further detail required by Google’s logic, as per the online support page above.

• the logic also needs a reference table of "source categories", where most sources that occur (to a significant extent) are categorized into "search", "social", "shopping" and "video" . This can be found on the same support page. (There is a shortcut to this you may find on your own—read further if you want it revealed.)
• plus any extra custom logic for channel groups that are becoming relevant as we speak, e.g. Generative AI traffic, Google Discover or arbitrary rules to segment SEO traffic based on where organic traffic land on your website.

In this 2-part tutorial series you will learn how to create a comprehensive channel grouping that takes into account:

• most of Google's rules as per their support page (including their proprietary categorization of traffic sources, and a shortcut to get it)
• additional information from Google Ads for more accurate results
• further custom rules for
  • traffic by AI agents
  • segmenting channels based on SEO rules
• + you'll see how this becomes easier with the free GA4Dataform.com solution
• + you'll get a "sneak peek" along the way into some tricks required to deal with Google Ads data.

In Part I, we'll prepare the data that we'll use for the channel grouping rule.