Part II – My First Project
The problem: automated budget tracking and management
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:
- It is possible to set daily spend limits in Bing and AdWords, but doing so results in lost opportunity, because of the variability of search volume.
- Advertising platforms offer functionality that can theoretically optimize bids to a budget target, but only if you are willing to ignore performance factors that exist outside of the platform.
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:
- To automate retrieval of daily spend data from Bing, AdWords, Facebook and other sources.
- To make budget and pacing data available to campaign managers in Google Sheets, and update data daily.
- To provide alerting when a campaign was spending but not mapped to an active order.
- To become a better programmer.
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.
The solution
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.
Details on each component
- Supermetrics handles the scheduled fetching of data from Google, bing and Facebook.
- Data from each of these sources is stored in separate Google Sheets. Each sheet acts like a table in a database. I chose to do this because keeping the design modular would make reuse easier and I could make changes to a module without having to update everything.
- The order management system is something I built using Zoho Creator several years ago. Creator makes it easy for non-programmers to build web applications. It also has an API, which I was able to use to fetch the data I needed in Sheets. Everything in the budget tracker is organized around the notion of orders, and the order management system is where account managers specify the scope, duration and budget of orders.
- The budget tracker brings all of this information together to give account managers a view of each of their accounts, month-to-date spend, spend targets and other information relevant to setting bids and budgets. It includes the following parts:
- Mappings – a tab mapping the relationships between orders and campaigns in AdWords, Bing, Facebook, etc.
- Error checking – a set of scripts that look for unaccounted-for spend and write errors to an errors tab.
- The budget tracker – a tab with a dashboard view for account managers.
- Scripts that fetch the data from the order management system, the Supermetrics sheets, and the mappings tab, and write the combined data to the dashboard.
- There is also a human process of reviewing errors and creating new mappings when an account manager adds new campaigns to an order he/she is managing.
A few Google Sheets tips:
- Learn how to use the FILTER spreadsheet function. Wow. That said, try to do as much as you can with scripts. Too many formulas will bog down your Sheet.
- The Filter option in the nav bar (totally different than the FILTER() spreadsheet function) affects everyone that is viewing the sheet. Applying saved views does not. I created a saved view for each account manager.
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.
