Explode Your Keyword List Right – Part 4

This last post of the series is one targeted toward SEO campaigns so large that maintaining, monitoring and collectively progressing them on a keyword level can feel like you’re trying to push a beached whale.

In any enterprise campaign, the achilles heel is almost always a lack of organization. With organization will come clarity in goals and strategy, as well as an ability to progressively build upon prior steps.

Keep in mind that while this particular process was built for campaigns targeting a large number of landing pages and keywords, everything big in marketing can always be scaled down. For ideas on a smaller scale keyword expansion and monitoring approach I would first start at Part One, Two or Three in this series.

Data Essential To The Process

Key Performance Indicators

To organize and prioritize a massive keyword list it is essential to know which KPI’s drive the online strategy. The top 2 performance indicators a company online should use to gauge success are either revenue or leads (depending on business model). Both of these will need to be retrieved from your analytics package.

Supportive Indicators

But, as we well know, there are a few other bits of data which incrementally assist in leading to these ultimate goals and allow us as marketers to understand we are on the right direction, or in some cases missing opportunities. On a keyword level these are potential search volume, difficulty, and current position (or rankings).

I call these supportive indicators because they not only provide us insight into what will be required to achieve our ultimate KPI goals but will also be heavily integrated into this particular keyword prioritization process.

Both your search volume and keyword difficulty will come from the Google Adwords Keyword Tool. The current position of each keyword you will need to derive from your choice of ranking analysis tools.

The Process

Primary Target Identification

Your first step in the process will be to single out which pages or specific categories within the site will be part of your overall SEO campaign. Keep in mind that this specific process is meant to expand a keyword list far beyond the obvious terms and ensure all potential traffic is eventually gained through a strategic approach.

This means that you could start with your already top 25-50 performing pages. So your first step would be to gather the top 50 or so location specific pages on your site. For our example we will go at this as though to be targeting a “car rental” niche.

*Note that this process works best as you work with pages that fall under the same niche.

Common Keyphrase Iteration Identification

The next step is to identify the common iterations that create big terms for each of these top pages. For instance, in the travel industry—and especially specific to car rentals—some of the biggest terms revolve around these following types:

  • the word “city” itself
  • the “name” of the city itself (if this doesn’t make sense just wait a moment)
  • “deals”
  • “cheap”
  • “discount”
  • “budget”
  • “low price”
  • or a combination of one or two of the above.
With this basic list you can build a baseline list of iterations which we will expand upon. Such as the following:

Baseline Keyword Iteration List To Expand Upon

Develop Baseline List for Each Page

Now we are ready to expand into a hearty keyword list for each target page. Going along with our example of the top cities for car rentals, we now need to single out each city individually with the iterations brainstormed above and expand out a strong list using the Google Adwords Keyword tool. This can be done by dropping the list above into a notepad and running one FIND & REPLACE key command replacing the term “name” with the actual city (or unique term relevant to a specific page):

Keyword Expansion Process Using Iterations

Aggregate Comprehensive Keyword List

With your terms now specific to the city (or otherwise unique element of the page your web property of focus is) it is now time to expand out a full list of terms from which we will work. This is done by placing each term (on the left) individually into the Google Adwords Keyword Tool and exporting 100 keywords per single term.

Aggregate Comprehensive List With Google Adwords Keyword Tool

With the full list of terms exported. Combine all of them together into one sheet and deduplicate the list. This will leave you with a unique list of terms.

Remove Industry Adjoining, Irrelevant Terms

One common occurrence when using the Adwords Keyword Tool is that adjoined industry terms, irrelevant to your overall purpose, will sneak their way into your keyword list. It is important to identify each of these types of terms and remove them from the list. In our example of “car rentals” a few common terms that arise which we remove are:

  • flight
  • air / airport / airline / airfare
  • ticket
  • fly
  • hotel

The best way to keep track of and remove these is to generate your own list on the side. Then each time you expand a list filter the aggregate to remove these irrelevant terms.

Filter & Remove Irrelevant Keywords

Duplicate Process Across All Priority Pages

At this point you have accomplished your list for one of your list of top target pages with which we will be prioritizing. Duplicate this process across each of those top niche pages you have selected for your web property. This means that for us, we moved from Denver to San Francisco to New York and on through the 50 cities special to our “car rental” expansion purposes.

After doing this, we were left with between 250-500 keywords per target page. However, this number will differ for you depending on the industry targeted.

As you are doing this it will be best to keep each pages keywords and the data you will gather for them each in a separate sheet of the same excel document similar to that shown below:

Keep Pages Keywords & Data Separate

Gather Essential Data

As we began this post, specific metrics were outlined that would be essential for this process. All your keywords for this project have been identified, so now it will be important to retrieve those metrics. As a reminder, they are:

  1. Current Position (Ranking)
  2. Search Volume Potential (Exact Match Monthly Search Volume from Adwords Keyword Tool)
  3. Keyword Difficulty (Competition Metric from Adwords Keyword Tool)
  4. Revenue (Best to get about 6-12 months worth of data from your Analytics package)

Set Benchmarks

With all data in place we are ready to start comparing and making decisions. The first step in this comparison process is to get your totals and averages. Run formulas adding up the sum [=SUM(Number 1, Number 2...)] or average [=AVERAGE(Number 1, Number 2...)] of each metric on a page by page basis.

Run Totals & Averages

*Similar to past posts in this series, keep in mind that in order to gather a quality average score for rankings you will need to the value showing that a term is not ranking (usually a “-” or “0″) with a 100.

Having all your data gather, totals and averages set you are now ready to calculate priorities. Rather than walk you through a step by step on this particular portion of the process it would be better to simply place the tools in your hands and let you use it at will:

Calculate Priorities

In order to determine priorities (both on a page to page comparison and a keyword to keyword comparison) the following formula will:

 

.4(Revenue/Max Revenue Top Page)+.3(Search Volume/Max Search Volume)+.2((101-Average Position)/100))+.1(Average Competiton/100%)

 

Let me explain:

  • Weighted Authority: To place a weighted authority on highest value metric, this is done by multiplying each part on a sliding scale of .4 down to .1. Therefore making revenue (this could be leads also) the highest priority, potential search volume the second, ranking performance the third, and so on.
  • Revenue: This is referring to each page’s individual revenue attribution (a sum total of every keywords revenue achieved within a particular page).
  • Max Revenue Top Page: This is referring to the page that has been identified to achieve the highest return on revenue in comparison to all other pages within the campaign.
  • Search Volume: This is referring to the total potential search volume of a particular page (The sum total of all the keywords within a page).
  • Max Search Volume: This is referring to the page that has been identified to have the highest potential search volume in comparison to all other pages within the campaign.
  • 101-Average Position/100: The term “average position” here is referring to the average ranking of all keywords within a particular page. Because a good average position is a lower number, and all other metrics here are based on “the higher the better,” this equation will take your average position and invert it to reflect this same frame of thought.
  • Average Competition/100%: The term “average competition” is referring to the average ranking of all keywords within a particular page. This is then divided by 100%.

The value achieved from this formula should be considered your “priority value.” Those pages with the highest value should be targeted more heavily first because you either have a gaping missed opportunity or it simply has the highest potential to drive more revenue.

Macrocosm & Microcosm

The beauty of this formula is that it can be calculated on a level to compare a series of web pages to each other and determine which should be targeted first (as shown above)—this is what I would call our “macrocosm”—and it can also be calculated within each page comparing keyword to keyword to determine which keywords are highest priority and should be targeted first. Comparing keyword to keyword within one page is your “microcosm.”

Is Your Brain Fried?

If your brain feels fried right now don’t worry. Mine is fried just trying to explain all of this in one blog post. As with anything else, you will likely begin to understand it more as you put it to practice.

Start by doing this process with 4-5 pages only. Get your metrics together and begin playing with the calculations. It won’t be long before you start scaling up.

Disclaimer: On-Site & Branding Signals

Above all else, when looking at targeting keywords remember that without the proper branding signals in place with quality on-page optimization, no website will sustain an quality ranking. The best way to look at search engine marketing is to:

First, be sure your on-page optimization is pristine. Each page should be optimized with purpose. If those purposes do not involve (1) usability, (2) conversion and (3) keyword targeted optimization, you are off the mark.

Second, it is essential to establish your business as an authoritative brand in the eyes of the search engine. Links pointing the site from partners websites, branded links using the business name and the URL itself as their anchor text as well as images linking to the website. Basically anything type of link that would be created in a purely natural process can be considered branded.

And lastly, begin targeting specific terms in your content and link building strategy. This last step is where the posts in this series will come into play.

Other Posts In The Series:

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

Post 2 – Using Google Analytics for Long Tail Identification & Quick Wins

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

Get Internet Marketing Insight For Your Company - SEO.com

5 Comments

  1. John Lincoln says

    Preston – I like your post. It is clear you take pride in your work and have put a lot of thought into this. Also, it is nice that you share your methodology and ideas. I will tell you that there are simpler ways of doing this and gaining way more location based, longtail traffic through dynamic KWA and page creation. But this method makes sense if you don’t have the engineering capabilities and need to prioritize things like content and individual page development. Anyways good ideas and a nice post. But might want to consider if this is the best way to spend your time, especially on an enterprise level client who has so many keywords to go after, an authoritative site and probably great engineers are their fingertips.
    Best – @johnelincoln

    • Preston Van Dyke says

      John – great points! I didn’t touch on it within the post but these types of processes are often built out conceptually like this – then developed into a less time consuming process using engineers. That way the process can be perpetuated without issue.

      However, I have come across quite of few SEO’s trying to do something similar without engineers at their disposal – which lead me to explaining the long format process here.

      Thanks again for the feedback!

  2. Julian Venter says

    Beached whale sounds just about right Preston. I think revenue plays a large part in a clients business. Like you’ve shown in your Aggregate Comprehensive Keyword List, Google places has also shown huge ROI for local search results. But in the end usability, conversion and keyword optimization is by far supreme. Julian

  3. Martin says

    Colibri Tool do the best job with keywords. I am using the premium account and I am very satisfied. I am able to compare and find my competitors either. Check it-http://colibritool.com/

Leave a Reply