**The recent addition of the *Firestore* Lookup variable in GTM Server opens up a whole new world of possibilities in terms of data enrichment. But how to get data into the *Firestore* database? This tutorial will show you how to load data from *BigQuery* into *Firestore* (including code samples).**

Contents

- Set up a Google Workflow that exports BigQuery data to a Storage Bucket as JSON.
- Set up a Google Cloud Function that uploads the JSON file to Google Firestore.
- Connect Firestore to Google Tag Manager Server and use the Firestore Lookup Variable

## Google Tag Manager Server & Firestore

The release of the Firestore Lookup and [asynchronous variables](https://www.simoahava.com/analytics/asynchronous-variables-server-side-google-tag-manager/) within GTM Server variables opens up a whole new world of possibilities.

By using the Firestore Lookup variable (or the asynchronous variable to lookup data from some other system) one could think of the following use cases:

- Enrich product data / transactions (e.g. adding margins or product attributes)
- Enrich user data (e.g. adding user properties, segments, CLV scores)
- User-ID enrichment / identity stitching (retrieve alternative identifiers)

The `Firestore API` that powers the *Firestore Lookup variable*, can also be leveraged to write data to Firestore. Use cases:

- A persistent storage system (e.g. storing user-level data over different events)
- Enrich user data (e.g. adding user properties, segments, CLV scores)
- *And much more*

Many clients I work for, store the necesarry data for the use cases mentioned above in `Google BigQuery` (think of single user profiles or product feeds). However, BigQuery is not suitable to connect directly to GTM Server.

> A while ago I wrote an [comparable article](https://stacktonic.com/article/create-a-user-profile-and-recommender-service-using-big-query-redis-and-gtm-server) explaining how to connect a Redis cache to GTM Server. However the Firestore method seems the way to go now (although the Redis / GTM Server connection can be optimized using asyncrhoniys variables)

## BigQuery vs. Firestore

BigQuery is meant as an analytics database, not as a database that can perform a lot of reads and writes in low latency environments. But what databases do? 

The following graph is a nice representation of the different databases and use cases. (of course biased to the options available in the Google Cloud)

![which-database-google-cloud.jpeg](https://static.stacktonic.com/medium_which_database_google_cloud_e5031028bd.jpeg)
*[Google Cloud database options](https://cloud.google.com/blog/topics/developers-practitioners/your-google-cloud-database-options-explained). Image by [thecloudgirl.dev](https://thecloudgirl.dev)*

There a multiple contenders for our use case (think of Redis / Memcache used in a [related article](https://stacktonic.com/article/create-a-user-profile-and-recommender-service-using-big-query-redis-and-gtm-server)), but in this case we'll opt for Firestore.

## The solution - Google Workflows!

The solution we've build and are sharing in this article leverages a `Google Worfklow` and a `Google Cloud Function`. 

Google Workflow enables you to set up an automation with just a "simple" `.yaml` file (also see code below). The Google Workflow will:

1. Export the specified query to a new BigQuery table 
2. Export the table to a Storage Bucket in JSON format
3. Creates a message in a Google Pub/Sub topic (holding the configuration for the Cloud Function to run like the bucket name and project-ID)

Lastly, a Google Cloud Function will trigger, opens the JSON export in the Storage Bucket and loops the rows into the Firestore database.

[![export-google-bigquery-to-firestore-plain.PNG](https://static.stacktonic.com/medium_export_google_bigquery_to_firestore_plain_e1c86f907a.PNG)](https://static.stacktonic.com/export_google_bigquery_to_firestore_plain_e1c86f907a.PNG)

> Read more about [Google Workflows](https://stacktonic.com/article/google-cloud-workflows-export-data-from-big-query-to-cloud-storage) in my other tutorial.

## Prerequisites

So let's start! 

Before proceeding, make sure you have:

- An active Google Cloud Project
- Some data within Google BigQuery, ready to send Firestore
- A query that fetches the data you want to send to Firestore (and think of a unique key you want to match with event data in GTM Server)
- A Google Cloud Storage bucket (to store the BigQuery exports)

And don't forget to enable the following services in your GCP project (and have some basic knowledge about the services):

- Enable Google Workflows
- Enable Google Cloud Functions
- Enable Google Pub/Sub

## Step 1: Create the Google Cloud Function

First step, create a Cloud Function that triggers on a Pub/Sub topic. Also create the Pub/Sub topic (can be done directly when creating the Cloud Function). 

- **Environment:** 1st gen
- **Trigger type:** Cloud Pub/Sub
- **Memory Allocated:** 1GB (depends on the amount of data you want to process, so adjust accordingly).
- **Timeout:** Depends on the file size you want to process (see limits). For files that are not that large, you could start with the default 60 seconds.
- **Runtime:** Node.js 16
- Attach a Service Account that has access to the Google Cloud Bucket + Firestore + Pub/Sub

Configure the following roles for the Service Account that is running the the Cloud Function (and is also added to the project where Firestore is enabled)

- Storage Admin
- Cloud Datastore User
- Pub/Sub Publisher

Copy paste the JS code below ([also available in the GitHub repo](https://github.com/stacktonic-com/gcp-export-bigquery-to-firestore/tree/master/cloudfunction-cloudstorage-to-firestore))

> You don't have to adjust or configure anything in the scripts itself. The Google Workflow will pass the configuration settings (like the bucket / path of the JSON export) in the Pub/Sub message.

**package.json**

```json
{
    "name": "gcf-cloudstorage-to-firestore",
    "version": "1.0.0",
    "dependencies": {
        "@google-cloud/storage": "^5.20.3",
        "firebase-admin": "^10.2.0",
        "split": "^1.0.1"
    }
}
```

**index.js**

```javascript
'use strict';

const admin = require('firebase-admin');
const {Storage} = require('@google-cloud/storage');
const split = require('split');

/**
 * Triggered from a Pub/Sub message.
 *
 * @param {!Object} event Event payload.
 * @param {!Object} context Metadata for the event.
 */
exports.loadCloudStorageToFirestore = async(event, context) => {

    const pubSubMessage = event.data ? Buffer.from(event.data, 'base64').toString(): '{}';
    const config = JSON.parse(pubSubMessage);

    console.log(config)

    if (typeof config.projectId != 'undefined') {

        const projectId = config.projectId;
        const bucketName = config.bucketName;
        const bucketPath = config.bucketPath;
        const firestoreCollection = config.firestoreCollection;
        const firestoreKey = config.firestoreKey;

        console.log(`Initiated new import to Firebase: gs://${bucketName}/${bucketPath}`)

        // Init Firebase
        if (admin.apps.length === 0) {
            admin.initializeApp({ projectId: projectId })
        }

        // Init Storage
        const storage = new Storage()
        const bucket = storage.bucket(bucketName);
        const file = bucket.file(bucketPath);

        let keysWritten = 0;

        try {
        
            // TO-DO: Remove old records

            // Read file and send to Redis
            file.createReadStream()
                .on('error', error => reject(error))
                .on('response', (response) => {
                    // connection to GCS opened
                }).pipe(split())
                .on('data',  async record => {
                    if (!record || record === "") return;
                        keysWritten++;

                        const data = JSON.parse(record);
                        const key = data[firestoreKey].replace(/[/]|\./g, '');

                        try {
                            await admin.firestore().collection(firestoreCollection).doc(key).set(data)
                        } catch(e) {
                            console.log(`Error setting document:  ${e}`);
                        }
                })
                .on('end', () => {
                    console.log(`Successfully written ${keysWritten} keys to Firestore.`);
                })
                .on('error', error => reject(error));
        
        } catch(e) {
            console.log(`Error importing ${bucketPath} to Firestore: ${e}`);
        }
    }

};
```
*Some parts taken from this [Cloud Function](https://futurice.com/blog/bigquery-to-memorystore).*

## Step 2: Create the Google Workflow

Create a new Google Workflow and make sure the Service Account you assigned to the Workflow has access to the following services:

- Google BigQuery (*BigQuery Data Editor + BigQuery Job User*)
- Google Storage (*Storage Admin*)
- Google Cloud Scheduler (to regularly execute the Workflow)

Copy / paste the code from below (or from this [GitHub repository](https://github.com/stacktonic-com/gcp-export-bigquery-to-firestore/tree/master/workflow-bigquery-to-cloudstorage)

> This time, **you do need to add your configuration values** (like `project_id` or `gcs_bucket`).

The GCP console will render a visual representation of your Workflow and should look something like this:

![google-workflow-bigquery-to-cloud-storage-json.PNG](https://static.stacktonic.com/google_workflow_bigquery_to_cloud_storage_json_07da6e2aba.PNG)

**workflow-bigquery-to-cloudstorage.yaml**

```yaml
- init:
    assign:
    - project_id: "<your-project-id>"
    - bq_dataset_export: "<your-bq-dataset-for-export-table>"
    - bq_table_export: "<your-bq-tablename-for-export-table>"
    - bq_query: >
            select
                user_id,
                device_first,
                channel_grouping_first
            from
                `stacktonic-cloud.st_core.dim_customer`
    - gcs_bucket: "<your-export-bucket>"
    - gcs_filepath: "firestore-export/firestore-export.json"
    - pubsub_topic: "<your-pubsub-topic-name>"
    - pubsub_message: {
            "projectId": "<your-firestore-project-id>",
            "bucketName": "<your-export-bucket>",
            "bucketPath": "firestore-export/firestore-export.json",
            "firestoreCollection": "<your-firestore-collection>",
            "firestoreKey": "<your-key-to-use-as-firestore-document-id>"
        }
- bigquery-create-export-table:
                call: googleapis.bigquery.v2.jobs.insert
                args:
                    projectId: ${project_id}
                    body:
                        configuration:
                            query:
                                query: ${bq_query}
                                destinationTable:
                                    projectId: ${project_id}
                                    datasetId: ${bq_dataset_export}
                                    tableId: ${bq_table_export}
                                create_disposition: "CREATE_IF_NEEDED"
                                write_disposition: "WRITE_TRUNCATE"
                                allowLargeResults: true
                                useLegacySql: false

- bigquery-table-to-gcs:
    call: googleapis.bigquery.v2.jobs.insert
    args:
        projectId: ${project_id}
        body:
            configuration:
                extract:
                    compression: NONE
                    destinationFormat: "NEWLINE_DELIMITED_JSON"
                    destinationUris: ['${"gs://" + gcs_bucket + "/" + gcs_filepath}']
                    sourceTable:
                        projectId: ${project_id}
                        datasetId: ${bq_dataset_export}
                        tableId: ${bq_table_export}
- publish_message_to_pubsub:
    call: googleapis.pubsub.v1.projects.topics.publish
    args:
      topic: ${"projects/" + project_id + "/topics/" + pubsub_topic}
      body:
        messages:
          - data: ${base64.encode(json.encode(pubsub_message))}
```

**Now run the Google Workflow!** 

The workflow should export the BigQuery query results (yes!) to Cloudstorage and load it into Firestore:

![google-firestore-example.PNG](https://static.stacktonic.com/medium_google_firestore_example_43d3520240.PNG)

## Step 3: Connect GTM Server with Firestore

The last step, make sure the GTM environment can connect with the Firebase database.  Simo Ahava already wrote a great article about [Firestore and GTM Server](https://www.simoahava.com/analytics/enrich-server-side-data-with-cloud-firestore/), so check out that article. 

Some remarks:

- **Make sure you set up Firestore in Native mode (can't be undone)**
- When you run GTM Server in App Engine, make sure the Service Account that runs App Engine (probably App Engine default service account) has access to Firestore (*Datastore User*).
- If not in the same project, add the service account that runs App Engine to the project where Firestore lives.

## Limits & cost

Some last considerations:

**Limits**

This setup is using full loads:

- Only new records are added or existing records are updated (so old records are not deleted).
- This process can be optimized by using delta’s (for example set up a process in BigQuery calculating the daily delta’s) or deleting all the records in the Firestore collection first.

Possible issues with large datasets:
- Google Cloud Functions timeout limit (configurable, but a maximum runtime of 9 minutes)
- Google Cloud Function memory limit (although files from Cloud Storage are not fully loaded into memory but processed streaming)

**Costs**

Google Workflows and Cloud Functions are serverless. Likely these will not incur a lot of costs since the Workfow and Function probably won't be executed a lot. Firestore heavily depends on volume + there is also a free tier. Check the [official pricing overview](https://cloud.google.com/firestore/pricing) for the full overview.

The recent addition of the Firestore Lookup variable in GTM Server opens up a whole new world of possibilities in terms of data enrichment. But how to get data into the Firestore database? This tutorial will show you how to load data from BigQuery into Firestore (including code samples).

large_google-bigquery-to-firestore.PNG

medium_google-bigquery-to-firestore.PNG

small_google-bigquery-to-firestore.PNG

thumbnail_google-bigquery-to-firestore.PNG

google-bigquery-to-firestore.PNG

Export BigQuery data to Google Firestore & GTM Server

**For a lot of data projects Apache Airflow is the orchestration tool of choice. However, Airflow can be a bit overkill for smaller projects and tasks. One alternative is Google Workflows where you can create orchestration flows using simple *.yaml* files. In this tutorial I will show you how to export data from BigQuery to a Google Cloud Storage bucket using Google Workflows.**

Contents:

- Introduction Google Workflows
- Workflow example: Exporting Google BigQuery data to Google Cloud Storage as a CSV (two methods).

## Why Google Workflows?

Google Workflows is a lightweight and serverless workflow orchestration service where you can connect Google Cloud and external services (using APIs). As mentioned, Apache Airflow is used in a lot of projects, but  it can be a bit overkill since you have to maintain the Airflow cluster (or pay to maintain it). It can also have some overlap with other tools like *dbt*. 

 Triggered by a post from Robert Sahlin related to [modern data workflow orchestration](https://robertsahlin.com/modern-data-workflow-orchestration-part-1/) and [The Unbundling](https://blog.fal.ai/the-unbundling-of-airflow-2/) of Airflow by Gorkem Yurtseven I've dicided to give [Google Workflows](https://cloud.google.com/workflows) a try for some workflows mainy related to Google Cloud. Currently, I'm using Workflows successfully in some smaller projects.

## Google Workflows basics

Basically, Google workflows uses a *.yaml* file where you can build your workflow. The file contains tasks that can be executed in order. You can upload this *.yaml* file and Google will create a visual representation of your flow. Flows can be scheduled using Cloud Scheduler, executed manually or using an API. Logs are also collected.

 There are a bunch of connectors / APIs available already and the list is growing. For an overview [checkout the official connector list](https://cloud.google.com/workflows/docs/reference/googleapis). Make sure you also check out the [introduction by Google](https://cloud.google.com/workflows/docs/overview).

> As of writing, Google Workflows is fairly new, so example code and documentation is somewhat limited.

Exporting from Google BigQuery to a Cloud Storage bucket is an operation that is present in a lot of pipelines I developed. So I'm taking this operation to build an example Workflow. Two methods:

## Method 1: BigQuery to Cloud Storage (intermediate table)

The first method will create an intermediate table from your query, and is exporting that table as a CSV to Google Cloud Storage. Adding this step enables you to export large amounts of data into a single file (max 1GB to a single file). If you have a limited amount of data or it doesn't matter that data is always exported to multiple CSV files / file parts, you could also use option 2.

**Workflow steps**

1. Run a query a save the result in an intermediate table
2. Export the intermediate table to a CSV

![google-workflows-bigquery-to-google-cloud-storage-option-1.png](https://static.stacktonic.com/google_workflows_bigquery_to_google_cloud_storage_option_1_78241bbfc8.png)

**Workflow .yaml code**

```yaml
- init:
    assign:
    - project_id: "<your-project-id>"
    - dataset_export: "<your-bigquery-dataset>"
    - table_export: "<your-bigquery-table>"
    - query: >
        select * from <your-table>
    - bucket: "<your-cloud-bucket>"
    - folder: "<your-export-folder-within-cloud-bucket>"
    - filename: ${string(sys.now()) + "-<your-filename>.csv"}

- bigquery-create-export-table:
                call: googleapis.bigquery.v2.jobs.insert
                args:
                    projectId: ${project_id}
                    body:
                        configuration:
                            query:
                                query: ${query}
                                destinationTable:
                                    projectId: ${project_id}
                                    datasetId: ${dataset_export}
                                    tableId: ${table_export}
                                create_disposition: "CREATE_IF_NEEDED"
                                write_disposition: "WRITE_TRUNCATE"
                                allowLargeResults: true
                                useLegacySql: false

- bigquery-table-to-gcs:
    call: googleapis.bigquery.v2.jobs.insert
    args:
        projectId: ${project_id}
        body:
            configuration:
                extract:
                    compression: NONE
                    destinationFormat: "CSV"
                    destinationUris: ['${"gs://" + bucket + "/" + folder + "/" + filename}']
                    fieldDelimiter: ","
                    printHeader: true
                    sourceTable:
                        projectId: ${project_id}
                        datasetId: ${dataset_export}
                        tableId: ${table_export}

```

## Method 2: BigQuery to Cloud Storage (direct)

This method will export a query result directly to a CSV. However, it will create multiple file parts in most situations. 

**Workflow .yaml code**

```yaml
- init:
    assign:
    - project_id: "<your-project-id>"
     - query: >
        select * from <your-table>
    -  - bucket: "<your-cloud-bucket>"
    - folder: "<your-export-folder-within-cloud-bucket>"
    - filename: "<your-filename)"
- bigquery-table-to-gcs:
    call: googleapis.bigquery.v2.jobs.query
    args:
      projectId: ${project_id}
      body:
        query: ${"EXPORT DATA OPTIONS( uri='gs://" + bucket + "/" + folder + "/'||current_date()||'-" + filename + "-part-*.csv', format='CSV', overwrite=true, header=true) AS " + query}
        useLegacySql: false
```

## Closing thoughts

Google Workflows is an excellent service for small projects and tasks. But it can also be used in larger projects. You can create Workflows using the interface, the CLI / API or [Terraform](https://cloud.google.com/workflows/docs/create-workflow-terraform), which makes it more manageable in larger projects. 

There are also some remarks. Think of pipelines that need to be able to perform backfills. By default, this is harder to achieve within Workflows. Or more complex flows that can't be easily writtin in a *.yaml* configuration file. In those cases, Airflow of Prefect are also good alternatives.



For a lot of data projects Apache Airflow is the orchestration tool of choice. However, Airflow can be a bit overkill for smaller projects and tasks. One alternative is Google Workflows where you can create orchestration flows using simple *.yaml* files. In this tutorial I will show you how to export data from BigQuery to a Google Cloud Storage bucket using Google Workflows.

large_workflows-bigquery-to-cloud-storage-leader.png

medium_workflows-bigquery-to-cloud-storage-leader.png

small_workflows-bigquery-to-cloud-storage-leader.png

thumbnail_workflows-bigquery-to-cloud-storage-leader.png

workflows-bigquery-to-cloud-storage-leader.png

Google Cloud Workflows: Export data from BigQuery to Cloud Storage

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

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.

medium_dbt-google-analytics-4-bigquery.PNG

small_dbt-google-analytics-4-bigquery.PNG

thumbnail_dbt-google-analytics-4-bigquery.PNG

dbt-google-analytics-4-bigquery.PNG

Google Analytics, BigQuery & dbt: A dbt example project

**Use Google Analytics 4 data to enrich a 360° Customer View with online behavioral data, such as visit frequency, most used device, channel and/or engagement metrics. This tutorial provides an SQL example you can use directly.**

- Enrich a **single customer view** with online behavioral data collected by Google Analytics 4
- Use the GA4 BigQuery exports to aggregate Google Analytics 4 data to **user level**
- Think of most frequently used marketing channel, device, visit frequency & recency and most viewed content.
- Go [directly to the SQL code](#query-the-customer-view)

## What is a Single Customer View?

A (single) customer view is also called a Unified Customer View, a 360° Customer View or Profile. Basically it aggregates data on user level from different sources to a single profile / representation of a customer (or user). 

Most Customer Data Platforms (CDPs) offer this functionality, but you can also build a single customer view within your data warehouse. Common sources for these customer views are:

- CRM databases
- Customer service systems
- Order databases
- Offline behavior (e.g. in-store purchases using a loyalty card)
- Online behavior

In this article, we will add the online behavioral data the the customer view. Example:

![google-analytics-4-single-customer-view-table.PNG](https://static.stacktonic.com/google_analytics_4_single_customer_view_table_fc79c6859e.PNG)

By creating such a customer view, data can be used to (but certainly not limited to):

- Get a better and more complete understanding of your customer.
- Input for (customer) segmentation (e.g. clustering).
- Input for modeling / predictions (e.g. conversion or churn prediction)
- Target ads (think of email or social/display campaigns, but also on-site personalization)
- Channel preference / optimization
- Content recommendations
- Providing additional information for customer service (e.g. by connecting the view to customer service / CRM software)

## Use Google Analytics 4 data to extract online behavior

As mentioned, Google Analytics 4 (GA4) can be used to add online behavior to the customer view. Leveraging the GA4 BigQuery exports, we can aggregate data to user level, thus providing all sorts of interesting user features.

> Caution! Make sure you have the appropriate user consent before collecting and using the data.

![single-customer-view-google-analytics-4.PNG](https://static.stacktonic.com/small_single_customer_view_google_analytics_4_15b0e1e90b.PNG)

## Customer identification & mapping table

To match GA4 data with other data sources, you will need some sort of a common identifier, like a customer-ID. 

> You could also build user profiles of **anonymous users**. So not for every use case a customer-ID is required. In this article, we will assume that you want to connect the GA4 data to other systems (but provided comments in the SQL code below to build profiles for anonymous users). 

So if you're planning to connect the data to other systems, you will need to configure the [collection and matching of customer-IDs / user-IDs within Google Analytics 4](https://stacktonic.com/article/create-a-user-mapping-table-based-on-the-google-analytics-4-big-query-dataset). This article will show you how to create a user mapping table (private Identity Graph), which you can use to:

1. Match device-IDs (GA4 cookie-ID: `user_pseudo_id`) to customer-IDs or user-IDs
2. Use this match / mapping table to easily add a customer-ID to sessions and unify sessions before aggregating the data to users, thus creating cross-device profiles, depending on the identification methods (e.g. login).

![identity-graph-resolution-bigquery-sql.png](https://static.stacktonic.com/small_identity_graph_resolution_bigquery_sql_09741780f7.png)

*Private Identity Graph*

## Extracting data from the BigQuery GA4 dataset

We assume you've already set up the GA4 exports to Google BigQuery. 

> If you've decided to group on customer-ID, we assume you've set up the mapping table described in the previous chapter. 

Now we're going to transform al the GA4 event level data to user level data. The different steps explained:

1. Aggregate the event (hit) level data to session level. You have to set the **lookback_window** variable for which the user views will be constructed. We've set this to 12 months, but you should adjust this to your specific situation.
2. Match sessions on device-ID (`user_pseudo_id`) to customer-IDs. We will use the mapping table that you can find in [this article](https://stacktonic.com/article/create-a-user-mapping-table-based-on-the-google-analytics-4-big-query-dataset), as described in the previous chapter. Based on this table, we will create an intermediate match table, where the ga_client_id (= `user_pseudo_id`) is unique. We only select device-IDs that have a maximum of 3 (or less) customer-IDs attached. If multiple customer-IDs, the most recent one is selected. You can skip this step if you want to create a view for anonymous users.
3. Aggregate the session level data to user level, and creating the "customer view". 

> The query will provide you with a bunch of user features (described in the table below). You're certainly not limited to these features, as you can come up with a lot more, specific for your business. The query will show you some concepts how to get different kinds of data and aggegrations.

*All features are calculated within the configured lookback period (except `first_touch` and `traffic_source_first`, because these are persistent parameters within the GA4 dataset)*

| Feature | Description |
| --- | --- |
| first_touch | Timestamp of the very first session (can be outside the lookback window) |
| first_session | Timestamp of first session within lookback period |
| last_session | Timestamp of last session |
| last_transaction | Timestamp of last transaction |
| sessions | Total number of sessions (complete lookback window)  |
| sessions_1d_7d | Sessions between last 1 and 7 days |
| sessions_7d_30d | Sessions between last 7 and 30 days |
| sessions_30d_90d | Sessions between last 30 and 90 days |
| sessions_engaged | Total number of engaged sessions ([see definition](https://support.google.com/analytics/answer/11109416?hl=en)) |
| engagement_time_avg | Average engagement time of engaged sessions (complete lookback window) |
| logins | Sessions with a login |
| total_transactions | Total number of transactions |
| total_transaction_value | Sum of transaction values |
| device_types | Number of unique device categories (mobile / tablet / desktop) used |
| channels | Number of unique channel (groupings) used |
| days_since_last_session | Days between current date and last session timestamp |
| days_since_last_transaction | Days between current date and last transaction timestamp | 
| device_first | First device (within lookback period) |
| device_last | Last device |
| device_most_freq | Most frequently used device |
| os_last | Last used operating system |
| os_most_freq | Most frequently used operating system |
| os_browser_last | Last used browser |
| os_browser_most_freq | Most frequently used browser 
| channel_acquisition | Acquisition channel (channel of first touch session, can be outside the lookback window).  |
| channel_first | First used channel (within lookback period ) |
| channel_last | Last used channel. A session can have multiple channels,the first channel within a session is used. |
| channel_most_freq | Most frequently used channel. A session can have multiple channels,the first channel within a session is used. |
| last_viewed_items| Last 5 items viewed. Deduplicated. |
| most_viewed_items | Most viewed items |
| mosted_viewed_categories | Most viewed categories, based on items views |

## Query: The customer view

> Keep in mind, this is a rather resource intensive query, so when used on large datasets make sure you select a small amount of data tot test. I'm also aware that it isn't he most efficient query (tips welcome!). In production, you could split up the query (for example create a separate sessions table that is updated daily, so you don't have to query the complete raw GA4 dataset every day).

*I've included a temporary function for defining channel groupings (based on traffic source and medium), but you could also use a [permanent function (see this article)](https://stacktonic.com/article/google-analytics-4-and-big-query-create-custom-channel-groupings-in-a-reusable-sql-function).*

[View the code on GitHub](https://gist.github.com/krisjan-oldekamp/b3b98ecc579fa40a8fdc48a84256a2de)

```sql
declare lookback_window int64 default 365; -- how many days to lookback into the ga4 dataset to calculate profiles

-- udf: channel grouping (you could put this in a permanent function)
-- also see https://stacktonic.com/article/google-analytics-4-and-big-query-create-custom-channel-groupings-in-a-reusable-sql-function
create temporary function 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
);

-- udf: most frequent value from array
create temp function most_freq(arr any type) as ((
    select x from unnest(arr) x group by x order by count(1) desc limit 1
));

-- udf: get first channel within session
create temp function first_channel_session(arr any type) as ((
    select 
        channel_grouping(x.source, x.medium, x.campaign) 
    from unnest(arr) x 
    where x.ignore_referrer is null 
    order by x.event_timestamp asc limit 1
));

with 
-- this in-between table is based mapping table described in this article (you can leave out this part if you want to group on device-id only)
-- https://stacktonic.com/article/create-a-user-mapping-table-based-on-the-google-analytics-4-big-query-dataset)
-- only select ga_client_id (`user_pseudo_id`) with 3 of less customer-ids attached. 
-- when multiple customer-ids, select most recent one.
customer_id_mapping as (
    select
        ga_client_id,
        customer_ids,
        (select x.customer_id from unnest(customer_ids) x order by x.timestamp desc limit 1) as customer_id
    from (
        select
            ga_id.id as ga_client_id,
            count(customer_id) as customer_ids_count,
            array_agg(struct(customer_id, ga_id.timestamp)) as customer_ids,
        from 
            `<your-project>.<your-dataset>.<your-mapping-table>`, 
            unnest(ga_client_id) as ga_id
        group by 
            ga_client_id
    ) 
    where
        customer_ids_count <= 3
),
-- aggegrate event level data to session level data and attach customer-id from mapping table
sessions as (
    select
        user_pseudo_id as ga_client_id,
        max(m.customer_id) as customer_id, -- logic to select only one customer-id per user_pseudo_id can be found in `customer_id_mapping`
        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,
        max((select value.string_value from unnest(event_params) where key = 'session_engaged')) as session_engaged,
        max((select value.int_value from unnest(event_params) where key = 'engagement_time_msec'))/1000 as engagement_time,
        max(lower(traffic_source.source)) as traffic_source_first,
        max(lower(traffic_source.medium)) as traffic_medium_first,
        min(timestamp_micros(user_first_touch_timestamp)) as session_start_first_touch,
        max(device.category) as device,
        max(device.operating_system) as os,
        max(device.web_info.browser) as browser,
        max(geo.country) as country,
        max(geo.city) as city,
        ifnull(first_channel_session(
            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 order by event_timestamp asc)
        ), 'direct') as first_channel_in_session,
        --countif(event_name = '<name-of-some-other-conversion-event>') as some_conversion,
        if(countif(event_name = 'login') > 0,1,0) as session_login,
        countif(event_name = 'purchase') as transactions,
        sum(ecommerce.purchase_revenue) as transaction_value,
        array_agg(
            if(event_name = 'view_item' or event_name = 'add_to_cart', struct(
                event_timestamp,
                event_name,
                (select item_id from unnest(items) limit 1) as item_id,
                (select item_category from unnest(items) limit 1) as item_category
            ), null) 
        ignore nulls order by event_timestamp desc limit 100) as item_interactions
    from
        `<your-project>.analytics_<your-ga4-property-id>.events_*` as s
    left join customer_id_mapping as m
        on m.ga_client_id = s.user_pseudo_id
    where
        _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval lookback_window day))
        and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
    group by
        user_pseudo_id,
        session_id
)

-- aggegrate to user level
select
    * except (item_interactions),
    array_to_string(
        array(select x.item_id from unnest(item_interactions) x where x.event_name = 'view_item' group by x.item_id order by max(x.event_timestamp) desc limit 5),
        '|'
    ) as last_viewed_items,
    array_to_string(
        array(select x.item_id from unnest(item_interactions) x where x.event_name = 'view_item' group by x.item_id order by count(1) desc limit 5),
        '|'
    ) as most_viewed_items,
    array_to_string(
        array(select x.item_category from unnest(item_interactions) x where x.event_name = 'view_item' group by x.item_category order by count(1) desc limit 5),
        '|'
    ) as most_viewed_categories
from (
    select
        --ga_client_id, -- uncomment when grouping on device-id instead of customer-id
        customer_id, -- remove when grouping on device-id
        min(datetime(session_start_first_touch)) as first_touch,
        min(datetime(session_start)) as first_session,
        max(datetime(session_start)) as last_session,
        max(datetime(if(transactions > 0, session_start, null))) as last_transaction,
        count(session_id) as sessions,
        countif(date_diff(current_date, datetime(session_start), day) between 0 and 7) as sessions_1d_7d,
        countif(date_diff(current_date, datetime(session_start), day) between 8 and 30) as sessions_7d_30d,
        countif(date_diff(current_date, datetime(session_start), day) between 31 and 90) as sessions_30d_90d,
        countif(session_engaged = '1') as sessions_engaged,
        safe_divide(sum(engagement_time), countif(session_engaged = '1')) as engagement_time_avg,
        sum(session_login) as logins,
        sum(transactions) as total_transactions,
        ifnull(sum(transaction_value),0) as total_transaction_value,
        count(distinct device) as devices,
        count(distinct first_channel_in_session) as channels,
        date_diff(current_date(),cast((max(session_start)) as date),day) as days_since_last_session,
        date_diff(current_date(),cast((max(if(transactions > 0, session_start, null))) as date),day) as days_since_last_transaction,
        string_agg(device order by session_start asc limit 1) as device_first,
        string_agg(device order by session_start desc limit 1) as device_last,
        most_freq(array_agg(device)) as device_most_freq,
        string_agg(os order by session_start desc limit 1) as os_last,
        most_freq(array_agg(os)) as os_most_freq,
        string_agg(browser order by session_start desc limit 1) as browser_last,
        most_freq(array_agg(browser)) as browser_most_freq,
        channel_grouping(min(traffic_source_first), min(traffic_medium_first), null) as channel_acquisition,
        string_agg(first_channel_in_session order by session_start asc limit 1) as channel_first,
        string_agg(first_channel_in_session order by session_start desc limit 1) as channel_last,
        most_freq(array_agg(first_channel_in_session ignore nulls)) as channel_most_freq,
        array_concat_agg(item_interactions limit 200) as item_interactions
    from
        sessions
    where -- remove when grouping on device-id
        customer_id is not null -- remove when grouping on device-id
    group by
        customer_id -- remove when grouping on device-id
        --ga_client_id --uncomment when grouping on device-id instead of customer-id
)
```

That's it!

## Closing thoughts & next steps

- Building a customer view enables a lot of marketing and customer service use cases. But, make sure you have the **appropriate consent** and be transparent to your users about how you process and use the data collected.
- You can run the query daily or multiple times per day. Based on the amount of data you have in GA4, keep in mind that this is probably a rather resource intensive query. You could split up the query and create an intermediate table for the session, an append data to this table instead of generating for the complete date range every time.
- If your looking to **activate audiences** (based on the customer view) in your (marketing) channels, you could check out [this article](https://stacktonic.com/article/build-your-own-cdp-activating-audiences-in-google-ads-customer-match-using-google-tentacles) where I describe how to send an audience from BigQuery to Google Ads.
- Lastly, if you want to **personalize** your website or app using (customer) data, you will need some sort of mechanism that can provide this data to your website fast. Using BigQuery directly is not a solid solution. In [this article I describe how to set this up, using Redis as a fast-read database and Google Tag Manager Server to serve the data](https://stacktonic.com/article/create-a-user-profile-and-recommender-service-using-big-query-redis-and-gtm-server).


Use Google Analytics 4 data to enrich a 360° Customer View with online behavioral data, such as visit frequency, most used device, channel and/or engagement metrics. This tutorial provides an SQL example you can use directly.

large_google-analytics-4-single-customer-view.PNG

medium_google-analytics-4-single-customer-view.PNG

small_google-analytics-4-single-customer-view.PNG

thumbnail_google-analytics-4-single-customer-view.PNG

google-analytics-4-single-customer-view.PNG

Enrich a Single Customer View with Google Analytics 4 BigQuery data

 **When joining data sources on user level, in many occasions the same user has different types of identifiers (or multiple identifiers of the same type). Think of different device (cookie)-IDs, customer-IDs or lead-IDs. Grouping all these identifiers into a matching or mapping table (*identity graph*) can be useful when you want to stitch online behavior to customers and build a 360° customer view. **

This tutorial will show you how to create an **identity graph** containing all default and custom user identifiers that can be collected in Google Analytics 4, using the BigQuery exports.

- Group **all available user identifiers** in Google Analytics 4 into **one BigQuery table**.
- Create sub tables based on this master table for joining other data easily.
- Go [directly to the SQL code](#query-the-mapping-table)

## Identity Resolution: What is an Identity Graph?

An identity graph is a a unified collection of user-IDs (like customer- or device-IDs) that can be associated to a user, based on their interactions with a website, app or platform. 

Platforms like Facebook or Google have their own identity graphs, but **the context of this article is to build an identity graph based on your first-party sources**, so the identity graph will be specific for your organization. 

![identity-graph-resolution-bigquery-sql.png](https://static.stacktonic.com/small_identity_graph_resolution_bigquery_sql_09741780f7.png)

*Identity Graph of first-party identifiers found in the GA4 dataset*

## Different types of identifiers

The mapping table we're creating in this article, will relate all the (configured) default and custom user-IDs to a single user. In most cases, these identifiers will be of the following types (also see the figure in the previous chapter):

| ID type | GA4 parameter | Description |
| --- | --- |
| Device-ID / Cookie-IDs | `user_pseudo_id` | Identifies the same user over different visits (sessions). A cookie-ID (GA client-ID) for web of instance-ID for apps. |
| Custom User-IDs | `user_id` / `custom_param` | First-party user-IDs such as customer-IDs or lead-IDs. Should be configured manually (customer-ID has to be made available in your web/app data layer and send to GA4). |
| Click-IDs | `gclid` | Google Click-ID (GCLID/DCLID) or Facebook Click-ID (FBCLID). Can be used to send conversion data to these platforms for example. In most cases this value can be retrieved on the landingpage (URL parameters). |
| Transaction-IDs | `ecommerce.transaction_id` | Identifying transactions belonging to a user / customer. |

Since we're basically grouping all online behavior based on cookie-IDs (device/browser) to (deterministic) user identifiers such as a customer-ID, online behavior can be **joined with other data sets** as well, like CRM data. 

> I can't emphasize enough, but make sure you have the appropriate user consent based on what you're doing with the data (e.g. statistics vs marketing purposes). 

There are some things you should keep in mind when working with online behavioral data in GA4:

- Devices and browsers can be shared between multiple users. You can't be 100% sure that you're dealing with the same user for every session in Google Analytics 4. Devices / browsers can be shared for example. 
- Practical example: when customer #1 logs in on browser #1, a customer-ID is tied to the cookie-ID of browser #1. If that same browser is used by another user, this behavior is tied to customer #1 as well. 
- IDs can be shared (unintentionally). For example sharing links with with click-IDs (?gclid=) or some other form of user specific identifiers (commonly used in email links)
- Cookies (device-IDs) can be deleted. Both by tracking prevention mechanisms such as Apple's ITP or manually.

## Use cases & business value

The identity graph can be used to;

- Join historical (browsing) behavior, thus giving insights in the path leading to a conversion
- Provide insights in cross-device user behavior (and attribution)
- Input for modelling like purchase or churn predictions
- Joining CRM or other data sources with online behavior (thus creating the base for a [360° customer view](https://stacktonic.com/article/enrich-a-single-customer-view-with-google-analytics-4-big-query-data))

## Google Analytics 4 - Prerequisites & definitions

*This tutorial assumes you have an Google Analytics 4 account and enabled the data exports to BigQuery.*

Regarding the user identifiers that are collected in GA4:

- The `user_pseudo_id` is the device/browser-ID identifier and collected automatically.
- You need to 'manually' populate the `user_id` variable with a business specific user identifier like a customer-ID. This identifier can tie together cross-device and cross-browser behavior. Not collected automatically.
- Additionally you could add other identifiers in custom parameters such as secondary customer-IDs, lead-IDs or click-IDs (and make sure you have the appropriate user consent).
- The `ecommerce.transaction_id` parameter is populated when you use the ecommerce parameters of GA4 within the purchase event.

##  The mapping table explained

The mapping table consists of two parts:

1. Collecting all identifiers > Collecting all the configured user identifiers hidden away within the events of the GA4 dataset. The query has a variable called `lookback_window` to set the window in which to look for identifiers. You could query the complete dataset every time (resource intensive) or daily update a table with this data.
2. Grouping the identifiers > Make one identifier the unique key (in this tutorial, the customer-ID). Other identifiers will be aggregated into an array using the `ARRAY_AGG` function, including the timestamp of last recognition. 

![google-analytics-4-user-mapping-table-bigquery.png](https://static.stacktonic.com/google_analytics_4_user_mapping_table_bigquery_bb3a7120d3.png)

*Table: Example result*

We've set a limit of 100 unique alternative identifiers per customer-ID / row, since "snowballing" of user-identifiers can occur (one identifiers is mapped to too many device identifiers for example) or due to some tracking issues (device-ID is reset on every page for example). 

As mentioned,  for each identifier an *array of structs* is created, containing all the unique identifiers (using the custom `DEDUP()` function), including the timestamp of the last event in which the identifier was recognized. This can be used to performs checks on "freshness" or expiration. GCLIDs can expire, for example.

Now, let's create the table!

## Query: The mapping table

This query will collect all the (configured) user-IDs within the GA4 dataset for all the configured user-ID and groups the results on the `customer_id`. 

> Keep in mind, this is a rather **resource intensive query**, so when used on large datasets make sure you select a small amount of data tot test. In production you could split up the query, one process to update the recognized identifiers (daily) and a second query that groups the data on the customer-ID (or other identifiers).

An example result (when parsed into JSON format):

``` json
{
  "customer_id": "123", 
  "ga_client_id": [
     { "id": "789", "timestamp": "123123" },
     { "id": "189", "timestamp": "123123" }
  ],
  "customer_id_secondary": [
      { "id": "897", "timestamp": "123123" }
  ],
  "gclid": [
      { "id": "89134", "timestamp": "123123" },
      { "id": "72347", "timestamp": "123123" }
  ],
  "...": []
}
```

The mapping table SQL:

*[Also available on GitHub](https://gist.github.com/krisjan-oldekamp/0a1a1c0085e6e6f68306f184ed1af4a1)*

``` sql
declare lookback_window int64 default 90; -- how many days to lookback into the dataset to search for ids (compared to today)

-- udf: deduplicate array of struct 
create temp function dedup(arr any type) as ((
    select 
        array_agg(t)
    from (
        select max(a.timestamp) as timestamp, a.id from unnest(arr) a group by a.id order by timestamp desc limit 100
    ) t
));

with ga_user_ids as (
  -- select all user-ids from the ga4 dataset in the specified time period
    select
        *
    from (
        select
            user_pseudo_id as ga_client_id, -- device-id
            user_id as customer_id, -- custom defined user-id (e.g. customer-id)
            (select value.string_value from unnest(user_properties) where key = 'relay_id') as customer_id_secondary, -- secondary custom user-id
            (select value.string_value from unnest(event_params) where event_name = 'page_view' and key = 'gclid') as gclid, -- click-id
            ecommerce.transaction_id as transaction_id, -- order-ids
            max(event_timestamp) as event_timestamp -- timestamps
        from `<your-project>.analytics_<your-dataset>.events_*`
        where
            _table_suffix between format_date('%Y%m%d',date_sub(current_date(), interval lookback_window day))
            and format_date('%Y%m%d',date_sub(current_date(), interval 1 day))
        group by
           1,2,3,4,5
    )
    -- filter out users without any additional user-ids besides ga_client_id
    where
        customer_id is not null
        or customer_id_secondary is not null
        or gclid is not null
        or transaction_id is not null
)

-- select all identifiers and group on customer-id.
select
    customer_id, 
    max(timestamp_micros(event_timestamp)) as timestamp_last_visit,
    -- aggegrate customer-ids to an array of structs
    dedup(array_agg(
        if(ga_client_id is not null,    
            struct(timestamp_micros(event_timestamp) as timestamp, ga_client_id as id),
            null
        ) ignore nulls order by event_timestamp desc
    )) as ga_client_id,
    -- aggegrate secondary customer-ids to an array of structs
    dedup(array_agg(
        if(customer_id_secondary is not null,
            struct(timestamp_micros(event_timestamp) as timestamp, customer_id_secondary as id),
            null
        )ignore nulls order by event_timestamp desc
    )) as customer_id_secondary,
    -- aggegrate gclids to an array of structs
    dedup(array_agg(
        if(gclid is not null, 
            struct(timestamp_micros(event_timestamp) as timestamp, gclid as id), 
            null
        ) ignore nulls order by event_timestamp desc
    )) as gclid,
    -- aggegrate transaction-ids to an array of structs
    dedup(array_agg(
        if(transaction_id is not null, 
            struct(timestamp_micros(event_timestamp) as timestamp, transaction_id as id), 
            null
        ) ignore nulls order by event_timestamp desc
    )) as transaction_id,
from ga_user_ids
where
    customer_id is not null
group by 1
```
## Query: Create an ID specific match table

Based on the result of the previous table, you could create a match table that can quickly provide a customer-ID for a device-ID (`user_pseudo_id`).

![google-analytics-user-matching-table.png](https://static.stacktonic.com/google_analytics_user_matching_table_572ebb79f0.png)

``` sql
select
    ga_id.id AS ga_client_id,
    customer_id
from `<your-project>.<your-dataset>.<your-mapping-table>`, 
    unnest(ga_client_id) as ga_id
group by 1, 2
```

## Summary & next steps

The examples provided in this tutorial can serve as input for a lot of interesting use cases as mentioned earlier. Again, make sure you have the appropriate consent. Some next steps:

- Group mapping table on other user identifiers (for example on device-ID)
- Automate the generation of the mapping table. As mentioned, my advise would be to add new identification events to a master table (daily) and from there build your mapping table
- Use the mapping table to create a *single customer view* or *single user view*, aggregating data into a user profile, which can be used for targeting or personalization.

Learn how to create a user mapping table (or Identity Graph) based on all the available user identifiers in the Google Analytics 4 BigQuery exports (like device-IDs or customer-IDs). The Identity Graph can be useful when you want to stitch online behavior to customers and build a 360° customer view.

large_identity-mapping-graph.png

medium_identity-mapping-graph.png

small_identity-mapping-graph.png

thumbnail_identity-mapping-graph.png

identity-mapping-graph.png

Create a User Mapping table based on the Google Analytics 4 BigQuery dataset

**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!).

large_channel-grouping-google-analytics.jpg

medium_channel-grouping-google-analytics.jpg

small_channel-grouping-google-analytics.jpg

thumbnail_channel-grouping-google-analytics.jpg

channel-grouping-google-analytics.jpg

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

**Making data backups is important. But what about your valuable SQL code, hidden away in views and scheduled queries? This tutorial will show you how to backup all the SQL code into a Git repository using Python.**

When a lot users are actively participating in the development of your Google Cloud Platform (GCP) environment, it's hard to track changes to views and scheduled queries. This solution will;

- **Backup** your BigQuery **views** and **scheduled queries** to `.sql` files on your local filesystem. 
- Ability to commit changes to a **Git** repository. When running the script regularly, you can track **change history**.
- Supports **multiple** GCP projects.
- Go directly [to the code](#the-python-script)

## Preparation

We will use a Service Account to authenticate with your Google Cloud project(s). 

> You can use the same service account for all the projects by adding this service account with the correct role(s) to all the GCP projects you would like to backup.

If you're not familiar with setting up a Service Account, read the [Google documentation](https://cloud.google.com/iam/docs/creating-managing-service-accounts) first.

- Create a Service Account and enable the `BigQuery User` role for the account.
- Generate and download the JSON keyfile for the Service Account.
- Enable the `Data Transfer API` within your GCP project

## How the script works

- The Python script will fetch the SQL code of all the views and the scheduled queries within the GCP projects configured in the script.
- Files are downloaded to your local filesystem. 
- When you enable the `REPO_COMMIT` setting, the script will clone a (remote) repository first, fetches the views and scheduled query SQL and commits the changes to the repo. Before it will clone the remote repository, it will delete an old version of the repo first

> When using a Git repository to backup your SQL and track change history, you can use a SSH key for authentication. See instructions for [GitHub](https://docs.github.com/en/github/authenticating-to-github/connecting-to-github-with-ssh/adding-a-new-ssh-key-to-your-github-account) or [Bitbucket](https://support.atlassian.com/bitbucket-cloud/docs/set-up-an-ssh-key/)

** File structure **

| Entity | Folder |
| --- | --- |
| Views | `<backup-folder>/<project-id>/bq_views/<dataset-name>/<table-name>.sql` |
| Scheduled queries | `<backup-folder>/<project-id>/bq_scheduled_queries/<scheduled-query-name>.sql` |

![bigquery-backup_file_structure.PNG](https://static.stacktonic.com/bigquery_backup_file_structure_b7a80f79e4.PNG)

## Installation and running the code

** Installing packages **

The script requires some dependencies. Use `pip` to install these packages:

- `pip install google-auth`
- `pip install google-cloud-bigquery`
- `pip install GitPython`

Last step is to adjust the settings in the script: 

| Setting | Description |
| --- | --- |
| LOCAL_PATH_BACKUP | Local path to your backup (or local repository) folder. Use a trailing slash |
| REPO_COMMIT | When set to True, the script will clone the Git repository specified in the REPO_LINK setting. Then it will delete all the folders in the repo, download the latest version of the views and scheduled queries to the repo and commits the changes. |
| REPO_KEY_PATH | Path to your SSH key. Leave blank when not using an SSH key. |
| GCP_JSON_KEYPATH | Path to the Service Account JSON keyfile |
| GCP_PROJECTS_BACKUP | Specify the GCP project-IDs (and locations) that the script has to check for views and queries. For the `location` value see the *region* column in the GCP Scheduled Query interface |

Now you're all set to run the code:

- `python backup_views_scheduled_queries.py`

## The Python script

Save the code to a file manually or download the code [directly from our GitHub repository](https://gist.github.com/krisjan-oldekamp/78869851421af2f75325c32302fa2137).

```python
############################################################
# Author    Krisjan Oldekamp / Stacktonic.com              
# Email     krisjan@stacktonic.com                         
# Article   https://stacktonic.com/article/backup-your-valuable-big-query-views-and-scheduled-queries-using-python
############################################################

import os
import git
import google.oauth2.service_account
from google.cloud import bigquery
from google.cloud import bigquery_datatransfer # Also enable the Data Transfer API in the GCP console

############################################################
# Settings

LOCAL_PATH_BACKUP = "./gcp-bigquery-sql-backup/" # Backup directory (end with a trailing /). When REPO_COMMIT is set to True, this folder will also be used to clone and commit to the Git repository

REPO_COMMIT = True # When set to True, the script will clone the Git repository specified in the REPO_LINK setting. Then it will delete all the folders in the repo, download the latest version of the views and scheduled queries to the repo and commits the changes.
REPO_LINK = "git@bitbucket.org..." # Repository link
REPO_KEY_PATH = "../.ssh/..." # Path to SSH private key used for authentication with the repository. Leave empty when not using an SSH key.

GCP_JSON_KEYPATH = "your-keyfile.json" # Path to JSON keyfile for Service Account

# You can specify multiple projects. The location / region is required for downloading scheduled query SQL.
GCP_PROJECTS_BACKUP = [
    {
        "project_id": "your-gcp-project-id",
        "location": "europe" # See the "region" column in the GCP scheduled query interface
    }
]

# End of settings
############################################################

# Authenticate use a Service account / JSON keyfile
def get_credentials(keyfile_json):
    scopes=['https://www.googleapis.com/auth/bigquery']

    return (
        google.oauth2.service_account.Credentials.from_service_account_file(keyfile_json, scopes=scopes)
    )

# Format scheduled query name so it can be used in a filename
def format_name(name):
    return name.lower().replace(" ", "_")

# Push commit to repository
def git_push_commit(repo, repo_key_path, repo_local_path):
    try:
        if repo_key_path == "":
            repo.git.add(all=True)
            repo.index.commit("Automated commit containing changes to BigQuery views and scheduled queries.")
            repo.remotes.origin.push()
        else:
            with git.Git().custom_environment(GIT_SSH_COMMAND="ssh -i " + repo_key_path):
                repo.git.add(all=True)
                repo.index.commit("Automated commit containing changes to BigQuery views and scheduled queries.")
                repo.remotes.origin.push()
    except:
        print("Some error occured while pushing the commit")    

# Save Google BigQuery views to local filesystem
def save_bigquery_views(credentials, project_id, path):
    
    client_bq = bigquery.Client(credentials=credentials, project=project_id)
    datasets = list(client_bq.list_datasets())
    cnt_views = 0

    # Loop datasets
    if datasets: 
        for dataset in datasets: 
            dataset_name = dataset.dataset_id
            dataset_ref = dataset.reference
            tables = list(client_bq.list_tables(dataset_ref))

            # Loop tables in dataset
            for table in tables:
                # Only select VIEW
                if table.table_type == "VIEW":
                    table_name = table.table_id
                    table_ref = dataset_ref.table(table_name)
                    table = client_bq.get_table(table_ref)
                    
                    backup_directory = path + project_id + "/bq_views/" + dataset_name
                    if not os.path.exists(backup_directory):
                        os.makedirs(backup_directory) # Create directory when it doesn't exist
                    
                    # Save view SQL to file
                    f = open(backup_directory + "/" + table_name + ".sql", "w+") # Save view SQL to file
                    f.write(table.view_query)
                    cnt_views+=1

    return cnt_views

# Save Google BigQuery scheduled queries to local filesystem   
def save_bigquery_scheduled_queries(credentials, project_id, location, path):
    
    client_bq_df = bigquery_datatransfer.DataTransferServiceClient(credentials=credentials)
    parent = "projects/{}/locations/{}".format(project_id, location)
    cnt_scheduled_queries = 0

    # Loop all Data Transfer elements in project
    for element in client_bq_df.list_transfer_configs(parent):
        
        scheduled_query_name = format_name(element.display_name)
        scheduled_query_sql = ""
        
        # Loop over Data Transfer parameters, select scheduled queries and get query SQL
        params = element.params.items()
        for key, value in params: 
            if key == "query":
                scheduled_query_sql = value

        backup_directory = path + project_id + "/bq_scheduled_queries/"
        if not os.path.exists(backup_directory): # Create directory when it doesn't exist
            os.makedirs(backup_directory)

        # Write to file
        f = open(backup_directory + "/" + scheduled_query_name + ".sql", "w+")
        f.write(scheduled_query_sql)
        cnt_scheduled_queries+=1

    return cnt_scheduled_queries

def execute():
    
    credentials = get_credentials(GCP_JSON_KEYPATH)
    
    print ("Found {} GCP projects in settings".format(len(GCP_PROJECTS_BACKUP)))

    if REPO_COMMIT:
        # Clone repository if Git commits are enabled
        print("Git commits enabled. Cloning repository {} to {}".format(REPO_LINK, LOCAL_PATH_BACKUP))
        
        # Delete folder / repository when existing
        if os.path.exists(LOCAL_PATH_BACKUP):
            git.rmtree(LOCAL_PATH_BACKUP)
        
        if REPO_KEY_PATH == "":
            repo = git.Repo.clone_from(REPO_LINK, LOCAL_PATH_BACKUP)     
        else:
            repo = git.Repo.clone_from(REPO_LINK, LOCAL_PATH_BACKUP, env={"GIT_SSH_COMMAND": "ssh -i " + REPO_KEY_PATH}) # Use SSH key
    
        # Remove old backups in repository
        repo_dirs = next(os.walk(LOCAL_PATH_BACKUP))[1]
        for dir in repo_dirs:
            if not dir.startswith("."):
                git.rmtree(os.path.join(LOCAL_PATH_BACKUP, dir))
    else:
        # Only download to local filesystem. Create directory when it doesn't exist
        if not os.path.exists(LOCAL_PATH_BACKUP):
            os.makedirs(LOCAL_PATH_BACKUP)

    # Loop through GCP project and save views and scheduled queries
    for i, project in enumerate(GCP_PROJECTS_BACKUP):

        print ("-- Starting backup for project: {}".format(project["project_id"]))
        
        views = save_bigquery_views(
            credentials, 
            project["project_id"], 
            LOCAL_PATH_BACKUP
        )
        print("# {} views saved..".format(views))

        scheduled_queries = save_bigquery_scheduled_queries(
            credentials, project["project_id"], 
            project["location"], 
            LOCAL_PATH_BACKUP
        )
        print("# {} scheduled queries saved..".format(scheduled_queries))

    # Push code to remote repository
    if REPO_COMMIT:
        git_push_commit(repo, REPO_KEY_PATH, LOCAL_PATH_BACKUP)
        print("Pushed code to repository..")

    print("Done.")
       
execute()
```

## Some last tips

- You probably want to schedule this script daily. You can use your favorite scheduling tool like [Apache Airflow](https://stacktonic.com/article/apache-airflow-2-0-examples-a-basic-dag-template-for-any-project) or good ol' cronjobs.
- On windows, configuring SSH keys can give you some headaches. See the [GitHub](https://docs.github.com/en/github/authenticating-to-github/connecting-to-github-with-ssh/generating-a-new-ssh-key-and-adding-it-to-the-ssh-agent) and the [Bitbucket](https://support.atlassian.com/bitbucket-cloud/docs/troubleshoot-ssh-issues/) documentation for troubleshooting.

Making data backups is important. But what about your valuable SQL code, hidden away in views and scheduled queries? This tutorial will show you how to backup your SQL code into a Git repository using Python.

large_google-bigquery-backup-views-scheduled-queries.jpg

medium_google-bigquery-backup-views-scheduled-queries.jpg

small_google-bigquery-backup-views-scheduled-queries.jpg

thumbnail_google-bigquery-backup-views-scheduled-queries.jpg

google-bigquery-backup-views-scheduled-queries.jpg

Backup your valuable BigQuery Views and Scheduled Queries using 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.

large_airflow-2-0-example-dag-template.PNG

medium_airflow-2-0-example-dag-template.PNG

small_airflow-2-0-example-dag-template.PNG

thumbnail_airflow-2-0-example-dag-template.PNG

airflow-2-0-example-dag-template.PNG

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. 