Amazon Marketing Cloud Path to Purchase Insights

-- Instructional Query: Path to Conversion by Campaign Groups
-- Please follow through the instructions in comments to customize this insight
/* Campaign Group Start */
/*
 Instructions:
 1. If no grouping is required, ignore the section below.
 Note: Default grouping will be set if no groupings are provided. DSP campaigns will be tagged as "DSP-Others", SP campaigns will be tagged as "SP-Others" and  SD campaigns will be tagged as "SD-Others" and SB campaigns will be tagged as "SB-Others
 2. To Apply grouping:
 Add the grouping for campaigns below. 
 For DSP, add campaign_id and group. Remove the section if not required.
 For SP, add the campaign name and group. Remove the section if not required.
 For SD, add the campaign name and group. Remove the section if not required.
 DSP campaigns with no groupings will be tagged as "DSP-Others", SP campaigns will be tagged as "SP-Others" SD campaigns will be tagged as "SD-Others" and SB campaigns will be tagged as "SB-Others"
 */
-- 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')
),
/* Campaign Group End */
/* Filters Start */
/*
 Instructions:
 1. To run the query without filters, do not make any modifications to this section.
 2. To apply filtering:
 Add the list of ids, names, and ASINs in the respective CTEs as per the instructions.
 To apply campaign filters, comment lines with tags [1 OF 5],[2 OF 5], [3 of 5] in the "impressions" CTE, and [4 OF 5] in the "converted" CTE.
 To apply ASIN filters, comment line with tag [5 OF 5] in the "converted" CTE.
 3. To run this query across DSP campaigns only, delete lines of code in the "Sponsored Products Section", "Sponsored Display Section" and "Sponsored Brands Section" in the "impressions" CTE.
 */
-- 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')
),
/* Filters End */
/* Query Start */
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 /*[1 OF 5]: Uncomment this line to apply campaign filters. Keep this line commented to skip filters*/
    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 /*[2 OF 5]: Uncomment this line to apply campaign filters. Keep this line commented to skip filters*/
    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 /*[3 OF 5]: Uncomment this line to apply campaign filters. Keep this line commented to skip filters*/
    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 /*[3 OF 5]: Uncomment this line to apply campaign filters. Keep this line commented to skip filters*/
    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))  /*[4 OF 5]: Uncomment this line to apply campaign filters. Keep this line commented to skip filters*/
    --AND tracked_asin IN (Select ASIN from tracked_asins) /*[5 OF 5]: Uncomment this line to apply ASIN filters. Keep this line commented to skip filters*/
  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
  /* Query End */