Unlocking Path to Conversion Insights with Amazon Marketing Cloud (AMC)

-- Instructional Query: Path to Conversion Campaign / DSP + Sponsored Ads--

/*Bug Fix (8/31/22): A new CTE, filter_impressions_dedupe has been added to dedupe values at user_id level in the filter_impressions CTE. Please refer to comments in the query to understand more. */

-- gather impressions  --

WITH impressions AS (

  SELECT

    -- OPTIONAL UPDATE Use the following code if you want to rename your campaigns. Update campaign_id and campaign group name. Make sure to comment out the campaign in the next line

    /*  CASE campaign_id 

     WHEN 11111111111111 THEN 'Display_Awareness'

     WHEN (222222222222) THEN 'Display_Conversion'

     ELSE 'Other' 

     END AS campaign,

     */

    --OPTIONAL UPDATE remove the campaign row below if you are using the campaign grouping code above.

    'DSP' AS product_type,

    campaign,

    user_id,

    MIN(impression_dt) AS impression_dt_first,

    SUM(impressions) AS impressions,

    SUM(total_cost) AS total_cost

  FROM

    dsp_impressions

  WHERE

    user_id IS NOT NULL -- AND campaign_id IN (11111111111111,222222222222)

  GROUP BY

    1,

    2,

    3

  UNION ALL

  SELECT

    --OPTIONAL UPDATE Use the following code if you want to rename your campaigns. Update campaign and campaign group name Make sure to comment out the campaign

    /*  CASE campaign 

     WHEN 'SP_campaign_name1' THEN 'SP_Awareness_name'

     WHEN 'SP_campaign_name2' THEN 'SP_Conversion_name'

     WHEN 'SD_campaign_name1' THEN 'SD_Awareness_name'

     WHEN 'SD_campaign_name2' THEN 'SD_Conversion_name'

     WHEN 'SB_campaign_name1' THEN 'SB_Awareness_name'

     WHEN 'SB_campaign_name2' THEN 'SB_Conversion_name'  

     ELSE 'Other' 

     END AS campaign,

     */

    --OPTIONAL UPDATE remove the campaign row below if you are using the campaign grouping code above.

    ad_product_type AS product_type,

    campaign,

    user_id,

    MIN(event_dt) AS impression_dt_first,

    SUM(impressions) AS impressions,

    SUM(spend) AS total_cost

  FROM

    sponsored_ads_traffic

  WHERE

    user_id IS NOT NULL -- AND campaign IN ('SP_campaign_name1','SP_campaign_name2','SD_campaign_name1','SD_campaign_name2','SB_campaign_name1','SB_campaign_name2')

  GROUP BY

    1,

    2,

    3

) -- gather conversions --

,

converted AS (

  SELECT

    -- OPTIONAL UPDATE Use the following code if you want to rename your campaigns. Update campaign_id and campaign group name. Make sure to comment out the campaign in the next line

    /*  CASE campaign_id 

     WHEN 11111111111111 THEN 'Display_Awareness'

     WHEN 222222222222 THEN 'Display_Conversion'

     ELSE 'Other' 

     END AS campaign,

     */

    --OPTIONAL UPDATE remove the campaign row below if you are using the campaign grouping code above.

    campaign,

    user_id,

    MAX(conversion_event_dt) AS conversion_event_dt_last,

    SUM(product_sales) AS product_sales,

    SUM(purchases) AS purchases

  FROM

    amazon_attributed_events_by_traffic_time

  WHERE

    purchases + total_purchases_clicks > 0

    AND user_id IS NOT NULL --OPTIONAL UPDATE if you want to select specific campaigns, use the following filter.

    -- AND (campaign_id IN (11111111111111,222222222222)

    -- OR campaign IN ('SP_campaign_name1','SP_campaign_name2','SD_campaign_name1','SD_campaign_name2','SB_campaign_name1','SB_campaign_name2'))

  GROUP BY

    1,

    2

) -- 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,

    i.impressions,

    i.impression_dt_first,

    -- DSP cost is reported in millicents. To calculate the cost in dollars/your currency, divide the cost value by 100,000.Sponsored Ads spend is reported as microcents. Divide by 100,000,000 to get the cost in dollars/your currency.

    IF(

      i.product_type IN (

        'sponsored_products',

        'sponsored_display',

        'sponsored_brands'

      ),

      (i.total_cost / 100000000),

      (i.total_cost / 100000)

    ) AS total_cost,

    c.conversion_event_dt_last,

    COALESCE(c.product_sales, 0) AS product_sales,

    COALESCE(c.purchases, 0) AS purchases

  FROM

    impressions i

    LEFT JOIN converted c ON c.user_id = i.user_id

    AND c.campaign = i.campaign

  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

      ),

      'campaign',

      f.campaign

    ) 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

)

/* Bug Fix: Start of Section added on 8/31/22 to dedupe values at user_id level */

,

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 pur_user_id,

    MAX(product_sales) AS product_sales,

    MAX(purchases) AS purchases

  FROM

    filter_impressions

  GROUP BY

    imp_user_id

),

/* Bug Fix: End of Section added on 8/31/22 to dedupe values at user_id level */

-- assemble impressions, clicks and conversions --

assembled_with_imp_conv AS (

  SELECT

    path,

    COUNT(DISTINCT a.imp_user_id) AS reach,

    SUM(b.impressions) AS impressions,

    SUM(b.total_cost) AS imp_total_cost,

    SUM(b.pur_user_id) AS users_that_purchased,

    SUM(b.product_sales) AS sales_amount,

    SUM(b.purchases) AS purchases

  FROM

    assembled a

    LEFT JOIN filter_impressions_dedupe b

    /*Bug Fix: Updated on 8/31/22 to dedupe values at user_id level*/

    ON a.imp_user_id = b.imp_user_id

  GROUP BY

    path

)

SELECT

  path,

  reach AS path_occurrences,

  impressions,

  imp_total_cost,

  users_that_purchased,

  sales_amount,

  purchases,

  (users_that_purchased / reach) AS user_purchase_rate

FROM

  assembled_with_imp_conv