Google Search Console connector reference

Important

This feature is in Beta. To use it, a workspace admin must turn on Lakeflow Connect for Google Search Console from the Previews page. See Manage Azure Databricks previews.

The managed Google Search Console connector in Lakeflow Connect supports 15 source tables in the default schema: 2 site tables, 12 prebuilt report tables, and a custom_report table. The site tables sync with a full refresh, and the report tables sync incrementally. The cursor field is date for daily report tables and custom_report, and hour for hourly report tables.

Supported source tables

The Google Search Console connector supports the following source tables, all under the default source schema. The site tables contain metadata about your verified Google Search Console properties. Each report table runs a search performance report through the Search Analytics API and groups the results by a fixed set of dimensions. The prebuilt report tables are organized by time granularity:

For definitions of each dimension and metric, see Search Analytics: query in the Google documentation.

Site tables

These tables contain metadata about your verified Google Search Console properties. They do not contain search performance metrics and are fully refreshed on every pipeline run.

Source table Primary key Description Sync mode
sites site_url Verified site properties registered in Google Search Console, including permission level. Full refresh only
sitemaps site_url, path Sitemaps submitted to Google Search Console for each verified property. Full refresh only

Prebuilt report tables

The prebuilt report tables contain click, impression, click-through rate (CTR), and position data from the Google Search Console Search Analytics API. All prebuilt report tables sync incrementally.

Each table returns the same four metrics (clicks, impressions, ctr, and position), but differs in which dimensions the data is aggregated on and which aggregation type Google applies before returning results. See Aggregation type.

Daily report tables

These tables aggregate search performance data by calendar day. The cursor field is date.

Source table Dimensions Aggregation type Search types Primary key Cursor field
search_analytics_all_fields date, country, device, page, query auto web, news, image, video site_url, date, country, device, page, query, search_type date
search_analytics_by_country date, country auto web, news, image, video, discover, googleNews site_url, date, country, search_type date
search_analytics_by_date date auto web, news, image, video, discover, googleNews site_url, date, search_type date
search_analytics_by_device date, device auto web, news, image, video, googleNews site_url, date, device, search_type date
search_analytics_by_page date, page auto web, news, image, video, discover, googleNews site_url, date, page, search_type date
search_analytics_by_query date, query auto web, news, image, video site_url, date, query, search_type date
search_analytics_page_report date, country, device, page auto web, news, image, video, googleNews site_url, date, country, device, page, search_type date
search_analytics_site_report_by_page date, country, device byPage web, news, image, video, googleNews site_url, date, country, device, search_type date
search_analytics_site_report_by_site date, country, device byProperty web, news, image, video site_url, date, country, device, search_type date

Hourly report tables

These tables aggregate search performance data by hour. They use only the hourly_all data state and are not affected by the data_state connector option. The cursor field is hour. Available search types are limited to web and googleNews.

Source table Dimensions Aggregation type Search types Primary key Cursor field
hourly_search_analytics_page_report hour, country, device, page auto web, googleNews site_url, hour, country, device, page, search_type hour
hourly_search_analytics_site_report_by_page hour, country, device byPage web, googleNews site_url, hour, country, device, search_type hour
hourly_search_analytics_site_report_by_site hour, country, device byProperty web, googleNews site_url, hour, country, device, search_type hour

Custom report table

The custom_report table runs a daily report with the dimensions, search types, and aggregation type that you specify in its connector options. See Custom reports.

Source table Dimensions Aggregation type Search types Primary key Cursor field
custom_report date, plus the values of dimensions The value of aggregation_type The values of search_types site_url, date, the values of dimensions, and search_type date

Connector options

The Google Search Console connector supports the following configuration options. See Examples for usage.

Option Type Scope Required Applies to Description
site_urls JSON array of strings Pipeline Yes All tables One or more verified Google Search Console property URLs to ingest. Each URL must be a property you have verified in Google Search Console (for example, https://example.com/ or sc-domain:example.com).
start_date String (yyyy-MM-dd) Pipeline No All incremental tables (search_analytics_*, hourly_search_analytics_*, custom_report) Earliest date from which to ingest data, in yyyy-MM-dd format. Defaults to 500 days before the first sync. Has no effect on sites or sitemaps (full refresh).
data_state String Table No Daily search performance tables (search_analytics_*) and custom_report Data freshness for daily search performance tables and custom_report. all (default) includes finalized and fresh data. final includes finalized data only. Set it only on search_analytics_* and custom_report objects. The sites, sitemaps, and hourly_search_analytics_* tables do not accept this option.
dimensions JSON array of strings Table Yes custom_report only The dimensions to group the report by, for example '["country", "device"]'. Allowed values are date, country, device, page, and query. The connector includes date even if you don't list it.
search_types JSON array of strings Table Yes custom_report only The search types to run the report for, for example '["web", "image"]'. Allowed values are web, image, video, news, discover, and googleNews. The connector runs the report one time for each search type and writes the search type to the search_type column.
aggregation_type String Table No custom_report only How Google Search Console aggregates metrics. One of auto (default), byPage, or byProperty. See Aggregation type.

Custom reports

Use the custom_report table to ingest a combination of dimensions, search types, and aggregation type that the prebuilt tables don't cover. Each custom_report object in a pipeline runs one report against every site URL in site_urls.

The connector requests the dimensions in dimensions, plus date if dimensions doesn't already include it. The report aggregates by day, so the custom_report table doesn't support the hour dimension. To ingest hourly data, use the hourly report tables.

To ingest more than one custom report, add a custom_report object for each report, each with a different destination_table. See Ingest a custom report.

Aggregation type

The aggregation_type option sets how Google Search Console aggregates the custom report's metrics. Each prebuilt report table uses a fixed aggregation type, listed in its table row.

aggregation_type Aggregation
auto Google Search Console chooses the aggregation. This is the default.
byPage Aggregates metrics by page.
byProperty Aggregates metrics by property. Doesn't support the page dimension or the discover and googleNews search types.

Because each aggregation type counts clicks differently, click totals across tables with different aggregation types don't match each other or the Google Search Console UI for the same date range. For details, see aggregationType in the Google documentation.

Destination table schemas

Every report table, including custom_report, includes the following columns that the connector adds:

  • site_url (STRING): The Google Search Console property URL that the row came from.
  • search_type (STRING): The search type that the row came from.
  • ingestion_timestamp (TIMESTAMP): The time that the connector ingested the row.

sites

Primary key: site_url Sync mode: Full refresh only

Field Data type
site_url STRING
permission_level STRING

sitemaps

Primary key: site_url, path Sync mode: Full refresh only

Field Data type
site_url STRING
path STRING
last_submitted STRING
is_pending BOOLEAN
is_sitemaps_index BOOLEAN
type STRING
last_downloaded STRING
warnings BIGINT
errors BIGINT
contents ARRAY<STRUCT<type: STRING, submitted: BIGINT, indexed: BIGINT>>

search_analytics_all_fields

Dimensions: date, country, device, page, query Aggregation type: auto Search types: web, news, image, video Primary key: site_url, date, country, device, page, query, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
country STRING
device STRING
page STRING
query STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_by_country

Dimensions: date, country Aggregation type: auto Search types: web, news, image, video, discover, googleNews Primary key: site_url, date, country, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
country STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_by_date

Dimensions: date Aggregation type: auto Search types: web, news, image, video, discover, googleNews Primary key: site_url, date, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_by_device

Dimensions: date, device Aggregation type: auto Search types: web, news, image, video, googleNews Primary key: site_url, date, device, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
device STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_by_page

Dimensions: date, page Aggregation type: auto Search types: web, news, image, video, discover, googleNews Primary key: site_url, date, page, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
page STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_by_query

Dimensions: date, query Aggregation type: auto Search types: web, news, image, video Primary key: site_url, date, query, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
query STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_page_report

Dimensions: date, country, device, page Aggregation type: auto Search types: web, news, image, video, googleNews Primary key: site_url, date, country, device, page, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
country STRING
device STRING
page STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_site_report_by_page

Dimensions: date, country, device Aggregation type: byPage Search types: web, news, image, video, googleNews Primary key: site_url, date, country, device, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
country STRING
device STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

search_analytics_site_report_by_site

Dimensions: date, country, device Aggregation type: byProperty Search types: web, news, image, video Primary key: site_url, date, country, device, search_type Cursor field: date

Field Data type
site_url STRING
search_type STRING
date STRING
country STRING
device STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

hourly_search_analytics_page_report

Dimensions: hour, country, device, page Aggregation type: auto Search types: web, googleNews Primary key: site_url, hour, country, device, page, search_type Cursor field: hour

Field Data type
site_url STRING
search_type STRING
hour STRING
country STRING
device STRING
page STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

hourly_search_analytics_site_report_by_page

Dimensions: hour, country, device Aggregation type: byPage Search types: web, googleNews Primary key: site_url, hour, country, device, search_type Cursor field: hour

Field Data type
site_url STRING
search_type STRING
hour STRING
country STRING
device STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

hourly_search_analytics_site_report_by_site

Dimensions: hour, country, device Aggregation type: byProperty Search types: web, googleNews Primary key: site_url, hour, country, device, search_type Cursor field: hour

Field Data type
site_url STRING
search_type STRING
hour STRING
country STRING
device STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

custom_report

Dimensions: date, plus the values of dimensions Aggregation type: The value of aggregation_type Search types: The values of search_types Primary key: site_url, date, the values of dimensions, and search_type Cursor field: date

The table includes the following columns. It also includes a STRING column for each of country, device, page, and query that you list in dimensions.

Field Data type
site_url STRING
search_type STRING
date STRING
clicks DOUBLE
impressions DOUBLE
ctr DOUBLE
position DOUBLE
ingestion_timestamp TIMESTAMP

Required OAuth permissions

The Google Search Console connector requests the https://www.googleapis.com/auth/webmasters.readonly scope. This scope grants read-only access to all Google Search Console data for properties the authenticated identity has access to.

Permission Required for
https://www.googleapis.com/auth/webmasters.readonly All tables
  • Last updated on