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.

Find Search Console in the catalog and click it.

Enter the Collect name, e.g. 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.

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).

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/.

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.

Click Next.

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.

A data synchronization schedule is created automatically. More information on this topic can be found here.
Click Create to complete the configuration.

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 Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| country | NULLABLE | STRING |
| device | NULLABLE | STRING |
| site_url | NULLABLE | STRING |
| search_type | NULLABLE | STRING |
| clicks | NULLABLE | INTEGER |
| impressions | NULLABLE | INTEGER |
| ctr | NULLABLE | FLOAT |
| position | NULLABLE | FLOAT |
Data schema for search_console_analytics
| Field Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| query | NULLABLE | STRING |
| page | NULLABLE | STRING |
| country | NULLABLE | STRING |
| device | NULLABLE | STRING |
| site_url | NULLABLE | STRING |
| search_type | NULLABLE | STRING |
| is_anonymized_query | NULLABLE | BOOLEAN |
| is_anonymized_discover | NULLABLE | BOOLEAN |
| clicks | NULLABLE | INTEGER |
| impressions | NULLABLE | INTEGER |
| position | NULLABLE | FLOAT |
Data schema for search_console_queries
| Field Name | Mode | Type |
|---|---|---|
| date | NULLABLE | DATE |
| query | NULLABLE | STRING |
| country | NULLABLE | STRING |
| device | NULLABLE | STRING |
| site_url | NULLABLE | STRING |
| search_type | NULLABLE | STRING |
| is_anonymized_query | NULLABLE | BOOLEAN |
| is_anonymized_discover | NULLABLE | BOOLEAN |
| clicks | NULLABLE | INTEGER |
| impressions | NULLABLE | INTEGER |
| position | NULLABLE | FLOAT |
