My first project with Google Sheets and Google Apps Script (GAS) was to rebuild the campaign budget tracker we use to monitor budget pacing in paid search campaigns. This was the core functionality I had tried to replace first with a 3rd-party tool, and then by hiring a developer. If you missed that part of the story, check out Part I: Failure and Recovery.
Without the help of a tool, budget tracking and pacing is a tedious and time-consuming process. Most of the campaigns we manage have a fixed monthly budget. And one advertiser might have multiple budgets, and those budgets might encompass multiple advertising platforms (AdWords, Bing, Facebook, etc.). We need to be able to keep track of how much has been spent against each budget throughout the month, so we can make adjustments to evenly pace the spend and make sure the budget lasts to the end of the month.
Several other dynamics that add complexity to budget pacing:
At the time I began my re-education, we were using a tool I’d built in Excel with VBA macros. Extending Excel with VBA was pretty cool in the late 90s, but by the 20-teens, it was out-of-date, and Microsoft had actually taken away VBA features in Office 2011 for the Mac, which is what I used. And as Two Octobers grew, the budget tracker had to handle more and more campaigns, which was bogging it down to the point where it was all but unusable.
My goals with the new budget tracker were:
And, most important of all, my overarching goal was to build a framework with which we could create other tools, such as automated bid management and KPI dashboards.
Once I had the programming skills to start, and an idea of where I was headed, I did not spend a lot of time planning. I assumed that the first version I built would be a working prototype, which would help me to better understand what I wanted in a first, production release.
One of my first choices was to use Supermetrics to retrieve data from the advertising platforms, rather than trying to figure out the APIs myself. Supermetrics provides add-ins for Excel and Google Sheets that fetch data from Google, Bing, Facebook and a variety of other sources. This functionality proved invaluable for what I was trying to do. The combination of Supermetrics and Google Apps Script has truly been game-changing for us.
Following is the architecture I ended up with, but understand that this was arrived at through trial and error rather than careful consideration.
A few Google Sheets tips:
The budget tracker in action:
Before sharing with the team, I wrote pretty detailed documentation on how the budget tracker works, and how to make updates. It has been in use for more than a year, and meets all of my initial goals. We currently manage > 500 separate order budgets, and it has handled growth very well. I make improvements every once in a while, but on the whole I’ve had to do very little to maintain it.
All told, it took me about five months from the time I started teaching myself Javascript to when the whole team was using the new budget tracker, and I still had my day job to worry about along the way. This is the power of Google Sheets, Google Apps Script and Supermetrics. With Sheets, you have an incredibly powerful tool for analyzing and reporting on data that sits on top of a scripting platform that can talk to literally anything with a web-based API. Plug Supermetrics in to that, and you barely have to write any code before you are automating your daily tasks.
In the next and last post in this series, I’ll describe our experiences as we make the transition to an organization of tool builders and computational thinkers.
Part I: Failure and Recovery
Part III: Automation Everywhere
Nico Brooks oversaw the development of the first commercial SEM management toolset, Go Toast, way back in the year 2000. Since then, he has worked with a variety of agencies and publishers on tools and processes to improve marketing efficiency and effectiveness. At Two Octobers, he gets to collaborate with a bunch of really smart people and play with code.
The latest developments in digital marketing include Meta feature updates, Google downgrading ranking on vastly…
ChatGPT traffic in the GA house! Plus new features in GA4 and understanding GTM first-party…
This article details the process of building two BigQuery tables for path analysis, with a…