Skip to content
Tutorials Center
My learning

Topics

Lesson 111 of 125 ยท Projects

Sessionization, Funnels and Retention

Analyze event data with SQL. Split clicks into sessions with LAG and running sums, build conversion funnels, and compute day-1 and day-7 retention cohorts.

Product and marketing teams live on event data: one row each time someone views a page, adds an item to a cart or buys something. A raw event table is just a long list, so the real work is turning it into answers. How many visits did we have, and how long did they last? Where do people drop out on the way to buying? Do they come back next week?

In this project you'll answer those three classic questions with plain SQL. Sessionization groups events into visits using a time gap. A funnel counts how many users reach each step. Retention measures how many of a group of new users return on later days. The tools are the ones you already know: LAG, running totals, conditional aggregation and joins. The islands technique from Gaps and Islands is the engine behind the first stage.

flowchart TD
  R["Raw events<br/>user, event, timestamp"] --> S["Stage 1: sessions<br/>LAG + gap rule + running SUM"]
  R --> F["Stage 2: funnel<br/>users per step, conversion"]
  R --> T["Stage 3: retention<br/>signup cohort + later activity"]
  S --> K["Session length, events per session"]

One event table feeds three analyses. Each stage is independent, so you can run them in any order.

The data

Every example below creates the same small table. Six users generated 24 events on a handful of April days. The event types are view, add_to_cart and purchase; timestamps are stored as text in the sortable YYYY-MM-DD HH:MM:SS format.

Two details are deliberate. User 1 returns later the same day and again on later days, which makes sessions and retention interesting. User 6 buys without any recorded view (maybe from an email link), which will matter in the funnel.

Stage 1: sessionization

A session is a burst of activity separated from the next burst by a period of inactivity. The usual rule, used by many analytics tools, is: a new session starts when the same user has been quiet for more than 30 minutes. The recipe has three steps:

  1. Use LAG(ts) to fetch the previous event time for the same user.
  2. Flag an event as a session start if there is no previous event or the gap exceeds 1,800 seconds.
  3. Take a running SUM of the flags per user. That counter is the session number.
SQL Assign a session number to every event

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

Check user 2: the 10:25 view is five minutes after the 10:20 cart, so it stays in session 1, but the next view at 11:30 is 65 minutes later, so it opens session 2. User 5's second view arrives exactly 40 minutes after the first and starts a new session, even though the cart event follows it five minutes later and stays with it. The gap rule is a convention, not a law; teams pick 30 minutes, 20, or whatever matches their product. Change 1800 and rerun to see how the sessions move.

Two things make this query reliable. PARTITION BY user_id restarts the numbering for each user, so session 1 exists for everyone. And the timestamps are compared as numbers (strftime('%s', ...) gives Unix seconds), which avoids string arithmetic. In PostgreSQL you would subtract timestamps and compare the result with INTERVAL '30 minutes'.

Session statistics

Wrap the previous query in another CTE, give each session a stable id (user plus session number) and aggregate. Now each row of the result is one visit:

SQL One row per session

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

The 24 events collapse into 14 sessions that average 1.71 events and 5.6 minutes; 8 of them are bounces and 3 contain a purchase. A session with a single event is a bounce: the visitor looked and left. Its duration is zero by construction, because we only know when events happened, not how long the last page stayed open. That limitation is why session length is always a slight underestimate. The same per_session result can be grouped by day, by traffic source or by user to answer "when do people visit?" and "who visits most?".

Stage 2: the conversion funnel

A funnel is an ordered list of steps, and the question is how many users make it to each one. Our funnel is view, then add to cart, then purchase. The simplest version counts distinct users per event type:

SQL Funnel step counts and conversion

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

First the query reduces the table to one row per user with the first time they did each thing, using MIN(CASE ...). That is conditional aggregation, and MIN of a CASE that yields NULL for other events ignores the nulls. Then it counts two ways.

The unordered counts say 5 viewed, 4 carted and 3 bought, because user 6 bought without viewing. The ordered counts say 4 viewed-then-carted and 2 bought in the right order. Which one is right depends on the question. "How many orders came from our site?" wants the first style; "how well does the shopping flow convert?" wants the strict, ordered style. Writing both side by side shows how large the difference is, and large gaps usually point at tracking bugs or alternative paths such as email links.

Conversion rates are plain division. From the strict numbers, 4 of 5 viewers added to the cart (80%) and 2 of those 4 bought (50%). Remember to divide by the previous step, not by the first step, when you want step-to-step drop-off, and to divide with a decimal (100.0 * a / b) to avoid integer division.

Stage 3: retention

Retention asks whether users come back. Group users into cohorts by their signup date, then count how many of each cohort were active exactly N days later. This version uses a small users table plus the same events:

SQL Day-1 and day-7 retention by signup date

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

The LEFT JOIN keeps users who never came back, so they count in the cohort size. COUNT(DISTINCT CASE ...) counts each returning user once even if they generated many events that day. This is called classic (or day-N) retention: active exactly on day N. A softer variant, rolling retention, counts anyone active on day N or later, which is more forgiving for products people use irregularly.

Retention tables get wide quickly (day 1, 2, 3, ... 30), so real reports often compute the day offset per event (julianday(date(e.ts)) - julianday(u.signed_up)), group by cohort and offset, and pivot the result. With a tiny cohort like ours the percentages jump around; real cohorts have hundreds or thousands of users before the numbers settle.

Pitfalls to remember

  • Time zones. Sessions and "days" depend on the time zone. Store UTC and convert before taking date().
  • Duplicate events. Tracking code often fires twice. Deduplicate first, as in Data Cleaning and Deduplication.
  • Bots and tests. A few automated accounts can distort funnels. Filter them out early.
  • Definition drift. Write the session gap, the funnel steps and the retention rule into the query comments so everyone computes the same numbers.
  • Performance. On big tables, index (user_id, ts) so the window sort is cheap, as discussed in indexes and query plans.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How do you detect the start of a new session for a user?

    A new session starts when there is no previous event or the gap from the previous event exceeds the timeout, for example 30 minutes. LAG(ts) provides the previous event time.
  2. 2. Why take a running SUM of the new-session flags?

    The flag is 1 at each session start and 0 elsewhere, so the running total increases only at starts. Every event between two starts has the same total, which is the session number.
  3. 3. In our data, user 6 purchased without a recorded view. What happens to the strict, ordered funnel?

    The ordered funnel requires purchase after cart after view. User 6 has no earlier steps, so the comparison is false (or NULL) and the user is not counted.
  4. 4. Why does the retention query use a LEFT JOIN from users to events?

    Users with no matching events still belong to their signup cohort. An inner join would drop them and inflate the retention percentages.

Summary

  • Sessionization = LAG for the previous event time, a flag when the gap passes the timeout, and a running SUM of the flags per user.
  • Aggregating sessions gives visits, events per session, duration and bounce counts.
  • A funnel reduces events to one row per user with MIN(CASE ...), then counts users per step; compare unordered counts with strict ordered counts.
  • Divide each step by the previous step for drop-off, using decimal division.
  • Retention groups users into signup cohorts and counts distinct users active on day N; use a LEFT JOIN so non-returning users stay in the cohort.
  • Write down your definitions (gap, steps, day N) and watch time zones, duplicates and bots.

Next: the SQL Cheat Sheet, a one-page reference of everything in this tutorial.