Amazon Marketing Cloud Path to Purchase Insights

blog-img

For brands navigating the complexities of Amazon advertising, understanding the customer journey from ad exposure to purchase used to be a big challenge. However, Amazon Marketing Cloud (AMC) has made it easy to understand the sequence of ad interactions and touchpoints across multiple channels and ad formats that have resulted in successful conversions.

In this blog post, we will explore how you can get this data and utilize it for a more effective advertising strategy.

Why Path to Purchase Data is Important?

In the context of Amazon shopping, customers often interact with multiple ads before completing a purchase. Amazon, however, typically attributes the conversion exclusively to the last ad clicked by the customer.

Does this imply that the earlier ads in our retail media mix, which the customer was exposed to, held no significance?

Not at all.

Those preceding ads played an equally crucial role in influencing the conversion. While streaming TV ads or Sponsored brand ads may not lead directly to a conversion, they are essential for creating awareness among the target audience about your product offerings.

By utilizing Path to Purchase data, you gain a clear understanding of how different ads contribute to driving conversions. This data provides insights into the impact of various ads on the customer’s shopping journey, helping you refine your advertising strategy for optimal effectiveness.

Amazon Marketing Cloud Path to Purchase Query

Here is an instructional query that Amazon provides to get the different combinations of paths to purchase along with performance metrics:

-- 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 */

If you are proficient in SQL knowledge, you can refine this instructional query. Group the conversions by filtering them for only a certain ad campaign type, such as Sponsored Products, Brands, Displays, or DSP ads.

Sample Output of Query

Amazon Marketing Cloud Path to purchase query output

From the results above, we can interpret that the highest amount of sales occurred when users were shown all three types of sponsored ads and followed the path SP → SB → SD. Additionally, most New to Brand Orders originated from the SP → SD path.

Benefits of leveraging the Path to Purchase query:

Strategic Budgeting

Understand which path has a higher conversion rate or a higher new-to-brand percentage and accommodate the appropriate budget for each campaign available in the path.

Targeted Messaging

Craft compelling ad creatives tailored to the user’s specific stage in the buying cycle, developing engagement and conversion.

Campaign Optimization

Refine campaigns precisely, focusing on touchpoints that maximize ROAS(Return on Ad Spend).

How Adbrew Can help you?

Navigating Amazon Marketing Cloud can be challenging for those without technical expertise, as it involves using SQL knowledge to run various queries and download data.

Fortunately, Adbrew has simplified this process by introducing the new Amazon Marketing Cloud Dashboard for AMC insights. You can effortlessly identify the most successful conversion paths and pinpoint the top-performing campaign groups in terms of awareness and conversions.

Our dashboard seamlessly integrates multiple reports, including Time to Conversion, Path to Purchase, ASIN Overlapping, Multi-Touch Attribution, and more.

So No more SQL tensions, just use Adbrew’s Amazon Marketing Cloud Dashboard for actionable insights for your advertising campaigns.

Ready to get started with Adbrew?

Adbrew provides cutting-edge automation, expertly curated strategies, and data-driven insights for brands to thrive on Amazon.

Final Thought:

The Path to Purchase query enhances advertisers’ ability to make data-driven decisions by providing clear visibility into how customers are interacting with their different ad campaigns. Armed with these insights, brands can develop focused Amazon ads campaigns, allocate budgets more effectively, and expect a noticeable improvement in their advertising efforts.

Grow your business on Amazon with Adbrew

Adbrew's Amazon PPC Optimisation Platform is being leveraged by 1000+ brands, sellers and agencies to optimise millions of ad-spend every month.

All Marketplaces Supported

24x7 Support

Expert Consultation