[ Updated Oct 17, 2017 ]
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.
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.
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
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.
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)”
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)”.
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”
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.
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:
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.
You now have three documents to give you a much better picture of Google Search and your Organic Channel:
- Week to week historic keyword ranking data;
- Recent change tracking and keyword ranking potential;
- A link strength planning document that helps you better organize how you’d like to link pages internally and externally.
About Ryan Purkey
Turn your marketing data into opportunity
We streamline your marketing data so you can focus on the insights.