Chasing down individual, unique long tail keyphrases will end up being a big waste of your time.

If your reaction to that sentence was, "OMG, how could he say that long tail keyphrases are a waste of time?! Grrr. What a f'in idiot! I can't wait to comment about how he is so totally wrong!" Then go back and re-read it carefully this time.

[I'll wait]

Now, better?

Long tail keyphrases are a critical growth area in any SEO strategy but spending your time on a finite list of long tail keyphrases that you have found is not the most efficient use of your time. I am going to show you how to build an actionable list of long tail keywords that once repeated across your core keyphrases will help identify long tail opportunities and holes in your current content strategy.

Build the List

What we are going to build out here is a list of long tail keywords not keyphrases. We want to pull out as many of the individual words that make up the many permutations of your core keyphrases.

The core keyphrase that I am going to use as an example is "bunk beds".

Step 1. Pull Top Keyphrase Ideas from Google Keyword Tool

1. Enter the core keyphrase that you are looking to build a list for into the tool
2. Select the "Only show ideas closely related to my search terms"
3. Switch from broad to exact match (this may just be a habit of mine)
4. Download All 100 suggested keyphrases

What this setup looks like in the Google Keyword Tool

Step 2. Clean-up and Split Keyphrases into Individual Words

1. Remove the extraneous data so all you are left with is your Keywords and Global Monthly Searches
   Note: Global Monthly Search data will be used as a directional number when applying strategy to the list
2. Perform 'Find & Replace' to remove the brackets '[' & ']' and every occurrence of 'bunk' and 'beds'.
   Tip: I've found it useful to do my replaces in this order 'bunk ' (with a space) then 'bunk' (without a space)
3. Then use Excel's built-in Text to Columns tool to split out your phrases into individual columns. Tip: Be sure to use the Space delimiter to split by
4. After the split insert the Global Monthly Searches data next to each of the newly created columns
   Don't worry about blank cell pairings that will be cleaned up in the next step

The result of all this should look something like this:

Step 3. Sort & Group Individual Keywords

1. Copy each of the column pairs [keyword-Global Month Searches] into a new sheet creating one long list
2. Use Excel's sort function to sort the list by Keyword from A-Z.
   (You can scroll down to the bottom of the list and remove all the blanks that have an associated Global Monthly Searches at this point to clean up the list further)
3. Use Excel's sub-total function (on the data tab) to group and subtotal all the Global Monthly Searches by Keyword.
   
4. Collapse the list so that only the Totals cells are showing (done by clicking the '2' in the grouping column)
   
5. Use the Excel go to command to select all the visible cells
   Keystrokes: F5 -> Click 'Special' button -> Click 'Visible cells only' radio button
   
6. Copy the selected cells and special paste their values into a new sheet

Step 4. Final Clean-up of Researched Keywords

1. Remove all occurrences of ' Total' on this sheet (with the leading space)
2. Human edit the keywords
   
   • Remove words like 'and', 'in', 'the'
   • Based on the project words like 'cheap' or 'expensive' that are not the intended market would be removed
   • Also, competitor terms like 'ikea' or other furniture companies might be removed
   • Some combining may occur as well like combining plurals or misspellings

Step 5. Add Analytics Data (for Advanced Direction)

The directional value, Global Monthly Searches, doesn't always paint the clearest picture for where you should be spending your time. I like to add analytics data to this sheet as well so I can clearly see which individual words that a site already has penetration for and which words the site may completely lack.

The process of adding your analytics data is very similar to how we created the researched keywords list.

1. Pull all traffic driving keywords to the site containing "bunk beds"
2. Remove 'bunk' and 'bed' from the list
3. Split out the phrases into individual word-traffic column pairings
4. Create your master list of words and do two sub-totals on the list
   First, do the sub-totals with 'Sum' selected as your function (copy visible cells to new sheet)
   Second, do the sub-totals with 'Count' selected as your function (copy visible cells to new sheet)
5. Create three columns - Word, Usage & Traffic - and then match this data back to the researched keywords based on the Word column.

The final result of adding Analytics data should look like:

What You Get Out of This Process

The final list that you have now created shows the true colors of the direction that you should be heading with your long tail content strategy. Not only have you found the individual words that are important to search engine users but you have outlined places where you are already have traction and others that you do not.

The Final Recommendations (based on the example)

If I was running this for a client, I would take these results back to them with the final recommendations that they should build out content for 'adult', 'black'(and other color related words), 'cool' and 'custom' bunk beds (granted they have the product to support these keyphrases).

More articles by this author

Monitoring and Fixing 404 Errors

The Search Engines Do Not Like Swiss Cheese 404s happen and...

Read More >>

Can a Consumer Advocacy Help SEOs reputation?

Ever since the SEO industry grew beyond the in-house and...

Read More >>