By Niels Hoven 2009/02/09

Do-it-yourself keyword strategy for under $10.

The foundation of a good search engine optimization (SEO) strategy is a well thought-out keyword strategy. Unlike the instant gratification of pay-per-click advertising, organic search efforts often take six months or more to show results. During those six months, your business will be creating mountains of content and building targeted links, all focused on 5-10 specific keyword phrases. If you've targeted the wrong phrase – perhaps it was too competitive, or there wasn't enough traffic, or the traffic didn't convert – the wrong keyword strategy will have cost your business thousands of hours in wasted effort.

A thorough analysis can easily take several days and require hundreds of dollars worth of analysis tools. Hiring a specialist who knows how to use them costs even more. Here's how to use data visualization to get it done in a few hours and for just a couple bucks.

Get the Tools

A wide range of factors come into play when evaluating keywords' potential for SEO. Of these, the most important are search volume, conversion rates, competitiveness, and relevancy to your site. With endless rows and columns of keyword data, the intuition required to balance these factors is a blend of art and skill that can only be developed with time.

However, data visualization allows us to do the same thing cheaply. The human brain can process pictures much more effectively than tables of numbers. By incorporating different parameters into different dimensions of our visualization, we can quickly scan a huge list of keywords and pick out the ones worth pursuing. In this guide, we'll use a number of free tools to collect the necessary data, build visualizations, deliver actionable insights, and set benchmarks.

Get the Lay of the Land

I will be helping Joe, owner of, develop a keyword strategy for organic search. (An imaginary website about imaginary creatures – how appropriate!) At this point, I know nothing about the competitive space.

I begin by getting a sense of what broad keyword categories are worth competing in. I just want a high level overview for now – we'll do a deep dive a bit later.

I ask Joe to give me a list of the main websites in his space and add them to an Excel spreadsheet. I do a few searches of my own and add the top results to the spreadsheet as well. The Keyword Extractor from Microsoft's Ad Intelligence Add-In now saves me hours of effort. It scrapes the (potentially hundreds of) webpages in my spreadsheet and tells me what words they're optimized for. Why go through all the bother of figuring out the best keyword categories when my competitors have already done it for me?

keyword extraction screenshot

The Keyword Extractor tool gives me hundreds of keyword ideas, some of them relevant, some of them not. But looking through the list, three main categories jump out at me: "bigfoot", "dragons", and "unicorns". I divide my keyword ideas into these three categories and throw out the ones that don't fit.

Keyword Data Points #1 and #2: Search Volume and Value

Now it's on to Google Adwords! I take my bigfoot keywords and feed them into the Adwords Keyword Tool, returning a huge list of keywords, all related to bigfoot. I do the same for dragons and unicorns and then feed the whole list into the Google Adwords Traffic Estimator. Traffic Estimator tells me the estimated cost per day for each keyword, which I use as a rough approximation for its value. Because advertisers pay more for keywords that convert well, this allows me to recognize keywords that may be particularly valuable, even despite lower search volumes.

keyword traffic estimator screenshot

Keyword Data Point #3: Competitiveness

Next, it's off to Wordze for a $7.95 one-day trial, where I can feed the entire list of keywords into Wordze and get a Keyword Effectiveness Index (KEI) for each phrase. You're welcome to use your favorite tool here – I'm just looking for a rough measure of each keyword's competitiveness and performance. I picked Wordze because it's cheap and because it lets me cut and paste my keywords in bulk.

KEI attempts to weigh the competitiveness of a keyword against its traffic potential, so it aggregates a lot of the numbers we've already collected. For many people, that's a good thing – choosing the right keyword strategy requires balancing so many factors that an arbitrary weighting and aggregation of the factors is sometimes the only way to reach a decision.

However, we'll be using Tableau Desktop, a data visualization package that will allow us to easily incorporate several dimensions into our analysis. So for us aggregation is bad – we would prefer transparency so that we could use the underlying data and do the analysis ourselves. But that's OK, we're just using KEI as a quick first-round screen to make sure we don't miss any high-potential keywords.

My first goal is to pare down this list of several hundred keywords. Here's a screenshot of my data in Excel. I want keyword phrases that have a balance of high traffic, high value, low competition, and high relevance to Joe's website. How am I supposed to find the keywords with the highest potential in all this data?

keywords excel screenshot

Narrow the List of Potential Keywords

Fortunately, this is where Tableau excels. In five or six clicks, I built this visualization of all the terms in my spreadsheet. I've separated the keywords into their umbrella categories (bigfoot, dragons, unicorns) and within each category, ordered the keywords by Estimated Cost / Day. The length of the bar corresponds to the Avg Monthly Search Volume (I'm using a logarithmic scale here, or else the highest trafficked words simply overwhelm the visualization), while the color is determined by the phrase's KEI.

keyword analysis data visualization

Now I don't have to strain my eyes struggling to make sense of hundreds of rows and columns of data. It only takes me a minute or two to scroll through my visualization and pick out the most valuable keywords (higher in the list) that have either high traffic (long bars) or good KEI's (green color).

Additionally, look at all the green for the bigfoot category vs. the unicorn category (see full keyword visualization). I've already got a preliminary indication that I probably want to focus my efforts on bigfoot instead of unicorns. That's an actionable insight that I never would have noticed with a table of numbers.

Make the Final Keyword Selections

I was using KEI as a rough measure of keyword competitiveness, mainly because there's a cheap tool to allow me to compute KEI for phrases in bulk. Now that I've narrowed my list of potential keywords, I can use a tool like SEOchat's Keyword Difficulty Check to evaluate the competitiveness of each term by hand.

I copy my last visualization and replace the KEI measure with my new Difficulty measure. I tell Tableau to ignore the keywords for which I didn't collect a difficulty score, and almost instantly I have a new visualization.

keyword selection data visualization

Just looking at the bigfoot terms for now, it's clear that "bigfoot sightings" is the dominant phrase in search volume. That dark red color also tells me the term is extremely competitive – Joe won't be ranking for it anytime soon. For now, let's hide the phrase "bigfoot sightings". Tableau rescales my graph and new insights jump out.

keyword selection data visualization

"Bigfoot sightings" was too competitive for my liking, but there are some variants that look very promising. "Sighting of bigfoot", "sightings of bigfoot", "first bigfoot sighting", "new bigfoot sighting", "Oklahoma bigfoot sighting", and "recent bigfoot sighting" are all very nice shades of green. These phrases are closely related, so it will be easy to create plenty of quality content to target all of these phrases. And the best part is that all that effort will be building up relevant authority for Joe's eventual run on the big-money phrase, "bigfoot sighting".

Oh, and there's even a misspelling that looking interesting. "Bigfoot sitings" doesn't look competitive at all – one highly optimized page might net me a few hundred extra visitors a month.

"Bigfoot organization" and "bigfoot photo" also stand out as highly trafficked, very valuable, but less competitive phrases. I make a mental note to evaluate them more carefully later. When I get a chance, I use SEO for Firefox to examine the competitive landscape. The top few results on Google have fairly high pagerank and a lot of incoming links, but the results from #5-#10 are unimpressive.

Joe is an avid blogger, has a very active forum on his site, and hasn't done any SEO at all until now. I think he could be a contender in search – we'll keep both those phrases in mind.

And just like that, we have a keyword strategy for the "bigfoot" category. We've got a nice set of niche keywords to attack for some immediate traffic, two somewhat more competitive terms to shoot for in the medium term, and one dream phrase ("bigfoot sightings") that we'll be building SEO karma for with all our groundwork right now. I can repeat this process in just a few minutes for "dragons" and "unicorns", and we're almost good to go.

Set Metrics

There's one last thing, though – benchmarking. Where are we now vs. where do we want to be? SEObook's Rank Checker will give me my current rank for a long list of keywords. In seconds, I can export the data to Excel and add it to my current visualization.

For visualization, I prefer to look at 1/rank rather than rank itself. First of all, it means that longer bars are better, which corresponds with my natural intuition. Second, it magnifies the difference between, say, a #3 and a #4 ranking term vs. a #103 and a #104 term – which is as it should be.

Turning to the unicorn category this time, it's clear that Joe's current SEO strategy (or lack thereof) isn't working for him. He's ranking really well for terms with no traffic at all and is barely on the board for higher volume terms. This visualization will be a great benchmark to refer to in six months when Joe wants to know whether our work has paid off.

keyword rank data visualization

It's up to you to decide how much time and money you want to invest into developing your keyword strategy. If your budget permits, by all means hire a specialist. But data visualization allows even non-technical users to evaluate keywords across a range of different factors. By representing different factors on multiple dimensions of our visualization, we can scan a huge list of keywords and quickly pick out the most promising.


Great blog post Neils!

It is easy to see how Tableau Software makes the "data pop" out at you so you know where to spend your time and energy. In today's economy everyone must be hyper efficient.

Search engine optimization is often hard for people to visualize and understand the value and work flows needed to create a measurable campaign.

Now I wonder about pulling other data from SEOMoz's Linkscape ( and other data say from social networks, twitter, etc. to create a single corporate dashboard to really see the entire new media marketing landscape with clarity.

Thanks for the deep info blog post, I will be sure to forward this to a few friends. Best, Tim Reha

Thanks for the compliment, Tim. And great suggestions. The problem, as always, seems to be with data accessibility. Any good ideas for how to get the Linkscape or social media data into an spreadsheet for better manipulation?

Great post.

Do you have information on how the KEI is computed. I'd like to recreate the algorithm myself.

If not, any suggestion on how to great your own?


Hi Jerome - Wordtracker's KEI computation is (was?) computed by squaring a keyword's popularity and dividing that number by the keyword's competitiveness.

The problem is, how do you compute a word's competitiveness? There are so many different factors that determine how hard it will be to beat your competitors - age, inbound links, pagerank, authority, number of search results...

Essentially, the only way to know exactly how competitive a keyword is would be to know the search engine's algorithm, and they're not talking!

So instead of trying to aggregate all these factors into one arbitrary KEI number, we use data visualization to examine multiple factors simultaneously, giving us a much richer view of the competitive landscape.

Which factors Niels would you explore in parallel to extract valuable learning from the visualization if not KEI alone?

Please remember that linking is not citation. I love that you used my image, but it's under Creative Commons licensing and you need to attribute it to me.

Great article BTW.

John -

Apologies- removed.

What an amazing article and real-world usage of blending several data sources together to get greater value. We currently pull the keyword traffic from GA or Webtrends and blend that with the Google Traffic Estimator in our MashBoards. With a bubble chart, you can easily view top keywords by several cost vs competiton vs volume vs your bounce rate.

Feel free to check out our examples at

This is a great article and introduced me to some really awesome tools I was unaware of, however I am still lost as most of the reports generated by the free tools have evolved since the writing of this article.

Has anyone tried following this tutorial recently? Currently it is over 3 years old... Would love to understand how this all works with today's stuff. Everything seems similar, but Google doesn't show Search Volume and and Cost Per Day .... I only seem to be able to enter in my own upper limit per day, my CPC upper limit... then it will tell my "impressions" not exactly what we are looking for I think.

Wow! This post is really great and very informative. Thank you so much for sharing this info about seo keyword analysis. I am really glad I came here as I learned a lot of things from this site.


Just wanted to share a post on SEO data visualization I just published.

That is great way to analyze keywords and its ranking. I will try to use these all and share with my friends also. That is very valuable post i ever seen in my life.