Reference

Endpoint Get Organic Search Report

Name

get_report

Description

Get Google organic search report for selected date range, dimensions and metrics (KPI) using Google Search Console API

Parameters

Parameter Required Options
Name: SiteUrl

Label: SiteUrl

 YES
Name: StartDate

Label: StartDate

The date for which to retrieve the report in YYYY-MM-DD format. Supported functions now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start) 		YES
Option Value
2023-12-31 2023-12-31
today today
yesterday yesterday
yearstart-10y yearstart-10y
monthstart monthstart
monthend monthend
yearstart yearstart
yearend yearend
weekstart weekstart
weekend weekend
yearstart-1y yearstart-1y
yearend-1y yearend-1y
monthstart-1d monthstart-1d
monthend+1d monthend+1d
monthend+1d-1y monthend+1d-1y
Name: EndDate

Label: EndDate

The date for which to retrieve the report in YYYY-MM-DD format. Supported functions now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start) 		YES
Option Value
2023-12-31 2023-12-31
today today
yesterday yesterday
yearstart-10y yearstart-10y
monthstart monthstart
monthend monthend
yearstart yearstart
yearend yearend
weekstart weekstart
weekend weekend
yearstart-1y yearstart-1y
yearend-1y yearend-1y
monthstart-1d monthstart-1d
monthend+1d monthend+1d
monthend+1d-1y monthend+1d-1y
Name: Dimensions

Label: Dimensions

 YES
Option Value
date date
page page
query query
country country
device device
searchAppearance searchAppearance

Output Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
Key1 DT_WSTR nvarchar(1000) 1000
Key2 DT_WSTR nvarchar(1000) 1000
Key3 DT_WSTR nvarchar(1000) 1000
Key4 DT_WSTR nvarchar(1000) 1000
Key5 DT_WSTR nvarchar(1000) 1000
Clicks DT_I8 bigint
Impressions DT_I8 bigint
Ctr DT_R8 float
Position DT_R8 float
If the column you are looking for is missing, consider customizing Google Search Console Connector.

Input Columns

Label Data Type (SSIS) Data Type (SQL) Length Description
There are no Static columns defined for this endpoint. This endpoint detects columns dynamically at runtime.

Examples

SSIS

Use Google Search Console Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Google Search Console
Read Google Search Console report (Organic Traffic) about site performance, ranking, clicks, ctr and impressions by date, page, country, query, country, device and searchAppearance.
Google Search Console
Get Organic Search Report
Required Parameters
SiteUrl Fill-in the parameter...
StartDate Fill-in the parameter...
EndDate Fill-in the parameter...
Dimensions Fill-in the parameter...
Optional Parameters
PagingMode ByPostData
PagingByUrlAttributeName {%offset%}
PagingIncrementBy 2000
EnableArrayFlattening True
PagingByUrlCurrentPage 0
SSIS API Source - Read from table or endpoint

API Destination

API Destination - Google Search Console
Read Google Search Console report (Organic Traffic) about site performance, ranking, clicks, ctr and impressions by date, page, country, query, country, device and searchAppearance.
Google Search Console
Get Organic Search Report
Required Parameters
SiteUrl Fill-in the parameter...
StartDate Fill-in the parameter...
EndDate Fill-in the parameter...
Dimensions Fill-in the parameter...
Optional Parameters
PagingMode ByPostData
PagingByUrlAttributeName {%offset%}
PagingIncrementBy 2000
EnableArrayFlattening True
PagingByUrlCurrentPage 0
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Get google organic search report for pages (Impressions, Clicks, Position, ctr)

Get google organic search report for pages by date, show impressions, clicks, position and ctr 

SELECT * FROM get_report
WITH(
   SiteUrl='https://mycompany.com'
  --You can use static date or date functions in StartDate and EndDate for dynamic reporting. 
  --Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
  ,StartDate='monthstart' --or static 2024-12-31
  ,EndDate='today'  --or static 2024-12-31
  ,Dimensions='date~page' --multiple dimensions can be separated using ~ (e.g. date~page), Allowed dimensions are date;page;query;country;device;searchAppearance
)

Get google organic search report by date (Impressions, Clicks, Position, ctr)

Get google organic search report for all pages by date, show impressions, clicks, position and ctr 

SELECT * FROM get_report
WITH(
   SiteUrl='https://mycompany.com'
  --You can use static date or date functions in StartDate and EndDate for dynamic reporting. 
  --Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
  ,StartDate='monthstart' --or static 2024-12-31
  ,EndDate='today'  --or static 2024-12-31
  ,Dimensions='date' --multiple dimensions can be separated using ~ (e.g. date~page), Allowed dimensions are date;page;query;country;device;searchAppearance
)

Get google organic search keywords report (Impressions, Clicks, Position, ctr)

Get google organic search keywords report for all pages group by date, show impressions, clicks, position and ctr 

SELECT * FROM get_report
WITH(
   SiteUrl='https://mycompany.com'
  --You can use static date or date functions in StartDate and EndDate for dynamic reporting. 
  --Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
  ,StartDate='monthstart' --or static 2024-12-31
  ,EndDate='today'  --or static 2024-12-31
  ,Dimensions='query' --multiple dimensions can be separated using ~ (e.g. date~page), Allowed dimensions are date;page;query;country;device;searchAppearance
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Get google organic search report for pages (Impressions, Clicks, Position, ctr)

Get google organic search report for pages by date, show impressions, clicks, position and ctr 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_report
WITH(
   SiteUrl=''https://mycompany.com''
  --You can use static date or date functions in StartDate and EndDate for dynamic reporting. 
  --Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
  ,StartDate=''monthstart'' --or static 2024-12-31
  ,EndDate=''today''  --or static 2024-12-31
  ,Dimensions=''date~page'' --multiple dimensions can be separated using ~ (e.g. date~page), Allowed dimensions are date;page;query;country;device;searchAppearance
)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SEARCH_CONSOLE_IN_GATEWAY];

Get google organic search report by date (Impressions, Clicks, Position, ctr)

Get google organic search report for all pages by date, show impressions, clicks, position and ctr 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_report
WITH(
   SiteUrl=''https://mycompany.com''
  --You can use static date or date functions in StartDate and EndDate for dynamic reporting. 
  --Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
  ,StartDate=''monthstart'' --or static 2024-12-31
  ,EndDate=''today''  --or static 2024-12-31
  ,Dimensions=''date'' --multiple dimensions can be separated using ~ (e.g. date~page), Allowed dimensions are date;page;query;country;device;searchAppearance
)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SEARCH_CONSOLE_IN_GATEWAY];

Get google organic search keywords report (Impressions, Clicks, Position, ctr)

Get google organic search keywords report for all pages group by date, show impressions, clicks, position and ctr 

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_report
WITH(
   SiteUrl=''https://mycompany.com''
  --You can use static date or date functions in StartDate and EndDate for dynamic reporting. 
  --Supported functions are now|today|yesterday|weekstart|weekend|monthstart|monthend|yearstart|yearend and supported intervals for add/subtract are ms|s|sec|min|h|hour|y|year|d|day|m|month|y|year. Example monthstart-3d (subtract 3days from month start)
  ,StartDate=''monthstart'' --or static 2024-12-31
  ,EndDate=''today''  --or static 2024-12-31
  ,Dimensions=''query'' --multiple dimensions can be separated using ~ (e.g. date~page), Allowed dimensions are date;page;query;country;device;searchAppearance
)';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_SEARCH_CONSOLE_IN_GATEWAY];