Marketing Matrix: The Ultimate Keyword Sorting and Organization Spreadsheet

by Stoney deGeyter

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.

Importing Your Keywords

Before you can import anything you need to have already gathered a list of keywords from your favorite keyword tools. For this post we'll use Google and Wordtracker.

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:

Note: Google will let you export up to 100 phrases while Wordtracker can give you hundreds if not a couple thousand. This spreadsheet can handle a little over 2000 phrases from both Google and Wordtracker (or your favorite source) combined. To keep this post simple, I've eliminated all but 600 phrases.

Merging Duplicate Phrases and Data

A good number of phrases will be duplicated between your research tools. We want to merge these so the Google and Wordtracker numbers are on the same row for each keyword.

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."

This will create a "Sort Warning" dialogue box:

Sort Warning

Be sure "Expand the selection" is selected; then hit "Sort". This makes sure that the search volume numbers stay with the correct search phrase as they are sorted A to Z.

The result should look something like this:

If you look at rows 16/17, 21/22, 26/27, and 34/35, you'll see the same phrase with a different search volume for both Google and Wordtracker. These are the duplicates we want to merge.

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.

Now you want to sort by search volume. Going back to the sort button at the top, select that; then, choose "Custom Sort."

A new dialogue box will pop up allowing you to customize your sort options. You want to sort by "Ggl" first, then by "WT" (or visa versa). You may have to "Add Level" to get the second sort option to be available.

Tip: Be sure to sort on "Values" and order "Largest to Smallest."

Negatives

Sorting by volume, you can get a pretty good idea as to what phrases are more popular than others as noted by either or both tools.

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.

Note: The lack of search volume on some of the Wortracker phrases here is likely due to me having deleted any phrase that registered less than 10 in Wordtracker search volume.

Eliminating Negative and Junk Words

Anytime you import keyword lists this size you're going to end up with a number of junk keyword phrases. This spreadsheet provides an easy way of getting them out of your way without having to delete them one by one.

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.

Negatives

In this case we'll pretend the client sells only automotive battery chargers, so the four words we typed in above can be globally eliminated.

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!

Note: The Negative Words tab is designed for global application. Do not add negatives that are core-term specific.

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".

Filter Negatives

This will cause all the negative words to be hidden. Notice below that rows 47, 61 and 77 are now hidden.

In most cases, using negative keywords is not enough. You will need to manually go through and eliminate keywords that aren't any good for optimization to your site. Simply place an "x" in the "No Good" column and your phases automatically highlight red.

Note: Do not manually add "x's" to the Negative column. If you run the Check Negatives macro again these will automatically be removed. Only manually mark negatives in the No Good column.

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!

Categorizing Into Shopping Funnels

I've written about the Research, Shop and Buy funnels for keywords before. You need to be familiar with that before using these categories.

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:

Assigning Keywords to URLs

Next you want to figure out which keywords should be optimized to any given page on your site. Let's start with the "Research" funnel. Use the filter options to hide all other keywords but those.

Looking at these keywords together can help you determine which URL(s) is/are best to optimize for the keyword group. Place the URLs in the blue area at the top of the page. Start with P1 (Page 1, see above). Now go through your funnel list and add "p1" to each keywords you plan to optimize for that page.

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.

As you can see, we placed "car" and "automotive" keywords on page 1, "motorcycle" keywords on page 2 and "boat" keywords on page 3.

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.

Now you can back and repeat this process for the Shop, Buy and Info keyword funnels.

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".

URLs