Get resource data (e.g. ad_group, campaign, keyword_view)
Returns data for any resource. Use get_resource_data with WITH(Resource='...') (e.g. Campaign, ad_group, keyword_view). Optionally set Attributes, Metrics, Segments, Where, OrderBy. Alternatively use the resource name in the FROM clause (see comments in code); list resources with SELECT * FROM get_resources.
Standard SQL query example
This is the base query accepted by the connector. To execute it in SQL Server, you have to pass it to the Data Gateway via a Linked Server. See how to accomplish this using the examples below.
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) - We recommend get_resource_data if possible.
Step-1: Find out all resources 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
*/Using OPENQUERY in SQL Server
SELECT * FROM OPENQUERY([LS_TO_GOOGLE_ADS_IN_GATEWAY], '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) - We recommend get_resource_data if possible.
Step-1: Find out all resources 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
*/')Using EXEC in SQL Server (handling larger SQL text)
The major drawback of OPENQUERY is its inability to incorporate variables within SQL statements.
This often leads to the use of cumbersome dynamic SQL (with numerous ticks and escape characters).
Fortunately, starting with SQL 2005 and onwards, you can utilize the EXEC (your_sql) AT [LS_TO_GOOGLE_ADS_IN_GATEWAY] syntax.
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) - We recommend get_resource_data if possible.
Step-1: Find out all resources 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]