Session Analysis and User Journeys

Sessions group user activity into meaningful visits. A "session" is a contiguous period of user activity — it starts when the user arrives and ends after a period of inactivity (typically 30 minutes). Session analysis reveals how users navigate your product, where they spend time, and which paths lead to conversion.

Understanding sessions is more nuanced than simple event counts: a user who visited 10 pages in one session and a user who visited 10 pages across 10 separate sessions have very different engagement profiles.

What Sessions Tell You

Session-level metrics are the primary unit of analysis for UX teams and product managers:

Bounce rate: Sessions with only one page — the user left immediately
Session duration: How long do users stay per visit?
Pages per session: How deeply do users explore?
Session conversion rate: What % of sessions end in a purchase?
Entry and exit patterns: Where do users start and end their journeys?

These metrics drive UX decisions, content prioritisation, and navigation design.

Dataset

-- ec_events: customer_id, event_type, created_at, session_id
-- A session typically ends after 30 minutes of inactivity
-- session_id is either pre-assigned by the analytics platform, or computed

Step 1 — Define Sessions from Raw Events

If your data doesn't include a pre-computed session_id, you can create sessions using the 30-minute inactivity rule. The technique uses LAG() to compute the gap between consecutive ec_events per user. Any gap over 30 minutes starts a new session.

SUM(new_session_flag) OVER (PARTITION BY customer_id ORDER BY created_at) is a running cumulative sum — each time a new session starts (flag = 1), the session number increments. Within a session (flag = 0), the number stays constant. This is the canonical session stitching pattern.

WITH event_gaps AS (
  SELECT
    customer_id,
    event_type,
    created_at,
    LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_timestamp,
    EXTRACT(EPOCH FROM (
      created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)
    )) / 60 AS gap_minutes
  FROM ec_events
),
session_flags AS (
  SELECT *,
    -- New session if first event ever (gap IS NULL) or gap > 30 minutes
    CASE WHEN gap_minutes IS NULL OR gap_minutes > 30 THEN 1 ELSE 0 END AS new_session_flag
  FROM event_gaps
),
sessions AS (
  SELECT *,
    SUM(new_session_flag) OVER (PARTITION BY customer_id ORDER BY created_at) AS session_num
  FROM session_flags
)
SELECT customer_id, session_num, MIN(created_at) AS session_start,
  MAX(created_at) AS session_end,
  COUNT(*) AS events_in_session
FROM sessions
GROUP BY customer_id, session_num
ORDER BY customer_id, session_num;

What This Returns

One row per session: the user, a sequential session number (1 = first session ever, 2 = second, etc.), start and end times, and event count within the session. Users with many sessions are engaged repeat visitors; users with only one session haven't returned.

Purchase this course to unlock the full lesson.

← Previous Next →