Reach & Frequency

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;

3 · Interpretation

Frequency above 7 suggests saturation/ad-fatigue; below 2 is often too weak for recall. Target band 3-5 per week.