EndPoint Query Google Ads data (Using GAQL - Google Ads Query Language)

Name

get_query_result

Description

Get Google Ads report data using GAQL (Google Ads Query Language) SQL query. Use Query builder from this link to select fields, segments, and metrics https://developers.google.com/google-ads/api/fields/v18/overview_query_builder (Click on Resource type you like to query) for SELECT, FROM and ORDER BY

Parameters

Parameter

Label

Required

Options

Description

Query

Enter Query (i.e. GAQL sql)

YES

Filter

Option	Value
$.results[*]	$.results[*]
$.results[*].campaign	$.results[*].campaign
$.results[*].customer	$.results[*].customer
$.results[*].anything_here	$.results[*].anything_here

CustomerId

CustomerId (without dash e.g. 2125557752)

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

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

Query Google Ads data (Using GAQL - Google Ads Query Language) using API Source

Google Ads

Query Google Ads data (Using GAQL - Google Ads Query Language)

SSIS API Source - Read from table or endpoint

Query Google Ads data (Using GAQL - Google Ads Query Language) using API Destination

Google Ads

Query Google Ads data (Using GAQL - Google Ads Query Language)

SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Query Google Ads data using GAQL (Google Ads Query Language)

--Use Query Builder here https://developers.google.com/google-ads/api/fields/v18/overview_query_builder
--Paste Generated query in Query='here'				

SELECT 
	[campaign.id] as id, 
	[campaign.name] as name, 
	[metrics.impressions] as impressions, 
	[metrics.clicks] as clicks, 
	[metrics.costMicros] / 1000000 as cost --//API returns Unit in Micro so to get actual value divide by 1000000
FROM get_query_result
WITH(
	  Filter='$.results[*]'
	, Query='SELECT  campaign.id, campaign.name, metrics.impressions, metrics.clicks, metrics.cost_micros 
FROM campaign 
WHERE campaign.status = ''ENABLED''
 AND metrics.impressions > 10
ORDER BY campaign.id
--LIMIT 1000
'
	--Set different Customer Id here if not supplied in connection settings
	--, CustomerId='1112223333'
)

SQL Server

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

Query Google Ads data using GAQL (Google Ads Query Language)

DECLARE @MyQuery NVARCHAR(MAX) = '--Use Query Builder here https://developers.google.com/google-ads/api/fields/v18/overview_query_builder
--Paste Generated query in Query=''here''				

SELECT 
	[campaign.id] as id, 
	[campaign.name] as name, 
	[metrics.impressions] as impressions, 
	[metrics.clicks] as clicks, 
	[metrics.costMicros] / 1000000 as cost --//API returns Unit in Micro so to get actual value divide by 1000000
FROM get_query_result
WITH(
	  Filter=''$.results[*]''
	, Query=''SELECT  campaign.id, campaign.name, metrics.impressions, metrics.clicks, metrics.cost_micros 
FROM campaign 
WHERE campaign.status = ''''ENABLED''''
 AND metrics.impressions > 10
ORDER BY campaign.id
--LIMIT 1000
''
	--Set different Customer Id here if not supplied in connection settings
	--, CustomerId=''1112223333''
)';

EXEC (@MyQuery) AT [LINKED_SERVER_TO_GOOGLE_ADS_IN_DATA_GATEWAY];