How To Create Awesome SEO Data Visualization with Tableau

How To Create Awesome SEO Data Visualization with Tableau

Posted by on Mon, Oct 21, 2013
Filed Under | Analytics, Search Engine Optimization

Let’s face it, we’re visual creatures. We’d much rather read and interpret visual data than have to dive into a numbers-packed spreadsheet.

This goes double for time-crunched executives.

How many times have you presented a data table like this to management, and not received the response you’re looking for?

SEO Rankings Moz

Data Tables like this can be rich in insight but are often dull and difficult to understand. They require interpretation and patient explanation.

Bottom line: Presentation matters.

What you need is truly awesome data visualization that’s easy to understand, great to look at and rich with actionable insight.

SEO Data Visualization with Tableau

Here at Blast Analytics & Marketing, we use a great data visualization software tool called Tableau.

In this post, I’ll share some of the reports I’ve created to effectively communicate data showing a site’s search engine visibility vs. their competitors.

Note: Be aware that you can download these Tableau visualizations to learn and use them yourself. There is a download link on the bottom of each visualization.

Calculate Relative Search Engine Visibility

To get there, we have to do a little math. Don’t worry, it’ll be quick and painless.

We’ll use a calculated metric called organic search visibility. This metric is based on a given domain’s position in the Search Engine Results Page (SERP) for every keyword for every search engine at a given time. It’s a way to normalize the data and put it on a scale of 1-100%. This way we can compare one domain against others (competitors).

To each keyword we attribute a set amount of points according to the following formula: Points = (31-n) where n is the page’s ranking in the SERPs for a particular keyword.

Let’s take a hypothetical example where a site ranks #1 for the keyword “KW1″ and #2 for “KW2″ on Google.

  • Step 1
    Calculate each your domain’s points for each keyword. For “KW1″ you get 31 – 1 = 30. For “KW2″ you get 31 – 2 = 29.
  • Step 2
    Calculate the total number of points for the domain: 30 + 29 = 59
  • Step 3
    Calculate the total number of possible points: 30 + 30 = 60
  • Step 4
    Take a percentage of Step 2 over Step 3: 59 / 60 = 98%
  • Step 5
    Now, do the same thing for the domain’s competitors.

Now that we have this data, let’s review four data visualizations designed to tell better stories about organic search performance.

#1 SEO Visualization: Organic Search Visibility over time

This chart shows keyword visibility over time and compares how “Example” (in blue) is doing vis a vis its competitors (Amazon, Mars and Mercury).

100% is good, 0% is bad.

You can visually see how Example’s KW1 keyword visibility is trending over time and how the competitors were doing at the same time.  In this example, you can deliver some very quick insights:

  • Overall our client’s site is dominating the SERPs
  • In early February there was probably an algorithm change that caused all rankings to drop
  • Amazon is gaining momentum, find out why and act!

The beautiful thing about this report is that you can filter by Keyword, creating an on-the fly SEO visualization for a group of keywords or even a specific keyword

#2 SEO Visualization: Ranking Distribution

Ranking distribution reports like this show how many keywords you’re ranking for and where they fall in the rankings. The visual “buckets” of ranking positions in this report give you a great idea of where you need to put your efforts. You can even filter by keyword and browse rankings distribution over time.

#3 SEO Visualization: Ranking Distribution Over Time

This is one of my favorite SEO visualizations because you can see how your keyword rankings are faring over time. Are you increasing the number of keywords ranked highly? If so, you should start seeing more green as you go from left to right.

Your goal is to put out the fire (minimize the orange and red). In fact, that’s how we refer to it here at Blast. Is the client ‘putting out the fire” over time?

#4 SEO Visualization: Find Outliers

For this visualization we combined rankings with Keyword Planner data: CPC, competition and average monthly searches. With Tableau these data sources can be combined and reveal valuable insights:

  • Keyword ‘Alana Corr’ has a considerable number of avg. monthly searches but it is on the third results page.
  • Our competitor Amazon is ranking well for ‘Alana Corr’ and ‘Duru Atar’.
  • On the keyword ‘Duru Atar’ dropped from top 30 over the last three weeks. Since this keyword has significant search volume, we should investigate further and work specifically on this keyword.

Start Telling Visual Stories

Tableau is a great tool to help you build SEO data visualizations and better communicate at an executive or operational level. Reports like these can help business decision makers see and understand data quickly and easily. If you haven’t already started telling visual stories with your data, we recommend that you add data visualizations to your reporting repertoire.

If you need help with data visualizations, let us know since we have the experience to get you over the Tableau learning curve and quickly delivering visual insights that will make a real difference in your marketing.

If you are already using data visualizations, please share your experience and examples.

Either way, join us for our first Tableau User Group meeting at Blast next month (November 5, 2013. Stay tuned for more tips on how to use Tableau and create data visualizations for actionable marketing insights.

Enjoy this post?

Join the discussion below, subscribe to our RSS feed or share it on the web.

This post was written by:

has written 2 posts on the Web Analytics Blog.

Joao is an Analytics Strategist at Blast Analytics & Marketing. His almost fifteen years of experience in the web industry provide a cornerstone to answer business questions through data analysis. Creativity, divergent thinking and continued will to learn are his trademarks.

Add Joao to your circles on Google+

Tags: , , , ,
  • Amalie Barras

    Hi Joao, thanks for sharing this post. I would like to build something similar in Tableau for the site I work on. May I ask what tool you’re using to collect this search rank data?

  • João Correia

    Hi Amalie, this ranking data is from MOZ but you can use any provider as long as you have historical data for each keyword.

  • Jesse Kanclerz

    For relative search engine visibility wouldn’t a better approach be to weight the keyword rank points by volume? Otherwise you could have a lot of #1 ranked keywords with low volumes artificially inflating the scoring trend.

Goal Driven Online Marketing & Analytics
Copyright © 1999-2013 Blast Analytics & Marketing