Get Resource Data (Query Attributes, Segments, Metrics)

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Google Ads Connector in API Source component to read data or in API Destination component to read/write data:

Read from <<[Dynamic Table]|~|googleAdsFields/|~|,FUN_REPLACE>> table using API Destination

This Endpoint belongs to <<[Dynamic Table]|~|googleAdsFields/|~|,FUN_REPLACE>> table, therefore you cannot work with it directly. Use this table and table-operation pair instead:

Google Ads

<<[Dynamic Table]|~|googleAdsFields/|~|,FUN_REPLACE>>

Select

ODBC application

Use these SQL queries in your ODBC application data source:

Get resource data (e.g. ad_group, ad_group_ad, keyword_view

Get data for any resource

SELECT * FROM get_resource_data
WITH (
Resource='Campaign'

--//Optional Parameters 
--, Attributes='' --Keep blank to output all attributes
--OR--
--, Attributes='campaign.id~campaign.name~campaign.end_date' 

--, Metrics='metrics.clicks~metrics.impressions'
--, Segments='segments.year~segments.month'
--//must supply where clause if certain segments used (e.g. date, year, month)
--, Where='segments.date between ''<<yearstart,FUN_TO_DATE>>'' AND ''<<today-1d,FUN_TO_DATE>>'' '
--, OrderBy='segments.year DESC, segments.month ASC'
)

/*
--OR-- Simple Mode (Query by Resource Name in FROM clause).

Step-1: Findout all resource you can query using  
select * from get_resources

Step-2: Use resource name like below (in FROM)
Example resources (If data is missing it may throw error)

select * from ad_group
select * from ad_group_ad
select * from ad_group_ad_asset_combination_view
select * from ad_group_ad_asset_view
select * from ad_group_asset
select * from ad_group_audience_view
select * from ad_schedule_view
select * from age_range_view
select * from android_privacy_shared_key_google_ad_group
select * from android_privacy_shared_key_google_campaign
select * from android_privacy_shared_key_google_network_type
select * from asset
select * from asset_field_type_view
select * from asset_group
select * from asset_group_asset
select * from asset_group_product_group_view
select * from asset_group_top_combination_view
select * from asset_set_asset
select * from asset_set_type_view
select * from bidding_strategy
select * from campaign
select * from campaign_aggregate_asset_view
select * from campaign_asset
select * from campaign_audience_view
select * from campaign_budget
select * from campaign_group
select * from campaign_search_term_insight
select * from channel_aggregate_asset_view
select * from click_view
select * from content_criterion_view
select * from conversion_action
select * from customer
select * from customer_asset
select * from customer_search_term_insight
select * from detail_placement_view
select * from display_keyword_view
select * from distance_view
select * from dynamic_search_ads_search_term_view
select * from expanded_landing_page_view
select * from gender_view
select * from geographic_view
select * from group_placement_view
select * from hotel_group_view
select * from hotel_performance_view
select * from hotel_reconciliation
select * from income_range_view
select * from keyword_view
select * from landing_page_view
select * from location_view
select * from managed_placement_view
select * from paid_organic_search_term_view
select * from parental_status_view
select * from per_store_view
select * from performance_max_placement_view
select * from product_group_view
select * from search_term_view
select * from shared_set
select * from shopping_performance_view
select * from shopping_product
select * from smart_campaign_search_term_view
select * from topic_view
select * from travel_activity_group_view
select * from travel_activity_performance_view
select * from user_location_view
select * from video
select * from webpage_view
*/

SQL Server

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

Get resource data (e.g. ad_group, ad_group_ad, keyword_view

Get data for any resource

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT * FROM get_resource_data
WITH (
Resource=''Campaign''

--//Optional Parameters 
--, Attributes='''' --Keep blank to output all attributes
--OR--
--, Attributes=''campaign.id~campaign.name~campaign.end_date'' 

--, Metrics=''metrics.clicks~metrics.impressions''
--, Segments=''segments.year~segments.month''
--//must supply where clause if certain segments used (e.g. date, year, month)
--, Where=''segments.date between ''''<<yearstart,FUN_TO_DATE>>'''' AND ''''<<today-1d,FUN_TO_DATE>>'''' ''
--, OrderBy=''segments.year DESC, segments.month ASC''
)

/*
--OR-- Simple Mode (Query by Resource Name in FROM clause).

Step-1: Findout all resource you can query using  
select * from get_resources

Step-2: Use resource name like below (in FROM)
Example resources (If data is missing it may throw error)

select * from ad_group
select * from ad_group_ad
select * from ad_group_ad_asset_combination_view
select * from ad_group_ad_asset_view
select * from ad_group_asset
select * from ad_group_audience_view
select * from ad_schedule_view
select * from age_range_view
select * from android_privacy_shared_key_google_ad_group
select * from android_privacy_shared_key_google_campaign
select * from android_privacy_shared_key_google_network_type
select * from asset
select * from asset_field_type_view
select * from asset_group
select * from asset_group_asset
select * from asset_group_product_group_view
select * from asset_group_top_combination_view
select * from asset_set_asset
select * from asset_set_type_view
select * from bidding_strategy
select * from campaign
select * from campaign_aggregate_asset_view
select * from campaign_asset
select * from campaign_audience_view
select * from campaign_budget
select * from campaign_group
select * from campaign_search_term_insight
select * from channel_aggregate_asset_view
select * from click_view
select * from content_criterion_view
select * from conversion_action
select * from customer
select * from customer_asset
select * from customer_search_term_insight
select * from detail_placement_view
select * from display_keyword_view
select * from distance_view
select * from dynamic_search_ads_search_term_view
select * from expanded_landing_page_view
select * from gender_view
select * from geographic_view
select * from group_placement_view
select * from hotel_group_view
select * from hotel_performance_view
select * from hotel_reconciliation
select * from income_range_view
select * from keyword_view
select * from landing_page_view
select * from location_view
select * from managed_placement_view
select * from paid_organic_search_term_view
select * from parental_status_view
select * from per_store_view
select * from performance_max_placement_view
select * from product_group_view
select * from search_term_view
select * from shared_set
select * from shopping_performance_view
select * from shopping_product
select * from smart_campaign_search_term_view
select * from topic_view
select * from travel_activity_group_view
select * from travel_activity_performance_view
select * from user_location_view
select * from video
select * from webpage_view
*/';

EXEC (@MyQuery) AT [LS_TO_GOOGLE_ADS_IN_GATEWAY];