Design an Ad Click Aggregator

• What is the finest time granularity advertisers need: per minute, per hour?
• Which dimensions must be filterable: country, device, placement, publisher?
• How long must raw click data and aggregates be retained?
• Is near-real-time data used for billing, or only the final daily numbers?
• What latency is acceptable for the click redirect itself?
• Do we need to support top-N queries, such as most clicked ads in the last hour?

• Record each ad click and redirect the user to the landing page.
• Aggregate clicks per ad per minute, sliced by dimensions such as country and device.
• Let advertisers query counts for arbitrary time ranges with results at most a minute stale.
• Count each click exactly once and exclude fraudulent clicks from billable totals.
• Provide top-N ads by clicks over recent windows for internal monitoring.
• Produce final daily billing totals that match raw logs.

• 10 billion clicks per day: about 115,000 per second average and 500,000 per second at peak.
• Aggregates visible to advertisers within 1 minute of the click.
• Redirect latency under 50 ms at p99.
• Raw events at about 500 bytes each are 5 TB per day, retained for 90 days in a data lake.
• Minute-level aggregates: 2 million ads times 1,440 minutes times a few dimension combinations is a few billion rows per day before rollup, so older data is rolled up to hourly and daily.
• No click is lost once accepted; the pipeline must recover from failures without double counting.

A lightweight click service logs each click to Kafka and immediately redirects the user, keeping the hot path free of aggregation work. A stream processor deduplicates clicks by click id, filters fraud, and aggregates counts in one-minute tumbling windows using event time, with checkpoints that give exactly-once results into an OLAP database. Raw events are also archived to a data lake, where a daily batch job recomputes the totals that drive billing and corrects any drift in the streaming numbers.

Click. The ad markup links to GET /click?ad=&imp=&sig=. The click service validates the signature (which proves the impression was served by us), generates a unique click id, appends the event with timestamp, ad id, campaign id, user and device info to Kafka, and returns 302 to the landing page. The Kafka write is acknowledged before the redirect so no click is lost.

Streaming aggregation. The stream aggregator reads the topic partitioned by ad id. It drops duplicates by checking each click id against keyed state with a 24 hour TTL, applies fraud rules (for example, many clicks from one IP on one ad within seconds), and adds valid clicks to one-minute tumbling windows keyed by ad id and dimensions. Windows close on event time with a watermark that allows a few minutes of lateness. Closed windows are written to the OLAP store as upserts keyed by (ad_id, minute, dimensions).

Query. The advertiser dashboard calls the query service, which runs a grouped sum over minute, hourly, or daily tables depending on the requested range.

Daily reconciliation. A batch job reads the full day of raw events from the lake, applies the same dedup and fraud logic with the benefit of complete data, and writes authoritative daily totals. Billing uses these numbers, and any difference from the streaming totals overwrites the corresponding rows.