Real-Time Clickstream Analytics with SQL

Clickstream analytics tracks every user interaction — page views, clicks, scrolls, form submissions — to understand user behavior in real time. A streaming database processes clickstream events as they arrive, maintaining always-current metrics like active users, conversion funnels, and session analytics without batch ETL.

Clickstream Pipeline with RisingWave

-- Ingest from Kafka
CREATE SOURCE clicks (user_id INT, page VARCHAR, action VARCHAR,
  referrer VARCHAR, duration_ms INT, ts TIMESTAMP WITH TIME ZONE)
WITH (connector='kafka', topic='clickstream', properties.bootstrap.server='kafka:9092')
FORMAT PLAIN ENCODE JSON;

-- Active users right now
CREATE MATERIALIZED VIEW active_users AS
SELECT COUNT(DISTINCT user_id) as active_users_5min
FROM clicks WHERE ts > NOW() - INTERVAL '5 minutes';

-- Page popularity
CREATE MATERIALIZED VIEW page_views AS
SELECT page, COUNT(*) as views, AVG(duration_ms) as avg_duration,
  COUNT(DISTINCT user_id) as unique_visitors
FROM clicks WHERE ts > NOW() - INTERVAL '1 hour'
GROUP BY page ORDER BY views DESC;

-- Conversion funnel
CREATE MATERIALIZED VIEW funnel AS
SELECT
  COUNT(DISTINCT user_id) FILTER (WHERE action='page_view') as viewed,
  COUNT(DISTINCT user_id) FILTER (WHERE action='add_to_cart') as added,
  COUNT(DISTINCT user_id) FILTER (WHERE action='checkout') as checked_out,
  COUNT(DISTINCT user_id) FILTER (WHERE action='purchase') as purchased
FROM clicks WHERE ts > NOW() - INTERVAL '24 hours';

Connect Grafana or Metabase directly via PostgreSQL for real-time dashboards.

Frequently Asked Questions

Why use stream processing for clickstream?

Clickstream generates high-volume events (thousands per second) that must be aggregated in real time for dashboards and personalization. Batch processing introduces hours of delay, making the data useless for real-time personalization or A/B test monitoring.

How does RisingWave compare to Google Analytics for clickstream?

Google Analytics is an end-to-end analytics product. RisingWave is infrastructure for building custom real-time analytics. Use GA for standard web metrics; use RisingWave when you need custom real-time aggregations, cross-system joins, or data ownership.