How to Use Excel to Estimate New PPC Account Performance
To start off our September PPC Hero series on how to use Excel for that next level of PPC super performance, I decided to do just that: start from the beginning! Is there a way to determine possible account performance before opening up the floodgates and turning your account on? We got this question via our Twitter account last month and the short answer is yes…and you can do it using Excel and a few simple formulas. The long answer is…well you’ll see.
First, I want to say that the following strategy is one I use to determine what I might be able to expect from a new account and I’m sure other account managers have various methods that differ from mine. What’s important is getting some kind of baseline data to look at and see if you’ll be getting anywhere close to your goals using the paid search medium, be it traffic or budget. As long as the method you’re using gets you some educated data to mull over, mission accomplished.
The last disclaimer I’m putting on this post is that the driving term here is ‘estimate.’ I never forward this information on to clients or our agency teams with a guarantee or notion of “we’ll provide you X leads and get you a Y CPL.” Never ever. All of these numbers must be qualified by the fact that they are estimates, with some assumed metrics applied in order to get some ballpark figures.
The overall point is that we’re making some educated guesses when estimating performance of an account before it goes live. Now that we’ve had that little chat…let’s dive in to the process!
- Complete some keyword research, if you haven’t already. I usually start on the client’s website and pull a handful of keywords from each page of the site or something similar. I then plug those terms I found in to the Google Keyword Tool to find some more ideas or variations of the terms I pulled manually. I suggest clicking the ‘Only show ideas closely related to my search terms’ option before running the Keyword Tool, so that you’re keeping things as tightly-themed as possible in this step.
- Pull the Keyword Tool data out to an Excel spreadsheet. While you can see all the data I’m about to discuss within the actual interface, I want to run some calculations on those numbers and possibly edit the list of terms, so it’s much easier to do if you pull all the data out in to an Excel sheet.
- Edit the proposed keyword list. This is an important step because while the interface isn’t intentionally trying to skew your data, depending on what vertical you’re getting information for, the interface could be tying keywords to your search that aren’t quite what you’re looking for. Find those terms in your spreadsheet and delete them and their data from your list. In this exercise, I pulled 61 keyword ideas into my Excel sheet, but ended up only keeping 37 of them for the remainder of the example based on relevancy.
- Total up searches and average out CPC. The data you just pulled from the Keyword Tool is going to show you approximate monthly search numbers (local and global), as well as estimated CPC’s and competition for each term. Total up your monthly search columns and average out your CPC and competition columns. This should give you a total number of searches per month across all your terms and averages for CPC and competition for all proposed keywords. That will look something like this (I hid a bunch of rows to get the column headers in the shot, FYI):
- Apply ‘industry averages’ to your totals.Now that you’ve added up searches, you’ve essentially got a guesstimate on total number of possible impressions in a given month for your term list. Using that number and your averaged out CPC, you can apply a typical or goal CTR percentage to ballpark clicks, cost and perhaps even conversions. Kind of like this (I used a 1% CTR and a 2% conversion rate on this example):
- Check the same terms in the Traffic Estimator. So you have some baseline reach and CPC estimates for a possible account structure, which is great. Putting all those same terms in the Traffic Estimator will allow you to manipulate your max CPC or daily budget to see how your traffic could fluctuate based on your budget restrictions or goals. You can also take a look at the corresponding graphs to see when additional budget (by click or day) will begin to plateau and stop bringing in proportionally more traffic.
- Take a look at the competition. Using a website like Spyfu or something similar, search out one of your top competitors and see what they’re spending and how many PPC clicks they’re getting in a day. Keep in mind these websites are very much rounding numbers and you may end up with some figures that don’t seem 100% accurate…because they probably aren’t. If you keep that in mind while looking at this information, it can give you more information about what you could be looking to spend or how much action you can expect to see as a result of launching your campaign in a similar vertical.
As I said at the very beginning, this is most definitely not the only way to get information about how your account could perform or what kind of reach/budget you might be looking at. If some of our readers have other methods, I encourage you to share them below! Take a couple different routes if you can, and average out what you find with a different method and this one. Have something interesting that happened to you in regards to running similar pre-launch estimations and finding out the actual performance varied greatly? Share you thoughts and ideas in the comments section below and also be sure to stay tuned the rest of this week for the remaining four posts on how to use Excel to your PPC benefit!
Get Your Brand in Front of Dedicated PPC Pros!
We're headed to London to join nearly 300 of our best PPC friends. And we still have a limited number of opportunities for your brand to join us!
The Top Ten Tips For Effective Dayparting
Determining your need for hour of day modifiers can provide extremely competitive returns based on time invested in implementing.
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
Should You Be Creating Device Specific Campaigns?
Creating new device specific campaigns is a lot of work and can potentially overcrowd your account. You need to determine if this tactic is right for you.
9 Hero Conf London Advanced Sessions You Can't Miss
If you'd like some more details (and we've got plenty of details), how about a rundown of the deepest, most actionable sessions you'll see. These 9 sessions are designed for the expert-level PPC professional.
Why Your PPC Account Isn’t Living Up To Its Potential
I will review common themes that I see in either prospective clients, new clients, or accounts that are red that aren't living up to their potential.
[New Webinar!] 6 Steps To An Advanced Competitor Analysis For Digital Marke...
In this new live webinar, Hanapin’s Jacob Fairclough and SEMrush’s Paul Klebanov will direct you through the process of analyzing your own account, comparing it to your competition, and taking the steps to put your brand above the rest.
5 Psychological Principles You Should Be Using In Your Marketing
I propose that knowing what your audience is thinking or even the steps in their thought process is even more beneficial than simply knowing who they are.
Add It To The List - Structured Snippets Now Available In Bing
Another item for the list of new features in Bing - Structured Snippets are now available to all U.S. advertisers, with international support already being rolled out.
Do You Want A Free PPC Hero T-Shirt?
We're conducting a brief 2-minute survey to get your thoughts and opinions. By taking the survey, you'll get entered to win a certified PPC Hero shirt! Fill out the survey by Friday, September 23rd.
4 Tips To Survive An Account Manager Change
Have you ever received a call or email from your agency that you will be getting a new account manager? The thought could be frightening, especially if you LOVE your current AM. After you take a deep breath and realize life will go on, keep...
Build Your PPC Network, One Pro at a Time!
Do you ever feel that the people who understand your role best live mostly on Twitter? Is it sometimes difficult to find those few individuals you know you can count on for the best PPC advice? We’ve got the solution for your paid search woes.
Balancing Volume, Competition, And Intent To Find The Right Keyword
Although it isn’t going to bring results as quickly as a PPC campaign (and you can even use these methods for PPC), you can’t go wrong with a good old organic traffic campaign running alongside your ads.