Design an Ad Click Aggregator

Someone taps an ad and lands on the advertiser's site, and a counter for that ad goes up by one. The advertiser watches those counters on a dashboard and is billed for every click. Count too few and the platform loses money. Count too many and it overcharges a customer, which is worse.

Interviewers ask this because counting gets hard at this scale: over a hundred thousand events a second, a few ads that get most of them, clicks that arrive late or twice, and a number that must be both fresh and exact. The steps follow A Framework for Any Design. Try each "Your turn" box before you read on.

#Clarify the problem

• Functional: record a click and redirect the user to the ad's landing page; let advertisers query click counts per ad per minute over any time range; produce final counts for billing.
• Non-functional: the redirect adds under 50 ms; a click shows on the dashboard within about 10 seconds; billed counts are exact, with each valid click counted once; no accepted click is lost; the system survives the loss of any one machine.
• Out of scope: choosing which ad to show, fraud scoring (we leave a hook for it), unique-user counts, the billing system itself.

#Estimate the scale

The scenario: 10 billion clicks a day, 1 million active ads, about 300 bytes per raw click event (ids, timestamps, a device hash, IP and user agent).

• Click rate: 10¹⁰ ÷ 86,400 s ≈ 116,000 clicks a second on average. Assume peaks of 3x: about 350,000 a second.
• Ingest bandwidth: 350,000 × 300 B ≈ 105 MB/s at peak. Easy for a partitioned log; impossible as single-row database increments.
• Aggregate rows: at most one row per ad per minute: 10⁶ × 1,440 = 1.44 billion rows a day. At about 40 bytes each that is about 58 GB a day, a ceiling, since a minute with no clicks writes no row. The average ad gets 10¹⁰ ÷ 1.44 × 10⁹ ≈ 7 clicks a minute.
• Partitions: if one log partition and one processor task comfortably handle 10,000 events a second (the scenario's assumption), the peak needs 350,000 ÷ 10,000 = 35. Use 64 for headroom: about 5,500 a second each, if clicks spread evenly. They do not, which is the hot-ad deep dive.

#Define the API

GET /c?t={signed impression token}
  302 Found   Location: the ad's landing page (looked up by ad_id)
  The click is recorded before the response. A bad or expired token still
  redirects, but the click is logged as invalid and never counted.
 
GET /v1/ads/{ad_id}/clicks?from=2026-11-14T10:00Z&to=2026-11-14T11:00Z
  200 { "ad_id": 42, "minutes": [
          { "minute": "10:05", "clicks": 1288, "final": false }, ... ] }
  403 Forbidden     the ad belongs to another advertiser
  400 Bad Request   range longer than 31 days at minute granularity
 
GET /v1/advertisers/{id}/clicks?from=...&to=...&group_by=ad&granularity=hour

The token is the decision worth arguing. When the ad server shows an ad, it creates a random impression id and signs {impression_id, ad_id, shown_at} with a secret key (an HMAC). The click service checks the signature with no database call, which proves we really served that impression, and the impression id gives every click a natural key for spotting duplicates. Without it, a script can invent clicks on a competitor's ad.

#Model the data

-- click event: in the log, then in object storage (about 300 B)
impression_id   BINARY(16)   -- random, from the signed token
ad_id           BIGINT
advertiser_id   BIGINT
shown_at        TIMESTAMP    -- from the token
clicked_at      TIMESTAMP    -- event time, set by the click service
device_hash, ip, user_agent, valid (bool)
 
-- aggregate: one row per ad per minute (about 40 B)
ad_clicks_minute
  advertiser_id  BIGINT
  ad_id          BIGINT
  minute         TIMESTAMP
  clicks         BIGINT
  final          BOOLEAN      -- set by reconciliation
  ORDER BY (advertiser_id, ad_id, minute)   -- upsert replaces the row

Clicks are written once, read in order by the processor, and read again in bulk by recounts: an append-only, partitioned log (such as Kafka), copied to object storage. Queries are "sum clicks for these ads over this range, grouped by minute or hour": a scan over a few columns of many rows, which a columnar analytics (OLAP) store such as ClickHouse or Druid does well. A key-value store cannot sum a range at all.

Store clicks as an absolute value that a later write replaces, never as an increment, so every writer is safe to retry.

#High-level design

Two paths compute the same numbers. The stream is fast and nearly always right; the batch recount is slower and is the truth for billing. Both write one OLAP table, and the batch write wins.

Step through one click from impression to invoice, including a double click, a crash and a late arrival.

#Deep dives

#The counting pipeline

The obvious design, UPDATE ads SET clicks = clicks + 1 per click, means 350,000 row writes a second, and a popular ad turns one row into a lock everyone waits for. Count in memory instead, and write totals.

The stream processor (such as Flink) keys events by ad_id, so one ad's clicks reach one task. Each task keeps, per ad, a count for each open one-minute tumbling window (fixed, non-overlapping minutes). Windows use event time, the clicked_at stamp, so a backlog after an outage still lands clicks in the right minute. For freshness, the task emits each open window every 5 seconds instead of waiting for the minute to close.

The log makes this recoverable: it keeps 7 days of events (3 TB × 7 = 21 TB, 63 TB with three replicas), so any consumer can rewind.

#Counting each click once

Duplicates come from users double-clicking, click servers retrying an append whose acknowledgement was lost, and the processor replaying after a crash.

• Users and retries: the processor keeps every impression id it has seen in the last 24 hours, the longest a click is valid after its impression (the token's shown_at enforces that). An impression belongs to one ad and the log is keyed by ad_id, so both copies of a click reach the same task. The cost: 10¹⁰ ids a day × 16 B = 160 GB, about 2.5 GB per task across 64 partitions, kept in a disk-backed state store.
• Processor crashes: the processor checkpoints log offsets, window counts and the dedup set together. On restart it rewinds all three to the same point and replays, so its state ends up as if each event had been processed once.
• Writes to the OLAP store: the replay re-sends rows the store already has. Because each write replaces the row for (ad, minute) with an absolute count, writing it twice changes nothing.

Be precise: the log delivers at least once. What you build is exactly-once effect: dedup by a business key plus idempotent writes. "The broker gives exactly-once", with nothing about how the sink handles a replay, is a weak answer.

#Hot ads and partition skew

Keying by ad_id puts all of one ad's clicks on one log partition and one task. Fine for 7 clicks a minute; not for an ad during a live final drawing 30,000 a second. The partition and its task were sized for 10,000, so they fall behind, and so does every other ad on them.

Split the hot key in two stages. The click service keys a hot ad's events by (ad_id, salt), with salt = hash(impression_id) mod 8, so ad 42's clicks spread over 8 partitions and tasks of about 3,750 a second each. A second stage keyed by ad_id keeps the latest partial count from each of the 8 and writes their sum: 8 records every 5 seconds instead of 1.8 million clicks a minute. The salt comes from the impression id, not a random number, so both copies of a double click still meet in one task.

Salt only the ads on a hot list that the click service refreshes every minute. One gap remains: if an ad joins the list between the two halves of a double click, the copies reach different tasks and the stream counts both. The recount removes it.

#Late events and the recount

Some clicks arrive late: a click server spooled them to disk while the log was unreachable. The processor tracks a watermark, its estimate that "no more events older than this will come", and keeps each minute open until the watermark is 2 minutes past its end. A later event goes to a side output and is left to the recount.

The batch recount runs every hour on the hour before last, reading the archived raw events with the same signature, validity and dedup rules. It carries the last 24 hours of seen impression ids from run to run, so a double click that straddles an hour still counts once. It overwrites the stream's rows and sets final = true. A click that arrives after its hour is final reruns that hour, and billing gets an adjustment. Later fraud verdicts fit here too: the recount excludes clicks marked invalid. Billing reads only final rows. Alert if the two paths disagree by more than, say, 0.1% in an hour; that means a bug in one of them.

#Bottlenecks and failures

At 10x, about 3.5 million clicks a second at peak, add click servers and go to 640 partitions at the same 5,500 a second each; dedup state stays 2.5 GB per task. The OLAP store gets more shards by advertiser. More ads cross the hot threshold, so salting becomes routine.

#What a strong answer sounds like

#Follow-up questions

#Advertisers want counts by country and device too. What changes?

Add those columns to the aggregate key. Rows multiply by the combinations that occur, so keep the minute table to a few dimensions and serve finer breakdowns from hourly rollups.

#How do you stop click fraud?

A separate system reads the same log, scores clicks (rate per device and IP range, impossible timing) and writes verdicts that the recount applies.

#Why not query the raw events directly?

A month for one advertiser can be billions of rows. Minute aggregates are about 50 times smaller than raw data (58 GB versus 3 TB a day).

#How long do you keep minute-level rows?

About 30 days (58 GB × 30 ≈ 1.7 TB), then roll up to hourly rows, 60 times fewer, kept for years.

#How would you run it in several regions?

Run click services and a log per region, and ship raw events to one region for the recount. The impression id still deduplicates across regions, because it was minted once.

#What to practise next

Design Top-K Trending counts at the same scale but trades exactness for memory, and Design Metrics Monitoring stores the counts as time series and alerts on them.