Google Search Console Google Search Console + Google Sheets Google Spreadsheets =

KPIBees Google Add-on.

Search Console to Google Sheets

As marketers we spend an important part of the working day checking stats on various tools that we’re using. It’s tedious process as we need to login into each individual platform. Also, if you’re doing marketing KPI reports for your business or clients, a lot of times you find yourself copy/pasting data from tools like Google Search Console to Google Sheets. This implies hours of manual work each week which would definitely be better spent on other tasks.

KPIBees offers a better approach to KPI maintenance: it enables you to pull data automatically for Google Search Console, and other marketing tools, to Google Sheets with just a few clicks! The KPIBees Google Sheet Add-on provides a user interface within Google Sheets that enables you to query the Google Search Console API directly within the sheet, and pasting the result starting with your selected spreadsheet cell. This feature grants you the power to build powerful marketing reports within minutes!

Another problem that KPIBees solves is that you can use the add-on to surpass the limitation of 16 months data retention policy that Search Console has. Using this add-on your data can be kept historically and never lost!

An important thing to note about the KPIBees’ Search Console integration is that it goes beyond the limit of 5k limit that Search Console API has in place through a smart system that performs multiple queries in background for each given query.

 

Search Analytics to Google Sheets Add-on

 

To get started, please follow the next steps:

Step 1: Install the KPIBees Gsuite Marketplace Sheets Add-on.

Step 2: Grant access to KPIBees to your Google account. Don’t worry, we’re 100% GDPR compliant!

Step 3: Open KPIBees in any spreadsheet by going to Add-ons / KPIBees / Launch.

Google Sheet Add-on location toolbar.

 

Step 4: Click on the Google Search Console integration.

 

Google Search Console Integration Google Sheets

 

Step 5: Authorize KPIBees to access your Google Search Console account. Then you need to name your query, select the desired Fields to fetch to the spreadsheet – see more information about the parameters that you can use.

 

Select Google Search Console API fields to import to Google Sheets

 

Step 6:  Once you run the query, the result is outputted starting with your selected spreadsheet cell.

 

Pull Search Analytics data to selected spreadsheet cell.

 

Step 8: You can add as many Google Search Console queries as you want from multiple accounts or website and even other data sources! These queries can be edited or refreshed from the Manage Queries menu at any point in the future.

Add multiple mysql queries in a spreadsheet

 

Step 9: When you’re done with your query setup you can set automated refresh triggers with email or Slack notifications! This ensures that your data is always up to date when you’re coming back to the spreadsheet. Talk about efficiency!

Schedule automatic refresh of mysql queries in excel

Google Search Console API 

Let’s go over the query options that are offered by the Google Search Console API so that you can make the fullest out of the Search Console integration.

Search Console Property

The Search Console Property represents the website that you want to use in the query. At the moment you can have a single website in the query. If you have an interesting use case of needing more than 1 website in query, please let us know, we’re interested in the use case!

Google Search Console API Fields

  • Clicks: The Clicks represent the number of clicks that you received on Google Searches.
  • Country: The Country represent the country of origin where your Google Search clicks came from.  
  • CTR: The CTR, also known as “Clickthrough Rate”,  is the number of clicks that your page(s) receive divided by the number of times your page(s) are shown in search results.
  • Date: The dates from the results were achieved.
  • Device: The devices of user used in the results, such Desktop, Mobile and Tablet.
  • Impressions: The number of times your page(s) were shown in Google Search.
  • Page: The website url(s) of the result(s).
  • Position: The position of the page within the Google Search results.
  • Query: The keyword used in the Google Search that generated a click or an impression for your page(s).

Google Search Console API Aggregation

This parameter establishes how the aggregation of the results is done. There are a couple of possibilities:
  •  Auto: This is the default option and it allows the system to decide how the aggregation is done.
  • byPage: The values are aggregated by URI.
  • byProperty: The values are aggregated by Property.

Google Search Console API Search Type

 Google Search Console results are divided in several categories depending on the traffic type, such as:
  • Web: This is the default option and it reflects web searches.
  • Video: The Video option will retrieve Video Search results.
  • Image: The Image option will retrieve Image Search results.

 

Furthermore, KPIBees supports integrations that enable you to create incredible marketing reports that include your Google Search Console reports:  Here are all the Google Sheets integrations that KPIBees supports: