Identifying Organic Traffic Losses With This Simple Google Sheet

Have you ever been hit with a traffic loss and are not sure where to start in identifying what happened? Did search queries lose rankings? Was there less search volume? Did those changes I made to title tags on a few pages hurt the CTR?

Of course Google Search Console contains comparison data that you can use to sort a date range versus another date range, but reading and analyzing data inside of Search Console is often slow and cumbersome, especially on larger and higher volume sites with lots of search queries involved.

You can grab a copy of this sheet at: https://theseopub.com/sheets/identifying-traffic-losses

Below I explain how to use it. There is also a video explanation linked at the bottom.

Screen shot of Google Search Console selecting comparison data

I created this sheet so you can more efficiently analyze what Search Console spits out, and it is super easy to use.

The first thing you want to do is make your own copy of the sheet. Go to File >>> Make a Copy.

Making a copy of a Google Sheet

Next, select the comparison data you want to inspect from Google Search Console. You can use one of the preset options such as the past 28 days, past 3 months, past 28 days versus the past 28 days year over year, or you can create a custom date range. The sheet will work no matter what you select.

Then export the data into a Google Sheet.

menu option to export data from Google Search Console

Once the data has exported, you want to select all and copy everything on the Queries tab.

Go to the Google Sheet I shared, select the tab at the bottom that is named ‘ImportData-Queries’.

screen shot of Google Sheet tabs

Make sure you have cell A1 selected and paste everything you copied from the export.

Then go back to the export data sheet and select all and copy everything on the Pages tab.

Pages tab in Google Sheets from Search Console export

Go back to the sheet I shared and paste all of this into the tab labeled ‘ImportData-Pages’.

Now you can go to the Query Analysis and Page Analysis tabs and see all the data sorted nicely for you. It highlights click, impression, position, and CTR losses in red. The columns above also have filters for sorting data. If you want to sort by search queries that lost the most clicks, you can do that.

Sorting data in Google Sheets

You can do the same thing with the Page Analysis tab, but this tab sorts your data by page, not query. You can see pages that lost traffic or impressions, etc.

Again, you can grab your own copy of the sheet at: theseopub.com/sheets/identifying-traffic-losses

Here is the video version of how to use this sheet: https://youtu.be/pPddmSfmwZY

Tools I Use:

🔎  SemrushCompetitor and Keyword Analysis

✔  Monday.comFor task management and organizing all of my client work

🗄  FraseContent optimization and article briefs

👑  ContentKing AppSite crawler, monitoring, and audit tool

🤖  JasperAI writing assistant

Sign Up So You Don't Miss the Next One:

vector representation of computers with data graphs

Past tips you may have missed...