This is legacy documentation, and may not be complete. To see the latest documentation, if you are a marketer, refer to the Marketers site. If you are a measurement partner, refer to the Measurement Partners site.

Sample queries in Ads Data Hub

  • The webpage provides sample SQL queries for analyzing Campaign Manager 360, Dynamic Data, Ad Formats, Google Ads, YouTube Ad Pods, YouTube Reserve, and General use cases within Ads Data Hub.

  • Sample queries are available via query templates in the UI and require working knowledge of SQL and BigQuery.

  • Campaign Manager 360 queries cover matching Floodlight variables, analyzing impression delivery, and examining unique cookie counts and frequency by various dimensions.

  • Google Ads queries include analyzing mobile app impressions, demographic delivery, viewability, time zone settings, and inventory type.

  • YouTube queries focus on ad pod performance, viewability metrics for Display and Video 360, and YouTube Reserve impression delivery and ad skips.

These sample queries assume working knowledge of SQL and BigQuery. Learn more about SQL in BigQuery.

Campaign Manager 360 Data Transfer queries

Match Floodlight variables with temp tables

Generate a match between user_id and custom Floodlight variables in the activity table. This can then be used to join first-party data with Campaign Manager 360 data.


/* Creating the match temp table. This can be a separate query and the
temporary table will persist for 72 hours. */
CREATETABLE
temp_tableAS(
SELECT
user_id,
REGEXP_EXTRACT(event.other_data,'u1=([^;]*)')ASu1_val
FROM
adh.cm_dt_activities_attributed
GROUPBY
1,
2)
/* Matching to Campaign Manager 360 impression data */
SELECT
imp.event.campaign_id,
temp.u1_val,
COUNT(*)AScnt
FROM
adh.cm_dt_impressionsASimp
JOIN
tmp.temp_tableAStempUSING(user_id)
GROUPBY
1,
2

Impression delivery

This example is good for impression management, and shows how to find the number of impressions that were served beyond frequency caps or if certain prospects were underexposed to ads. Use this knowledge to optimize your sites and tactics to get the right number of impressions in front of a chosen audience.

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345] */
WITHfiltered_uniquesAS(
SELECT
user_id,
COUNT(event.placement_id)ASfrequency
FROMadh.cm_dt_impressions
WHEREuser_id!='0'
ANDevent.advertiser_idINUNNEST(@advertiser_ids)
ANDevent.campaign_idINUNNEST(@campaign_ids)
ANDevent.country_domain_name='US'
GROUPBYuser_id
)
SELECT
frequency,
COUNT(*)ASuniques
FROMfiltered_uniques
GROUPBYfrequency
ORDERBYfrequency
;

This example helps identify tactics and ad formats that lead to increases or decreases in unique cookie count or frequency.

/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */
SELECT
COUNT(DISTINCTuser_id)AStotal_users,
COUNT(DISTINCTevent.site_id)AStotal_sites,
COUNT(DISTINCTdevice_id_md5)AStotal_devices,
COUNT(event.placement_id)ASimpressions
FROMadh.cm_dt_impressions
WHEREuser_id!='0'
ANDevent.advertiser_idINUNNEST(@advertiser_ids)
ANDevent.campaign_idINUNNEST(@campaign_ids)
ANDevent.placement_idINUNNEST(@placement_ids)
ANDevent.country_domain_name='US'
;

You can also include site or placement IDs in the WHERE clause to narrow your query.

This example joins the cm_dt_impressions table and the cm_dt_state metadata table to show total impressions, cookie counts per state, and average impression by user, grouped by North America geographic state or province.


WITHimpression_statsAS(
SELECT
event.country_domain_nameAScountry,
CONCAT(event.country_domain_name,'-',event.state)ASstate,
COUNT(DISTINCTuser_id)ASusers,
COUNT(*)ASimpressions
FROMadh.cm_dt_impressions
WHEREevent.country_domain_name='US'
ORevent.country_domain_name='CA'
GROUPBY1,2
)
SELECT
country,
IFNULL(state_name,state)ASstate_name,
users,
impressions,
FORMAT(
'%0.2f',
IF(
IFNULL(impressions,0)=0,
0,
impressions/users
)
)ASavg_imps_per_user
FROMimpression_stats
LEFTJOINadh.cm_dt_stateUSING(state)
;

Display and Video 360 audiences

This example shows how to analyze Display and Video 360 audiences. Learn which audiences impressions are reaching, and determine if some audiences perform better than others. This knowledge can help balance unique cookie count (putting ads in front of a lot of users) and quality (narrow targeting and viewable impressions), depending on your goals.

/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */
WITHfiltered_impressionsAS(
SELECT
event.event_timeasdate,
CASE
WHEN(event.browser_enumIN('29','30','31')
ORevent.os_idIN
(501012,501013,501017,501018,
501019,501020,501021,501022,
501023,501024,501025,501027))
THEN'Mobile'
ELSE'Desktop'
ENDASdevice,
event.dv360_matching_targeted_segments,
event.active_view_viewable_impressions,
event.active_view_measurable_impressions,
user_id
FROMadh.cm_dt_impressions
WHEREevent.dv360_matching_targeted_segments!=''
ANDevent.advertiser_idinUNNEST(@advertiser_ids)
ANDevent.campaign_idINUNNEST(@campaign_ids)
ANDevent.dv360_country_code='US'
)
SELECT
audience_id,
device,
COUNT(*)ASimpressions,
COUNT(DISTINCTuser_id)ASuniques,
ROUND(COUNT(*)/COUNT(DISTINCTuser_id),1)ASfrequency,
SUM(active_view_viewable_impressions)ASviewable_impressions,
SUM(active_view_measurable_impressions)ASmeasurable_impressions
FROMfiltered_impressions
JOINUNNEST(SPLIT(dv360_matching_targeted_segments,' '))ASaudience_id
GROUPBY1,2
;

Viewability

These example show how to measure Active View Plus viewability metrics.


WITHTAS(
SELECTcm_dt_impressions.event.impression_idASImpression,
cm_dt_impressions.event.active_view_measurable_impressionsASAV_Measurable,
SUM(cm_dt_active_view_plus.event.active_view_plus_measurable_count)ASAVP_Measurable
FROMadh.cm_dt_impressions
FULLJOINadh.cm_dt_active_view_plus
ON(cm_dt_impressions.event.impression_id=
cm_dt_active_view_plus.event.impression_id)
GROUPBYImpression,AV_Measurable
)
SELECTCOUNT(Impression),SUM(AV_Measurable),SUM(AVP_Measurable)
FROMT
;


WITHRawAS(
SELECT
event.ad_idASAd_Id,
SUM(event.active_view_plus_measurable_count)ASavp_total,
SUM(event.active_view_first_quartile_viewable_impressions)ASavp_1st_quartile,
SUM(event.active_view_midpoint_viewable_impressions)ASavp_2nd_quartile,
SUM(event.active_view_third_quartile_viewable_impressions)ASavp_3rd_quartile,
SUM(event.active_view_complete_viewable_impressions)ASavp_complete
FROM
adh.cm_dt_active_view_plus
GROUPBY
1
)
SELECT
Ad_Id,
avp_1st_quartile/avp_totalASViewable_Rate_1st_Quartile,
avp_2nd_quartile/avp_totalASViewable_Rate_2nd_Quartile,
avp_3rd_quartile/avp_totalASViewable_Rate_3rd_Quartile,
avp_complete/avp_totalASViewable_Rate_Completion_Quartile
FROM
Raw
WHERE
avp_total > 0
ORDERBY
Viewable_Rate_1st_QuartileDESC
;

Dynamic data in Campaign Manager 360 Data Transfer

Number of impressions per dynamic profile and feed

SELECT
event.dynamic_profile,
feed_name,
COUNT(*)asimpressions
FROMadh.cm_dt_impressions
JOINUNNEST(event.feed)asfeed_name
GROUPBY1,2;

Number of impressions per dynamic reporting label in feed 1

SELECT
event.feed_reporting_label[SAFE_ORDINAL(1)]feed1_reporting_label,,
COUNT(*)asimpressions
FROMadh.cm_dt_impressions
WHEREevent.feed_reporting_label[SAFE_ORDINAL(1)] <> ""#whereyouhaveatleastonereportinglabelset
GROUPBY1;

Number of impressions where the reporting label = ‘red’ in feed 2

SELECT
event.feed_reporting_label[SAFE_ORDINAL(2)]ASfeed1_reporting_label,
COUNT(*)asimpressions
FROMadh.cm_dt_impressions
WHEREevent.feed_reporting_label[SAFE_ORDINAL(2)]="red"
GROUPBY1;

Number of impressions where reporting dimension_1 = ‘red’ and reporting dimension_2 = ‘car’ in feed 1

SELECT
event.feed_reporting_label[SAFE_ORDINAL(1)]ASfeed1_reporting_label,
event.feed_reporting_dimension1[SAFE_ORDINAL(1)]ASfeed1_reporting_dimension1,
event.feed_reporting_dimension2[SAFE_ORDINAL(1)]ASfeed2_reporting_dimension1,
event.feed_reporting_dimension3[SAFE_ORDINAL(1)]ASfeed3_reporting_dimension1,
event.feed_reporting_dimension4[SAFE_ORDINAL(1)]ASfeed4_reporting_dimension1,
event.feed_reporting_dimension5[SAFE_ORDINAL(1)]ASfeed5_reporting_dimension1,
event.feed_reporting_dimension6[SAFE_ORDINAL(1)]ASfeed6_reporting_dimension1,
COUNT(*)asimpressions
FROMadh.cm_dt_impressions
WHEREevent.feed_reporting_dimension1[SAFE_ORDINAL(1)]="red"
ANDevent.feed_reporting_dimension2[SAFE_ORDINAL(1)]="car"
GROUPBY1,2,3,4,5,6,7;

Ad Formats in Campaign Manager 360 Data Transfer

These examples show how to determine which ad formats are maximizing unique cookie count or frequency of impressions. Use this knowledge to help balance total unique cookie count and user exposure to ads.

Impression delivery

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset.*/
WITHfiltered_uniquesAS(
SELECT
user_id,
CASE
WHENcreative_typeLIKE'%Video%'THEN'Video'
WHENcreative_typeISNULLTHEN'Unknown'
ELSE'Display'
ENDAScreative_format,
COUNT(*)ASimpressions
FROMadh.cm_dt_impressionsimpression
LEFTJOINYOUR_BQ_DATASET.campaignscreative
ONcreative.rendering_id=impression.event.rendering_id
WHEREuser_id!='0'
ANDevent.advertiser_idINUNNEST(@advertiser_ids)
ANDevent.campaign_idINUNNEST(@campaign_ids)
ANDevent.country_domain_name='US'
GROUPBYuser_id,creative_format
)
SELECT
impressionsASfrequency,
creative_format,
COUNT(DISTINCTuser_id)ASuniques,
SUM(impressions)ASimpressions
FROMfiltered_uniques
GROUPBYfrequency,creative_format
ORDERBYfrequency
;

/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345]. YOUR_BQ_DATASET must be
replaced with the actual name of your dataset. */
WITHfiltered_impressionsAS(
SELECT
event.campaign_idAScampaign_id,
event.rendering_idASrendering_id,
user_id
FROMadh.cm_dt_impressions
WHEREuser_id!='0'
ANDevent.advertiser_idINUNNEST(@advertiser_ids)
ANDevent.campaign_idINUNNEST(@campaign_ids)
ANDevent.country_domain_name='US'
)
SELECT
Campaign,
CASE
WHENcreative_typeLIKE'%Video%'THEN'Video'
WHENcreative_typeISNULLTHEN'Unknown'
ELSE'Display'
ENDAScreative_format,
COUNT(DISTINCTuser_id)ASusers,
COUNT(*)ASimpressions
FROMfiltered_impressions
LEFTJOINYOUR_BQ_DATASET.campaignsUSING(campaign_id)
LEFTJOINYOUR_BQ_DATASET.creativesUSING(rendering_id)
GROUPBY1,2
;

Mobile app impressions with _rdid tables

Query 1:


SELECT
campaign_id,
COUNT(*)ASimp,
COUNT(DISTINCTuser_id)ASusers
FROMadh.google_ads_impressions
WHEREis_app_traffic
GROUPBY1
;

Query 2:


SELECT
campaign_id,
COUNT(DISTINCTdevice_id_md5)ASdevice_ids
FROMadh.google_ads_impressions_rdid
GROUPBY1
;

The results can be joined using campaign_id.

Demographic delivery

This example shows how to determine which campaigns are reaching a given demographic.

/* For this query to run, @customer_id
must be replaced with an actual ID. For example [12345] */
WITHimpression_statsAS(
SELECT
campaign_id,
demographics.genderASgender_id,
demographics.age_groupASage_group_id,
COUNT(DISTINCTuser_id)ASusers,
COUNT(*)ASimpressions
FROMadh.google_ads_impressions
WHEREcustomer_id=@customer_id
GROUPBY1,2,3
)
SELECT
campaign_name,
gender_name,
age_group_name,
users,
impressions
FROMimpression_stats
LEFTJOINadh.google_ads_campaignUSING(campaign_id)
LEFTJOINadh.genderUSING(gender_id)
LEFTJOINadh.age_groupUSING(age_group_id)
ORDERBY1,2,3
;

Viewability

For an overview of Viewability with query samples, see Advanced Active View metrics 

SELECT
customer_id,
customer_timezone,
count(1)asimpressions
FROMadh.google_ads_impressionsi
INNERJOINadh.google_ads_customerc
ONc.customer_id=i.customer_id
WHERETIMESTAMP_MICROS(i.query_id.time_usec)>=CAST(DATETIME(@date,c.customer_timezone)ASTIMESTAMP)
ANDTIMESTAMP_MICROS(i.query_id.time_usec) < CAST(DATETIME_ADD(DATETIME(@date,c.customer_timezone),INTERVAL1DAY)ASTIMESTAMP)
GROUPBYcustomer_id,customer_timezone

Inventory type

This sample query demonstrates the concept of inventory type. You can use the inventory_type field to determine which inventory your ads served on, such as Gmail or YouTube Music. Possible values: YOUTUBE, YOUTUBE_TV, YOUTUBE_MUSIC, SEARCH, GMAIL, OTHER. Other refers to either the Google Display or Video network.

SELECT
i.campaign_id,
cmp.campaign_name,
i.inventory_type,
COUNT(i.query_id.time_usec)ASimpressions
FROMadh.google_ads_impressionsi
LEFTJOINadh.google_ads_campaigncmpON(i.campaign_id=cmp.campaign_id)
WHERE
TIMESTAMP_MICROS(i.query_id.time_usec)
BETWEEN@local_start_date
ANDTIMESTAMP_ADD(@local_start_date,INTERVAL@number_days*24HOUR)
GROUPBY1,2,3
ORDERBY4DESC

YouTube ad pod queries

Ad pods group 2 ads into a single ad-break during longer YouTube viewing sessions. (Think commercial break, but limited to 2 ads.) Ads served in ad pods remain skippable. However, if a user skips the first ad, the second ad is also skipped.

SELECT
cmp.campaign_name,
imp.is_app_traffic,
COUNT(*)AStotal_impressions,
COUNTIF(clk.click_idISNOTNULL)AStotal_trueview_views
FROMadh.google_ads_impressionsimp
JOINadh.google_ads_campaigncmpUSING(campaign_id)
JOINadh.google_ads_adgroupadgUSING(adgroup_id)
LEFTJOINadh.google_ads_clicksclkON
imp.impression_id=clk.impression_id
WHERE
imp.customer_idINUNNEST(@customer_ids)
ANDadg.adgroup_type='VIDEO_TRUE_VIEW_IN_STREAM'
ANDcmp.advertising_channel_type='VIDEO'
GROUPBY1,2

Display and Video 360 viewability metrics by line items

WITH
imp_statsAS(
SELECT
imp.line_item_id,
count(*)astotal_imp,
SUM(num_active_view_measurable_impression)ASnum_measurable_impressions,
SUM(num_active_view_eligible_impression)ASnum_enabled_impressions
FROMadh.dv360_youtube_impressionsimp
WHERE
imp.line_item_idINUNNEST(@line_item_ids)
GROUPBY1
),
av_statsAS(
SELECT
imp.line_item_id,
SUM(num_active_view_viewable_impression)ASnum_viewable_impressions
FROMadh.dv360_youtube_impressionsimp
LEFTJOIN
adh.dv360_youtube_active_viewsav
ONimp.impression_id=av.impression_id
WHERE
imp.line_item_idINUNNEST(@line_item_ids)
GROUPBY1
)
SELECT
li.line_item_name,
SUM(imp.total_imp)asnum_impressions,
SUM(imp.num_measurable_impressions)ASnum_measurable_impressions,
SUM(imp.num_enabled_impressions)ASnum_enabled_impressions,
SUM(IFNULL(av.num_viewable_impressions,0))ASnum_viewable_impressions
FROMimp_statsasimp
LEFTJOINav_statsASavUSING(line_item_id)
JOINadh.dv360_youtube_lineitemliON(imp.line_item_id=li.line_item_id)
GROUPBY1

YouTube Reserve queries

Impression delivery by advertiser

This query measures the number of impressions and distinct users per advertiser. You can use these numbers to calculate the average number of impressions per user (or "ad frequency").

SELECT
advertiser_name,
COUNT(*)ASimp,
COUNT(DISTINCTuser_id)ASusers
FROMadh.yt_reserve_impressionsASimpressions
JOINadh.yt_reserve_orderorderONimpressions.order_id=order.order_id
GROUPBY1
;

Ad skips

This query measures the number of ad skips per customer, campaign, ad group, and creative.

SELECT
impression_data.customer_id,
impression_data.campaign_id,
impression_data.adgroup_id,
impression_data.ad_group_creative_id,
COUNTIF(label="videoskipped")ASnum_skips
FROM
adh.google_ads_conversions
GROUPBY1,2,3,4;

General queries

Subtract one group of users from another

This example shows how to subtract one group of users from another. This technique has a wide range of applications, including counting non-converters, users with no viewable impressions, and users with no clicks.

WITHexcludeAS(
SELECTDISTINCTuser_id
FROMadh.google_ads_impressions
WHEREcampaign_id=123
)
SELECT
COUNT(DISTINCTimp.user_id)-
COUNT(DISTINCTexclude.user_id)ASusers
FROMadh.google_ads_impressionsimp
LEFTJOINexclude
USING(user_id)
WHEREimp.campaign_id=876
;

Custom overlap

This query measures the overlap of 2 or more campaigns. It can be customized to measure overlap based on discretionary criteria.

/* For this query to run, @campaign_1 and @campaign_2 must be replaced with
actual campaign IDs. */
WITHflagged_impressionsAS(
SELECT
user_ID,
SUM(IF(campaign_IDinUNNEST(@campaign_1),1,0))ASC1_impressions,
SUM(IF(campaign_IDinUNNEST(@campaign_2),1,0))ASC2_impressions
FROMadh.cm_dt_impressions
GROUPBYuser_ID
SELECTCOUNTIF(C1_impressions > 0)asC1_cookie_count,
COUNTIF(C2_impressions > 0)asC2_cookie_count,
COUNTIF(C1_impressions > 0andC2_impressions > 0)asoverlap_cookie_count
FROMflagged_impressions
;

Partner Sold - Cross Sell

This query measures impressions and click-throughs of partner-sold inventory.

SELECT
a.record_dateASrecord_date,
a.line_item_idASline_item_id,
a.creative_idAScreative_id,
a.ad_idASad_id,
a.impressionsASimpressions,
a.click_throughASclick_through,
a.video_skippedASvideo_skipped,
b.pixel_urlASpixel_url
FROM
(
SELECT
FORMAT_TIMESTAMP('%D',TIMESTAMP_MICROS(i.query_id.time_usec),'Etc/UTC')ASrecord_date,
i.line_item_idasline_item_id,
i.creative_idascreative_id,
i.ad_idasad_id,
COUNT(i.query_id)asimpressions,
COUNTIF(c.label='video_click_to_advertiser_site')ASclick_through,
COUNTIF(c.label='videoskipped')ASvideo_skipped
FROM
adh.partner_sold_cross_sell_impressionsASi
LEFTJOINadh.partner_sold_cross_sell_creative_conversionsASc
ONi.impression_id=c.impression_id
GROUPBY
1,2,3,4
)ASa
JOINadh.partner_sold_cross_sell_creative_pixelsASb
ON(a.ad_id=b.ad_id)
;

App store impressions

The following query counts the total number of impressions grouped by app store and app.

SELECTapp_store_name,app_name,COUNT(*)ASnumber
FROMadh.google_ads_impressionsASimp
JOINadh.mobile_app_info
USING(app_store_id,app_id)
WHEREimp.app_idISNOTNULL
GROUPBY1,2
ORDERBY3DESC

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2025年04月01日 UTC.