A web or app log is a long list of events: user, timestamp, what happened. Nothing in it says “session”. Yet product teams talk about sessions per user, pages per session and bounce rate all the time. A session is something you construct, and the usual rule is simple: events from the same user belong to the same session until there is a gap longer than some timeout, commonly 30 minutes. Let us build it once by hand.

One user, six events

A user opens a grocery app today at these times: 10:00, 10:12, 10:35, 11:20, 11:25 and 14:02. Start by sorting by time, which they already are. Then compute the gap between each event and the one before it:

  • 10:12 minus 10:00: 12 minutes
  • 10:35 minus 10:12: 23 minutes
  • 11:20 minus 10:35: 45 minutes
  • 11:25 minus 11:20: 5 minutes
  • 14:02 minus 11:25: 157 minutes

Mark every event whose gap exceeds 30 minutes, or which has no previous event, as the start of a new session. The first event (10:00) starts one. The 11:20 event follows a 45-minute gap, so it starts another. The 14:02 event follows 157 minutes and starts a third. The 10:12, 10:35 and 11:25 events continue whatever session was running.

Now assign session numbers by counting how many start flags you have seen up to and including each event: 1, 1, 1, 2, 2, 3. Three sessions. The first lasts 35 minutes (10:00 to 10:35), the second 5 minutes, and the third has one event, so its duration is zero, which already tells you something unsettling about “average session duration”. More on that below.

The same steps in SQL, in words

The method translates directly into three layers, each usually a CTE or a subquery.

  1. Use LAG over a window partitioned by user and ordered by timestamp, to bring the previous event’s time onto each row.
  2. Compute a new-session flag with a CASE expression: 1 if the previous time is null or the difference exceeds 30 minutes, otherwise 0. The date-difference function differs by database, so use your warehouse’s own.
  3. Take a running SUM of that flag over the same window, ordered by timestamp. That running total is the session number within the user.

Combine user id and session number into a session key, and then ordinary GROUP BY gives you events per session, start, end and duration. The running-sum-of-flags trick is the heart of it. It is the same idea that powers gaps-and-islands problems, where a counter that increments at each break labels each block.

Where the neat picture gets messy

Ties and duplicates. Two events with the same timestamp have an ambiguous order, so add a tiebreaker such as an event id. Mobile apps also resend events on retry, so deduplicate first, or you will inflate event counts.

Timezones and clocks. Device clocks are wrong more often than you would like. If you can, use server receipt time, or at least compare both. Store timestamps in UTC and convert to IST only when reporting, otherwise a late-night session straddling midnight gets split by the calendar day.

Single-event sessions. Our 14:02 session has zero duration because we only know when it started. Duration is measured from first to last event, so one-event sessions look like instant visits even if the user read the page for five minutes. Averages of duration are therefore biased low, and the median is more honest, though still imperfect.

The timeout is a choice, not a law. Google Analytics 4 uses 30 minutes by default for web sessions, which is where the convention comes from, but a food-delivery app and a long-form video platform have very different natural gaps. Rerun the 11:20 event above with a 60-minute timeout: the 45-minute gap no longer breaks the session, and you get two sessions rather than three. A metric that moves by a third when you change one parameter deserves a footnote.

Some definitions also end a session at midnight, or when the traffic source changes (a user clicks a different campaign link). These add rules to the same skeleton: any condition that counts as a break becomes another reason to set the flag to 1.

Write down your definition wherever the metric is published. Two dashboards using 30 minutes and 60 minutes will produce different sessions-per-user, and someone will eventually ask which is right. Neither. They answer slightly different questions.

Figures and names in the examples are illustrative.

Leave a Reply

Trending