Automated Client Budget/Pacing Dashboards

Overview: At Ziggy, managing client budgets and pacing documents was a time-consuming manual process, often requiring updates every two to three days across multiple countries or business areas. The goal was to ensure that spending stayed within the allocated monthly budgets for each client, but this approach was inefficient and prone to errors. To streamline the process, the Automated Client Budget/Pacing Dashboards were developed. These dashboards enabled daily automatic updates of spend data, allowing growth marketers and clients to quickly adjust and monitor budgets in real-time, significantly saving time and enhancing accuracy.

Objective: The primary objective of this project was to create an automated system that synchronised data streams from various platforms, ensuring all campaign spend data was accurately filtered into designated campaign groups within the dashboards. This automation aimed to improve the accuracy of budget pacing, enable more strategic decision-making, and free up valuable time for growth marketers to focus on optimisations and other high-value tasks. Key performance indicators (KPIs) included the accuracy of daily spend data, time saved in budget management, and the reduction of manual errors.

Role & Responsibilities: As the lead on this project, my role involved integrating BigQuery data with individual client dashboards. I was responsible for filtering and projecting this data into Pivot Tables, which were then linked to each client's pacing documents. This required the creation of custom formulas, lookups, and data transformations to ensure that each piece of information flowed correctly into the relevant groupings within the budget pacing documents.

Approach & Tools Used: The approach centred around using Google Sheets to import BigQuery data, which was then structured through Pivot Tables. By leveraging functions such as =IMPORTRANGE, =IF, and =SUMIF, I was able to create dynamic dashboards that pulled data into the correct categories based on a comprehensive set of lookup codes. These codes combined various data points like month, project, and region to ensure accurate categorisation and reporting of spend data.

Challenges & Solutions: One of the key challenges was matching campaign data to the appropriate cost groups, especially when dealing with a high volume of data across different clients and regions. To address this, I developed a sophisticated lookup system that generated unique codes for each campaign based on multiple criteria. This allowed for precise filtering and aggregation of data, ensuring that all relevant information was accurately captured and reported in the dashboards.

Results & Impact: The implementation of the Automated Client Budget/Pacing Dashboards had a transformative impact on Ziggy’s operations. The dashboards were rapidly adopted by the internal team, providing a reliable, daily view of client spend pacing. This automation not only enhanced the accuracy of budget management but also freed up significant time that could be reallocated to more strategic activities, such as optimisations and ad-hoc tasks. The new system streamlined the entire budgeting process, leading to more effective decision-making and improved client satisfaction.

Key Learnings & Reflections: Through this project, I gained valuable insights into the complexities of data integration and automation, particularly the importance of creating robust data validation processes. The experience underscored the value of automated systems in reducing manual workload and improving data accuracy, and it enhanced my skills in using advanced functions within Google Sheets to build scalable, reliable dashboards. In hindsight, incorporating even more advanced data validation steps could have further reduced the initial setup time and improved the system’s resilience to data discrepancies.