"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

 **The *dbt* (data build tool) framework has gained much popularity over the last few years. It's an excellent tool to transform your data, but also helps with setting up a data warehouse workflow (orchestration, dependencies, environments, documentation etc). This tutorial will provide you with an example *dbt* project repository, using the Google Analytics 4 BigQuery dataset, and walks you through the most important parts of the setup, so you can bootstrap your own project quickly.**

- A starter **dbt project example**, using BigQuery as a data warehouse backend.
- Using the Google Analytics 4 event data export in BigQuery.
- **Transforming Google Analytic 4 event level data** to sessions.
- Create a user mapping table and grouping session data to customer profiles.
- Create the base for **marketing reports**, like a channel performance report.
- Tips regarding **project structure**, **incremental models** and workflow.

## What is dbt?

I would recommend you check out the [dbt introduction articles](https://docs.getdbt.com/docs/introduction) first, but for starters;

*dbt (data build tool) enables analytics engineers to transform data in their warehouses by simply writing select statements. dbt handles turning these select statements into tables and views. dbt does the T in ELT (Extract, Load, Transform) processes – it doesn’t extract or load data, but it’s extremely good at transforming data that’s already loaded into your warehouse.*

These are the most important features;

- **Orchestration**: *dbt* automatically detects dependencies (lineage) between models, generates a dependency graph (DAG) and can executes SQL (models) in the correct order.
- **Deployment**: Support for development and production environments, track history using the *dbt* repository, forces you to use a code repository basically.
- **Testing & integration**: *dbt* enables you to set up test easily (missing data, data formats etc) and performs integration tests.
- **Flexibility**: Easily recreate or backfills tables.
- **Documentation**: *dbt* automatically generates documentation / data catalog
- **DRY**: Reusable chunks of code in the for of macros

![dbt-overview-datawarehouse.PNG](https://static.stacktonic.com/medium_dbt_overview_datawarehouse_dd4909e027.PNG)

And above all, *dbt* doesn't really have a steep learning curve.

## 1. The example project

> **Update Augusts 2022**: Recently, an open-source [dbt-ga4](https://github.com/Velir/dbt-ga4) package was released by [Adam Ribaudo](https://twitter.com/adamribaudo) and others. The dbt package transforms the GA4 dataset into report-ready dimensional models. This article still helps you to get to know dbt using the GA4 dataset, but we don't provide all the transformations the package does. So. check this one out!

First of all, the example project **can be found [here on GitHub](https://github.com/stacktonic-com/stacktonic-dbt-example-project)**. I recommend you check out the *dbt* documentation first and understand the concepts of *dbt* in general. This project can help to master (and how to apply) the concepts. Besides you can use this project to bootstrap your project.

The project has the following characteristics:

** Data warehouse **

The example project uses Google BigQuery as a data warehouse backend.

**Sources**

The projects transforms the default [Google Analytics 4 BigQuery exports](https://support.google.com/analytics/answer/7029846?hl=en&ref_topic=9359001). Google Analytics exports to [date sharded tables](#4-ga4-source-tables-and-incrementality) (and this is something that you can't change currently).

** Transformations **

The project will transform the raw GA4 data into the following models:

- A staging model of the Google Analytics 4 data source (where you can rename and recast your data sources into a consistent format).
- Intermediate model to aggregate event level GA4 data to sessions (in an incremental table, so the complete GA4 dataset doesn't have to be transformed completely on every run). 
- Intermediate model to build a user mapping table out of all the available user identifiers in GA4 (based on [this article](https://stacktonic.com/article/create-a-user-mapping-table-based-on-the-google-analytics-4-big-query-dataset))
- Create a core user (customer) model, aggregating online behavioral data to user (customer) level. Can be used to build a [360 customer view](https://stacktonic.com/article/enrich-a-single-customer-view-with-google-analytics-4-big-query-data) for example)
- Create the base for marketing reports (channel performance report on day level).

## 2. Prerequisites

You can configure the example project to use your own settings and data warehouse (BigQuery). For that, you have te setup at least the following:

-  Created a Google Cloud Project and enabled the Google Analytics 4 exports. The project "as is" is configured to use the [Google Analytics 4 example dataset](https://support.google.com/analytics/answer/10937659?hl=en#zippy=%2Cin-this-article).
- [Installed *dbt*](https://docs.getdbt.com/dbt-cli/installation) or use the cloud hosted version of *dbt* (free for 1 developer seat, benefit that is has a built-in / dedicated IDE). 
- I've used dbt Cloud, were you can configure the BigQuery dbt profile, but you could also [set this up manually](https://docs.getdbt.com/reference/warehouse-profiles/bigquery-profile)
- Check and adjust the settings in the following files: `dbt_project.yml` and `.dbt/profiles.yml` (especially if your not using dbt Cloud)

## 3. Project structure

You're free to define your own structure, but I tried to keep the proposed setup by *dbt* in mind, so there is some logical structure you project stays manageable when it grows over time. The following articles regarding style and project structure are a good read:

- [dbt best practices](https://docs.getdbt.com/docs/guides/best-practices)
- [How dbt structures their projects](https://discourse.getdbt.com/t/how-we-structure-our-dbt-projects/355)

In the example project;

- Intermediate models are placed within a subfolder of the most relevant mart (you could create a separate top-level folder for intermediate tables as well)
- Intermediate tables and views are placed into a dedicated BigQuery dataset
- The `schema` and `dataset` parameters are interchangeable. Since BigQuery uses the last term, `dataset` is used throughout the project. 

**Folder structure**

![dbt-folder-structure.PNG](https://static.stacktonic.com/dbt_folder_structure_87fc340ced.PNG)

**BigQuery Datasets**

*dbt* will create and populate multiple datasets within BigQuery. Next to adding some structure to your project, splitting up your models enables you (more easily) to hide certain datasets for all users in your organization.

| Dataset | Description |
| --- | --- |
| `<prefix>_staging` | Staging data models (renaming / recasting source tables so sources are in a consistent format) | 
| `<prefix>_core` | Core data warehouse models |
| `<prefix>_marketing` | Marketing data warehouse models |
| `<prefix>_int` | All intermediate tables (e.g. joining and grouping different sources). Placed in a separate dataset so you can hide these tables from certain the user(groups) |
| `<prefix>_utils` | Dataset holding utilities, like (reusable) User Defined Functions (UDFs) |

*The development environment will create the same datasets, but with a development prefix*

![dbt-bigquery-structure.PNG](https://static.stacktonic.com/dbt_bigquery_structure_bbb1d91e10.PNG)

*dbt BigQuery dataset structure*

## 4. GA4 source tables and incrementality

The GA4 tables are date sharded (`events_YYYYMMDD`) instead of partitioned. In terms of performance (and costs), it's important to query these tables correctly.

> Google [recommends](https://cloud.google.com/bigquery/docs/partitioned-tables#dt_partition_shard) using partitioned tables instead of sharded tables (like Google the Google Analytics 4 tables). So it strange that that Google Analytics 4 uses date sharding instead of partitioning.

If you use a date column to select incremental amounts of data (and not using the `_table_suffix` pseudo column), every time you select an incremental amount data, it will query all the date sharded tables of the Google Analytics 4 exports. Not that of a big problem when you don't have a lot of data, but it is a problem (in terms of cost and performance) when handling large datasets. 

Some solutions;

1. Ideally, partitioned tables are to way to go. However we have no control over this data source, so you could materialize the date sharded tables in a date (day) partitioned table yourself. However, since the the Google Analytics 4 dataset can be rather large, it will generate extra costs because your duplicating the dataset, so it might not be the logical choice in some situations.
2. Filter on `_table_suffix` instead of a date column (see [this article](https://docs.getdbt.com/reference/resource-properties/identifier)).
3. Create a macro to insert `execution_date` into the models alias parameter ([article from The Telegraph team](https://medium.com/the-telegraph-engineering/dbt-a-new-way-to-handle-data-transformation-at-the-telegraph-868ce3964eb4))

In our project, we've selected option #2, so we don't have to duplicate our data. If there is any better method (now or in the future) please let me know and I will update the project. Example method below or see [this file](https://github.com/stacktonic-com/stacktonic-dbt-example-project/blob/master/models/marts/core/intermediate/int_events_sessions__grouped.sql) in the example project:

*In the example below, the `table_suffix` column refers to the BigQuery `_table_suffix` pseudo column*

```sql
 {{
  config(
    materialized = 'incremental',
    partition_by = {'field': 'session_start_at', 'data_type': 'timestamp'},
    incremental_strategy = 'insert_overwrite',
    tags=['incremental']
  )
}}

-- google analytics 4 events
with events as (
    
    select 
        * 
    from {{ ref('stg_google_analytics__events') }}

    
    {% if var('execution_date') != 'notset' %}
        
        where
            -- specific date set by variable (using the _table_suffix_ pseudo column for performance)
            table_suffix = '{{ var('execution_date') }}'

    {% elif is_incremental() %}

        where
            -- incremental data (using the _table_suffix_ pseudo column for performance)
            (table_suffix between format_date('%Y%m%d', date(_dbt_max_partition))
                and format_date('%Y%m%d', date_sub(current_date(), interval 1 day)))

    {% endif %}
)
```

## 5. Execution & scheduling

You can use the following commands to run all or specific models.

``` bash
# Run all models
dbt run

# Run a specific model
dbt run --select int_events_sessions__grouped

# Run models with a specific tag
dbt run --select tag:incremental

# Run all models and rebuild tables
dbt run --full-refresh

# Run all models for a specific date -> replaces (date) partitions for incremental models, for example the incremental session models build on top the GA event exports
dbt run --vars '{execution_date: 20211025}'

```

The relation between the models (lineage):

![dbt-google-analytics-4-lineage.PNG](https://static.stacktonic.com/medium_dbt_google_analytics_4_lineage_88526232af.PNG)

** Scheduling **

You can run dbt both locally as in dbt Cloud. This last options has a built-in scheduler to run your *dbt* commands. Another good option is to use Apache Airflow to schedule *dbt* model execution. Benefit is that you have more insights into model execution, tracks history and you can (re)run specific models with ease. You can check out the following articles:

- Astronomer https://www.astronomer.io/blog/airflow-dbt-1
- https://towardsdatascience.com/creating-an-environment-with-airflow-and-dbt-on-aws-part-3-2789f35adb5d
- https://analyticsmayhem.com/dbt/schedule-dbt-models-with-apache-airflow/

## 6. Other features

Lastly, the example project has some other small features and capabilities.

- **Profiles**: Configuration for a development and production environment (`.dbt/profiles.yml`).
- **Model configuration**: Default model configuration (e.g. dataset / suffixes / folders) in [`dbt_profile.yml`](https://github.com/stacktonic-com/stacktonic-dbt-example-project/blob/master/dbt_project.yml)
- **Documentation**: All fields in the final dimension and fact tables are documented (within the `core.yml` and `marketing.yml` files) 
- **Variables**: `start_date` can be used to define the start date of your data and `execution_date` can be used to rebuild or backfill (incremental) models for a specific date (and overwrite the old partitions). `dataset_udf` is used to configure the BigQuery dataset where the UDFs (macros) are stored.
- **Macros**: There are several macros available in the project. Within the `/macros/udf` folder there are several macros that create user defined functions (UDFs), such as a channel grouping function. The `create_udfs.sql` macro runs all the UDF macros when the model is run (so the UDFs are up-to-date).
- **Tags**: Incremental models are tagged with `incremental`, daily incremental tables with `daily`.
- **Testing**: [Freshness settings](https://github.com/stacktonic-com/stacktonic-dbt-example-project/blob/master/models/staging/google_analytics/src_google_analytics.yml) for the GA4 source table is setup and for the fact and dimensions tables, [checks](https://github.com/stacktonic-com/stacktonic-dbt-example-project/blob/master/models/marts/core/core.yml) on unique identifiers are build in.

## Closing thoughts

The goal of this example projects is to bootstrap your project quickly and show you some of dbt's features how to apply them in your project. If you have any suggestions and improvements, please let me know!

The dbt (data build tool) framework has gained much popularity over the last few years. It&#x27;s an excellent tool to transform your data, but also helps with setting up a data warehouse workflow (orchestration, dependencies, environments, documentation etc). This tutorial will provide you with an example dbt project repository, using the Google Analytics 4 BigQuery dataset, and walks you through the most important parts of the setup, so you can bootstrap your own project quickly.

Google Analytics, BigQuery & dbt: A dbt example project

**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?