Cleaning up 28.5 million datapoints

using the Google Cloud Platform

The Challenge

Rival is an innovative data-driven marketing agency that is making the most of online market signals
Rival wanted to provide its customers with a measure for their "Market share of search". The idea was to use Google Trends data to understand, for example, how many people google Mercedes vs BMW vs Audi vs VW
The challenge was that Google Trends doesn't provide absolute search volumes, just relative sizes over certain time periods.
The other problem was that we were looking at 1623 search terms across 5 years in 9 geographies, requiring 28.5 datapoints
All of these would have to make it through our complex data analytics pipeline - a computationally costly task
Finally, results needed to be loaded into the users UI in under a second

Our Approach

google trends
google trends
google trends
google trends
We built a complex data ingestion, manipulation and storage pipeline that enabled reliability reading in data and querying it under a second.
To make sure we could reliably make many thousands of API calls every week, we used Google Cloud Task queues that allow endlessly retrying API calls with exponential backoff until they pass.
We then had a lot of heavy data manipulation to do to calculate market shares. Initially we did this in BigQuery, but quickly realised that the number of writes would cost too much. Therefore we did the data manipulation in a managed PostgreSQL instance, which was quick and low cost.
However, the results would need to be queriable from the UI with sub-1-second waiting times. To achieve this, we pushed our big data output into BigQuery, which is optimised for fast parallel querying.
Finally, we built the filterable Business Dashboards using Looker Studio, which integrates with BigQuery. This allowed fast prototyping of report formats customers wanted to see.
To make the experience look customised and to enable a paywall, we then embedded the Looker Studio reports in a web application for the final product.

The Results

0 m
Data points ingested
Average query time
Dashboard Charts

Working with Andi and his team at Wakeflow has been an exceptional experience. Their professionalism and dedication to understanding our business requirements are truly commendable. They are a pleasure to deal with and always make time to answer our questions, no matter how big or small.

Frank Fernández
Head of Product

Let's fix your tech problem

If you tell us about it, we can fix it

Go on, get in the booth

Get Yourself Better Tech
Gemma House 39, Lilestone Street,
London, England, NW8 8SS, UK
us now
face popup
Talk strategy with an expert
Get expert advice on the right strategy for your business!