Integration of Search Console with BigQuery

Connect Google Search Console to BigQuery with WitCloud and collect clicks, impressions, CTR and positions for your sites, by date, query, page, country and device.

Updated

Introduction

This integration uses the Google Search Console API to collect search performance data for your sites (clicks, impressions, click-through rate and average position) and store it in Google Cloud BigQuery. The data is collected for all search types Search Console reports: web, image, video, news, Discover and Google News.

Configuration guide

Follow the steps below to connect Search Console with BigQuery. Once the setup is complete, the data is synchronized to your BigQuery project automatically.

The Google account you authorize must have access to the sites you want to collect. Only sites that account can see in Search Console appear in the list.

Module setup

Go to Integrations and click Add integration.

Integrations list with the Add integration button

Find Search Console in the catalog and click it.

Integration catalog filtered to Search Console

Enter the Collect name, e.g. Search Console PL.

Collect name field filled in with the example name Search Console PL

Authorization

If you have already authorized Search Console and the token has the required access, pick it from the Select Search Console authorization drop-down and skip this step.

Click Authorize.

Authorize button next to the Select Search Console authorization field

In the Google window that opens, choose the Google account and allow access to your Search Console data. WitCloud asks for the webmasters permission, which lets it read your Search Console sites and their performance data. When you return to WitCloud, the new authorization is selected for you.

Site selection

Open Site urls and choose the sites you want to collect. The list shows the sites the authorized Google account can access, including domain properties (sc-domain:example.com).

Select site urls drop-down open with example sites

You can choose several sites. Each selected site becomes a separate data collect named Collect name - site url, for example Search Console PL - https://demo-shop.example/.

Select site urls field with two example sites selected

Advanced settings

Click Show advanced settings to choose Tables to collect. All three tables (totals, analytics and queries) are selected by default; remove the ones you do not need. At least one table is required. See Data schema for what each table holds.

Advanced settings with the Tables to collect field listing totals, analytics and queries

Click Next.

Collect settings filled in with the Next button

Historical data and scheduling

Enter the number of days of history to load in Historical Data. The maximum is 365 days. Google keeps Search Console data for 16 months, so older data is not available.

Historical Data field filled in with 365 days

A data synchronization schedule is created automatically. More information on this topic can be found here.

Click Create to complete the configuration.

Data collection settings with the Create button

Done! Your Search Console data will now be synchronized with BigQuery.

Data schema

Each data collect writes the tables you selected. search_console_totals has daily totals by country and device, search_console_analytics adds the query and the page, and search_console_queries has daily data by query, country and device (it does not include Discover and Google News, which report no queries). The search_type column tells which search type a row belongs to.

Data schema for search_console_totals
Field NameModeType
dateNULLABLEDATE
countryNULLABLESTRING
deviceNULLABLESTRING
site_urlNULLABLESTRING
search_typeNULLABLESTRING
clicksNULLABLEINTEGER
impressionsNULLABLEINTEGER
ctrNULLABLEFLOAT
positionNULLABLEFLOAT
Data schema for search_console_analytics
Field NameModeType
dateNULLABLEDATE
queryNULLABLESTRING
pageNULLABLESTRING
countryNULLABLESTRING
deviceNULLABLESTRING
site_urlNULLABLESTRING
search_typeNULLABLESTRING
is_anonymized_queryNULLABLEBOOLEAN
is_anonymized_discoverNULLABLEBOOLEAN
clicksNULLABLEINTEGER
impressionsNULLABLEINTEGER
positionNULLABLEFLOAT
Data schema for search_console_queries
Field NameModeType
dateNULLABLEDATE
queryNULLABLESTRING
countryNULLABLESTRING
deviceNULLABLESTRING
site_urlNULLABLESTRING
search_typeNULLABLESTRING
is_anonymized_queryNULLABLEBOOLEAN
is_anonymized_discoverNULLABLEBOOLEAN
clicksNULLABLEINTEGER
impressionsNULLABLEINTEGER
positionNULLABLEFLOAT