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