Ad Click Event Aggregation

Phase 1 — Clarify & Scope

Interviewer: Design an ad click event aggregation system — think Google Ads or Facebook's attribution pipeline. What clarifications do you need?

Candidate: A few questions:

1. What's the primary output — real-time click counts per ad, or attribution (which ad led to a conversion)?
2. What's the event volume — clicks per second?
3. Query patterns: dashboards refreshing every minute, or ad-hoc analytics queries over historical data?
4. Exactly-once semantics required, or is approximate counting acceptable?
5. What's the latency SLA — how fresh does reporting need to be?

Interviewer: Aggregate click counts per ad_id per minute. 1 billion clicks per day. Reports must be queryable within 1 minute of the click. Exactly-once semantics required — advertiser billing depends on it. Support top-100 most-clicked ads queries in real time.

Candidate: Let me size this:

$\text{Average click rate} = \frac{10^9}{86{,}400} \approx 11{,}574 \text{ clicks/sec}$

$\text{Peak click rate (10× average)} \approx 115{,}740 \text{ clicks/sec}$

Each click event ~500 bytes (ad_id, user_id, timestamp, IP, user agent, page URL):

$\text{Ingest bandwidth} = 115{,}740 \times 500 \approx 58 \text{ MB/sec peak}$

$\text{Storage (raw, 90 days)} = 10^9 \times 500 \times 90 \approx 45 \text{ TB}$

Interviewer: Good. What are the core functional requirements?

Candidate:

• Ingest click events in real time with deduplication
• Aggregate click counts per (ad_id, minute) window
• Expose query API: clicks for ad_id over time range, top-N ads by clicks
• Exactly-once counting — no double-billing, no missed clicks
• Support late-arriving events (network delays,…