I spend a lot of time digging into GSC data and am constantly uncovering opportunities for growth.
This Google Sheet is going to make that process a bit easier for you. It has two advantages over working in GSC directly.
First, it is much faster to sort through than working in GSC. Second, it’s going to allow you to quickly see where a page is missing clicks.
So let’s dive in how to use it.
Head into Search Console and click on Search Results. Then filter the timeframe to 28 days. You could use a longer timeframe, but I want more recent average ranking data, so I find 28 days works best.
Next, pick a page you want to dive into, and filter by that page by clicking on it. You can also manually filter for a specific page by clicking on the +New filter button, selecting page, and than entering in the URL.
Now export everything to Google Sheets.
Make sure you are on the Queries tab in the exported Google Sheet and select all and copy everything.
Now go to the my Search Opportunity sheet. Click on File >>> Make a copy to make your own copy to use. You do not need edit permission on this sheet. You are going to create your own copy to use whenever you want.
On the Search Opportunity sheet, go to the tab that says ImportData.
Make sure you are on cell A1 and now paste everything in.
Go back to the Search Opportunity tab.
What this sheet is doing is simple but I find it really useful for quickly finding potential areas to increase traffic. All it is doing is subtracting the total number of clicks for each search query from the number search impressions.
Obviously, you are never going to get 100% of the clicks no matter how well you craft a title tag or how high you rank, but when I look at this data in relation to the average position this quickly helps me identify areas for improvement.
I like to take it a step further too.
There are filters in place on this sheet. There are different ways you can use these filters.
For example, click on the filter icon beside Position. Select Filter by condition. On the dropdown menu, look for Is between.
Select 1 and 3.
Now you have search queries on this page ranking with an average position between 1 and 3.
If I see a lot of missed clicks here, it could be because of things like:
- bad title tags
- targeting the wrong search intent
- getting killed by a feature snippet
You can also use the filter to sort the Opportunity column from highest to lowest.
Again, make sure you grab a copy of the Sheet here.
