This template helps you to quickly go through Search Console data to find content gaps in your website.
If you’ve been producing content on a regular basis, chances are there will be some topics and keywords you have missed.
Enter: content gaps.
In this article I’ll walk you through how to use data from Google Search Console and spreadsheets to quickly find content gaps you can fill in with content (or modify existing content).
What are content gaps?
A content gap is a keyword you are not ranking for, but probably should be. Specifically, this sheets template finds keywords you rank for past page 3 (an avg. position 20-100+).
Often, these are topics that are relevant to your site, but are not being targeted effectively with content. Or maybe they and could just do with better on-page optimisation. Or maybe just need a refresh.
The point is, these are pages that are already ranking. And, with a nudge, could be ranking better.
Finding content gaps in your website with Google Sheets
Finding these content gaps can take time. But thankfully it doesn’t have to with a the Search Console API and a spreadsheet.
This template goes through your Search Console data and finds pages/queries ranking in positions 20 – 100+. The sheet also organises your opportunities with the highest CTR first, so you can see which pages people are engaging with the most.
A good thing to look out for here is pages that are getting a low CTR but a high amount of Impressions. Why? Because if you are getting eyeballs on your search snippet, but no ones clicking through, you probably need to update your metadata.
How to use the sheet
To get it setup…
What you’ll need:
- Google Search Console setup for your site.
- Search Analytics for sheets add-on installed.
- A Google account (obviously) so you can make a copy of this template:
Step 1 – Install + configure the add-on
Once you’ve made a copy of the template, you’ll want to make sure you have the Search Analytics for Sheets add-on installed.
If this is your first time using the add-on, you’ll need to authorize it
Step 2 – Setting up your sheet
No you’ve got the add-on, let’s run it. Go to:
Add-ons >>> Search Analytics for Sheets >>> Open Sidebar
Psssst. Make sure your Google account is authorized to use the Search Console property you want to use.
Step 3 – Setting up the add-on
Choose your site. Make sure to add Query & Page to the Group By box.
In Results Sheet, select ‘GSC DATA’ (If you don’t select the GSC DATA tab, it won’t work. 👎)
Your setup should look like this:
NOTE: Limiting the Rows returned to 1000 rows is optional. Pulling in all the rows for a big site can slow down a sheet.
Step 4 – Pull the data
Now request data.
This will fill the content gaps tab with data.
Step 5 – Adding in search volumes [optional]
If you want to add search volumes for your keywords, you can quickly add this in too.
Column C is hidden and waiting to be filled with keyword data. Just click the small arrows between the cell headings to reveal it:
For quickly grabbing search volumes, you can use your tool of chocie.
Personally, I like to use KWFinder. It’s quick and easy to use and makes adding search volumes to our sheet pretty fast.
(As long as your data has the keyword in Column A and the search volume in Column B, the sheet will pull it through. Viva la VLOOKUPs)
Go to KW Finder and header over to import. Here, you’ll be able to drop in the keywords from the content gaps sheet:
Export these results and add them in the Search Volumes tab. This will pull volumes in the main tab.
If you want a free option for bulk grabbing this data, you can try https://searchvolume.io/
Filling in content gaps
So now you’ve got a nice list of pages and keywords to go optimize. But how?
Here’s a few things you can try:
- Add content – Sounds obvious. But look at the keywords you don’t have pages for… and then create pages for them if it makes sense to do so.
- Go after long tail keywords – These are queries made up of at least 3 words. They are more specific so have a stronger search intent and are sometimes easier to rank for.
- Update old content – You don’t always have to create brand new content. You could just look at old content and improve/update it.
- Repurpose – Repurposing content saves time (sometimes). It might be that your content isn’t engaging and could work better in a different format.
- Re-focus – Maybe you are not quite addressing the searchers query. Refocusing you page content to answer what a user actually wants to know can be all it takes.