Do you want to enhance the lifetime value of your customers on Amazon through targeted advertising campaigns?

Upselling and cross-selling are two well-established strategies that have proven effective for brands for over a decade in boosting the LTV of any customer.

But the key question is: whom should you target for upselling, and which products are the right fit for cross-selling or upselling?

Thankfully, if you have access to Amazon Marketing Cloud, leveraging the ASIN Purchase Overlap query provides an opportunity to gain valuable insights into these questions.

In this blog post, we will cover what the AMC ASIN purchase overlap query is, why it matters, and how to use it effectively.

What does ASIN Purchase Overlap mean in Amazon Marketing Cloud?

Amazon Marketing Cloud gathers extensive data on shopper behavior, including purchase histories. AMC analyzes this data to identify patterns in which different products are frequently bought together. ASIN purchase overlap shows the degree of overlap between different ASINs when the same user purchases two different ASINs.

ASIN Purchase Overlap Query Example

Here is an example of an SQL query that you can use to download the ASIN purchase overlap report for your brand.

/* Instructional Query: 'ASIN Purchase Overlap for Upselling' 
 The query will help track the ASINs purchased together. 
 With that, we can identify potential upselling opportunities.
 This query is looking at the time, when the conversion happened, 
 by using table amazon_attributed_events_by_conversion_time. */
-- Optional UPDATE : Add DSP campaign IDs and/or SA campaign names below and uncomment line [1 of 1] in the tracked_asin_list CTE for applying campaign filters. To run the query without filters, ignore this section and leave the line [1 of 1] commented.
WITH dsp_campaigns (campaign_id) AS (
  VALUES
    (111111111111),
    (222222222222),
    (333333333333)
),
sa_campaigns (campaign) AS (
  VALUES
    ('SP_campaign_name_1'),
    ('SD_campaign_name_2'),
    ('SB_campaign_name_3')
),
-- get lead ASIN info
asin_info AS (
  SELECT
    tracked_asin AS lead_asin,
    COUNT(DISTINCT user_id) AS asin_info_dist_user_count,
    SUM(purchases) AS asin_info_purchases_sum
  FROM
    amazon_attributed_events_by_conversion_time
  WHERE
    purchases > 0
    AND user_id IS NOT NULL
  GROUP BY
    1
),
-- get list of purchased ASINs and the purchasing user_ids
tracked_asin_list AS (
  SELECT
    DISTINCT tracked_asin,
    user_id
  FROM
    amazon_attributed_events_by_conversion_time ATC
  WHERE
    purchases > 0
    AND user_id IS NOT NULL
    /*AND (ATC.campaign_id IN (SELECT campaign_id FROM dsp_campaigns) OR ATC.campaign IN (SELECT campaign FROM sa_campaigns)) --Optional Update [1 of 1] Uncomment this line to enable campaign filters. Keep it commented to run the query without filters.*/
),
-- determine overlap between purchase audiences via INNER JOIN on previously created table
determine_overlap AS (
  SELECT
    ta.tracked_asin AS lead_asin,
    tb.tracked_asin AS overlap_asin,
    COUNT(DISTINCT ta.user_id) AS dist_lead_and_overlap_asin_purchased_user_count
  FROM
    tracked_asin_list ta
    INNER JOIN tracked_asin_list tb ON ta.user_id = tb.user_id
  WHERE
    ta.tracked_asin <> tb.tracked_asin
  GROUP BY
    1,
    2
)
SELECT
  ai.lead_asin,
  ai.asin_info_purchases_sum AS lead_asin_purchases,
  ai.asin_info_dist_user_count AS dist_lead_asin_purchased_user_count,
  do.overlap_asin,
  do.dist_lead_and_overlap_asin_purchased_user_count,
  (
    do.dist_lead_and_overlap_asin_purchased_user_count / ai.asin_info_dist_user_count
  ) AS user_overlap
  /*OPTIONAL UPDATE, generate URL to ASIN detail page.*/
  -- Depending on the country of your advertiser, you need to adjust the top level amazon domain, e.g. replacing amazon.com with amazon.co.uk for an advertiser active in UK.
  -- ,concat('https://amazon.com/dp/', ai.lead_asin) as url_to_lead_asin
  -- ,concat('https://amazon.com/dp/', do.overlap_asin) as url_to_overlap_asin
FROM
  asin_info ai
  LEFT JOIN determine_overlap do ON ai.lead_asin = do.lead_asin
WHERE
  ai.lead_asin <> do.overlap_asin
  AND do.dist_lead_and_overlap_asin_purchased_user_count > 1

Example Query Results

• The first column displays the primary lead ASIN for the data provided in this row.

• The second column shows the total sum of purchases for the leading ASIN.

• The third column indicates the count of distinct users purchasing the leading ASIN.

• The fourth column displays the ASIN against which we are checking the overlap with the leading ASIN.

• The fifth column shows the count of distinct users purchasing both the leading ASIN and the overlapping ASIN.

How to Use this Report in your Advertising Strategy.

Once you identify the combination of products frequently purchased by the same audience, you can create two distinct campaigns:

1. Cross-Selling Campaign:

Launch Sponsored Ads (Sponsored Products and Display) or DSP ads with contextual/product targeting ads to promote the overlapped ASIN on the lead ASIN's product detail page, and vice versa. 

This way, when visitors check out the product pages for the lead ASINs or their overlapping counterparts, they'll see both options, making it easier for them to consider purchasing both products together.

2. Upselling Campaigns:

Begin by creating two separate DSP audiences—one for people who have purchased the lead ASIN and another for those who have purchased overlapped ASINs. Launch a DSP retargeting campaign targeting those who have purchased the lead ASIN, and excluding the audience that has purchased overlapped ASINs within the same campaign. 

Implementing this approach optimizes your advertising performance, ensuring that ads reach potential customers who have bought the lead ASIN but not the overlapped one, creating a prime opportunity for a second purchase.

How Adbrew can help you?

Amazon Marketing Cloud offers valuable granular data and profound insights, making it a powerful tool. However, its complexity can pose challenges for individuals without SQL knowledge.

Fortunately, Adbrew has developed a user-friendly solution – our new Amazon Marketing Cloud Dashboard. With this dashboard, you can effortlessly access actionable insights for your brand.

Our dashboard includes ready-made graphs and tables designed for various queries, including ASIN Purchase Overlap. This allows you to effortlessly identify combinations of top overlap ASIN purchases with just a click, eliminating the need to manually write a query.

Ready to get started with Adbrew?

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

Schedule a Demo

Final Thought on Amazon Marketing Cloud ASIN Purchase Overlap Query

In conclusion, Amazon Marketing Cloud's ASIN Purchase Overlap is a powerful query that can help you unlock a wealth of upsell and cross-sell opportunities. By identifying products that customers frequently buy alongside a specific ASIN, you can craft personalized recommendations that boost average order value and build customer loyalty.