/case-studies/rival/top.png
/case-studies/rival/logo.svg

Cleaning up 28.5 million datapoints

using the Google Cloud Platform

The Challenge

c_6
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
c_17
google trends
c_17
google trends
c_17
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.
resultresult

The Results

0 m
Data points ingested
0.0sec
Average query time
0
Dashboard Charts
"

The team are friendly and efficient, quickly getting the app/portal off the ground and rapidly acting on any given feedback.

"
avatar
logo
Jackson Smith
Quality Technical Assistant
phonebooth

Let's fix your tech problem

If you tell us about it, we can fix it

Go on, get in the booth

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