Intermediate5 weeks

Website clickstream pipeline with sessions

Turn a stream of page-view events into sessions, funnels and a daily report.

Tools: Python, SQL, Kafka (optional), DuckDB or PostgreSQL

Why interviewers like it

Event data is everywhere in product companies, and sessionising is a classic interview question. This project lets you say you handled event data, late arrivals and repeated events, which are things juniors rarely have.

The problem

A website records every click as an event: who, what and when. Product managers want to know how many visits happen, how long they last, and where people drop off. You will generate events, group them into sessions, and report daily numbers.

How the data moves

  1. Event generator
  2. Raw events
  3. Sessionise
  4. Sessions table
  5. Daily report

The data

Generate your own events with a small Python script (user id, page, time). This is better than a download because you can create messy cases on purpose: duplicates, out-of-order events and missing user ids.

Week by week

  1. Week 1Write the event generator. Include duplicates and late events on purpose.
  2. Week 2Store raw events as they arrive and never change them. Add a unique event id.
  3. Week 3Group events into sessions: a new session starts after 30 minutes with no activity.
  4. Week 4Build a funnel (home, product, cart, paid) and a daily report. Add checks for duplicates.
  5. Week 5Optional: send the events through Kafka. Write the README and a design diagram.

Starter code

Sessions from raw events. Run it first, then grow it into the real project.

PythonSessions from raw events
from datetime import datetime, timedelta

events = [
    ("u1", "2024-03-01 10:00"), ("u1", "2024-03-01 10:10"), ("u1", "2024-03-01 11:00"),
    ("u2", "2024-03-01 10:05"), ("u2", "2024-03-01 10:30"),
]
GAP = timedelta(minutes=30)

def sessionize(events):
    last_seen = {}
    counter = {}
    out = []
    for user, ts in sorted(events, key=lambda e: (e[0], e[1])):
        t = datetime.strptime(ts, "%Y-%m-%d %H:%M")
        if user not in last_seen or t - last_seen[user] > GAP:
            counter[user] = counter.get(user, 0) + 1
        last_seen[user] = t
        out.append((user, ts, f"{user}-s{counter[user]}"))
    return out

for row in sessionize(events):
    print(row)
('u1', '2024-03-01 10:00', 'u1-s1')
('u1', '2024-03-01 10:10', 'u1-s1')
('u1', '2024-03-01 11:00', 'u1-s2')
('u2', '2024-03-01 10:05', 'u2-s1')
('u2', '2024-03-01 10:30', 'u2-s1')

User u1 comes back after 50 minutes, so a new session starts. Then do the same in SQL with window functions.

What to hand in

  • The generator and pipeline code with a README
  • A sessions table and a daily report table
  • A funnel chart or table
  • A short note on how you handled duplicates and late events

Viva and interview questions

Practise saying each answer out loud. Short and honest beats long and vague.

What is a session and why 30 minutes?

A session is one visit: a run of events with no long gap. Thirty minutes is a common choice. It is a business rule, so I made it a setting, not a hard-coded number.

How do you handle duplicate events?

Each event has a unique id. I keep the raw table as it arrived and remove duplicates when I build the clean table, so I can always trace back.

What about events that arrive late?

I reprocess the affected day. Raw events are kept, so recomputing sessions for that day gives the right answer.

Batch or streaming, and why?

I used batch for the daily report because the business reads it once a day. Streaming would only matter if someone needed live numbers.

How would you test the sessionising logic?

With a tiny list of events where I know the answer, like the example above, including a gap of exactly 30 minutes to test the boundary.

How to stand out

  • Send events through Kafka and read them with a consumer
  • Add a live-looking counter that updates every few seconds
  • Write unit tests for the session rule and the boundary cases

Put it on your CV

OutputResume bullet
Designed an event pipeline that groups clickstream events into sessions (30-minute rule), de-duplicates repeated events and produces daily funnel reports, with unit tests for session boundaries.

README outline

  1. What problem it solves
  2. Event format
  3. How sessions are decided
  4. How to run and test it
  5. Handling duplicates and late data
  6. Next steps

Learn the pieces