Finding Golden Keywords With Traffic Travis Part 2

Aim of article: To organize and sort through your list of high quality keywords.

Cost: $0

Time to complete: 30 minutes, medium effort.

What you will need: Excel or Google spread sheet.

In PART 1 of this series, I showed you how to find highly searched keywords with low competition and a high cost per click. In this 2nd part, I will show you out to organize this information in a nice Excel sheet so that you are able to sift through these great keywords and find the best of the best.

First, let’s load up the two csv files that we created with Traffic Travis yesterday.

Here are the two spreadsheets you should have in front of you.

The next step is to combine them. Copy and paste one alongside the other. Make sure that the keywords match up. If they don’t, we will be working with inaccurate information. Once we see that the keywords match up, place a filter over the columns so that everything remains organized.

*Tip: If they don’t match up, highlight the columns in one set of information and go to Data>Filter. Then arrange alphabetically. Remove the filter and do the same for the second set. 

Now we can clean this sheet up a bit. Delete all of the columns except for the keyword, difficulty, global monthly searches, US monthly searches and the CPC columns.

Now we are looking at this:

Looking a little better now, isn’t it?

Next, we are going to add the following formulas into columns “F” and “G”.

In cell F1:               =C2*0.5*0.02*E2

In cell G1:               =D2*0.5*0.02*E2

Copy F1 and G1 and paste them all the way to the bottom of our list.

This is weird! What is this? This is a generic formula to find the ballpark value of ranking #1 for that keyword.

Note: This is not an accurate way of determining the value of a keyword. It is only a quick and easy was to compare one keyword to another in our list.

It is time to sort!

Use the filter to get rid of the medium and difficult keywords, and then sort largest to smallest in column F. If you followed along, you should be looking at a list of 197 EASY to rank keywords that have 1,000+ searches, $1+ CPC, with the best at the top of the list!

Go through the list and see which keywords you like. Right away, you will see

hydraulic – 40,500 searches per month with a CPC of $1.58.

hydraulics – 22,200 searches per month with a CPC of $1.95.

If you can rank for those keywords, that’s roughly $1,000 per month plus long tails like “hydraulic hose” which you can find further down the list.

Look through your list and see what appeals to you.

That wraps up our hunt for the golden keyword. Now that you know the process, use your own seed keywords and try it yourself. In part 3, we will get back to Traffic Travis and I will talk you through the process of selecting one of these keywords, as well as how to find some good long tail keywords to use on your site.