The Amazon knowledge
your agent didn't
have to earn.

WITH asins (asin) AS (
  VALUES ('B0XXXXXXX1'), ('B0XXXXXXX2')
),
purchase AS (
  SELECT user_id, MAX(event_dt_utc) AS purchase_dt_max
  FROM conversions_for_audiences
  WHERE event_subtype = 'order'
    AND tracked_item IN (SELECT asin FROM asins)
  GROUP BY 1
),
atc AS (
  SELECT user_id, MAX(event_dt_utc) AS atc_dt_max
  FROM conversions_for_audiences
  WHERE event_subtype = 'shoppingCart'
    AND tracked_item IN (SELECT asin FROM asins)
  GROUP BY 1
)
SELECT atc.user_id
FROM atc
LEFT JOIN purchase ON atc.user_id = purchase.user_id
WHERE atc_dt_max > purchase_dt_max
   OR purchase_dt_max IS NULL;

SELECT
  CASE
    WHEN event_subtype IN (
      'snsSubscription','firstSnSOrder','repeatSnSOrder'
    ) THEN 'subscriber'
    ELSE 'non-subscriber'
  END AS user_type,
  COUNT(DISTINCT user_id) AS users_that_purchased,
  SUM(total_product_sales) AS total_sales,
  SUM(total_product_sales) /
    COUNT(DISTINCT user_id) AS average_spend
FROM conversions_all
GROUP BY 1;

-- Path-to-Conversion grouped by campaign category
-- Joins DSP impressions, sponsored ads traffic,
-- and attributed events on user_id within window.
SELECT
  path_signature,
  campaign_category_path,
  COUNT(DISTINCT user_id) AS converters,
  SUM(total_product_sales) AS sales
FROM customer_journey_paths
GROUP BY 1, 2
ORDER BY converters DESC;