Design web analytics (Google Analytics)

Every time a page on a news site, an online store or a personal blog loads, a few lines of JavaScript tell an analytics service about it. Later the owner opens a dashboard and sees pageviews, unique visitors, bounce rate, top pages, referrers, countries, devices, and how many people are on the site right now. One dashboard for one site is a weekend project. The design gets interesting because one service does this for a million sites at once, and they differ by five to six orders of magnitude: a personal blog logs a hundred events a day, a big news site thirty million — and ten times that on the evening an election result or an IPL final lands. Events come from browsers you do not control, ad blockers drop some, bots fake others, and privacy law makes the obvious way to recognise a returning visitor — a cookie — a poor default. You will design a service that never slows a customer’s page, answers a 30-day dashboard in under a second for the blog and the news site alike, and counts people honestly without identifying them.

Understand the problem

Web analytics sounds like counting, and weak answers start with a counter per page. Agree on three things first: what a “visitor” is when you may not store identifiers, how fresh and how exact the numbers must be, and how unevenly traffic is spread across tenants. This is not billing. Nobody is invoiced from these numbers, so they must be consistent and close, not exact to the event; the billing-grade cousin of this problem is ad click aggregation.

Functional requirements

1. Site owners should be able to track their site with one script tag. It records pageviews automatically, including route changes in single-page apps, plus custom events such as “Signup” or “Add to cart” with a few properties.
2. Site owners should be able to see a dashboard for any date range: pageviews, unique visitors, visits (sessions), bounce rate and visit duration, as a time series and broken down by page, referrer source, country and device, with filters such as “only visitors from Instagram on mobile”.
3. Site owners should be able to see who is on the site right now: visitors in the last five minutes, with their pages and sources, refreshed about every 30 seconds.
4. Site owners should be able to define funnels — an ordered list of pages or events — and see how many visitors reached each step, and where they dropped off, over a date range.

Non-functional requirements

• Ingest scale. 1M sites and 50B events a month: about 19k events/s on average. Design for 100k events/s at peak, of which one site may send 30k/s during a big live event.
• Never hurt the customer’s site. The script is small, loads asynchronously and never blocks rendering. Collector availability of 99.99%, because an event the collector does not accept is lost for good; dashboards can live with 99.9%.
• Freshness. Dashboards include events from under a minute ago; the realtime view lags by under 30 seconds.
• Query latency. A dashboard panel answers in under 300 ms at p50 and under 2 s at p99 for a 30-day range — for the biggest site too. Funnels within 5 s. Ranges over a year may take longer.
• Accuracy. Pageview and event counts exact relative to what was received; unique visitors within about 1–2%; anything computed from a sample is labelled as such.
• Durability and retention. An accepted event survives the loss of any machine or zone. Raw events kept 13 months (enough for year-on-year), daily rollups for the life of the account.
• Privacy. No cookies by default, no raw IP address or full user agent at rest, and a site’s data deleted within days of a request.
• Isolation. One site’s spike must not move another tenant’s dashboard p99.

Out of scope

• Billing-grade counting and ad attribution. When money depends on every event, you need exactly-once pipelines and audits; that is ad click aggregation.
• Server and infrastructure metrics. CPU, latency and alerting are time series per host, not events per visitor: metrics monitoring and alerting.
• Session replay and heatmaps. They record page snapshots and mouse paths — megabytes per visit in blob storage — which is a different pipeline.
• Cross-device identity. Recognising one person on a phone and a laptop needs logins and consent; see the follow-up questions.
• Accounts, plans and invoices for the analytics product itself. The usual SaaS control plane, covered in multi-tenant SaaS platform and usage metering and billing.

The set-up

Plan the approach

Data flows one way: browser → collector → log → enrichment → store → dashboard. Build that write path first, then one read path per requirement, and say the shape early: “an append-only ingest path, a columnar store sorted by site, and pre-aggregation for whatever is too big to scan.” The deep dives are where this problem is won: collection, identity without cookies, distinct counting, storage, sessions and funnels, the one huge tenant, and the realtime path.

Core entities

• Site. site_id, domain, owner, plan, and time zone — “today” for an Indian news site starts at midnight IST, not UTC.
• Event. One pageview or custom event: site, timestamp, name, page path, referrer source, UTM tags, country and city, browser, OS, device type, custom properties, visitor ID and session ID. Immutable once written.
• Visitor ID. A 64-bit hash that identifies one browser on one site for one day, and nothing more.
• Daily salt. A random secret, replaced every midnight and deleted after two days.
• Session (visit). Site, visitor, start and end, entry and exit page, pageviews, events, and whether it bounced.
• Rollup row. Site, day (or hour), one dimension value → counts plus a HyperLogLog state.
• Funnel. Site, name, ordered steps (a page pattern or an event name) and a time window.

The events table is the centre of the design. In ClickHouse it looks like this:

CREATE TABLE events
(
    site_id         UInt64,
    timestamp       DateTime,
    name            LowCardinality(String),  -- 'pageview' or a custom event name
    visitor_id      UInt64,                  -- daily-salted hash, never an IP
    session_id      UInt64,
    hostname        LowCardinality(String),
    pathname        String,
    referrer_source LowCardinality(String),  -- 'Google', 'Instagram', 'Direct / None'
    utm_campaign    String,
    country         LowCardinality(FixedString(2)),
    city_geoname_id UInt32,
    device          LowCardinality(String),  -- 'Mobile', 'Desktop', 'Tablet'
    browser         LowCardinality(String),
    os              LowCardinality(String),
    props           Map(String, String)      -- custom properties, size-capped
)
ENGINE = MergeTree                           -- ReplicatedMergeTree in production
PARTITION BY toYYYYMM(timestamp)
ORDER BY (site_id, toDate(timestamp), name, visitor_id, timestamp)
SAMPLE BY visitor_id
TTL timestamp + INTERVAL 13 MONTH;

API

There are two surfaces: the collector the script calls millions of times a minute, and the stats API the dashboard calls.

The collector. One endpoint:

POST /api/event HTTP/1.1
Host: collect.example-analytics.com
Content-Type: text/plain;charset=UTF-8
User-Agent: Mozilla/5.0 (Linux; Android 14; ...) Chrome/130.0 Mobile Safari/537.36

{"n":"pageview","u":"https://news.example.in/ipl/final-live?utm_source=whatsapp","d":"news.example.in","r":"https://www.google.com/"}

HTTP/1.1 202 Accepted
Access-Control-Allow-Origin: *

A custom event adds a name and properties: {"n":"Add to cart","u":"…","d":"shop.example.in","p":{"sku":"KURTA-42"}}. The body goes as text/plain, which browsers treat as a simple cross-origin request, so no CORS preflight precedes each beacon. The response is an empty 202 that the script never reads. And no cookies travel either way; the collector sees the IP and user agent only because every HTTP request carries them.

The stats API, used by the dashboard and by customers’ own scripts:

GET /api/v1/stats/timeseries?site_id=42&period=30d&interval=day
    &metrics=visitors,visits,pageviews,bounce_rate,visit_duration
    &filters=source==Instagram;device==Mobile

{
  "results": [
    { "date": "2026-10-26", "visitors": 184021, "visits": 251108,
      "pageviews": 993041, "bounce_rate": 41.2, "visit_duration": 187 }
  ],
  "sampled": false,
  "timezone": "Asia/Kolkata"
}

The rest of the surface follows the same pattern:

GET  /api/v1/stats/breakdown?site_id=42&period=7d&property=page&limit=10   top pages
GET  /api/v1/stats/realtime?site_id=42                                    visitors in the last 5 min
PUT  /api/v1/sites/913/funnels/kurta-purchase                             steps + window (control DB)
GET  /api/v1/sites/913/funnels/kurta-purchase?period=7d                   counts per step

{
  "funnel": "kurta-purchase",
  "window_s": 1800,
  "steps": [
    { "step": "View /products/*", "visitors": 10000 },
    { "step": "Add to cart",      "visitors": 2500, "from_previous_pct": 25.0 },
    { "step": "Visit /checkout",  "visitors": 1000, "from_previous_pct": 40.0 },
    { "step": "Purchase",         "visitors": 600,  "from_previous_pct": 60.0 }
  ],
  "sampled": false
}

The sampled flag tells the owner when a number came from a sample.

Back-of-envelope estimates

These numbers decide whether rollups are needed, how many shards you run, and where the bottleneck is. (Method: back-of-envelope estimation.)

Events. 50B a month ÷ 30 days ≈ 1.67B events/day; ÷ 86,400 s ≈ 19k events/s on average. Evening peaks run about 3× the average, roughly 58k/s; add one big live event on top and plan for 100k events/s.

Skew. The mean site sends 1.67B ÷ 1M ≈ 1,700 events/day; the median far fewer, around a hundred. Assume the top 1,000 sites send half of everything — 835M ÷ 1,000 ≈ 835k events/day each — and the biggest single site 30M a day, about 2% of the total. On an IPL final night that one site can send 30k events/s.

Ingest bandwidth. A beacon is about 400 B of body plus 400 B of headers (the user agent alone is around 150 B): 100k × 800 B = 80 MB/s ≈ 640 Mbit/s at peak, and 1.67B × 800 B ≈ 1.3 TB a day. A collector parses, does an in-memory lookup or two and forwards; plan for about 10k requests/s per pod: 100k ÷ 10k = 10 pods at peak, 20 for zone spread and headroom.

Kafka. Raw and enriched topics together carry about 1 KB per event: 1.67B × 1 KB ≈ 1.7 TB/day before compression. At about 4× compression, three replicas and three days’ retention: 1.7 ÷ 4 × 3 × 3 ≈ 3.8 TB of broker disk. With 64 partitions per topic, peak raw traffic of 100k × 600 B = 60 MB/s is under 1 MB/s per partition.

Event storage. An enriched event has about 25 columns and takes roughly 300 B in a row format, mostly URLs and referrers. In a row store that is 1.67B × 300 B ≈ 500 GB/day, about 200 TB for 13 months before indexes and replicas. A columnar store sorted by site and time compresses far better: Cloudflare reported 36.74 B per row in ClickHouse for an HTTP log of over 100 columns that took 1,630 B per message as Cap’n Proto. Assume 40 B per row for our narrower but URL-heavy rows: 1.67B × 40 B ≈ 67 GB/day; × 395 days ≈ 26 TB; × 2 replicas ≈ 53 TB.

Sessions and rollups. At about 3 events per session there are 1.67B ÷ 3 ≈ 550M sessions/day; at 50 B per compressed row that is 28 GB/day, about 22 TB for 13 months with two replicas. Daily rollups hold one row per site, day and dimension value: 600k active sites × ~60 rows + 1,000 big sites × ~50k rows ≈ 36M + 50M ≈ 90M rows/day, about 19× fewer than events. At 50 B a row that is 4.5 GB/day, about 3.6 TB for 13 months with replicas. In total about 80 TB: 16 shards × 2 replicas = 32 servers holding about 2.5 TB each, with disk headroom for background merges.

Dashboard queries. 200k dashboard loads in the peak hour × ~10 panels = 2M queries/hour ≈ 560 queries/s. Realtime views: 20k open at the peak, polling every 30 s ≈ 670 requests/s — kept off ClickHouse.

Scan size. A median site’s 30 days are a few thousand rows: one or two 8,192-row granules. The biggest site’s 30 days are 30 × 30M = 900M rows; just its visitor ID column is up to 900M × 8 B ≈ 7.2 GB to read and hash. A daily rollup answers the same question from 30 rows.

High-level design

Build the design one requirement at a time; by the end it works for a few thousand sites in one region.

Site owners track their site with one script tag

The script is the only code that runs on someone else’s site, so it does little: load with defer from a CDN, send a pageview, send another when a single-page app changes the URL, and expose a function for custom events.

The write path in five steps:

1. The browser fetches the script once from a CDN and caches it.
2. On each page, the script sends a small beacon to the collector: event name, URL, referrer and site domain.
3. The collector checks the site and the page’s hostname, stamps server time, looks up the country from the IP, computes the visitor ID (see the deep dives), discards the IP, replies 202 and appends to Kafka’s raw-events topic, keyed by site and visitor.
4. Enrichers consume the topic. They parse the user agent into browser, OS and device type and then drop it, classify the referrer (google.com → Google, l.instagram.com → Instagram, no referrer → Direct / None), pull out UTM tags, and drop bots and referrer spam into a counted “dropped” bin rather than losing them silently.
5. A loader batches clean events and inserts them into ClickHouse every few seconds. Batching is not optional: each insert creates a new data part that background merges must later fold together, so thousands of tiny inserts a second would bury the store in merge work.

Kafka sits in the middle because the two sides move at different speeds. Collectors must accept every event the moment it arrives; everything downstream can fall behind for minutes on election night and catch up later from the log.

Site owners see a dashboard for any date range

A dashboard is a dozen panels, and each panel is one query: a time series, a top-10 breakdown, or a handful of totals. The stats API authenticates the owner, loads the site’s settings (time zone, plan limits) from a small control database, and turns each panel into SQL that always starts by filtering on one site and a time range.

1. The owner opens the dashboard for the last 30 days; the UI fires one request per panel.
2. The stats API checks that the owner may see site 42 and loads its time zone, so “a day” means a day in IST.
3. It builds SQL — visitors are distinct visitor IDs, pageviews are rows named pageview — and sends it to ClickHouse.
4. Results come back as rows per day or per dimension value and are returned as JSON.

For now, bounce rate and visit duration are computed at query time by grouping each visitor’s events into visits; a deep dive moves this to ingest. The control database is an ordinary PostgreSQL instance: sites, users, plans and funnel definitions, a few million rows, never on the event path.

Site owners see who is on the site right now

“Visitors now” is a different kind of question: tiny time range, asked over and over, and most interesting exactly when the site is busiest. Plausible defines current visitors as those active in the last five minutes and refreshes its realtime view every 30 seconds — reasonable targets to adopt. Rather than asking ClickHouse every 30 seconds per open tab, add a live counter: a consumer of the enriched stream that keeps, per site and per minute, the set of visitor IDs it has seen, in Redis with a short expiry.

1. The enricher publishes each clean event to an enriched-events topic.
2. The live counter adds the visitor ID to the key for that site and minute, such as rt:42:1031, which expires after 35 minutes.
3. The stats API answers /realtime by unioning the last five minute keys, and charts the last 30 one-minute counts.
4. The realtime view polls every 30 seconds.

Site owners define funnels

A funnel asks “of the people who viewed a product, how many added it to the cart, started checkout and paid — in that order, within 30 minutes?” Funnel definitions live in the control database; the counting happens at query time, because owners edit steps and want the answer for any past range immediately. ClickHouse has a function built for this: windowFunnel(window)(timestamp, cond1, cond2, …) returns, per group, the deepest consecutive step reached inside a sliding time window.

SELECT level, count() AS visitors
FROM
(
    SELECT visitor_id,
           windowFunnel(1800)(timestamp,
               name = 'pageview' AND pathname LIKE '/products/%',
               name = 'Add to cart',
               name = 'pageview' AND pathname = '/checkout',
               name = 'Purchase') AS level
    FROM events
    WHERE site_id = 913 AND timestamp >= now() - INTERVAL 7 DAY
    GROUP BY visitor_id
)
GROUP BY level
ORDER BY level;

The query returns how many visitors stopped at each level; visitors who reached step k are those at level k or deeper, a running sum from the bottom.

You now have a working service: events flow in without slowing pages, dashboards are SQL over one table, the realtime view has its own path, and funnels run on demand. The deep dives are about what breaks with a million sites, privacy rules, and one site a million times bigger than the median.

Deep dives

How do we get events out of the browser without slowing pages or losing them?

The script has two jobs that pull against each other: it must never make the customer’s page slower, and it must not lose the events that matter most — which tend to happen just as the visitor leaves.

Bad: send the last event from unload. Trackers used to send their final event from an unload or beforeunload handler, with a synchronous request or an image they waited for. Both hold up the page while it unloads, so the next page feels slow. Worse, on phones these handlers often never run: MDN’s guidance walks through a user switching apps and later killing the browser, where neither unload nor pagehide fires. You pay in your customers’ page speed and still lose the data.

Good: asynchronous beacons at the right moments. Send the pageview as soon as the script runs, using fetch with keepalive: true so the request survives a quick click onward. Send end-of-page data — time on page, scroll depth — when document.visibilityState becomes hidden, using navigator.sendBeacon: the browser queues a small POST (up to 64 KiB) and delivers it without delaying navigation. For single-page apps, also send a pageview when the URL changes through history.pushState or the back button. Keep a 1×1 image request as a fallback for pages that cannot run scripts; bots love it, so it gets extra scrutiny.

Great: beacons, plus a first-party path and a collector that only appends. Two losses remain after the beacons are right. The first is ad blockers: filter lists block well-known analytics hostnames, and Plausible’s documentation puts the share of visitors hidden by strict blockers at typically 5–25%, depending on the audience. A first-party proxy recovers most of them: the site serves the script and the event endpoint from its own domain (news.example.in/s/e), and a CDN rule or a few lines of server config forward them to your collector. You pay with setup work on the customer’s side, and with the trap in the callout below.

The second loss is the collector itself. A beacon is never retried, so the moment the collector accepts an event is the durability boundary. Keep it boring: no database calls on the request path (site list, geo data and current salt are held in memory and refreshed every minute), writes to Kafka in batches a few milliseconds long, and a local disk spool if Kafka is unreachable. A crash loses only the current batch — a rounding error against 1.67B a day, so this design accepts it rather than paying for exactly-once delivery, which ad click aggregation needs because its numbers are money.

How do we count unique visitors without cookies?

“Unique visitors” needs some way to tell that two pageviews came from the same person. There are three families of answers.

Bad: third-party cookies or fingerprinting. A cookie on the vendor’s own domain recognises a person across every site using the vendor — which is why several major browsers now block third-party cookies by default and regulators treat it as tracking. Fingerprinting (screen size, fonts, canvas rendering combined into a stable ID) dodges cookie rules on paper but is still tracking without consent.

Good: a first-party cookie holding a random client ID. The script stores a random ID in a cookie on the site’s own domain and sends it with every event. You gain real returning-visitor counts, funnels that span days, and retention cohorts. You pay with a consent banner in the EU and similar regimes, gaps where visitors decline, and inflation from people who clear cookies or browse privately — each returns as “new”.

Great, as the default: a daily-rotating salted hash. For each event, compute visitor_id = hash(salt_today, user_agent, ip, site) with a keyed hash, then throw the IP away. Plausible’s open-source code does exactly this: SipHash over the user agent, IP and domain, keyed with a random salt that a scheduled job replaces at midnight, with salts older than 48 hours deleted. Within a day, the same browser on the same network gets the same ID, so you can count unique visitors and build sessions. Across days the IDs are unrelated, and once the salt is gone not even you can link them.

You gain counting with no cookie, no device storage and nothing personal at rest. You pay in what the metric means: “unique visitors in October” becomes, in effect, the sum of daily unique visitors — a daily reader counts 31 times — as Plausible’s metric definitions say openly. The hash also errs both ways. Two people behind one carrier-grade NAT with the same phone model and browser version merge into one, common on Indian mobile networks where many phones share a public IPv4 address; one person who moves from office Wi-Fi to mobile data mid-visit splits into two.

Two details finish the design. First, midnight: a visit from 23:58 to 00:05 would split when the salt changes, so the session builder also looks for an open session under the ID computed with yesterday’s salt — Plausible’s session code does exactly this, one reason the previous salt is kept. Second, the hash is computed in the collector, which already holds the salt in memory, so the IP never reaches Kafka, the archive or the store. Sites with consent can also opt in to a persistent first-party ID (see the follow-ups).

Why don’t unique visitors add up, and how do we count them for any range?

Even with a visitor ID, “how many distinct IDs?” is the most expensive question on the dashboard, because distinct counts are not additive. A hundred visitors at 09:00 and 120 at 10:00 might be 130 people or 220.

Bad: store distinct counts per hour or per page, then add them. Cheap and wrong. Every returning visitor is counted again, so the total drifts upward with the number of buckets: a day built from 24 hourly counts can easily be double the truth.

Good: exact COUNT(DISTINCT visitor_id) over raw events. Always right, but exact counting must remember every ID it sees: for the news site’s 5M daily visitors, a hash set of about 5M × 16 B ≈ 80 MB per query, times every group in a breakdown — after reading 7.2 GB of visitor IDs for 30 days of raw rows.

Great: store mergeable sketches, and merge them at query time. A HyperLogLog sketch hashes each ID, uses some bits of the hash to pick one of m registers, and keeps in that register the longest run of leading zeros it has seen in the rest. Flajolet and colleagues show in the original paper that the standard error is about 1.04/√m: with 16,384 registers that is 1.04 ÷ 128 ≈ 0.8%, in 16,384 × 6 bits = 12 KiB, whether the set holds a thousand IDs or a billion. The property that matters most here: two sketches merge by taking the larger value in each register, and the result is exactly the sketch you would have built from both streams together. So hourly or daily sketches stored in a rollup combine into any date range, any set of pages and any set of shards.

Practical implementations keep small sets in a sparse form and switch to registers as they grow, so a blog with 40 visitors still sees 40. Google Analytics estimates its own session and user counts with HyperLogLog++ and notes they can differ slightly from exact counts in its raw export. You pay a small, bounded, documented error, and sketches answer only “how many?”, never “which ones?”.

How does this fit with daily salts? IDs never repeat across days, so merging 30 daily sketches gives the sum of daily uniques — the metric’s documented meaning, not a bug. Merging still does real work within a day (24 hourly sketches, or five realtime minutes), across pages and sources (“visitors to any /ipl page”), across shards, and for opted-in persistent IDs, where a 30-day merge counts each person once. More in count-min sketch and HyperLogLog.

How do we store 50B events a month and still answer in under a second?

Every query names one site and a time range, then groups by a few columns. That shape — read a slice, touch few columns, aggregate — is OLAP, and the storage layout should be built around it.

Bad: a row store with an index on (site_id, timestamp). Familiar, and fine for a few thousand sites. But it reads whole rows to get at one column and stores about 500 GB a day before indexes; a 30-day visitors chart for the biggest site reads 900M rows × 300 B = 270 GB.

Good: a columnar table sorted by site, then time. In ClickHouse’s MergeTree the sort key is also a sparse index: one mark per 8,192-row granule, small enough to live in memory. With site_id first, a site’s rows are contiguous, so a query skips every granule belonging to anyone else, and a median site’s month is a granule or two. Only referenced columns are read, and sorted values compress well. Partition by month — partitions exist to drop old data cheaply, not for speed — and never by tenant: the MergeTree docs say to put the client identifier first in the sort key instead. What remains: the biggest sites still scan hundreds of millions of rows.

Great: add rollups, sampling and a router. Three additions make big sites bounded:

• Rollups as materialized views. A materialized view runs on every inserted batch and writes partial aggregates into an AggregatingMergeTree table, which keeps merging rows with the same key in the background. Counts are stored as sums, unique visitors as sketch states.
• Sampling by visitor. SAMPLE BY visitor_id lets a query read a fixed fraction of visitors. Because it samples whole visitors rather than random rows, sessions and funnels stay internally consistent; you scale the counts up and flag the answer as sampled.
• A query router in the stats API picks the cheapest table that answers exactly: daily rollups for totals and single-dimension breakdowns over closed days, hourly rollups plus the raw tail for today, the raw table for combined filters (“Instagram and mobile and /ipl pages”), with a 10% sample when the range is huge.

CREATE TABLE pages_daily
(
    site_id   UInt64,
    day       Date,
    pathname  String,
    pageviews SimpleAggregateFunction(sum, UInt64),
    visitors  AggregateFunction(uniqCombined, UInt64)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (site_id, day, pathname);

CREATE MATERIALIZED VIEW pages_daily_mv TO pages_daily AS
SELECT site_id, toDate(timestamp) AS day, pathname,
       count() AS pageviews,
       uniqCombinedState(visitor_id) AS visitors
FROM events
WHERE name = 'pageview'
GROUP BY site_id, day, pathname;

-- Top pages, last 30 days: merge the stored states.
SELECT pathname, sum(pageviews) AS pageviews, uniqCombinedMerge(visitors) AS visitors
FROM pages_daily
WHERE site_id = 42 AND day >= today() - 30
GROUP BY pathname
ORDER BY visitors DESC
LIMIT 10;

Behind the chart: 30 daily sketches of 12 KiB are about 370 KB, and a month of top pages for the biggest site is 30 days × ~20k pages ≈ 600k rollup rows, tens of milliseconds. Cloudflare uses the same pattern, keeping unique visitors as aggregate states in rollup views, and cut its rollup tables’ index granularity from 8,192 to 32 rows, which halved query latency and roughly tripled throughput — big granules suit scans, small ones suit lookups. What you pay: every rollup is another table to keep in step, and unplanned filter combinations fall back to raw data. Don’t pre-aggregate every combination; each dimension multiplies the rows.

How do we build sessions, bounce rate and funnels?

A session (visit) is a run of events from one visitor with no gap longer than 30 minutes — the default in both Google Analytics and Plausible. A visit is a bounce if it has one pageview and no interactive custom event. Visit duration, entry page and exit page all depend on sessions, so where you build them matters.

Bad: build sessions at query time. Sort each visitor’s events by time and cut at 30-minute gaps with window functions, on every dashboard load. Always correct and easy to change, but for the biggest site a 30-day bounce rate re-sorts 900M rows per query.

Good: an hourly batch job writes a sessions table. Queries become cheap reads of a small table, but sessions arrive up to an hour late, and a visit that straddles a batch boundary is split unless you stitch it.

Great: build sessions in the stream, at ingest. The enriched-events topic is keyed by site and visitor, so one sessionizer worker sees all of a visitor’s events in order. It keeps open sessions in memory; each event either extends the open session (last event under 30 minutes ago) or starts a new one. Sessions are written as cancel-and-replace rows, the pattern Plausible’s ingest code uses: each update writes the previous version with sign = −1 and the new one with sign = +1. Background merges cancel the pairs, and until then queries weight by sign, so sum(sign * is_bounce) / sum(sign) is the bounce rate either way. Memory is modest: 550M sessions a day ÷ 48 half-hours × 3 at peak ≈ 35M open sessions × ~300 B ≈ 10 GB, about 165 MB on each of 64 workers. You pay with stateful workers: a crash or rebalance loses open sessions and splits some visits, unless you checkpoint state as Flink does.

Funnels stay at query time, because owners edit steps and expect answers for past ranges, but their cost grows with the site’s events in range. For the biggest sites, run windowFunnel on SAMPLE 0.1 and multiply by ten: sampling whole visitors keeps each sampled funnel complete, where a random 10% of rows would break most funnels mid-way. Saved funnels on the largest sites can be precomputed per closed day overnight. Daily IDs mean a funnel cannot follow a visitor past midnight — rarely a problem in a 30-minute window. The trade-off throughout is batch versus stream.

What happens when one site sends a hundred times its normal traffic?

On election-results day a news site that usually sends 300 events a second climbs to 30,000 for hours, and its newsroom keeps the realtime view on a wall screen. Walk the pipeline and ask where one site’s traffic piles up.

Ingest is safe if you pick keys carefully. Collectors and enrichers are stateless and scale out. Kafka topics are keyed by site and visitor, so one site spreads over all 64 partitions while each visitor’s events stay in order on one. Keyed by site_id alone, the whale would pin one partition and one consumer while 63 sat idle. The pressure points are the store, the queries and the realtime counter.

Bad: shard the store by site_id. Ideal for a million small sites — every query touches one shard. But the whale’s 300M events for the day, and all its heavy queries, land on one machine, and thousands of small sites on that shard see their dashboards slow down: a noisy neighbour you built in.

Good: spread rows randomly. Load is perfectly even, but every query fans out to all 16 shards: 560 queries a second becomes about 9,000 shard requests a second, nearly all for sites with a few hundred rows, and each answer waits for the slowest of 16 machines.

Great: shard by site, then by visitor for the few whales. Small sites keep site_id as the sharding key. A site above a threshold — say 1M events a day — is promoted to a shard group of 4–8 shards, with rows placed by hash(site_id, visitor_id). Keeping each visitor on one shard is the point: sessions, funnels and per-visitor work run locally, and only counts and sketches are merged by the coordinating node. Promotion applies to new days; the router remembers where older days live. You pay with a routing table and cross-shard merges for a few hundred customers.

Layout alone does not stop one tenant’s heavy queries from hurting others, so add guards on the read path — the subject of noisy neighbours and fairness:

• Per-query limits on rows read, memory and run time; past them, answer from a sample and say so rather than letting a two-year funnel run for minutes.
• Per-tenant concurrency caps, a few heavy queries per site at a time, queued fairly.
• Caching of closed days. Yesterday’s numbers are final once late events have landed, so cache them per site and query only today.
• An ingest guardrail: a per-site rate limit at the collector set at about 20× the plan, which stops bot floods and runaway scripts without throttling a real spike. Real spikes are accepted; floods are dropped and counted.
• A dedicated cell for the handful of largest enterprise customers, so their election night is entirely their own.

How do we show “visitors right now” without hammering the store?

With 20,000 realtime views open at the evening peak, polling every 30 seconds, that is about 670 reads a second. The hard part is the one site with half a million people on it.

Bad: query ClickHouse on every poll. It works, but it ties the realtime view to the analytical cluster: 670 queries a second on the freshest, least-merged parts of the table, competing with month-long reports, and only as fresh as the loader’s last batch.

Good: exact sets in Redis, per site and minute — the step 3 design. Cheap and exact for 999,990 sites. For the news site with 500k visitors in five minutes, each minute’s set holds up to 500k IDs, about 25 MB at ~50 B per member; every poll unions 2.5M members; and all 30k events a second hit one key on one Redis shard — a hot key.

Great: a sketch per site per minute, pre-merged in the workers. Swap the sets for HyperLogLog sketches — Redis has them built in, and counting several keys at once returns the size of their union. A sketch never exceeds 12 KiB. Then remove the hot key: each counter worker keeps its own in-memory sketch per site and minute and merges it into Redis once a second, so the whale’s 30k writes a second become one merge per worker per second. You pay about a second of extra staleness, invisible behind a 30-second poll, and a 0.8% error on a number that changes every second anyway. Realtime top pages and sources are per-minute counters capped at a few hundred values per site.

The final design

Put the pieces together. The only thing a visitor’s browser waits for is a 202 from a collector doing in-memory work; everything else is asynchronous and can fall behind and catch up.

1. Collect. The script sends beacons, directly or through the site’s first-party proxy. The collector checks the domain and hostname against its in-memory site list, stamps server time, looks up the country, hashes IP, user agent and site with today’s salt, drops the IP, replies 202 and appends to raw-events keyed by site and visitor — spooling to disk if Kafka is unreachable.
2. Enrich. Enrichers parse and then drop the user agent, classify the referrer, extract UTM tags, drop bots, data-centre traffic and referrer spam into a counted bin, and publish to enriched-events.
3. Archive. A sink writes enriched events to object storage as compressed hourly files — the source for replays, backfills and customer exports. No IPs, so the archive is as privacy-safe as the store.
4. Load. The loader inserts batches every few seconds into the events table on the right shard (by site, or by site and visitor for whales); materialized views update hourly and daily rollups with counts and sketch states as part of the same insert.
5. Sessionize. Sessionizer workers apply the 30-minute rule in memory, checking yesterday’s ID around midnight, and write cancel-and-replace session rows.
6. Count live. Live counters keep per-site, per-minute sketches and top-value counters, merged into Redis once a second with a 35-minute expiry.
7. Query. The stats API authenticates the owner, loads the site’s time zone and limits from the control database, and routes each panel to daily or hourly rollups, sessions, or the raw table — sampled by visitor and labelled when the range is huge — under per-tenant limits, with closed days cached.
8. Realtime. /realtime unions the last five minute sketches from Redis; the view polls every 30 seconds.

Check it against the requirements. The page waits only on an async beacon, and 20 collector pods carry 100k events/s with room to spare; accepted events reach three Kafka replicas within milliseconds. Dashboards see events within about 10 seconds, the realtime view within a second plus its poll. Most panels read rollups — 30 rows for a month of totals, about 600k for the biggest site’s top pages — or a few granules for a small site, inside 300 ms at p50; unusual filters on whales fall back to a labelled 10% sample to stay under 2 s. Uniques are within about 1%. Replicas, the Kafka log and the archive make data loss a multi-failure event. No IP touches disk, salts die after 48 hours, and the biggest site is spread, capped and pre-merged, so its election night does not move anyone else’s p99.

What interviewers look for by level

Follow-up questions

What if a customer needs retention cohorts or logged-in journeys across weeks?

Offer an opt-in persistent identifier for sites that collect consent: a random first-party cookie ID, or the site’s own user ID hashed with a per-site key, in its own column so the cookieless default is untouched. Merged sketches of this ID count each person once across days, funnels can span days, and weekly cohorts become a query over first-seen dates. You pay with consent handling, gaps where visitors decline, and personal data you must delete on request — batched daily, since rewriting columnar parts per request is expensive.

What if a bot farm or referrer spammer targets a site?

Filter in layers, cheapest first: known crawler user agents; data-centre IP ranges, checked in the collector before the IP is dropped (Plausible excludes about 32,000 ranges); events whose hostname is not the site’s, which kills “ghost” spam posted straight to the collector; a shared list of referrer-spam domains; and behavioural rules in the sessionizer, such as one visitor loading 100 pages a minute. Prefer precision over recall — deleting real traffic is worse than keeping a few bots — and show owners a “filtered” count so sudden drops are explainable.

What if the pipeline stalls for an hour on election night?

Collectors keep accepting: Kafka holds three days, and they spool locally if it is down. Consumers resume from committed offsets; because event times come from the collector, late-loaded events land in the right minutes and the materialized views fold them into the right days. Give each loader batch a deterministic ID from its partition and offset range so a retried batch is not inserted twice — the full exactly-once toolkit is in ad click aggregation. The dashboard shows “data delayed by N minutes” from consumer lag instead of silently low numbers.

What if EU customers require their data to stay in the EU?

Run the data plane as regional cells: collectors, Kafka, ClickHouse and Redis per region, each site pinned to one region at creation, with its script pointed at that region’s collector. The global control plane holds accounts and settings, never events, and salts are generated per region. You pay with duplicated infrastructure and smaller clusters, and moving a site between regions becomes a planned migration.

What if customers want raw-event exports or their own SQL?

Serve exports from the object-store archive as compressed columnar files per site and day, off the serving path. Ad-hoc SQL on the shared cluster is risky, so run it on a separate replica set or warehouse with row policies pinning every query to the customer’s site_id, plus quotas on rows read — and charge for it.

How to say it in the interview

“I’ll design a multi-tenant web analytics service: a million sites, about 50 billion events a month — roughly 20 thousand a second on average and 100 thousand at peak — with dashboards under a second and a realtime view of the last five minutes.

The script is tiny and asynchronous. It sends the pageview with fetch keepalive and end-of-page data with sendBeacon when the page becomes hidden, because unload handlers don’t fire reliably on phones. Sites can serve it through a first-party proxy to get past ad blockers, as long as the proxy forwards the visitor’s IP.

The collector is stateless. It checks the site, looks up the country, hashes IP, user agent and site with a salt that rotates daily, drops the IP, replies 202 and appends to Kafka keyed by site and visitor. No cookies and nothing personal at rest; the price is that a visitor is unique per day.

Downstream, enrichers parse the user agent and drop bots, and a stream sessionizer applies the 30-minute rule at ingest. Events and sessions go into ClickHouse sorted by site, then time, so a small site’s dashboard reads a granule or two. Materialized views keep daily rollups with counts and HyperLogLog states. Distinct counts don’t add, but sketches merge, so any range is a merge of stored pieces, within about one percent.

The real challenge is skew: the median site sends a hundred events a day, the biggest thirty million, and ten times that on election night. So Kafka is keyed by visitor, not site; whales are spread over a shard group by visitor, so sessions and funnels stay local; queries have per-tenant limits and fall back to a labelled visitor sample when a range is huge. The realtime view reads per-minute sketches in Redis, pre-merged in the workers so one site never becomes a hot key.”

Concepts used in this design

• OLTP vs OLAP, warehouses and columnar storage — why the events table is columnar and sorted by site.
• Count-min sketch and HyperLogLog — mergeable unique-visitor counts.
• Kafka — the buffer between collectors and everything else, keyed by visitor.
• Batch vs stream processing — building sessions at ingest instead of in batches.
• Flink and stream processing — keyed state and checkpoints for the sessionizer.
• Table partitioning — monthly partitions that make retention a cheap drop.
• Database sharding — site-first sharding and shard groups for whales.
• Multi-tenant data — one pooled table with the tenant first in every key.
• Noisy neighbours and fairness — per-tenant query limits and concurrency caps.
• Rate limiting — the ingest guardrail against floods.
• Redis — per-minute sketches behind the realtime view.
• Hot keys, cache stampede and invalidation — why the biggest site needs pre-merging.
• Content delivery network — serving the script and first-party proxying.
• Cell-based architecture — dedicated cells and regional data residency.
• Design ad click aggregation — exactly-once counting when the numbers are money.
• Design top-K trending — streaming heavy hitters, a cousin of “top pages”.

References and further reading

1. Plausible Analytics source code (GitHub)github.comSalts rotated daily at midnight and deleted after 48 hours; visitor ID as a SipHash of user agent, IP and domain under the salt; ingest-time sessions with a 30-minute check written as cancel/insert rows; events sorted by site and date, sampled by user ID, partitioned by month; about 32K data-centre IP ranges excluded.
2. Plausible docs: Metrics definitionsgithub.comUnique visitors counted per day, visits that end after 30 minutes of inactivity, the bounce definition, and current visitors as those active in the last 5 minutes.
3. Plausible docs: Bypass adblockers with a proxygithub.comStrict blockers typically hide 5–25% of visitors; serving the script and endpoint from the site’s own domain recovers most of them.
4. MDN: Navigator.sendBeacon()developer.mozilla.orgBeacons are queued POSTs that never delay unload, the 64 KiB limit, sending on visibilitychange, and why unload and pagehide are unreliable on mobile.
5. Google Analytics Help: About Analytics sessionssupport.google.comSessions time out after 30 minutes of inactivity by default, the session_start event and session ID, and HyperLogLog++ estimation of session and user counts.
6. HyperLogLog: the analysis of a near-optimal cardinality estimation algorithm (Flajolet, Fusy, Gandouet, Meunier, 2007)algo.inria.frStandard error of about 1.04/√m for m registers, about 2% accuracy beyond 10^9 distinct items in 1.5 KB, and optimal parallel (mergeable) computation.
7. ClickHouse docs: MergeTree table engineclickhouse.comThe sorting key as a sparse index over 8,192-row granules, monthly partitions, never partitioning by client ID, SAMPLE BY, TTL, and inserts that create parts merged in the background.
8. ClickHouse docs: AggregatingMergeTreeclickhouse.comIncremental rollups through materialized views that store -State values and are read with -Merge functions; worth it when rows shrink by orders of magnitude.
9. ClickHouse docs: Parametric aggregate functions (windowFunnel)clickhouse.comA sliding-window funnel that returns the deepest consecutive step each group reached, with strict-order and deduplication modes.
10. HTTP Analytics for 6M requests per second using ClickHouse (Cloudflare)blog.cloudflare.com36.74 B per row in ClickHouse versus 1,630 B as Cap’n Proto, unique visitors kept as aggregate states in materialized views, and index granularity 32 on rollup tables halving query latency.