Using Excel to Get the Best Budgets for Your Campaigns
September 12, 2012
One of life’s essential conundrums is how to allocate your resources. Whether it’s your energy, your time or your money, figuring out what to do with what you have determines what kind of person (or company) you’re going to be.
When it comes to life, most of us are left to our own devices, which explains why I spend such an inordinate amount of my time watching reruns of Chopped on my beloved TiVo or why Eric Couch spends so much of his time reading on his beloved Kindle. There’s no spreadsheet for life, guys. It’s up to us.
You know what there is a spreadsheet for? PPC. There are lots and lots of spreadsheets in PPC. (We’re even writing a whole series about them!) The best thing about spreadsheets? They can answer all of the tough questions for you.
One such tough question that needs to be answered: how to allocate our budgets? There are some important steps to take prior to heading into the sweet, sweet salvation of Excel, however.
Start by asking yourself a couple of questions:
- Are you limited by budget to begin with? If there isn’t enough traffic out there that’s pertinent to you, you probably don’t need to worry about budget allocation. Grow your account or campaign, then check back here.
- What is most important to my account? CPL? ROAS? Lead quality? You need to identify your key performance indicator, as everything we’ll do today is based off of that KPI. Make sure to factor in any mitigating considerations for your account, such as geography or lead type.
Once you have a KPI identified for your account, we can move over to Excel and get crazy (or logical).
With your KPI as a starting point, look at recent performance in your account. Brand new accounts should be based off of the projections from Kayla’s recent post, so this analysis will be for existing accounts only. We’re going to be optimizing our budgets off of the past month’s worth of data (or whatever timeframe makes sense to you/your account).
The longer the account has been around, the better grasp you’ll have on the sales cycle for PPC and also performance across different campaigns. Be aware that the data you’re using could mean different things in six months. We’re doing the best that we can to optimize with everything we have available.
If your KPI is CPL and CPL alone, once you download a campaign report you’ll have all of the data that you need. It’s a simple process – just sort by CPL (ascending) and see if your best campaigns are limited by budget at all. If their Lost IS (Budget) is anything higher than 0.00%, give them more budget. The converse is also true – stop giving as much money to your high CPL campaigns.
Hopefully, your KPI will be something more in depth than CPL. All leads are not created equal, so CPL can be a devilish little figure. If you’re e-commerce, you should have revenue figures tracked through analytics. If you’re lead gen, you are hopefully tracking your campaigns on the backend for lead value/quality. Failing all of this, you should at least have a sense of which keywords are more valuable than others. I.e. “buy new bike” is going to have a higher implied value than “buy new bike pump.”
If you have real, concrete data on hand, use Excel to look it up and pull it into your campaign report. VLOOKUP is in intimidating-sounding function that is actually just the greatest thing in the world. All it does is take one value in one spreadsheet, look for that value in a different spreadsheet, and then pull in associated data for the two values.
For setting my budgets I would VLOOKUP to get ROAS in my campaign report:
lookup_value: Click the cell your campaign name is in
table_array: Select the columns/area in the second worksheet where you want your data from. Make sure to include the entire range, so I just make it easy on myself and select entire columns. Also, make sure that your campaign column in the second report is on the far left side of your new range (which means that you may need to reshuffle your column order in that report).
Col_index_number: This is what cell Excel should pull in from your second report. With the campaign name being index number one, count up to the column of data you’re looking for. (This step usually leads to me mumbling numbers softly under my breath and contributing to my overall look of “nearly deranged homeless man.”)
Range_lookup: Type in the word “false.” This tells Excel to only bring back results from campaigns that match exactly.
It’s also important to note that the two values you’re trying to sync up need to match exactly (meaning no trailing spaces, the hyphens need to be exact, spacing between words, etc.). That caused me a lot of grief in my early VLOOKUP days (especially with numbers that were formatted as text). If you get all #N/A’s, double check that your values are matching.
Now that you have ROAS in your report, you can sort by it. Determine what’s bringing in the most revenue for you and allocate your resources accordingly. When it comes to ROAS, that’s money that you’re making for your company directly. Not only should you ensure that you don’t lose any ROAS to Budget, you should also take a look at decreasing your Lost IS (Rank) (a decrease of a loss is a good thing, as any bad grammarian wouldn’t be able to tell you).
You won’t magically be able to spend as much as you want just because you have a high budget allotted for your great ROAS campaigns. The final step to setting monthly budgets with Excel involves some quick projections to see where you’ll spend now that you’ve decreased your lost IS to budget.
Look at the cost that you had in your date range. That value is going to change heading forward (assuming that your budgets were previously capped or that they will be capped in the future). Modify that daily spend number by the amount you changed your budget. This will just be (Change in Daily Budget) x (Number of Days in Your Projections). You can then subtract or add this to your previous month’s total spend to get projected monthly spend. Keep in mind that accounts aren’t going to hit their budget exactly every day, so give some wiggle room based on previous performance. Especially for your top performing campaigns, ensure that they have plenty of headroom in the budget. If they go over in projected spend it will be a happy accident.
Since budgets are set on a daily level, I always look at my spend in daily increments. Simple stuff – projected cost/# of days in your data (which is a nice little formula that you can put in your spreadsheet).
This figure needs to be pretty close to what your target daily spend would be (target daily spend is your budget/days in the month). If you’re far below goal, allocate more budget in campaigns that are still capped that may not have the best KPI. If you’re over daily spend still, remove even more budget from your poorly performing campaigns. Watch those spends as the month goes on and adjust accordingly. This should at least be able to get you to a starting point for your monthly budgets.
Expanded Text Ads And Other Bing Ads Editor Updates
Pilot access for Expanded Text Ads is now available to Bing Ads users. Get access to the link and learn about other new features in the latest Bing Ads Editor update.
Keynotes from Bing & Google + the Hero Conf Advanced Track
Where do you go to get the most relevant information in PPC, plus the opportunity to network with the best and brightest the industry has to offer? Why Hero Conf, of course.
3 Questions You Need To Answer Before Beginning Paid Advertising
So you’ve you decided you want to start PPC advertising, but now you are asking, “Where do I start?” We've listed and answered 3 questions you should be asking.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Road Trip - 5 Tips For Creating International Campaigns
International markets can add complexity to any account, but here are a few settings and opportunities to make your global ad strategy stand out in non-US markets.
Discover 3 Levels Of Geo Targeting For Beginners, Intermediates, & Experts
Setting geographic targets is day one. Whether it’s to exclude places you cannot sell or serve or to specifically target your primary customer base, the implementation of geo-targets is on the must-do list.
[New Webinar!] What You Need To Know About New AdWords Features
In this new live webinar, Hanapin Associate Director of Services Jeff Baum and Optmyzr’s CEO Frederick Vallaeys will walk you through the new AdWords tools and show how you can use them to optimize your accounts.
The Social Strategies You Need To Know For App Promotion
Create social strategies to promote app awareness and leverage geographic and demographic data reported by these platforms to refine audiences.
Guide the Industry and Take the 2016 State of PPC Survey!
Each year, Hanapin Marketing manages extensive research into the State of PPC Report, which provides a statistical breakdown of the digital ad industry and guides expectations.
What's In A Name? Building Powerful Product Titles For Ecommerce
Over the years, we've tested a variety of different product title alterations in hopes of finding the perfect title. Did we? Yes, and no. The fact of the matter is, it depends.
What Upgraded Bing URLs Mean For You
Bing is continuing its effort to catch up with Adwords. Upgraded URLs, which isolate tracking parameters from final URLs, are now available to Bing advertisers.
Where Does Your Hero Conf Dollar (or Pound) Go?
5 Common Excel Issues And How To Fix
Excel, while a fabulous tool for PPC, is not without its occasional hang-ups. In this post, we’ll go through some common problems, and how to fix them.
[New Whitepaper!] Guide to Google AdWords: Advanced Edition
In this whitepaper, you will gain a greater understanding of the distinction between e-commerce and lead generation PPC strategies, learn the effective use of Remarketing, how to utilize advanced segmentation for improved targeting, cross-device attribution, and how to use automated rules in your accounts.