One of the greatest challenges when setting up a new account is when there is no substantial data waiting for you. This means that you will be using the keyword metrics that are readily available to set-up a winning campaign. A problem I faced early on in my PPC career is how to use both the average CPC and the monthly search volume together to meet the budget and goals of each campaign. Sometimes you have accounts that have a rather large budget, and the focus is on getting more impressions & clicks. There are other times that an account is limited, and the CPC is very important.

However, just focusing on one of these metrics & ignoring the other is not the answer. I had trouble simply eyeballing that balance between average CPC and potential impressions. So to make it a lot easier, and a bit faster, I developed a great method using Microsoft Excel and a few rank functions. The following steps assume that you have a well developed keyword list, and are looking to maximize the impact your budget can make. I will be using a keyword list, built from the Google keyword tool, for a shoe company trying to target people who suffer from plantar fasciitis. (If you don’t feel like reading the rest you can just download the example spreadsheet available on this post & use that.)

Step 1: Upload all of your keywords to Google Keyword Tool, and get the Avg CPC & monthly search volume. Once you’ve got it, download it into excel.

Step 2: The spreadsheet you’re working with should have columns A-C filled in with Keyword, Monthly Search Volume, and Avg CPC, respectively. In column D, copy and paste the following Excel Function: =RANK(B2,$B$2:$B$411,0). This will rank each keyword’s monthly search volume in relation to all other keywords.

Step 3: In column E, copy and paste the following function: =RANK(C2,$C$2:$C$411,1). This will rank the Avg CPC of each keyword in relation to each other. There is one important difference in this function. Notice that the last number is a 1 instead of a 0. This means that lower CPCs will be assigned a higher rank (this is based on the assumption that you want to pay less per click).

Step 4: Here is where the magic happens. This is where you may have to adjust some numbers on an account by account basis. Copy and paste the following function into column F: =(D2*0.3)+(E2*0.7). Then, sort the keywords by column F. In case you can’t tell what I’m doing here, I am taking each of the ranks, and weighting them based on their importance. So, in the example I’ve put together, budget takes precedence over potential impressions. So the CPC is given 70% of the total rank and the search volume is given 30% of the total rank.

I think the best way to get across the effectiveness of this method is to look at some summary statistics over the different ways to sort the keywords.
If we sorted them solely based on search volume for the first 50 keywords we would get:
Avg CPC: $1.30
Potential impressions: 1,782,900
(That’s a lot of potential impressions but on the higher of the CPC for these keywords)

If we sorted the keywords based on CPC for the first 50 keywords we would get:
Avg CPC: $0.80
Potential Impressions: 316,343
(We dropped the CPC to a better range, but it cost us over 1 million impressions)

If we sort the keywords based on our weighted average for the first 50 keywords we would get:
Avg CPC: $0.86
Potential Impressions: 634,800
(We’ve more than doubled our impressions from the volume sort, with <10% increase in Avg CPC)

Now it’s possible that some people out there may be able to eyeball a list, pick and choose certain keywords, and get better numbers than this. Remember, this is a quick & easy process that can be repeated on an infinite number of accounts. Adjusting the weights as needed, of course. I typically only make 10% jumps, but if you have enough time, or the keyword metrics are incredibly similar you could use 5% or even 1% jumps.

Download a sample spreadsheet here.

 
Opinions expressed in the article are those of the guest author and not necessarily bgTheory. If you would like to write for Certified Knowledge, please let us know.