Import Google Search Console Data to Airtable

In this guide, we'll import data from Google Search Console into Airtable. We'll first set up our Airtable base and connect to Google Search Console using the Data Fetcher app. We'll then import impressions, clicks & positions for the last 7 days. Finally, we'll schedule this to happen automatically every week, so we can track these metrics over time.

Install Data Fetcher

Screenshot 2021-02-17 at 16.10.03.png

Set Up Base

We're going to import clicks, impressions, CTR and positions data from Google Search Console. Each record will represent one page/ query (keyword) combination. Each week we'll import data for the previous week into a table called 'Weekly metrics', appending it to what's already in the table. This will allow us to see the performance of pages/queries over time.

  • Create a table called 'Pages'. This table will keep track of all our site's pages, organized by URL and linked to from our other tables. There's no need to manually add your pages, as Data Fetcher will do this automatically.
  • In the 'Pages' table, change the primary field name to 'URL' and the type to 'URL' as well.
  • Create a table called 'Days'.
  • In the 'Days' table create a field called 'End date' with type 'Formula'. When we make API requests to Google Search Console, we'll reference this field as the end date. Enter the following formula:

DATETIME_FORMAT(NOW(),'YYYY-MM-DD')

  • In the 'Days' table create a field called 'Start date' with type 'Formula'. When we make API requests to Google Search Console we'll reference this as the start date. Enter the following formula:

DATETIME_FORMAT(DATEADD({End date},-7,'day'),'YYYY-MM-DD')

Screenshot 2021-04-05 at 11.05.36.png
  • Create a table called 'Weekly metrics'. We're going to import our clicks, impressions and positions (ranking) data into this table each week.
  • In the 'Weekly metrics' table change the name of the primary field to 'Id' and the type to 'Autonumber'.
  • In the 'Weekly metrics' table create a new field called 'Page' that is linked to the 'Pages' table.
  • In the 'Weekly metrics' table create a new field called 'Week end date' with type 'Created time'. In the field formatting disable 'Include a time field'.
Screenshot 2021-04-05 at 11.09.53.png

Import Google Search Console Data to Airtable

  • On the home screen of the Data Fetcher app, click 'Create request'.

Screenshot 2021-02-17 at 16.12.14.png

  • On the create request screen, enter the request name 'Weekly metrics by page & query'.

Based on the Google Search Console API documentation, we will use the searchAnalytics endpoint to fetch analytics data for our site.

  • Find the exact URL for your property in the Google Search Console dashboard. For example for this site, it is 'https://datafetcher.io/'.
Screenshot 2021-04-05 at 11.20.59.png
  • Enter this URL in the input on URLEncoder and click 'Encode'. Copy the result. For example, 'https://datafetcher.io/' becomes 'https%3A%2F%2Fdatafetcher.io%2F'.
  • In Data Fetcher, enter the following URL on the create request screen. Replace SITE_URL with the encoded URL from the previous step:

https://www.googleapis.com/webmasters/v3/sites/SITE_URL/searchAnalytics/query

{

"endDate": "",

"startDate": "",

"rowLimit": 25000,

"dimensions": [

"page",

"query"

],

"aggregationType": "BY_PAGE"

}

Including both page and query in the dimensions field means we'll create one Airtable record per page/ query combination. You can see a list of possible dimensions and make adjustments to the request in the API documentation.

  • In the request, body click in between the " next to 'endDate' and add a reference to the 'End date' column we created in the 'Days' table.
  • In the request, body click in between the " next to 'startDate' and add a reference to the 'Start date' column we created in the 'Days' table.
  • Your request body should now look something like:

{

"endDate": "***Days*End date*rec4Gsjj3NXCyJTmT***",

"rowLimit": 25000,

"startDate": "***Days*Start date*rec4Gsjj3NXCyJTmT***",

"dimensions": [

"page",

"query"

],

"aggregationType": "BY_PAGE"

}

  • Set the output table to 'Weekly metrics'.
  • Set the output view to 'Grid view'.
  • Set the Write mode as 'Append'.
  • Click 'Save'.
Screenshot 2021-04-05 at 11.43.20.png

Connect To Google Search Console

  • Scroll down to 'OAuth Connection'.
  • Click the 'Add' button on the right-hand side.
  • Select 'Google Search Console' on the modal.
Screenshot 2021-04-05 at 11.51.18.png
  • In a new window, you'll be prompted to connect your Google account to Data Fetcher.
  • In Data Fetcher, click 'Run'. You'll see a loading message while the request runs, then the response field mapping modal will open.
  • Map the following response fields:
    • 'rows.keys.1'. This field contains page URLs. So change the 'Data type' to 'Link'. Select the table field 'Page'.
    • 'rows.keys.2'. This field contains queries/ keywords. Enter 'Query' in the input next to 'Or, create field:'.
    • 'rows.clicks'. Enter 'Clicks' in the input next to 'Or, create field:'.
    • 'rows.impressions'. Enter 'Impressions' in the input next to 'Or, create field:'.
    • 'rows.ctr'. Change the 'Data type' to 'Percentage' and enter 'CTR' in the input next to 'Or, create field:'.
    • 'rows.position'. Enter 'Position' in the input next to 'Or, create field:'.
    • 'responseAggregationType'. Click the toggle so this field is not kept.
  • Click 'Confirm'. The necessary fields will be created, then the request will run again and update our base.
  • Click 'Save'.

Schedule Request to Run Weekly

Let's schedule this request to run every week, so we can track these Google Search Console metrics over time.

  • In Data Fetcher, scroll to 'Schedule'.
  • You will need a paid Data Fetcher account to use this feature, so click the link to upgrade your account if you haven't already. After upgrading, click the 'I've done this' button.
  • Add your Airtable API key if you haven't already.
  • Turn on 'Schedule this API request'.
  • Select 'Every week' in the dropdown.
  • Choose which day you want it to run on. You should only pick one day otherwise there will be overlaps in the weekly data.
  • Click 'Save'