SEO Keyword Analysis: How to Use Data Visualization to Make it Quick and Easy

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.

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.

• Microsoft adCenter Add-in Beta for Excel (free)
• Google Adwords Keyword Tool (free)
• Google Adwords Traffic Estimator (free)
• SEO Chat Keyword Difficulty Check (free)
• Wordze 1-Day Trial ($7.95, optional)
• SEO for Firefox (free)
• SEObook rank checker (free)
• Tableau Desktop Trial (free)

I will be helping Joe, owner of imaginarycreaturesfanclub.com, 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?

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.

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.

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?

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.

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.

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.

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.

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

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.

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.