**Make your life easier and define custom Channel Groupings in a reusable function (UDF) when working with Google Analytics 4 data in BigQuery. The channel grouping definition based on GA4 parameters *source*, *medium* and *campaign* can be reused with ease, without duplicating all the channel grouping logic in every query (win!)**

- [The function](#example-1---default-channel-grouping-ua) is saved into BigQuery as a **persistent** User Defined Function (UDF)
- The channel grouping logic can be edited in one place and changes will directly affect all the queries where the UDF function is used.
- Examples provided for both the **Universal Analytics** as the **Google Analytics 4** default channel grouping definitions, which you can **customize to your needs**.

> **Update 2021-11-17**: Google released a new Default Channel Grouping definition for Google Analytics 4. I've tried to capture this new GA4 definition [in a BigQuery equivalent](#example-2---default-channel-grouping-ga4). 

## Defining Channel Groupings

Channel groupings are a logical combination of acquisition parameters (like `source`, `medium`, `campaign`) to classify your traffic into channels. In Google Analytics, the parameters mentioned are populated based on URL parameters (like `?utm_x=...`) or the *auto tagging* link between GA and Google Ads or Campaign. More information about channel groupings [in the Google documentation](https://support.google.com/analytics/answer/6010097?hl=en#zippy=%2Cin-this-article).

![default-channel-grouping-paths.PNG](https://static.stacktonic.com/small_default_channel_grouping_paths_60d90a9cf2.PNG)

The definitions of the channels should depend on your specific business needs. Next to the *Default Channel Grouping* definition of Google Analytics, you can create your own custom channel grouping(s). As mentioned, mappings can be made based on dimensions `source`, `medium`, `campaign`, but you can add more parameters to the function easily. 

In the [first SQL example](#example-1---default-channel-grouping) below, we've provided a generic definition which looks a lot like the Default Channel Grouping of Google Universal Analytics. 

- See [this page](https://support.google.com/analytics/answer/3297892?hl=en&ref_topic=6010089) for the Default Channel Grouping definition within the GA interface. 
- Google Analytics 4 does not provide the option (yet) to create custom channels groupings in the interface. Universal Analytics does, but it doesn't export these groupings to BigQuery (and if they did, you can't alter historical data). 
- The [Google Analytics 4 Default Channel Grouping definition](https://support.google.com/analytics/answer/9756891?hl=en) differs from Universal Analytics.
- The functions below can be used for both the Google Analytics 4 and Universal Analytics 360 exports.

> We highly recommend to customize your channel grouping based on your business needs. Therefore it's important to get your UTM tagging in order, so you can add more depth in to your mappings.

To illustrate, you could create the following additional rules in your groupings:

- Paid search: branded vs. non-branded campaigns (based on the `campaign` parameter for example)
- Social: organic social vs. paid social (make sure you ?utm= tag your social media campaigns accordingly)
- Email: Transactional emails vs. marketing related emails

You could also throw the `traffic source` into the mix. This can be useful when you want to slim down all the unique "source / medium" combinations. Of course, you can **create multiple functions** for **different levels of depth**.

## The BigQuery functions

Let's talk action. You can run the SQL function below directly in the BigQuery interface and it will automatically create the function within the dataset your specified in the `CREATE OR REPLACE FUNCTION`. It will show up in the tree on the left side of your screen;

![user-defined-function-channel-grouping.PNG](https://static.stacktonic.com/user_defined_function_channel_grouping_1061a47b1a.PNG)

### Example 1 - Default Channel Grouping UA

This is the BigQuery equivalent of the Google Universal Analytics default channel grouping definition.

*[View code on GitHub](https://gist.github.com/krisjan-oldekamp/bad52a06062b09729434ab65091b5393)*
```sql
create or replace function `<your-project>.<your-dataset>.channel_grouping`(tsource string, medium string, campaign string) as (
    case
        when (tsource = '(direct)' or tsource is null) 
            and (regexp_contains(medium, r'^($not set$|$none$)$') or medium is null) 
            then 'direct'
        when regexp_contains(medium, r'^(social|social_advertising|social-advertising|social_network|social-network|social_media|social-media)$') 
            then 'social'
        when regexp_contains(medium, r'^(email|mail)$') 
            then 'email'
        when regexp_contains(medium, r'^(affiliate|affiliates)$') 
            then 'affiliate'
        when regexp_contains(medium, r'^(cpc|ppc|paidsearch)$') 
            then 'search_paid'
        when regexp_contains(medium, r'^(display|cpm|banner)$') 
            then 'display'
        when medium = 'organic'
            then 'search_organic'
        when medium = 'referral'
            then 'referral'
        else '(other)'
    end
);
```

### Example 2 - Default Channel Grouping GA4

**2021-11-17**: Google recently [updated the default channel grouping definition](https://support.google.com/analytics/answer/9756891?hl=en) of Google Analytics 4, so I've replaced the old advanced example with the GA4 example, since there is a lot of overlap (the GA4 definition has mush more dept than the Universal Analytics one). 

> Be aware that not all the rules applied in GA4 can be used in BigQuery since we do not have the exact definitions of rules like "Source matches a list of social sites". Please let me know if you have any suggestions to further tweak the function.

*[View code on GitHub](https://gist.github.com/krisjan-oldekamp/bad52a06062b09729434ab65091b5393)*
``` sql
create or replace function `<your-project>.<your-dataset>.channel_grouping`(tsource string, medium string, campaign string) as (
    case
        when (tsource = 'direct' or tsource is null) 
            and (regexp_contains(medium, r'^($not set$|$none$)$') or medium is null) 
            then 'direct'
        when regexp_contains(campaign, r'^(.*shop.*)$') 
            and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            then 'shopping_paid'
        when regexp_contains(tsource, r'^(google|bing)$') 
            and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            then 'search_paid'
        when regexp_contains(tsource, r'^(twitter|facebook|fb|instagram|ig|linkedin|pinterest)$')
            and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*|social_paid)$') 
            then 'social_paid'
        when regexp_contains(tsource, r'^(youtube)$')
            and regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            then 'video_paid'
        when regexp_contains(medium, r'^(display|banner|expandable|interstitial|cpm)$') 
            then 'display'
        when regexp_contains(medium, r'^(.*cp.*|ppc|paid.*)$') 
            then 'other_paid'
        when regexp_contains(medium, r'^(.*shop.*)$') 
            then 'shopping_organic'
        when regexp_contains(tsource, r'^.*(twitter|t\.co|facebook|instagram|linkedin|lnkd\.in|pinterest).*') 
            or regexp_contains(medium, r'^(social|social_advertising|social-advertising|social_network|social-network|social_media|social-media|sm|social-unpaid|social_unpaid)$') 
            then 'social_organic'
        when regexp_contains(medium, r'^(.*video.*)$') 
            then 'video_organic'
        when regexp_contains(tsource, r'^(google|bing|yahoo|baidu|duckduckgo|yandex|ask)$') 
            or medium = 'organic'
            then 'search_organic'
        when regexp_contains(tsource, r'^(email|mail|e-mail|e_mail|e mail|mail\.google\.com)$') 
            or regexp_contains(medium, r'^(email|mail|e-mail|e_mail|e mail)$') 
            then 'email'
        when regexp_contains(medium, r'^(affiliate|affiliates)$') 
            then 'affiliate'
        when medium = 'referral'
            then 'referral'
        when medium = 'audio' 
            then 'audio'
        when medium = 'sms'
            then 'sms'
        when ends_with(medium, 'push')
            or regexp_contains(medium, r'.*(mobile|notification).*') 
            then 'mobile_push'
        else '(other)'
    end
);
```

## How to use and test the function

When the function is saved, you can use the function this way:

``` sql
select
    <your-dataset>.channel_grouping(source, medium, campaign) aschannel_grouping
    -- or
    `<your-project>.<your-dataset>.channel_grouping`(source, medium, campaign) as channel_grouping
from
     <your-table>
```

Make sure you test your function when you are creating the grouping rules. You could use the following query to check what traffic parameters are mapped to which channel:

> Google Analytics 4 currently has some quirks and unknowns to deal with. Related to traffic acquisition parameters, sessions are not reset when a user is switching from channel within an active session.  Next to that it's not clear how GA4 is generating / attributing the *Traffic Acquisition* reports in the interface. Also, Google Analytics 4 uses conversion modelling to attribute conversions (due to tracking mechanisms such as Apple ITP). So currently it's impossible to align your BigQuery data with these interface reports. However, this is not a problem when defining your channels.

```sql
with
-- select session in last 30 days
sessions as (
    select
        user_pseudo_id as ga_client_id,
        concat(user_pseudo_id,'.',(select cast(value.int_value as string) from unnest(event_params) where key = 'ga_session_id')) as session_id, -- combine user_pseudo_id and session_id for a unique session-id
        timestamp_micros(min(event_timestamp)) as session_start,
        array_agg(
            if(event_name in('page_view','user_engagement','scroll'), struct(
                event_timestamp,
                lower((select value.string_value from unnest(event_params) where key = 'source')) as source,
                lower((select value.string_value from unnest(event_params) where key = 'medium')) as medium,
                lower((select value.string_value from unnest(event_params) where key = 'campaign')) as campaign,
                (select value.int_value from unnest(event_params) where key = 'entrances') as is_entrance,
                (select value.int_value from unnest(event_params) where key = 'ignore_referrer') as ignore_referrer
            ), null) 
        ignore nulls) as channels_in_session,
        countif(event_name = 'purchase') as conversions,
        sum(ecommerce.purchase_revenue) as conversion_value
    from
        `<your-project>.analytics_<your-dataset>.events_*`
    where
        _table_suffix between 
            format_date('%Y%m%d', date_sub(current_date(), interval 30 day))
            and format_date('%Y%m%d', date_sub(current_date(), interval 1 day))
    group by
        user_pseudo_id,
        session_id
),
-- get first campaign parameters from session and aggegrated metrics
traffic_acquisition as (
    select
        (select t.source from unnest(channels_in_session) as t where t.ignore_referrer is null order by t.event_timestamp asc limit 1) as source,
        (select t.medium from unnest(channels_in_session) as t where t.ignore_referrer is null order by t.event_timestamp asc limit 1) as medium,
        count(distinct session_id) as sessions,
        sum(conversions) as conversions,
        ifnull(sum(conversion_value), 0) as conversion_value
    from
        sessions
    group by
        1, 2
)

-- map source / medium to channel grouping using a user defined function (ignore campaign)
select
    *,
    <your-dataset>.channel_grouping(source, medium, null) as channel_grouping
from
    traffic_acquisition
```

![channel-grouping-test.PNG](https://static.stacktonic.com/channel_grouping_test_b4792e476b.PNG)
*Check your rules.*

## Some last tips

- Create a dedicated dataset for your UDF functions
- When you rerun the SQL, the UDF function will update.
- Create different channel grouping functions, based on the needs (using different levels of granularity for example)

*For more information on UDFs within BigQuery, see the [Google Documentation](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_function_statement).*

Make your life easier and define custom Channel Groupings in a reusable function (UDF) when working with Google Analytics 4 data in BigQuery. The channel grouping definition based on GA4 parameters source, medium and campaign can be reused with ease, without duplicating all the channel grouping logic in every query (win!).

Google Analytics 4 & BigQuery: Create custom Channel Groupings in a reusable SQL function

"Know your channels", a wise man once said. Get actionable insights on your channel performance by building custom attribution models using Google Analytics 4 data in BigQuery. This end-to-end tutorial will show you how to prepare the GA4 data and build rule based and data driven (Markov) models.

Build a Data Driven Attribution Model using Google Analytics 4, BigQuery and Python

**Over the years I've written a lot of Apache Airflow pipelines (DAGs). Be it in a custom Apache Airflow setup or a Google Cloud Composer instance. I've created a DAG file structure (boilerplate) so that it improved consistency and collaboration within my team, which I'm sharing in this tutorial.**

- Airflow 2.0 starter template, containing practical Airflow concepts and examples
- Go directly [to the code](#the-dag-starter-template--boilerplate)

## Concepts and examples

![Airflow 2.0 DAG example template](https://static.stacktonic.com/medium_airflow_2_0_example_dag_template_abeccf07c6.PNG)

The example / template contains a lot of concepts that I use regularly. I've provided a short description and reference to most of these concepts in the comments. To summarize:

- It shows how to import default and contributed **Operators** and **Hooks** (through providers, like AWS and Google Cloud Platform)
- Grouped the configuration settings of the DAG in the header section (comes in handy especially within large DAGs)
- **Scheduling** and **backfilling** (using the `catchup` parameter)
- Using **custom Python functions** with the new Airflow 2.0 Taskflow API (instead of the PythonOperator). Also see [the tutorial on the Airflow website](https://airflow.apache.org/docs/apache-airflow/stable/tutorial_taskflow_api.html). 
- Setting **task dependency** using >> operators. Also see [this section](https://airflow.apache.org/docs/apache-airflow/stable/concepts/dags.html#task-dependencies) of the Airflow documentation
- Using [dynamic tasks](https://airflow.apache.org/docs/apache-airflow/stable/concepts/dags.html#dynamic-dags): (a selection) of tasks can be placed in a loop, for example multiple different file imports.
- Using **TaskGroups**: combining a set of Tasks in a Taskgroup, which will also be grouped in the interface. Usefull in large DAGs.
- Using [macros](https://airflow.apache.org/docs/apache-airflow/stable/macros-ref.html) like executing date `{{ ds }}` or a modified one, like execution date minus 5 days `{{ macros.ds_add(ds, -5) }}`

## The DAG starter template / boilerplate

> The starter template was originally written for Apache Airflow versions 1.9.x. We've rewritten the code for Airflow 2.x and added Airflow 2.0 and added new functionality and concepts (like the Taskflow API). The starter template for Apache Airflow version 1.9.x can be found here.

*View code [on GitHub](https://github.com/stacktonic-com/apache-airflow-dag-example-template)*

``` python
############################################################
# Author    Krisjan Oldekamp / Stacktonic.com              #
# Email     krisjan@stacktonic.com                         #
############################################################

# Libraries
import json
import os
from datetime import datetime, timedelta
import pendulum

# Airflow
from airflow.models import DAG, Variable
from airflow.utils.task_group import TaskGroup
from airflow.utils.dates import days_ago
from airflow.decorators import task

# Default Airflow operators 
# https://airflow.apache.org/docs/apache-airflow/stable/concepts/operators.html

from airflow.operators.bash import BashOperator
from airflow.operators.dummy import DummyOperator

# Contributed Airflow operators (providers) 
# https://airflow.apache.org/docs/apache-airflow-providers/operators-and-hooks-ref/index.html

#from airflow.providers.amazon.aws.transfers.gcs_to_s3 import GCSToS3Operator
#from airflow.providers.amazon.aws.transfers.local_to_s3 import LocalFilesystemToS3Operator

from airflow.providers.google.cloud.operators.bigquery import BigQueryExecuteQueryOperator
from airflow.providers.google.cloud.transfers.gcs_to_bigquery import GCSToBigQueryOperator
from airflow.providers.google.cloud.transfers.bigquery_to_gcs import BigQueryToGCSOperator
from airflow.providers.google.cloud.transfers.local_to_gcs import LocalFilesystemToGCSOperator

############################################################
# DAG settings
############################################################

LOCAL_TZ = pendulum.timezone("Europe/Amsterdam")

DAG_NAME = "stacktonic_example_dag" # DAG name (proposed format: lowercase underscore). Should be unique.
DAG_DESCRIPTION = "Example DAG by Krisjan Oldekamp / stacktonic.com"
DAG_START_DATE = datetime(2021, 10, 15, tzinfo=LOCAL_TZ) # Startdate. When setting the "catchup" parameter to True, you can perform a backfill when you insert a specific date here like datetime(2021, 6, 20)
DAG_SCHEDULE_INTERVAL = "@daily" # Cron notation -> see https://airflow.apache.org/scheduler.html#dag-runs
DAG_CATCHUP = False # When set to true, DAG will start running from DAG_START_DATE instead of current date
DAG_PAUSED_UPON_CREATION = True # Defaults to False. When set to True, uploading a DAG for the first time, the DAG doesn't start directly 
DAG_MAX_ACTIVE_RUNS = 5 # Configure efficiency: Max. number of active runs for this DAG. Scheduler will not create new active DAG runs once this limit is hit. 

############################################################
# Default DAG arguments
############################################################

default_args = {
    "owner": "airflow",
    "start_date": DAG_START_DATE,
    "depends_on_past": False,
    "email": Variable.get("email_monitoring", default_var="<FALLBACK-EMAIL>"), # Make sure you create the "email_monitoring" variable in the Airflow interface
    "email_on_failure": True,
    "email_on_retry": False,
    "retries": 2, # Max. number of retries before failing
    "retry_delay": timedelta(minutes=60) # Retry delay
}

############################################################
# DAG configuration (custom)
############################################################

SOME_CUSTOM_CONFIG = "yes"

DYNAMIC_TASKS =  [{
    "account": "123",
    "some_setting": True
},{
    "account": "456",
    "some_setting": False
}]

############################################################
# Python functions (custom) using the Taskflow API decorators (@task)
############################################################

@task
def python_function_with_input(value: str):
    print("Custom Python function, print whatever you want!")
    print(value)

############################################################
# Repeatable Airflow Operator functions (for use in dynamic tasks)
############################################################

def dynamic_operator_task(i):
    return DummyOperator(
        task_id="dynamic_task_" + DYNAMIC_TASKS[i]["account"]
    )

############################################################
# Main DAG
#############################################################

# Create DAG
with DAG(
    DAG_NAME,
    description=DAG_DESCRIPTION,
    schedule_interval=DAG_SCHEDULE_INTERVAL,
    catchup=DAG_CATCHUP,
    max_active_runs=DAG_MAX_ACTIVE_RUNS,
    is_paused_upon_creation=DAG_PAUSED_UPON_CREATION,
    default_args=default_args) as dag:

    # Start
    start = DummyOperator(
        task_id="start")

    # Bash Operator
    print_start_bash = BashOperator(
        task_id="print_start_bash",
        bash_command="echo 'hello {{ ds }}'") # Using a macro for execution date, see other macros https://airflow.apache.org/docs/apache-airflow/stable/macros-ref.html

    # Wait till finished
    wait_till_finished = python_function_with_input("All finished")

    # Complete
    complete = DummyOperator(
        task_id="complete")

    # Define a taskgroup so tasks will be grouped in the interface -> usefull when you have a lot of repeated task sequences for example.
    with TaskGroup(group_id='task_group_with_two_tasks') as task_group_with_two_tasks:
        # Set order of execution within taskgroup (see also https://airflow.apache.org/concepts.html#bitshift-composition)
        python_function_with_input('taskgroup task #1') >> python_function_with_input('taskgroup task #2')

    # Set task depencency using >> (see also https://airflow.apache.org/docs/apache-airflow/stable/concepts/dags.html#task-dependencies)
    start >> print_start_bash

    # Add dynamic (repeating) tasks (https://airflow.apache.org/docs/apache-airflow/stable/concepts/dags.html#dynamic-dags), looping the dicts from DYNAMIC_TASK and defining order of execution
    for i, dct in enumerate(DYNAMIC_TASKS):

        print_start_bash \
        >> python_function_with_input(dct["account"]) \
        >> [python_function_with_input("Execute together. This is the execution date: {{ ds }} #1"), python_function_with_input("Execute together. This is the execution date minus 5 days: {{ macros.ds_add(ds, -5) }} #2")] \
        >> wait_till_finished \
        >> task_group_with_two_tasks \
        >> dynamic_operator_task(i) \
        >> complete
```

## Learnings and considerations

Lastly, some learnings and considerations;

- Use connectors to store sensitive information like keys and passwords
- Split up your DAGs when they become to large or to complex
- DRY: Use dynamic tasks (or DAGs) when performing the same flow but with different settings (e.g. file imports for different countries)
- When using the same (custom) functionality in multiple DAG files, try to write generic, reusable functions
- Setup alert monitoring properly (tip: Slack of Microsoft Teams notifications)

Starting to write DAGs in Apache Airflow 2.0? Some useful examples and our starter template to get you up and running quickly.

Apache Airflow 2.0 Examples - A basic DAG template for any project

Cloud (data) platforms such as AWS, Google Cloud or Azure enable you to build a marketing data platform that doesn't only ingest and join data, but can also produce actionable insights and activate data in other tools and platforms. 

Building a cloud Marketing Data Platform

Google Analytics 4 is more than just a measurement and reporting tool. The event data exports to Google BigQuery (available in the free version) and the GA4 API brings a ton of new possibilities. Think of advanced analysis, joining data with other sources, building prediction models or integrate with other systems.

Google Analytics 4 Advanced

Increased privacy concerns, legislation and browser updates affecting (third-) party cookies is resulting in a new era where users regain control over their data and organizations should rethink their customer and marketing strategy. This will impact a lot of area's such as data collection, advertising, first-party data strategy, legal and UX. 

Privacy, cookies & first-party data

I'm the founder of Stacktonic and co-founder of [Turntwo](https://turntwo.com) (digital growth agency). My area of expertise is in Marketing Technology & Analytics, and I've been fascinated by the digital world since I was young. My educational background is in business and marketing, and I quickly realized that the combination of marketing, technology and data was where my interests lay. Throughout my career, I've focused on projects that involve these topics.

I started by professional career at a digital marketing agency called Merkle Netherlands, where I eventually became the Marketing Technology lead. In 2022, after 9 years at Merkle, I decided to pursue my entrepreneurial dreams, so I became an independent freelance consultant and shortly after that co-founded Turntwo in 2023 together with a former colleague.

## Stacktonic & Turntwo - Background

Stacktonic started as a platform to share knowledge in the form of hands-on marketing technology, data and analytics related tutorials. Continuing doing that, Stacktonic is also part of Turntwo, a digital growth agency. At [Turntwo](https://turntwo.com) we connect business strategy to marketing, data & technology. And we're interested in your challenge!

## Getting stuff done

Leveraging business, digital marketing and technical experience, I can assist on strategic level but are also fully able to turn strategy into action. I can lead or assist projects and teams, from ideation to execution, in a hands-on manner. Talking the same language as management, marketers and developers I can operate efficiently and maximize results.


Krisjan Oldekamp

Founder / Marketing Technology & Analytics consultant

Stacktonic & Turntwo - Background

Getting stuff done

Interested?