The AMC Query Library: 15 Ready-to-Run Queries

Introduction

What is Amazon Marketing Cloud?

Amazon Marketing Cloud (AMC) is Amazon's clean room analytics environment that lets advertisers run custom SQL queries across their advertising and retail data. Unlike standard Amazon reporting, AMC lets you join purchase history with ad impressions, clicks, and conversions at the user level, unlocking insights like customer lifetime value, cross-sell paths, organic vs. paid acquisition, and full-funnel attribution.

This guide provides 15 production-tested SQL queries that you can copy and run directly in your AMC instance. Each query has been optimized for AMC's SQL dialect, tested against real data, and includes analysis recommendations for turning raw output into actionable insights.

Who This Guide Is For

This guide is for Amazon advertisers, brand managers, and agencies who have access to AMC and want to build customer analytics, LTV reports, or attribution models. You should be comfortable with basic SQL concepts (SELECT, JOIN, GROUP BY), though every query is provided ready to run.

Key AMC Rules & Gotchas

AMC has a unique SQL dialect with specific limitations, reserved words, unit conversions, and date column traps that will save you hours of debugging. For the full reference, see the AMC Complete Reference Guide →

Paid vs. Free AMC Tables

Not all AMC tables are available on the free tier. Amazon offers paid data subscriptions (such as Flexible Shopping Insights) that unlock additional tables beyond what's included with base AMC access. If you try to run a query that references a paid table you haven't subscribed to, the query will fail with an error.

⚠️ 10 of 15 Queries Require Paid AMC Table Subscriptions

The following tables used in this guide require an active Amazon paid data subscription:

amazon_retail_purchases: required for Q1, Q2, Q4, Q5, Q6, Q10, Q12, Q13, Q15
conversions_all: required for Q3

The remaining 5 queries (Q7, Q8, Q9, Q11, Q14) use only standard tables included with base AMC access: sponsored_ads_traffic, dsp_impressions, dsp_clicks, and amazon_attributed_events_by_conversion_time.

How to check: In your AMC instance, open the Paid Features tab to see which subscriptions are active. Amazon has made its first-party paid features, including Amazon Retail Purchases, free to subscribe through December 31, 2026. If you don't see the option to opt in, reach out to your Amazon rep to get access.

Throughout this guide, queries requiring paid tables are flagged with a 💲 Paid table badge, and free queries are marked ✓ Free.

The Four Query Categories

The 15 queries are organized into four categories, each targeting a different aspect of your Amazon business:

📊

Revenue & Customer Analytics

3 queries

Q1 Monthly Revenue & Customers
Q2 Purchase Frequency
Q3 Subscribe & Save

💰

Customer Lifetime Value

3 queries

Q4 Cohort LTV Analysis
Q5 Cross-Sell Paths
Q6 Time to Second Purchase

📈

Ad Spend Tracking

3 queries

Q7 Sponsored Ads Spend
Q8 DSP Spend by Campaign
Q9 SB/SBV/SD Spend

🎯

Attribution & Acquisition

6 queries

How AMC Data Flows

Understanding the end-to-end workflow, from raw AMC tables to actionable dashboards:

🗄️

AMC Tables

amazon_attributed_events dsp_impressions sponsored_ads_traffic amazon_retail_purchases dsp_clicks

⚡

AMC Queries

SQL queries run inside AMC UI 15 ready-to-run

📤

CSV Export

Download results as CSV files

📊

Excel / Sheets

Pivot tables Charts & graphs Filter & sort

Reference

Quick Reference Table

All 15 queries at a glance. Click any query name to jump to its full documentation.

#	Query Name	Category	Tables Used	Subscription	Est. Rows
Q1	Monthly Revenue & Customers by ASIN	Revenue	`amazon_retail_purchases`	💲 Paid	~600
Q2	Purchase Frequency Distribution	Revenue	`amazon_retail_purchases`	💲 Paid	~5
Q3	Subscribe & Save Analysis	Revenue	`conversions_all`	💲 Paid	~5,600
Q4	Cohort LTV Analysis	LTV	`amazon_retail_purchases`	💲 Paid	~1,800
Q5	Cross-Sell Paths	LTV	`amazon_retail_purchases`	💲 Paid	~4,600
Q6	Time to Second Purchase	LTV	`amazon_retail_purchases`	💲 Paid	~185
Q7	Sponsored Ads Spend by ASIN	Spend	`sponsored_ads_traffic`	✓ Free	~1,500
Q8	DSP Spend by Campaign	Spend	`dsp_impressions`	✓ Free	~175
Q9	SB/SBV/SD Spend by Campaign	Spend	`sponsored_ads_traffic`	✓ Free	~200
Q10	Organic vs Paid First Touch	Attribution	`amazon_retail_purchases` `sponsored_ads_traffic` `dsp_clicks`	💲 Paid	~120
Q11	Full-Funnel Attribution	Attribution	`amazon_attributed_events_by_conversion_time`	✓ Free	~19,000
Q12	Organic vs Paid by ASIN	Attribution	`amazon_retail_purchases` `sponsored_ads_traffic` `dsp_clicks`	💲 Paid	~3,000–5,000
Q13	NTB vs Repeat by ASIN	Attribution	`amazon_retail_purchases`	💲 Paid	~5,000–10,000
Q14	First-Touch Attribution by Channel	Attribution	`amazon_attributed_events_by_conversion_time` `sponsored_ads_traffic` `dsp_impressions`	✓ Free	~5
Q15	Path to Purchase	Attribution	`sponsored_ads_traffic` `dsp_impressions` `amazon_retail_purchases`	💲 Paid	~500–2,000

Category 1

Revenue & Customer Analytics

These queries extract core business metrics from amazon_retail_purchases: monthly revenue, customer counts, purchase frequency, and subscription data. They form the foundation of any AMC analytics setup.

Q1 Monthly Revenue & Customers by ASIN 💲 Paid table required

The foundational revenue query. Pulls monthly revenue, unique customer count, and units sold for every ASIN in your catalog. This is the starting point for any product-level financial analysis. Use it to identify top sellers, track growth trends, and build category-level dashboards.

Output Columns

Column	Type	Description
`purchase_month`	TIMESTAMP	First day of the month (truncated)
`asin`	TEXT	Product ASIN
`asin_name`	TEXT	Product name as stored in AMC
`unique_customers`	INTEGER	Count of distinct buyers
`revenue`	DECIMAL	Total revenue (unit_price × units_sold)
`units`	INTEGER	Total units sold

AMC Tables Used

amazon_retail_purchases

SQL Query

Query 1: Monthly Revenue & Customers by ASIN SELECT DATE_TRUNC('MONTH', purchase_date_utc) AS purchase_month, asin, asin_name, COUNT(DISTINCT user_id) AS unique_customers, SUM(unit_price * purchase_units_sold) AS revenue, SUM(purchase_units_sold) AS units FROM amazon_retail_purchases GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Monthly Revenue & Customers by ASIN

Top products for January 2026: revenue, unique customers, and units sold

purchase_month	asin	asin_name	unique_customers	revenue	units
2026-01	B0EXAMPLE01	Cat Litter 40LB	2,530	$104,200	2,930
2026-01	B0EXAMPLE02	Puppy Pads 100ct	1,110	$28,200	1,280
2026-01	B0EXAMPLE03	Waste Bags 600ct	930	$18,800	1,010
2026-01	B0EXAMPLE04	Pet Shampoo 32oz	680	$16,200	770
2026-01	B0EXAMPLE05	Litter Mat XL	590	$15,000	660
2026-01	B0EXAMPLE06	Pet Wipes 400ct	450	$10,200	490

Top 5 ASINs by Revenue: January 2026

Cat Litter 40LB

$104,200

Puppy Pads

$28,200

Waste Bags

$18,800

Pet Shampoo

$16,200

Litter Mat XL

$15,000

💡 Pro Tip: Revenue Units

Revenue in amazon_retail_purchases is calculated from unit_price × purchase_units_sold. Unlike ad spend, this is already in dollars, so no conversion is needed.

Recommended Pivot Table Setup:

Rows: ASIN (or product category tag)
Columns: purchase_month
Values: SUM of revenue, SUM of unique_customers

Best Chart Types:

Line chart: Revenue trends over time by product category
Stacked bar chart: Revenue composition by category per month
Heatmap: ASIN × month matrix to spot seasonal patterns

Product Tagging: Create an ASIN-to-category mapping table (e.g., "Litter", "Grooming", "Training"). Add a VLOOKUP column to group ASINs into categories, then pivot by category for cleaner dashboards.

Calculated Fields to Add:

Revenue per Customer = revenue / unique_customers
Average Order Value = revenue / units (approximate)
MoM Growth % = (this_month - last_month) / last_month

Q2 Purchase Frequency Distribution 💲 Paid table required

Segments your entire customer base by how many orders they've placed (1, 2, 3–5, 6–10, 11+). Reveals the repeat purchase rate and shows how LTV scales with purchase frequency. Essential for understanding customer retention and the value of repeat buyers.

Output Columns

Column	Type	Description
`order_bucket`	TEXT	Frequency segment (1_order, 2_orders, 3-5_orders, etc.)
`customers`	INTEGER	Number of customers in this bucket
`avg_ltv`	DECIMAL	Average lifetime revenue per customer
`avg_unique_products`	DECIMAL	Average number of distinct ASINs purchased

AMC Tables Used

amazon_retail_purchases

SQL Query

Query 2: Purchase Frequency Distribution WITH customer_stats AS ( SELECT user_id, COUNT(DISTINCT purchase_id) AS total_orders, SUM(unit_price * purchase_units_sold) AS lifetime_revenue, COUNT(DISTINCT asin) AS unique_asins FROM amazon_retail_purchases GROUP BY user_id ) SELECT CASE WHEN total_orders = 1 THEN '1_order' WHEN total_orders = 2 THEN '2_orders' WHEN total_orders BETWEEN 3 AND 5 THEN '3-5_orders' WHEN total_orders BETWEEN 6 AND 10 THEN '6-10_orders' ELSE '11+_orders' END AS order_bucket, COUNT(DISTINCT user_id) AS customers, AVG(lifetime_revenue) AS avg_ltv, AVG(unique_asins) AS avg_unique_products FROM customer_stats GROUP BY 1

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Purchase Frequency Distribution

How many orders customers place over their lifetime, with average LTV per bucket

order_bucket	customer_count	pct_of_total	avg_ltv	avg_units
1 order	23,050	68.2%	$32	1.2
2 orders	5,920	17.5%	$71	2.6
3–5 orders	3,440	10.2%	$134	5.1
6–10 orders	1,010	3.0%	$292	11.3
11+ orders	370	1.1%	$684	26.8

Customer Distribution

23.1K

68%

5.9K

3.4K

3–5

1.0K

6–10

370

11+

Orders per Customer

Average LTV by Frequency

$32

$71

3–5

$134

6–10

$292

11+

$684

Orders per Customer → Avg Lifetime Revenue

🔑 Key Insight

In most Amazon brands, 60% of customers are one-time buyers. But the 40% who come back often account for 70%+ of total revenue. This query quantifies that exact split for your brand.

Best Chart Types:

Bar chart: Customers by order bucket (shows the distribution)
Bar chart with secondary axis: Customers (bars) vs. avg_ltv (line) by bucket
Pie/donut chart: % of total customers in each bucket

Calculated Fields to Add:

% of Total Customers = customers / SUM(customers)
Total Revenue per Bucket = customers × avg_ltv (shows total revenue contribution)
LTV Multiplier = avg_ltv / avg_ltv_of_1_order_bucket

Key Question to Answer: What is the LTV multiplier from 1-order to 2-orders? If it's 3x, every dollar spent driving repeat purchases generates 3x the value.

Q3 Subscribe & Save Analysis 💲 Paid table required

Adds the purchase_retail_program dimension to break out Subscribe & Save orders from standard purchases. Helps quantify subscription revenue and identify which products have the strongest S&S adoption. Note: S&S data availability varies by AMC instance.

Output Columns

Column	Type	Description
`purchase_month`	TIMESTAMP	First day of the month
`tracked_asin`	TEXT	Product ASIN
`purchase_retail_program`	TEXT	Purchase program (e.g., Subscribe & Save, or empty)
`units`	INTEGER	Total units sold
`revenue`	DECIMAL	Total revenue

AMC Tables Used

conversions_all

SQL Query

Query 3: Subscribe & Save Analysis SELECT DATE_TRUNC('MONTH', CAST(event_date_utc AS TIMESTAMP)) AS purchase_month, tracked_asin, purchase_retail_program, SUM(total_units_sold) AS units, SUM(total_product_sales) AS revenue FROM conversions_all GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Subscribe & Save Analysis

S&S enrollment and retention, comparing subscription vs one-time buyers

purchase_month	tracked_asin	purchase_retail_program	units	revenue
2026-01	B0EXAMPLE01	(empty)	2,930	$104,200
2026-01	B0EXAMPLE02	(empty)	1,280	$28,200
2026-01	B0EXAMPLE03	(empty)	1,010	$18,800
2026-01	B0EXAMPLE04	(empty)	770	$16,200

⚠️ S&S Data Not Available

The purchase_retail_program field returned empty for all rows. This is common when the AMC instance doesn't have Subscribe & Save data populated, or when the brand doesn't have S&S enabled on their products. The query still runs; it just won't differentiate between S&S and one-time purchases.

What it would look like with S&S data Example

purchase_month	tracked_asin	purchase_retail_program	units	revenue
2026-01	B0EXAMPLE01	Subscribe & Save	1,150	$39,000
2026-01	B0EXAMPLE01	One-Time Purchase	1,780	$65,200
2026-01	B0EXAMPLE02	Subscribe & Save	370	$8,000
2026-01	B0EXAMPLE02	One-Time Purchase	910	$20,200

⚠️ Known Issue: Empty S&S Data

purchase_retail_program may return empty for all rows in some AMC instances. This is a known limitation: S&S data availability varies. You can also check for sns_subscription_id in conversions_all as an alternative indicator.

Recommended Pivot Table Setup:

Rows: ASIN or product category
Columns: purchase_retail_program
Values: SUM of revenue, SUM of units

Best Chart Types:

Stacked bar: S&S vs standard revenue by product category
Line chart: S&S adoption rate over time (S&S revenue / total revenue)

Calculated Fields:

S&S Adoption Rate = S&S revenue / total revenue per ASIN
S&S Revenue % = S&S revenue / total revenue (brand-wide)

If purchase_retail_program returns empty, this query still works as a basic revenue query with an extra empty column; use it as a fallback.

Category 2

Customer Lifetime Value

These queries analyze customer behavior over time: cohort retention, cross-sell paths, and repurchase timing. Together they paint a complete picture of how customers interact with your brand after their first purchase.

Q4 Cohort LTV Analysis 💲 Paid table required

Groups customers into monthly cohorts based on their first purchase date, then tracks their cumulative revenue and activity over subsequent months. This is the gold standard for LTV analysis. It shows you exactly how much revenue each cohort generates over time and whether your newer cohorts are more or less valuable than older ones.

Output Columns

Column	Type	Description
`cohort_month`	TIMESTAMP	Month of first purchase (defines the cohort)
`revenue_month`	TIMESTAMP	Month when revenue was generated
`active_customers`	INTEGER	Customers from the cohort who purchased in this month
`revenue`	DECIMAL	Total revenue from this cohort in this month
`units`	INTEGER	Total units sold

AMC Tables Used

amazon_retail_purchases

SQL Query

Query 4: Cohort LTV Analysis WITH first_purchase AS ( SELECT user_id, MIN(DATE_TRUNC('MONTH', purchase_date_utc)) AS cohort_month FROM amazon_retail_purchases GROUP BY user_id ) SELECT fp.cohort_month, DATE_TRUNC('MONTH', rp.purchase_date_utc) AS revenue_month, COUNT(DISTINCT rp.user_id) AS active_customers, SUM(rp.unit_price * rp.purchase_units_sold) AS revenue, SUM(rp.purchase_units_sold) AS units FROM amazon_retail_purchases rp INNER JOIN first_purchase fp ON rp.user_id = fp.user_id GROUP BY 1, 2

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Cohort LTV Analysis

Revenue per customer by acquisition cohort over time

cohort_month	revenue_month	active_customers	revenue	units
2025-07	2025-07	3,510	$89,300	4,030
2025-07	2025-08	690	$18,900	850
2025-07	2025-09	410	$11,200	510
2025-08	2025-08	3,670	$94,200	4,250
2025-08	2025-09	750	$20,500	920
2025-09	2025-09	3,290	$84,600	3,810

Revenue Retention Heatmap

Avg revenue per customer (darker = higher retention value)

Jul '25Aug '25Sep '25 Oct '25Nov '25Dec '25

Mo 0Mo 1Mo 2Mo 3Mo 4Mo 5Mo 6

$25

$11

$26

$11

$26

$12

$24

$10

$29

$13

$28

$11

Low

High

💡 Pro Tip: Building LTV Curves

Post-export, calculate months_since_first = DATEDIF(cohort_month, revenue_month, "M") and create a cumulative sum of revenue per cohort. Plot these as line charts where X = months since first purchase and each line = a cohort. This gives you classic LTV curves showing how revenue accumulates over time.

Recommended Pivot Table Setup:

Rows: cohort_month
Columns: months_since_first (calculated: DATEDIF or manual month difference)
Values: SUM of revenue (or CUMULATIVE SUM for LTV curves)

Best Chart Types:

Line chart (LTV curves): X = months since acquisition, Y = cumulative revenue per customer, each line = a cohort. The most important chart in this guide.
Heatmap: Cohort × month matrix showing revenue density. Dark cells = high activity.
Retention chart: active_customers / cohort_size per month (requires joining with Q1 for initial cohort size)

Calculated Fields:

Months Since First = (YEAR(revenue_month) - YEAR(cohort_month)) × 12 + (MONTH(revenue_month) - MONTH(cohort_month))
Cumulative Revenue = Running sum of revenue within each cohort
LTV per Customer = Cumulative Revenue / Cohort Size
Retention Rate = active_customers / initial cohort size

Q5 Cross-Sell Paths 💲 Paid table required

Identifies what customers buy second after their first purchase. For each first-ASIN → second-ASIN pair, shows how many customers followed that path. Use this to optimize product bundling, recommendation widgets, and advertising strategy. Knowing that 500 customers who bought Product A next bought Product B is gold for targeting.

Output Columns

Column	Type	Description
`first_asin`	TEXT	The ASIN of the customer's first purchase
`second_asin`	TEXT	The ASIN of the customer's second purchase
`customer_count`	INTEGER	Number of customers who followed this path (min 10)

AMC Tables Used

amazon_retail_purchases

SQL Query

Query 5: Cross-Sell Paths WITH ranked AS ( SELECT user_id, asin, purchase_day_utc, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_day_utc, asin) AS rn FROM amazon_retail_purchases ) SELECT r1.asin AS first_asin, r2.asin AS second_asin, COUNT(DISTINCT r1.user_id) AS customer_count FROM ranked r1 INNER JOIN ranked r2 ON r1.user_id = r2.user_id AND r2.rn = 2 WHERE r1.rn = 1 GROUP BY 1, 2 HAVING COUNT(DISTINCT r1.user_id) >= 10

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Cross-Sell Paths (First → Second ASIN)

Most common product journeys: what customers buy after their first purchase

first_asin	first_product	second_asin	second_product	customer_count
B0EXAMPLE01	Cat Litter 40LB	B0EXAMPLE01	Cat Litter 40LB (repeat)	1,700
B0EXAMPLE01	Cat Litter 40LB	B0EXAMPLE03	Waste Bags 600ct	370
B0EXAMPLE02	Puppy Pads	B0EXAMPLE02	Puppy Pads (repeat)	310
B0EXAMPLE03	Waste Bags	B0EXAMPLE01	Cat Litter 40LB	230
B0EXAMPLE04	Pet Shampoo	B0EXAMPLE04	Pet Shampoo (repeat)	210
B0EXAMPLE01	Cat Litter 40LB	B0EXAMPLE05	Litter Mat XL	130

Top Cross-Sell Flows

Line thickness = relative purchase volume

First Purchase

Second Purchase

Cat Litter 40LB

Puppy Pads

Waste Bags

Pet Shampoo

Litter Mat XL

Cat Litter 40LB

Waste Bags

Puppy Pads

Pet Shampoo

Litter Mat XL

🔑 Key Insight

The HAVING >= 10 filter removes noise from low-volume paths. If you want more granularity, lower it to 5, but be aware this increases output rows significantly. For brands with 100+ ASINs, keep it at 10 or higher.

Best Analysis Approach:

Map ASINs to product names/categories using a VLOOKUP column
Filter by a specific first_asin to answer: "What do customers buy after buying Product X?"
Sort by customer_count descending (remember: sort post-export, not in AMC)

Best Chart Types:

Horizontal bar chart: For a given entry product, top 10 second purchases by customer count
Sankey diagram: First product → Second product flow (use a tool like SankeyMATIC)
Pivot table: Rows = first_asin category, Columns = second_asin category, Values = SUM(customer_count)

Actionable Insights:

If first_asin == second_asin for many customers → strong repurchase signal for that product
High cross-sell pairs → create Amazon bundles or Sponsored Display retargeting campaigns
Use top paths to inform DSP audience strategy: target buyers of Product A with ads for Product B

Q6 Time to Second Purchase 💲 Paid table required

Measures how long it takes customers to come back for a second purchase, broken down by their first ASIN. Shows both average and median days to repeat. The median is more useful since a few extreme outliers can skew the average. Use this to time your retargeting campaigns and email follow-ups.

Output Columns

Column	Type	Description
`first_asin`	TEXT	The ASIN of the customer's first purchase
`customers_with_repeat`	INTEGER	Number of customers who made a second purchase (min 10)
`avg_days_to_repeat`	DECIMAL	Average days between first and second purchase
`median_days_to_repeat`	DECIMAL	Median days between first and second purchase

AMC Tables Used

amazon_retail_purchases

SQL Query

Query 6: Time to Second Purchase WITH ranked AS ( SELECT user_id, asin, purchase_dt_utc, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_dt_utc, asin) AS rn FROM amazon_retail_purchases ) SELECT f.asin AS first_asin, COUNT(DISTINCT f.user_id) AS customers_with_repeat, AVG(SECONDS_BETWEEN(f.purchase_dt_utc, s.purchase_dt_utc) / 86400.0) AS avg_days_to_repeat, MEDIAN(SECONDS_BETWEEN(f.purchase_dt_utc, s.purchase_dt_utc) / 86400.0) AS median_days_to_repeat FROM ranked f INNER JOIN ranked s ON f.user_id = s.user_id AND s.rn = 2 WHERE f.rn = 1 GROUP BY 1 HAVING COUNT(DISTINCT f.user_id) >= 10

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Time to Second Purchase

How long it takes customers to come back, by first product purchased

first_asin	product_name	customers_with_repeat	avg_days	median_days
B0EXAMPLE01	Cat Litter 40LB	2,300	38.2	32
B0EXAMPLE03	Waste Bags 600ct	670	42.6	36
B0EXAMPLE02	Puppy Pads 100ct	570	45.1	38
B0EXAMPLE04	Pet Shampoo 32oz	370	52.8	45
B0EXAMPLE05	Litter Mat XL	290	48.3	41
B0EXAMPLE06	Pet Wipes 400ct	190	56.4	48

Median Days to Second Purchase by Product

Cat Litter 40LB

32 days

Waste Bags

36 days

Puppy Pads

38 days

Litter Mat XL

41 days

Pet Shampoo

45 days

Pet Wipes

48 days

💡 AMC Date Math

AMC doesn't have a DATEDIFF function. Use SECONDS_BETWEEN(start, end) / 86400.0 to convert to days. This works with purchase_dt_utc (TIMESTAMP type); don't use the INTEGER date columns.

Best Chart Types:

Bar chart: Median days to repeat by product category (sorted ascending)
Scatter plot: X = median_days_to_repeat, Y = customers_with_repeat (identifies high-volume fast-repeat products)

Product Category Grouping: Map first_asin to categories, then calculate weighted averages within each category. Consumable products (litter, waste bags) should have shorter repeat cycles than durable goods.

Actionable Insights:

If median is 45 days → schedule retargeting to start at day 30
If avg ≫ median → you have long-tail repeat customers pulling the average up. The median is your true signal.
Products with short repeat cycles are prime candidates for Subscribe & Save pushes

✦ ADLABS FEATURE

Semi-Automatic Campaign Management

You set the targets and guardrails. AdLabs handles bid optimization with math you can check, keeps your campaign structure clean with two-click harvesting, and logs every change. Spend less time in the weeds and more time scaling what works.

Try Free →

Category 3

Ad Spend Tracking

These queries pull ad spend from Sponsored Ads and DSP, broken down by ASIN and campaign. Combined with the revenue queries in Category 1, they let you calculate true Customer Acquisition Cost (CAC) and ROAS at the product level.

Q7 Sponsored Ads Spend by ASIN (Monthly) ✓ Free

Pulls monthly Sponsored Products, Brands, and Display spend broken down by the creative ASIN and ad product type. This is your primary spend-by-product query for Sponsored Ads. Note that SB/SBV campaigns may not have a creative_asin; use Q9 for campaign-level SB/SD spend.

Output Columns

Column	Type	Description
`spend_month`	TIMESTAMP	First day of the month
`creative_asin`	TEXT	ASIN associated with the ad creative
`ad_product_type`	TEXT	sponsored_products, sponsored_brands, or sponsored_display
`ad_spend_dollars`	DECIMAL	Ad spend in dollars (converted from microcents)
`clicks`	INTEGER	Total clicks
`impressions`	INTEGER	Total impressions

AMC Tables Used

sponsored_ads_traffic

SQL Query

Query 7: Sponsored Ads Spend by ASIN (Monthly) SELECT DATE_TRUNC('MONTH', event_date_utc) AS spend_month, creative_asin, ad_product_type, SUM(spend) / 100000000.0 AS ad_spend_dollars, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM sponsored_ads_traffic GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

spend_month	creative_asin	ad_product_type	spend	clicks	impressions
2025-10	B0EXAMPLE01	Sponsored Products	$11,050	14,510	1,105,000
2025-10	B0EXAMPLE01	Sponsored Brands	$2,920	3,250	374,000
2025-10	B0EXAMPLE01	Sponsored Display	$1,280	1,910	292,000
2025-11	B0EXAMPLE01	Sponsored Products	$13,610	17,120	1,274,000
2025-11	B0EXAMPLE01	Sponsored Brands	$3,740	4,130	445,000
2025-11	B0EXAMPLE01	Sponsored Display	$1,910	2,710	341,000

Sponsored Display

⚠️ Microcents Conversion

Sponsored Ads spend is in microcents. Dividing by 100,000,000 (10⁸) converts to dollars. If your spend looks like it's in the trillions, you forgot this step. If it looks like it's in the pennies, you divided by the wrong number.

Recommended Pivot Table Setup:

Rows: creative_asin (mapped to product category)
Columns: spend_month
Values: SUM of ad_spend_dollars
Filter: ad_product_type (to slice by SP vs SB vs SD)

Calculated Fields:

CPC = ad_spend_dollars / clicks
CTR = clicks / impressions
CPM = (ad_spend_dollars / impressions) × 1000

Combine with Q1: Join this data with Q1 output on ASIN + month to calculate ACOS = ad_spend / revenue and CAC = ad_spend / new_customers.

Q8 DSP Spend by Campaign (Monthly) ✓ Free

Pulls monthly DSP spend at the campaign level from the dsp_impressions table. DSP campaigns don't have ASIN-level granularity in their spend data, so this groups by campaign name and ID. Use campaign naming conventions to map spend back to product categories.

Output Columns

Column	Type	Description
`spend_month`	TIMESTAMP	First day of the month
`campaign`	TEXT	DSP campaign name
`campaign_id`	TEXT	DSP campaign ID
`dsp_spend_dollars`	DECIMAL	DSP spend in dollars (converted from millicents)
`impressions`	INTEGER	Total impressions served

AMC Tables Used

dsp_impressions

SQL Query

Query 8: DSP Spend by Campaign (Monthly) SELECT DATE_TRUNC('MONTH', impression_date_utc) AS spend_month, campaign, campaign_id, SUM(total_cost) / 100000.0 AS dsp_spend_dollars, SUM(impressions) AS impressions FROM dsp_impressions GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

DSP Spend by Campaign (Monthly)

Amazon DSP programmatic spend and impression volume by campaign

spend_month	campaign	dsp_spend_dollars	impressions
2025-10	Litter - DSP - Retargeting - Views	$5,050	1,284,000
2025-10	Pet Care - DSP - Prospecting - InMarket	$3,170	1,116,000
2025-11	Litter - DSP - Retargeting - Views	$6,980	1,704,000
2025-11	Pet Care - DSP - Prospecting - InMarket	$4,270	1,428,000
2025-12	Litter - DSP - Retargeting - Views	$5,920	1,512,000

Total Monthly DSP Spend Trend

$15K$12K$9K$6K$3K$0

Sep '25Oct '25Nov '25Dec '25Jan '26

⚠️ Millicents ≠ Microcents

DSP total_cost uses millicents (÷ 100,000 for dollars), while Sponsored Ads spend uses microcents (÷ 100,000,000). Don't mix them up; it's a 1,000x difference.

Recommended Pivot Table Setup:

Rows: campaign (or extracted campaign category)
Columns: spend_month
Values: SUM of dsp_spend_dollars, SUM of impressions

Calculated Fields:

CPM = (dsp_spend_dollars / impressions) × 1000
% of Total DSP Spend = campaign_spend / SUM(all_campaign_spend)

Campaign Category Mapping: If your DSP campaigns follow a naming convention (e.g., "Retargeting - Litter" or "Prospecting - Grooming"), extract the category prefix to group spend. Use LEFT(campaign, FIND(" - ", campaign)-1) or similar text formula.

Q9 SB/SBV/SD Spend by Campaign (Monthly) ✓ Free

Extracts Sponsored Brands (including SBV) and Sponsored Display spend at the campaign level. Unlike Q7 which groups by creative_asin, SB and SD campaigns often don't have a meaningful creative ASIN. This query uses campaign names, which typically contain category prefixes like "Litter - NBR - SBV...", to enable category-level spend mapping.

Output Columns

Column	Type	Description
`spend_month`	TIMESTAMP	First day of the month
`campaign`	TEXT	Campaign name (contains category prefixes)
`ad_product_type`	TEXT	sponsored_brands or sponsored_display
`ad_spend_dollars`	DECIMAL	Spend in dollars (converted from microcents)
`clicks`	INTEGER	Total clicks
`impressions`	INTEGER	Total impressions

AMC Tables Used

sponsored_ads_traffic

SQL Query

Query 9: SB/SBV/SD Spend by Campaign (Monthly) SELECT DATE_TRUNC('MONTH', event_date_utc) AS spend_month, campaign, ad_product_type, SUM(spend) / 100000000.0 AS ad_spend_dollars, SUM(clicks) AS clicks, SUM(impressions) AS impressions FROM sponsored_ads_traffic WHERE ad_product_type IN ('sponsored_brands', 'sponsored_display') GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

SB/SBV & SD Spend by Campaign

Sponsored Brands (including Video) and Sponsored Display spend breakdown

spend_month	campaign	ad_product_type	spend	clicks	impressions
2025-10	Brand - SB - Top of Search	Sponsored Brands	$1,940	2,320	251,000
2025-10	Litter - SBV - Product Page	Sponsored Brands Video	$970	1,280	172,000
2025-10	Litter - SD - Retargeting	Sponsored Display	$890	1,340	205,000
2025-11	Brand - SB - Top of Search	Sponsored Brands	$2,510	2,770	314,000
2025-11	Litter - SBV - Product Page	Sponsored Brands Video	$1,240	1,610	209,000
2025-11	Litter - SD - Retargeting	Sponsored Display	$1,700	2,350	292,000

Monthly SB vs SD Spend Comparison

$3.0K

Sep

$3.8K

Oct

$5.4K

Nov

$4.8K

Dec

$4.3K

Jan

Sponsored Display

💡 Campaign Name Category Mapping

Extract the first word or phrase before the first " - " delimiter in the campaign name to identify the product category. For example, "Litter - NBR - SBV Top" → category = "Litter". This lets you spread SB/SD spend across product categories in a CAC/LTV report.

Key Steps:

Add a "Category" column using text extraction from campaign name
Pivot by Category × spend_month for a clean spend-over-time view
Combine with Q7 data to get total Sponsored spend (SP from Q7 + SB/SD from Q9)

Best Chart Types:

Stacked bar: SB vs SD spend by category
Line chart: Monthly SB/SD spend trends by category
Treemap: Share of SB/SD spend by category

Why This Matters for CAC: Without Q9, you'd only have SP spend by ASIN (Q7). Many brands allocate significant budget to SB/SBV campaigns that don't have ASIN-level tracking. This query fills that gap by letting you map SB/SD spend to categories via campaign names.

Category 4

Attribution & Acquisition

The most powerful queries in the library. These join purchase data with ad interaction data to determine how customers were acquired (organic vs. paid), attribute conversions to specific campaigns, and classify buyers as new-to-brand or repeat, all at the product level.

Q10 Organic vs Paid First Touch (Account-Level) 💲 Paid table required

For each new customer, determines whether they ever clicked a Sponsored Ad or DSP ad before their first purchase. Classifies customers as "paid_touched" or "organic_only" and groups by cohort month. This is the simplest organic/paid split. It answers: "What percentage of our new customers were influenced by ads?"

Output Columns

Column	Type	Description
`cohort_month`	TIMESTAMP	Month of the customer's first purchase
`acquisition_type`	TEXT	'paid_touched' or 'organic_only'
`new_customers`	INTEGER	Number of new customers in this segment

AMC Tables Used

amazon_retail_purchases sponsored_ads_traffic dsp_clicks

SQL Query

Query 10: Organic vs Paid First Touch (Account-Level) WITH first_purchase AS ( SELECT user_id, MIN(DATE_TRUNC('MONTH', purchase_date_utc)) AS cohort_month FROM amazon_retail_purchases GROUP BY user_id ), ad_touched AS ( SELECT user_id, 1 AS is_paid FROM sponsored_ads_traffic WHERE clicks > 0 GROUP BY user_id UNION SELECT user_id, 1 AS is_paid FROM dsp_clicks WHERE clicks > 0 GROUP BY user_id ) SELECT fp.cohort_month, CASE WHEN adtch.user_id IS NOT NULL THEN 'paid_touched' ELSE 'organic_only' END AS acquisition_type, COUNT(DISTINCT fp.user_id) AS new_customers FROM first_purchase fp LEFT JOIN ad_touched adtch ON fp.user_id = adtch.user_id GROUP BY 1, 2

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Organic vs Paid First Touch

How new customers discovered the brand: organic search/browse vs ad-attributed

cohort_month	acquisition_type	new_customers
2025-08	Organic	2,300
2025-08	Paid	1,370
2025-09	Organic	2,170
2025-09	Paid	1,480
2025-10	Organic	2,470
2025-10	Paid	1,700

New Customer Acquisition: Organic vs Paid (Monthly)

3,670

Aug

3,650

Sep

4,170

Oct

4,860

Nov

4,380

Dec

3,960

Jan

Organic

Paid (Ad-Attributed)

🔑 "Ad Touched" vs "Ad Attributed"

This query uses a broad definition: a customer is "paid_touched" if they ever clicked any ad, even if that click wasn't directly before their purchase. This overestimates paid attribution compared to last-click models, but it's the correct approach for understanding ad influence across the full customer journey.

Recommended Pivot Table Setup:

Rows: cohort_month
Columns: acquisition_type
Values: SUM of new_customers

Best Chart Types:

100% stacked bar: Organic vs paid mix over time (shows trend shifts)
Line chart: Organic % over time: organic / (organic + paid)
Area chart: Stacked area showing absolute new customer volume by type

Key Metrics:

Organic % = organic_only / (organic_only + paid_touched) × 100
Paid CAC = Total ad spend (Q7 + Q8 + Q9) / paid_touched customers
Blended CAC = Total ad spend / ALL new customers

Q11 Full-Funnel Attribution ✓ Free

Amazon's own attribution model, using the amazon_attributed_events_by_conversion_time table. Provides campaign-level attribution with new-to-brand flags, broken down by ad product type, campaign, and tracked ASIN. This is the most detailed attribution data available in AMC, but it only covers ad-attributed events (up to 25 months of data).

Output Columns

Column	Type	Description
`conversion_month`	TIMESTAMP	Month when the conversion occurred
`ad_product_type`	TEXT	sponsored_products, sponsored_brands, sponsored_display, or NULL for DSP
`campaign`	TEXT	Campaign name
`tracked_asin`	TEXT	ASIN attributed to the conversion
`new_to_brand`	BOOLEAN	Whether the purchaser is new to the brand
`customers`	INTEGER	Unique customers
`purchases`	INTEGER	Total purchases
`attributed_revenue`	DECIMAL	Total attributed product sales
`ntb_purchases`	INTEGER	New-to-brand purchase count
`ntb_revenue`	DECIMAL	New-to-brand attributed revenue

AMC Tables Used

amazon_attributed_events_by_conversion_time

SQL Query

Query 11: Full-Funnel Attribution SELECT DATE_TRUNC('MONTH', CAST(conversion_event_date_utc AS TIMESTAMP)) AS conversion_month, ad_product_type, campaign, tracked_asin, new_to_brand, COUNT(DISTINCT user_id) AS customers, SUM(purchases) AS purchases, SUM(total_product_sales) AS attributed_revenue, SUM(new_to_brand_purchases) AS ntb_purchases, SUM(new_to_brand_total_product_sales) AS ntb_revenue FROM amazon_attributed_events_by_conversion_time WHERE purchases > 0 GROUP BY 1, 2, 3, 4, 5

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Full-Funnel Attribution (Ad-Attributed Purchases)

Revenue attributed to each ad type: which channels drive conversions

conversion_month	ad_product_type	campaign	tracked_asin	customers	attributed_revenue
2026-01	Sponsored Products	Litter - SP - Exact - Top Keywords	B0EXAMPLE01	1,100	$44,900
2026-01	Sponsored Products	Bags - SP - Auto	B0EXAMPLE03	370	$13,800
2026-01	Sponsored Brands	Brand - SB - Top of Search	B0EXAMPLE01	290	$11,600
2026-01	Sponsored Display	Litter - SD - Retargeting	B0EXAMPLE01	170	$6,800
2026-01	DSP	Pet Care - DSP - Prospecting	B0EXAMPLE01	110	$4,500

Attributed Revenue by Ad Type: January 2026

Output Columns

Column	Type	Description
`cohort_month`	TIMESTAMP	Month of first purchase
`asin`	TEXT	ASIN of first purchase
`acquisition_type`	TEXT	'paid_touched' or 'organic_only'
`new_customers`	INTEGER	Number of new customers

AMC Tables Used

amazon_retail_purchases sponsored_ads_traffic dsp_clicks

SQL Query

Query 12: Organic vs Paid New Customer Acquisition by ASIN WITH first_purchase AS ( SELECT user_id, DATE_TRUNC('MONTH', purchase_date_utc) AS cohort_month, asin, purchase_date_utc, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY purchase_date_utc, asin) AS rn FROM amazon_retail_purchases ), first_only AS ( SELECT user_id, cohort_month, asin, purchase_date_utc FROM first_purchase WHERE rn = 1 ), ad_touched AS ( SELECT user_id, 1 AS is_paid FROM sponsored_ads_traffic WHERE clicks > 0 GROUP BY user_id UNION SELECT user_id, 1 AS is_paid FROM dsp_clicks WHERE clicks > 0 GROUP BY user_id ) SELECT fo.cohort_month, fo.asin, CASE WHEN adtch.user_id IS NOT NULL THEN 'paid_touched' ELSE 'organic_only' END AS acquisition_type, COUNT(DISTINCT fo.user_id) AS new_customers FROM first_only fo LEFT JOIN ad_touched adtch ON fo.user_id = adtch.user_id GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Organic vs Paid New Customers by ASIN

Per-product breakdown of how new customers were acquired

cohort_month	asin	product_name	acquisition_type	new_customers
2026-01	B0EXAMPLE01	Cat Litter 40LB	Organic	1,490
2026-01	B0EXAMPLE01	Cat Litter 40LB	Paid	1,040
2026-01	B0EXAMPLE02	Puppy Pads	Organic	740
2026-01	B0EXAMPLE02	Puppy Pads	Paid	370
2026-01	B0EXAMPLE03	Waste Bags	Organic	500
2026-01	B0EXAMPLE03	Waste Bags	Paid	430

New Customer Acquisition by Product: January 2026

1.5K

1.0K

Cat
Litter

740

370

Puppy
Pads

500

430

Waste Bags

410

270

Pet
Shampoo

350

240

Litter
Mat

Organic

Paid

💡 Why Q12 Over Q10

Q10 gives you the account-level organic/paid split. Q12 adds the ASIN dimension, letting you build product category filters. If product A is 90% organic and product B is 40% organic, you know exactly where your ads are driving acquisition and where organic brand strength carries the weight.

Recommended Pivot Table Setup:

Rows: ASIN (mapped to product category)
Columns: acquisition_type
Values: SUM of new_customers
Filter/Slicer: cohort_month (for time-based filtering)

Best Chart Types:

100% stacked bar: Organic vs paid % by product category
Grouped bar chart: Absolute new customers: organic vs paid per category
Line chart with category filter: Organic % trend over time for a selected category

Product Category Slicing: Create an ASIN → Category mapping, add as a column, then create a slicer/filter pill for category. This lets you toggle between "All Products", "Litter", "Grooming", etc. in your dashboard.

Q13 NTB vs Repeat Purchases by ASIN (Full History) 💲 Paid table required

Classifies every purchase as New-to-Brand or Repeat based on whether it's the customer's first month purchasing from your brand. Unlike Q11 (which covers up to 25 months of ad-attributed data), this query spans the full retail history (up to 5 years) and covers ALL purchases, not just ad-attributed ones. The definitive NTB analysis query.

Output Columns

Column	Type	Description
`purchase_month`	TIMESTAMP	Month of purchase
`asin`	TEXT	Product ASIN
`customer_type`	TEXT	'new_to_brand' or 'repeat'
`customers`	INTEGER	Unique buyers
`units`	INTEGER	Units sold
`revenue`	DECIMAL	Total revenue

AMC Tables Used

amazon_retail_purchases

SQL Query

Query 13: NTB vs Repeat Purchases by ASIN (Full History) WITH first_purchase AS ( SELECT user_id, MIN(purchase_date_utc) AS first_purchase_date FROM amazon_retail_purchases GROUP BY user_id ) SELECT DATE_TRUNC('MONTH', rp.purchase_date_utc) AS purchase_month, rp.asin, CASE WHEN DATE_TRUNC('MONTH', rp.purchase_date_utc) = DATE_TRUNC('MONTH', fp.first_purchase_date) THEN 'new_to_brand' ELSE 'repeat' END AS customer_type, COUNT(DISTINCT rp.user_id) AS customers, SUM(rp.purchase_units_sold) AS units, SUM(rp.unit_price * rp.purchase_units_sold) AS revenue FROM amazon_retail_purchases rp INNER JOIN first_purchase fp ON rp.user_id = fp.user_id GROUP BY 1, 2, 3

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

NTB vs Repeat Purchases by ASIN (Full History)

New-to-brand vs returning customers: monthly trend across all products

purchase_month	asin	product_name	customer_type	customers	units	revenue
2026-01	B0EXAMPLE01	Cat Litter 40LB	NTB	1,580	1,720	$70,700
2026-01	B0EXAMPLE01	Cat Litter 40LB	Repeat	950	1,210	$33,500
2026-01	B0EXAMPLE02	Puppy Pads	NTB	770	850	$19,500
2026-01	B0EXAMPLE02	Puppy Pads	Repeat	340	430	$8,700
2026-01	B0EXAMPLE03	Waste Bags	NTB	610	650	$12,400
2026-01	B0EXAMPLE03	Waste Bags	Repeat	320	360	$6,400

NTB vs Repeat Customers: Monthly Trend (All ASINs)

4,140

Aug

4,140

Sep

4,680

Oct

5,460

Nov

5,160

Dec

4,860

Jan

New-to-Brand (NTB)

Repeat Customers

🔑 NTB Definition

A customer is "new_to_brand" if their purchase occurs in the same month as their very first purchase from your brand. All subsequent months = repeat. This is conservative: even if they buy a new product for the first time, if they've purchased ANY product before, they're classified as repeat.

💡 Large Result Set

This query can return 5,000–10,000+ rows (ASIN × month × customer_type). If it times out in AMC, add HAVING COUNT(DISTINCT rp.user_id) >= 5 to filter out very low-volume ASINs.

Recommended Pivot Table Setup:

Rows: purchase_month
Columns: customer_type
Values: SUM of customers, SUM of revenue
Filter/Slicer: ASIN (mapped to product category)

Best Chart Types:

Stacked area chart: NTB vs repeat customers over time (shows the full history)
100% stacked bar: NTB % by product category
Line chart: NTB % trend over time (new_to_brand / total × 100)

Calculated Fields:

NTB % = new_to_brand_customers / (new_to_brand + repeat) × 100
NTB Revenue % = new_to_brand_revenue / total_revenue × 100
Revenue per NTB Customer = new_to_brand_revenue / new_to_brand_customers
Revenue per Repeat Customer = repeat_revenue / repeat_customers

Combine with Q12: Use Q12 (organic vs paid) + Q13 (NTB vs repeat) together to build a 2×2 matrix: Organic NTB, Organic Repeat, Paid NTB, Paid Repeat: the most complete customer acquisition view possible.

New-to-Brand vs Repeat Customers

Monthly customer acquisition breakdown

New-to-Brand

Repeat

1.2k9006003000

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Q14 First-Touch Attribution by Channel ✓ Free

For each converting customer, identifies their very first ad touchpoint and attributes the conversion to that channel. Shows SP, SB, SD, DSP, or Organic breakdown with NTB rates. Essential for understanding which channels drive initial awareness and which types of customers each channel attracts.

Output Columns

Column	Type	Description
`first_touch_channel`	TEXT	Channel of first ad touchpoint (Sponsored Products, Sponsored Brands, Sponsored Display, DSP, or Organic)
`customer_count`	INTEGER	Number of converting customers attributed to this channel
`total_revenue`	DECIMAL	Total revenue from these customers
`total_units`	INTEGER	Total units sold
`pct_of_customers`	DECIMAL	Percentage of total converting customers
`avg_revenue_per_customer`	DECIMAL	Average revenue per customer for this channel
`ntb_customer_count`	INTEGER	Number of new-to-brand customers
`ntb_pct`	DECIMAL	Percentage of customers who are NTB

AMC Tables Used

amazon_attributed_events_by_conversion_time sponsored_ads_traffic dsp_impressions

SQL Query

Query 14: First-Touch Attribution by Channel WITH conversions AS ( SELECT user_id, MAX(CASE WHEN new_to_brand = true THEN 1 ELSE 0 END) AS is_ntb, SUM(COALESCE(units_sold, 0)) AS total_units, SUM(COALESCE(total_product_sales, 0)) AS total_revenue FROM amazon_attributed_events_by_conversion_time WHERE conversion_event_date_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND total_purchases > 0 GROUP BY user_id ), sp_touches AS ( SELECT user_id, event_date_utc AS touch_time, 1 AS ch_code FROM sponsored_ads_traffic WHERE event_date_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND ad_product_type = 'sponsored_products' ), sb_touches AS ( SELECT user_id, event_date_utc AS touch_time, 2 AS ch_code FROM sponsored_ads_traffic WHERE event_date_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND ad_product_type = 'sponsored_brands' ), sd_touches AS ( SELECT user_id, event_date_utc AS touch_time, 3 AS ch_code FROM sponsored_ads_traffic WHERE event_date_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) AND ad_product_type = 'sponsored_display' ), dsp_touches AS ( SELECT user_id, impression_date_utc AS touch_time, 4 AS ch_code FROM dsp_impressions WHERE impression_date_utc BETWEEN CAST('2025-01-01' AS TIMESTAMP) AND CAST('2026-01-31' AS TIMESTAMP) ), all_touches AS ( SELECT user_id, touch_time, ch_code FROM sp_touches UNION ALL SELECT user_id, touch_time, ch_code FROM sb_touches UNION ALL SELECT user_id, touch_time, ch_code FROM sd_touches UNION ALL SELECT user_id, touch_time, ch_code FROM dsp_touches ), first_touch_ranked AS ( SELECT user_id, ch_code, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time ASC) AS rn FROM all_touches ), first_touch AS ( SELECT user_id, ch_code FROM first_touch_ranked WHERE rn = 1 ), attributed AS ( SELECT COALESCE(ft.ch_code, 0) AS ch_code, c.user_id, c.is_ntb, c.total_units, c.total_revenue FROM conversions c LEFT JOIN first_touch ft ON c.user_id = ft.user_id ), channel_stats AS ( SELECT ch_code, COUNT(DISTINCT user_id) AS customer_count, SUM(total_revenue) AS total_revenue, SUM(total_units) AS total_units, SUM(is_ntb) AS ntb_customer_count FROM attributed GROUP BY ch_code ), grand_total AS ( SELECT SUM(customer_count) AS total_customers FROM channel_stats ) SELECT CASE cs.ch_code WHEN 1 THEN 'Sponsored Products' WHEN 2 THEN 'Sponsored Brands' WHEN 3 THEN 'Sponsored Display' WHEN 4 THEN 'DSP' ELSE 'Organic' END AS first_touch_channel, cs.customer_count, cs.total_revenue, cs.total_units, ROUND(100.0 * cs.customer_count / NULLIF(gt.total_customers, 0), 2) AS pct_of_customers, ROUND(cs.total_revenue * 1.0 / NULLIF(cs.customer_count, 0), 2) AS avg_revenue_per_customer, cs.ntb_customer_count, ROUND(100.0 * cs.ntb_customer_count / NULLIF(cs.customer_count, 0), 2) AS ntb_pct FROM channel_stats cs CROSS JOIN grand_total gt

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

First-Touch Attribution by Channel

Which channel first introduced converting customers to your brand

first_touch_channel	customer_count	total_revenue	total_units	pct_of_customers	avg_revenue_per_customer	ntb_customer_count	ntb_pct
Sponsored Products	11,680	$310,300	14,600	60.8%	$26.57	7,610	65.2%
DSP	3,790	$120,800	4,740	19.7%	$31.87	1,280	33.8%
Sponsored Brands	2,390	$67,800	2,990	12.4%	$28.37	1,640	68.6%
Sponsored Display	790	$23,100	990	4.1%	$29.24	310	39.2%
Organic	550	$24,500	690	2.9%	$44.55	190	34.5%

Customers by First-Touch Channel

Which channel first introduced converting customers

Sponsored Display

790

Organic

550

NTB Rate by First-Touch Channel

Percentage of new-to-brand customers per channel

Sponsored Display

39.2%

Organic

34.5%

DSP

33.8%

💡 Pro Tip

Sponsored Brands typically shows the highest NTB rate because it captures customers during brand discovery searches. SP dominates volume (60%+) but SB punches above its weight for new customer acquisition. Organic first-touch customers convert at the highest revenue per customer, proof that brand awareness pays off. Use integer codes instead of strings in CTEs to avoid AMC dropping labels through UNION ALL chains. The hardcoded dates in Q14 and Q15 must sit inside the date range you pick in the AMC UI, or the query returns zero rows.

Key Metrics to Calculate:

Cost per First Touch: Combine with Q7/Q8 spend data to calculate acquisition cost by channel
NTB Cost per Acquisition: Channel spend ÷ ntb_customer_count
Revenue per NTB Customer: Filter to NTB only and compare avg_revenue across channels

Best Chart Types:

Horizontal bar chart: Customer count by channel (as shown above)
Grouped bar chart: NTB vs Repeat side by side per channel
Pie/donut chart: Share of customers by first-touch channel

Strategic Insight: If SP dominates first-touch (>50%), you're heavily search-dependent; consider diversifying into DSP/SB for top-of-funnel growth. If Organic first-touch customers show higher avg revenue, that validates upper-funnel investment to build brand awareness before the search moment.

Q15 Path to Purchase 💲 Paid table required

Maps the ad touchpoint journey for converting customers. It shows the first 5 touchpoints in sequence (e.g., "sponsored_products > dsp > sponsored_brands"). Reveals the most common paths customers take before purchasing, helping you understand which channel combinations drive the best outcomes.

Output Columns

Column	Type	Description
`path`	TEXT	Sequence of up to 5 ad touchpoints separated by " > "
`customers`	INTEGER	Number of customers who followed this path
`revenue`	DECIMAL	Total revenue from customers on this path
`rev_per_customer`	DECIMAL	Average revenue per customer on this path

AMC Tables Used

sponsored_ads_traffic dsp_impressions amazon_retail_purchases

SQL Query

Query 15: Path to Purchase WITH all_touches AS ( SELECT user_id, event_date_utc AS touch_time, ad_product_type AS ad_type FROM sponsored_ads_traffic WHERE event_date_utc >= CAST('2025-01-01' AS TIMESTAMP) AND event_date_utc < CAST('2026-02-01' AS TIMESTAMP) UNION ALL SELECT user_id, impression_date_utc AS touch_time, 'dsp' AS ad_type FROM dsp_impressions WHERE impression_date_utc >= CAST('2025-01-01' AS TIMESTAMP) AND impression_date_utc < CAST('2026-02-01' AS TIMESTAMP) ), numbered AS ( SELECT user_id, ad_type, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY touch_time) AS rn FROM all_touches ), pivoted AS ( SELECT user_id, MAX(CASE WHEN rn = 1 THEN ad_type END) AS t1, MAX(CASE WHEN rn = 2 THEN ad_type END) AS t2, MAX(CASE WHEN rn = 3 THEN ad_type END) AS t3, MAX(CASE WHEN rn = 4 THEN ad_type END) AS t4, MAX(CASE WHEN rn = 5 THEN ad_type END) AS t5 FROM numbered WHERE rn <= 5 GROUP BY user_id ), paths AS ( SELECT user_id, CONCAT( COALESCE(t1, ''), CASE WHEN t2 IS NOT NULL THEN CONCAT(' > ', t2) ELSE '' END, CASE WHEN t3 IS NOT NULL THEN CONCAT(' > ', t3) ELSE '' END, CASE WHEN t4 IS NOT NULL THEN CONCAT(' > ', t4) ELSE '' END, CASE WHEN t5 IS NOT NULL THEN CONCAT(' > ', t5) ELSE '' END ) AS path FROM pivoted ), user_purchases AS ( SELECT user_id, SUM(unit_price * purchase_units_sold) AS revenue FROM amazon_retail_purchases WHERE purchase_date_utc >= CAST('2025-01-01' AS TIMESTAMP) AND purchase_date_utc < CAST('2026-02-01' AS TIMESTAMP) GROUP BY user_id ) SELECT pa.path, COUNT(*) AS customers, SUM(p.revenue) AS revenue, SUM(p.revenue) / COUNT(*) AS rev_per_customer FROM user_purchases p INNER JOIN paths pa ON p.user_id = pa.user_id GROUP BY pa.path

Sample Output

Example data shown below is illustrative; actual outputs will vary based on your AMC instance and date range.

Sample Output

Path to Purchase

Most common ad touchpoint journeys before conversion

path	customers	revenue	rev_per_customer
sponsored_products	7,700	$296,700	$38.53
sponsored_products > sponsored_products	2,770	$123,400	$44.55
dsp > sponsored_products	1,310	$64,200	$49.01
sponsored_brands > sponsored_products	1,100	$56,200	$51.09
sponsored_products > dsp > sponsored_products	740	$40,400	$54.59
dsp	590	$22,500	$38.14
sponsored_products > sponsored_brands > sponsored_products	520	$29,600	$56.92
dsp > sponsored_products > sponsored_products	430	$23,100	$53.72

Top 5 Paths to Purchase

Customer journey flows: touchpoint sequence before conversion

7,700 customers

SP → SP

2,770

DSP → SP

1,310

SB → SP

1,100

SP → DSP → SP

740

💡 Pro Tip

Add HAVING COUNT(*) >= 50 to filter noise paths. The most actionable insight here is which ad types appear as the FIRST touch in high-revenue paths; that tells you where to invest top-of-funnel budget.

Recommended Analysis:

Sort by rev_per_customer DESC: multi-touch paths almost always have higher value per customer
Group by first touchpoint: extract the first element of each path to see top-of-funnel channel performance
Group by path length: compare 1-touch vs 2-touch vs 3+ touch conversion value

Best Chart Types:

Horizontal bar chart: Top paths by customer count (as shown above)
Sankey/flow diagram: If you have visualization tools that support it, Sankey charts are ideal for path data
Scatter plot: Customers (x) vs Rev/Customer (y) to find high-value paths

Combine with Q14: Use Q14 for channel-level first-touch summary, then Q15 to understand the full journey. Together they answer "where do customers start?" and "what path do they take?"

✦ ADLABS FEATURE

Custom Dashboards & Reporting

Live, white-label dashboards that track TACOS with retail, ads, DSP, SQP and AMC widgets across your catalog. Stop sending static spreadsheets; give clients and stakeholders real-time access to the metrics that matter.

Try Free →

AdLabs custom reporting dashboards for Amazon sellers

Reference

AMC Tables Reference

The five core AMC tables used across this query library. Each table contains many more columns than listed here; these are the key columns relevant to the queries in this guide.

amazon_retail_purchases

The core retail purchases table. Contains every purchase made by customers of your brand on Amazon. Used by 9 of the 15 queries in this library.

Column	Type	Notes
`user_id`	TEXT	⛔ Non-viewable; used for JOINs and COUNT(DISTINCT) only
`asin`	TEXT	Product ASIN
`asin_name`	TEXT	Product name
`purchase_date_utc`	TIMESTAMP	Purchase date; use with DATE_TRUNC
`purchase_dt_utc`	TIMESTAMP	Purchase datetime; use for SECONDS_BETWEEN
`purchase_day_utc`	INTEGER	Day number; usable for ORDER BY in window functions
`purchase_id`	TEXT	Unique order identifier
`unit_price`	DECIMAL	Price per unit in dollars (already converted)
`purchase_units_sold`	INTEGER	Units in this line item
`purchase_program_name`	TEXT	Subscribe & Save indicator (may be empty)

sponsored_ads_traffic

All Sponsored Products, Sponsored Brands, and Sponsored Display traffic events: impressions, clicks, and spend. Used by Q7, Q9, Q10, Q12, Q14, and Q15.

Column	Type	Notes
`user_id`	TEXT	⛔ Non-viewable
`event_date_utc`	TIMESTAMP	Use this for DATE_TRUNC (not event_day_utc)
`creative_asin`	TEXT	ASIN shown in the ad; may be empty for SB/SD
`ad_product_type`	TEXT	sponsored_products, sponsored_brands, sponsored_display
`campaign`	TEXT	Campaign name
`campaign_id`	TEXT	Campaign ID
`spend`	BIGINT	⚠️ In microcents; divide by 100,000,000 for dollars
`clicks`	INTEGER	Number of clicks
`impressions`	INTEGER	Number of impressions

dsp_impressions

DSP impression-level data with spend information. Used by Q8 for DSP spend tracking, and by Q14 and Q15 for touchpoints.

Column	Type	Notes
`user_id`	TEXT	⛔ Non-viewable
`impression_date_utc`	TIMESTAMP	Use this for DATE_TRUNC (not impression_day_utc)
`campaign`	TEXT	DSP campaign name
`campaign_id`	TEXT	DSP campaign ID
`total_cost`	BIGINT	⚠️ In millicents; divide by 100,000 for dollars
`impressions`	INTEGER	Number of impressions

dsp_clicks

DSP click-level data. Used by Q10 and Q12 to determine if a customer was "ad-touched" via DSP.

Column	Type	Notes
`user_id`	TEXT	⛔ Non-viewable
`click_date_utc`	TIMESTAMP	Click timestamp
`campaign`	TEXT	DSP campaign name
`clicks`	INTEGER	Number of clicks

amazon_attributed_events_by_conversion_time

Amazon's attribution table, the largest table (392 columns). Links ad traffic to conversion events. Used by Q11 for full-funnel attribution and by Q14 for conversions.

Column	Type	Notes
`user_id`	TEXT	⛔ Non-viewable
`conversion_event_date_utc`	TEXT	⚠️ Requires CAST(... AS TIMESTAMP) before DATE_TRUNC
`ad_product_type`	TEXT	Ad type that drove the conversion (NULL for DSP)
`campaign`	TEXT	Campaign name
`tracked_asin`	TEXT	ASIN attributed to the conversion
`new_to_brand`	BOOLEAN	Amazon's NTB flag
`purchases`	INTEGER	Number of attributed purchases
`total_product_sales`	DECIMAL	Total attributed sales revenue
`new_to_brand_purchases`	INTEGER	NTB purchase count
`new_to_brand_total_product_sales`	DECIMAL	NTB revenue

Written by

AdLabs team

Amazon Ads Verified Partner

Amazon PPC software for brands and agencies, used by 6,500+ advertisers.