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
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 is 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. The system now processes 23 million data points across 1,623 search terms, 5 years of historical data, and 9 geographies, delivering results with an average query time of just 0.8 seconds.
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. The final solution includes 34 dashboard charts, each providing actionable insights into market share of search metrics that help Rival's clients understand their competitive positioning.
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.
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.
