By Nicholas Chimonas
11 Feb 2015

Format & Filter Keyword Ranking Data for Human Readability - Tutorial Tuesday

Keyword Research

Tutorial Tuesday Banner

To: Linkaratiers

From: Nicholas Chimonas

Welcome to what I hope is an early spring edition of Tutorial Tuesday. In today’s tutorial you will learn how to filter out the noise from large sets of keyword ranking data, and identify meaningful takeaways to help inform your link building & SEO strategy.

Massive data sets that exist as raw Excel sheets are not easy to interpret meaning from, so you need to distill the data down to digestible sizes in order to understand what data is signficant. Today’s half of this process is focused on formatting your data set using a Chimonas approved method, both for the purposes of isolating the important data points, and for ease of use by humans, bosses, and YOU!

NSC ALA raw vs format

Robots to the left, humans to the right.

The point of this process is to identify keyword ranking opportunities and missed chances. Keep in mind that this is a microscopic viewpoint, and the macroscopic is just as important for other applications. This simple technique can help guide link building and keyword strategy.

This guide begins at the point where you already have large amounts of keyword ranking data, but you’re not sure where to start or how to disseminate meaning behind the numbers.

If you do not already have keyword data, check out Jesse Stoler’s Tutorial Tuesday on using Ubersuggest to discover relevant keywords, so you can plug and play with this tutorial. Then acquire ranking data with your favorite ranking tool. Serplab.co.uk is a good free one, but I’ll be using Authority Labs data for this walk through.

1. Download keyword ranking data as CSV or Excel from your favorite ranking tool.

authoritylabs

http://authoritylabs.com

2. Clean your column header row. Here's how:


2a. If extra data is above your column headers, move it to a new sheet tab.

NSC ALA header formatting

2b. Delete any empty rows and columns, and freeze the top row.

NSC ALA freeze top row

  • Once you've deleted the empty rows above your column headers so that they are located in row 1, click on the "1" of row 1 to highlight it.
  • Click the "View" tab at the top of Excel.
  • Click "Freeze Panes".
  • Click Freeze Top Row.
  • Scroll down and observe the effect of the Excel spell you just invoked! Cool.

2c. Give the header an easy to read color format. Delete or hide unnecessary columns.

NSC ALA check cell header format highlights

  • Click the "Home" tab at the top of Excel.
  • Click the "Check Cell" box in the "Styles" formatting section of the "Home" top toolbar.

NSC ALA select all expand

 

  • Click the triangle "select all" button adjacent to row 1 and column A.
  • Double click the column divider line between column A and column B to expand all columns.
  • Right click any unnecessary columns and select "Hide".
  • For the Authority Labs data that I'm working with, I keep keyword data, Google rankings, Bing rankings, search volume, and result type.
  • A nice touch is to "center" all data columns besides keywords and URLs.

NSC ALA data points centered

Follow the red boxes to find your center.

3. Apply a data filter. Organize and Filter your data! [Steps below]

NSC ALA data filter

3a. First, sort search volume in descending order.

NSC ALA sort volume

3b. Second, filter out"0", "No Data", and "(blank)" by un-checking their corresponding boxes, so that we only see rankings with registered relative search volume.

NSC ALA filter out zero volume

3c. Next, sort rankings in ascending order.

NSC ALA sort rankings

4. Apply a heat map with conditional formatting.

NSC ALA apply conditional formatting highlights

  • Navigate to the "Home" tab.
  • Highlight your Google rankings column by clicking on the column letter (in my case, column B).
  • Click "Conditional Formatting" in the "Styles" section of the "Home" tab.
  • Hover your mouse over "Highlight Cell Rules".

4a. Define the conditional formatting rules.

  • In order to highlight keywords ranking on page 1, click "Less Than" (see above), enter "11", and select green as the cell fill color.

NSC ALA conditional formatting page 1

  • For page 2 rankings, click "Between...", enter "11" and "21", and select yellow as the cell fill color.
  • For page 3 rankings, click "Between...", enter "21" and "31", and select orange as the cell fill color. Pro tip: You'll have to click "custom format", then "color" to get to orange.

NSC ALA custom orange

  • For everything below page 3 rankings, select "Greater than", enter "30", and select red for the cell fill color.
  • [Optional] If you're tracking rankings from other search engines in a different column, copy and "special paste" just your formatting to the other ranking data columns.
    • Copy the entire column you just applied conditional formatting rules to,
    • Right click the column you want to apply the formatting to, and select "Formatting".

NSC ALA copy paste formatting

5. Enjoy the finished product!

Don't forget to save the spreadsheet as .xlsx (Excel Workbook), DO NOT save as .csv. Otherwise, your formatting will not be saved.

NSC ALA finished product

Ah... Now that's easy on the eyes. I don't always look at spreadsheets, but when I do, I drink Tecate. Er, I mean, Dos Equis.

You may remember that at one point we sorted our "Volume" column in descending order. Scroll down the sheet to all of the keywords that do not have registered rankings, and you'll notice that your biggest targets are all sorted by descending volume. This is useful data to help prioritize bigger initiatives.

NSC ALA prioritize unranked volume

Now that we have isolated the most important data under a microscopic and aesthetically pleasing view, there are many ways to digest this information to guide your link building and keyword strategy. From here, we want to identify which keywords would be worth focusing on to improve their corresponding page’s ranking. Primarily, your pages that are ranking on search engine result pages 1, 2, and 3 will present the best opportunities for quickly capturing additional traffic.

Tune in next week for another edition of Tutorial Tuesday, where I will guide you through my method of assessing the meaning behind the data to capitalize on new traffic opportunities, and develop strategic takeaways. Happy Excelling!

Nicholas Chimonas

If you want more of my brain knowledge, you ought to connect with me on twitter - @NChimonas.