Ad Click Aggregator

What the interviewer is asking

Many online ads are paid per click, so an ad platform has to record every click, show advertisers how each ad is doing, and bill them for it. "Design an ad click aggregator" asks for that pipeline: a flood of tiny events, counted quickly and counted right.

The interviewer listens for counting once, as clicks arrive; splitting the stream by ad; counting by event time, with a plan for late clicks; how a crash double counts and how to stop it; and raw clicks kept to recount the bill. The walkthrough follows the 8 steps of the interview framework.

1. Scope: what version 1 must do

• Record every click on an ad.
• Show each ad's clicks per minute, a minute or two behind.
• Keep the totals exact enough to bill by: no click lost, none counted twice.
• Drop obvious fraud, like one user clicking the same ad 50 times.

Name the rest and park it: conversions (who bought something after a click), attribution (which ad gets credit for a sale), and the dashboard's charts and exports. Say the "minute or two" out loud: it's the room the design uses to wait for late clicks.

2. Data: raw clicks and per-minute counts

data                key                fields
raw clicks          click_id           ad_id, user, time
per-minute counts   (ad_id, minute)    clicks

A click is a small event of about 100 bytes, with a click id that makes it unique; that id matters later, when clicks get repeated. The counts are what advertisers read: one per ad per minute, at most 1,440 a day per ad (24 × 60), however many clicks it gets. Any range adds up from minutes, so a running total isn't needed. Leave out the ad's picture, personal details beyond a user id, and purchases.

3. The API

GET /click?ad=ad7&redirect=shoe.shop/sale
→ 302 Found
Location: shoe.shop/sale

GET /ads/ad7/clicks?from=10:00&to=10:03&granularity=minute
→ 200 OK
{"10:00": 1204, "10:01": 1187, "10:02": 1240}

The ad's link points to the click service, which records the click and answers with a 302, as a URL shortener does: a 302 is asked again on every tap, so every click is counted, while a browser remembers a 301 and skips you next time. Record the click quickly and redirect; nobody should wait for the counting.

4. The busy path: recording a click

Every tap on any ad is a write: the click service gives the click an id, records it and redirects. Advertisers read their counts a few times an hour. So unlike a URL shortener, where opens outnumber new links 100 to 1, here writes far outnumber reads, and the design is about absorbing writes.

5. The numbers

• Clicks: 1 billion a day. Round a day to 100,000 seconds and drop five zeros: 10,000 a second on average (about 11,600 with the real 86,400).
• Peaks: the interviewer says peaks run about 10× the average, so plan for about 100,000 clicks a second. Dividing by the 1,440 minutes of a day gives clicks a minute instead, a common slip.
• Raw data: 1 billion × 100 bytes = 100 GB a day, about 36 TB a year, around 110 TB with the usual 3 copies.
• Counts: say 100,000 ads are running: at most 100,000 × 1,440 ≈ 150 million per-minute counts a day, about 3 GB at about 20 bytes each.

Say one database server takes about 5,000 simple writes a second: saving and counting every click directly asks 20 times that.

6. Services: a stream, aggregators and two stores

click service → click stream → aggregators → counts DB → query service
                     ↘ raw store (object storage, a file per hour)

• Click service: gives each click an id, appends it to the click stream and answers 302. A simple fraud rule fits here: drop a user's repeated clicks on one ad.
• Click stream: a log-based stream, like Kafka, that keeps every click on disk for a few days (say 7), split into partitions.
• Aggregators: read the stream, count per ad per minute in memory, and write each minute's counts once. That's batching: a minute of an ad's clicks becomes one write.
• Counts DB: the per-minute aggregates, keyed by ad and sorted by minute: a wide-column store, or an analytics store built for time series. One ad's day is at most 1,440 rows, read in order.
• Raw store: a second consumer group copies every click to object storage: cheap, and the source for recounts and disputes.
• Query service: answers GET /ads/{id}/clicks, adding minutes up into hours or days.

Read replicas wouldn't help, since every copy applies every write, and counting with a query on each dashboard refresh repeats the same work again and again.

7. Scale: partitions by ad_id, and a viral ad

Say one aggregator counts about 20,000 clicks a second: 100,000 a second needs at least 5. Split the stream into partitions, say 10 for room to grow, each read by one aggregator of the consumer group. Partition by ad_id: all of an ad's clicks land on one aggregator, so its count for a minute is whole there and written once. Round robin would scatter every busy ad over all 10 partitions, up to 10 partial counts per ad per minute; splitting by minute would send all 100,000 clicks a second to one partition, 5× what an aggregator counts.

The trap is a hot ad. If ad7 goes viral at 50,000 clicks a second, every click carries the key ad7 and lands on one partition, 2.5× what its aggregator counts. More partitions don't help (the key decides), and a second aggregator in the group would sit idle. Split the hot key, the sharded-counter fix: the click service adds a shard number, ad7#0 to ad7#4, so 5 keys spread over several partitions at about 10,000 a second each, and ad7's minute is the sum of its 5 counts:

key             clicks at 10:30
ad7#0           600,493
ad7#1           600,968
ad7#2           598,472
ad7#3           599,756
ad7#4           600,311
ad7, added up   3,000,000

Only ads on a hot list get split: the aggregators spot them and tell the click service.

8. Deep dive: late clicks and watermarks

Clicks don't always arrive in order: an app may send clicks it saved while the phone was offline, or a server retries a failed send. Count each click by event time, the time it carries, not by when it arrives: then a delay or a replay can't move a click into another minute. Stamp the time on a server when you can, since a phone's clock may be off.

The question is when to close a minute and write its count. Here are 8 clicks on a small ad, ad2, in the order they arrived:

click   happened   arrived    closed on time   wait a minute
#1      10:00:05   10:00:06   10:00            10:00
#2      10:00:21   10:00:22   10:00            10:00
#3      10:00:40   10:00:41   10:00            10:00
#4      10:01:02   10:01:03   10:01            10:01
#5      10:00:58   10:01:20   lost             10:00
#6      10:01:35   10:01:36   10:01            10:01
#7      10:00:50   10:01:45   lost             10:00
#8      10:00:30   10:02:40   lost             lost

Closed the moment it ends, 10:00 keeps only 3 of its 6 clicks. A watermark fixes most of that: it runs a minute behind the newest click time the aggregator has seen, and a minute closes once the watermark passes its end. On a busy partition the newest click time keeps up with the clock, so 10:00 closes at about 10:02:00 with 5 clicks; 10:01 gets its 2 either way. A longer wait catches more, but every count shows later; a minute fits the "minute or two" of Scope. Clicks later still, like #8, are left to the nightly recount.

9. Deep dive: exactly once, or close

The stream delivers each click at least once. Say that when a minute closes, an aggregator adds its count to the counts DB with +=, then saves its offset, its place in the partition. A crash between the two repeats clicks:

step                                     saved offset   ad5 at 10:05
read offsets 100 to 139: 40 clicks       100            0
10:05 closes: += 40                      100            40
crash before saving offset 140           100            40
new aggregator reads 100 to 139: += 40   100            80

That's 80 for 40 real clicks: every click read before the crash counts twice. A standby restarts from the same offset and doubles them too; saving the offset more often only shortens the gap; saving it first loses clicks instead; and ids remembered in memory die with the aggregator. Saving the offset on a timer is worse still: it can pass clicks whose counts live only in memory, so a crash can lose clicks as well as double them. Two fixes work:

• Save the counts and the offset together, in one transaction, for example by keeping each partition's offset in the counts DB. A new aggregator starts exactly where the saved counts end. The open minutes' counts go into the same save; stream processors such as Apache Flink save state and offsets together in checkpoints.
• Skip repeats by click_id: save the ids of recently counted clicks with the counts, and skip any id already there. The click id is an idempotency key, and it also catches a click the click service sent twice after a timeout. Ten minutes of ids at 100,000 a second is 60 million ids, about 1 GB at 16 bytes each, spread over the aggregators.

That's what "exactly-once" means here: the stream still delivers repeats, and the aggregator makes them harmless.

10. Deep dive: billing and the nightly recount

A view count may be a few off; a bill may not. The streaming counts can still be wrong: a click later than the watermark, a bug in an aggregator, fraud found the next day. So bill from a recount: each night a batch job reads the day's raw clicks, drops repeats by click_id, applies the latest fraud rules and corrects the day's totals. The dashboard shows the fast streaming counts; the invoice uses the recounted ones. A fast streaming path beside an exact batch path is often called a lambda architecture; replaying a long-kept stream instead is often called kappa.

What breaks first

• A viral ad: one partition falls behind; split its key.
• An aggregator crash: clicks repeat; save the counts with the offset, or skip repeats by click_id.
• Late clicks: tune the watermark; the recount catches the rest.
• Long ranges: a year of minutes is many rows; keep hourly and daily totals too.
• Fraud and open redirects: limit clicks per user and ad; redirect only to the ad's own address, or anyone can use your domain to send people anywhere.

Summary: the order to follow

1. Scope: every click recorded, counted per ad per minute, exact enough to bill.
2. Data: raw clicks with a click_id; (ad_id, minute) → count.
3. API: GET /click answered with a 302; GET /ads/{id}/clicks by minute.
4. Busy path: recording a click; writes far outnumber reads.
5. Numbers: about 100,000 clicks a second at peaks; 100 GB of raw clicks a day.
6. Services: click service, click stream, aggregators, counts DB, query service, raw store.
7. Scale: partitions by ad_id; a viral ad split over 5 keys.
8. Deep dives: event time and a watermark a minute behind; counts saved with the offset or the click_ids; a nightly recount for billing.