Query of the Week · Amazon Marketing Cloud
Query of the Week — AMC SQL explained in context
Amazon Marketing Cloud queries from our library, explained in context: from the business question through the SQL to the interpretation. With archive.
Latest Query of the Week: Query of the Week #4: Reach & Frequency
Week 42 · 2026-10-15
How many unique users does the campaign reach, and how often? The reach & frequency query checks whether your frequency cap holds.
1 · Business question
How many unique users does the campaign reach and how often (check frequency capping)?
2 · Query (SQL)
SELECT
campaign_id,
COUNT(DISTINCT user_id) AS reach,
ROUND(1.0 * COUNT(*) / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS avg_frequency,
ROUND(1.0 * COUNT(DISTINCT CASE WHEN impressions >= 7 THEN user_id END)
/ NULLIF(COUNT(DISTINCT user_id), 0), 4) AS pct_freq_ge_7
FROM amazon_attributed_events
WHERE event_type = 'impression'
GROUP BY campaign_id;
Query and interpretation in the library →
3 · Interpretation
Frequency above 7 suggests saturation/ad-fatigue; below 2 is often too weak for recall. Target band 3-5 per week.
The library
- Audience Overlap (User Overlap)
How much do two ad audiences overlap (e.g. DSP audience vs. Sponsored Brands audience)? Where are we burning budget on double delivery?
- Path to Conversion
Which touchpoint sequences occur before a purchase conversion? Where does Sponsored Display act as an assist?
- Campaign Lift
Does a test campaign actually lift conversion rate vs. a control group (incrementality)?
- Reach & Frequency
How many unique users does the campaign reach and how often (check frequency capping)?
Archive
Sources and freshness
Snapshot (Retail Data Insights (Frontend lane, swarm t_b70a1333)).
- Amazon Ads — Amazon Marketing Cloud (Library guide) · 2026-07-12