AMC SQL Query Library: 20 Copy-Paste Templates for Amazon Marketing Cloud
Amazon Marketing Cloud's power lives in SQL queries against event-level ad data — impressions, clicks, conversions, DSP views, Sponsored Ads attribution, all at user-day granularity. But the learning curve is steep: AMC has its own schema, privacy thresholds, and quirks. This library gives you 20 copy-paste-ready queries covering the four most valuable use cases: measurement, audience building, optimization, and reporting. Adapt the WHERE clauses, lookback windows and brand ASINs to your account, and you will have real insights in your first week.
💻 Before You Start
- All queries target AMC's default schema (sponsored_ads_traffic, dsp_impressions, etc.)
- Replace
{brand_asins},{start_date},{lookback_days}placeholders with your actual values - AMC enforces minimum output thresholds (~100 users) — queries returning fewer users will aggregate to privacy-preserving levels
- Default attribution window is 14 days — adjust in WHERE clauses as needed
For the no-code version of audience-building queries (#6-13), see our 12 pre-built AMC audiences guide.
Measurement Queries (5)
These answer: “What did my ads actually do?” Output is reports, not audiences.
New-to-Brand (NTB) Rate
What percent of ad-attributed purchases came from customers who had never bought your brand before? Core growth KPI.
SELECT
COUNT(DISTINCT CASE WHEN ntb_flag = 1 THEN user_id END) AS ntb_buyers,
COUNT(DISTINCT user_id) AS total_buyers,
COUNT(DISTINCT CASE WHEN ntb_flag = 1 THEN user_id END) * 100.0
/ COUNT(DISTINCT user_id) AS ntb_rate_pct
FROM amazon_attributed_events_by_traffic_time
WHERE conversion_event_date >= '{start_date}'
AND conversion_event_date < '{end_date}'
AND tracked_asin IN ({brand_asins});ASIN Overlap Between Campaigns
Which ASINs are being purchased by customers who saw ads for different ASINs? Reveals halo effect.
WITH a_buyers AS (
SELECT DISTINCT user_id FROM amazon_attributed_events_by_traffic_time
WHERE tracked_asin = '{asin_a}' AND conversion_event_date >= '{start_date}'
),
b_buyers AS (
SELECT DISTINCT user_id FROM amazon_attributed_events_by_traffic_time
WHERE tracked_asin = '{asin_b}' AND conversion_event_date >= '{start_date}'
)
SELECT
(SELECT COUNT(*) FROM a_buyers) AS a_total,
(SELECT COUNT(*) FROM b_buyers) AS b_total,
COUNT(*) AS overlap_users
FROM a_buyers INNER JOIN b_buyers USING (user_id);Incrementality — Exposed vs Control Conversion Rate
Did customers exposed to your ads buy at a higher rate than an equivalent unexposed control? Gold standard measure.
WITH exposed AS (
SELECT DISTINCT user_id, 1 AS exposed FROM dsp_impressions
WHERE impression_date BETWEEN '{start_date}' AND '{end_date}'
AND campaign_id IN ({campaign_ids})
),
control AS (
SELECT DISTINCT user_id, 0 AS exposed FROM amc_control_group
WHERE control_date BETWEEN '{start_date}' AND '{end_date}'
),
all_users AS (SELECT * FROM exposed UNION ALL SELECT * FROM control)
SELECT exposed, COUNT(DISTINCT user_id) AS users,
COUNT(DISTINCT CASE WHEN c.user_id IS NOT NULL THEN all_users.user_id END) AS buyers,
COUNT(DISTINCT CASE WHEN c.user_id IS NOT NULL THEN all_users.user_id END) * 100.0
/ COUNT(DISTINCT all_users.user_id) AS conv_rate_pct
FROM all_users
LEFT JOIN conversions c ON all_users.user_id = c.user_id
GROUP BY exposed;Path-to-Conversion (Ad Type Sequence)
What sequence of ad touchpoints did converting customers see? Most common paths reveal the real customer journey.
SELECT
ad_type_path,
COUNT(DISTINCT user_id) AS converting_users,
SUM(order_value) AS revenue
FROM (
SELECT user_id,
LISTAGG(DISTINCT ad_type, ' -> ') WITHIN GROUP (ORDER BY event_timestamp) AS ad_type_path,
SUM(purchase_amount) AS order_value
FROM amazon_attributed_events_by_conversion_time
WHERE conversion_event_date >= '{start_date}'
AND tracked_asin IN ({brand_asins})
GROUP BY user_id
)
GROUP BY ad_type_path
ORDER BY revenue DESC
LIMIT 20;View-Through Attribution by Creative
Which DSP creatives drive conversions without clicks (view-through)? Critical for video/upper-funnel assessment.
SELECT
creative_id, creative_name,
SUM(CASE WHEN attribution_type = 'click' THEN 1 ELSE 0 END) AS click_conversions,
SUM(CASE WHEN attribution_type = 'view' THEN 1 ELSE 0 END) AS view_conversions,
SUM(purchase_amount) AS total_revenue
FROM amazon_attributed_events_by_traffic_time
WHERE conversion_event_date BETWEEN '{start_date}' AND '{end_date}'
AND ad_product_type = 'dsp'
AND tracked_asin IN ({brand_asins})
GROUP BY creative_id, creative_name
ORDER BY total_revenue DESC;Audience-Building Queries (8)
These produce audiences you can push to DSP or Sponsored Display. Each matches one of the 12 no-code audience templates.
Cart Abandoners (Last 7 Days)
Users who added to cart but did not purchase. Best retargeting audience by ROAS.
SELECT DISTINCT user_id
FROM sponsored_ads_traffic
WHERE event_type = 'add_to_cart'
AND event_date >= CURRENT_DATE - INTERVAL '7' DAY
AND tracked_asin IN ({brand_asins})
AND user_id NOT IN (
SELECT DISTINCT user_id FROM amazon_attributed_events_by_conversion_time
WHERE conversion_event_date >= CURRENT_DATE - INTERVAL '7' DAY
AND tracked_asin IN ({brand_asins})
);High-LTV Customers (Top 5%)
Your most valuable customers. Seed for lookalike audiences; exclude from retargeting.
WITH user_ltv AS (
SELECT user_id, SUM(purchase_amount) AS total_spend,
COUNT(DISTINCT conversion_event_id) AS order_count
FROM amazon_attributed_events_by_conversion_time
WHERE conversion_event_date >= CURRENT_DATE - INTERVAL '365' DAY
AND tracked_asin IN ({brand_asins})
GROUP BY user_id
),
threshold AS (
SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_spend) AS p95_spend
FROM user_ltv
)
SELECT user_id FROM user_ltv, threshold
WHERE total_spend >= p95_spend;Subscribe & Save Churned (Last 90 Days)
Customers who cancelled Subscribe & Save. High winback potential.
SELECT DISTINCT user_id
FROM sponsored_ads_traffic
WHERE event_type = 'subscribe_save_cancel'
AND event_date BETWEEN CURRENT_DATE - INTERVAL '90' DAY AND CURRENT_DATE
AND tracked_asin IN ({brand_asins})
AND user_id IN (
SELECT user_id FROM amazon_attributed_events_by_conversion_time
WHERE event_type = 'subscribe_save_delivery'
AND conversion_event_date < CURRENT_DATE - INTERVAL '90' DAY
GROUP BY user_id HAVING COUNT(*) >= 2
);Competitor-Brand Shoppers
Customers who viewed/bought from named competitor ASINs but never from your brand. Highest-ROAS conquest audience.
WITH competitor_engaged AS (
SELECT DISTINCT user_id FROM sponsored_ads_traffic
WHERE tracked_asin IN ({competitor_asins})
AND event_date >= CURRENT_DATE - INTERVAL '60' DAY
),
brand_buyers AS (
SELECT DISTINCT user_id FROM amazon_attributed_events_by_conversion_time
WHERE tracked_asin IN ({brand_asins})
)
SELECT user_id FROM competitor_engaged
WHERE user_id NOT IN (SELECT user_id FROM brand_buyers);Cross-Category Buyers (SKU-A Buyers Who Have Not Bought SKU-B)
Cross-sell candidates. Highest-converting cross-sell mechanism on Amazon.
SELECT DISTINCT a.user_id
FROM amazon_attributed_events_by_conversion_time a
WHERE a.tracked_asin IN ({sku_a_asins})
AND a.conversion_event_date >= CURRENT_DATE - INTERVAL '90' DAY
AND a.user_id NOT IN (
SELECT user_id FROM amazon_attributed_events_by_conversion_time
WHERE tracked_asin IN ({sku_b_asins})
);Deal Seekers
Customers who engage primarily with discounted/deal-surfaced content. Exclude from full-price campaigns.
SELECT DISTINCT user_id
FROM sponsored_ads_traffic
WHERE event_date >= CURRENT_DATE - INTERVAL '30' DAY
AND placement_type IN ('lightning_deals', 'prime_day_deals', 'deal_of_the_day')
GROUP BY user_id
HAVING COUNT(*) >= 3;Engaged-No-Purchase (3+ Detail Page Views, No Buy)
Highly-considering users who have not converted. Social proof / testimonial creative moves the needle.
SELECT user_id
FROM sponsored_ads_traffic
WHERE event_type = 'detail_page_view'
AND tracked_asin IN ({brand_asins})
AND event_date >= CURRENT_DATE - INTERVAL '60' DAY
GROUP BY user_id
HAVING COUNT(*) >= 3
AND user_id NOT IN (
SELECT user_id FROM amazon_attributed_events_by_conversion_time
WHERE tracked_asin IN ({brand_asins})
AND conversion_event_date >= CURRENT_DATE - INTERVAL '60' DAY
);Lookalike Seed (Top 1% by Order Count)
Seed audience for DSP lookalike modeling. Amazon expands this into a similar-behavior audience.
WITH ranked AS (
SELECT user_id, COUNT(DISTINCT conversion_event_id) AS orders,
NTILE(100) OVER (ORDER BY COUNT(DISTINCT conversion_event_id) DESC) AS pct
FROM amazon_attributed_events_by_conversion_time
WHERE tracked_asin IN ({brand_asins})
AND conversion_event_date >= CURRENT_DATE - INTERVAL '365' DAY
GROUP BY user_id
)
SELECT user_id FROM ranked WHERE pct = 1;Optimization Queries (4)
These drive campaign-level bid and placement decisions.
Frequency Capping — Impression Fatigue Curve
At what impression count does conversion rate flatten? Set your frequency cap there.
SELECT
impression_bucket,
COUNT(DISTINCT user_id) AS users,
COUNT(DISTINCT CASE WHEN converted = 1 THEN user_id END) * 100.0
/ COUNT(DISTINCT user_id) AS conv_rate_pct
FROM (
SELECT user_id,
CASE
WHEN impressions <= 2 THEN '1-2'
WHEN impressions <= 5 THEN '3-5'
WHEN impressions <= 8 THEN '6-8'
WHEN impressions <= 12 THEN '9-12'
ELSE '13+' END AS impression_bucket,
MAX(CASE WHEN c.user_id IS NOT NULL THEN 1 ELSE 0 END) AS converted
FROM (SELECT user_id, COUNT(*) AS impressions FROM dsp_impressions
WHERE impression_date >= '{start_date}' GROUP BY user_id) i
LEFT JOIN conversions c ON i.user_id = c.user_id
GROUP BY user_id, impressions
)
GROUP BY impression_bucket ORDER BY impression_bucket;Bid Modifier Analysis — Top of Search vs Rest
Is your Top-of-Search bid premium paying back? Compare conversion rates by placement.
SELECT
placement, COUNT(DISTINCT user_id) AS users,
SUM(clicks) AS clicks,
SUM(purchase_amount) AS revenue,
SUM(spend) AS spend,
(SUM(spend) / NULLIF(SUM(purchase_amount), 0)) * 100 AS acos_pct
FROM sponsored_ads_traffic
WHERE event_date >= '{start_date}'
AND ad_product_type = 'sponsored_products'
GROUP BY placement;Placement Efficiency by Page-Type
Which detail-page placements convert best? Optimize product-targeting campaigns accordingly.
SELECT
placement_page_type,
COUNT(DISTINCT user_id) AS reached,
COUNT(DISTINCT CASE WHEN purchase_amount > 0 THEN user_id END) AS converters,
SUM(purchase_amount) AS revenue,
SUM(spend) AS spend
FROM sponsored_ads_traffic
WHERE event_date >= '{start_date}'
AND ad_product_type = 'sponsored_display'
GROUP BY placement_page_type
ORDER BY revenue DESC;Daypart Performance — Hour of Day CPC/CVR
Identify high-conversion hours for IST dayparting rules.
SELECT
EXTRACT(HOUR FROM event_timestamp AT TIME ZONE 'Asia/Kolkata') AS hour_ist,
COUNT(*) AS clicks,
SUM(spend) AS spend,
SUM(purchase_amount) AS revenue,
AVG(spend) AS avg_cpc,
COUNT(CASE WHEN purchase_amount > 0 THEN 1 END) * 100.0 / COUNT(*) AS cvr_pct
FROM sponsored_ads_traffic
WHERE event_date >= '{start_date}'
GROUP BY hour_ist ORDER BY hour_ist;Reporting Queries (3)
For executive dashboards, investor decks, and monthly business reviews.
Multi-Touch Attribution — First-Touch vs Last-Touch Revenue
How much revenue is credited to each ad type under different attribution models?
WITH touches AS (
SELECT user_id, ad_type, event_timestamp, purchase_amount,
ROW_NUMBER() OVER (PARTITION BY user_id, conversion_event_id ORDER BY event_timestamp ASC) AS first_rank,
ROW_NUMBER() OVER (PARTITION BY user_id, conversion_event_id ORDER BY event_timestamp DESC) AS last_rank
FROM amazon_attributed_events_by_conversion_time
WHERE conversion_event_date >= '{start_date}' AND tracked_asin IN ({brand_asins})
)
SELECT ad_type,
SUM(CASE WHEN first_rank = 1 THEN purchase_amount ELSE 0 END) AS first_touch_revenue,
SUM(CASE WHEN last_rank = 1 THEN purchase_amount ELSE 0 END) AS last_touch_revenue,
SUM(purchase_amount) AS even_split_revenue
FROM touches GROUP BY ad_type;Cross-Channel Incrementality — DSP vs Sponsored Ads Only
Quantify the lift of adding DSP on top of existing Sponsored Ads spend.
SELECT
exposure_group,
COUNT(DISTINCT user_id) AS users,
SUM(purchase_amount) AS revenue,
SUM(purchase_amount) / COUNT(DISTINCT user_id) AS revenue_per_user
FROM (
SELECT user_id, SUM(purchase_amount) AS purchase_amount,
CASE
WHEN MAX(CASE WHEN ad_product_type = 'dsp' THEN 1 ELSE 0 END) = 1
AND MAX(CASE WHEN ad_product_type LIKE 'sponsored%' THEN 1 ELSE 0 END) = 1
THEN 'dsp_plus_sponsored'
WHEN MAX(CASE WHEN ad_product_type LIKE 'sponsored%' THEN 1 ELSE 0 END) = 1
THEN 'sponsored_only'
WHEN MAX(CASE WHEN ad_product_type = 'dsp' THEN 1 ELSE 0 END) = 1
THEN 'dsp_only'
END AS exposure_group
FROM amazon_attributed_events_by_conversion_time
WHERE conversion_event_date >= '{start_date}' AND tracked_asin IN ({brand_asins})
GROUP BY user_id
) exposures
GROUP BY exposure_group;Customer Journey — Time to First Purchase
Median days from first ad exposure to first purchase. Informs retargeting window sizing.
WITH journeys AS (
SELECT user_id,
MIN(CASE WHEN event_type IN ('impression', 'click') THEN event_date END) AS first_touch,
MIN(CASE WHEN purchase_amount > 0 THEN event_date END) AS first_purchase
FROM amazon_attributed_events_by_conversion_time
WHERE event_date >= '{start_date}' AND tracked_asin IN ({brand_asins})
GROUP BY user_id
)
SELECT
PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY DATEDIFF('day', first_touch, first_purchase)) AS p25_days,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF('day', first_touch, first_purchase)) AS median_days,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY DATEDIFF('day', first_touch, first_purchase)) AS p75_days
FROM journeys
WHERE first_purchase IS NOT NULL AND first_touch IS NOT NULL;How to Run These Queries
- Log into your AMC instance via Amazon Ads console → Measurement & Analytics → Amazon Marketing Cloud.
- Open the SQL workbench. Paste the query.
- Replace placeholders (
{brand_asins},{start_date}, etc.) with actual values. - Click Run. Queries typically complete in 30 seconds to 5 minutes depending on date range and data volume.
- Results can be downloaded as CSV or used to create audiences directly.
AMC Query Quirks to Watch For
- Privacy thresholds. Any query returning fewer than ~100 users will aggregate or suppress rows. Design queries with this in mind.
- 14-day default attribution. AMC uses 14-day attribution by default for DSP; 7-day for Sponsored Ads. Adjust explicitly in WHERE clauses if you want something else.
- Schema evolution. Amazon updates AMC tables periodically — the schema names and field lists above were accurate as of April 2026. Always check your AMC instance's schema reference.
- No PII. You cannot expose individual user email/name/address. Audience-building queries produce hashed
user_idvalues which are only usable within Amazon's ad products.
Get These Pre-Loaded in AMC
eVanik's AMC platform ships with all 20 of these queries pre-loaded and parameterized for your account. No copy-paste, no placeholder replacement — just click, adjust parameters, and run. Plus bespoke query development for custom hypotheses not covered above.
Start Free AMC Assessment
