Sessionizing Events

The two partitions compare as follows.

• Cost of the absolute minute: a daily job must read across the boundary. A job that sessionizes day 3 alone cannot see the 23:50 view. It must also read at least the last 30 minutes of day 2. A session that started on day 2 may then gain views, so day 2's figures change after they were written.
• When splitting at midnight is better: a report that defines sessions per calendar day, and whose published days must never change. The split is then part of the definition, and the report should state it.

Session Length and Views per Session

Session length is the time from the first view of a session to the last, and views per session is the number of page views in it. Both come from a GROUP BY on the visitor and session number, applied after the windows:

WITH timed AS (
  SELECT view_id, visitor_id, day_no,
         day_no * 1440 + view_min AS abs_min
  FROM page_views
),
flagged AS (
  SELECT view_id, visitor_id, day_no, abs_min,
         CASE WHEN LAG(abs_min) OVER w IS NULL
                OR abs_min - LAG(abs_min) OVER w >= 30
              THEN 1 ELSE 0 END AS is_new
  FROM timed
  WINDOW w AS (PARTITION BY visitor_id ORDER BY abs_min, view_id)
),
numbered AS (
  SELECT visitor_id, day_no, abs_min,
         SUM(is_new) OVER (
           PARTITION BY visitor_id
           ORDER BY abs_min, view_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS session_no
  FROM flagged
)
SELECT visitor_id, session_no,
       MIN(day_no) AS start_day,
       COUNT(*) AS views,
       MAX(abs_min) - MIN(abs_min) AS length_min
FROM numbered
GROUP BY visitor_id, session_no
ORDER BY visitor_id, session_no;
Copy

Output:

+------------+------------+-----------+-------+------------+
| visitor_id | session_no | start_day | views | length_min |
+------------+------------+-----------+-------+------------+
| V-11       |          1 |         2 |     3 |         17 |
| V-11       |          2 |         2 |     2 |         21 |
| V-22       |          1 |         2 |     3 |         40 |
| V-22       |          2 |         3 |     1 |          0 |
+------------+------------+-----------+-------+------------+
Copy

V-22's first session runs from minute 4290 to minute 4330, which is 40 minutes across midnight. The length uses abs_min for the same reason as the gap: MAX(view_min) - MIN(view_min) would give 1430 - 10 = 1420 minutes for that session.

The length stops at the last view, so the time spent on the last page is not counted, and a one-view session has length 0. The average over all four sessions is (17 + 21 + 40 + 0) / 4 = 19.5 minutes, and without the one-view session it is 78 / 3 = 26. Neither is wrong, but the metric's definition must say which one it reports.

Choosing the Gap

The timeout decides every number above. One query can count sessions for several timeouts at once, by joining the gaps to a list of candidate values:

WITH timed AS (
  SELECT view_id, visitor_id,
         day_no * 1440 + view_min AS abs_min
  FROM page_views
),
gaps AS (
  SELECT abs_min - LAG(abs_min) OVER (
           PARTITION BY visitor_id
           ORDER BY abs_min, view_id
         ) AS gap_min
  FROM timed
),
limits AS (
  SELECT 10 AS gap_limit UNION ALL SELECT 20 UNION ALL SELECT 30
  UNION ALL SELECT 60 UNION ALL SELECT 300
)
SELECT l.gap_limit,
       SUM(CASE WHEN g.gap_min IS NULL OR g.gap_min >= l.gap_limit
                THEN 1 ELSE 0 END) AS sessions
FROM limits l
CROSS JOIN gaps g
GROUP BY l.gap_limit
ORDER BY l.gap_limit;
Copy

Output:

+-----------+----------+
| gap_limit | sessions |
+-----------+----------+
|        10 |        8 |
|        20 |        7 |
|        30 |        4 |
|        60 |        4 |
|       300 |        2 |
+-----------+----------+
Copy

The count falls from 8 to 4 between 10 and 30 minutes, and then stays at 4 up to 60. No gap in the table lies strictly between 21 and 65 minutes, so every timeout from 22 to 65 splits the views at the same two gaps, 261 and 65. On a large table, the same query over more values shows the range where the count changes least, and a timeout inside that range is the least sensitive choice.

• A short timeout gives more, shorter sessions. At 10 minutes, the 14-minute pause on the pancake video ends V-11's first session, although all three views are about one breakfast.
• A long timeout gives fewer, longer sessions. At 300 minutes, V-11's breakfast and lunch visits become one session of 3661 - 3362 = 299 minutes.

Google Analytics ends a session after 30 minutes of inactivity by default, so a 30-minute timeout makes a warehouse count easier to compare with its reports. An administrator can change that timeout there, and a session has no maximum length.

Practice: Count Sessions per Visitor

The site's analytics team uses a 20-minute rule: a view that comes 20 minutes or more after the same visitor's previous view starts a new session. A session may cross midnight. The page_views table has the columns shown above. Order each visitor's views by day_no, then view_min, then view_id, and not by view_id alone, because some views were recorded late.

For each visitor, return these columns in this order:

• visitor_id.
• sessions: the number of sessions.
• views: the number of page views, counting a repeated page each time.
• longest_min: the length in minutes of the visitor's longest session, from its first view to its last, which is 0 for a one-view session.

Sort the result by visitor_id.