Skip to main content
keyword-research-vizualization

How to do mass-scale keyword research with data visualization tools

The concept of keyword research isn’t new, and lots has been written about it.

It seems that we need a guide to do keyword research every year (give me a break), and the guides do a great job of explaining conceptually how keyword research should be done, and how to do it on a small scale.

However, there are literally hundreds of thousands of keywords you could be going after – even if your website is small.  And in today’s competitive SEO environment, you need to spread a wide net to find that lucrative search arbitrage you’re looking for.

If you’re doing a thorough job of keyword research, you should end up a list of several thousand keywords.  This amount of data can be cumbersome, overwhelming, and tough to extract insights from.  That’s where data visualization comes into play.

Using a data visualization tool can help you whittle down the thousands of potential keywords you have, and help you discover the gems that you can win and get traffic from.

This post includes a few of my favorite visualizations that I used to help a real company grow it’s traffic in a competitive industry – consumer loans.  I used Tableau for all the visualizations (you can find the interactive sheets at the bottom on desktop.

Before you start, you’ll need

A giant list of keywords 

There are dozens of ways to get this – suggested search, webmaster tools, Keywordtool.io, Moz, Ahrefs, SEMRush, customer interviews, reddit, Quora, forums.  The great thing about using a data viz tool is you can do this in mass quantities, not worrying about quality.  We’ll filter it down later.

A broader topic for each keyword 

Broader topics not only add structure and organization to your database of keywords; they are a valuable dimension on which to evaluate your content marketing performance.  Anyone who’s done content marketing for awhile knows that some pieces flop, and some pieces knock it out of the park.  Measuring performance by topic allows you to get a sense of what is really working that should double down on, and what is flopping that you should kill.

Search volume for each keyword  

Either get it directly from the Google Adwords tool or your favorite tool that hooks into their API.

A keyword difficulty score  

If you’re the 800 pound gorilla in you’re niche, you don’t have to worry about keyword difficulty.  For example, this post I wrote on Hubspot went straight to #4 for the competitive term ‘customer success plan’.

So unless you’re Hubspot, you’re going to face tough competition for shelf space on the search results.  So you’ll need some sort of keyword difficulty score to find the opportunities with the highest volume and lowest competition.  Ahrefs, SEMRush and Moz all provide some sort of keyword difficulty score.

And onto the visualizations

Viz #1 – The Treemap

The Treemap is a great ‘at a glance’ tool.  As you can see below, each topic is put into a rectangle, filled by rectangles representing each topic.  The size of the rectangles is proportional to the search volume.

keyword-research-competitive-treemap

At first glance here, you can see which categories dominate, and furthermore, which high volume keywords dominate each category.  Not super actionable, but definitely a great way to get a 10,000 foot view.  For example, you can see that some individual keywords like “credit cards”, “payday loans” and “credit scores” dwarf entire other categories.  You may just remove those from your analysis, or break them into smaller categories.

Again, this is more of an ‘at a glance’ tool that can get you the lay of the land.

Viz #2 – The Topic-level Paretto Chart

The Paretto principle is often used in business to mean 20% of the X drives 80% of the Y.  However, that’s often not the case in SEO.  A Paretto chart is the best way to visualize this.

Here the bars represent the volume of a topic, and the line represents the cumulative volume of the total.

keyword-research-pareto-chart

The simple conclusion, based on what I’m hovering over, is that the top 5 topics account for 68.5% of the search volume.  Now this isn’t all that useful on it’s own, so let’s bring the keyword difficulty into play here.

In the chart below, I’ve overlaid keyword difficulty onto the bars (red being most competitive, green being least competitive).

keyword-research-difficulty-score-decile-1

Here you can see that while the budgeting category has the 3rd most volume, a great deal of the search volume within the budgeting topic is low competition.

Since it’s tough to see some of the longer tail breakdowns, we’ll add a 100% stacked chart below.

keyword-research-competitive-score-pareto-100-stacked

Viz #3 – The Topic-level Paretto Chart

Now that we have a sense of which topics we might want to dive into, we can use the same Pareto chart to visualize keyword competitiveness within a topic.

Here I have topics on the left in list form, sorted by search volume with weight average difficulty for reference.  Click on one (or more), and the keywords within that topic will show on the right.

keyword-level-paretto

The only difference between this chart and the previous Paretto chart is that each bar represents a keyword within a selected topic, and the coloring is the score of that keyword.

You can see that while this topic is incredibly competitive, there’s one 1,000 volume keyword – ‘do credit inquiries go away’ – that’s significantly less competitive than the rest, as evidenced by the green color.

Viz #4 Scatter Plot / Difficulty- Matrix

Whenever you’re looking to optimize for two factors, a great way to visualize is a simple scatter plot.  As you can see below, we’ve plotted keyword difficulty on the Y axis and search volume on the X axis.  For reference, we’ve included median lines and shaded area for upper and lower quartiles (just for reference, they don’t mean all that much).

keyword-difficulty-matrix

Here, you’re looking for keywords towards the lower right of the chart – low difficulty and high search volume.  “How long does it take to build good credit” might be a blog post to go after.

One step further: Focusing on conversions

Now, not all keywords are created equally.

Meaning that search volume & competitiveness aside, some keywords are simply more valuable than others.  Traffic itself is great, but if your end goal is conversions, you should take that into account when doing your keyword research.

Here are two ways you can incorporate that into your dataset.

Your Google Adwords Performance Data, Exported:  Specifically you’ll want to create a report with “Search Keyword” as a dimension and “Conversion Rate” as the measure.  You can also include impressions to do a gut check on the Adwords tool’s search volume.

Then, you simply append the conversion rate to each keyword, and multiply it by search volume to get ‘conversion potential’.  Just swap out conversion potential for search volume in the visualizations.

A classification of search intent (optional):  Many SEOs will break keywords into binary categories of navigational, transactional, and informational. For some businesses, classifying each keyword into one of these categories can be useful.  Rate them on 1-5 scale, 1 being ‘purely informational’ and 5 being ‘ready to buy’, and use this in the visualizations.  One good way to visualize these is to throw them on the colors and size of a scatter plot.

The final visualization

It’s a bit tough to see here, so check it out on Tableau Public.  Note how it’s interactive.

Once I did the keyword research, this took me maybe 30 minutes to build.  Now I can use it for a long time before I run out of content ideas.  Additionally, I can exclude keywords once I’ve written about them so others on the team don’t cross paths.  It’s also quite simple to add keywords to the list.

 

 

Conclusion

Excel, Google Sheets or a $99 / month tool might be cool if you’re just starting out, a local business, or in a wide open niche.

But if you’re trying to compete with the big boys and scale up your content operation, using data visualization can help give you that leg up you need.