Design a Cohort Analysis Platform

Problem statement

Design a multi-tenant cohort analysis platform that ingests user creation and activation events, tags each user into a cohort by a shared start event (signup week, first purchase, feature activation), tracks the same users' activity over subsequent periods, and serves retention tables and curves to analysts with interactive latency.

The naive formulation looks like a GROUP BY: group users by signup week, join with activity events, count distinct users per offset. At billions of events that join is exactly what breaks. Retention at offset k is the cardinality of an intersection: users in cohort c AND users active during period (t_c + k). Approximate distinct sketches such as HyperLogLog merge under union but not under intersection, so they cannot answer retention precisely. The platform needs an exact, mergeable, compact membership structure - compressed bitmaps - plus columnar storage and pre-aggregation to make scans cheap.

The brief requires ingestion of user creation and activation events, cohort tagging by time or attribute, activity tracking across subsequent intervals, and retention tables or graphs, with efficient time-series queries, partitioning by event date or user ID, a UI for dynamic cohort definition, and scale to many cohorts and billions of events.

Public operating baseline versus design assumptions

Public evidence shows the category is real and large. Amplitude's public materials describe processing trillions of events per year across customer apps; Mixpanel publicly claims comparable annual event volumes; Cloudflare publicly reported a peak of about 78 million HTTP requests per second (April 2023) and runs ClickHouse for log and HTTP analytics; LinkedIn built Apache Pinot and its engineering blog reports serving over 100,000 QPS of user-facing analytics. These are company-reported figures used as context, not requirements for our system.

For capacity planning this answer explicitly assumes a mature multi-tenant platform: 3,000 tenant apps, 400M tracked end-users, 1.5B events/day average (~17.4k/s) with a 5x event peak (~87k/s, provisioned at 90k/s), 60k analyst seats with 8k concurrent at peak, 50k saved cohorts, and 10k ad-hoc cohort queries per day. Unless tied to a citation, every number is a stated design assumption.

The four architectural planes

1. Ingestion plane: SDKs, gateway, validation, dedupe, streaming bus.
2. Compute and storage plane: stream processors, columnar event store, identity store, bitmap builder, rollups.
3. Serving plane: cohort definition service, query engine, cache, chart and export APIs.
4. Governance plane: tenancy, RBAC, PII erasure, audit, data quality.

A strong answer keeps these planes separate: ingestion correctness never waits on query load, and query freshness is explicitly watermarked rather than silently stale.