m Moritz Bauer — writing on digital data
◦ Essay ·

Dataform – Automation with Google Analytics 4

How to trigger Dataform repositories automatically based on GA4 raw data exports in BigQuery using Log Sinks, Pub/Sub, and Workflows.

Google Analytics 4 offers a free raw data export to BigQuery. In this post I’ll show how Dataform repositories can be automated efficiently based on these exports.

Google Analytics 4 raw data export

Google writes the GA4 raw data into BigQuery on a fairly regular basis. A separate table is created for each day. For example, the table events_20230802 contains the data from August 2nd, 2023.

In the screenshot below you can see that on August 1st, 2023, GA4 data was exported to BigQuery five times. The first four jobs update the table events_20230730, and the last one updates events_20230731. The graph nicely shows that these exports are spread out across the day.

Importantly, these raw data exports don’t always happen at the same time, and data is sometimes added later. In practice I’ve seen updates even on tables older than 6 days, even though Google’s official documentation states 72 hours, i.e. 3 days.

Daily export tables (events_YYYYMMDD) are created after Analytics collects all of the events for the day. Analytics will update daily tables for up to 72 hours beyond the date of the table with events that are timestamped with the date of the table, e.g., event bundles that come in late from Measurement Protocol or the Firebase SDKs. For example, if the table date is 20220101, then Analytics will update the table through 20220104 with events that are timestamped 20220101.

https://support.google.com/analytics/answer/9358801?hl=en

To avoid triggering several Dataform executions in quick succession, we deliberately pull the last 3 or 5 days of raw data so that we cover any new data that arrives later.

In the past I liked to set up a Log Sink on the update procedures of the GA4 tables. Unfortunately, in practice it turned out that too many Dataform executions got triggered with too short an interval, so we hit the BigQuery update limit.

A dynamic trigger for Dataform

The following services are used for the automation:

  • Log Sink — defines the logs that will later be used as the trigger
  • Pub/Sub topic — passes our log on to the workflow
  • Workflows — defines which Dataform repository / workspace we run, and sets environment variables if needed

Log Sink

First, we set up a Log Sink that will serve as the trigger of our automation. Every time Google creates a new table for GA4 in BigQuery, our pipeline should run.

To do that, we test the filter we want to use for the Log Sink in the Logs Explorer under Logging.

proto_payload.authentication_info.principal_email="firebase-measurement@system.gserviceaccount.com"
proto_payload.resource_name=~"projects/{PROJECT_ID}/datasets/{DATASET_ID}/tables/events_2"
proto_payload.authorization_info.permission="bigquery.tables.create"

This filter matches all GA4 raw data exports while excluding the intraday tables. In this example you have to replace project ID and dataset ID with your own values. It’s a good idea to test the filter in the Log Explorer before finalizing the Log Sink.

As the destination for the Log Sink we choose a Pub/Sub topic, which you either created earlier or will create as part of the Log Sink setup process. From there you can see which logs would actually trigger your workflow.

We restrict the trigger to logs where new tables are created, because BigQuery has update / merge limits that would otherwise lead to errors. With this approach in mind, your Dataform code should query the last 3 or 8 days so that the late updates are picked up too.

Workflow + Eventarc

Once the Log Sink with the Pub/Sub topic as destination is set up, we can create the workflow and the Eventarc trigger.

In the source of the workflow we put the following code. It does the following:

  • Assigns the current DATETIME to the variable current_date
  • Sets the repository variable, which defines the Dataform workflow to execute
  • Dataform execution — passes the table name to Dataform via vars and defines the workspace under gitCommitish
  • If reaching the GitHub repository or creating the compilation result fails, there are 3 retry attempts
main:
    params: [args]
    steps:
    - init:
        assign:
        - current_date: ${time.format(sys.now())}
        - repository: projects/se-ifs-prod/locations/europe-west3/repositories/marketing_dwh
    - createCompilationResult:
        try:
            call: http.post
            args:
                url: ${"https://dataform.googleapis.com/v1beta1/" + repository + "/compilationResults"}
                auth:
                    type: OAuth2
                body:
                    gitCommitish: dev
                    codeCompilationConfig:
                        vars:
                            current_date: ${current_date}
            result: compilationResult
        retry: ${http.default_retry}
    - createWorkflowInvocation:
        call: http.post
        args:
            url: ${"https://dataform.googleapis.com/v1beta1/" + repository + "/workflowInvocations"}
            auth:
                type: OAuth2
            body:
                compilationResult: ${compilationResult.body.name}
        result: workflowInvocation
    - complete:
        return: ${workflowInvocation.body.name}

Dataform Workspace

In the Dataform workspace we set the environment variable in dataform.json under vars.

{
  "defaultSchema": "ga4_transformations",
  "assertionSchema": "ga4_assertions",
  "warehouse": "bigquery",
  "defaultDatabase": "GCP_PROJECT",
  "defaultLocation": "REGION",
  "vars": {
     "current_date": "not_set"
   }
}

We can then reference this variable in the SQLX files and use it in the SQL WHERE clause, for example.

We use the environment variable current_date in Dataform so that, when triggered manually from the workspace, all data is queried. When the automation triggers a run via Workflows, we filter with _table_suffix >= format_date("%Y%m%d",current_date()-3) to only pull the last 3 days of GA4 raw data.

${when(dataform.projectConfig.vars.current_date !== "not_set",`_table_suffix >= format_date("%Y%m%d",current_date()-3)`,true)}

Summary

Automation through Workflows is flexible and can be tweaked freely via the Dataform API. Environment variables, table or dataset prefixes, or which tags to execute can all be passed to the Dataform workspace.

Eventarc triggers offer a lot of flexibility through their native integration with GCP services. For larger projects with multiple data sources — like the Google Ads raw data export or Meta raw data — it makes sense to add additional triggers for the workflow. Combined with Dataform tags, you can run only the parts of a SQL pipeline that are affected by the raw data update.

For deploying this automation I currently use a Terraform script. I’ll cover how to use it in a future post.