In this week’s SEO Pub Chat (new year, new name) I want to share with you a Google Sheet I made that allows you to quickly sort top ranking search queries from Google Search Console whose CTRs may not be performing well.
Grab a copy here: https://docs.google.com/spreadsheets/d/1NSjVve9dKHItzbBntMuggzwbzp4yFIELH2ypl0ova-M/edit?usp=sharing
Go to File >>> Make a Copy
All you need to do is export your data from Search Console into a Google Sheet. Copy everything that shows up on the first tab.
Then go into the sheet I created and select the second tab. It’s titled ImportData.
Make sure you have cell A1 selected and paste everything you just copied.
Now go back to the first tab and everything will be formatted for you. Under the position column, you will see a filter set up. Click it and you will see it will sort everything ranking between position 1 and 5. Hit ‘OK’ and it will run the sort. You can change this to whatever you want.
At the top of the data you will see a yellow box. Put whatever CTR you want in there.
Everything below that CTR will now be highlighted for you to inspect.
It’s that simple.
You can look through the data, find the low CTRs and make a determination based on the query, CTR, and average ranking position if it is something you need to inspect further.
Remember that there are sometimes good reasons why a page would rank highly, but have a poor CTR. Maybe the page does not really match the search intent. There could be a featured snippet above the search results that provides the answer searchers are looking for.
This sheet is simply to help you sort your data quickly to find situations you want to take a closer look at.
I made a video showing you how to use it as well:
