Our Latest News

How To Access Google Analytics Data in Google Sheets

Search Analytics for Sheets to Gain Improved SEO Insights

An SEO specialist or an SEO consultant, who works in-house or handles majority clients in an organization, uses the search console for majority reasons. The search console helps monitor and diagnose website traffic and position changes or discover opportunities for content ideas and optimizations. It also helps optimize ranking and make justified decisions for the website. Information prevailing in Google Search Console, influences technical decisions for websites to gain better marketing analysis or deeper SEO insights.

The aim of this guide is to give a base on how to use Search Analytics in sync with Google sheets to boost SEO. The guide shows us the process, extracts data and creates automatic backups from Google Search Console into Google Sheets.

Drawback of the Search Analytics UI

Google Search Console Analytics web UI is not very user friendly, as sorting and managing data for worthwhile insights is often difficult, even CSV downloads don’t make much of a difference.

Grouping is the primary problem with Search Analytics UI

For example, viewing an entire list of keywords in Search Analytics, and simultaneously getting corresponding landing pages is not possible. Alternatively one needs to filter, query by query to check out associated/corresponding landing pages and vice versa. (This is just a single case.)

Search Analytics UI, is not designed to carry out any large scale grouping .Filters need to be put individually by separate keyword, separate landing page, separate country and so on. This takes away a huge chunk of time.

API’s at the rescue.

Taking into account feedback from Search analytics feature, Google decided to make Google Search Console data accessible to developers’ via API’s, with an added benefit of applying any sort of grouping and/or filtering.

Isn’t it exciting to have a sheet where one column is filled with keywords, the other with relating pages, the one next to that with corresponding countries etc, Imagine the same sheet on corresponding columns will have Impressions, CTR’s, clicks and positions for each combination.

Everything in a single API call.

Obtaining data into Google Sheets

Search Analytics API, works as a genius it is a useful feature that automatically backs up GSC data once a month. Previously these backups were done manually or through paid sheets add-ons or a python script.

What does Search Analytics for sheets mean?

Google sheets have a few very useful and free of cost add-on, Search Analytics for Sheets is one of them. It permits fetching grouped and filtered data from GSC (via its API), as per requirement and creates monthly automated backups.

Steps towards installation of Search Analytics for sheets are pretty simple. There are two ways in which it can be done.

(a) Install from Chrome Web Store or
(b) Through Google Sheets – Add-ons

Search Analytics for Sheets

Steps
1. Create a new Google Spread Sheet.
2. On the Menu bar go to Add-ons >>> Get Add-ons
3. Next look out for Search Analytics for sheets.
4. Install it. (Accept permission request dialogue to access Google Analytics data.)
5. Once that is done open a spreadsheet where the add-on is to be used.
6. Next Go to Add-ons -> Search Analytics for Sheets -> Open Sidebar.
7. Authorize it with a GSC account (make sure that Sheets are logged in with GSC account, then close the window once it says it was successful)

*Note: This needs to be done once per user account, once the add-on is installed, it will be available for all the spreadsheets.

Search analytics for sheets

Ways in which Search Analytics for Sheets can be helpful.

A few examples on, how add-ons can be useful.

Accommodate data in regards to queries and their corresponding landing pages

Reasons can be many, diagnosing traffic changes, finding content optimization chances, or look for relevant landing pages, capturing data on queries and landing pages simultaneously, generally provide prompt insights.

Even better phenomenon here is that, it’s quite simple and requires a few clicks.
• Select the website
• Select preferred date intervals (by default minimum and maximum dates available in GSC are captured)
• In the Group field, select “Query,” then “Page”
• Click “Request Data”

That is all that is required, now a new sheet with information like, list of queries with associated landing pages is made available. Information on impressions, clicks, CTR, and position for each query-page pair is available.

*In what way the data is to be utilized is left up to the user.

Arrive at more specific grouping and filtering options

Date, device and country groupings while filtering tend to arrive at more precise data of one or more dimensions.

Date Grouping: A new column with real time information like day when the Impressions, click, CTR & position were recorded is mentioned. This in particular is useful for specific queries, in order to have a personalized rank tracker.

Grouping by device and country gives an opportunity to understand where the audience is from. Making use of country grouping showcases information about how a website is doing internationally. Very useful if the target audience is from various countries.

Whereas in device grouping it is something more experimental, considering the growth in mobile traffic all over. It is vital to understand how Google ranks a site on desktop and mobile, it also guides as to where there is need and scope for improvement.

Filtering: It is the basic process. One can choose between the comparing factors in order to gain specific information. A note here, that multiple filters are added cumulatively for the time being. Filtering can be useful in various ways. For instance, if a great deal of content marketing is involved, page filters can be used to retrieve URL’s that have blogs.

Last but the most important
Automated Search Analytics backups:
The most important information to be kept in mind is that data available in Search Analytics is only available for a time period of 90 days.

Backing up data in order to access it later in time is necessary. The necessary steps here are, both keep a track and manually download data every month, or use a complicated but automated method like the python script. Search analytics add-on sheet make things easier and allows data backup to take place effortlessly.

When requesting data, select the site and set up a grouping or filter of your choice. And directly set up backup. This ends the process and the current spreadsheet will host backup henceforward, until it is disabled.

Further going as a routine, data will be fetched automatically for the previous month and will be backed up on the 3rd day of the month. In case of delays, the add on will give repeated attempts until it succeeds.

It will also maintain a log of the attempts, which can be notified via emails, if opted for. In this way, the Search Analytics add-on makes life easy for an SEO consultant and keeps a track of reports by generating automated backups.

Share this post: