Sessions

1-- core.mixpanel_source.sessions
2-- User sessions rebuilt from raw events, using Mixpanel's default timeout rule: a session
3-- ends after 30 minutes without an event from that user.
4--
5-- Grain: one row per session (distinct_id x session start).
6-- Depends on: staging.mixpanel_source.events
7--
8-- Mixpanel computes sessions at query time and does not export them, so they are rebuilt
9-- here. A user's next event opens a new session when more than 30 minutes have passed
10-- since their previous event, or when it falls on a new UTC day. The day break keeps
11-- every session inside one daily row, which is how a sessions-per-day chart reads; a
12-- session running across midnight is counted as two. Change `inactivity_minutes` to match
13-- a custom timeout set in your project's session settings.
14--
15-- Users are the raw `distinct_id`, as in the other models: Mixpanel's identity merge is
16-- not applied, so a session before login and one after are two sessions. Every event
17-- counts, including automatic ones; filter event_name in `ordered` to narrow that.
18-- `duration_seconds` is the time from first to last event, so a one-event session is 0.
19
20WITH settings AS (
21    SELECT 30 AS inactivity_minutes
22),
23
24ordered AS (
25    SELECT
26        e.distinct_id,
27        e.event_name,
28        e.event_at,
29        DATE(e.event_at)                                                  AS event_date,
30        LAG(e.event_at) OVER (
31            PARTITION BY e.distinct_id, DATE(e.event_at) ORDER BY e.event_at, e.event_hash
32        )                                                                 AS prior_event_at,
33        e.event_hash
34    FROM {{staging.mixpanel_source.events}} e
35    WHERE e.distinct_id IS NOT NULL
36),
37
38flagged AS (
39    SELECT
40        o.*,