Keyword research is an important part of the optimization process. There are thousands of ways, tools and resources to do keyword research. Every SEO has their own methodology, favorite data and ways to organize and sort through that data in order to create solid keyword lists for their SEO campaigns.
But there is more to keyword research than just the research. You have to be able organize and sort your keywords into groups that will allow you to optimize them most effectively into your website.
This post will walk you through the organizational side of the keyword research process, using a customized keyword research spreadsheet I have created to make the process easy. You still have to do the research, but this tool helps us create keyword lists for on-page optimization.
Let's get to it.
First, you need to download and open the spreadsheet linked above. You should be on the "Phrase 1" tab at the bottom.
If you know what your "core" phrase is, you can rename that tab accordingly. We'll work with the phrase "battery charger" since that will give us plenty of variety to work with.
You first need to export your keyword lists from the research tool to a different spreadsheet. Make sure your search volume is to the left of the keywords. If not, you'll need to move some columns around until it is. For Google you can insert an extra column between the search volume and the keywords so you can copy/paste cleanly into this keyword organization spreadsheet.
Tip: When exporting from Google, be sure to select the exact match for accurate search volume numbers. You'll then need to use find/replace to remove the brackets before importing to your keyword spreadsheet.
After you paste the keywords, be sure to select the option that allows you to keep the formatting on the current sheet rather than importing the format from the sheet you copied from.
Once you paste both sets of keywords and search volume, your sheet will look something like this:
The first step is to alphabetize the keywords. To do this, click column "D" so the entire column is highlighted. Then, over on the far right on the Excel "Home" tab, you'll see a "Sort & Filter" option. Click that and then select "Sort A to Z."
The result should look something like this:
This spreadsheet has a cool "Merge Duplicates" button. Click that button and all the dupes will be merged together. Just like magic!
Tip: Make sure no garbage code is imported in with the keywords. If a bad text is imported into the "All Keywords" column, the merge macro can get screwed up.
Here you can see the duplicates merged together. Notice rows 16, 20, 24, and 31.
Tip: Be sure to sort on "Values" and order "Largest to Smallest."
Be careful not to use search volume alone for determining keyword value. As you can see below in rows 18, 30, and 37, Wordtracker isn't showing search volume for some substantial Google phrases. Similarly, if you were to scroll down, you'd also see quite a number of phrases that Google doesn't record as viable, but Wordtracker does.
First, scan your list looking for any negative words you want to eliminate. Then add those words to column A in the negative word tab at the bottom.
No, go back to your research tab and click the "Check Negatives" button. Any keyword phrase that contains a negative word is automatically highlighted red throughout the worksheet!
If you accidentally added a negative word that does not belong, no worries. Just remove it and re-click "Check Negatives" to correct.
Important: Don't leave any empty cells in the negative keywords list. If you remove a keyword be sure to fill in the blank spot.
If you have a lot of negative words highlighted and want to get them out of your way, the spreadsheet has a filter option that lets you hide them all. Click the filter icon at the bottom right corner of the negative column heading, then uncheck the "x".
You can then filter out all "No Good" keywords just as you did with the negative words, leaving you only with phrases that you believe are worth optimizing!
The general idea is that you want to categorize each keyword into one of these funnels. You can through this section in four steps, marking phrases that fit any one category at a time, or go keyword by keyword and mark the most appropriate category for each as you go down the list.
Tip: If you organize one category at a time you can hide each category when complete leaving only unassigned keywords. This can help make sure none are missed.
Once you're done your spreadsheet will look something like this:
Note: You will likely find that not every keyword in each funnel works for a given page. You might need several unique URLs for each filter group.
When you're ready to optimize, you can use the filtering option to hide all keywords except for the page you you are optimizing. Below I've filtered all but page 1 keywords.
One last thing. If you run out of blue URL space at the top of this spreadsheet, don't fret! You'll notice the rows jump from 6 to 14. There are six hidden rows that will allow you to add more. All you have to do is highlight rows 6 and 14, right click and select "unhide".
Stoney deGeyter is the President of Pole Position Marketing, a leading search engine optimization and marketing firm helping businesses grow since 1998. Stoney is a frequent speaker at website marketing conferences and has published hundreds of helpful SEO, SEM and small business articles.
If you'd like Stoney deGeyter to speak at your conference, seminar, workshop or provide in-house training to your team, contact him via his site or by phone at 866-685-3374.
Stoney pioneered the concept of Destination Search Engine Marketing which is the driving philosophy of how Pole Position Marketing helps clients expand their online presence and grow their businesses. Stoney is Associate Editor at Search Engine Guide and has written several SEO and SEM e-books including E-Marketing Performance; The Best Damn Web Marketing Checklist, Period!; Keyword Research and Selection, Destination Search Engine Marketing, and more.
Stoney has five wonderful children and spends his free time reviewing restaurants and other things to do in Canton, Ohio.
Copyright © 1998 - 2020 Search Engine Guide All Rights Reserved. Privacy