#20HardSearch, Autocomplete & Big Data

Design Google Analytics - User Analytics Dashboard and Pipeline

ASKED IN INTERVIEWS AT:MicrosoftMetaQualtricsGoogle

EXECUTIVE SUMMARY & PROBLEM STATEMENT

Design an enterprise analytics ingestion pipeline receiving client clickstream beacons, computing real-time conversions, and serving sub-second interactive OLAP queries.

SCALE ESTIMATION & TRAFFIC ENVELOPE

DAILY ACTIVE USERS / TPS1 Billion events ingested daily
READ / WRITE TRAFFIC RATIOExtreme write bias; complex analytical queries
5-YEAR STORAGE CAPACITYCompressed columnar storage: ~10 TB/month in ClickHouse/BigQuery

FUNCTIONAL REQUIREMENTS

  • [1]Collect pageview and custom event beacons from websites
  • [2]Compute unique visitor counts, bounce rates, and session durations
  • [3]Serve interactive dashboards filtering across custom dimensions

NON-FUNCTIONAL REQUIREMENTS

  • •Ingest 1 Million events/sec
  • •Dashboard queries return in < 500ms
  • •Cost-effective storage of billions of events

CORE ARCHITECTURAL COMPONENTS

HTTP Beacon Collector
Kafka Message Bus
Flink Real-Time Sessionizer
ClickHouse Columnar Warehouse
Dashboard Query Engine

DATA SCHEMA & PERSISTENCE

RawEvents (site_id, timestamp, visitor_id, session_id, url, referrer, browser, country)

DISTRIBUTED TRADE-OFFS & FAILURE MODES

  • ⟡HyperLogLog provides 98.5% accurate unique visitor counts using 1.5 KB memory, versus exact counts requiring multi-gigabyte hash sets.