Journey and path analysis with SQL

A funnel tests a path you already know. Journey analysis discovers what people or accounts actually did before or after a meaningful event.

GraphJSON does not currently expose a dedicated journey-map visualization. Use ClickHouse SQL to construct bounded paths, inspect them as a table or bar chart, and convert a useful discovery into a governed funnel or metric.

Define the analytical grain#

Choose one:

• anonymous visitor
• user
• account
• session
• workflow instance

Do not mix grains in one path. An account path can interleave actions from several teammates; that may be correct for a collaborative workflow and wrong for an individual onboarding flow.

Define:

Start: first signup_started
End: signup_completed or 24-hour timeout
Entity: anonymous_id
Maximum steps: 8
Noise events: heartbeat, page_visible, tooltip_opened
Repeat policy: collapse consecutive repeats
Time zone: UTC

Bound time and steps before querying. Unbounded histories produce paths that are slow, noisy, and difficult to interpret.

Instrument path-ready events#

Each event needs:

{
  "event": "integration_test_started",
  "event_id": "evt_01J...",
  "user_id": "usr_42",
  "account_id": "acct_7",
  "session_id": "ses_91",
  "workflow_id": "onboarding_42",
  "schema_version": 1
}

Use a workflow ID when one journey can span sessions or several people. Use a session ID when inactivity should end the path. Preserve event time and a stable tie-breaker for events that can occur in the same second.

Remove noise before building arrays#

Start from a curated event set:

WITH relevant AS (
  SELECT
    JSONExtractString(json, 'session_id') AS session_id,
    JSONExtractString(json, 'event') AS event,
    JSONExtractString(json, 'event_id') AS event_id,
    timestamp
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 30)
    AND JSONExtractString(json, 'session_id') != ''
    AND JSONExtractString(json, 'event') IN (
      'signup_started',
      'workspace_named',
      'data_source_connected',
      'integration_test_started',
      'integration_test_passed',
      'dashboard_published',
      'signup_abandoned'
    )
)
SELECT count()
FROM relevant

Do not remove an event only because it makes the path untidy. Remove it because the protocol defines it as irrelevant to the decision.

Build ordered session paths#

WITH
  relevant AS (
    SELECT
      JSONExtractString(json, 'session_id') AS session_id,
      JSONExtractString(json, 'event') AS event,
      JSONExtractString(json, 'event_id') AS event_id,
      timestamp
    FROM product_events
    WHERE
      toDateTime(timestamp) >= subtractDays(now(), 30)
      AND JSONExtractString(json, 'session_id') != ''
      AND JSONExtractString(json, 'event') IN (
        'signup_started',
        'workspace_named',
        'data_source_connected',
        'integration_test_passed',
        'dashboard_published'
      )
  ),
  paths AS (
    SELECT
      session_id,
      arrayMap(
        item -> item.3,
        arraySort(
          item -> (item.1, item.2),
          groupArray((timestamp, event_id, event))
        )
      ) AS path
    FROM relevant
    GROUP BY session_id
  )
SELECT
  arrayStringConcat(arraySlice(path, 1, 8), ' → ') AS journey,
  count() AS sessions
FROM paths
WHERE length(path) > 0
GROUP BY journey
ORDER BY sessions DESC
LIMIT 50

event_id provides deterministic ordering when timestamps match. If IDs are random rather than ordered, add a producer sequence field for workflows where within-second order matters.

Collapse consecutive repeats#

Repeated page views or retries can dominate a path. Collapse only adjacent repeats:

arrayFilter(
  (event, index) ->
    index = 1 OR event != path[index - 1],
  path,
  arrayEnumerate(path)
) AS collapsed_path

Do not remove all repeated occurrences globally. A user who returns to an earlier step is analytically different from one who saw the event twice in immediate succession.

Find the next action after a start#

WITH paths AS (
  SELECT
    JSONExtractString(json, 'session_id') AS session_id,
    arrayMap(
      item -> item.3,
      arraySort(
        item -> (item.1, item.2),
        groupArray((
          timestamp,
          JSONExtractString(json, 'event_id'),
          JSONExtractString(json, 'event')
        ))
      )
    ) AS path
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 30)
    AND JSONExtractString(json, 'session_id') != ''
  GROUP BY session_id
)
SELECT
  if(
    indexOf(path, 'signup_started') = 0,
    'not_started',
    if(
      indexOf(path, 'signup_started') = length(path),
      'no_next_event',
      path[indexOf(path, 'signup_started') + 1]
    )
  ) AS next_event,
  count() AS sessions
FROM paths
WHERE indexOf(path, 'signup_started') > 0
GROUP BY next_event
ORDER BY sessions DESC

This uses the first signup_started. If repeated starts have distinct meaning, choose an occurrence policy explicitly.

Find paths before conversion#

WITH paths AS (
  SELECT
    JSONExtractString(json, 'session_id') AS session_id,
    arrayMap(
      item -> item.3,
      arraySort(
        item -> (item.1, item.2),
        groupArray((
          timestamp,
          JSONExtractString(json, 'event_id'),
          JSONExtractString(json, 'event')
        ))
      )
    ) AS path
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 30)
    AND JSONExtractString(json, 'session_id') != ''
  GROUP BY session_id
)
SELECT
  arrayStringConcat(
    arraySlice(
      path,
      greatest(indexOf(path, 'dashboard_published') - 4, 1),
      5
    ),
    ' → '
  ) AS conversion_path,
  count() AS sessions
FROM paths
WHERE indexOf(path, 'dashboard_published') > 0
GROUP BY conversion_path
ORDER BY sessions DESC
LIMIT 30

Validate array-boundary behavior with known sessions. The result includes up to four preceding events plus conversion.

Compare converted and abandoned paths#

Create one row per session:

SELECT
  session_id,
  has(path, 'dashboard_published') AS converted,
  arrayStringConcat(arraySlice(path, 1, 6), ' → ') AS first_six_events
FROM paths

Then group by converted and first_six_events. Look for:

• steps common only in converted paths
• loops and repeated configuration steps
• error or support actions before abandonment
• alternate successful routes
• long gaps between specific steps

This is descriptive. A behavior that appears in successful paths is not necessarily causal.

Measure time between steps#

For a known pair:

WITH per_session AS (
  SELECT
    JSONExtractString(json, 'session_id') AS session_id,
    minIf(
      timestamp,
      JSONExtractString(json, 'event') = 'signup_started'
    ) AS started_at,
    minIf(
      timestamp,
      JSONExtractString(json, 'event') = 'dashboard_published'
    ) AS completed_at
  FROM product_events
  WHERE
    toDateTime(timestamp) >= subtractDays(now(), 30)
    AND JSONExtractString(json, 'session_id') != ''
  GROUP BY session_id
)
SELECT
  quantileExact(0.50)(completed_at - started_at) AS p50_seconds,
  quantileExact(0.90)(completed_at - started_at) AS p90_seconds
FROM per_session
WHERE completed_at >= started_at AND started_at > 0

Show conversion rate alongside time among converters. A fast median among the small group that completed can hide a large abandoned population.

Define drop-off#

“No next event” can mean:

• the user left
• the session ID changed
• the observation window ended
• instrumentation failed
• the final action happens in another service

Use a settled window. For a 24-hour journey, exclude starts newer than 24 hours from a final drop-off rate.

Emit an explicit terminal event when the workflow knows it ended unsuccessfully. Do not invent signup_abandoned from browser closure unless your definition can support it.

Handle paths across devices or people#

An anonymous session path should not silently become an account path at login. Choose whether to:

• keep the anonymous session intact
• link it to the known user retrospectively
• begin a new authenticated path
• group by a durable workflow ID

For collaborative onboarding, group by account_id or workflow_id and accept that multiple users can contribute. Include actor ID for drill-down, but do not count actors as paths accidentally.

Use Identity stitching and account lifecycle for sign-in and account-switch semantics.

Control path explosion#

Path combinations grow rapidly. Bound:

• date range
• eligible population
• event allowlist
• maximum steps
• session or workflow duration
• minimum path frequency
• output rows

Normalize events before path analysis. Dynamic names such as viewed_report_391 turn every entity into a new branch.

Start with one entry or outcome event. Do not generate every path through the entire product and expect one readable answer.

Turn discovery into a governed metric#

When a path matters:

1. inspect representative sessions
2. confirm instrumentation completeness
3. define the intended ordered steps
4. choose entity and conversion window
5. build a funnel or explicit SQL metric
6. add a metric owner and fixtures
7. monitor step completeness

Journey exploration generates hypotheses. The governed funnel becomes the repeatable measurement.

Validation checklist#

• Entity, start, end, window, and maximum steps are explicit.
• Same-second ordering has a deterministic tie-breaker.
• Noise removal follows a written rule.
• Consecutive-repeat collapse is distinguished from global deduplication.
• Recent immature paths are excluded from final drop-off.
• Anonymous, user, account, and workflow paths are not mixed.
• High-cardinality event names are normalized.
• SQL results reconcile against hand-constructed journeys.
• Descriptive associations are not presented as causal.
• Important discoveries become versioned funnels or metrics.

Continue with Build a conversion funnel and A/B test, Create retention curves, and Test metrics and saved SQL in CI.