Whether you are on the agency or client side, two of the most important aspects of PPC management are budget management and identifying significant performance fluctuations in real-time (e.g. spend doubled from the prior day unexpectedly). Unfortunately, there aren’t many out-of-the-box options that help monitor budgets and performance fluctuations. In this article we will be discussing how we’ve improved our budget management and trend monitoring efforts through automated reporting, which has been instrumental in delivering better service to our clients.
A Brief History
Prior to our more automated approach to daily tracking, we were doing things the old-fashioned way. Manually scraping data from sources like Google Analytics and Google AdWords (now Google Ads) and adding them to Excel-based tracking documents. This would create inefficiencies and more opportunity for manual error.
I was then tasked with finding a better way to do this and found a marketing reporting tool from Supermetrics. They have many products that can help you with a number of tasks, we’ve found their Google Sheets plug-in to be the most useful for this task. Their plug-in allows us to pull data in customizable formats from multiple sources (Analytics, Google Ads, Bing Ads, etc.). The best part is that it also allows us to refresh the data for different cadences (daily, weekly, monthly, etc.). This eliminates the manual process we were stuck with in the past.
Our Approach
We’ve also included 60 days’ worth of daily performance (some rows hidden) with conditional formatting to help better identify trends. As you can see, we’ve come a long way since only focusing on daily spend. We now include KPIs such as CPC and CPA that are important metrics to monitor daily and weekly, to account for changes in account behavior and competition.
Below, we’ve shared an example of what one of our PPC budget tracking sheets looks like. This is an abbreviated view that has several rows and columns hidden to make the view more digestible. The main components include the ability to switch between campaigns types (in this case generic vs brand), summary rows that show pacing & trends, and a budget summary table (which should be broken out by geos if there are dedicated budgets) that displays spend and CPA trends against the month’s budget.
The biggest benefit from this new template is that it allows us to deliver insights more efficiently and effectively to our clients.
How to set up your own daily spend tracker
Setting up your own daily tracker is a relatively straight-forward process if you have the appropriate Excel skillset. We’ve included the necessary steps to getting your daily spend tracking sheet up and running here:
- Sign up for a Supermetrics account
- Link your Supermetrics to all appropriate accounts (e.g. Google Ads, Facebook Ads, Google Analytics, etc.)
- Create a Google Sheet & start generating data pull queries from Supermetrics
- Structure your front-end dashboard tab to show all relevant KPIs (see image above)
- Use various Excel formulas (see below for examples) to pull appropriate data into your dashboard
- Ensure all data pulls are set to refresh daily (or whichever cadence you prefer)
- Be sure to QA your data routinely to ensure it is pulling correctly and that Supermetrics isn’t sampling your data
Some helpful formulas for PPC spend tracking
Below, we’ve included some helpful formulas to help you construct your own daily spend tracking sheet. These simple formulas will help you sum data for specific date ranges, in addition to other functions.
- First day of month:
- =EOMONTH(TODAYS DATE,-1)+1
- Run rate for the past 7 days:
- =((sumifs(SPEND RANGE,DATE RANGE,”>=”&TODAY()-7))/7)*DAY(EOMONTH(FIRST DAY OF MONTH,0))
- Sum last X days of a data set:
- =sumifs(SUM RANGE,DATE RANGE,”>=”&TODAY()-X)
Final thoughts
If you are looking for a better way to track daily performance and budgets, we recommend adopting a similar approach to ours. It’s a critical component to PPC management (as well as other channels like Social and Organic) that often gets overlooked. Once a more streamlined reporting system is in place; the process becomes much more efficient.
You can do so yourself, but if you’d rather leave to the professionals, please contact us by email at sales@synapsesem.com or by phone at 781-591-0752.
