Reference

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 Required Options
Name: Query

Label: Enter Query (i.e. GAQL sql)

 YES
Name: Filter

Label: Filter
Option Value
$.results[*] $.results[*]
$.results[*].campaign $.results[*].campaign
$.results[*].customer $.results[*].customer
$.results[*].anything_here $.results[*].anything_here
Name: CustomerId

Label: CustomerId (without dash e.g. 2125557752)

Login to your Google Ads account and see top-right corner for your CustomerId

Output 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.
If the column you are looking for is missing, consider customizing Google Ads 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 Ads Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Google Ads
Read and write Google Ads data effortlessly. Integrate, manage, and automate campaigns, ad groups, keywords, and performance — almost no coding required.
Google Ads
Query Google Ads data (Using GAQL - Google Ads Query Language)
Required Parameters
Enter Query (i.e. GAQL sql) Fill-in the parameter...
Optional Parameters
Filter $.results[*]
SSIS API Source - Read from table or endpoint

API Destination

API Destination - Google Ads
Read and write Google Ads data effortlessly. Integrate, manage, and automate campaigns, ad groups, keywords, and performance — almost no coding required.
Google Ads
Query Google Ads data (Using GAQL - Google Ads Query Language)
Required Parameters
Enter Query (i.e. GAQL sql) Fill-in the parameter...
Optional Parameters
Filter $.results[*]
SSIS API Destination - Access table or endpoint

ODBC application

Use these SQL queries in your ODBC application data source:

Query data using GAQL 

--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 data using GAQL 

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