Press "Enter" to skip to content

How to Build a Directional Long Tail Keyword List

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
Google Adword 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:
Split Keyphrases to Columns

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.
    Sub-total All Keywords
  4. Collapse the list so that only the Totals cells are showing (done by clicking the ‘2’ in the grouping column)
    Collapse Sum Groups
  5. Use the Excel go to command to select all the visible cells
    Keystrokes: F5 -> Click ‘Special’ button -> Click ‘Visible cells only’ radio button
    Select Visible Cells
  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:
Final List of Long Tail Keywords

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



  1. Top niche Keyword July 26, 2011

    It think, it is the best longtail keyword research directional post ever. Well explained and to the point.

    At least, I have understood,how to research for long tail keywords.

  2. nathan August 29, 2011

    I got stuck with “analytics data” is that the excel add in ?

  3. Glaf Cole September 17, 2012

    Neat breakdown, definitely will use this! Got to grips with the technicality in detail that is unrivalled on broad and deep searches on the matter.

Comments are closed.

Copyright© 2010-2024 Search News Central (SNC) | No material on this site may be used or repurposed in any fashion without prior written permission.

Search News Central uses Accessibility Checker to monitor our website's accessibility.