Explode Your Keyword List Right – Part 2

• Pin It

Whatever your source for data, the healthier the amount the better. In our first post we looked to Google Webmaster Tools for our source to build from. Today we’ll use Google Analytics. Our goal will be to quickly identify long tail clusters of value.

Referring Keywords

If you are doing SEO right, your link building, content (both on-site and off), social media and other inbound marketing techniques will all have peripheral keyword advantage. This means that more than just those keywords you target will drive significant traffic. This is done through a comprehensive, intelligent and creative content strategy (rather than just a link-building strategy).

If that be the case, you can take advantage of this benefit and build upon it. The first step is to export your referring keyword data from Google Analytics. Depending on how much traffic the website of interest generates, your time frame should vary. Typically, I like to work with between three and six months:

Before exporting your data, be sure you are looking at both organic and paid keywords. There isn’t any point to only looking at one or the other as we are simply looking for overall keyword opportunities. With that done go ahead and export your data.

The next step is to gather your additional needed metrics to provide the ability to make comprehensive decisions. For this process those metrics should include:

1. Current Rankings / URLs:

As was mentioned in the first post, there are a number of great tools available for grabbing a high number of rankings. Once you have access to these tools the process of gathering rankings is fairly simple. You simply set up a campaign by dumping all of your keywords in. Allow for a day or so to gather the rankings, then run your export into an Excel file.

2. Phrase Length:

There are a lot of different ways to determine the difficulty of a keyword. Two common tools are the Keyword Difficulty Tool by SEOmoz and the competition metric provided in the Google Adwords Keyword Tool. But for quick analysis on a large number of keywords one of the best ways to sort them by difficulty is to look at the length of the phrase. When working in Excel, drop this formula into a column to the right of your keyword list and you’ve got your difficulty metric:

=LEN(A2)-LEN(SUBSTITUTE(TRIM(A2),” “,”"))+1

In addition to the above metrics, your analytics export should contain the following:

• Visits
• Revenue (if eCommerce)
• Pages/Visit
• Average Time on Site
• Percentage of New Visits
• Bounce Rate

While we won’t incorporate every one of these metrics into our decision filtering, they are good to have available when considering any potential expansion.

With these in place your last step is to “Find & Replace” each of the non-ranking terms to show a value of “100″ rather than “0″ or “-”.

Identify Missed Opportunities

With all of your keywords and correlative data pulled into one, you’re ready to start manipulating. Remember, our goal for this process is to quickly create clusters of long tail phrases to target for quicker wins.

So let’s get into it.

First step is to sort your current data. Highlight all of your data and run a custom sort.  The two metrics you are including in the sort are the length of the phrase—looking at the longest first, and the current ranking—looking at the lowest first.

In this sort we identify the easiest terms (longest phrases in number of words) to target with the largest missed opportunities (ranking low in the index).

With the sort in place you will need to use your own judgment. For instance, it isn’t usually a common thing to target phrases more than 10 words long. However, pieces of that phrase may be of great value to you.

There will also be keywords that have brought in revenue that rank low and keywords that haven’t brought in any revenue. Typically a keyword ranking low yet still driving revenue should be valued above those that are not. This type of logic should be used when evaluating opportunities.

Typically I find myself looking in the middle of the phrase length spectrum and working from there.

Identify Relevant Clusters

Next, you will need to determine which of those top showing phrases are of priority to the website of interest.

Select one term at a time, then re-sort the list to show any other terms ranking for that same URL to identify potential clusters to work from in your optimization on-page and link building strategy.

The amount of what you are able to expand out and begin targeting should be determined by you and your team.

Choose your clusters based on relevance first. The ranking URL will be a great driving factor for that identification. But you can also look at the use of words within a phrase to identify any possible outliers not yet ranking at all.

From there you will want to look at each target page for on-page optimization needs then begin building out your external content and link-building strategy.

Previous Posts In The Series:

Post 1 – Building a Keyword List Based On Top Relevant Terms in Google Webmasters

Next Up In The Series:

Post 3 – Using Google Webmaster Tools To Locate New Terms & Build Strong Variations

Post 4 – An Enterprise Approach To Keyword Prioritization & Targeting