Skip to content
Tutorials Center
My learning

Topics

Lesson 33 of 35 ยท Projects and Practice

Project: A Web Analytics Pipeline

Design a web analytics backend in ClickHouse: raw events table, sorting key, materialized-view rollups, funnel and retention queries, with SQL stand-ins you can run.

Web analytics is the classic ClickHouse workload: a firehose of small events (page views, clicks, purchases), a handful of dashboards asking "how many, by what, over which time" and a requirement for answers in well under a second. In this project you design the whole pipeline: the raw events table, rollups, and the queries behind a dashboard.

ClickHouse cannot run on this site, so the DDL is shown as read-only code. The portable parts (funnels, retention, rollups) run in the editors on generated sample data. The project uses ideas from materialized views, AggregatingMergeTree and the sparse primary index.

Requirements

  1. Ingest up to 50,000 events per second from a collector.
  2. Dashboards: visitors and page views per day, top pages, country breakdown, conversion funnel, weekly retention.
  3. Queries over the last 90 days in under a second, and raw events kept for 13 months.
  4. Many sites (tenants) in one table.

Step 1: the raw events table

SQL
CREATE TABLE analytics.events
(
    site_id     UInt32,
    ts          DateTime,
    visitor_id  UInt64,
    session_id  UInt64,
    event       LowCardinality(String),   -- 'pageview', 'signup', 'purchase'
    url         String,
    referrer    String,
    country     LowCardinality(String),
    device      LowCardinality(String),
    revenue     Decimal(12, 2) DEFAULT 0
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(ts)
ORDER BY (site_id, toDate(ts), event, visitor_id)
TTL ts + INTERVAL 13 MONTH DELETE;

Design choices to explain in your own words:

  • site_id first, because every dashboard filters by one site, which prunes to a contiguous range.
  • toDate(ts) second: a coarse time bucket keeps rows of one day together without making the key too specific.
  • event third, as a low-cardinality column used in most filters, then visitor_id so one visitor's events are close (helps uniq and sessions).
  • Monthly partitions make dropping old data cheap and keep the partition count small.
  • LowCardinality on repetitive strings and Decimal for money.

Step 2: daily rollup with a materialized view

SQL
CREATE TABLE analytics.daily
(
    site_id   UInt32,
    day       Date,
    country   LowCardinality(String),
    pageviews SimpleAggregateFunction(sum, UInt64),
    visitors  AggregateFunction(uniq, UInt64),
    revenue   SimpleAggregateFunction(sum, Decimal(12, 2))
)
ENGINE = AggregatingMergeTree
ORDER BY (site_id, day, country);

CREATE MATERIALIZED VIEW analytics.daily_mv TO analytics.daily AS
SELECT site_id, toDate(ts) AS day, country,
       countIf(event = 'pageview') AS pageviews,
       uniqState(visitor_id)       AS visitors,
       sum(revenue)                AS revenue
FROM analytics.events
GROUP BY site_id, day, country;

The dashboard query reads the small rollup and merges states:

SQL
SELECT day, sum(pageviews) AS pageviews, uniqMerge(visitors) AS visitors
FROM analytics.daily
WHERE site_id = 7 AND day >= today() - 90
GROUP BY day ORDER BY day;
flowchart LR
    C["Collector"] -->|"batched inserts"| E["events (MergeTree)"]
    E --> V["daily_mv"]
    V --> D["daily (AggregatingMergeTree)"]
    D --> G["Dashboard"]
    E --> F["Funnel / retention queries"]

Raw events stay available for deep dives; the rollup serves the dashboard.

Step 3: funnels

A funnel counts visitors who completed step 1, then step 2 within a time window. ClickHouse has a specialized aggregate, windowFunnel:

SQL
SELECT level, count() AS visitors
FROM (
    SELECT visitor_id,
           windowFunnel(86400)(ts, event = 'pageview', event = 'signup', event = 'purchase') AS level
    FROM analytics.events
    WHERE site_id = 7 AND ts >= today() - 30
    GROUP BY visitor_id
)
GROUP BY level ORDER BY level;

windowFunnel(window)(timestamp, cond1, cond2, ...) returns the deepest step reached within window seconds. Portable SQL can compute the same with conditional aggregation, which you can try below.

Step 4: retention

retention(cond1, cond2, ...) returns an array of 0/1 flags telling whether a visitor was active in day 0 and in each later period. Portable SQL uses a self join, as in the second editor.

Run the logic on sample data

SQL Funnel with conditional aggregation (stand-in for windowFunnel)

Press Run (or Ctrl + Enter) to see the result here.

Expected shape: 300 viewers, 100 signups (every third visitor), 33 purchases (every ninth). Compute the conversion rate from step to step as a follow-up exercise.

SQL Weekly retention with a self join

Press Run (or Ctrl + Enter) to see the result here.

Step 5: operating it

  • The collector batches rows (or uses async inserts) and retries with a deterministic token.
  • Use a Distributed table and sharding by cityHash64(visitor_id) only when one node is not enough; see sharding.
  • Give the dashboard a read-only user with a quota and a row policy per tenant.
  • Alert on part counts, replication lag and disk space (see operations).

Extensions to try

  1. Add a top_pages rollup with SummingMergeTree keyed by (site_id, day, url) and read the top 20.
  2. Add a bloom-filter skipping index on session_id for support lookups.
  3. Compute sessions with a 30-minute inactivity gap using neighbor or window functions.
  4. Store a user-agent in a dictionary-decoded device column.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is site_id the first column of the sorting key?

    Putting the most commonly filtered, low-to-medium cardinality column first lets the sparse index skip other tenants' data.
  2. 2. What type does the visitors column of the rollup need so unique counts merge correctly across days and parts?

    Unique counts are not additive, so a state is stored and merged.
  3. 3. What does windowFunnel(86400)(ts, a, b, c) return per group?

    It returns the maximum number of consecutive conditions matched within the time window.
  4. 4. How do you keep raw events for only 13 months?

    TTL ... DELETE removes old rows during merges, and monthly partitions make part drops cheap.

Summary

  • Raw events go into a MergeTree sorted by tenant, day and event; rollups serve dashboards.
  • A materialized view into an AggregatingMergeTree keeps real-time daily stats with uniqState/uniqMerge.
  • windowFunnel and retention answer funnel and retention questions in one pass.
  • TTL, quotas and monitoring turn the design into a production system.

Next: Project: IoT Metrics and Rollups