Session Analysis Without a Session Variable

A session ID is useful in an event stream, removing ambiguity when grouping visits, calculating per-session metrics, or asking what share of sessions included a feature interaction. When available, analysis is simpler.

Session IDs are missing from many tracking setups. Some were assembled quickly, while server-side pipelines may not have a clear session concept in their data model. Many product teams therefore have event data with user IDs and timestamps, but no session identifier.

Without a session ID, session analysis remains possible. You must infer sessions from the available data. It takes extra work, but the method is practical and applies to most event schemas.

The Standard Method: Time-Gap Sessionization

Time-gap sessionization is the usual way to turn timestamped events into sessions. For each user, keep consecutive events together while each gap stays below a set threshold. Once a gap passes it, begin another session.

Web analytics has traditionally used a 30-minute threshold, inherited from older systems and suitable for browser products. Mobile apps often benefit from 15 minutes because users may step away and return. For back-office or internal tools left open for hours, 60 minutes may fit better, or calendar-day boundaries can force a cut.

The best threshold depends on the product and merits tuning. Too short fragments sessions; too long combines separate visits. Both mistakes distort per-session metrics.

Writing It in SQL

Window functions handle the implementation. The same pattern works in PostgreSQL, BigQuery, Snowflake, and comparable SQL dialects.

Step 1: find each user's gap from the preceding event.

SELECT
  user_id,
  event_name,
  occurred_at,
  LAG(occurred_at) OVER (
    PARTITION BY user_id
    ORDER BY occurred_at
  ) AS prev_event_at
FROM events

Step 2: mark a new session when the gap passes the threshold or no earlier event exists.

SELECT
  user_id,
  event_name,
  occurred_at,
  CASE
    WHEN prev_event_at IS NULL THEN 1
    WHEN EXTRACT(EPOCH FROM (occurred_at - prev_event_at)) / 60 > 30 THEN 1
    ELSE 0
  END AS is_session_start
FROM event_gaps

Step 3: use a running sum of the start flags to number each user's sessions.

SELECT
  user_id,
  event_name,
  occurred_at,
  SUM(is_session_start) OVER (
    PARTITION BY user_id
    ORDER BY occurred_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_number
FROM session_starts

This creates a derived session_number for grouping events. Pairing user_id with session_number forms a session key for duration, events per session, conversion rates, and other analysis.

Session Duration and the Last-Event Gap

Duration is not as straightforward as it seems. Subtracting the first timestamp from the last works when many events occur, but gives single-event sessions zero and misses meaningful reading time after the final action.

For most product analytics, this limitation is acceptable if recognized. Zero-duration single-event sessions lower the average and distort percentiles. If duration is a core metric, exclude them from that calculation and report them separately as bounce-equivalent sessions.

The final event records when activity stopped, not necessarily when the user left. Untracked reading or viewing time therefore makes duration an undercount. Add finer-grained engagement events, such as scroll depth, time-on-page pings, or video progress, rather than altering the session rule. A revised definition cannot recover uncaptured information.

Common Sessionization Complications

Time-gap sessionization has several common failure cases.

Background and automated activity can lengthen sessions artificially. A browser closed three hours earlier may appear active if a background refresh fires every 20 minutes. Remove machine-generated and sync events first, or sessionize only selected user-interaction event types.

Multi-tab and multi-device use produces interwoven events rather than one sequential session. Someone using two tabs or moving between desktop and phone may look continuously active unless a gap exceeds the threshold. This limitation matters for productivity tools and back-office software. The genuine fixes are client-side session tracking, which returns to the session ID issue, or device-level sessions linked afterward to the user.

Long pauses can occur within one genuine use context. A user may open a form, spend 45 minutes finding information elsewhere, then return to finish it. A 30-minute rule creates two sessions. The appropriate split depends on your product's definition, so the threshold should reflect the behavior you want to measure.

Using It Without a Full Pipeline

Decide where this logic belongs. Mature data setups should place sessionization in a lasting dbt transformation or materialized view. Early-stage teams often query raw event tables without a transformation layer.

There, the three SQL steps can become one CTE query that runs on demand and needs no maintenance. The downside is repeated computation whenever an analyst or PM studies sessions. That is fine below a few million rows, but as the table grows, the query slows and a materialized sessions table becomes worth considering.

At Mitzuo, we treat sessionization as intent that can be translated. For "how many sessions included a feature interaction last week," we identify the request and add session grouping to the generated query instead of requiring a sessions table beforehand. Teams without a dbt layer can thus answer session-level questions using their current data, without waiting for a sprint of engineering work.

What Sessions Let You Measure

Once sessions have a usable definition, many questions no longer need analyst help. Events per session can indicate engagement quality. Feature coverage shows whether users reaching feature A also reach feature B in that session. Session funnels are cleaner than event funnels because they accommodate funnel completion across multiple visits.

A raw session ID column is not required. You need the 30 or so lines of SQL above and a gap threshold suited to the product. A missing session variable is a data quality gap, not a dead end. Timestamps still contain the signal; the session container must be rebuilt around them.