How to create an SEO keyword rank tracking report with Supermetrics

KEYWORD RANK TRACKING REPORTS · 13-MINUTE READ · By Ryan Purkey on October 17 2017.

Recording historical, accurate, landing-page-targeted keyword rankings is critical to building up organic traffic to a site. For sites tracking keywords in hundreds, this guide will detail how you can measure and improve these rankings.

Data in these reports will come from Google Search Console, and will be organized to meet actionable goals. I used Supermetrics for Google Sheets to build queries, which fetch Search Console data.

Creating a rank history spreadsheet

Start with a new Google Spreadsheet and rename “Sheet1” to “RankHistory” Next, create a tab with a label in YYYY-WW format. This means the year (2017) and the numbered week of the year (ex. 40 – You can visit https://whatweekisit.com to see the week of the year alongside dates). This will provide an easy-to-sort format of tabs for organizing all your weekly historic data while staying under the 200 tab limitation of Google Spreadsheets.

In this new 2017-40 tab, go to the Add-on’s drop down menu, hover your mouse over Supermetrics Add-on, and click “Launch Sidebar.” from the drop down menu.

Going down the side bar entries, select Google Search Console (GSC) as the data source.

For sites select all versions of your website in GSC: https, http, www, non-www. This alone helps solve tracking migration issues if you’ve recently switched from HTTP to HTTPS or have made www / non-www the canonical version of your site.

Initially under “Select dates” go as far back in the 90 day window as you can, choosing “last year and this year to date.” Later this will change to a custom date range of Sunday to Saturday data, for example, 2017-08-27 to 2017-09-02, but on the first run you’re interested in pulling as many search queries as the Google Search Console data will allow.

Next in “Select metrics” add: Impressions, Clicks, Average position, and CTR (%). Finally in the “Split by” section add: Search query. For # of rows to fetch, test out a few different ranges in order to see what number covers the bulk of meaningful searches.

Other metrics in this section–like Landing page, Search type, Device, and Country–can be very useful in different types of reporting, but for the purposes of this rank tracking report we’ll only need the “Search query” metric.

The initial report is now ready. Click the “Get Data to Table” button and let Supermetrics populate the sheet.

After it does so copy the list of keywords back into the “RankHistory” tab under a “Search Queries” header. Next create columns to the right of those keywords with the labels “2017-40i”, “2017-40c”, “2017-40ap”, and “2017-40ctr”. Underneath these headings you’ll use VLOOKUP to pull in the data from the list of keywords. First though, go back into the 2017-40 sheet and adjust the dates to a custom date range that matches the 40th week of the year.

Now you’ll be ready to pull in the numbers using the formulas:

=VLOOKUP(A2,’2017-40′!$A$2:$F$12002,2,FALSE) for Impressions

=VLOOKUP(A2,’2017-40′!$A$2:$F$12002,3,FALSE) for Clicks

=VLOOKUP(A2,’2017-40′!$A$2:$F$12002,4,FALSE) for Average Position

=VLOOKUP(A2,’2017-40′!$A$2:$F$12002,5,FALSE) for Click Through Rate

Depending on the amount of data you’re collecting, adjust the end of the range ($F$####) to better fit your data. Copy these four formulas down each column to fill the entire week. Before getting the numbers alone, to help speed up the process for duplicating this step, select “View” and “Show formulas” and copy and paste the entire four column formula list to a text editor like Notepad, TextEdit, or TextWrangler. There you can do a Find and Replace to adjust the formulas for other weeks in the report: 2017-39, 2017-38, and so on.

Switching out of formula view and back into the numbers after the data populates, copy it and “Paste special” as “Paste values only” so that you’re avoiding limits on the number of formulas Google allows GSheets to have (40,000 cells). You should now have a Spreadsheet with two tabs: RankHistory, 2017-40, and a Text Editor with the list of formulas for duplicating in the next week of columns.

Now you’ll just go through the remaining weeks, duplicating the 2017-40 week back into time until you reach as far back as Google Search Console data will allow (the previous 90 days). Moving forward you’ll update the spreadsheet with each week as it passes as a Monday morning or Sunday evening task. For ease of view, freeze the first column and top row of the RankHistory tab so that data is always labeled when scrolling.

Creating a rank tracking spreadsheet

Start with a blank spreadsheet and create three sheets named “RankTracking” “90DayData” and “LastWeek”

Using Supermetrics as above, import data from Google Search Console to the “90DayData” sheet using the date setting of, “Last year, and this year to date” the metrics of “Clicks” “Impressions” “Average Position” and “CTR (%)” and Split by, “Search query” and “Landing page”. Again, limit the amount of data pulled to meaningful numbers. Also be cautious of getting too large of data sets as Google Spreadsheet is limited to 40,000 cells of formulas that will be used for things like VLOOKUP and comparing metrics.

On the “LastWeek” tab, import all the same metrics and splits but set the date to “Last week (Sun-Sat)”

Now go into the “RankTracking” Tab, Copy and paste the data from the “90DayData” tab into Column A, B, C, D and E of “RankTracking”. Rename the C, D, E, and F Header to: 90d Clicks, 90d Impressions, 90d Average Position, 90d CTR (%). Label G1, “1-2 Rank w CTR 30%” Label H1 “Possible Gains”. In G2 create the formula, “=IF(F2>30,F2*0.01*D2,D2*0.3)” and in H2 the formula, “=SUM(G2-C2)”

Important Note: Your site’s CTR might vary from ~30% for 1-2 average position rankings, so be sure to double check this average to ensure that percentage is roughly accurate. You can do so by averaging all ranks between 1-2 in a separate sheet.

For Columns I and J, label I as, “Last Week Average Position” and J as, “Change”. Using the formula of, “=VLOOKUP(A2,LastWeek!$A$2:$F$10001,5, FALSE)” in I2, copy the formula down the column to the end of the data. Let it calculate and then populate Column J with the formula, “=SUM(E2-I2)”.

This Spreadsheet should now have a 90 day to 7 day comparison for your rankings as well as possible traffic increases due to ranks improving from past ranking averages to a number 1 or 2 position.

Creating a link strength spreadsheet

This Google Spreadsheet is a little different than the other two, we’re going to be looking at External and Internal Links as recorded by Google Search Console in the “Search Traffic” section, “Internal Links”. To construct the first part of the Spreadsheet, export the table from, “Your top 1,000 pages that are linked from other domains” found in the Links to Your Site section of Search Traffic. Next go to internal links and download the table there. Combine the two so that you have the following Columns, A: Your pages, B: Ext. Links, C: Source domains, D: Internal Links. Name this tab in the spreadsheet, “LinkStrength”

Create another tab labeled, “Keywords90D” and import Supermetrics GSC data for the past 90 days Split by “Landing page,” Search query,” and displaying the metrics, “Clicks” “Impressions” “Average position” and “CTR (%)”

Now return to the LinkStrength Sheet and name the header, “Semantics & User Group” in Column E. This column is for your own categorization of pages on your site. If you have products, you can label each page by product type. Some pages–like the privacy statement, and terms of service–can be labeled “Navigation.” Others will be pages that group similar topics or are an article that would fall under the umbrella of a certain topic or reader. The idea of this column is to use labels that track to a semantic group or certain user interest.

Next, label the next few columns, “Link KW 1,” “Link KW 2,” “Link KW 3,” and so on. Just stick to 5 to start (Columns F-J) and leave them blank. They’ll likewise be for manual input later on.

The last group of columns to add will be “Search Query 1,” “Search Query 2,” “Search Query 3,” and so on. In this example we’ll use six columns (Columns K – P). Depending on how many queries you get to individual pages add or reduce the columns here to a manageable number.

The point of them will be to pull in the multiple search queries that are driving traffic to each Landing Page. If your home page is the top ranking page for your data sorted top-down from Ext. Links, skip it for the time being, it’s most likely pulling in the most searches for your brand name and root domain searches. Most of the keyword rich pages are going to be from the first sub-page onward. Use the following formula:

=IFERROR(INDEX(Keywords90D!$B$2:$B$50001,SMALL(IF(ISNUMBER(SEARCH($A3,Keywords90D!$A$2:$A$50001)),ROW(Keywords90D!$B$2:$B$50001)-ROW(Keywords90D!$B$2)+1),COLUMNS($K3:K3))),””)

What this formula is doing is looking for the partial match of the page listed in Column A, “Your pages” in the full URLs of the Keywords90D tab. You’ll notice from the highlighted sections, that this formula is being used on a large data set (50,000 search queries pulled in from Supermetrics). It will work faster and be easier to manage with a smaller set. Change the top range number to match the amount of data you collect.

After these changes, copy the formula over to the right for as many “Search Query #” columns as you have. It should change the formula as follows:

=IFERROR(INDEX(Keywords90D!$B$2:$B$50001,SMALL(IF(ISNUMBER(SEARCH($A3,Keywords90D!$A$2:$A$50001)),ROW(Keywords90D!$B$2:$B$50001)-ROW(Keywords90D!$B$2)+1),COLUMNS($K3:L3))),””)

This will look for the next instance of the landing page and the next keyword. Now you’ll be able to see on one page which keywords are driving traffic to which pages with the ability to sort those pages by number of External Links, Internal Links, Semantic & User Groups. By doing so you can quickly find the keywords from your earlier research that you’d like to target in building better internal and external links. You’ll now be able to target this linking semantically as well. The data will also tell you which pages are some of the strongest based on their number of external links, which are “orphans” due to lack of internal links, and where you can best interlink pages to both engage users and increase link strength.

Wrapping up

You now have three documents to give you a much better picture of Google Search and your Organic Channel:

1. Week to week historic keyword ranking data;
2. Recent change tracking and keyword ranking potential;
3. A link strength planning document that helps you better organize how you’d like to link pages internally and externally.

About Ryan Purkey

Ryan Purkey is an APEC based SEO, Qualified in AdWords & Analytics. You can reach him via his Google Partner agency, rQuadrant, or connect with him on his LinkedIn profile.