AdTech analytics pipeline refactored from $10 to $0.25 per day in cloud cost

Every part of an inherited Google Analytics to BigQuery pipeline reworked for the lowest running cost that kept the data intact

Cloud data solutions

Web analytics dashboard on BigQuery data on a laptop: sessions per day by device, traffic sources, events by type and top landing pages

Who we worked with

AdTech company reporting on Google Analytics data

Our client is an AdTech company whose reporting runs on Google Analytics data, with Tableau on top. The data had to reach BigQuery, and the open question was what that should cost.

Client under NDA

What we did

Inherited pipeline, refactored end to end

Idle 90% of the time

Our client wanted its Google Analytics data in BigQuery without paying $5,000 a month for Google Analytics 360. The free BigQuery export caps how much it sends a day, and the client's traffic was past that cap, so it needed a data pipeline of its own. Another agency had built a cheap one. It ran on a server that stayed on all day for jobs that took minutes, sat idle 90% of the time and cost $10 a day.

Reading the code first, then refactoring all of it

We worked through the inherited code before changing it, then refactored all of it and kept the data intact. Apache Airflow's scheduling was optimized and the Python scripts refactored. Before the switch, the old and the new pipeline ran side by side, and we compared their tables in BigQuery, row counts per table included, for the same days. The Tableau reports gave the same figures as before. The initial refactor scope took about 20 hours.

Staying on for the next platform version

The client stayed with us for the next version of its platform. On it we set up a way to add new clients at minimal cloud cost. In AdTech a new client is ordinary business, so a cost that repeats with each one matters more than a cost paid once at build time.

Diagram: an always-on $10-a-day server against event-driven jobs at $0.25 a day, moving Google Analytics data to BigQuery

How the cost came down

Jobs that start when there is work

A pipeline is built once and paid for every day

The $10 a day paid for a server that stayed on around the clock. The cloud bill arrives every day, whether the pipeline is working or waiting.

Refactoring all of it

A patch on code nobody has read tends to move a cost somewhere harder to see. Reading the inherited pipeline first is how you learn where the money goes and what each script does to the data on its way to BigQuery. Once we knew both, we refactored all of it.

One rule for every cloud tool

Each part of the pipeline got the same question. What is the cheapest way to run this step? Work that arrives in bursts doesn't need a server waiting for it, so those workloads now start on an event, through Cloud Functions and Pub/Sub. We kept Compute Engine and BigQuery use to a minimum.

Google Cloud and data stack

What the refactored pipeline runs on

Python

Apache Airflow

BigQuery

Cloud Storage

Compute Engine

Cloud Functions

Pub/Sub

Tableau

The result

$0.25/day

Cloud infrastructure cost, down from $10

~20 hours

To complete the initial refactor scope

Cloud bill bigger than your pipeline's workload?

Tell us what your data pipeline does and what it costs to run. We reply to every enquiry within 24 hours.

Case studies

Other data projects we delivered