SEO: Get insight into your keywords with Google Search Console [spreadsheet]

[jrineakter]

For content that you have yet to write, it is very difficult to estimate how it will perform in the organic search results. Fortunately, you know much more about the content you have already written with the help of Google Analytics and Google Search Console data. The question is of course how to optimize the existing content on your website and how to discover new opportunities.


In this article I'll show you how to quickly find content opportunities within your website using Google Search Console and Google Sheets.

No time to read the whole article and want to get started with the Google spreadsheet right away? Then read this section first , so you know for sure that your keyword export is processed correctly within the spreadsheet.

Google keyword insights spreadsheet V2
In 2014 I set up a Google spreadsheet to quickly see how your website performs for different types of keywords. The spreadsheet helps you see where there are opportunities to increase CTR (click through rate).

In the meantime, quite a lot has france telegram number list changed. Google Webmaster Tools is now called Google Search Console, and the new Search Console API has been launched , which now also provides access to ranking data. Despite these major improvements, it remains difficult to gain insight into the keyword performance of a website with these Google products.

In addition to the above changes, the Google Search Console export function has also been adjusted. The new spreadsheet has been adjusted to handle this well. To gain more insight into your keyword data, I will show you how to answer the following two questions:

How does my website perform for (non-)brand related keywords, and what is this distribution?
What are the most important keyword opportunities within my website?
1. How does my website perform for (non-)brand related keywords?
The answer to this question is one of the first things I look at when assessing a website and estimating its potential. If 90 percent of your organic traffic comes from branded searches, this is a good sign that something is wrong with your website:

A the website in question consists of 90 percent company-related information and too little non-brand related content such as: product information, blog articles, news articles, manuals, tools, and so on).
B Non-brand related content can be found on the website, but the keyword focus is not optimal.
non-branded keyword opportunities

The example above involves a website that contains non-brand related content that is not targeting the right keywords.

The chosen keyword themes are too generic and the website has too little authority to rank within the top 10.

This problem can be solved by adding more long-tail keywords or by choosing less competitive keywords.

2. What are the most important keyword opportunities within my website?
CTR Analysis
Branded searches generally have a higher click-through rate than product or informational searches. If you perform a search for “snackbar Kwalitaria”, chances are you will click on the search result of kwalitaria.nl, because this is the snack bar you are looking for. All graphs within the spreadsheet are therefore divided by type of search (branded vs non-branded).

CTR per position branded vs non-branded

Okay, great, but how can I use this data to determine where opportunities lie within my website?

In the spreadsheet, go to the tab “Non-branded keywords” and filter the right column by “Traffic Potential”. Now you have a nice overview of the keywords with a relatively low click-through compared to the average CTR per position for all non-branded searches. The next step you want to take to further investigate these opportunities is simply to perform a search within Google for the term in question. Preferably do this using Google Chrome's incognito mode and to be on the safe side, also add &pws=0 to the query parameter, so that you do not receive personalized search results.

What do you want to pay attention to next?

Does the snippet (Page title, URL, Meta description and other markup) of the intended landing page within the search results match the search intent for the keyword?
Or is the relatively low CTR caused by external factors?
When it comes to CTR, you have no control over the following:

4 top ads + Google shopping ads.
Local results, but you don't have a physical location.
Visual elements within search results, such as images and video.
To further assess deviations within the CTR of a keyword, you can also use this tool . Use the filters to segment by search query type, branded vs non-branded, and the number of ads shown within the search results page.

Increase CTR
Does the keyword you are researching meet point 1 and are there no directly identifiable external factors (point 2) that cause the CTR to be lower than expected?

CTR example

In this case, optimize the display of your search snippet to improve the click-through rate:

Write an attractive and relevant page title. Try to work your focus keyword into an attention-grabbing sentence. Also watch this video for interesting ideas and inspiration:
Make sure your meta description also contains the focus keyword, so that you increase the chance that Google also shows your custom description instead of a selection that they extract from the content of the page themselves. In addition, you can work with USPs (cheapest price, delivery time, service, etc.) but also special characters , so that your description stands out more compared to that of your competitors.
Use structured data to make your results stand out even more, for example in ratings, recipe formatting, events and more.
Broaden keyword themes
Once you know which keywords rank well (preferably within the top 7) you can discover high performing keyword themes. Based on these themes you can then do broadening keyword research to further expand existing content and possibly create new pages.

How do you use Google Spreadsheet?
Make sure that both within Google Docs and Google Search Console all language settings are set to Dutch. This prevents formatting issues, which cause the data to be formatted incorrectly.
Open the GWT Keyword Research V2.0 spreadsheet and make a copy.
Log in to Google Search Console , select the website in question and go to “Search Traffic” → “Search Analytics”.
Then select “Clicks”, “Impressions”, “CTR” and Position:search console selections
Optional: You can also use additional filters such as date, search type, country and device.
Scroll to the bottom of the page and click download.
Make sure the file has all the data divided into columns (use the “Text to Columns” option within Excel for this.)
Copy all columns from the export and paste all data into the “GWT data” tab of the GWT Keywords spreadsheet.
Go to the “Settings” tab to enter the brand-related keywords WITHOUT capital letters.
You're done! All calculations are now performed automatically.