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.

Updated: · Library: 4 queries · Archive: 4 episodes

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)).