Search in Google Sheets for Better SEO Insights

As an SEO, whether you’re working in-house or handling many clients in an agency, you’ve been using this tool for a bunch of reasons. Whether it’s diagnosing traffic and position changes or finding opportunities for optimizations and content ideas, Google Search Console’s Search Search Analytics has been at the core of most SEOs’ toolset.
The scope of this small guide is to give you a few ideas on how to use Search Analytics together with Google Sheets to help you in your SEO work.

Some problem Search Analytics UI

problem with the Search Analytics UI is grouping. If you’d like to see a list of all the keywords in Search Analytics and, at the same time, get their corresponding landing pages, you can’t do that. You instead need to filter query-by-query (to see their associated landing pages), or page-by-page (to see their associated queries). And this is just one example.

API for the save in SEO Technology

Google launched the official API for search Analytics With it, you can do pretty much anything you can do with the web UI, with the added benefit of applying any sort of grouping and/or filtering.

Everything in one API call
Query
Page
Country
Device
Clicks
Impressions
CTR
Position\

Take the data into Google Sheets

I have enjoyed using Excel but have since migrated over to Google Sheets due to its cloud nature (which means easier sharing with my co-workers) and expandability via scripts, libraries, and add-ons.I liked the idea of speeding up and improving my daily monitoring and diagnosing for traffic and ranking changes.

What is Search Analytics for sheets?

Simply put, Search Analytics for Sheets is a (completely free) Google Sheets add-on that allows you to fetch data from GSC (via its API), grouped and filtered to your liking, and create automated monthly backups.

Search Analytics for Sheets can help you
Next, I’ll give you some examples on what you can use the add-on for, based on how I mainly use it.
Grab information on queries and their associated landing pages
it is to diagnose traffic changes, find content optimization opportunities, or check for appropriate landing pages, getting data on both queries and landing pages at the same time can usually provide instant insights.

You’ll now have a new sheet containing a list of queries, their associated landing pages, and
information about impressions, clicks, CTR, and position for each query-page pair.

Check keyword opportunities
Use a sheets filter to only show rows with positions between 10 and 21 (usually second-page results) and see whether landing pages can be further optimized to push those queries to the first page. Maybe work a bit on the title tag, content and internal linking to those pages.

Diagnose landing page performance

Check position 20+ rows to see whether there’s a mismatch between the query and its landing page.
Perhaps you should create more landing pages, or there are pages that target those queries but aren’t accessible by Google.

Improve CTR
Look closely at position and CTR. Check low-CTR rows with associated high position values and see if there’s any way to improve titles and meta descriptions for those pages (a call-to-action might help), or maybe even add some rich snippets (they’re pretty effective in raising CTR without much work).

Get your traffic dropped
Had significant changes in traffic? Do two requests (for example, one for the last 30 days and one for the previous 30 days) then use VLOOKUP to compare the data.

Positions dropped across the board? Time to check GSC for increased 4xx/5xx errors, manual actions, or faulty site or protocol migrations.
Positions haven’t dropped, but clicks and impressions did? Might be seasonality, time to check year-over-year analytics, Google Trends, Keyword Planner.

Source:moz.com

Peter Zmijewski is the founder and CEO at KeywordSpy. His expert knowledge on Internet Marketing practices and techniques has earned him the title “Internet Marketing Guru“ He is also an innovator, investor and entrepreneur widely recognized by the top players in the industry.