Parsing and analyzing semantics for SEO: 5 free Google Sheets templates

When you work with semantics, you gradually “grow” with different tools. Paid services overlap the main tasks - but you don’t always want to pay (and it doesn’t always make sense). Sometimes you need to quickly find the keys, figure out the SEO plan, expand the semantics of a specific request. And here Google Sheets templates help a lot.

We share five proven templates that will be useful for promotion on Google.

1. Definition of promising key phrases

When there is little money for SEO (in the case of SMEs, this is almost always the case), moving out of the core of thousands of requests will fail. We'll have to choose the “fattest” ones, and put off the rest until better times.

One way is to choose the phrases by which the site’s pages are from 5th to 20th position on Google. According to them, you can quickly and inexpensively get into the TOP-5. Well, a jump in positions, say, from the twelfth to the third will give much more traffic than from the 100th to the 12th (you can find out the exact increase in traffic using the scenario forecast in Data Studio).

Google keyword phrases are available in the Search Console. For their unloading there is a template described in Codingisforlosers .

To unload keys from the TOP-20, you must:

• create a copy of the Quick Wins Keyword Finder template (all templates in the article are closed from editing, please do not request access rights - just create a copy and use it);
• Install the add-on for Google Sheets Search Analytics for Sheets (to configure export of reports from the Search Console to Google Sheets);
• have access to an account in Search Console and accumulated statistics on requests (at least for a couple of months).

We open the template and configure the data upload from the Search Console (menu "Add-ons" / "Search Analytics for Sheets" / "Open Sidebar").

For automatic uploading on the Requests tab:

• select the site in the “Verified Site” field (after confirming access to the account, a list of sites will appear in the site);
• in the “Group By” field, select “Query” and “Page” (that is, we will retrieve data for queries and pages);
• in the "Results Sheet" field, be sure to set "RAW Data", otherwise the template will not work.

Click the “Request Data” button. After exporting the data, the “Quick Wins” sheet shows the requests, pages, the number of clicks, impressions, average CTR and position for the period. These keys are suitable for priority promotion.

In addition to automatic unloading, the template has a manual mode. Go to the MANUAL tab and enter the data (keys, URLs and positions). On the “Quick Wins [MANUAL]” tab there will be a selection of prospective queries.

2. Search for “feil” keys (heat map of positions)

This tool from JSVXC is similar to the previous one, but solves a different problem. It helps to find “fail” requests for which the site receives less traffic. Tool feature - heat map. It is convenient to navigate with it in a large mass of requests.

“Fail” can conditionally be called requests for which the site takes positions from 10 to 100. The content of the site is usually relevant to such requests, but for some reason search engines do not put it in the TOP-10 (problems with content, internal optimization, insufficient authority of the site )

What you need to use the heat map:

• Create a copy of the Content Gap Finder template
• Install the add-on for Google Sheets Search Analytics for Sheets (if you installed it while working with the previous template, then reinstallation is not required);
• Access your account in Search Console with data for at least a couple of months.

First, configure the data upload from the Search Console:

• open the copied template and launch the Search Analytics for Sheets add-on;
• we select a site, the period of uploading data, in the field “Group By” indicate “Query” and “Page”, in the field “Results Sheet” - “RAW Data”;

• click “Request Data”.

Go to the "Content Gaps" sheet. Keys are grouped by page. For each of them, the number of clicks, impressions, CTR and average position is reflected. Color marking (heat map) helps to see the big picture and quickly find the keys you need.

Thus, we immediately see by what requests we can “tighten up” our positions. How is another question. For example, we recently talked about how to filter out old content and increase SERP traffic by more than 2 times.

3. Parsing questions and answers in search results

Questions / answers can also be extracted manually from the search results. But why, if there is a template from Hannah Rampton ?

This is one of the templates that we use when searching for ideas for content and staging TK for copywriters. The analysis of issues related to the main request allows us to delve deeper into the topic and create intent-oriented content (for more details, see our article on the Neural Matching algorithm ).

To upload questions / answers:

• Create a copy of the Google Q&A Extraction_v2 template ;
• Install the free Scraper extension for Chrome (it parses data from web pages using XPath);
• change the language from Russian to English in the search engine settings (this is necessary for the formulas to work correctly in the template).

Getting started parsing questions / answers:

• we enter into Google the main query we are interested in and find the block with questions;

• we click on relevant issues one by one - the more we open them, the more new questions will appear; our task is to open as many questions as possible;

• click on the Scraper icon in the browser and select "Scrape similar ..." in the drop-down menu;

• in the window that opens, in the “Selector” block, select “XPath”, enter a request in the field for parsing drop-down lists with questions / answers: // g-accordion-expander (note that the Columns block is filled in the same way as in the screenshot);
• click "Scrape";

• after parsing, click “Copy to clipboard”;
• open the template, go to the sheet “Google Questions and Answers”, hover over cell A10 and press Ctrl + Shift + V.

If everything is done correctly, then the fields with questions, answers and URLs will be filled in automatically.

On the “Clean Data” sheet, the same information is presented in a user-friendly text format (in addition, duplicates are excluded here).

On the “Search by Keyword” sheet, you can find questions on a given keyword (or part of it).

You can also select questions by domain - for this, enter the full URL or part of it on the “Search by Domain” sheet.

Thus, you will quickly and free of charge find relevant questions on your subject.

4. Parsing search hints

Search hints are a good source of live semantics. In order not to collect it by hand, use a template from Ranktank .

What you need to get started:

• create a fake Google account (when you run the script in the Google template, it “swears” because of an unverified source - you should not once again be scattered with personal data);
• log into this account and create a copy of the Keyword Research Tool with Google Autocomplete API 2.0 template .

How to collect tips:

• go to the “KW Topics” sheet and enter support phrases - based on them we will collect tips;

• go to the “KW Research” sheet and select the word in the “Keyword” field by which we will collect hints, and also indicate the language;
• in the upper table in the left column are collected tips for the main query, and in the remaining columns - tips for the just collected tips;

• click on “+” (for the script to work, confirmation of access to the Google account will be required);
• after that, the collected keys are transferred to the Keywords column;

• clicking on the “-” sign cancels the addition of phrases to the Keywords column.

Useful features of the template:

• if you check the box opposite the “Auto” cell, then the collected hints will be automatically added to the “Keywords” column;

• if you click on the prompt in the header of the table, the template will take this prompt as a basis and pars new prompts on it; So you delve into the selection of keys with tails on several levels;

• specify plus and minus keywords to filter the list (for example, if you specify the plus word "brembo", then only the keys with this word will remain in the Keywords column; if you specify the negative keyword "price", then all will be removed from the list keys with this word).

Despite all the advantages of the template, it has a drawback - parsing only in the region from which the entrance to the Google account is registered. For information requests this is not critical, but for commercial (often geo-dependent) problems.

Therefore, to select hints for geo-dependent phrases, it is better to use advanced parsers - for example, the PromoPult parser . Here you can specify any region of Russia and the world, set the depth and rules of parsing, configure the sources of parsing (not only Google, but also Yandex, and YouTube).

Here's a guide to parsing tips for SEO.

5. Definition of “hidden” data at the keyword level

Google Analytics has the ability to load data from the Search Console. But you will not see anything new - all the same pages, CTR, line items and impressions. And it would be interesting to see what percentage of failures when clicking on certain keywords and, more interestingly, how many goals were achieved for them.

Here the template from Sarah Lively, which is described in the article for MOZ, will help.

To get started, install the add-ons for Google Sheets:

• Google Analytics Spreadsheet Add-on
• Search Analytics for Sheets (if you used the first two templates, then you already have this add-on).

Step 1. Set up data upload from Google Analytics

Create a new table, open the "Add-ons" / "Google Analytics" menu and select "Create new report".

We fill in the report parameters:

• Name - “Organic Landing Pages Last Year”;
• Account - select an account;
• Property - select a resource;
• View - select a view.

Click "Create report". The “Report Configuration” sheet appears. At first it looks like this:

But we need it to look like this (enter the upload parameters manually):

Just copy and paste the report parameters (and delete the value 1000 in the Limit field):

Report name	Organic Landing Pages Last Year	Organic Landing Pages This Year
View ID	// here will be your ID in GA !!!	// here will be your ID in GA !!!
Start date	395daysAgo	30daysAgo
End date	365daysAgo	yesterday
Metrics	ga: sessions, ga: bounces, ga: goalCompletionsAll	ga: sessions, ga: bounces, ga: goalCompletionsAll
Dimensions	ga: landingPagePath	ga: landingPagePath
Order	-ga: sessions	-ga: sessions
Filters
Segments	sessions :: condition :: ga: medium == organic	sessions :: condition :: ga: medium == organic

After that, in the "Add-ons" / "Google Analytics" menu, click "Run reports". If all is well, you will see a message like this:

Two new sheets with report names will also appear.

Step 2. Uploading data from Search Console

We work in the same file. Go to the new sheet and launch the Search Analytics for Sheets add-on.

Unloading options:

• Verified Site - indicate the site;
• Date Range - set the same period as in the Organic Landing Pages This Year report (in our case, the last month);
• Group By - “Query”, “Page”;
• Aggregation Type - “By Page”;
• Results Sheet - select the current "Sheet 1".

We upload the data and rename “Sheet 1” to “Search Console Data”. We get the following table:

To bring the data into a form comparable with Google Analytics, change the URLs to relative ones - delete the domain name (through the replacement function, change the domain to an empty character).

After changing the URL should look like this:

Step 3. Summarize data from Google Analytics and Search Console

Copy the Keyword Level Data template . Open it and copy the “Keyword Data” sheet to our working file. In the columns “Page URL # 1” and “Page URL # 2” we insert the relative URLs of the pages for which we want to compare statistics.

For each page, statistics from Google Analytics is pulled, as well as the 6 most popular keys for which there were clicks. Of course, this is not detailed statistics for each key, but still it is better than nothing.

If necessary, you can refine the template - change the indicators, the number of keys uploaded, etc. How to do this is described in detail in the original article .

As you can see, to work with keys it is not necessary to immediately get a wallet. There are many easy solutions. Follow our publications - we will share our usefulness more than once.