**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-merchant-center:
    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

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

**Given the recent developments surrounding Google Analytics within the EU, it's not unrealistic to think that organizations are wondering what else is out there. Competitors in the analytics space are bashing Google Analytics every chance they get, but I would advise to look beyond that.**

Don't get me wrong, I think Google Analytics is an fantastic product and I hope they will sort it out. But it's always good to broaden your view, especially when talking about Google Analytics, which was / is a no-brainer for a lot of companies. 

Instead of giving you an exhaustive list with all the **alternative or complementary** analytics vendors and/or frameworks available, I will try to identify common analytics functionality and what **types of alternatives** are out there that enable you to build a data driven organization:

- An overview of common functionalities within analytics tools and frameworks.
- Relevant developments within the analytics and data space (not only on technical).
- Approaches & types of alternatives.
 
> I'm aware that the recent developments is potentially impacting more than Google Analytics alone, but I'm not diving into the legal aspect of things in this article.

## Google Analytics, a brief history

Yes, Google Analytics, 2005, Urchin and [all that](https://en.wikipedia.org/wiki/Urchin_(software)). Bottom line is, Google Analytics is the leading analytics platform for years. From my experience, Google Analytics has the following benefits: 

- Adoption & maturity
- Familiarity of the (reporting) interface
- Documentation & community
- Ease of implementation
- Reporting API
- Integration with other products. Not only GA integrating with other Google products but also out-of-the-box integration of GA in numerous tools (think of CMS systems, plugins etc).
- Advanced capabilities (like raw data exports to Google BigQuery)

It's not that other tools are lacking most of these capabilities, but making your organization data driven is mainly about people and process. Data analysts and technical marketers are scarce, so it really helps that there is a tool most users are already familiar with, even when they switch jobs (especially for the less tech savvy users).

## Common features

So let's dig into the (high-level) features of (web) analytics tools to give some context. In reality, there are countless features and every tool has it's own approach. But overall, you can argue that the main focus is to **measure and report on behavioral data, mostly related to web and app activity**. Common features:

| Functionality | Description |
| --- | --- |
| Data collection client | Think of Javascript libraries (e.g. gtag.js) and ready-to-use packages or SDKs that can be used directly within a PHP, NodeJS or other development environment. These clients will interact with the (collection) API of the analytics tool. Also closely related to Tag Management such as Tealium, Google Tag Manager or Segment: providing templates / out-of-the-box integrations for these data collection clients. The data collection client should also support customization in some form (e.g. custom events of properties). | 
| Basic reporting | A clear and easy-to-work-with reporting interface where you can get basic insights out-of-the-box. |
| Custom reporting | A custom reporting interface where business specific reports can be build & shared within your organization. |
| User management | Control user access to reporting data / dashboards |
| Integrations | Integrate with other products, think of data enrichment (e.g. auto tagging) and building and sharing audiences | 
| API & exports | Exporting data, both aggerated or raw event data. By providing a (reporting) API or real-time / periodical data dumps to (analytical) databases. |

![analytics-architecture.PNG](https://static.stacktonic.com/medium_analytics_architecture_d093116b6d.PNG)

## Developments within the market

As mentioned, becoming a data-driven organization is not only about the tool and its features. There are some relevant developments in recent years that have impact on how you should build your data-driven organization:

- Data analysts & technical marketers are scarce.
- The offering and ease of use of analytics related tools improved enormously (e.g. SaaS tools, analytical databases)
- Much easier (and cheaper) to work with large amounts of data (such as web/app clickstream data) within (cloud) analytical databases (Snowplow, BigQuery etc).
- Privacy & regulations (think of GDPR, cookie consent and the current buzz around Google Analytics / EU legislation)
- Move to "server-side" event / tag management
- "Self-service" BI tools on the rise. Related to the adoption of cloud analytical databases.

## Types of alternatives

These developments impact decision making but also increases the development and popularity of **alternative** or **complementary** tools and frameworks. So I'm not looking into "all-in-one" reporting suites only (as I would categorize Google Analytics). There are other solutions as well:

1. All-in-one reporting suites
2. Data collectors & data warehouse loaders
3. Event hubs / routers

To give you some examples, when talking alternative solutions:

- Switch to an alternative "reporting suite" vendor (duh!)
- Set up a data collector that collects and loads the data into your data warehouse. Use a BI tool to set up and share the reports within your organization. Modern analytical databases and BI tools (Superset, Looker, PowerBI, Tableau etc) are perfectly able to handle this types of data. But you will have to acquire some expertise to set up these flows and reports initially (+ onboarding / training of the end users).

Complementary solutions:

- Set up an event hub for data collection and route the events to Google Analytics or another analytical tool. 
- Use the raw data dumps next to the (native) reporting interface to execute more advanced use cases and/or reporting in a BI tool (or to prepare an organization for a life without the application / default reporting interface).

**The privacy aspect**

Privacy and compliance is becoming much more important (and rightfully so). Think of the location where the data is stored, user consent or what kind of data (e.g. PII, cookies etc) is send to the analytics servers. So make sure you know how a specific tool is dealing with these aspects and what the configuration options are. It could also be a wise idea to self-host a solution for example.

Almost every tool of framework is using a first-party cookie to measure the same user over different visits. But you could also decide to not use any cookies (or only after user consent). You will lose a lot insights, but it's still possible to measure effectiveness of your website for example, however not on user level.

Let's have a look at the different types of solutions.

> Note: I haven't tried all the alternatives in depth, so I'm giving a high-level description of the tools, instead of a full comparison. **Please let me know if you're missing something!**

## 1. All-in-one reporting suites

In these type of tools the user (reporting) interface plays a central role. Everything is built around the interface. How to create a measurement plan and set up data collection *the right way* is something else, but when you set up data collection in these tools, the default reports are populated immediately and you're good to go. 

| Vendor | Description | Pricing | Hosting |
| --- | --- |
| [Google Analytics](https://marketingplatform.google.com/about/analytics/) | Analytics offering within the Google Marketing Platform | Free & paid | Cloud |
| [Piwik Pro](https://piwik.pro/) | Analytics suite with a focus on user privacy and data security | Free & paid | Cloud |
| [Mixpanel](https://mixpanel.com/) | Focus on product analytics. | Free & paid | Cloud |
| [Amplitude](https://amplitude.com/) | Focus on product analytics. Part or a larger suite / other modules available. | Free & paid | Cloud |
| [Adobe Analytics](https://business.adobe.com/products/analytics/adobe-analytics.html) | Analytics product within the Adobe Experience Cloud. Corporate focus. | Paid | Cloud |
| [Matomo](https://matomo.org/) | Formerly Piwik. Free and open source analytics platform, focused on user privacy and data security. | Free & paid | Cloud / On-premise |
| [Heap.io](https://heap.io/) | Focus on auto capturing events and behavioral metrics | Free & paid | Cloud |

## 2. Data collectors & loaders

Secondly, this category of tools is focusing on the data collection part and storing that data into an (cloud) analytical database (e.g. Snowflake, Google BigQuery or AWS Redshift), without providing an reporting interface. 

Snowplow is popular in this category and around for some time. The tool provides an extensive library of data collection clients, such as a Javascript tracker, SDKs, packages for popular frameworks such as NodeJS. Main goal is to get the raw behavioral data to your (cloud) data warehouse in real-time.

**Note:** Equally important for the other categories as well, a good collection client / API, including a sound data structure / schema (protocol), handling of (first-party) cookies and customizability is not present in every tool. Good example of mature collection protocols: the [GA measurement protocol](https://developers.google.com/analytics/devguides/collection/protocol/ga4) or the [Snowplow Tracker Protocol](https://docs.snowplowanalytics.com/docs/collecting-data/collecting-from-own-applications/snowplow-tracker-protocol/)

![analytics-architecture-datacollector.PNG](https://static.stacktonic.com/medium_analytics_architecture_datacollector_ce687a3341.PNG)

| Vendor | Description | Pricing | Type |
| --- | --- |
| [Snowplow](https://snowplowanalytics.com/) | Open source platform for (online) behavioral data collection, streaming data into (cloud analytical) databases. | Free & paid | Cloud / On-premise |

## 3. Event hubs / routers

This last category has overlap with the previous one, but ads an additional component: routing / distributing the collected events to a range of other systems (on the **server-side**).  

Almost every tool will provide you with an user interface and templates to configure those routes. Sometimes part of a larger suite (for example as a step before aggerating event data to user profiles and audiences).

Rules, filters and transformations can be applied to the (raw) data stream. Next to data warehouses as a destination, other types of data destinations are present as well. Think of analytics tools (category #1), marketing channels (e.g. Facebook) or marketing automation platforms. All without implementing additional Javascript tracking libraries, seen the events are forwarded on the server-side.

![analytics-architecture-eventhub.PNG](https://static.stacktonic.com/medium_analytics_architecture_eventhub_0c6cd9edf2.PNG)

| Vendor | Description | Pricing | Hosting | 
| --- | --- |
| [Tealium EventStream](https://tealium.com/products/tealium-eventstream/) | Collect & route events to external applications or the Tealium CDP.  | Paid | Cloud |
| [Google Tag Manager Server](https://developers.google.com/tag-platform/tag-manager/server-side) | Self hosted "server-side tag management" solution of Google. Distribute behavioral event streams to Google and non-Google destinations. | Free (only cloud costs) | On-premise |
| [Segment](https://segment.com/) | Collect and distribute data to other applications. Also has CDP capabilities.  | Free & paid | Cloud |
| [Rudderstack](https://www.rudderstack.com/) | Collect and distribute data to other applications. Focus on developers and has CDP capabilities. | Free & Paid | Cloud |
| [MetaRouter](https://www.metarouter.io/) | Server-side event router with a focus on compliance and security | Paid | Cloud / On-premise |
| [Jentis](https://www.jentis.com/) | Server-side tag management solution hosted in the EU | Paid | Cloud |
| [Jitsu](https://jitsu.com/) | "Open source Segment alternative" | Free & paid | Cloud / On-premise |

## Closing thoughts

Selecting an alternative or complementary data collection tool or stack should depend on your type of business and more important, your requirements. The list or requirements is growing, since not only the wishes of the business user are important. The privacy of the user tracked is equally important and it's not given that the tool that shouts the loudest regarding their privacy offering is the best option in your situation.

Start with your business objectives, requirements, the people that need to work with the tool and the expertise and resources available within your organization. Then pick a vendor.




Given the recent developments surrounding Google Analytics within the EU, it's not unrealistic to think that organizations are wondering what else is out there. Competitors in the analytics space are bashing Google Analytics every chance they get, but I would advise to look beyond that.

large_analytics-architecture-header.PNG

medium_analytics-architecture-header.PNG

small_analytics-architecture-header.PNG

thumbnail_analytics-architecture-header.PNG

analytics-architecture-header.PNG

Google Analytics alternatives - An overview

**Privacy related developments such as tracking prevention mechanisms impacts *web* measurement. But what about mobile apps? Next to advertising and ad attribution (e.g. Apple's App Tracking Transparency), it also impacts first-party app measurement, campaign and install tracking. This article will shed some light on app measurement in general and how to optimize first-party measurement of mobile apps and campaigns (both Android as iOS).**

- Impact of recent privacy related updates and changes (such as iOS 14.5)
- What user identifiers are used to measure cross-app (device) and in-app behavior.
- How to measure in-app activity & engagement.
- How to track campaigns when opening or installing an app.
- Technical solutions and architecture.

 > Note: This article does not express my opinion about companies like Google or Apple and their approach to privacy. I'll stick to the facts and consequences of decisions they made, but I certainly do recognize the underlying motives; an increased need and focus for privacy and transparency.

## The Advertising (Device) Identifiers (IDFA / GAID)

So, let's start with the user identifier that is (or was) used for cross-app, device tracking and received the most impact recently; the Advertising Identifier. The Advertising Identifier can be seen as the *third-party cookie* of the mobile app ecosystem. When enabled, the IDFA is able to track the same user (mobile device) over different apps. For example:

- Ad networks can track and (re)target users
- Attribution of ad impressions / clicks from an publishers app to conversions in your own app.
- Install tracking, by using the IDFA as an identifier before and after the redirect to an app store.

As mentioned, the IDFA can be used to track and target users cross-app and is an unique identifier for a device.

Both iOS (Apple) and Android (Google) use a variant of the advertising identifier;

| Platform | Identifier | Description | Remarks | 
| --- | --- | --- | --- |
| Android | GAID | Google Advertising ID. Identifies the unique device | User consent needed in most cases. |
| iOS | IDFA | ID For Advertisers. Identifies the unique device | User consent needed trough the AppTransparencyFramework and additionally your company specific consent. |

Recently, as of iOS version 14.5, Apple basically disabled the use of the IDFA by introducing the App Tracking Transparency (ATT) framework. This heavily impacted platforms like Facebook that depended on the IDFA for ad targeting and cross-app user tracking.

## iOS App Tracking Transparency (ATT)

Introduced in iOS 14.5, a user needs to give explicit permission though the iOS *[AppTransparencyFramework](https://developer.apple.com/app-store/user-privacy-and-data-use/)* (ATT) when certain types of (user) data is collected in an app and shared with third-party vendors. The framework basically is an iOS "native" consent popup which can't  be altered a lot (design or inner workings). You also need to document the data collected in your privacy details.

![apple-app-transparency-framework-popup.PNG](https://static.stacktonic.com/apple_app_transparency_framework_popup_cc7eb9a01e.PNG)

In their documentation, Apple is [mentioning situations](https://developer.apple.com/app-store/user-privacy-and-data-use/) (important!) where where permission through the *AppTransparencyFramework* is necessary. Mostly related to sharing data with third-parties that is used for ad targeting or to track users (cross-app and device). Not adhering to these rules can result in your app **being deleted from the iOS App Store**.

When talking about first-party measurement, the last situation they mention is important:

- *Placing a third-party SDK in your app that combines user data from your app with user data from other developers’ apps to target advertising or measure advertising efficiency, even if you don’t use the SDK for these purposes. For example, using an analytics SDK that repurposes the data it collects from your app to enable targeted advertising in other developers’ apps.*

Apple **allows** you to collect first-party data for analytics purposes without a user consenting to the ATT popup. (but, make sure you don't use this data for retargeting for example and also make sure you are compliant to local privacy rules). 

Without an Advertising-ID, it's perfectly possible to measure (first-party) in-app user engagement and campaign performance, and in some cases  install tracking.

## First-party measurement: The App Instance Identifier

To measure first-party app activity, you will need some sort of identifier that can identity activity of the same user over different app "sessions" (probably not the right term when talking about apps). In contrary to the advertiser-ID, the instance-ID doesn't identify the same user over different apps, so it can't be used to recognize the same user of different apps. You could compare this with a *first-party cookie* on web. In general:

- The instance identifier uniquely identifies an app installation on a specific device.
- Doesn't track a user over different apps (third-party context).
- In most cases, when app or device cache is reset or an uninstall occurs, the instance identifier is deleted.

As an app developer, one could create such an instance identifier yourself but many app measurement and (campaign) attribution tools can handle this for you. Some pretty random examples of app measurement tools and how they're handling the app instance-ID:

| Framework | Instance Identifier | Description | Docs |
| --- | --- | --- | --- |
| Firebase / Google Analytics 4 | Firebase installation ID (FID) | Previously FirebaseInstanceId. FID is reset on uninstalls, clearing device or app cache or after 270 days of inactivity. Used for the Google Analytics 4 client-ID / user_pseudo_id. | [docs](https://firebase.google.com/docs/projects/manage-installations#monitor-id-lifecycle) |
| AppsFlyer | AppsFlyer ID | Automatically created on every install | [docs](https://support.appsflyer.com/hc/en-us/articles/4408847686161-Device-identifiers) | 
| Adjust | Adjust Device Identifier | Created on install | [docs](https://help.adjust.com/en/article/gather-device-ids-android-sdk#adjust-device-identifier) |
| MixPanel | Distinct-ID | Reset on uninstalls or clearing app or device cache. | [docs](https://help.mixpanel.com/hc/en-us/articles/115004509426-Distinct-ID-Creation-JavaScript-iOS-Android-#ios) |

Using these tools, first-party measurement can be implemented (such as GA4 or Singular). Think of measuring *first-opens, screen views, events, purchases*.  Most of these tools will handle some default events (app opens, first opens / installs or uninstalls) and implement other functionality (like Google's Install Referrer API) out-of-the-box.

> When picking a first-party app measurement tool, make sure collection of Advertising Identifiers (see the first chapter) is disabled without user consent. Additionally, using iOS, when you want to leverage the IDFA, you need to enable to the Transparency Framework popup. 

In general, implementation of these tools is roughly the same, you have to implement an SDK and set up screen and event tracking within your app. Depending on the tool of choice, they will provide an (reporting) interface and in most cases provide the option to access the data through an API or dump the data in analytical database (think of Google BigQuery when talking about Firebase).

But what about **measuring campaign performance**? How did users find and install your app? And if they already installed your app, how did you reactivate those users? For this, deep links come in handy.

## Deep linking

It's convenient to open a specific screen within your app directly. For example when a user is searching for a specific product on the web, already has your app installed, clicks the product link and the app opens the specific product screen (instead of the home screen). For this, deep links can be used. When your app is already installed, this is commonly referred to as *direct deep linking*. 

Deep linking is closely related to campaign (and install) tracking, since you can add *campaign info* to the deep links such as the campaign source, name or ad content (think of the well-known *?utm_xx=* parameters), so campaign performance can be measured.

There is no industry standard regarding deep linking, so every platform handles it slightly different. Under the hood, iOS and Android are using the methods mentioned below (and by default only works when the app is already installed). 

- iOS: [Universal Links](https://developer.apple.com/ios/universal-links/) 
- Android: [Android App Links](https://developer.android.com/studio/write/app-link-indexing)

But what if:

1. A user clicking the deep link doesn't have your app installed? 
2. How to pass the deep link and campaign parameters to your app when a user is redirected to one of the app stores to install your app?
3. A user is on a desktop device?

In the first two cases, *deferred deep linking* can be used. Basically, the deep link is "deferred" after the app is installed (deep linking *with memory*). For this to work, most likely you will like need a tool or framework that is handling this for you (like Firebase, Singular or Branch).  Additionally (in most tools), you can customize flows that handle the different situations mentioned above. 

A simple flow could like like this:

![app-install-open-tracking-flow.PNG](https://static.stacktonic.com/app_install_open_tracking_flow_9522782fa2.PNG)

> Next to deep link flows , most vendors are offering related features like app measurement, monitoring, attribution, deep linking (flows) and/or error logging in one product suite.  

## Deferred deep linking / app-to-app / web-to-app flows

As mentioned, deep links can be used to track campaigns and traffic sources (by adding campaign information to the deep link URL so that these are passed to the app). One problem: what if the app isn't installed and the user gets redirected to one of the app stores? Can we still pass the deep links and campaign parameters?

Yes, but seen the recent developments (e.g. iOS 14.5) options are limited. 

Some of the most used mechanisms for *deferred deep linking* (and used by most tools in one form or another):

| Method | Type | Description |
| --- | --- | --- |
| IDFA / GAID | Deterministic | The IDFA / GAID is used to identify the same user between a (deep link) click and a first-open (install). This way the deep link and campaign parameters can be retrieved on app open. Limited used due to user consent necessary / iOS 14.5. | 
| Install Referrer API (Android) | Deterministic | Leveraging the Install Referrer API of the Play Store, campaign parameters used to visit the Play Store (before install) can be passed to the app on first-open.  |
| Pasteboard "hack" | Deterministic | Saving the deep link on click in the iOS pasteboard and reading the value when (first) opening the app. Not possible after recent iOS update. |
| Use the app store analytics reports | Deterministic | Leveraging the reporting interfaces of the app stores to get (aggregated) information about campaign effectiveness |
| SKAdNetwork (iOS) | Deterministic | (privacy-first) mechanism for aggregated install attribution statistics. Cannot be used for deep linking, is not on user level and is not real-time (24-48 hours). Also see [this article](https://www.singular.net/blog/what-is-skadnetwork/). |
| Fingerprinting | Probabilistic | Using identifiers like IP address to match the same user when clicking a deep link and opening the app after install. Not as accurate as the methods described above. Limited use duo to user consent needed in most cases. |

## Install measurement

So which methods are left? The App Store and Play store have their own (simplified) analytics / measurement interface. Both app stores accept campaign tracking (URL) parameters when referring users to the stores, so app installs and other metrics can be attributed to campaigns. 

** App / Play Store campaign parameters**

| Platform | Campaign tracking | Docs |
| --- | --- | --- |
| Android | `?utm_...=` parameters (like `?utm_source=` and `utm_medium=`, same as Google Analytics) can be used to label traffic to the Google Play Store and viewed in the Play Store reports. | [docs](https://developers.google.com/analytics/devguides/collection/android/v4/campaigns) |
| iOS | The `?pt=` (provider) and `?ct=` (campaign code) URL parameters can be used to label traffic to the Apple App store. Reports available within Apple App Analytics. | [docs](https://help.apple.com/app-store-connect/#/itcfa7936330) |

This is nice and will give you some insights, but can we retrieve these values within the app and therefore your own, first-party measurement system? In iOS, no. Android? Yes. By using the Playstore's *Install Referrer API* to request the install referrer (campaign parameters used when visiting the Play Store) on the app's first open.

![app-install-tracking.PNG](https://static.stacktonic.com/medium_app_install_tracking_1ad4f23aea.PNG)

To summarize the available options for install tracking;

- Heavily depending on the tool you use to create deep links (or a framework developed in-house)
- Within Android, the Install Referrer API can be used
- On Android, the GAID linking method can be used, but usage is limited due to user consent. This method in iOS is practically deprecated after iOS 14.5. 
- For both Android and iOS probabilistic methods (fingerprinting) can still be used, but make sure you know how this works exactly and have the necessary user consent.
- On iOS, the SKADNetwork can be leveraged for aggerated statistics (mostly for app-to-app flows)

## Summary & closing thoughts

Overall, it's still perfectly possible to perform app measurement, including campaign measurement. The use of cross-app identifiers (IDFA/GAID) is limited nowadays due to developments related to privacy and tracking prevention. 
This is a good thing, but it does impact attribution, deep linking and install tracking. So you have to rethink your setup (boot technically as your privacy setup) and be aware of the impact on your analytics.

Privacy related developments such as tracking prevention mechanisms impacts *web* measurement. But what about mobile apps? Next to advertising and ad attribution (e.g. Apple's App Tracking Transparency), it also impacts first-party app measurement, campaign and install tracking. This article will shed some light on app measurement in general and how to optimize first-party measurement of mobile apps and campaigns (both Android as iOS).

medium_app-measurement-tracking.PNG

small_app-measurement-tracking.PNG

thumbnail_app-measurement-tracking.PNG

app-measurement-tracking.PNG

First-party mobile app measurement & install tracking

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

Privacy, cookies & first-party data

**There is some debate about the effectiveness and future potential of Customer Data Platforms (CDPs). One aspect is the increasing competition from cloud data (warehouse) platforms. Overlap seems to be growing as it became much easier to build (some) of the functionality that existing CDPs are offering. Referencing this approach, a term I hear more often is the *Headless* (Customer) Data Platform. What is this *headless* approach all about and how does it compare to existing, stand-alone tools and suites?**

The Headless Customer Data Platform (CDP) or Data Management Platform (DMP) refers to an increasingly popular solution (as there is no exact definition and probably never will be): 

- A cloud data warehouse acts as the foundation (think of GCP / AWS / Azure). Different data sources are ingested, transformed and joined. 
- Create insights and actions on all kinds of levels (**not limited to customer data**). Think of product or campaign level data.
- Activate data in channels, platforms and channels **directly from the data warehouse environment**
- Without a stand-alone CDP tool or suite, but a combination of cloud (data) services and specialized tools or frameworks.

## Headless?

*Headless* architecture gained popularity in the Content Management (CMS) world and is widely used these days. The concept: decoupling the back-end (content management) and the front-end (website). The CMS will act as a central content hub. Through an API, content can be shared with all sorts of applications, such as websites, apps or even dynamically generated emails . The front-end is no longer intertwined with the content management system. The result: all content centralized in one system and front-end applications can be developed independently from the CMS.

The headless CDP or DMP acts as a **(marketing) data hub** where insights and actions can be created on all kinds of levels **and** are activated / integrated with channels, tools or platforms (think of audiences, events or triggers). This is done directly from the cloud data warehouse, without the need for a stand-alone CDP tool or suite. However, there is no centralized user interface. The solution consists of a combination of services within the cloud environment (building blocks) and other (specialized) tools that fulfill a specific need (e.g. connectivity or decision making). 

![headless-cdp-architecture_white.PNG](https://static.stacktonic.com/medium_headless_cdp_architecture_white_cb9e8e79bc.PNG)


## Not limited to customer data only

I'm comparing the headless approach to existing stand-alone CDP solutions, which are mainly build around customer data. However, a headless data management system is not limited to customer data only. Some examples:

- Identity management, building a single (360°) customer view and (customer) audiences.
- Enriching product data / product feeds. Gaining insights into product performance.
- Create models, such as product recommenders, purchase of churn prediction models or forecasting sales (both in batch as real-time).
- Creating business rules or “triggers” (decisioning) for follow-up / next-best actions.
- Sales and engagement reports, based on multiple data sources. Creating notifications and alerts based on deviations within these reports.

The headless approach has a lot of overlap with the idea behind [The Modern Datastack](https://snowplowanalytics.com/blog/2021/05/12/modern-data-stack/). However, I will try to make the comparison with existing CDP tools, so limiting myself to functionalities these platforms generally offer.

## A data warehouse 'with benefits'

Many organizations recently invested in a data warehouse in one of the well-known cloud providers such as AWS, Azure or GCP. Data can be retrieved from various sources and joined together. Usually this is based on periodic batches (once per day or hour), but real-time data ingestion is also perfectly possible (due to the rise of real-time architecture). Think of (live) orders or clickstream data from websites / apps.

Working with such a data warehouse and all the different services within these platforms became much easier. Services within the cloud platforms can be stacked on top of each other (**building blocks**), making it easier to import, transform and analyze large amounts of data. Skills that are important for working with such a data environment, such as SQL, R or Python, are also increasingly present within organizations (but yes, in most cases not nearly enough).

*Activating* this data in channels and/or tools directly from the data warehouse is what increases the overlap with existing CDPs, thus becoming an alternative. 

## The headless approach vs. CDP tools: overlap in functionality

There are many CDP 'flavors'. For example, one platform is focusing more on activation, personalization and AI. The other on creating insights, identity resolution and a 360° customer view. However, almost all of the systems require you to to collect and ingest source data. Often (Javascript) trackers have to be implemented on-site and (mostly) CSVs uploaded to join online data with order or CRM data. 

Lately, when talking about a CDP when a cloud data warehouse environment is already present (or will be), generally the following comes up:

1. The data sources that already flow into your data warehouse, also have to be integrated with the CDP.
2. Capturing and collecting real-time (on-site) behavioral data most of the times is already done by systems such as Google Analytics or Snowplow (and more recently leveraging *server-side* tag management solutions). So why is it necessary to create another online behavioral database (in which measurement methodology certainly will differ)?
3. Duplicating business rules already present in your warehouse environment. Think of identity resolution, creation of a 360 customer view or audience selections.

If the above applies to your organization depends on your specific situation, but in most cases there will be overlap. And most CDP and data management tools do not (yet) work together in such a way that they can "piggyback" on everything that is already collected and calculated in your data warehouse.

## Disadvantages of a headless CDP

Of course, stand-alone CDPs have their advantages. Mainly this is time and cost. First off all if you don't have a data warehouse and/or resources are limited, a stand-alone CDP suite could fit your needs and get you started quickly, seen the out-of-the box functionality. The cost factor heavily depends type of system, volume and integrations. When zooming in, the headless CDP has the following disadvantages: 

1. No central user interface & ease of use: a headless system does not have an integrated user interface where settings can be easily adjusted or insight can be given into all processes. It is certainly possible to gain insight into the processes of a headless system, but those insights have to be created yourself or is available in multiple systems. So there is some technical / SQL knowledge required to set up audiences or journeys for example.
2. (Real-time) decision engine / flow builder: making decisions based on real-time data flows (e.g. web or app data). Creating such a real-time decision engine (e.g. online personalization use cases) within the cloud is possible, but is much more complex than clicking some rules together in a user interface. Also, creating flows / journeys is easily done within most CDP solutions.
3. Connectivity to channels and tools is maintained by the platform. In a headless CDP, you have to build the integrations yourself (mostly trough API connections), although there are currently several (open-source) frameworks available, such as [Google Tentacles](https://stacktonic.com/article/build-your-own-cdp-activating-audiences-in-google-ads-customer-match-using-google-tentacles). Interesting development is the rise of "reverse ETL" tools.

## Reverse ETL

This last one deserves some some explanation. There are already a lot of tools that ingest data *into* your warehouse, but less tools that can activate that data to your marketing channels or other platforms. *Reverse ETL* tries to solve this problem. Ultimately, a tool like this should enable you to focus on building use cases and not to worry about connector development and maintenance (e.g. the Facebook API). Next to that, the tools is providing insights in the process and data that flows out of your cloud platform. It also seems that some of these tools are developing a user interface where non-technical users can create audiences or flow on top of your data warehouse data.

*An example setup*

![headless-cdp-architecture_white_example_setup.PNG](https://static.stacktonic.com/medium_headless_cdp_architecture_white_example_setup_0c4b10c778.PNG)

## When to choose for a headless approach?

When your organization already has a cloud data warehouse or planning to set one up, the headless approach can be interesting if you:

1. Don't want to purchase and implement an (expensive) CDP tool, for example if you just want to share audiences, triggers or other data directly from the data warehouse with your (marketing) channels or (external) platforms;
2. Want to realize advanced and more complex use cases based on different sources and combinations, which are not possible or hard to achieve in stand-alone tools (by clicking stuff together) or without moving the data out and in the CDP platform to perform the necessary actions.
3. Do not want to duplicate data and recreate business rules in different environments (single source of thruth).
4. Unlock the potential and activate non-customer data, such as product or campaign data.

Using various cloud services and dedicated / specialized tools (such as reverse ETL), it's possible to connect data and tools relatively easily. Keep in mind that certain skills must be present in the team that will work with the headless CDP on a daily basis and at scale. Think of data engineers, tech-savvy marketers and analysts. This is especially important as use cases become more complex. 

## Start with your use cases

But most importantly, when choosing between a headless or a stand-alone CDP (or whatever solution), start with **defining clear use cases** and determine the roles / persons that will work with such a system. *“The ability to create audiences in a drag-and-drop interface and share these with our marketing channels”* is not a solid use case. Creating 100 audiences, in which the same customer is in 80 of these 100 audiences, and is targeted 80 times with a different message, does not work. Try to work out the details.

Insight and ease of use are certainly advantages of a stand-alone CDP, although the promise that every marketer can work with a CDP is often not fulfilled. Whether that depends on the marketer or the tool will differ per situation and person. The fact is that in reality, the more technical marketers and/or data engineers become the main users of such systems when the "low-hanging-fruit" use cases are live and CDP needs to be build out.

So, the best tip I can give is to involve the right people, work out clear requirements, detailed use cases and then pick a solution.

There is some debate about the effectiveness and future potential of Customer Data Platforms (CDPs). One aspect is the increasing competition from cloud data (warehouse) platforms. Overlap seems to be growing as it became much easier to build (some) of the functionality that existing CDPs are offering. Referencing this approach, a term I hear more often is the *Headless* (Customer) Data Platform. What is this *headless* approach all about and how does it compare to existing, stand-alone tools and suites?

large_headless-cdp-architecture.PNG

medium_headless-cdp-architecture.PNG

small_headless-cdp-architecture.PNG

thumbnail_headless-cdp-architecture.PNG

headless-cdp-architecture.PNG

The Future of CDPs – The Headless approach

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

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

**This guide shows you how to set up an independent "vendor-agnostic" Event Driven Data Layer (EDDL) and how to process events within this data layer. Example code is provided, so you can build your own data connectors. **

- Learn **how to set** up an independent Event Driven Data Layer.
- Learn how to **attach an event listener to process the events** in the data layer.
- Using the Google Data Layer Helper library.
- **[Example connectors](#example-1---log-events-to-the-console)**: Simple console logs and a Tealium iQ (Tag Management) connector.

## Why is this still relevant?

This article could easily been written 5 years ago. Still it's relevant today and even more important seen the recent focus on **quality first-party data** (which should have been a focus all along..). 

Anyway, a lot of articles are written on this topic (some mentioned later on), but I felt I could add some value with some practical examples and give it some context seen recent developments regarding tracking & first-party data. 

A well structured Event Driven Data Layer (EDDL) including mechanism that enables you to process and listen to events in the EDDL, can be extremely powerful when you're planning to create an abstraction layer between your website / app and TMS or other client-side data collection tool. Especially;

- When you want to take **full ownership of your data layer** and don't want to depend on vendor specific code and/or requirements.
- When **not** using GTM.
- Using data collection tools or tag management systems that **do not have an event driven data layer mechanism** in place.
- You want to create your own data collection library / script (for example to provide data to Google Tag Manager Server without using the `gtag.js` library or client-side GTM).

But what exactly is an EDDL?

## The Event Driven Data Layer (EDDL)

The term EDDL gained popularity through the efforts of Jim Gorden (Jimalytics). As mentioned, there are already a lot of good articles written about the workings and benefits of the EDDL. So I would recommend to check out the following articles first:

- [The Event Driven Data Layer](https://jimalytics.com/tag-management/the-event-driven-data-layer/) by Jimalytics.
- [Event Driven Data Layer guide](https://www.searchdiscovery.com/blog/event-driven-data-layer-guide-1/) by Search Discovery.

**Short recap: ** Many know the concept of an EDDL from Google Tag Manager (the `dataLayer`). Replace `digitalData` with `dataLayer` in the example below all should look familiar.  

The Event Driven Data Layer itself basically is just a plain Javascript array (`digitalData` in the example below), containing (event) objects.  Think of pageviews, clicks or purchases. The Javascript array is available at `window` scope.

Example:

```javascript
window.digitalData = window.digitalData || [];

// Pageview event
digitalData.push({
  "event": "page",
  "page_type": "product_detail",
  "product_id": "12345",
  "product_name": "Shiny red shoes"
  // ...
});

// Purchase event
digitalData.push({
  "event": "purchase",
  "transaction_id": "777",
  "product_id": "12345",
  "product_name": "Shiny red shoes"
  // ...
});
```

You can structure the events and properties any way you like, so that it nicely fits your type of business. Keep in mind, **consistency is key**!

When such a data layer is in place, one can write some code or use existing libraries to **process the events pushed into the data layer** array and **attach listeners** to it. The listeners can be used to build connectors or wrappers around vendor-specific tracking scripts, so you can map your data layer variables to vendor specific requirements (in one place).

Some common use cases:

- Connect event listeners to the data layer (e.g. Tag Management Systems / tracking script connectors)
- Connect data validators (e.g. JSON schema validation)
- Transform data to specific formats / business requirements before data is send to data collection or TMS tools.
- Switch listeners based on different platforms or environments, but using the same data layer. Think of parts of your website that are loaded in a Webview, for example in Hybrid apps (like Cordova)

![vendor-agnostic-data-layer.PNG](https://static.stacktonic.com/small_vendor_agnostic_data_layer_0c9f4e95b4.PNG)

## The benefits of using an EDDL

But what are the benefits of an EDDL compared to (vendor specific) data layer and collection setups?

- **No vendor specific knowledge** required **for developers** (e.g. vendor libraries), plain Javascript arrays and objects (except for building the connector itself).
- **No vendor lock-in** / vendor agnostic, swap out vendors (client-side TMS / tracking solutions) more easily.
- Data layer structure not tied to vendor specific requirements.
- Asynchronous
- Excellent fit for single page / event-driven applications.

## Vendor Agnostic what?

As you can see in the example above, besides (optional) event object structure, there isn't much vendor-specific logic involved. Since GTM uses the same concept, you could say that GTM already uses an "independent" data layer (besides the recommended event structure). 

But, Google Tag Manager isn't the only TMS used (yes, really). Think of Tealium, Adobe or Segment. But we also have data collection scripts that can be implemented without a TMS, such as the `gtag.js` library (and becomes relevant if you don't want to use client-side GTM to implement server-side GTM for example).

In many cases, these vendors require you to implement vender specific code. Some examples:

**Tealium iQ (TMS)**

A Tealium iQ pageview:

```javascript
utag.view({
    "tealium_event": "product_view",
    "page_type"    : "product",
    "product_id"   : ["12345"],
    "product_name" : ["Red shoes"]
});
```

**Adobe**

> Adobe recently (2020) launched it's own event driven data layer library, [called ACDL](https://github.com/adobe/adobe-client-data-layer), so bad example.

**gtag.js**

A gtag.js pageview:

```javascript
gtag("event'" "page_view", {
  "page_title": "<Page Title>",
  "page_location": "<Page Location>",
  "page_path": "<Page Path>",
  "send_to": "<GA_MEASUREMENT_ID>"
})
```

As you can see, there is a lot of vendor specific code involved. The point here is that you **don't want is to implement such vendor specific code all through your website or app**. So, creating an abstraction layer would be a wise approach. 

> I've implemented an event driven data layer for multiple clients over the last few years (DigitalDataQueue), and it really has proved its worth. For some clients, after the EDDL implementation, we've changed the TMS multiple times (something to do with the business being sold multiple times..). It saved a lot of people a lot of work and data quality was never compromised.

## Listen to and process events in the data layer

A good example of a data layer "listener" library is the [Data Layer Helper library](https://github.com/google/data-layer-helper) by Google (also used in GTM). We will use this library in the examples below.

> Back in 2018, together with my former colleague [Folkert Mudde](https://nl.linkedin.com/in/folkertmudde) we've rewritten added some features to the Google Data Layer Helper Library and named it [DigitalDataQueue (GitHub)](https://github.com/Oogst/digital-data-queue). It's a bit outdated now and Google updated their Data Layer Helper Library in the meantime, so I will use the Google library in the examples below.

The Data Layer Helper library will:

- Process events passed into the data layer (`digitalData.push({ .. })`)
- Activate listener functions (connectors) when new events are pushed into the data layer
- Process (historical) event already present in the datalayer before the library is activated. 
- Merges event data (object) into a data model (object)

**Order of operations**
![data-layer-helper-order-of-operations.PNG](https://static.stacktonic.com/data_layer_helper_order_of_operations_caf860beeb.PNG)

Firstly, check out the [GitHub repo]((https://github.com/google/data-layer-helper)) of the Data Layer Helper. The library is documented pretty well. 

You can add the library to you application easily;

``` bash
npm install data-layer-helper
// or
yarn install data-layer-helper
```

And import the package

```
import 'node_modules/data-layer-helper/dist/data-layer-helper';
```

Other option is to host the minified [`data-layer-helper.js`](https://github.com/google/data-layer-helper/blob/master/dist/data-layer-helper.js) version of the library somewhere and include it into you application.

Let's move on to the examples.

## Example 1 - Log events to the console

The first example will just log the events pushed in the data layer to the (browser) console. It nicely demonstrates the capabilities of the library.

```javascript
// Make sure to include the Data Layer Helper library first

window.digitalData = window.digitalData || [];

// Pageview event
digitalData.push({
  "event": "page",
  "page_type": "product_detail",
  "product_id": "12345",
  "product_name": "Shiny red shoes"
  // ...
});

const DigitalDataHelper = new DataLayerHelper(digitalData);

function listenerLogToConsole(model, event) {
  console.log(event);
}

const DigitalDataHelper = new DataLayerHelper(digitalData, {
  listener: listenerLogToConsole,
  listenToPast: true, // Process events already in the digitalData array before initializing the library.
});
```

Now, when you execute the code, you should see the pageview event in your browser console. 

## Example 2 - Tealium iQ connector

The seconds example show you how to create a wrapper function around Tealium iQ specific code, so its activated based on events within the data layer.

**Tip:** It's perfectly possible to include the data-layer-helper.js script AND the listener function in a `Preloader` extension within Tealium itself. Benefit is that you know for sure that Tealium is loaded before activating the listener (+ easy to edit the listener function within Tealium itself).

```javascript


<script type="text/javascript" src="data-layer-helper.js"></script>


<script type="text/javascript">
	// Declare datalayer
	window.digitalData = window.digitalData || [];
	
	// .. Populate datalayer
	// digitalData.push({ .. });
	
	// Tealium config overraide: disable default pageview `view()` on load 
	// To prevent duplicate pageviews, since the page event will trigger a pageview
	window.utag_cfg_ovrd = {
		noview: true
	};
	
	// Tealium Universal Base Tag
	(function(a,b,c,d) {
		a='//tags.tiqcdn.com/utag/ACCOUNT/PROFILE/ENVIRONMENT/utag.js';
		b=document;c='script';d=b.createElement(c);d.src=a;
		d.type='text/java'+c;d.async=true;
		a=b.getElementsByTagName(c)[0];a.parentNode.insertBefore(d,a)})();

	// Data layer listener function: Tealium connector
	// Tip: You can implement this code directly in the front-end OR add the code as a preloader extension within Tealium
	function listenerTealium(model, event) {
	   
		// Declare utag_data / UDO (Tealium datalayer)
		window.utag_data = window.utag_data || [];
	   
		// Tealium specific transformations
		var _event = event;
		var _model = model;
		
		// Mapping digital-data-queue events to Tealium
		if (typeof _event.event != 'undefined') {
			switch(_event.event) {
				case "page":
					window.utag_data = _event;
					window.utag.view(window.utag_data, function() { window.utag.DB("digital-data: Event: \"" + _event.event + "\" to utag.view()"); });
					break;
				default:
					// Using the model (instead of event) to populate event parameters, so page properties are available within the event.
					window.utag.link(_model, function() { window.utag.DB("digital-data: Event : \"" + _event.event + "\" to utag.link()"); });
			}
		} else {
			// No event property set
		}
	}

	// Init Data Layer Helper
	var DigitalDataHelper = new DataLayerHelper(digitalData, {
		listener: listenerTealium,
		listenToPast: true, // Process events already in the digitalData array before initializing the library.
	});
</script>
```

## Other interesting reads

The examples above showed you some of the possibilities using an EDDL / event listener. I'd love to hear your experiences and use cases.

Lastly, some interesting articles related to this topic not mentioned before:

- [How to trigger actions on a data layer](https://antonbies.com/event-driven-data-layer-how-can-you-trigger-actions-on-a-data-layer-push/) by Anton Bies
- [The Data layer explained](https://www.simoahava.com/analytics/data-layer/) by Simo Ahava (old but gold)

This guide will show you how to set up an independent (vendor-agnostic) Event Driven Data Layer (EDDL) and how to process events within this data layer. Example code is provided, so you can build your own data connectors. 

large_event-driven-data-layer.PNG

medium_event-driven-data-layer.PNG

small_event-driven-data-layer.PNG

thumbnail_event-driven-data-layer.PNG

event-driven-data-layer.PNG

The Independent Event-Driven Data Layer – A Practical Guide

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

Learn how to serve user level data from BigQuery to your website or app, using Redis (low-latency), Cloud Functions and Google Tag Manager Server. The user-level profile data can be used for on-site personalization or data enrichments within GTM Server.

medium_gtm-server-profile-store.PNG

small_gtm-server-profile-store.PNG

thumbnail_gtm-server-profile-store.PNG

gtm-server-profile-store.PNG

Create a User Profile and Recommender service using BigQuery, Redis and GTM Server

**Running Google Tag Manager (GTM) Server in Google App Engine? Learn how to set up error alerts when 4xx and/or 5xx alerts exceed a certain threshold.**

When using GTM Server in production, it's important that the service runs stable and when it does not, you're notified straight away. This tutorial will show you how to:

- Set up alerts in when **5xx** and **4xx** errors exceed a certain threshold
- Using Google Cloud Monitoring
- Notifications can be send by  **email **,  **sms **,  **Slack ** or to  **other channels **.

## What is Google Cloud Monitoring?

Google Cloud Monitoring can be used to gain (performance) insights in the services you run within the Google cloud. Additionally, it enables you to create alerts based on all sorts of (performance) metrics. 

You can set up alerts by creating an **alerting policy**. This is basically done in the drag and drop interface of the GCP console OR you can upload a policy file using the *Monitoring API* (not all types of alerting policies and rules can be configured in the GCP interface). 

When an incident occurs, alerts can be send to different **notification channels** like: 

- Email / SMS
- Slack
- Webhooks
- Cloud Pub/Sub

## Create alerts for Google App Engine / GTM Server

Google Tag Manager Server can (and in a lot of cases will) handle a lot of traffic. The **ratio** of 4xx and 5xx errors can be a good indicator for your GTM server stability. For example, a lot of 5xx errors can point to server stability or scaling issues. 

Our goal was to set up a policy that will trigger when the ratio of 4xx and 5xx errors, compared to the total request exceed a certain threshold. However, we've tried to *drag and drop* the related metrics to create this policy in the interface, but we didn't succeed.

Luckily, I've came across [this example](https://cloud.google.com/monitoring/alerts/policies-in-json#json-ratio) in the Google documentation, explaining how to set up a *metric-ratio* policy, using the Monitoring API (and indeed, it's currently not possible to set this up by dragging and dropping stuff in the interface).

> So caution! When you want to update your policy, make sure you do it through the Monitoring API and not in the GCP interface. It seems to overwrite some of the settings, resulting in not triggering any errors.

I've adjusted to policy from the example a bit. The policy will:

-  Take 5xx and 4xx errors into account. 
-  Error threshold is set to 1% (`thresholdValue`). However, if you have a lot of traffic 1% of all responses are still a lot of errors, so adjust to your specific setup.

Save the code (policy) below to your local filesystem, in this tutorial we've named it `gtm_server_alerting_policy.json`. 

> Make sure you replace `<your-project-id>` with your GCP project-ID. 

Next, you can use the Cloud Shell to upload the policy file:

1. In the Google Cloud Console, open up the Cloud Shell (icon in the top right corner of the interface). The Cloud Shell is activated on the bottom of your screen.
2. Click the "Open Editor" button (on top of the the Shell window) and upload the `gtm_server_alerting_policy.json` file.
3. Click the "Open Terminal" button and use the following command to create the policy:

``` bash
gcloud alpha monitoring policies create --policy-from-file="gtm_server_alerting_policy.json"
```

4. Now the policy is created and is visible in the GCP interface (Monitoring > Alerting):

![google-tag-manager-server-error-alerting.PNG](https://static.stacktonic.com/google_tag_manager_server_error_alerting_9ed8be08c8.PNG)

5. Configure your notification channels (like email or Slack) in the GCP interface (also under Monitoring > Alerting).
6. You're all set!

**gtm_server_alerting_policy.json**
``` json
{
    "displayName": "HTTP error count exceeds 1 percent for GTM Server / App Engine",
    "combiner": "OR",
    "conditions": [
        {
            "displayName": "Ratio: HTTP 4xx and 5xx error-response counts / All HTTP response counts",
            "conditionThreshold": {
                 "filter": "metric.label.response_code>=\"400\" AND
                            metric.label.response_code<\"600\" AND
                            metric.type=\"appengine.googleapis.com/http/server/response_count\" AND
                            project=\"<your-project-id>\" AND
                            resource.type=\"gae_app\"",
                 "aggregations": [
                    {
                        "alignmentPeriod": "300s",
                        "crossSeriesReducer": "REDUCE_SUM",
                        "groupByFields": [
                          "project",
                          "resource.label.module_id",
                          "resource.label.version_id"
                        ],
                        "perSeriesAligner": "ALIGN_DELTA"
                    }
                ],
                "denominatorFilter": "metric.type=\"appengine.googleapis.com/http/server/response_count\" AND
                                      project=\"<your-project-id>\" AND
                                      resource.type=\"gae_app\"",
                "denominatorAggregations": [
                   {
                      "alignmentPeriod": "300s",
                      "crossSeriesReducer": "REDUCE_SUM",
                      "groupByFields": [
                        "project",
                        "resource.label.module_id",
                        "resource.label.version_id"
                       ],
                      "perSeriesAligner": "ALIGN_DELTA",
                    }
                ],
                "comparison": "COMPARISON_GT",
                "thresholdValue": 0.01,
                "duration": "0s",
                "trigger": {
                    "count": 1
                }
            }
        }
    ]
}
```


Running Google Tag Manager Server (GTM) in Google App Engine? Learn how to set  up error alerts when 4xx and/or 5xx alerts exceed a certain threshold.

medium_google-app-engine-4xx-5xx-error-alerts.png

small_google-app-engine-4xx-5xx-error-alerts.png

thumbnail_google-app-engine-4xx-5xx-error-alerts.png

google-app-engine-4xx-5xx-error-alerts.png

How to set up error alerts for Google Tag Manager Server on App Engine

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

medium_identity-graph.png

small_identity-graph.png

thumbnail_identity-graph.png

identity-graph.png

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

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

medium_attribution_header.PNG

small_attribution_header.PNG

thumbnail_attribution_header.PNG

attribution_header.PNG

Data Driven Attribution

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

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

**In this tutorial we will show you how to activate audiences in Google Ads Customer Match lists using the Google Tentacles framework. The framework can be used activate your (first-party) data in marketing and advertising platforms. Basically building a headless Customer Data Platform (CDP) within an (existing) cloud / data environment without an expensive CDP tool or suite**

- Automate the audience push to Google Ads (Customer Match lists), based on (hashed) email address or other available identifiers.
- Google Tentacles runs "serverless" within the Google Cloud Platform and starts running when a new file is uploaded in a specific Storage bucket.
- These files are new line delimited JSONs, containing the create and remove to audience operations. These files can be generated by your data warehouse (e.g. Google BigQuery or Dataflow).
- Can be used for a lot of common use cases (pushing audiences or conversion events to external platforms).
- The framework itself is focused on Google products (what a suprise!), but connectors for non-Google products can be developed easily (e.g. Facebook Conversion API or Custom Audience connector).

## What is Tentacles?

[Tentacles](https://github.com/GoogleCloudPlatform/cloud-for-marketing/blob/main/marketing-analytics/activation/gmp-googleads-connector/README.md) is an (unofficial) Google framework, operating **serverless** using different services within the Google Cloud Platform, like Cloud Functions & PubSub. 
The **integration framework** is able to handle large amounts of data to be send to other, mostly marketing related Google products (like Google Analytics data upload, Google Campaign conversions or Google Ads Custom Audiences). 
Next to all the Google related connectors, the framework is handling tasks like `queueing`, `batching`, `rate-limits` and `(error) logging` and therefore can be used perfectly for building *custom connectors*, like a Facebook Custom Audience connector we've build.

More background information on the framework [in this article](https://cloud.google.com/architecture/serverless-integration-solution-for-google-marketing-platform).

![google-tentacles-serverless-framework-headless-cdp.png](https://static.stacktonic.com/google_tentacles_serverless_framework_headless_cdp_1ed8fd795c.png)

*Architectural layout of the serverless integration framework. Source: [Google](https://cloud.google.com/architecture/serverless-integration-solution-for-google-marketing-platform)*

## 1. Preparation

If not present already, create a Google Cloud project ([instructions](https://cloud.google.com/resource-manager/docs/creating-managing-projects)) where you can run Tentacles. Tentacles provides you with a bash install script, that will check and activate all services / APIs needed. It assumes you're an project administrator, so if it's not possible to grant administrator rights, at least you need the following permissions / roles;

- Service Account User
- Cloud Functions Developer
- Pub/Sub Editor
- Storage Admin
- Service Usage Admin
- Cloud Datastore User
- Service Management Administrator

The following services / APIs are used by Tentacles, if not activated already, the install script will enable it for you;

> In new GCP projects, you have to select a Datastore / Firestore operation mode. For Tentacles, **choose the Firestore Native mode**. You can't change this setting later on, so make sure you set it to Firestore Native mode.

- **Google Ads API** (you have to enable this manually in your project)
- Google Cloud Storage
- Firestore (Native mode)
- Google Cloud Functions 
- Google Pub/Sub

**Create an OAuth Client**

1. In GCP navigate to Google APIs & Services > Credentials
2. If not present already, configure the consent screen and fill out the required fields.
3. Create new OAuth 2.0 Client credentials. When creating, select the **Desktop app**
Create )Desktop app
4. Write down the `Client-ID` and `Client-Secret`, you will need it during the installation.

** Google Ads API basic access**

In Google Ads, **create an API developer token** and apply for API basic access. This will take some days. [See documention](https://developers.google.com/google-ads/api/docs/access-levels)

## 2. Installation / deployment

Let's move on the deployment of Tentacles.

1. In your Google Cloud project, open up the Cloud shell (top right).

2. Clone the repository to your Cloud shell instance;

```bash
git clone https://github.com/GoogleCloudPlatform/cloud-for-marketing.git
```

3. Run the install script and follow the instructions.

```bash
cd cloud-for-marketing/marketing-analytics/activation/gmp-googleads-connector
bash deploy.sh
```

- The namespace chosen will be the prefix of all services created by Tentacles
- When asked for the APIs (connectors) to install, choose "Google Ads API for Customer Match Upload" (option 5).
- Enter the Client-ID and Cliend-Secret you've created in the earlier steps, this will generate a verification link that results in a response code you need to paste back into the install script.

The install script will enable all the services if not already, creating the Pub/Sub topics, Cloud Functions, Cloud bucket and triggers. Next step is to configure the Google Ads Customer Match connector.

## 3. Configure the Google Ads connector

The Google Ads connector uses the Google Ads API (not the old Google Adwords API). It expects;

- Two separate JSON files per audience, co (per audience),  with one of the available customer identifiers, like (hashed) email or telephone. See [this page](https://developers.google.com/google-ads/api/reference/rpc/v7/UserIdentifier) for all available identifiers.
- One file containing the `create` (add) to audience operations
- One file containining the `remove` from audience operations

> Be aware that you're uploading customer data to Google, so make sure you have the correct consent / permissions from those users.

**Connector configuration**

Every Google Ads audience needs to be defined in the `config_api.json` file. For the same audience you have to create 2 configurations, one for `create` operators and one for `remove` operators. 

> As I'm told, this is because Tentacles mimics the behavior of the Google Ads API. The Google Ads API separates create and delete operations. Next to that, using multiple identifiers (like hashed email and phone) also results in additional configurations, because the Google Ads API also separates these operations.

| Setting | Description |
|---|---|
| developerToken | Google Ads developer token. Make sure you have Basic Access to the Google Ads API |
| customer_id | Google Ads account-ID where you want the audiences to be created |
| login_customer_id | Google Ads MCC account-ID, when accessing the account through an MCC. |
| list_id| Google Ads Customer Match list ID > You will have to create the audience first in the interface and fetch the ID. Hopefully, automatic audience creation will be added in the future  |
| list_type | Type of identifiers you wish to upload to this audience, for example `hashed_email` or `hashed_phone_number`. See [this page](https://developers.google.com/google-ads/api/reference/rpc/v7/UserIdentifier). |
|operation| `create` or `remove`, so you have to create two separate configurations for the same audience |


**Example `config_api.json` file. **

*ACM: Ads Customer Match configurations (the config_api.json can also contain configurations for the other connectors*

``` json
{
  "ACM": {
    "audience_1_add": {
        "developerToken": "<GOOGLE-ADS-API-DEVELOPER TOKEN>",
        "customerMatchConfig": {
            "customer_id": "<ADS-CUSTOMER-ID>",
            "login_customer_id": "<ADS-MCC-CUSTOMER-ID>",
            "list_id": "<CUSTOM-AUDIENCE-ID>",
            "list_type": "hashed_email",
            "operation": "create"
        }
    },
    "audience_1_remove": {
        "developerToken": "<GOOGLE-ADS-API-DEVELOPER TOKEN>",
        "customerMatchConfig": {
            "customer_id": "<ADS-CUSTOMER-ID>",
            "login_customer_id": "<ADS-MCC-CUSTOMER-ID>",
            "list_id": "<CUSTOM-AUDIENCE-ID>",
            "list_type": "hashed_email",
            "operation": "remove"
        }
    }
  }
}
```

1. Go back to the Google Cloud shell. 
2. You can use the file editor (Open Editor button), navigate to `cloud-for-marketing/marketing-analytics/activation/gmp-googleads-connector` 
3. Open `config_api.json` and edit the contents / paste your Google Ads Custom Audience configuration.

Then, run the following command to upload or update the config into Tentacles (the bash script will load or update the configuration into the Firestore database).

```
bash deploy.sh update_api_config
```

> The install script doesn't remove entries  in the Firestore database, only adds or updates.

Now, Tentacles is all set to receive the JSON files containing the create and remove operations for Google Ads audiences.

## 4. Automate the Google Ads import files

The last step is to automate the generation of the newline delimited JSON files. The naming of these files should like like `API[ACM]_config[audience_1_add]_<YYYYMMDD>.json`, where the connector key (ACM) and config key (audience_1_add) should match the entries in `config_api.json`

The content of the newline delimited JSON files should look like;

``` json
{ "hashed_email": "f660ab912ec121d1b1e928a0bb4bc61b15f5ad44d5efdc4e1c92a25e99b8e44a" }
{ "hashed_email": "bceede42d53175d644efb79514d3967d3f9b92eb810b2c30bcddfae571b8b377" }
{ "hashed_email": "8d1dc4e440a9965c3388c8714d3dd25ce99d855fd6828396f0fab07f57fba820" }

```

Most likely, you will have to execute the following tasks;

- Create a selection (audience) based on available data in your data warehouse (e.g. "all high-value customers")
- Calculate delta's / incremental tables, containing the `create` and `remove` audience operations
- When using BigQuery, you could create **date sharded** (partition) tables (audience_1_YYYMMMDD), holding all the the **SHA256** hashed emails of that date for the specific audience. 
- Based on these date sharded tables, you can calculate the differences between the most recent audience state and audience state of the previous date. 
- With these differences/deltas you can **create two intermediate tables**, one containing the **SHA256** hashed emails for the `create` operations (new users in audience), the other containing the `delete` operations (users need to be deleted).
- Export the 2 intermediate tables to JSON files (newline delimited), using the correct filename > `API[ACM]_config[audience_1_add]_<YYYYMMDD>.json` (can be exported directly from BigQuery)
- Move file to the Google Cloud bucket where the Tentacles Cloud Function is listening to (in the `/outbound` folder)

You could use the following SQL to calculate differences based on two date sharded tables (current date vs. previous date):

``` sql
#standardSQL
SELECT
  operator AS operator,
  JSON_EXTRACT_SCALAR(record, '$.hashed_email') AS hashed_email
FROM (
  -- Get new / adjusted records compared to previous table -> CREATE operations
  SELECT 
    *,
    'create' AS operator, 
  FROM (
    SELECT TO_JSON_STRING(a) AS record FROM `{table_name}_*` a WHERE _TABLE_SUFFIX = '{current_date}' -- New table
    EXCEPT DISTINCT
    SELECT TO_JSON_STRING(b) AS record FROM `{table_name}_*` b WHERE _TABLE_SUFFIX = '{prev_date}' -- Old table
  )
  UNION ALL
  -- Get missing records in current table, compared to previous table -> REMOVE operations
  SELECT
    *,
    'remove' AS operator, 
  FROM (
    SELECT TO_JSON_STRING(b) AS record FROM `{table_name}_*` b WHERE _TABLE_SUFFIX = '{prev_date}' -- Old table
    EXCEPT DISTINCT
    SELECT TO_JSON_STRING(a) AS record FROM `{table_name}_*` a WHERE _TABLE_SUFFIX = '{current_date}' -- New table
  )
)
```

There are a lot of solutions possible automating these tasks, but you could use one of the following setups;

1. **Apache Airflow**

Apache Airflow has all the connectors available to automate these tasks (create audience tables, create intermediate / delta tables) and export it in JSON format to the Tentacles Cloud bucket. Using Apache Airflow, you can create a scalable solution that can handle a lot of audiences.

2. **Scheduled Queries + Cloud Scheduler + Cloud Functions**

- Use BigQuery Scheduled Queries to generate the daily full audience tables and the delta tables (see example SQL above).
- Use Cloud Functions to run a function that starts the BigQuery export job to a JSON file in the Tentacles bucket. See [this code](https://cloud.google.com/bigquery/docs/samples/bigquery-extract-table-json).
- Let Cloud Scheduler run the Cloud Function after the scheduled queries ran.

## Summary

Tentacles will enable you to activate your data directly out of your cloud environment pretty easily. It could save some money compared to expensive CDP tools / suites, but keep in mind

- It doesn't provide you with a nice user interface
- Only has some basic (error) logging
- Heavily Google focused, but customer connectors can added / developed easily

Some last tips;

- Google Ads Connector; when you using multiple identifiers (like email and telephone), you need to split up files according to the number of different identifiers used + add/ remove operations. Resulting in 4 files per audience when using hashed_email and hash_phone_number for example. Not that efficient, but you can adjust the Google Ads connector (nodeJs) without much effort, for example combining create and add operations in 1 file.
- You can debug Tentacles, using the logs provided in the Google Cloud Functions interface or the Cloud Logging / Stackdriver interface.

## Useful links & documentation

- [Tentacles GitHub repository](https://github.com/GoogleCloudPlatform/cloud-for-marketing/blob/main/marketing-analytics/activation/gmp-googleads-connector/README.md)
- [Tentacles overview & architecture by Google](https://cloud.google.com/architecture/serverless-integration-solution-for-google-marketing-platform)
- [Tentacles tutorial by Google](https://cloud.google.com/architecture/deploying-a-serverless-integration-solution-cloud-functions-cloud-pubsub)


In this tutorial we will show you how to activate audiences in Google Ads Customer Match lists, using the Google Tentacles framework. It will enable you to automate the audiences push to Customer Match lists, based on (hashed) email address or other available identifiers.

medium_tentacles.jpg

small_tentacles.jpg

thumbnail_tentacles.jpg

tentacles.jpg

Build your own CDP: Activating audiences in Google Ads Customer Match using Google Tentacles

**
Due to privacy related browser developments such as tracking prevention mechanisms (ITP / ETP), it's getting more difficult to correctly set persistent first-party cookies. Even if there is no data shared with any third-party. Having consistent first-party cookies is important to measure your website user behavior over multiple website visits. When using popular frameworks such as Next.js or Gatsby, your website is prerendered into static HTML files and can be served from an AWS S3 bucket and the CloudFront CDN. Lambda@Edge can be used to run server-side code when a page is requested. In this tutorial we will use a Lambda function to set a persistent first-party (HTTP) cookie in the page response.
**

- Using an **Lambda@Edge function** attached to the ** viewer-response event** within CloudFront, a **persistent first-party cookie** can be set by modifying response headers and setting an HTTP (server-side) cookie. 
- The cookie contains a **UUID**. For new users, a fresh UUID is created. For returning users the existing value is used to extend the cookie.
- Go directly [to the code](#the-nodejs-lamda-function)

> The UUID enables you to track users over different visits (on your first-party domain). Be aware that you need consent of your users, depending on how you use this UUID. So keep the interests of your users in mind and be transparent / ask for the right permissions when needed.

## Modify AWS CloudFront responses using Lamda@Edge

As mentioned, prerendering your website or application to static (HTML) files and serving them out of an AWS S3 bucket in combination with the CloudFront CDN is gaining popularity due to frameworks such as Next.js or Gatsby. 

One of the challenges is that you can't run any server-side code directly from your static files. Lambda@Edge solves this problem. It enables you to run code (Python or Node.js) on several events within Cloudfront, so you can modify requests and responses. 
Lamda function can be used to redirect users to A/B testing variants or country specific content. Modifying responses is also one of the possibilities, including creation of HTTP / server-side cookies. The Lamda@Edge functions are deployed on different locations in edge network, thus will execute as close to the user as possible.

Lamda@Edge functions can be execute on the following locations;

- Viewer Request
- Origin Request
- Origin Response
- **Viewer Response** > We will use this event to inspect the viewer request headers and set a cookie in the HTTP response. 

![first-party-cookie-http-response-lambda-at-edge.png](https://static.stacktonic.com/first_party_cookie_http_response_lambda_at_edge_8213d24a81.png)

*More information can be found in the [AWS documentation](https://docs.amazonaws.cn/en_us/lambda/latest/dg/lambda-edge.html)*

## Why use Lamda@Edge to set a server-side cookie?

Setting a first-party cookie containing an UUID user identifier directly in the response of the HTML page requested has several benefits:

- No dependency on client-side Javascript to make an additional request to a service that sets a HTTP cookie. 
- Using such a mechanism mentioned above could create dependency issues; when a service is expecting the first-party UUID cookie but the client-side cookie service has not returned a response yet.
- You can use the first-party cookie in server-side tag management solutions like Google Tag Manager Server, without the need of this tool to be responsible for creating the cookie (since it can be blocked)

## Deploying the Lamda function

*See [the AWS documentation](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/lambda-edge-how-it-works-tutorial.html) for a more in depth tutorial.*

Steps:

We assume that you already have a website running in AWS Cloudfront.

1. Create a Lamda function in region `us-east-1`

> It's **important** that you create the Lamda function in the **us-east-1** (N. Virginia) region. When deploying the function to Lamda@Edge it will distribute the functions to all the different locations in the Edge network. Therefore debugging Lamda@Edge function can be difficult, because it generates logs in the region where the Lamda@Edge function is triggered (as close to the end user as possible). So when your located in Germany, most probably the logs can be found in region eu-central-1

2. Download the `index.js` and `package.json` from our [GitHub repository](https://github.com/stacktonic-com/aws-lamda-at-edge-set-uuid-cookie).
3. The Lamda function has some dependencies like the `uuid` and `cookie` packages. You will need to install the packages first. Run `npm run install` in the folder where you placed the package.json and index.js file
4. Create a ZIP file of `index.js`, `package.json` and the `/node_modules` folder (created after installing the packages).
5. Upload the ZIP file to AWS and deploy the Lamda Function
6. Add a trigger to the Lambda Function. Choose "CloudFront" and when configuring this trigger, select the "Viewer Response" event

![cloudfront-lambda-viewer-response.PNG](https://static.stacktonic.com/cloudfront_lambda_viewer_response_36e7c0a3e7.PNG)

- It will take some time (seconds / minutes) before the Lambda function is deployed to all Edge locations.
- When deployed succesfully, you can check if the cookie is set in your browser / developer console (also check that the UUID value remains consistent when navigating to different pages):

![first-party-http-cookie.PNG](https://static.stacktonic.com/first_party_http_cookie_2a251ee586.PNG)

**Some last tips**

- When deploying a new version of your function, **make sure** you also deploy the function to Lamda@Edge again (step 6). When deploying again, you can select "Use existing CloudFront trigger on this function" in the CloudFront trigger settings.
- As mentioned, you can debug the logs of a Lamda@Edge function only in the region where the function was invoked. 
- By default, the IAM role attached to your function will not properly create logs in other regions than us-east 1 (see [this thread](https://stackoverflow.com/questions/46503108/lambdaedge-not-logging-on-cloudfront-request)). 
- Solution is to adjust the IAM role of your Lamda function to;

```json
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "logs:CreateLogGroup",
            "Resource": "arn:aws:logs:*:*:*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "logs:CreateLogStream",
                "logs:PutLogEvents"
            ],
            "Resource": [
                "arn:aws:logs:*:*:log-group:*:*"
            ]
        }
    ]
}
```

## The Node.js Lamda function

The AWS Lamda code ([view full repository on GitHub](https://github.com/stacktonic-com/aws-lamda-at-edge-set-uuid-cookie)). The `package.json` is available on GitHub.

```javascript
// Lambda@Edge function, (re)writing first-party user UUIDs
// Author: Krisjan Oldekamp / Stacktonic.com
// Email: krisjan@stacktonic.com

'use strict';

const { v4: uuidv4 } = require('uuid');
const cookie = require('cookie');
const cookieName = 'FPD'; // Cookie name containing first-party identifier
const cookieLifetime = 730; // Cookie expiration of user UUID in days (2 years) 

exports.handler = async (event, context) => {
    
    const request = event.Records[0].cf.request;
    const response = event.Records[0].cf.response;

    try {
    
        const requestHeaders = request.headers;
        const responseHeaders = response.headers;

        // Generate random UUID.
        let uuid = uuidv4();
        let setCookie = false;

        if (requestHeaders.cookie) {
            // Get all cookie information from request headers
            let cookies = cookie.parse(requestHeaders.cookie[0].value);
            
            // Check if user UUID cookie is present and get existing UUID value from requestheaders.
            if (cookies[cookieName] !== undefined) {
                uuid = cookies[cookieName];
            } 
            
            // Check for presence of user UUID cookie. If not present, (re)write cookie
            if (cookies[cookieName] === undefined) {
                setCookie = true;
            }
           
        } else {
            setCookie = true;
        }

        // Set or extend cookies when neccesarry.
        if (setCookie === true) {
            let domainTld = (requestHeaders.host[0].value).split(/\./).slice(-2).join('.');

            // Build user cookie string with user UUID.
            let cookieStringUser = cookie.serialize(cookieName, String(uuid), {
                domain: '.' + domainTld,
                path: '/',
                secure: true,
                httpOnly: true,
                sameSite: 'lax',
                maxAge: 60 * 60 * 24 * cookieLifetime 
            });

            // Set HTTP cookies in response.
            responseHeaders['set-cookie'] = [{
                key: 'set-cookie',
                value: cookieStringUser
            }];
        }
        return response;
    } catch(err) {
        console.log(`Error handling response: ${err}`)
    }
    return response;
};
```


Hosting your website on AWS S3 / CloudFront? Deploy a Lambda@Edge function to set a persistent first-party cookie that can be used to measure website user behavior over different visits, without the use of any Javascript / client-side scripts. 

medium_edge.jpg

small_edge.jpg

thumbnail_edge.jpg

edge.jpg

How to set a persistent UUID cookie using CloudFront and Lamda@Edge

** Price competitiveness and product benchmark data, already available in the Merchant Center interface, is made available in Google BigQuery. Leveraging these exports, you can create advanced insights into your product catalog that can be used for automation as well.**

- Export product and brand level market insight data (based on all the merchants on Google Shopping / Merchant center) to Google BigQuery
- Enrich your productfeed with the Merchant Center data to create valuable advanced insights and automations
- [BigQuery example query](#example-query)

## Connecting Merchant Center to Google BigQuery

First step, you will need a Google Cloud Project with an active billing account. If not present already, you can check out [these instructions](https://cloud.google.com/resource-manager/docs/creating-managing-projects).

The official [Google documentation](https://cloud.google.com/bigquery-transfer/docs/merchant-center-transfer#setting_up_a_google_merchant_center_transfer) provides all the info to set up the data transfer service between Merchant Center and Google BigQuery. When the connection is set up, Merchant Center will run a daily export of several reports.

![merchant-center-bigquery-export-flow.svg](https://static.stacktonic.com/merchant_center_bigquery_export_flow_dd1c547ff4.svg)
*Source: Google*

## Merchant Center exports

Several reports can be exported by the transfer service. You don't have to export all the reports (can be configured when setting up the export).

> Be aware that the Merchant Center exports are not that small in terms of GBs. For every available Google product category and country, it will export 10.000 top products for example. So it could generate some costs in your Google Cloud project. See the tips in the last chapter of this article.

The most important exports;

| Report | Table name | Description |
| - | - | - |
| Products and product issues | Product_\[ACCOUNT\] | Productdata provided to Merchant Center, enriched with information like status, item issues, Google product categories and (country) destinations |
| Price benchmarks | Products\_PriceBenchmarks\_\[ACCOUNT\] | Based on the **price competitiveness** report in the Merchant Center Interface. When available, it provides a price benchmark on product level based on data of other retailers (matching based on GTIN). |
| Best Sellers - Top Products | BestSellers\_TopProducts\_\[ACCOUNT\] | A ranked list of **top products** (max 10.000 products per country and Google product category). Based on data of all merchants (Shopping ads and unpaid listings). The same product can be featured multiple times in this table. Per ranking country + multiple times per Google product (sub) categories. |
| Best Sellers - Top Products Inventory | BestSellers\_TopProducts_Inventory\_\[ACCOUNT-ID\] | Mapping between your product feed and top products. The same product-ID can exist multiple times times (but has a unique ranking-ID). Per ranking country and in multiple Google product categories.  |
| Best Sellers - Top Brands | BestSellers\_TopBrands\_\[ACCOUNT\] | A ranked list of **top brands** (10.000 per country and Google product category). Based on data of all merchants (Shopping ads and unpaid listings). |

*Also see [this overview](https://cloud.google.com/bigquery-transfer/docs/merchant-center-transfer) in the official Google documentation.*

Some of the most interesting properties and definition;

- `price_benchmark_value`: The average (click-weighted) price of a specific product, based on all merchants who advertise that same product on Shopping ads. Matching products between merchants is based on GTIN. 
- `rank`: The popularity of the product on Shopping ads, on Google product category and country level. Popularity is based on the estimated number of products sold. The rank updates daily, but data in the exports can be delayed up to 2 days.
- `previous_rank`: The change in rank over the previous 7 days.
- `relative_demand.min / .max / .bucket`: A product's current estimated demand in relation to the product with the highest popularity rank in the same category and country. Also includes buckets (e.g. Very High)
- `previous_relative_demand.min / .max / .bucket`: Same as above, only calculated on the last 7 days.
- `price_range.min` / `price_range.max`: The lower and upper pricerange without decimals. Doesn't include shipping costs.

## Practical use cases & business value

Combining the different reports, you can think of the following use cases;

- **Product feed status**: Get Merchant Center and Shopping errors and notifications related to your product feeds.
- **Product inventory optimization**: Using the best seller reports, you can check which popular products are already in your inventory and what potential products could be added, based on the complete list of top products per Google product categories AND country. 
- **Price and bid optimization**: Using the price competitiveness benchmark information, you can check how you are doing in terms of price against competitors. 
- ** Trending products and brands**: Identify trending products / brands based on the changes in best seller rank over the last X days / weeks / months. Differentiate per country
- **Product feed enrichment**: Enrich you product feed with Google market insights data
- **Rule based automation**: Based on the product feed enrichment, pause and enable campaigns / products in channels (e.g. Google Shopping) or adjust bids. For example based on price competitiveness.

![Enrich product feed with Merchant Center data](https://static.stacktonic.com/enrich_productfeed_with_merchent_center_data_8477494f1d.PNG)

## Example query

To show you how the data can be combined, I've constructed a query that will join and combine data out of different Merchant Center tables on product level. Some remarks:

- **Google doesn't provide data for every product in your feed**. Either a product is not in the bestsellers list or Google just doesn't provide or calculate it (not enough data and probably some other reasons). Or you have unique products that other merchants don't offer.
- Use the `BestSellers_TopProducts_Inventory` as a mapping table to join the `BestSellers_TopProducts` data with your `Product` data by joining on `rank_id` (available in both tables)
- The "original" product-ID as known in your systems is not directly available in the different exports. It's available in the `product_id` field but you have te split the value and select the last part since Google added additional information to it, example: `online:nl:NL:<original-product-id>`
- The reports contain a lot of data. For example the best seller reports contains 10.000 best sellers in every Google product category and country. For efficiency reasons, try to select only the countries needed. Also important because the same product can be duplicated over different countries in the table Google provides.
- The reports contain nested and repeated data, make sure you understand the table structures. For example the product errors and notifications are nested.

**Example: Enrich product feed with benchmark and price competitiveness data **

The following query will result in this table that you could match on your original productfeed and could serve as input for automation rules (on product level)

![enrich-productfeed-with-merchent-center-data.PNG](https://static.stacktonic.com/enrich_productfeed_with_merchent_center_data_8477494f1d.PNG)

Make sure you replace the dataset / table names with the correct values in your situation.

*View code on [GitHub](https://gist.github.com/krisjan-oldekamp/631e2f029d89538da45f4946f7d5be4f)*

```sql
-- Author: Krisjan Oldekamp
-- https://stacktonic.com/article/create-advanced-google-shopping-insights-using-merchant-center-big-query-exports

-- set variable to change the fetch date easily
declare gmc_fetch_date date default date('2021-09-05');

with
-- get productfeed uploaded in gmc for specific date
gmc_products as (
    select
        _partitiondate as date,
        *
    from
        `dataset.products_<merchant-id>`
    where
        _partitiondate = gmc_fetch_date
        and target_country in ('nl')
),
-- get mapping table to join rank (bestsellers) and productfeed
gmc_product_inventory as (
    select
        *
    from
        `dataset.bestsellers_topproducts_inventory_<merchant-id>`
    where
        _partitiondate = gmc_fetch_date
        and (regexp_contains(rank_id, ':nl:'))
),
-- get price benchmark information
gmc_price_benchmarks as (
    select
        _partitiondate as date,
        *
    from
        `dataset.products_pricebenchmarks_<merchant-id>`
    where
        _partitiondate = gmc_fetch_date
        and country_of_sale in ('nl')  -- specify the countrycodes, since dataset countains all countries	
    ),
-- get bestseller information (if product ranks in bestseller list -> 10000 products per google product category and country).
gmc_bestsellers as (
    select  
        _partitiondate as date,
        inventory.product_id as product_id,
        bestseller.rank_timestamp,
        bestseller.rank_id,
        bestseller.rank,
        bestseller.previous_rank,
        if(inventory.product_id is null,false,true) as product_in_inventory,
        bestseller.ranking_country,
        bestseller.ranking_category as ranking_category_id,
        (select name from bestseller.ranking_category_path where locale = 'nl-nl') as ranking_category_path,
        (select name from bestseller.product_title where locale = 'nl-nl') as product_title,
        concat(gtins[safe_offset(0)],'|',gtins[safe_offset(1)],'|',gtins[safe_offset(2)],'|') as gtins,
        bestseller.brand,
        bestseller.google_brand_id,
        bestseller.google_product_category as google_product_category_id,
        (select name from bestseller.google_product_category_path where locale = 'nl-nl') as google_category_path,
        bestseller.price_range.min as price_range_min,
        bestseller.price_range.max as price_range_max,
        bestseller.price_range.currency as price_range_currency,
        bestseller.relative_demand.min as relative_demand_min,
        bestseller.relative_demand.max as relative_demand_max,
        bestseller.relative_demand.bucket as relative_demand_bucket,
        bestseller.previous_relative_demand.min as previous_relative_demand_min,
        bestseller.previous_relative_demand.max as previous_relative_demand_max,
        bestseller.previous_relative_demand.bucket as previous_relative_demand_bucket
    from 
        `dataset.bestsellers_topproducts_<merchant-id>` as bestseller
    -- only join products when available in the productfeed and the bestsellers list
    inner join gmc_product_inventory as inventory
        on bestseller.rank_id = inventory.rank_id
    where 
        _partitiondate = gmc_fetch_date
        and ranking_country in ('nl') 
)

    -- join all the data on product level
select
    products.date,
    products.product_id as product_id_gms,
    split(products.product_id, ':')[safe_offset(3)] as product_id,
    products.target_country,
    products.title,
    products.price.value,
    benchmarks.price_benchmark_value,
    (cast(benchmarks.price_benchmark_value as float64) - cast(products.price.value as float64)) as price_benchmark_diff,
    benchmarks.price_benchmark_currency,
    bestsellers.rank,
    bestsellers.previous_rank,
    bestsellers.price_range_min,
    bestsellers.price_range_max,
    bestsellers.relative_demand_min,
    bestsellers.relative_demand_max,
    bestsellers.relative_demand_bucket,
    bestsellers.previous_relative_demand_min,
    bestsellers.previous_relative_demand_max,
    bestsellers.previous_relative_demand_bucket
from gmc_products as products
-- join price benchmark data
left join gmc_price_benchmarks as benchmarks
    on products.product_id = benchmarks.product_id
    and products.date = benchmarks.date
-- join bestseller data
left join gmc_bestsellers as bestsellers
    on products.product_id = bestsellers.product_id
    and products.date = bestsellers.date
```

## Closing thoughts

Using the BigQuery Merchant Center exports, you can generate insights at scale and use it for automation. But keep in mind,  the exports hold a lot of data, so it could generate some costs in your Google Cloud project (and you probably won't need all the data). The tables are date partitioned, so you could write a Python script or use Airflow to delete partitions older than X days to keep the amount of data in BigQuery (probably after you've created some aggregate reports).






Using the BigQuery export functionality of Google Merchant Center, Google provided market insights like price competitiveness and benchmarks can be leveraged at scale. Combining this data with you product feed, valuable insights and optimizations can be delivered. 

large_google-shopping-bigquery-advanced-analysis.jpg

medium_google-shopping-bigquery-advanced-analysis.jpg

small_google-shopping-bigquery-advanced-analysis.jpg

thumbnail_google-shopping-bigquery-advanced-analysis.jpg

google-shopping-bigquery-advanced-analysis.jpg

Create Advanced Google Shopping Insights using Merchant Center BigQuery exports

**Use (real-time) triggers like (sports) events or weather conditions to automate your display and/or video campaigns in Google Display & Video 360 (DV360). This tutorial will show you how to automate these operations using Python and the DV360 API.**

- **Activate** and **pause** DV360 **line items**, making use of the DV360 API and Python
- Activate **manual triggers** to quickly activate a line item, that will automatically pause after a configurable amount of time.
- The script can be scheduled using a serverless function like AWS Lamba / Google Cloud Functions or a tool like Apache Airflow.
- Go directly [to the code](#changing-the-line-item-status)

## Practical use cases & business value

The new DV360 API (mid-2020) unlocked some interesting new features and actions became much more simpler to automate. 

![google-display-video-360-enable-line-item.PNG](https://static.stacktonic.com/google_display_video_360_enable_line_item_324ff943bd.PNG)

To give a few practical examples, using this functionality;

- (De)activate line items based on **weather conditions**
- (De)activate line items based on **events** like company / national events or sports matches (e.g. campaigns based on match outcomes)
- (De)activate line items based on **sales or stock levels**

Another interesting, related, feature are the "[manual triggers](https://support.google.com/displayvideo/answer/9265340?hl=en)"; a line-item can be activated quickly and it will automatically deactivate after a desired amount of time has passed (so no need to deactivate the line item trough the API or interface). 

Think of;

- Activate line items when an event has started (and automatically deactivate after X time)
- Activate line items when a commercial has aired (and automatically deactivate after X time)

Line items with manual triggers will also work using our code example (two use cases provided), but you have to attach a manual trigger on the line item within the [DV360 interface](https://support.google.com/displayvideo/answer/9265340?hl=en) first.

## Preparation & configuration

Before running the script, a few things need to be configured:

- [Create a Google Cloud project](https://cloud.google.com/resource-manager/docs/creating-managing-projects) when you don't have one already
- Activate the Google Display & Video 360 API within the Google Cloud Console

![google-display-video-360-enable-api.PNG](https://static.stacktonic.com/google_display_video_360_enable_api_272a0c8370.PNG)
- [Create a service account](https://cloud.google.com/iam/docs/creating-managing-service-accounts) in the Google Cloud console to programmatically authenticate with the different Google services (without using a user account)
- Add the service account to your Google DV360 account and grant access rights to the correct advertiser accounts (I've used the `"Standard" role` but probably can be configured more accurate).

## Changing the line item status

The Python script below will;
- Authenticate using a service account (change GOOGLE_JSON_KEYFILE to the location of your `keyfile.json`)
- Update a line-item based on `advertiser-id`, `line-item-id` and desired `line-item status`.
- Or activate a manual trigger based on `advertiser-id` and `trigger-id` (can be obtained in the DV360 interface when a trigger is created)

> Keep your JSON key file secure!

*View code [on GitHub](https://gist.github.com/krisjan-oldekamp/044945ef754a766bd7dd7390ac41e3cb)*

``` python
###################################################
# Author    Krisjan Oldekamp / Stacktonic.com
# Email     krisjan@stacktonic.com
# Article   https://stacktonic.com/article/how-to-activate-and-pause-line-items-in-google-dv-360-using-python
####################################################

import os
from urllib.error import HTTPError
from googleapiclient import discovery
from oauth2client.service_account import ServiceAccountCredentials

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

GOOGLE_JSON_KEYFILE = "<your-keyfile>.json" # Google Cloud Platform Service Account JSON keyfile
GOOGLE_DV360_API_VERSION = "v1"
GOOGLE_DV360_API_SCOPES = ["https://www.googleapis.com/auth/display-video"]

DV360_ADVERTISER_ID = "<your-dv360-advertiser-id>"

####################################################

# Google D&V360 API service
def get_dv360_service():
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        GOOGLE_JSON_KEYFILE,
        scopes=GOOGLE_DV360_API_SCOPES)

    return discovery.build('displayvideo', GOOGLE_DV360_API_VERSION, credentials=credentials, cache_discovery=False)

# Change DV360 Line Item status
def update_lineitem_status(advertiser_id, line_item_id, status_new):

    service = get_dv360_service()

    status_dv360 = 'ENTITY_STATUS_ACTIVE' if status_new == 'ACTIVE' else 'ENTITY_STATUS_PAUSED'

    line_item_obj = {
        'entityStatus': status_dv360,
    }

    # Update the line item.
    update_line_item = service.advertisers().lineItems().patch(
        advertiserId=advertiser_id,
        lineItemId=line_item_id,
        updateMask="entityStatus",
        body=line_item_obj
    ).execute()

    print("Lineitem " + line_item_id + " updated to " + status_dv360)

# Activate a manual trigger
def manual_trigger(advertiser_id, trigger_id):

    service = get_dv360_service()

    # Activate the trigger
    update_line_item = service.advertisers().manualTriggers().activate(
        advertiserId=advertiser_id,
        triggerId=trigger_id
    ).execute()

    print("TriggerId " + trigger_id + " activated.")


# Usecase 1: Update lineitem status

line_item_id = "123" # DV360 Line-Item-ID
line_item_status_new = "ACTIVE" # New status -> ACTIVE or PAUSED

update_lineitem_status(DV360_ADVERTISER_ID, line_item_id, line_item_status_new)

# Usecase 2: Activate a manual trigger (create and attach a manual trigger in the DV360 interface first)

trigger_id = "123"

manual_trigger(DV360_ADVERTISER_ID, trigger_id)
```

## How to automate

There are endless ways to automate the script. The methods I've used;

- Wrapping the script in an Apache Airflow DAG
- Using a serverless function, like AWS Lambda or Google Cloud Functions. In our case, the Cloud function was triggered using Cloud Scheduler and the function fetched line-items and activation status from a Google Sheet.

Use (real-time) triggers like (sports) events or weather conditions to automate your display and/or video campaigns in Google Display & Video 360 (DV360). This tutorial will show you how to automate these operations using Python and the DV360 API.

large_traffic-light.jpg

medium_traffic-light.jpg

small_traffic-light.jpg

thumbnail_traffic-light.jpg

traffic-light.jpg

How to activate and pause line items in Google DV360 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.

medium_pipeline.jpg

small_pipeline.jpg

thumbnail_pipeline.jpg

pipeline.jpg

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

In many organizations, most data is already ingested and transformed in a data warehouse. So there is a lot of overlap in both data and functionality when implementing a completely stand-alone CDP tool next to an existing datawarehouse. We like the concept of a CDP that consists of multiple tools and services, Lytics is trying to solve this in their newest product feature (open beta). Would like to see more functionality like this from other vendors.

CDP vendor Lytics claims it will deeply integrate with your existing datawarehouse

Google confirmed that the removal of third-party cookies within the Google Chrome browser is delayed until late 2023. Google has accommodated several of it's new privacy related technologies, like FLoC (Federated Learning of Cohorts) within the so called "Privacy Sandbox". Unfortunately for Google, it seems that it's [not getting the support](https://www.theverge.com/2021/4/16/22387492/google-floc-ad-tech-privacy-browsers-brave-vivaldi-edge-mozilla-chrome-safari) needed.

This delay however, will give more time to to adapt to the "death of cookies", however we strongly advise to adapt now and not in late 2023.


Deprecation of third-party cookies in Google Chrome delayed until 2023

Now the Google Ads and Floodlight templates are released, the most important ad products are covered and Google moved GTM Server out of beta.

Google Tag Manager Server out of beta

Google launched a new documentation and developer website for gtag.js and Google Tag Manager. It combines all the information to implement and configure the gtag.js global site tag and GTM. In our opinion the new website is an improvement since GTM and especially gtag.js documentation was scattered out over different product documentation portals.


articles (18)