Clickstream Modeling

A clickstream is the event stream of what people do in a product: pages viewed, buttons tapped, searches run, videos paused. Each row is one person doing one thing at one instant, and a mid-sized app produces hundreds of millions of these a day. Nothing about a tap changes once it has happened, so the raw table is append only, and the questions share one shape: how many people did X, then Y, within one visit.

Schema

raw_events: event_id varchar PK, anonymous_id varchar, user_id varchar, event_name varchar, event_time timestamp, event_date date, page_url varchar, device_type varchar, referrer varchar, properties_json varchar, ingested_at timestamp
sessions: anonymous_id varchar, session_number integer, started_at timestamp, ended_at timestamp, event_count integer, landing_page varchar, converted boolean

Two identity columns matter. anonymous_id is a random id the app assigns before anyone logs in, and user_id is filled once they do. A shopper browses 20 products anonymously, logs in to buy, and the login event, which carries both ids, is the only thing that connects the browsing to the purchase. Drop either column and every funnel breaks at the login step.

A tracking plan: few names, many properties

The event name is the vocabulary everyone shares, so it stays small and follows one pattern: object, then past-tense action. What varies goes in properties. product_viewed with product_id 220 is one event name that covers the whole catalog, while product_220_viewed is a new event name per product and a dashboard nobody can maintain. The tracking plan is the table that says which names exist, when each fires, and which properties are required.

event_name	Fires when	Required properties
page_viewed	a screen or page renders	page_url, referrer
product_viewed	a product detail page renders	product_id, price, category
search_performed	the search button is tapped	query, results_count
cart_item_added	an item lands in the cart	product_id, quantity, price
order_placed	checkout completes	order_id, total, item_count

Sessions: the visit the raw table never records

The app records taps. A visit is derived: a run of events from one anonymous_id with no gap of 30 minutes or more between neighbours. lag() fetches the previous event's time for the same person, a gap of 30 minutes or a missing previous event flags a new session, and a running sum of those flags numbers each session. The cutoff is a convention, and a video app measuring binge watching might pick 4 hours.

WITH gaps AS (
  SELECT
    anonymous_id,
    event_time,
    event_name,
    lag(event_time) OVER (
      PARTITION BY anonymous_id
      ORDER BY event_time
    ) AS prev_time
  FROM raw_events
  WHERE event_date = DATE '2026-10-20'
),
flags AS (
  SELECT
    anonymous_id,
    event_time,
    event_name,
    CASE
      WHEN prev_time IS NULL
        OR date_diff('minute', prev_time, event_time) >= 30 THEN 1
      ELSE 0
    END AS new_session
  FROM gaps
)
SELECT
  anonymous_id,
  event_time,
  event_name,
  sum(new_session) OVER (
    PARTITION BY anonymous_id
    ORDER BY event_time
  ) AS session_number
FROM flags

anonymous_id	event_time	event_name	new_session	session_number
anon_9f2	08:14:02	page_viewed	1	1
anon_9f2	08:16:40	product_viewed	0	1
anon_9f2	08:17:05	cart_item_added	0	1
anon_9f2	13:02:11	page_viewed	1	2
anon_9f2	13:03:50	order_placed	0	2

Funnels

A funnel asks how many sessions reached each step. Counting events gives the wrong answer, because one session can view 12 products and add 3 to the cart. The query reads sessionized_events, the October 20 output of the sessionization query saved as a table, where one anonymous_id with one session_number is one visit. The inner query collapses each visit to 3 flags, and the outer one counts the sessions with each flag set.

SELECT
  count(*) AS sessions,
  count_if(viewed > 0) AS viewed_product,
  count_if(added > 0) AS added_to_cart,
  count_if(purchased > 0) AS purchased
FROM (
  SELECT
    anonymous_id,
    session_number,
    count_if(event_name = 'product_viewed') AS viewed,
    count_if(event_name = 'cart_item_added') AS added,
    count_if(event_name = 'order_placed') AS purchased
  FROM sessionized_events
  GROUP BY
    anonymous_id,
    session_number
)

sessions	viewed_product	added_to_cart	purchased
48,210	31,004	6,112	1,933

A product manager wants a dashboard of how many shoppers viewed each product. An engineer proposes one event name per product, such as product_220_viewed. What goes wrong?

Nothing goes wrong, since each product gets its own count, which is what the dashboard asks for and what the plan documents
Each product becomes a new event name, the tracking plan grows by thousands of rows, and a count by product_id on one product_viewed event would have done the same job
The event name exceeds the length the envelope allows, so the loader rejects every one of these events at ingestion
Properties cannot carry a product id, so a name per product is the only way to record which product was viewed

About this lesson

This section is part of the Event Streams lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.