Keyword Research: How to do a Search Console Keyword Gap Analysis
Video Transcription
Phil Pearce: Hi. This is a walkthrough of Accelerate Agency's template for a gap analysis using a Google Sheet.
Phil Pearce: The first thing you'll need to do is make sure you've got Google Search Console access, so that the sheet is able to run. You'll also need to install a plug-in for Google Sheets, which is just this link here, and it's Search Analytics for Sheets. Then, if you open the link here, which is bit.lygap-template, it will take you to the sheet. Once we've got the sheet open, you'll be in read only mode, so you'll need to press make a copy which is just file and make a copy. I'm also going to rename this the name of the client or the account that I'm going to run this on. I'm going to use Accelerate Agency as the demo of this sheet.
Phil Pearce: Once I've got the copy open and I've installed the plug-in, I should now see it in the list where it says add-ons and Search Analytics [inaudible 00:01:14]. I'm going to press open sidebar and then select the site that I'm interested in running the gap analysis on, which is accelerateagency.com. It's going to default to the last 90 days. That's fine for these purposes. I'm going to select the fields of page and then query, and they need to be in that order. I'm going to add a filter that says, "Only the country of United Kingdom." In this one needs to be a lower case value, and it's the three digit short code for the country. In this case, it's GBR.
Phil Pearce: It is possible to add another filter for device, such as desktop or mobile. For this particular one, I want to blend both together so I'm going to not add a device filter. Then, just press request data and it's going to go off and grab that data out of Search Console and then insert it into a new sheet. Once it's grabbed that data from the sheet, if you select all, by press the little box in the far left and press control C to copy, and then open the little tab that says, "UK desktop," and then paste in those values into the sheet. I'm going to paste it into field B. I'm going to use paste special and paste all of these text values in.
Phil Pearce: It's going to take a few moments for that to kind of paste in and for Google Sheets to finish. I'm just going to hide the search bar, the plug-in bar now, because I don't need that. I'm just going to check to see what it added. This has added about 6,000 rows in this one. Then, if you click in cell H2, you just need to edit the formula to detect whether it's a brand phrase or not. I'm going to use part of a brand name on this one, so I'm just going to do a wildcard for the word accelerate. I'm actually going to do the second one for just 'acc'
Phil Pearce: You don't necessarily have to populate the second one, but as long as it's detecting whether it's a brand phrase or not. The reason for this is we want to split our brand and non-brand, so that we can work out... Share a search for both phrases separately. It's marked that our brand phrase is Accelerate Agency Bristol as a brand match on that one. I'm also just going to make sure that it's copied all of the other formula down. I'm going to select cell I2 across, press control C to copy that, and then paste all these values down to the end like that. It's going to paste and populate the field called quadrant position and query type, which are going to be used in the charts to the right in a second.
Phil Pearce: Again, I'm just waiting for Google Sheets to kind of finish spinning around. It's copied and that all looks good. Just make sure that it does copy all of these formula right down to the end of the sheet. To check that's worked, I'm going to go onto the brand and non-brand report. It's got kind of data in there now. If there is a lot of rows, then you need to just edit the charts for these so it selects a data range that includes everything. I'm going to actually just go to the very end of the sheet. This one is using cell number 6449 as the last kind of range. I'm just going to tweak the range for the chart to six, I think it was 6499, I believe. 6499.
Phil Pearce: I just need to ensure that's set on all of these charts [inaudible 00:05:39] selects all of the data. You can then remove the first sheet as you'll no longer need that. That's the data collection kind of piece done. In terms of analyzing the results, we'll just have a look and see what it's come up with. On the non-brand report, it's saying there's a couple of positions here. We've got one phrase, which is in position one, with a really good click through rate. These other phrases, that one in number six, high volume searches, although the click through rate for that one is not massive.
Phil Pearce: That one in position five looks interesting, and the same with two and three, where it's got a reasonably good kind of click through rate, but the potential to kind of push those ones up. I'm also just going to go into non-brand detail where it actually tells me exactly what those phrases are. In this one here, you'll see that one in position four, which has got 15% click through rate in position four, which is amazing, that's a really good signal, but it's in position four rather than position one. That would be a really good phrase to target to push up and optimize.
Phil Pearce: The same with tag manager agency, reasonably good click through rate for a lowest position. Then, Google Analytics Agency in position nine, a 10% click through rate so these ones are all really good. The size of the bubble affects the volume. We'll just have a look at the phrases and the pages to see if there's kind of any overlaps, which the keywords through to the landing pages on these. That's the page that needs optimizing. This is potentially a way of just quickly checking to see the size of the opportunity and see which keywords and which phrases can be focused on to kind of generate improvements quickly. All right, thanks. If you like this video, please kind of like and comment in the bits below.
Welcome to Accelerate Agency’s guide on how to do a Keyword Gap Analysis. A gap analysis is a fantastic method of keyword research that will allow you to find key phrases that you can quickly optimise your webpages for. It allows you to find keywords that your web pages already rank for and more specifically, which of those pages rank for that keyword. So it’s really important for your SEO.
Today we’ll be showing you an extremely in-depth guide on how to perform a gap analysis. we’ll be performing a gap analysis on our own site www.accelerate-agency.com so that you can follow along as we do it.
Be prepared for lots of screenshots and lots of minor details because this guide has been made so that even your grandma could do it! However, by the end of it, you will have mastered an extremely powerful tool for keyword research.
Keyword Data Collection
First of all, you want to make sure that you have access to the Google Search Console of the company you want to perform the gap analysis for. For me, I’ll be using Accelerate Agency’s website.
You need to be using Google Sheets, then you will need to install a plugin for Google Sheets called ‘Search Analytics for Sheets’
You will also need to make a copy of this keyword gap analysis template we’ve made for you. There are also some notes in the first tab to help you out during the process to give you quick tips when you get lost.
Now you can rename your document to the company you are researching. we are going to do Accelerate because that’s us!
Book a Consultation
Now you have installed the plugin, you should be able to click on add-ons on the tool-bar and select search analytics for sheets. It will open up a sidebar to the right which we will be using a lot. So please keep it open.
The first thing you want to do on the sidebar is to select the verified site. This is the site that you will be researching for the keyword gap analysis.
The date range will usually default to 90 days or longer which is fine for this type of search. However, if you want to change the date ranges to explore different data, you need to do this now.
Now you need to group by page and then query. It is essential that you group them in this order. Otherwise, your keyword research won’t work. So please put them as we have done on the screenshots.
Add a country filter to equal the country that you live in. If you want to analyse global data, then this isn’t necessary. However, for Accelerate, we are just going to analyse the greatest country on Earth: the UK. This is because Accelerate, for the time being, are only interested in ranking for keyphrases relevant to UK users. Therefore we’ve put in the three-digit country code the for the UK: gbr.
It’s also possible to add filters for other things such as device etc. However, for our keyword gap analysis, we want to analyse every device.
Now click ‘request data’. The data will be pulled from Google Search Console. Therefore it’s super reliable.
Wait a few moments and all the data relating to keyword research for your site will be pulled. Click the box in the top left to select all the data or press ctrl+a, then copy the data by pressing ctrl+c.
Now go over to the UK Desktop tab. Here you’ll need to select cell B1 and paste the values only. You can do this by pressing ctrl+shift+v or right-clicking and navigating the menu as we’ve done.
In the same tab, go over to cell H2 and edit the formulae. You need to replace the word ‘brand’ with your brand name and the word ‘brand2’ with your second brand name. This will enable you to distinguish branded keywords from non-branded keywords. This is an important factor in making your keyword research effective. So please make sure you include your brand name.
I put in ‘accelerate’ for ‘brand’ and ‘acc’ for ‘brand2’.
To make sure it does this for every cell below it, you need to copy the cell you have edited. Then press ctrl+shift+down to highlight all cells below and then press ctrl+v to paste the values. Please allow about fifteen seconds for your table to adjust.
As said before, we want to do this so we can tell branded searches apart from non-branded. So check that it says true next to the branded phrases.
Now check that the cells from ‘isbrand’ and to the right have all their formulae working. Our first row’s formulae were not working so we just copied and pasted the second row into the top row. This just copies the formulae and not the actual values themselves.
Now everything should be working well. To check that the formulas are working well, go over to the pivot non-brand and pivot brand tabs. If data is populating the tables, it’s working!
Close the add on so that the pivot table editor shows up.
You want to make sure that you are using all the data available. So you now need to check how many rows are in the UK desktop tab.
Then you need to replace the second number in the date range with that number. Make sure you do this for both branded and non-branded.
You can now remove the first sheet as you don’t need it anymore. Now the data collection part of your keyword research and gap analysis is done!
Analysis
Now onto the analysis! Go back to the pivot non-branded tab to find some jazzy looking charts. The bubbles represent different keywords. The bigger the bubble – the higher the volume of searches for that keyword. The higher up along the y-axis the bubble is – the higher the CTR is for that keyword. The further along the x-axis the bubble is – the lower your position is for that keyword.
This graph is to show you, at a glance, if there are any quick wins. It also shows you how your company is performing for key phrases in general. For Accelerate keywords in position 2, 3 and 5 look the most interesting as they are high up on the y-axis. This means we are already getting a high CTR and therefore are likely to be relevant. They also have an OK search volume. They also are already in a relatively high position, which makes them low hanging fruit.
Now we have had a quick glance at how we’re doing, we can check the ‘non-branded detail’ tab. Here we can see the actual keywords and our position and CTR for them.
You can also play around with the filters at the side of the graph to change how the keywords are sorted to show different keywords.
We can see when sorting by the SUM of impressions, that ‘cro agency bristol’ and ‘actionfield’ stand out. They are two keywords that could be quite useful to optimise as they have a high CTR and are already in a high position. I could play around with the filters to find other keywords too and you definitely should for your gap analysis.
You are essentially looking for phrases that have a high CTR. This is because it means your website is showing up for keyphrases that are relevant and users are visiting your site because of this. The graph only shows the top ten, so all phrases here are going to be low-hanging-fruit anyway.
Finally, we can go over to the ‘non-branded detail 2’ tab. Here you can find the URL of your website that matches that keyphrase. This way you can find the web-page you need to optimise for that keyword.
There it is! You have completed an important part of keyword research. You now should have identified some keywords that your domain is already ranking for.on top of that, you should have also found which specific pages of your domain are ranking for these. This is great because now you know which pages to optimise for which keywords.
The keyword gap analysis is a really great tool for getting quick wins and finding easy ways to boost your SEO. Utilise this powerful tool today along with our arsenal of super useful SEO tools to master the art of SEO.
Alternatively, contact the experts and ask for a consultation to take your SEO to the next level!
- About the Author
- Latest Posts
Nick Brown is the founder & CEO of accelerate agency, a SaaS SEO agency. Nick has launched several successful online businesses, writes for Forbes, published a book and has grown accelerate from a UK agency to a company that now operates across US, APAC and EMEA and employs 160 people. He was also once charged at by a mountain gorilla
