Reach & Frequency

1 · Business-Frage

Wie viele Unique-User erreicht die Kampagne und wie oft (Frequency-Capping pruefen)?

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 ueber 7 deutet auf Saturation/Ad-Fatigue hin; Frequency unter 2 oft zu schwach fuer Erinnerungseffekt. Zielband 3-5 pro Woche.