Sessions, Gaps, Islands & Funnels

So far, one row meant one order. Now one row means one event. An event is one action by a customer at one time, such as a page view or a purchase.

Teams rarely ask about single events. They ask about groups of events. In this chapter you build three kinds of group:

• A session: events from one customer that are close in time. It is one visit.
• A streak: days in a row on which a customer was active.
• A funnel: steps in a fixed order, with a count of customers at each step.

None of these is a column in a table. You build each one from the event rows.

You need window functions from chapter 5. A window function looks at nearby rows without merging them.

This chapter uses LAG, ROW_NUMBER, SUM() OVER and PARTITION BY. If you forget one, each is explained again where it is used.

The events table

This chapter adds one table, events. Each row is one action: who did it, when, and what. Omar (customer 5) has no events, as he had no orders.

SELECT customer_id, event_time, event_name
FROM events
ORDER BY customer_id, event_time;

Follow Asha, customer 1. She has seven events on four different days. She is the best customer to check by hand.

A session is a group of events from one customer with no long pause inside it. You decide how long a pause must be. Here, a pause of more than 30 minutes starts a new session.

Look at Asha on 3 January. She acts at 09:00, 09:10 and 09:20, then comes back at 14:00. That pause is 4 hours 40 minutes, so 14:00 starts a new session.

The query builds three columns, one at a time:

1. prev_time: the time of the customer’s previous event.
2. new_session: a flag. It is 1 when a new session starts on this row, and 0 when it does not.
3. session_id: a running total of the flags (explained in step 3). Rows with the same number are in the same session.

Step 1: find the previous event

LAG(event_time) reads event_time from the row before. PARTITION BY customer_id keeps each customer apart. ORDER BY event_time sets which row is before:

SELECT customer_id,
       event_time,
       LAG(event_time) OVER (
         PARTITION BY customer_id
         ORDER BY event_time
       ) AS prev_time
FROM events
ORDER BY customer_id, event_time;

Asha’s second row shows 09:00, the time of her first row. The first row of each customer has no row before it, so prev_time is NULL.

Step 2: flag the new sessions

Subtract prev_time from event_time to get the gap. Then flag the row with 1 if the gap is more than 30 minutes. The first step becomes a CTE, a named result from chapter 4:

WITH step1 AS (
  SELECT customer_id,
         event_time,
         LAG(event_time) OVER (
           PARTITION BY customer_id
           ORDER BY event_time
         ) AS prev_time
  FROM events
)
SELECT customer_id,
       event_time,
       event_time - prev_time AS gap,
       CASE
         WHEN prev_time IS NULL THEN 1
         WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1
         ELSE 0
       END AS new_session
FROM step1
ORDER BY customer_id, event_time;

The gap shows hours:minutes:seconds. So 04:40:00 is 4 hours 40 minutes, and 20:00:00 is 20 hours.

Asha’s gaps of 10 minutes stay in the session, so their flag is 0. Her gaps of 4 hours 40 minutes, 20 hours, 1 day and 3 days each start a new one.

Ben’s second event is exactly 30 minutes after his first. The rule says “more than 30”, so it stays in his session.

A running total adds the flags from the first row down to the current row. Each new session adds 1, so it gets the next number.

This is the same query as before, with one more step at the end:

WITH step1 AS (
  SELECT customer_id,
         event_time,
         LAG(event_time) OVER (
           PARTITION BY customer_id
           ORDER BY event_time
         ) AS prev_time
  FROM events
),
step2 AS (
  SELECT customer_id,
         event_time,
         CASE
           WHEN prev_time IS NULL THEN 1
           WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1
           ELSE 0
         END AS new_session
  FROM step1
)
SELECT customer_id,
       event_time,
       new_session,
       SUM(new_session) OVER (
         PARTITION BY customer_id
         ORDER BY event_time
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS session_id
FROM step2
ORDER BY customer_id, event_time;

Asha’s flags are 1, 0, 0, 1, 1, 1, 1. The running sum gives 1, 1, 1, 2, 3, 4, 5. Her first three events share session 1.

The last line of OVER is the frame (chapter 5). ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW means: add every row from the first one down to this one.

Keep it. Without it, two events with the same time would get the same session number.

Now every event has a session number. Group by customer_id and session_id, and each group is one session.

You cannot use a window function in GROUP BY. So the session numbers come from a CTE:

WITH step1 AS (
  SELECT customer_id,
         event_time,
         LAG(event_time) OVER (
           PARTITION BY customer_id
           ORDER BY event_time
         ) AS prev_time
  FROM events
),
step2 AS (
  SELECT customer_id,
         event_time,
         CASE
           WHEN prev_time IS NULL THEN 1
           WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1
           ELSE 0
         END AS new_session
  FROM step1
),
step3 AS (
  SELECT customer_id,
         event_time,
         SUM(new_session) OVER (
           PARTITION BY customer_id
           ORDER BY event_time
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS session_id
  FROM step2
)
SELECT customer_id,
       session_id,
       MIN(event_time) AS started,
       MAX(event_time) AS ended,
       COUNT(*)        AS event_count
FROM step3
GROUP BY customer_id, session_id
ORDER BY customer_id, session_id;

The table has 8 sessions. Asha has 5, and the other three customers have 1 each. Asha’s first session lasts 20 minutes and holds 3 events.

The rule changes the count

The 30 minutes is a choice, not a law. Change it to 15 minutes and Ben’s cart, 30 minutes after his view, starts a second session. The total becomes 9.

Different products need different rules:

A streak is days in a row. In SQL, a run of rows with no break is an island. A gap is the break between two islands.

Here, an island is a run of days on which a customer had at least one event. Asha has two islands: 3 to 5 January, and 8 January alone.

First you need one row for each customer and day. Asha has four events on 3 January, so use DISTINCT.

Then number the days, and subtract the number from the day. ROW_NUMBER gives each row a number 1, 2, 3 in order:

WITH days AS (
  SELECT DISTINCT customer_id,
         CAST(event_time AS date) AS day
  FROM events
),
numbered AS (
  SELECT customer_id,
         day,
         CAST(ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY day
         ) AS int) AS rn
  FROM days
)
SELECT customer_id,
       day,
       rn,
       day - rn AS island_key
FROM numbered
ORDER BY customer_id, day;

Look at Asha. From 3 to 5 January, the day and rn both go up by 1. So island_key stays at 2 January.

Then she skips to 8 January. The day jumps by 3, but rn goes up by only 1. The key changes to 4 January, and a new island starts.

The key is only a label. Ben and Carla also have 2 January as a key, but you compare keys only inside one customer. Now group by customer and key:

WITH days AS (
  SELECT DISTINCT customer_id,
         CAST(event_time AS date) AS day
  FROM events
),
numbered AS (
  SELECT customer_id,
         day,
         CAST(ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY day
         ) AS int) AS rn
  FROM days
),
keyed AS (
  SELECT customer_id, day, day - rn AS island_key
  FROM numbered
)
SELECT customer_id,
       MIN(day)  AS first_day,
       MAX(day)  AS last_day,
       COUNT(*)  AS days_in_row
FROM keyed
GROUP BY customer_id, island_key
ORDER BY customer_id, first_day;

Asha has two islands: three days in a row, then one day. Her longest streak is 3. To get it, take MAX(days_in_row) for each customer.

A funnel is a list of steps in a fixed order. You count the customers who reach each step. The customers you lose between two steps are the drop-off.

The shop has three steps: view, then cart, then purchase. Count the different customers at each step. COUNT(DISTINCT …) with CASE is from chapter 3:

SELECT COUNT(DISTINCT CASE WHEN event_name = 'view'
                       THEN customer_id END) AS viewed,
       COUNT(DISTINCT CASE WHEN event_name = 'cart'
                       THEN customer_id END) AS carted,
       COUNT(DISTINCT CASE WHEN event_name = 'purchase'
                       THEN customer_id END) AS purchased
FROM events;

Four customers viewed: Asha, Ben, Carla and Dev. Two used the cart: Asha and Ben. One bought: Asha.

Omar has no events, so he is in no count.

Now turn the counts into a table of conversion. Conversion is the share of customers who reach a step. Here it is measured against the viewers.

You do this sum by hand: 2 of 4 is 50%, and 1 of 4 is 25%.

The last column is the step before minus this step. 4 − 2 = 2 lost, and 2 − 1 = 1 lost.

The last query counted each step on its own. It did not check the order. A customer who viewed after buying would still count at the cart and purchase steps.

To check the order, first find the first time of each step for each customer. MIN picks the earliest time. A step the customer never did gives NULL:

SELECT customer_id,
       MIN(CASE WHEN event_name = 'view'
                THEN event_time END) AS viewed_at,
       MIN(CASE WHEN event_name = 'cart'
                THEN event_time END) AS carted_at,
       MIN(CASE WHEN event_name = 'purchase'
                THEN event_time END) AS bought_at
FROM events
GROUP BY customer_id
ORDER BY customer_id;

Now count a customer at a step only if the step comes after the one before. A comparison with NULL is never true, so a missing step is left out:

WITH per_customer AS (
  SELECT customer_id,
         MIN(CASE WHEN event_name = 'view'
                THEN event_time END) AS viewed_at,
         MIN(CASE WHEN event_name = 'cart'
                THEN event_time END) AS carted_at,
         MIN(CASE WHEN event_name = 'purchase'
                THEN event_time END) AS bought_at
  FROM events
  GROUP BY customer_id
)
SELECT COUNT(viewed_at) AS viewed,
       COUNT(CASE WHEN carted_at > viewed_at THEN 1 END) AS carted,
       COUNT(CASE WHEN bought_at > carted_at
                   AND carted_at > viewed_at THEN 1 END) AS purchased
FROM per_customer;

The answer is still 4, 2, 1. In this table, everyone did the steps in order. With a customer who carted before viewing, this query would leave that customer out.

Three choices to write down

Two teams can count “the same” funnel and get different numbers. Each team made different choices. State these three in the definition:

1. Order: must the steps happen in order? This query says yes.
2. Time limit: must the cart come within, say, 24 hours of the view? Add a test on the times, for example: carted_at < viewed_at + INTERVAL '24 hours'. Here both carts come within an hour, so the counts stay the same.
3. Repeats: does a customer who views twice count once? Here yes, because we use the first time.

A cohort is a group of customers who started on the same day. Retention is the share of a cohort that comes back later. Here, “started” means the first day with an event.

Find day-1 retention: the share of each cohort active on the day after the first day. Use a LEFT JOIN so a cohort with nobody back still shows a row:

WITH first_day AS (
  SELECT customer_id,
         MIN(CAST(event_time AS date)) AS cohort_day
  FROM events
  GROUP BY customer_id
),
active AS (
  SELECT DISTINCT customer_id,
         CAST(event_time AS date) AS day
  FROM events
)
SELECT f.cohort_day,
       COUNT(DISTINCT f.customer_id) AS cohort_size,
       COUNT(DISTINCT a.customer_id) AS back_next_day,
       ROUND(100.0 * COUNT(DISTINCT a.customer_id)
             / COUNT(DISTINCT f.customer_id), 1) AS back_pct
FROM first_day AS f
LEFT JOIN active AS a
       ON a.customer_id = f.customer_id
      AND a.day = f.cohort_day + 1
GROUP BY f.cohort_day
ORDER BY f.cohort_day;

Asha, Ben and Carla started on 3 January. Only Asha was active on 4 January, so 1 of 3 came back. Dev started on 4 January and was not active on 5 January.

Dev’s cohort still shows a row with 0 back, because of the LEFT JOIN. An inner join would drop it.

Chapter 5 has a section, Days since first order, that uses the same cohort idea.

Each exercise opens in the SQL workspace with its own tables. The exercises use deliveries and plays (a music app), not the shop. The ideas are the same as in this chapter.

Do the two deliveries exercises in order. The second one builds on the first.