A new, event-driven pipeline delivers data on-demand, with 0% manual effort

In this case study, we will detail how we helped a long-standing client receive and visualise accurate and uptodate data, despite challenges with the timing of data retrieval from the Google Analytics Platform

Background to the project

Adaptive & Co has had the pleasure of partnering with Road Scholar over the course of several years. Road Scholar is a non-profit leader in education travel that offers thousands of learning adventures across all 50 US states and over 150 countries. Through our partnership, Adaptive & Co delivers a range of services to ensure that Road Scholar gets the most out of their data and can make informed marketing decisions to maximise their budget and conversions. 

Prior to this project, Adaptive & Co had created Looker Studio dashboards to help Road Scholar visualise important website data including interactions, user behaviour and ecommerce metrics. The dashboard relied on daily exports from GA4 into Big Query. However, that export didn’t arrive at a specific time every day; the data might pull through in the morning, afternoon, or occasionally several days at once after a delay. If the data was pulled from Big Query too early, gaps would arise. 

In these circumstances, a fixed refresh schedule would either lead to incomplete dashboard data, or the dashboard would remain stale for hours at a time. The problem was compounded by the fact that there was no automatic way to catch data from late or backfilled days. 

Our strategy

To solve this issue, Adaptive & Co built an event-driven pipeline on Google Cloud. With this new solution in place, an Eventarc trigger, powered by Cloud Audit Logs, watches the GA4 BigQuery dataset and ignores partial intraday tables. The moment a completed daily export lands, it invokes a Cloud Function that calls the BigQuery Data Transfer API to run six scheduled queries scoped to that day, rebuilding only the reporting tables Looker Studio reads from.

The process happens in five steps, from raw data landing to the dashboard updating:

• ‍GA4 delivers the day's data. Google Analytics exports the previous day's website activity into the BigQuery dataset when that day's export completes. ‍
• Google Cloud detects the new table. A log-based trigger watches the GA4 dataset and fires when the completed daily export table lands. There is no fixed schedule to guess.‍
• The automation runs. That trigger starts a Cloud Function built specifically for this pipeline. ‍
• The reporting tables are rebuilt. The function runs a set of pre-built queries that recalculate the metrics behind the dashboards, scoped to that specific day only. Existing days are left untouched. ‍
• Dashboards read the updated tables. The Looker Studio dashboards read directly from the refreshed BigQuery tables, so the numbers seen reflect the latest completed GA4 export. 

Results at a Glance

• ‍Update frequency: Automatically, once per day, shortly after GA4's export completes‍
• Manual effort required: Zero manual effort required. 100% automated end-to-end‍
• Reliability safeguard: System ignores incomplete/in-progress data, only acts once a full day's data has landed, and handles late or backfilled days automatically‍
• Table updates: 6 reporting tables updated per run

What these numbers mean in practical terms

Road Scholar’s dashboard’s tables now update automatically when GA4 data lands, with zero manual refreshes and no need to guess GA4’s export schedule. Late-arriving and backfilled days trigger their own rebuild with no intervention, and reruns are safe: each refresh recalculates only the day it was triggered for, leaving the rest of the reporting history untouched.

Road Scholar can now rely upon their dashboards to show reliable data as soon as it arrives, without gaps or inconsistencies caused by the download schedule. More importantly, accurate and complete data visualisation leads to more efficient workflows and more timely downstream decision-making.