Live analytics for advertisers at two million writes a day

On an advertising platform, what a customer pays for is proof that their listing was seen. When a repair shop pays for a listing, the only question that matters at renewal is how many people saw it and how many called.

The analytics are what justifies the invoice, which rules out buying an off-the-shelf tool. General web analytics measures traffic to a site. It does not measure the value delivered to one advertiser, and it cannot put that number back into the advertiser's account, in their dashboard, next to their renewal.

The system had to take writes at volume, serve live reads and aggregate a whole year quickly, all at the same time. Each of these is easy on its own.

A time-series database for ingest, with a deliberately small index. I put identifiers such as listing, user and device into fields. In a time-series store tags are indexed and fields are not, so putting a listing ID in a tag means every new listing creates a new series, and eventually the index suffocates. This is the single most common way these installations fail.

Keeping listing IDs out of the index meant the time-series store could not group by listing efficiently, so that grouping was done in the relational database.

Daily rollups in the relational database. Raw events were kept for seven days, with a daily export to object storage as the archive. A daily summary per listing landed in the relational database, and that is what the dashboard and the invoices queried.

Because of the rollups, the annual report never touched a raw row: it read 365 summarized rows per listing.

Speed measured on the largest accounts. The under-one-second figure holds for accounts with over 900 listings, where a year of daily rollups is roughly 330,000 rows aggregated in a single query. A report that returns in under a second on an account with three listings proves nothing about the architecture, so I measured at the worst case rather than the average.

Total and unique views in separate buckets. I counted unique views by session and by page URL, so someone who searched in two cities was not counted as one view: they were looking for two different things. Totals are the bigger number and look better, while uniques tell an advertiser how many different people saw them. I kept both, so neither number had to be hidden.

Bot filtering at three layers. I filtered bots at the firewall, by known addresses and by user agent. On an advertising platform an inflated view count damages trust, because an advertiser who sees five hundred views and no calls stops believing the number, and then stops believing the invoice.

• 2M+ writes per day into the time-series database
• Advertisers read their numbers live in the dashboard
• Annual report under one second at 900+ listings, reading 365 daily rollups per listing
• The same data feeds the yearly report sent before each renewal

Seven days of raw retention was chosen early and never revisited. It was almost certainly right, but I never checked what we lost by it: whether anyone ever needed to reprocess a window longer than a week, and what it would have cost to keep thirty days. I picked a number and moved on.