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

Label

Required

Options

Description

SiteUrl

YES

StartDate

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

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)

EndDate

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

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	Raw
Key1	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
Key2	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
Key3	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
Key4	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
Key5	`DT_WSTR`	`nvarchar(1000)`	1000	`False`
Clicks	`DT_I8`	`bigint`		`False`
Impressions	`DT_I8`	`bigint`		`False`
Ctr	`DT_R8`	`float`		`False`
Position	`DT_R8`	`float`		`False`

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	Raw	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 component to read data or in API Destination component to read/write data:

Get Organic Search Report using API Source

Google Search Console

Get Organic Search Report

SSIS API Source - Read from table or endpoint

Get Organic Search Report using API Destination

Google Search Console

Get Organic Search Report

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];