Design an Ad Click Aggregator: Fast Redirects, Exactly-Once Counts and Billing-Grade Accuracy

Listen

Listen. When a user clicks an ad, we record it and redirect them to the advertiser's page. Advertisers query clicks per ad per minute, filtered by country, device or campaign. Billing needs exact daily totals. Ad selection, auctions and impression counting are out of scope, though impressions use the same pipeline.

Qualities. The redirect must be fast, under fifty milliseconds at p ninety-nine, or users bounce. No click lost, none counted twice. Dashboards can be a minute behind and should answer in a second, even over ninety days. Billing must be exact but can wait a day. And bot clicks must be filtered out.

Ask scale

Scale. A billion clicks a day is ten thousand a second, fifty thousand during a big live event. Each raw click is about two hundred bytes, so two hundred gigabytes a day, over seventy terabytes a year. The aggregates are much smaller: around three hundred million ad per minute rows a day, rolled up to hours after thirty days.

1B clicks/day
  1B / 10⁵ s = 10,000 clicks/s
  peak (big events) ×5 = 50,000/s

raw click ≈ 200 B
  1B × 200 B = 200 GB/day
  ≈ 73 TB/year raw

aggregates: ad × minute × dims
  ≈ 300M rows/day, roll up hourly
  after 30 days

Define API

The API. The click link carries an impression id, the ad id and a signature, all created when the ad was served. It returns a three oh two redirect. Advertisers get a query endpoint with a time range, granularity and optional group by, and billing reads exact daily totals from a separate endpoint.

GET /c?imp=8f3a…&ad=501&sig=…
  → 302 Location: advertiser URL

GET /v1/ads/{id}/clicks
  ?from=…&to=…
  &granularity=minute|hour|day
  &group_by=country
→ [{ t, country, clicks }]

GET /v1/billing/{account}/daily

Data model

Data model. Each raw click is an immutable event, kept in Kafka and then in a data lake. Dashboards read a pre-aggregated table in a column store such as ClickHouse or Druid: one row per ad, minute and dimension, with click counts and unique users estimated with HyperLogLog. Billing totals live in a small relational table.

click event (Kafka + lake)
  click_id, impression_id, ad_id,
  campaign_id, user_hash, ip,
  country, device, event_time

ad_clicks_minute (OLAP)
  ad_id, minute, country, device
  clicks, unique_users (HLL)
  sort key (ad_id, minute)

billing_daily (SQL), exact

High-level design

The high-level design. The click service validates the link, appends the click to Kafka, then redirects. A stream aggregator such as Flink groups clicks by ad and minute and writes rows to the column store. The dashboard queries that store. Raw events are also copied to the lake, which we will use later.

Why not insert every click into a database and run group by when advertisers ask? Because a month is thirty billion rows, and every dashboard refresh would scan them. Aggregating once, in the stream, turns a billion clicks into a few hundred million small rows that are already grouped. Dashboards read sums, never raw events.

Deep dive: the click

Deep dive one: the click itself. Write to Kafka with acks set to all, meaning every in-sync replica has it, before you send the redirect. That costs a few milliseconds. If Kafka is slow, append to a local disk spool, redirect anyway, and replay the spool later. The user never waits on aggregation or a database.

Next, duplicates and fraud. When the ad is served, attach a random impression id and an H M A C signature over it and the ad. A click with a bad signature is a forged link and is dropped. A second click with the same impression id within an hour is a duplicate. Add simple bot rules, like one IP clicking a hundred times a minute.

at ad serve time:
  imp_id = random 128-bit
  sig = HMAC(secret, imp_id|ad)

at click time:
  bad sig → drop (forged link)
  imp_id seen in last 1 h → dup
    (state per key with TTL)

bot rules: same IP 100×/min,
  no impression, datacentre IPs

Deep dive: event time

Deep dive two: time. A phone in a tunnel can deliver a click six minutes late. Group by event time, when the click happened, not when it arrived. A watermark tells the aggregator when a minute is probably complete, so it can emit that row. Allow a few minutes of lateness by updating the row. Anything later is left for the nightly batch to fix.

Deep dive: exactly once

Deep dive three: exactly-once. The aggregator checkpoints its window state and its Kafka offsets together. After a crash it restores and replays from those offsets. That only works if the output is idempotent, so write the full count for each ad and minute as an upsert, never as plus some delta. Replaying then rewrites the same row with the same number.

Deep dive: hot ads

Hot ads. A halftime ad during a big final can draw thirty thousand clicks a second, all on one key and one task. Salt the key: add a small suffix from zero to seven, so eight tasks share that ad. A second step adds the eight partial counts for each minute. Only salt ads above a threshold, so ordinary ads stay simple.

key = ad_id → one task

hot ad: key = ad_id + "#" +
  (click_id mod 8)
→ 8 tasks share the load

second step sums the 8
partial counts per minute

salt only ads over a threshold

Deep dive: queries

Querying. A column store reads only the columns a query needs and compresses them well. Sorting by ad id, then time, means one advertiser's ninety days is a small contiguous range. Keep minute rows for thirty days and hourly rows for two years. A query over months reads the hourly table and comes back in a fraction of a second.

Deep dive: reconcile

Reconciliation. Every night a batch job reads yesterday's raw clicks from the lake, deduplicates fully, includes the very late events and applies the latest fraud rules. It compares its totals with the stream totals per ad. A gap above a tenth of a percent raises an alert. Billing uses only the batch numbers, and the dashboard is corrected to match.

Trade-offs

Failure modes. If Kafka is slow, the spool keeps redirects fast. If the aggregator is down, dashboards go stale but every click sits safely in Kafka, which keeps seven days. If the column store is down, replay its writes later. And if the counting logic has a bug, fix it and replay history from the lake. Speed can be lost, clicks cannot.

Monitor redirect latency, consumer lag, the share of late and dropped events, the stream versus batch difference, and duplicate and fraud rates. At ten times the traffic, add partitions and salt more keys, then run a click pipeline per region, keeping clicks close to users, with a global roll-up only for billing.

Say it like this

In the interview: the click goes to a durable log before the redirect. Signed impression ids catch duplicates and forged links. A stream job aggregates by ad and minute using event time and watermarks, with checkpoints and an upsert sink for exactly-once counts. Salt hot ads, serve dashboards from a column store with roll-ups, and bill from a nightly reconciled batch.