-- Instructional Query: Path to Conversion by Campaign Groups
-- Please follow through the instructions in comments to customize this insight
-- OPTIONAL UPDATE Use the following code if you want to group your campaigns per the instructions above
WITH campaign_group (campaign_id_or_name, campaign_group) AS (
VALUES
('111111111111111111', 'group 1'),
('222222222222222222', 'group 1'),
('SP_campaign_name_1', 'group 2'),
('SP_campaign_name_2', 'group 2'),
('SD_campaign_name_1', 'group 3'),
('SD_campaign_name_2', 'group 3'),
('SB_campaign_name_1', 'group 4'),
('SB_campaign_name_2', 'group 4')
),
-- OPTIONAL UPDATE Use the following code if you want to apply campaign filters per instructions above
dsp_campaigns (campaign_id) AS (
VALUES
-- Provide a list of dsp campaign_ids below to filter
('111111111111111111'),
('222222222222222222')
),
sp_campaigns (campaign) AS (
VALUES
-- Provide a list of sponsored products campaign names below to filter
('SP_campaign_name_1'),
('SP_campaign_name_2')
),
sd_campaigns (campaign) AS (
VALUES
-- Provide a list of sponsored display campaign names below to filter
('SD_campaign_name_1'),
('SD_campaign_name_2')
),
sb_campaigns (campaign) AS (
VALUES
-- Provide a list of sponsored brands campaign names below to filter
('SB_campaign_name_1'),
('SB_campaign_name_2')
),
tracked_asins (ASIN) AS (
VALUES
-- Provide list of tracked ASINs below to filter
('ASIN111'),
('ASIN222')
),
impressions AS (
SELECT
COALESCE(campaign_group, 'DSP-Others') AS campaign_group,
'DSP' AS product_type,
user_id,
MIN(impression_dt) AS impression_dt_first,
MAX(impression_dt) AS impression_dt_last,
SUM(impressions) AS impressions,
SUM(total_cost) AS total_cost
FROM
dsp_impressions i --INNER JOIN dsp_campaigns dsp on dsp.campaign_id = i.campaign_id
LEFT JOIN campaign_group G ON g.campaign_id_or_name = i.campaign_id
WHERE
user_id IS NOT NULL
GROUP BY
1,
2,
3 -- Sponsored Products Section START
UNION ALL
SELECT
COALESCE(campaign_group, 'SP-Others') AS campaign_group,
'SP' AS product_type,
user_id,
MIN(event_dt) AS impression_dt_first,
MAX(event_dt) AS impression_dt_last,
SUM(impressions) AS impressions,
SUM(spend) AS total_cost
FROM
sponsored_ads_traffic a --INNER JOIN sp_campaigns sp on sp.campaign = a.campaign
LEFT JOIN campaign_group G ON g.campaign_id_or_name = a.campaign
WHERE
user_id IS NOT NULL
AND ad_product_type = 'sponsored_products'
GROUP BY
1,
2,
3 -- Sponsored Products Section END
-- Sponsored Display Section START
UNION ALL
SELECT
COALESCE(campaign_group, 'SD-Others') AS campaign_group,
'SD' AS product_type,
user_id,
MIN(event_dt) AS impression_dt_first,
MAX(event_dt) AS impression_dt_last,
SUM(impressions) AS impressions,
SUM(spend) AS total_cost
FROM
sponsored_ads_traffic a --INNER JOIN sd_campaigns sd on sd.campaign = a.campaign
LEFT JOIN campaign_group G ON g.campaign_id_or_name = a.campaign
WHERE
user_id IS NOT NULL
AND ad_product_type = 'sponsored_display'
GROUP BY
1,
2,
3 -- Sponsored Display Section END
-- Sponsored Brands Section START
UNION ALL
SELECT
COALESCE(campaign_group, 'SB-Others') AS campaign_group,
'SB' AS product_type,
user_id,
MIN(event_dt) AS impression_dt_first,
MAX(event_dt) AS impression_dt_last,
SUM(impressions) AS impressions,
SUM(spend) AS total_cost
FROM
sponsored_ads_traffic a --INNER JOIN sb_campaigns sb on sb.campaign = a.campaign
LEFT JOIN campaign_group G ON g.campaign_id_or_name = a.campaign
WHERE
user_id IS NOT NULL
AND ad_product_type = 'sponsored_brands'
GROUP BY
1,
2,
3 -- Sponsored Brands Section END
),
-- gather conversions --
converted AS (
SELECT
user_id,
MAX(conversion_event_dt) AS conversion_event_dt_last,
-- Sales metrics
SUM(
IF(
ad_product_type IS NULL,
product_sales,
total_product_sales
)
) AS product_sales,
-- replace product_sales with total_product_sales to include brand halo sales from DSP
SUM(
IF(ad_product_type IS NULL, purchases, total_purchases)
) AS purchases,
-- replace purchases with total_purchases to include brand halo purchases from DSP
-- NTB sales metrics
MAX(IF(new_to_brand = TRUE, 1, 0)) AS ntb_cust,
SUM(
IF(
ad_product_type IS NULL,
new_to_brand_product_sales,
new_to_brand_total_product_sales
)
) AS ntb_product_sales,
-- replace new_to_brand_product_sales with new_to_brand_total_product_sales to include brand halo sales from DSP
SUM(
IF(
ad_product_type IS NULL,
new_to_brand_purchases,
new_to_brand_total_purchases
)
) AS ntb_purchases -- replace new_to_brand_purchases with new_to_brand_total_purchases to include brand halo purchases from DSP
FROM
amazon_attributed_events_by_traffic_time
WHERE
IF(ad_product_type IS NULL, purchases, total_purchases) > 0 -- replace purchases with total_purchases to include brand halo conversions from DSP
AND user_id IS NOT NULL --AND (campaign_id IN ( Select campaign_id from dsp_campaigns) OR campaign IN (Select campaign from sp_campaigns) OR campaign IN (Select campaign from sd_campaigns) OR campaign IN (Select campaign from sb_campaigns))
--AND tracked_asin IN (Select ASIN from tracked_asins)
GROUP BY
1
),
-- only include impressions that happened before conversion event time --
filter_impressions AS (
SELECT
i.user_id AS imp_user_id,
c.user_id AS pur_user_id,
i.campaign_group,
i.impressions,
i.impression_dt_first,
i.impression_dt_last,
i.product_type,
-- DSP cost is reported in millicents. To calculate the cost in dollars/your currency, divide the cost value by 100,000 (1e5). Sponsored Ads spend is reported as microcents. Divide by 100,000,000 (1e8) to get the cost in dollars/your currency.
IF(
i.product_type = 'DSP',
(i.total_cost / 1e5),
(i.total_cost / 1e8)
) AS total_cost,
c.conversion_event_dt_last,
COALESCE(c.product_sales, 0) AS product_sales,
COALESCE(c.purchases, 0) AS purchases,
COALESCE(c.ntb_cust, 0) AS ntb_cust,
COALESCE(c.ntb_purchases, 0) AS ntb_purchases,
COALESCE(c.ntb_product_sales, 0) AS ntb_product_sales
FROM
impressions i
LEFT JOIN converted c ON c.user_id = i.user_id
WHERE
(
c.user_id IS NOT NULL
AND i.impression_dt_first < c.conversion_event_dt_last
)
OR c.user_id IS NULL
),
-- order campaigns based on impression event time--
ranked AS (
SELECT
NAMED_ROW(
'order',
ROW_NUMBER() OVER (
PARTITION BY f.imp_user_id
ORDER BY
f.impression_dt_first,
f.impression_dt_last desc,
campaign_group,
product_type
),
'campaign_group',
f.campaign_group
) AS campaign_order,
imp_user_id
FROM
filter_impressions f
WHERE
f.imp_user_id IS NOT NULL
),
-- create campaign path group by user --
assembled AS (
SELECT
ARRAY_SORT(COLLECT(DISTINCT a.campaign_order)) AS path,
a.imp_user_id
FROM
ranked a
GROUP BY
a.imp_user_id
),
-- dedupe table conversions to represent each row as each user --
filter_impressions_dedupe AS (
SELECT
imp_user_id AS imp_user_id,
SUM(impressions) AS impressions,
SUM(total_cost) AS total_cost,
MAX(IF(pur_user_id IS NOT NULL, 1, 0)) AS converted,
MAX(product_sales) AS product_sales,
MAX(purchases) AS purchases,
MAX(ntb_cust) AS ntb_cust,
MAX(ntb_purchases) AS ntb_purchases,
MAX(ntb_product_sales) AS ntb_product_sales
FROM
filter_impressions
GROUP BY
imp_user_id
),
-- assemble impressions and conversions --
assembled_with_imp_conv AS (
SELECT
path AS path,
COUNT(DISTINCT a.imp_user_id) AS reach,
SUM(b.impressions) AS impressions,
SUM(b.total_cost) AS total_cost,
SUM(b.converted) AS users_that_purchased,
SUM(b.product_sales) AS sales_amount,
SUM(b.purchases) AS purchases,
SUM(b.ntb_cust) AS ntb_users_that_purchased,
SUM(b.ntb_product_sales) AS ntb_sales_amount,
SUM(b.ntb_purchases) AS ntb_purchases
FROM
assembled a
LEFT JOIN filter_impressions_dedupe b ON a.imp_user_id = b.imp_user_id
GROUP BY
path
) -- FINAL SELECT
SELECT
path,
reach AS path_occurrences,
impressions AS impressions,
total_cost AS total_cost,
users_that_purchased AS users_that_purchased,
sales_amount AS sales_amount,
purchases AS purchases,
(users_that_purchased / reach) AS user_purchase_rate,
ntb_users_that_purchased AS ntb_users_that_purchased,
ntb_sales_amount AS ntb_sales_amount,
ntb_purchases AS ntb_purchases,
(ntb_users_that_purchased / users_that_purchased) AS ntb_percentage
FROM
assembled_with_imp_conv
