Import and work with Search Analytics data in Google Sheets

Smugl

Registered Member
Joined
Sep 7, 2016
Messages
60
Reaction score
31
Hello World! I translated for you an article with a Russian-language site for the tool that I use very often! The article describes a new extension that will help you bypass the limitations of Search Console and get maximum data.

Strongly do not criticize for my English, plz )

-
Author: Mihai Aperghis (Mihai Aperghis) - SEO Consultant and Director of Vertify, Romania.

One of the main problems when working with the report "Analysis of search reports" in Search Console is an inconvenient grouping of data. Downloading statistics in CSV format does not make the work easier.

Get the maximum amount of data from Search Console in the desired format will help the Search Analytics extension for Google Sheets, created by the author of the article.

The problem with the Google Search Analytics interface

The possibilities of grouping data in the report "Analysis of search queries" are limited. For example, in it you can not immediately see a list of all search queries and related landing pages. To get this information, you need to filter the data for each request (to see the landing pages) or on each page (to see relevant queries).

In fact, the report "Analysis of search queries" is not possible to conduct an extended grouping of data. To get the information you need, you need to filter the data for each key, each landing page and country. This is a very time-consuming task, requiring a lot of time.

The API comes to the rescue

In summer 2015, Google launched the official API for Search Analytics. In addition to the standard functions, it also allows the use of various filters and groupings of data.

Imagine that you now have one column with keywords, another with associated landing pages, another with their respective countries or devices. For each combination, you can see impressions, clicks, CTR, and line items. All the necessary data in one API call.


What is Search Analytics for Sheets?

Search Analytics for Sheets is a free extension for Google Sheets that allows you to retrieve data from Google Search Console (via API), group and filter them in random order, and automatically upload statistics every month.

You can install the extension from the Chrome Web Store. Another way is through Google Sheets. For this you need:
  • Open any file in Google Sheets;
  • Go to "Add-ons" -> "Install add-ons";
  • Through the search, find "Search Analytics for Sheets" and install it.

After installation, open the table in which you would like to use the extension, go to "Add-ons", select "Search Analytics for Sheets" and click "Open Sidebar".

Log in to the GSC account (make sure that Google Sheets uses the same Google account). This operation will need to be performed once for each account. After installation, the add-on will be available on all tables.

If no site is confirmed in the account through which the entry was made, the extension will return an error.

Ways to use Search Analytics for Sheets

In this part of the article, we consider several ways to use the extension.

Obtaining information on requests and related landing pages

This is one of the most useful features of the add-on. In the standard interface of Search Console to cope with this task is not easy.

To get this data, do the following:
  • Select a site;
  • Specify the desired data range;
  • In the "Group" field, select "Query", then "Page";
  • Click Request Data.

Now you have a new table that contains a list of keywords, landing pages associated with them, and data on clicks, impressions, CTR, and positions for each request-page pair.

Search for optimization opportunities by query


In the table, use a filter to display only rows with positions between 10 and 21 (usually the results on the second page of the SERP) and see if landing pages can be optimized to raise these requests to the first page. Perhaps you need to change the title tag, the contents of the pages or internal links to them.

Diagnostics of landing page effectiveness

Check the rows with positions 20+ to see if there is a discrepancy between the request and its landing page. Perhaps you need to create more landing pages, or they are pages that are optimized for these queries but are not available to Google.

Finding ways to improve your CTR

Look closely at the position and CTR. Check the rows with low CTR and high positions to see if you can improve title tags or meta descriptions for these pages. You can also add CTA elements and micro-markup to get extended snippets in the output.

Advanced grouping capabilities

Data can be grouped by date, country, or device. When grouping by date, a new column will be created with that day, when impressions, clicks, CTR and position were recorded.

This option is useful when combined with a filter on a specific request. This way you can track the positions in the issuance.

Grouping by country and device allows you to understand where your audience is.

Grouping by the device in combination with grouping by request and / or page allows you to find out how Google ranks the site on desktops and mobile devices, and where improvements are needed.

Automatic upload of Search Analytics data

As you know, in Search Analytics data is available for the last 90 days. Although Google repeatedly promised to expand this range, so far nothing has changed.

Experts use different methods for uploading statistics, including user scripts. The big drawback of these solutions is that each time the process needs to be started manually.

Using Search Analytics for Sheets, you can automate the task.

As for receiving data, you first need to select a site and configure the desired grouping and filtering. It is recommended to use grouping on request and page. You can also use the filter by country. After that, you need to activate uploading data.

What happens next: monthly (usually the 3rd day of each month) backup will be started. The data for the previous month will be added to the table (each month has its own sheet).

If data is not available at the time of copying (the update may be delayed for up to a week), the extension will retry every day until the task is completed.

The add-on will also create a separate sheet for aggregated data for the month (total impressions and clicks, CTR and line items, without any groupings or filters).

You can also configure a backup for the same site, but with other grouping/filtering options, or for another site. To do this, open a new table and activate this option there. You can always see a list of all the backups on the "About" tab.

----
Thank you for reading =)
 
Top