This is the part where I put everything together in one spreadsheet and decide what keywords to go after.
I come from the corporate world and in my experience the single piece of software which the corporations cannot possibly exist is MS Excel. Although I was writing macros we will not need that kind of knowledge in our keyword research process. But first we need to export all data.
Exporting From Market Samurai
In Market Samurai we were looking for keywords with TAComp of 1,000 or less. See the tutorial here
TA Comp (Majestic Title & Anchor Competition) – The number of pages in Majestic’s index that contain the keyword term in both the page title and in the anchor text of an external link.
While in the keyword research module and after applying the filters hit the Export button. It will export everything into a csv file. What you will end up with is this:
I love simplifying this so I drop all columns which I don’t need. These are – SEO Traffic, Majestic Title Comp, Majestic URL Comp, Majestic Anchor Comp and Searches/SEOT Estimated. This way I have only the searches and TA Comp left.
Save the file as xlsx and let’s go to LongTail Pro
Exporting From LongTail Pro
Load your campaign and click on the Keyword Results button. If you haven’t done so, calculate the Keyword Competitiveness for all keywords before exporting.
After you have all the data just click on the Export Results button. Just like with Market Samurai, your results will be saved in a csv file.
As we are interested only in the Keyword Competitiveness index we can safely delete all unnecessary columns. After that we need to remove the brackets. We do that so it will be easier later to combine the results from Market Samurai and LongTail Pro.
The Easy Way to Remove Them
Simply press CTRL+F to open the Find and Replace dialog and go to the Replace tab. In the first text box type “[“ and leave the other one empty, hit Replace All. This will remove the opening bracket. Now do the same with the closing brackets.
Alright, copy the two columns and paste them in the second sheet of the Market Samurai’s export.
The Almighty VLOOKUP
This is probably the function which I use the most in Excel. If you would like to know exactly what it does I suggest you go and read this – Using VLOOKUP in Excel
All we need to do now is create a new column in the original table called Avg KC. Using VLOOKUP just match the Keyword Competitiveness from the second spreadsheet to the keywords.
From here what I like to do is just color in green all keywords with KC of 30 or below, orange (kinda) for 31-35 and red for 36+. This gives me a better perspective of the potential difficulty of the keywords.
If you want to select a main keyword make sure it has enough monthly searches. If you don’t have green high-volume traffic keywords look at the orange or red ones too. Your other option is to just select more keywords to go after… or do both if the keywords allow it.
Although the keywords are pretty obvious now I like to have a look at the competition for the green and orange keywords. It’s really easy and I don’t go too deep. I will show you how to do it next time.
Proper Keyword Research 01 – Coming Up With Ideas
Proper Keyword Research 02 – The Shotgun Approach
Proper Keyword Research 03 – Now We Are Getting Somewhere
Proper Keyword Research 04 – Getting a Second Opinion
Proper Keyword Research 05 – Let’s See What We Have Here
Proper Keyword Research 06 – I Spy With My Little Eye…