Data Hacks: 4 Rapid Visualization Tricks for Keyword Rankings

By Ninja Dan

I love spreadsheets and I’m always looking for new ways to approach the data – sometimes you analyze spreadsheets the same old way and it’s hard to figure out what they’re telling you. The data just isn’t as forthcoming.

That’s when a new way to manipulate them might be the trick needed to pull out those juicy, actionable insights. A few of my favorite data hacks come in handy when I’m comparing two different keyword reports for a site: the most recent one and an earlier one, usually a benchmark report.

Nothing gives you a better understanding of the dynamics and ecology of a client’s keyword space than getting granular and analyzing keyword rankings line by line, but it’s a true luxury. If a client has a few dozen to a few hundred keywords, you might get to pour over every nook and cranny of the reports.

But what do you do when you’re staring down the business end of a 5,000 to 10,000-line spreadsheet (or bigger!) and comparing it to an earlier one? The data is too overwhelming – there’s simply no way you can approach it the same way.

The way I approach these types of situations is to go in the opposite direction: don’t think granular, think Big Picture. Or rather, visualize the Big Picture. Here’s the process I use to quickly visualize what the data is telling you in ways not immediately clear on the surface.

Identify Gains and Losses

When comparing keyword rankings, I’m always thinking in terms of Then and Now. Whether I’m looking at keywords that rank in the Top 10, 20 or even 100 spots on the SERPs, the first thing I want to know is how many did the client gain or lose from Then to Now. That’s easy enough – I just subtract the number of lines in Then from Now and the remainder is my answer.

If it’s a positive number, the client’s had keyword gains. If it’s negative, they’ve had a loss. I take the number of gains or losses and determine what percentage it is of Then. That’s the growth rate or shrink rate of the keyword space. I create a line graph to visualize the growth or shrink rate from Then to Now. The steeper the line, the more keyword growth or shrink the site has seen.

The overall growth or shrink rate is useful to know, but it doesn’t tell me much beyond that. To dig deeper, I want to know which keywords the client’s gained or lost. I need to start using some tricks. Here’s what I do:

1. I sort the keywords in both spreadsheets by alphabetical order.
2. I then isolate the keywords they have in common – duplicates. I don’t want to delete them, so I won’t use the Dedupe function found in Excel and other software. Instead, I copy the keywords from both spreadsheets into a text manipulation tool like the Duplicate Line Remover at TextMechanic.com (which lets you isolate duplicates) or you can just dump them into a third spreadsheet and use an advanced filter. However it’s done, I want to isolate the duplicate lines, not eliminate them. These are the movers – keywords that were in both reports and moved either up or down in the rankings.
3. I go back and find the movers in the Then and Now spreadsheets by quickly moving down my alphabetized lists.
4. I use a fill to shade the movers a distinct color and then sort them by fill. Even in a spreadsheet with thousands of lines, the alphabetical lists will help you move through the lines as quickly as possible. All the un-shaded lines in the Then spreadsheet are the losses. All the un-shaded lines in the Now spreadsheet are the gains.
5. I cut and paste them into a new spreadsheet and put them aside for later. You could spend hours just poring over the gains and losses alone.
6. I then delete the losses and gains entirely from the spreadsheets.
7. I save a different copy of the spreadsheets with a different name so I can always come back to the raw data if I like.

Identify Bumps and Slides

Now I’m left with just the movers in both spreadsheets, sorted alphabetically. The order of the keywords should be the same, but the Then and Now rankings should all be different. Now we start playing with the data:

1. I add a new column to the Now spreadsheet right next to the Now rankings, label it “Then”, and then copy the entire Then rankings into that column.
2. I add another column labeled “Change” next to the Then column.
3. I place a relative formula in the Change column to subtract Now from Then, and I then use the Autofill function to quickly apply the formula down the rest of the Change column.
4. I sort the spreadsheet by Change and look at the results. All the positive numbers are ranking bumps and all the negative numbers are ranking slides. The bigger the Change, the bigger the bump or slide.
5. I compare how many bumps vs. slides there are and make a pie chart to visualize the ratio.
6. I also give bumps and slides their own different color fills. I shade bumps in green and slides in red. If Change = 0 for a keyword, it stayed static and I don’t add any fill.
7. Finally, I keep the bumps and slides sorted by fill but add a secondary sort criteria – search volume. Each site is different, but I determine an appropriate cutoff spot for high and low search volumes for that site.
8. I go back to the bumps and slides and add one more layer of color fills. High volume bumps I leave green but shade low volume ones with a yellow fill (cautiously optimistic). High volume slides I leave red (alert!) but shade low volume ones with an orange fill (monitor situation).
9. I resort the entire spreadsheet of movers by overall search volume from high to low. It now appears as bands of colors – green, yellow, orange and red.

Take a Step Back

Finally, I literally take a step back and just look at the color distributions, not the individual keywords. Based on what I see, I can quickly draw a few conclusions and determine where I want to look deeper:

• If the top of the spreadsheet is evenly mixed red and green like a Christmas tree, the high volume phrases are showing specific fluctuations but no overall trends emerged.
•  If the top is mostly green, the client’s seen more high volume bumps than slides. If it’s mostly red, the opposite is true.
• If the bottom of the spreadsheet is evenly mixed orange and yellow, the low volume phrases are showing specific fluctuations but no overall trend’s emerged.
• If the bottom is mostly yellow, the client’s seen more low volumes bumps than slides. If it’s mostly orange, the opposite is true.

Ideally, a best-case spreadsheet would be mostly greens on top of mostly yellows. A worst case spreadsheet would be mostly reds on top of mostly oranges. One with mostly greens on top of mostly oranges means high volume bumps with low volume slides. One with mostly reds on top of mostly yellows means high volume slides with low volume bumps.

These visualization tricks let you quickly get a sense of what’s the Big Picture in the client keyword space from Then to Now. The larger the spreadsheets, the more useful this becomes, because it lets you decide where you want to dig deeper and investigate more. Time is money and these tricks allow you to be efficient with your time.

This can save you hours of time and headaches rather than moving through keyword rankings line by line – time and energy better invested somewhere else. Try out these visualization techniques the next time you’re comparing two different keyword reports and you’ll see the data in a whole new light.