Download all search queries and landing pages from Google Search Console

This post shows you how to set up automatic monthly backups of Search Analytics data from Google Search Console. Spoiler alert: No coding skills required.

Google Search Console (formerly Google Webmaster Tools) is a powerful tool that provides insights into how Google understands your website. It’s a set of tools that help webmasters improve their websites. The resource allows checking the number of pages indexed, structured data and hreflang implementation, and much more.

I use Search Console almost every day and Search Analytics report is one of my favorite features. As you all know, Google no longer provides keyword information in Google Analytics because all organic traffic is encrypted.

Search Analytics report

On May 6th, 2015, Google officially launched the new Search Analytics report in the Search Console. The report enables you to segment your organic traffic data and analyze each segment, so you need not work with aggregated data.

Report dimensions

There are six groups (segments) that allow you to break down data.

  • Queries
  • Pages
  • Countries
  • Devices
  • Search Type
  • Data

And there are four metrics to choose from:

  • Impressions
  • Clicks
  • Average position
  • CTR (click-through-rate)

All these metrics are not as straightforward as they seem to be. So, do you know how Search Console calculates the metrics? If not, Google published a great article, explaining all the metrics, on their help center.

Download Search Analytics data

The report itself provides a decent interface that gives you the power to segment data to very detailed pieces. However, I still prefer downloading data to Google Spreadsheets for further analysis because Search Analytics report is not as advanced as Google Spreadsheets.

Also, there are two other limitations:

  • 1,000 rows limit – It may be enough for small sites, but it’s very limiting, especially for larger sites.
  • Maximum 90 days date range – Data is available only for 90 days in the report, therefore, it’s difficult to spot trends and impossible to make a yearly comparison.

Google Search Console allows you to download Search Analytics data; the “Download button is at the bottom of the report, but the “1,000 rows” limit is still there. Moreover, it exports data with only one segment. You can’t export “Pages” and “Queries” simultaneously!

How to export Search Analytics data?

To be fair and not to glorify Google Sheets only, there are four options:

  1. Manually (disadvantages described above)
  2. Using a script
  3. Excel plugin
  4. Google Spreadsheets Add-on

To export all Search Analytics data and not only a limited part, we must use Search Console API.

If you’re a programmer or a geek with some free time to kill, feel free to write your own script. Search Console API documentation can be found here. This option is the most flexible one, so if you have specific needs, this one is for you. See the Python sample for calling required method.

For the rest of us who prefer a simpler solution, two options are left. Both options use Search Console API, so they download all data as well, but also prepare data directly to use in your favorite spreadsheet program (Excel or Google Sheets).

Analytics Edge plugin for Excel

Glenn Gabe has already published a great guide on how to install and use Analytics Edge plugin in his article on searchengineland.com. So, there is absolutely no need to write any further information about it again.

Search Analytics for Sheets Add-on

This add-on can request data based on your specification instantly, but that’s not the best feature. I adore the ability to schedule daily or monthly backups.

Just set it up once and let it work. You don’t even need to open the spreadsheet. Each time a new backup is done, you will get an email notification. One less thing to think about.

Install the Add-on

  1. Open the Add-on page in Google’s webstore – link
  2. Click on the blue “+FREE” button (a new Google Sheet will be opened)
  3. Accept the authorization
  4. Open the Search Analytics sidebar (Add-on -> Search Analytics for Sheets -> Open Sidebar)

Activate Search Analytics for Sheets add-on in a Google sheet

Activate Search Analytics for Sheets add-on in a Google sheet

Request Data

Use the “Requests” tab in the sidebar for one-time requests. Most of the setup is straightforward, but there are some that worth describing

  • Verified Site – Drop-down menu with all verified properties (website) in Google Search Console associated with your Google account.
  • Date Range – 90 days limit applies here as well.
  • Search Type – Select default (web), image or video.
  • Group By – Multiple dimensions can be selected per request (Query, Page, Device, Date, Country). This is a real benefit of using Search Console API instead of GSC interface.
  • Filter By – You can filter your data based on Query, Page, Device, Country (e.g. request data only for mobile devices).
  • Aggregation Type – This setting is for cases when multiple URLs to your site appears in SERP for a given search query. This may affect all the metrics.
    • Default (auto) – Default aggregation is by property.
    • By Property – It behaves like there is only one URL
    • By Page – Calculate metrics for each URL
  • Rows returned – Forget about GSC limitations and request everything!
  • Results Sheet – Choose if you want to export data to a new sheet or an existing one.

One-time Search Analytics data request

One-time Search Analytics data request

Backup Scheduling

While scheduling backups, most fields that need to be set up are the same as for one-time requests. Then, select Monthly period (runs every 3rd of the month). Daily backups are coming soon.

If you want to work with metrics for each day, add “Date” dimension to the setup. Now, simply check a few checkboxes at the bottom of the sidebar, and you’re ready to go!

Four months of data (122 days) stored in a spreadsheet

Four months of data (122 days) stored in a spreadsheet

Privacy restrictions

There is one catch, though. The total number of clicks and impressions differ from the sum of all rows if “query” dimension is used due to privacy reasons. This may mean that Google is hiding search queries that could identify the user who performed the search. The official note is here:

“In addition, very rare queries will be omitted for user privacy reasons.”

Unfortunately, as you can see in the example below, “very rare” may even mean that over 50% queries are omitted. The example is based on a small set, but you will see that a significant percentage of queries is hidden even for large sets.

Search Analytics report showing clicks per search queries

Search Analytics report showing clicks per search queries

It’s not clear whether the data is omitted due to privacy reasons or the tool provides only a sample set of queries.

On the other hand, Google at least provides some data, so I’ll stop complaining!

Analyzing Search Analytics data

Once you have all the data in a Google Spreadsheet, you can slice and dice that information based on your specific needs.

I’ll give you two examples, but the number of options is truly endless.

Queries per landing page

Using a pivot table, we can easily group search queries to a landing page.

Landing page per query report

This is a great report because it helps analyze whether your pages show up in the right search queries.

Do you see a search phrase not answered on the landing page? If so, extend your content and provide the answer. This process of fine tuning your landing pages improves visitors’ satisfaction. Moreover, you may get a nice boost in rankings because the page answered the user’s question (user satisfaction has always been important for Google) and improved your content.

Clicks and Impressions per device type

You can also segment your traffic by device and then compare trends by using pivot tables. On the example below, you can see that desktop gets more visibility (impressions), however, search traffic to mobile devices already exceeded desktop traffic in December.

Device comparison (Desktop vs. Mobile)

Device comparison (Desktop vs. Mobile)

Wrap-up

Automate tasks wherever it’s possible, and you can focus on more important tasks that require your attention. Using this add-on, you can schedule automatic backups of valuable data from Search Analytics report for a year-over-year comparison, analyzing trends or conducting further analysis. Google Search Console provides extremely useful tools and reports, therefore, if you are not familiar with all its features, it’s about time to change it.

Install the Add-on

I'm an SEO Strategist who enjoys technical aspects of SEO and web analytics because you can't optimize anything if you don't measure it.
  • Mihai Aperghis

    Thanks for the mention, Robin!

    • http://rozhon.com/ Robin Rozhon

      Noticed you updated the add-on and added the ability to backup the data daily. That’s great!

  • Durbar Ghosh

    Thanks for the article. I was really worried that GSC stores 90 days of data. I was doing it manually for all important pages and articles one by one, each month.

    • http://rozhon.com/ Robin Rozhon

      I’m happy to hear it’s useful to you!