Data & analytics · Advanced

Ad click aggregation

Log-then-count clicks with an exactly-once stream aggregator and a raw lake for batch recounts.

Every part, and why it is there

People clicking ads

Billions of ad clicks a day, each one money for someone. Users only care that the click takes them to the advertiser’s site immediately.

Advertiser dashboard

Advertisers watch spend and click-through in near real time and expect the numbers to match their bill.

Load balancer

Separates the high-volume click endpoint from the low-volume reporting API so each scales on its own.

Click tracker

Records the click with a unique id and answers with a redirect to the advertiser’s URL. It never counts anything itself, so it stays fast and stateless.

Ad URL cache

Maps ad id to its landing URL so the redirect never waits on a database.

Raw click stream

A durable, partitioned log of every click. It decouples logging from counting and lets you replay clicks if counting code was wrong.

Stream aggregator

A Flink-style job that drops duplicate click ids and counts clicks per ad per one-minute window. Checkpoints plus transactional output mean a crash neither loses nor double-counts a window.

Aggregates (OLAP store)

Per-minute counts by ad, campaign and country, built for fast slice-and-dice queries. Rows are upserted by a deterministic id so a replayed window overwrites rather than adds.

Raw archiver

Copies every raw click into the data lake in hourly files.

Raw click lake

Cheap, complete history. A nightly batch job recounts from here and corrects the real-time numbers before invoices go out.

Reporting API

Serves dashboard queries from the aggregates only. Raw clicks are never scanned on a user request.

Asked in interviews at

  • meta
  • rippling
  • snap
  • google
  • stripe

Concepts used here

  • HyperLogLog: Count 10 million different visitors in 12 KB, to within about 1%, without storing a single ID.
  • Count-Min Sketch: Count 500 million hashtags a day in about half a megabyte. It may overcount a little, but it never undercounts.

Learn more

All system design examples