As a PPC account manager, the most critical eye— Sauron’s eye— is often cast on your ability to analyze and optimize; to study data and extract actionable insights that make the top line metrics like cost per click, cost per acquisition, or return on ad spend, trend towards a marketing goal.
With these types of demands, and the pressure that comes in tandem, it can be easy to lose sight of some of the more basic responsibilities of managing digital accounts. In this instance, I’m speaking specifically about managing budgets.
No account manager, no matter how skilled or mindful she is, is immune to mistakes or oversights. Often in an agency environment a single account manager can be responsible for managing twenty to thirty individual budgets (to say nothing of all the managerial minutiae that occurs inside those budgets).
If you’ve spent any considerable amount of time in the marketing industry, you’ve probably had a moment: you pull up your spend sheet with 10 days remaining in the month and realize you’re projected to eclipse the budget in, oh, an hour or so. You break out into hives and a cold sweat. You slink off to the office sink to throw cold water on your face. You wrap a microfiber blanket around your shoulders and you shiver.
And you never let it happen again.
Yeah, right. The notion that all budgets will be managed perfectly— especially when you’re contracted from all angles— is an idealism, not a reality. There are tremendous amounts of moving parts underneath a budget, especially in the digital realm. Every penny spent is subject to an auction, often times with thousands of participants. Advertising platforms don’t necessarily view the budget you set as a hard cap. Google, for instance, gives itself the freedom to exceed each campaign’s daily budget by a certain percentage. Especially in accounts with a lot of campaigns, the compounding effect of this allowance can be disastrous for budget management.
I’m going to show you one of my favorite ways to project your end-of-month spend, in a way that accounts for some (but not all) account variables, and most specifically, dayparting.
First, open up your good friend Microsoft Excel and create a row for each day of the week.
Now, we’re going to utilize one of the greatest achievements in the entire history of civilization: the calendar.
Create two more columns: one for the amount of days that the weekday has already occurred, and one for the amount of weekdays still remain. So it should resemble this:
Cool. Now let’s shift out of Excel and into my favorite part of AdWords: the dimensions tab.
Click there.
Then click here.
Great. Now use this view to understand how much money your account has spent on each day of the week so far in the month.
Use your magic column-creator and put this information in your spreadsheet.
The spend numbers in the AdWords and Excel screenshots aren’t aligned because the numbers were pulled at different dates. That’s neither here nor there. It’s time to harness your column-wizardry once more. And guess what? Now we get to start doing some math. Don’t worry, nothing too complicated. Just some simple division.
Divide your total spend column by your days passed column to yield your average weekday spend column.
total spend / days passed = average weekday spend
Now, add up your total spend and put it in a cell labeled MTD spend.
Alright, now we have all the tools to get our projection. This is where the magic happens, people.
What you’re going to do now is multiply each “days remaining” cell by its corresponding “average weekday spend” cell. Then you’re going to find the sum of those products and add it to your MTD spend.
Here’s a screenshot of my excel formula, as well as the row and column labels for your reference.
If you format your table precisely as I have, this is your formula:
It looks like a lot, but it’s simple when you boil it down. This formula answers three questions, and in this order:
1) How many instances of each day of the week remain in the month?
2) How much spend does the account average per day of the week?
3) If in-account factors remain constant, where does the end of month spend project?
Notice the qualifier there: “if in-account factors remain constant”. Obviously this isn’t a given. I said as much earlier in this post. That doesn’t mean that this methodology can’t give you a great idea of what kind of track you’re on. But you should supplement this type of projection with one that accounts for any big account changes you might have made recently.
Here’s a formula for a normalized projection.
(month to date spend) + ((last 7 days spend / 7)*days remaining in month)
This formula takes into account the average daily spend in your account over the course of the last 7 days and applies it to the rest of the month. You should look at your dayparting-based projection simultaneously with your normalized projection. Cover all your bases. When the end of the month comes, you’ll be right on target.
(featured image by patrick/Flickr)
