이 샘플 쿼리에서는 SQL 및 BigQuery의 실무 지식이 있다고 가정합니다. BigQuery의 SQL에 대해 자세히 알아보기
Campaign Manager 360 데이터 전송 쿼리
플러드라이트 변수를 임시 테이블과 일치
활동 테이블에서 user_id와 맞춤 플러드라이트 변수 간의 일치를 생성합니다. 그런 다음 퍼스트 파티 데이터를 Campaign Manager 360 데이터와 조인하는 데 사용할 수 있습니다.
/* Creating the match temp table. This can be a separate query and the
temporary table will persist for 72 hours. */
CREATE TABLE
temp_table AS (
SELECT
user_id,
REGEXP_EXTRACT(event.other_data, 'u1=([^;]*)') AS u1_val
FROM
adh.cm_dt_activities_attributed
GROUP BY
1,
2 )
/* Matching to Campaign Manager 360 impression data */
SELECT
imp.event.campaign_id,
temp.u1_val,
COUNT(*) AS cnt
FROM
adh.cm_dt_impressions AS imp
JOIN
tmp.temp_table AS temp USING (user_id)
GROUP BY
1,
2
노출 게재
이 예는 노출 관리에 유용하며 최대 게재빈도를 넘어서 게재된 노출수를 찾거나 특정 잠재고객이 광고에 과소 노출되었는지 확인하는 방법을 보여줍니다. 이러한 지식을 바탕으로 사이트와 방법을 최적화하여 선택한 잠재고객에게 적절한 횟수만큼 노출시키세요.
/* For this query to run, @advertiser_ids and @campaigns_ids
must be replaced with actual IDs. For example [12345] */
WITH filtered_uniques AS (
SELECT
user_id,
COUNT(event.placement_id) AS frequency
FROM adh.cm_dt_impressions
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.country_domain_name = 'US'
GROUP BY user_id
)
SELECT
frequency,
COUNT(*) AS uniques
FROM filtered_uniques
GROUP BY frequency
ORDER BY 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(DISTINCT user_id) AS total_users,
COUNT(DISTINCT event.site_id) AS total_sites,
COUNT(DISTINCT device_id_md5) AS total_devices,
COUNT(event.placement_id) AS impressions
FROM adh.cm_dt_impressions
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.placement_id IN UNNEST(@placement_ids)
AND event.country_domain_name = 'US'
;
WHERE 절에 사이트 또는 게재위치 ID를 포함하여 쿼리 범위를 좁힐 수도 있습니다.
주별 순 쿠키 수 및 평균 게재빈도
이 예에서는 cm_dt_impressions 테이블과 cm_dt_state 메타데이터 테이블을 조인하여 북미 지역의 주/도별로 그룹화된 총노출수, 주당 쿠키 수, 사용자당 평균 노출수를 표시합니다.
WITH impression_stats AS (
SELECT
event.country_domain_name AS country,
CONCAT(event.country_domain_name, '-', event.state) AS state,
COUNT(DISTINCT user_id) AS users,
COUNT(*) AS impressions
FROM adh.cm_dt_impressions
WHERE event.country_domain_name = 'US'
OR event.country_domain_name = 'CA'
GROUP BY 1, 2
)
SELECT
country,
IFNULL(state_name, state) AS state_name,
users,
impressions,
FORMAT(
'%0.2f',
IF(
IFNULL(impressions, 0) = 0,
0,
impressions / users
)
) AS avg_imps_per_user
FROM impression_stats
LEFT JOIN adh.cm_dt_state USING (state)
;
Display & Video 360 잠재고객
이 예에서는 Display & Video 360 잠재고객을 분석하는 방법을 보여줍니다. 어떤 잠재고객 노출수에 도달하고 있는지 알아보고 특정 잠재고객이 다른 잠재고객보다 실적이 우수한지 확인하세요. 이러한 정보를 바탕으로 목표에 따라 고유 쿠키 수(많은 사용자에게 광고 표시)와 품질(타겟팅 및 조회 가능 노출 범위 좁히기)의 균형을 유지할 수 있습니다.
/* For this query to run, @advertiser_ids and @campaigns_ids and @placement_ids
must be replaced with actual IDs. For example [12345] */
WITH filtered_impressions AS (
SELECT
event.event_time as date,
CASE
WHEN (event.browser_enum IN ('29', '30', '31')
OR event.os_id IN
(501012, 501013, 501017, 501018,
501019, 501020, 501021, 501022,
501023, 501024, 501025, 501027))
THEN 'Mobile'
ELSE 'Desktop'
END AS device,
event.dv360_matching_targeted_segments,
event.active_view_viewable_impressions,
event.active_view_measurable_impressions,
user_id
FROM adh.cm_dt_impressions
WHERE event.dv360_matching_targeted_segments != ''
AND event.advertiser_id in UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.dv360_country_code = 'US'
)
SELECT
audience_id,
device,
COUNT(*) AS impressions,
COUNT(DISTINCT user_id) AS uniques,
ROUND(COUNT(*) / COUNT(DISTINCT user_id), 1) AS frequency,
SUM(active_view_viewable_impressions) AS viewable_impressions,
SUM(active_view_measurable_impressions) AS measurable_impressions
FROM filtered_impressions
JOIN UNNEST(SPLIT(dv360_matching_targeted_segments, ' ')) AS audience_id
GROUP BY 1, 2
;
조회가능성
이 예에서는 Active View Plus 조회가능성 측정항목을 측정하는 방법을 보여줍니다.
WITH T AS (
SELECT cm_dt_impressions.event.impression_id AS Impression,
cm_dt_impressions.event.active_view_measurable_impressions AS AV_Measurable,
SUM(cm_dt_active_view_plus.event.active_view_plus_measurable_count) AS AVP_Measurable
FROM adh.cm_dt_impressions
FULL JOIN adh.cm_dt_active_view_plus
ON (cm_dt_impressions.event.impression_id =
cm_dt_active_view_plus.event.impression_id)
GROUP BY Impression, AV_Measurable
)
SELECT COUNT(Impression), SUM(AV_Measurable), SUM(AVP_Measurable)
FROM T
;
WITH Raw AS (
SELECT
event.ad_id AS Ad_Id,
SUM(event.active_view_plus_measurable_count) AS avp_total,
SUM(event.active_view_first_quartile_viewable_impressions) AS avp_1st_quartile,
SUM(event.active_view_midpoint_viewable_impressions) AS avp_2nd_quartile,
SUM(event.active_view_third_quartile_viewable_impressions) AS avp_3rd_quartile,
SUM(event.active_view_complete_viewable_impressions) AS avp_complete
FROM
adh.cm_dt_active_view_plus
GROUP BY
1
)
SELECT
Ad_Id,
avp_1st_quartile / avp_total AS Viewable_Rate_1st_Quartile,
avp_2nd_quartile / avp_total AS Viewable_Rate_2nd_Quartile,
avp_3rd_quartile / avp_total AS Viewable_Rate_3rd_Quartile,
avp_complete / avp_total AS Viewable_Rate_Completion_Quartile
FROM
Raw
WHERE
avp_total > 0
ORDER BY
Viewable_Rate_1st_Quartile DESC
;
Campaign Manager 360 데이터 전송의 동적 데이터
동적 프로필 및 피드당 노출수
SELECT
event.dynamic_profile,
feed_name,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
JOIN UNNEST (event.feed) as feed_name
GROUP BY 1, 2;
피드 1의 동적 보고 라벨당 노출수
SELECT
event.feed_reporting_label[SAFE_ORDINAL(1)] feed1_reporting_label,,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_label[SAFE_ORDINAL(1)] <> “” # where you have at least one reporting label set
GROUP BY 1;
피드 2에서 보고 라벨이 'red'인 노출수
SELECT
event.feed_reporting_label[SAFE_ORDINAL(2)] AS feed1_reporting_label,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_label[SAFE_ORDINAL(2)] = “red”
GROUP BY 1;
피드 1에서 보고 dimension_1이 'red'이고 보고 dimension_2가 'car'인 노출수
SELECT
event.feed_reporting_label[SAFE_ORDINAL(1)] AS feed1_reporting_label,
event.feed_reporting_dimension1[SAFE_ORDINAL(1)] AS feed1_reporting_dimension1,
event.feed_reporting_dimension2[SAFE_ORDINAL(1)] AS feed2_reporting_dimension1,
event.feed_reporting_dimension3[SAFE_ORDINAL(1)] AS feed3_reporting_dimension1,
event.feed_reporting_dimension4[SAFE_ORDINAL(1)] AS feed4_reporting_dimension1,
event.feed_reporting_dimension5[SAFE_ORDINAL(1)] AS feed5_reporting_dimension1,
event.feed_reporting_dimension6[SAFE_ORDINAL(1)] AS feed6_reporting_dimension1,
COUNT(*) as impressions
FROM adh.cm_dt_impressions
WHERE event.feed_reporting_dimension1[SAFE_ORDINAL(1)] = “red”
AND event.feed_reporting_dimension2[SAFE_ORDINAL(1)] = “car”
GROUP BY 1,2,3,4,5,6,7;
Campaign Manager 360 데이터 전송의 광고 형식
이 예에서는 고유 쿠키 수 또는 노출 빈도를 극대화하는 광고 형식을 확인하는 방법을 보여줍니다. 이러한 정보를 활용하여 총 고유 쿠키 수와 사용자 광고 노출 간의 균형을 유지하세요.
노출 게재
/* 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.*/
WITH filtered_uniques AS (
SELECT
user_id,
CASE
WHEN creative_type LIKE '%Video%' THEN 'Video'
WHEN creative_type IS NULL THEN 'Unknown'
ELSE 'Display'
END AS creative_format,
COUNT(*) AS impressions
FROM adh.cm_dt_impressions impression
LEFT JOIN YOUR_BQ_DATASET.campaigns creative
ON creative.rendering_id = impression.event.rendering_id
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.country_domain_name = 'US'
GROUP BY user_id, creative_format
)
SELECT
impressions AS frequency,
creative_format,
COUNT(DISTINCT user_id) AS uniques,
SUM(impressions) AS impressions
FROM filtered_uniques
GROUP BY frequency, creative_format
ORDER BY frequency
;
순 쿠키 수 및 게재빈도
/* 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. */
WITH filtered_impressions AS (
SELECT
event.campaign_id AS campaign_id,
event.rendering_id AS rendering_id,
user_id
FROM adh.cm_dt_impressions
WHERE user_id != '0'
AND event.advertiser_id IN UNNEST(@advertiser_ids)
AND event.campaign_id IN UNNEST(@campaign_ids)
AND event.country_domain_name = 'US'
)
SELECT
Campaign,
CASE
WHEN creative_type LIKE '%Video%' THEN 'Video'
WHEN creative_type IS NULL THEN 'Unknown'
ELSE 'Display'
END AS creative_format,
COUNT(DISTINCT user_id) AS users,
COUNT(*) AS impressions
FROM filtered_impressions
LEFT JOIN YOUR_BQ_DATASET.campaigns USING (campaign_id)
LEFT JOIN YOUR_BQ_DATASET.creatives USING (rendering_id)
GROUP BY 1, 2
;
Google Ads
_rdid 테이블이 있는 모바일 앱 노출수
쿼리 1:
SELECT
campaign_id,
COUNT(*) AS imp,
COUNT(DISTINCT user_id) AS users
FROM adh.google_ads_impressions
WHERE is_app_traffic
GROUP BY 1
;
쿼리 2:
SELECT
campaign_id,
COUNT(DISTINCT device_id_md5) AS device_ids
FROM adh.google_ads_impressions_rdid
GROUP BY 1
;
결과는 campaign_id를 사용하여 조인할 수 있습니다.
인구통계 게재
이 예에서는 특정 인구통계에 도달하는 캠페인을 확인하는 방법을 보여줍니다.
/* For this query to run, @customer_id
must be replaced with an actual ID. For example [12345] */
WITH impression_stats AS (
SELECT
campaign_id,
demographics.gender AS gender_id,
demographics.age_group AS age_group_id,
COUNT(DISTINCT user_id) AS users,
COUNT(*) AS impressions
FROM adh.google_ads_impressions
WHERE customer_id = @customer_id
GROUP BY 1, 2, 3
)
SELECT
campaign_name,
gender_name,
age_group_name,
users,
impressions
FROM impression_stats
LEFT JOIN adh.google_ads_campaign USING (campaign_id)
LEFT JOIN adh.gender USING (gender_id)
LEFT JOIN adh.age_group USING (age_group_id)
ORDER BY 1, 2, 3
;
조회가능성
쿼리 샘플을 통한 조회가능성의 개요는 고급 Active View 측정항목을 참고하세요.
Google Ads 광고주 시간대 설정
SELECT
customer_id,
customer_timezone,
count(1) as impressions
FROM adh.google_ads_impressions i
INNER JOIN adh.google_ads_customer c
ON c.customer_id = i.customer_id
WHERE TIMESTAMP_MICROS(i.query_id.time_usec) >= CAST(DATETIME(@date, c.customer_timezone) AS TIMESTAMP)
AND TIMESTAMP_MICROS(i.query_id.time_usec) < CAST(DATETIME_ADD(DATETIME(@date, c.customer_timezone), INTERVAL 1 DAY) AS TIMESTAMP)
GROUP BY customer_id, customer_timezone
인벤토리 유형
이 샘플 쿼리는 인벤토리 유형의 개념을 보여줍니다. inventory_type 필드를 사용하여 Gmail 또는 YouTube Music과 같이 광고가 게재된 인벤토리를 확인할 수 있습니다. 가능한 값: YOUTUBE, YOUTUBE_TV,
YOUTUBE_MUSIC, SEARCH, GMAIL, OTHER. OTHER는 Google 디스플레이 또는 동영상 네트워크를 의미합니다.
SELECT
i.campaign_id,
cmp.campaign_name,
i.inventory_type,
COUNT(i.query_id.time_usec) AS impressions
FROM adh.google_ads_impressions i
LEFT JOIN adh.google_ads_campaign cmp ON (i.campaign_id = cmp.campaign_id)
WHERE
TIMESTAMP_MICROS(i.query_id.time_usec)
BETWEEN @local_start_date
AND TIMESTAMP_ADD(@local_start_date,INTERVAL @number_days*24 HOUR)
GROUP BY 1, 2, 3
ORDER BY 4 DESC
YouTube 광고 모음 쿼리
광고 모음은 보다 긴 YouTube 시청 세션 중에 2개의 광고를 하나의 광고 시점으로 그룹화합니다. (2개의 광고로 제한된 상업 광고가 예입니다.) 광고 모음에 게재되는 광고는 건너뛸 수 있는 상태로 유지됩니다. 그러나 사용자가 첫 번째 광고를 건너뛰면 두 번째 광고도 건너뜁니다.
Google Ads TrueView 인스트림 스트림 노출 및 TrueView 조회수
SELECT
cmp.campaign_name,
imp.is_app_traffic,
COUNT(*) AS total_impressions,
COUNTIF(clk.click_id IS NOT NULL) AS total_trueview_views
FROM adh.google_ads_impressions imp
JOIN adh.google_ads_campaign cmp USING (campaign_id)
JOIN adh.google_ads_adgroup adg USING (adgroup_id)
LEFT JOIN adh.google_ads_clicks clk ON
imp.impression_id = clk.impression_id
WHERE
imp.customer_id IN UNNEST(@customer_ids)
AND adg.adgroup_type = 'VIDEO_TRUE_VIEW_IN_STREAM'
AND cmp.advertising_channel_type = 'VIDEO'
GROUP BY 1, 2
광고 항목별 Display & Video 360 조회가능성 측정항목
WITH
imp_stats AS (
SELECT
imp.line_item_id,
count(*) as total_imp,
SUM(num_active_view_measurable_impression) AS num_measurable_impressions,
SUM(num_active_view_eligible_impression) AS num_enabled_impressions
FROM adh.dv360_youtube_impressions imp
WHERE
imp.line_item_id IN UNNEST(@line_item_ids)
GROUP BY 1
),
av_stats AS (
SELECT
imp.line_item_id,
SUM(num_active_view_viewable_impression) AS num_viewable_impressions
FROM adh.dv360_youtube_impressions imp
LEFT JOIN
adh.dv360_youtube_active_views av
ON imp.impression_id = av.impression_id
WHERE
imp.line_item_id IN UNNEST(@line_item_ids)
GROUP BY 1
)
SELECT
li.line_item_name,
SUM(imp.total_imp) as num_impressions,
SUM(imp.num_measurable_impressions) AS num_measurable_impressions,
SUM(imp.num_enabled_impressions) AS num_enabled_impressions,
SUM(IFNULL(av.num_viewable_impressions, 0)) AS num_viewable_impressions
FROM imp_stats as imp
LEFT JOIN av_stats AS av USING (line_item_id)
JOIN adh.dv360_youtube_lineitem li ON (imp.line_item_id = li.line_item_id)
GROUP BY 1
YouTube Reserve 쿼리
광고주별 노출 게재
이 쿼리는 광고주당 노출수와 고유 사용자 수를 측정합니다. 이 숫자를 사용하여 사용자당 평균 노출수(또는 '광고 게재빈도')를 계산할 수 있습니다.
SELECT
advertiser_name,
COUNT(*) AS imp,
COUNT(DISTINCT user_id) AS users
FROM adh.yt_reserve_impressions AS impressions
JOIN adh.yt_reserve_order order ON impressions.order_id = order.order_id
GROUP BY 1
;
광고 건너뛰기
이 쿼리는 고객, 캠페인, 광고그룹 및 광고 소재당 광고 건너뛰기 횟수를 측정합니다.
SELECT
impression_data.customer_id,
impression_data.campaign_id,
impression_data.adgroup_id,
impression_data.ad_group_creative_id,
COUNTIF(label = "videoskipped") AS num_skips
FROM
adh.google_ads_conversions
GROUP BY 1, 2, 3, 4;
일반 쿼리
다른 사용자 그룹에서 특정 사용자 그룹 제외
이 예에서는 다른 사용자 그룹에서 특정 사용자 그룹을 제외하는 방법을 보여줍니다. 이 기법은 비전환 사용자, 조회 가능 노출이 없는 사용자, 클릭 없는 사용자 집계 등에서 광범위하게 사용됩니다.
WITH exclude AS (
SELECT DISTINCT user_id
FROM adh.google_ads_impressions
WHERE campaign_id = 123
)
SELECT
COUNT(DISTINCT imp.user_id) -
COUNT(DISTINCT exclude.user_id) AS users
FROM adh.google_ads_impressions imp
LEFT JOIN exclude
USING (user_id)
WHERE imp.campaign_id = 876
;
맞춤 중복
이 쿼리는 2개 이상 캠페인의 중복을 측정합니다. 재량에 따라 중복을 측정하도록 맞춤설정할 수 있습니다.
/* For this query to run, @campaign_1 and @campaign_2 must be replaced with
actual campaign IDs. */
WITH flagged_impressions AS (
SELECT
user_ID,
SUM(IF(campaign_ID in UNNEST(@campaign_1), 1, 0)) AS C1_impressions,
SUM(IF(campaign_ID in UNNEST(@campaign_2), 1, 0)) AS C2_impressions
FROM adh.cm_dt_impressions
GROUP BY user_ID
SELECT COUNTIF(C1_impressions > 0) as C1_cookie_count,
COUNTIF(C2_impressions > 0) as C2_cookie_count,
COUNTIF(C1_impressions > 0 and C2_impressions > 0) as overlap_cookie_count
FROM flagged_impressions
;
파트너 판매 - 크로스셀(cross-sell)
이 쿼리는 파트너 판매 인벤토리의 노출수와 클릭률을 측정합니다.
SELECT
a.record_date AS record_date,
a.line_item_id AS line_item_id,
a.creative_id AS creative_id,
a.ad_id AS ad_id,
a.impressions AS impressions,
a.click_through AS click_through,
a.video_skipped AS video_skipped,
b.pixel_url AS pixel_url
FROM
(
SELECT
FORMAT_TIMESTAMP('%D', TIMESTAMP_MICROS(i.query_id.time_usec), 'Etc/UTC') AS record_date,
i.line_item_id as line_item_id,
i.creative_id as creative_id,
i.ad_id as ad_id,
COUNT(i.query_id) as impressions,
COUNTIF(c.label='video_click_to_advertiser_site') AS click_through,
COUNTIF(c.label='videoskipped') AS video_skipped
FROM
adh.partner_sold_cross_sell_impressions AS i
LEFT JOIN adh.partner_sold_cross_sell_conversions AS c
ON i.impression_id = c.impression_id
GROUP BY
1, 2, 3, 4
) AS a
JOIN adh.partner_sold_cross_sell_creative_pixels AS b
ON (a.ad_id = b.ad_id)
;
앱 스토어 노출수
다음 쿼리는 앱 스토어 및 앱별로 그룹화된 총 노출수를 계산합니다.
SELECT app_store_name, app_name, COUNT(*) AS number
FROM adh.google_ads_impressions AS imp
JOIN adh.mobile_app_info
USING (app_store_id, app_id)
WHERE imp.app_id IS NOT NULL
GROUP BY 1,2
ORDER BY 3 DESC