How to Use Search Analytics in Google Sheets for Better SEO Insights – SEO NYC & Digital Marketing

As an SEO, if you’re working in-house or managing many clients in an agency, you’ve likely been using this tool for a lot of factors. When it’s assessing traffic and standing changes, or finding opportunities for optimizations and articles thoughts, Google Search Console’s Search Search Analytics continues to be at the core of the majority of SEOs’ toolset.

The range of this small guide is to give you a couple of tips on the best way best to use Search Analytics collectively with Google Sheets to help you in your SEO work. As with the guide on the best way to perform competitive analysis in Excel, this one can also be focused around a tool that I’ve built to help me get the most of Search Analytics: Search Analytics to get Sheets.

The Issue with the Search Analytics UI

Sorting out and managing data in the Google Search Console Search Analytics web UI so as to get meaningful insights is often tough to perform, and even the CSV downloads don’t make it much easier.

The principal problem with the Search Analytics UI is grouping.

If you’d love to find a listing of all the key words in Search Engine Optimization and, at the identical moment, get their corresponding landing pages, then you can’t do this. You instead need to filter question by question (to determine their associated landing pages), or any page by page (to determine their associated inquiries). And this is simply 1 example.

Search Analytics Grouping

Fundamentally, with the Search Analytics UI, you can’t do some kind of grouping on a big scale. You have to filter by every key word, every landing page, every country etc. so as to get the data you need, which would take a great deal of time (and possible a part of your sanity too).

In regards the API for its save

Official Google Webmaster Central Blog Search Analytics API

Using it, you can do pretty much anything you can do with the web UI, with the added benefit of applying any kind of grouping and/or filtering.

Imagine you can now have one column full of keywords, another column with their corresponding landing pages, and then maybe another one with their corresponding countries or devices, and also have impressions, clicks, CTR and rankings for each mix.

Everything in 1 API phone

Getting the data into Google Sheets

I’ve traditionally enjoyed utilizing Excel but have previously migrated over to Google Sheets due to its cloud-nature (which means easier sharing with my own co-workers) and expandability via scripts, scripts and add-ons.

After being greatly inspired by Seer Interactive’s SEO Toolbox (a open-source Google Sheets library that delivers some very pleasant features for daily SEO tasks), then I chose to build a nickname script that could use the Search Analytics API.

I liked the concept of speeding up and enhancing my daily monitoring and assessing for traffic and ranking alterations.

Additionally, utilizing the API gave me the most fairly useful characteristic of automatically backing up your GSC info after a mont. (Before, you needed to perform this manually, then use a paid Sheets add-in or a Python script.)

Once things began to take form with the script, so I realized that I could take this public by releasing it into an add-on.

What’s Hunt Analytics to sheets?

To put it simply, Search Analytics for Sheets is a (completely free) Google Sheets add-on that allows you to fetch data from GSC (via its API), both booted and filtered into your liking, and also create automatic monthly copies.

  • Open a Google spreadsheet
  • Visit Add-ons -> Buy add-ons
  • Look for Search Analytics to Sheets
  • Setup it (It will request that you authorize a lot of things, however you can rest safe: The add-on has been reviewed by Google without any data has been saved/monitored/used in any other manner except catching it and placing it on your spreadsheets).

Once that is done, start a spreadsheet where you’d like to use the add-in and:

Search Analytics for Sheets Install

  • Visit Add-ons -> Search Analytics to Sheets -> Open Sidebar
  • Authorize it along with your GSC account (be sure you’re logged in Sheets together with your GSC account, then close the window after it says it was successful)

You’ll simply have to do this once per user account, so when you put in it, the add-in will soon be available for all your spreadsheets.

PS: You’ll find an error should youn’t have any sites verified in your logged in account.

How do Search Analytics to Sheets help you

Next, I will give you a few examples on what you can use the add-on for, based on how I mainly use it.

Grab information on inquiries and their associated landing pages

When it is to diagnose traffic fluctuations, find content optimization chances or check for appropriate landing pages, getting data on both questions and landing pages at the identical time can usually offer instant insights. Apart from automated backups, this is undoubtedly the feature that I use the most, especially as it’s rather tough to replicate the procedure utilizing the typical web UI.

On Top of That, it’s quite simple to do this and requires Just a couple clicks:

  • Choose the website
  • Select your preferred date period (by default it will catch the minimum and maximum dates available in GSC).
  • At the Group field, select “Query,” then “Page”
  • Click “Request Data”

You’ll now have a fresh sheet comprising a list of questions, their associated landing pages, and information regarding impressions, clicks, CTR and rank for every query-page pair.

Search Analytics for Sheets Example 1

What you do with the data is up to you:

  • Assess keyword chances

Use a sheets filter to just show rows with rankings between 10 and 21 (usually second page outcomes) and also see if landing pages can be further optimized to drive these questions to the very first page. Maybe work a bit on the name tag, internal and content linking to these pages.

  • Diagnose landing page performance

Check place 20+ pops to see whether there’s a mismatch between the question and its landing page. Maybe you ought to make more landing pages, or you will find pages that target those questions but are not available by Google.

Look carefully at position and CTR. Assess low-CTR rows with associated high position values and see whether there’s any way to improve titles and meta descriptions for those webpages (a call-to-action may help), or maybe even add a few wealthy snippets (they’re fairly helpful in raising CTR without much work).

  • Learn why your traffic dropped
    • had significant modifications in traffic? Do two requests (as an instance, just one for the past 30 days and you for the past 30 days) subsequently use VLOOKUP to compare the data.
    • Areas dropped across the board? Time to check GSC for improved 4xx/5xx errors, manual actions, or faulty website or protocol migrations.
    • Positions haven’t dropped, but impressions and clicks did? Might be more seasonality, time to check year-over-year analytics, Google Trends, Keyword Planner.
    • Impressions and places haven’t dropped, but clicks/CTR did? Manually check these questions, see if the Google UI has shifted (more top ads, featured snippet, AMP carousel, “from the news” box, etc..)

I might go on, but that I should probably leave this for a separated post.

Get Increased granularity with further grouping and filtering options

Despite the fact that I don’t use them as much, the current date, country and device groupings let you dive deep into the data, while filtering allows you to fetch certain data to a more dimensions.

Search Analytics for Sheets Grouping

Date grouping produces a new column with the actual day once the impressions, clicks, CTR and position were recorded. This is particularly useful together with a filter for a specific query, which means that you can essentially have your own rank tracker.

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

Using country grouping will let you know how your site fairs globally, which is obviously highly useful if you target customers in more than 1 country.

However, device grouping is probably something you’ll play more with, given that the increase in traffic everywhere. Together with query and/or page grouping, this can is helpful to understand exactly how Google ranks your website on mobile and desktop, and in which you may need to improve (generally speaking you’ll probably be more interested in mobile rankings here rather than desktop, because those might pinpoint problems with certain pages on your website and their mobile usability).

Search Analytics for Sheets Grouping Example

Filtering is exactly what it sounds like.

Pick between question, webpage, country and/or device to select specific information to be recovered. You may add some filters, so simply remember that, for the time being, multiple blockers have been added cumulatively (all conditions must be fulfilled).

Search Analytics for Sheets Grouping Example

Aside from the status monitoring example mentioned earlier, filtering can be helpful in other situations also.

If you’re doing a lot of content marketing, maybe you’ll use the webpage filter to simply retrieve URLs that contain /blog/ (or anything subdirectory your articles is below), whereas filtering by country is great for international websites, as you may anticipate.

Just remember 1 thing: Search Analytics provides a good deal of data, however not all the data. They have a tendency to leave out information that is overly human (as in, very few users can be aggregated because outcome, such as, as an instance, long tail questions).

This also suggests that, the further you group/filter, the less aggregated the data isalso, and a specific advice will not offered. That does not mean that you shouldn’t use groups and filters, it’s just something to keep in mind when you’re adding up the amounts.

Saving the best for last: Automated hunt Analytics backups

This is the feature that got me into building this add-in.

I use GSC data quite a bit, from client reports to comparing data from several time periods. Unless you’ve never used GSC/WMT at years past it’s highly unlikely you don’t understand that the data out there in Search Analytics merely spans about the previous 90 days.

This typically requires either remembering to manually obtain the data every month, or utilizing a more complex (but automated) method like a Python script.

The Search Analytics for Sheets add-on allows you to do this effortlessly.

Much like when requesting data, select the website and set up any filtering and category that you’d love to use. I strongly advise using question and page grouping, and possibly country filtering to cut some of the sound.

Then simply allow the backup.

That is it.The current spreadsheet will host that backup from today, until you decide to disable it.

Search Analytics for Sheets Example 2

What happens now is that once per month (normally on the 3rd day of the month) that the backup will run automatically and bring the data for the preceding month into the spreadsheet (every month will have its own sheet).

In case there are delays (sometimes Search Analytics info can be postponed even up to a week), then the add-on will likely re-attempt to run the backup daily until it succeeds.

It’s going also maintain a log together with all backup attempts, and send you an email if you would like.

Search Analytics for Sheets Backup Log

It is going to also make another sheet for monthly aggregated data (the complete number of clicks and impressions plus CTR and place data, without any grouping or filtering), and so that way you’ll be sure you’re ‘saving’ the real overview information too.

If you would prefer more than 1 backup (either another backup for the identical site but with different grouping/filtering options or a fresh backup for a different site), then just start a new spreadsheet and allow the backup there. You’ll always be able to see a listing including all the backups inside the “About” tab.

For now, just monthly backups are available, though I am considering including a weekly and/or daily alternative too. But that might be more complex, especially in instances where GSC data is postponed.

Going Additional

I hope you’ll find the tool as useful as I think it is.

If you do discover anything else or have any feature requests, please allow me to know via the add on feedback function in Google Sheets or via the type on the official website.

Otherwise, I hope that the tool can help you in your daily SEO work as far as it helps me. Looking ahead to find out more use cases to this in the remarks.

PS: The application does not support more than 5,000 rows at the moment, working on becoming that enhanced!

Subscribe to The Moz Top 10, a semimonthly mailer upgrading you on the top ten newest pieces of SEO news, suggestions, and rad hyperlinks found by the Moz team. Think of it as the exclusive digest of things you don’t have the time to hunt down however would like to read!

Like this: