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!

1. 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.
2. 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.
3. 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.
4. 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):
5. 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):

   

   

   

6. 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.
7. 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!