Explore your Google Search Console data with this free Google Sheet.
If you liked my ‘Search Console Quick Insights‘ tool, you’re in for a treat! Introducing its successor, I’m excited to release the Search Console Explorer Sheet!
Jump straight to the Google Sheet download
I hadn’t anticipated the initial tool being as popular as it was, it received a lot of attention with great feedback which was incredible to see. I get a real buzz from helping others and seeing so many people use the tools I’ve built.
I’ve spent many hours overhauling this to make this even better for you all. It’s been redeveloped so much that it feels like a completely new tool! Not only has it had a complete design overhaul but every single formula and table has been re-worked for the better. I’ve re-engineered existing functionality and added a few new additions. A few users had been running into issues with the lost queries not loading but this has all been ironed out with fully dynamic ranges across the board.
As such, I’ve taken this opportunity to name this version inline with it’s upcoming Data Studio counterpart – Search Console Explorer.
More on the Data Studio version towards the end, let me show you what I’ve been up to!
Search Console Explorer Sheet
Features include:
– Advanced Search:
Search for and exclude queries using contains or regexmatch
– Smarter Sorting:
Order in more insightful ways such as Click Gain or Loss, Impression Gain or Loss
– New Queries:
See all the new queries now receiving impressions which did not in the prev period. These can be refined with include and exclude using regex
– Lost Queries:
See all the queries no longer receiving impressions compared to the prev period. These can be refined with include and exclude using regex
– Top Insights (by Gain and Loss):
See queries with the greatest gains and losses across clicks, impressions plus new and lost visibility. Refine with include and exclude using regex
– Segmented Gain and Loss:
See the real picture with segmented gain and loss according to your search. You may have gained 60k impressions around queries relating to ‘jumper’ but how many did you lose along the way too?
– SERP Distribution (Top 30):
Clicks and Impressions are divided into the top 3 pages of the results based on average position
– [Optional] Add Search Volume data:
Using a Keyword Keg export, search volume data can be added and trends will be extracted and shown alongside.
Let’s Explore the Main Tabs!
Search All
This is the main tab to really explore your data! This table is querying all of the data including new and lost terms. Using the include and exclude functionality, you can filter queries further to see the segmented gain and loss across the subset.
For example, you may have gained 60k impressions around motorcycles but did you lose 30k along the way? The segments shown at the top can help you see the clearer picture.
As with all tabs, if you have opted to include a Keyword Keg export then search volume and annual trend data will also be shown to the left. The annual search trend is shown numerically and using a sparkline, highlighting peak months in green and the lowest months in red.
Another new addition is the Avg Position Spread, seen in the top right. Caution to this as average position data over any time period is not actionable but I’ve included this view for interest only. This divides the clicks and impressions based on average position and splits this across the first 3 pages of the results, showing you the contribution from each.
New and Lost
This tab uses the same search functionality as the ‘Search All’ tab but instead queries new or lost terms, depending on what you’ve selected.
If you leave the filters blank, all results are returned and then if you notice a pattern you wish to explore further you can refine using the include and exclude within the same tab or dive back into the ‘Search All’ tab to see the wider picture. I recommend you do move between the tabs as it’s important to see how the part plays into the whole.
To clarify:
- A New term is one you are now receiving impressions for which you did NOT in the previous period.
- A Lost term is one which was receiving impressions in the initial period but has not received ANY in the latest period. These are extreme losses, so those which have zeroed. The declines are highlighted in other tabs accordingly.
Top Insights
A leaderboard of sorts, this tab is more popular than I anticipated and offers you a dynamic selection of the greatest gain and loss across three groups: Clicks, Impressions and New and Lost visibility.
Selecting Top 5, 10, 25, 50 or even 100, the insight tables show:
- Top {X} by Click Increase
- Top {X} by Click Decrease
- Top {X} by Impression Increase
- Top {X} by Impression Decrease
- Top {X} New Queries (by Impressions)
- Top {X} Lost Queries (by Impressions)
Download the Search Console Explorer Sheet
To download your own copy, visit
https://hanr.link/SCE-Gsheet-v1
If you’re a regular user, I recommend bookmarking the link so you can make future copies without needing to come back here (Hey, come back if you want!).
Say Thanks by Sharing! If this tool has helped you in any way or you just appreciate me making it, the best thing you can do to say thanks is to share it with more people! Alternatively, consider buying me a coffee.
Where is the Data Studio version?
The ‘Search Console Explorer Studio‘ does some of what the Sheets version offers but with extra functionality around geo data, along with highlighting some potential keyword cannibalisation by the nature of presenting all URLs per query on interaction. You can find the Search Console Explorer Studio v1 here.
Keen To Build Your Own Tools?
I am often asked how someone can get started building their own tools. I recently spoke at Search Norwich about ‘How to Get Started with the Query Function in Google Sheets’. This function is the backbone of all the Sheets based tools I build and I highly recommend you having a play with this function.
You can see my slides here and the accompanying tool I built is linked below.
Query Learning Aid Workbook V1: https://hanr.link/QueryWorkbook
If you have any questions, I’ll be happy to help where I can so drop me an email or Twitter DM.