Repeat purchase analysis in AMC: repeat buyers per brand with Amazon Retail Purchases
In short: For repeat purchase rates you need the Amazon Retail Purchases dataset (up to 60 months of purchase history): count orders per buyer and brand, then set buyers with more than one order against all buyers. `user_id` may appear only in the CTE and inside `COUNT(DISTINCT …)`, never in the final SELECT.
The data basis
amazon_retail_purchasesholds purchases in the Amazon store regardless of advertising, up to 60 months. It needs a paid features subscription (free through December 31, 2026); the notes are on the data source page.purchase_ididentifies the purchase,user_idthe buyer; both have the VERY_HIGH threshold and belong in CTEs only.purchase_order_methoddescribes how the item was bought (S shopping cart, B Buy Now, 1 1-Click),purchase_program_namethe associated purchase program.
Query: repeat buyers per brand
WITH orders_per_user AS (
SELECT
user_id,
asin_brand,
COUNT(DISTINCT purchase_id) AS orders
FROM
amazon_retail_purchases
GROUP BY
user_id,
asin_brand
)
SELECT
asin_brand,
COUNT(DISTINCT user_id) AS buyers,
SUM(CASE WHEN orders > 1 THEN 1 ELSE 0 END) AS repeat_buyers
FROM
orders_per_user
GROUP BY
asin_brandSubscribe and Save
The CLTV playbook recommends the shopping insights datasets so non-advertising-driven purchases such as Subscribe and Save count. In conversions_all the first Subscribe and Save order is an event of the category website, and the field sns_subscription_id carries the subscription ID (threshold INTERNAL). The docs do not offer a ready subscription evaluation; treat subscription purchases separately when you compare repeat rates.