User Paths – A Data Driven Model

A user path is a sequence of sessions by the same user that leads to a conversion, or ends without one. Sessions alone can’t show how people decide over days; user paths can. In this article we build user paths from GA4 data in BigQuery with two rules: a purchase ends a path, and so does a long period of inactivity. The full SQL is included.

Why analyze user paths instead of sessions?

Ask around what the most used concept in web analytics is, and “session” will be one of the most common answers. It’s useful, but many questions need something broader:

• Attribution: which traffic sources contributed to a conversion and which were redundant. Since September 2023, GA4 and Google Ads offer only data-driven and last-click models; with your own paths you can compute first-click, linear or position-based models yourself.
• Behavioral analysis across sessions: what users do in the second or third session before they buy.
• Pain points across sessions: where journeys that never convert break down.

All of these need user paths as “step 0”. Let’s define them in a data-driven way.

What data do you need?

We use the GA4 data model and the raw BigQuery export, because reports don’t expose identifiers such as user_pseudo_id. To illustrate the method, we use Google’s public GA4 e-commerce sample dataset (obfuscated, so internal consistency is limited, but good enough for illustration). We need four things:

• a user identifier (user_pseudo_id),
• a session identifier (the ga_session_id event parameter),
• the timestamp of each session start,
• a flag for sessions with a purchase.

GA4 is event-based and sessions are a derived concept, so we build them ourselves:

WITH TMP AS (
  SELECT
    user_pseudo_id,
    ep.value.int_value AS session_id,
    event_timestamp,
    CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END AS purchase
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` e,
    UNNEST(event_params) ep
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
    AND ep.key = 'ga_session_id'
)
SELECT
  user_pseudo_id,
  session_id,
  TIMESTAMP_MICROS(MIN(event_timestamp)) AS session_start,
  SUM(purchase) AS transactions
FROM TMP
GROUP BY user_pseudo_id, session_id

user_pseudo_id identifies the browser or app instance. ga_session_id is hidden in the nested event_params array as an int_value; it’s unique only per user, so we group by both. The temporary table selects all events with timestamps and flags purchases; grouping gives session-level results. The _TABLE_SUFFIX condition selects January 2021 (about 120,000 sessions). GA4 stores one table per day, so always filter by date to control query cost; see BigQuery costs.

Breakpoint 1: does a conversion end the path?

A user path is a set of sessions leading to a conversion, so we can also call it a conversion process. It ends when the conversion happens. In reality, a customer may buy an item and return an hour later for a related one, which could reasonably belong to the same path. A more nuanced rule is possible, but for this model we assume that a conversion always ends the current path.

Take the user with user_pseudo_id = 27488559.4267170544 (let’s call them Alice). Alice has several sessions and two transactions, so two conversion processes.

Breakpoint 2: when does inactivity end the path?

Something doesn’t fit in Alice’s history: more than a week passed between her second and third session. Sessions in one journey are usually clustered together, so it makes sense to treat the first two sessions as a separate journey. This is the second assumption: prolonged inactivity ends the path.

How long is “prolonged”? We find the empirical distribution of waiting times between consecutive sessions of the same user and choose a percentile as the limit. A higher percentile allows longer journeys; a lower one splits them more often.

Percentile of waiting time	Inactivity limit (sample data, Jan 2021)	Effect on Alice
75th	1.79 days	Sessions 1–2 separated; session 5 becomes a one-session path
90th	5.79 days	Sessions 1–2 separated from the rest

If users return at all, it’s most likely within the first few hours, and more than 90% of returns happen within a week. Your limit will depend on the industry: furniture or travel journeys are longer than fashion or groceries.

Note that the two breakpoints work differently. Inactivity affects the current session (if the gap is too long, this session starts a new path). A conversion affects the next session (after a purchase, the following session starts a new path). One session can trigger both.

How do you build user paths in BigQuery? (full SQL)

The query below puts both rules together. It calculates the time since the previous session with LAG, takes the 90th percentile with APPROX_QUANTILES, flags breakpoints and numbers each user’s paths with a running SUM.

-- 1. Sessions from GA4 events
WITH events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id,
    event_timestamp,
    event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20210101' AND '20210131'
),
sessions AS (
  SELECT
    user_pseudo_id,
    session_id,
    TIMESTAMP_MICROS(MIN(event_timestamp)) AS session_start,
    COUNTIF(event_name = 'purchase') AS transactions
  FROM events
  WHERE session_id IS NOT NULL
  GROUP BY user_pseudo_id, session_id
),
-- 2. Waiting time since the previous session and whether it converted
gaps AS (
  SELECT
    *,
    TIMESTAMP_DIFF(session_start, LAG(session_start) OVER w, MINUTE) / 60 AS hours_since_prev,
    LAG(transactions) OVER w AS prev_transactions
  FROM sessions
  WINDOW w AS (PARTITION BY user_pseudo_id ORDER BY session_start)
),
-- 3. Inactivity limit = 90th percentile of waiting times
inactivity AS (
  SELECT APPROX_QUANTILES(hours_since_prev, 100)[OFFSET(90)] AS limit_h
  FROM gaps
  WHERE hours_since_prev IS NOT NULL
),
-- 4. Breakpoints: first session, previous session converted, or prolonged inactivity
flags AS (
  SELECT
    g.*,
    CASE
      WHEN g.hours_since_prev IS NULL THEN 1
      WHEN g.prev_transactions > 0 THEN 1
      WHEN g.hours_since_prev > i.limit_h THEN 1
      ELSE 0
    END AS new_path
  FROM gaps g
  CROSS JOIN inactivity i
)
-- 5. Path number = running count of breakpoints per user
SELECT
  *,
  SUM(new_path) OVER (
    PARTITION BY user_pseudo_id ORDER BY session_start
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS path_number
FROM flags
ORDER BY user_pseudo_id, session_start

Save the result as a table (for example user_paths) and summarize it at path level:

-- Path-level summary (run on the result of the query above, saved as user_paths)
SELECT
  user_pseudo_id,
  path_number,
  COUNT(*) AS sessions,
  MIN(session_start) AS path_start,
  MAX(session_start) AS path_end,
  MAX(IF(transactions > 0, 1, 0)) AS converted
FROM user_paths
GROUP BY user_pseudo_id, path_number

To change the model, replace OFFSET(90) with another percentile, or set a fixed limit (for example 7 days = 168 hours).

What are the limitations?

• Devices: user_pseudo_id is per browser or app; journeys across devices need user_id (logged-in users).
• Consent: without consent for analytics storage, sessions can’t be linked to one user, so paths are shorter or missing.
• Cookie lifetime: cleared cookies or browser limits on cookies break paths.
• Traffic sources: for attribution, add source and medium per session. The traffic_source field shows only the user’s first acquisition; use collected_traffic_source or session_traffic_source_last_click (added in 2023 and 2024; see Adswerve). The 2020–2021 sample dataset doesn’t include them.

What can you do with user paths?

• Attribution: assign each path’s conversion to its sessions’ sources with first-click, linear, position-based or your own data-driven model. See GA4 attribution models and attribution analysis.
• Path metrics: sessions per converting path, time to conversion, share of paths that convert.
• Early pain points: compare the first sessions of converting and non-converting paths to see where journeys fail. See the e-commerce purchase path.

FAQ

How do I get session IDs from the GA4 BigQuery export?

Read the ga_session_id parameter from event_params, for example (SELECT value.int_value FROM UNNEST(event_params) WHERE key = ‘ga_session_id’). It’s unique only together with user_pseudo_id.

How long should the inactivity limit be?

Base it on your data: take the distribution of time between sessions and choose a percentile, such as the 75th or 90th. In the GA4 sample dataset, the 90th percentile is 5.79 days.

Can I use position-based attribution in GA4?

Not in the interface. Google removed first-click, linear, time decay and position-based models in September 2023. You can still calculate them on your own user paths in BigQuery.

Does this work across devices?

Only if you set user_id for logged-in users. user_pseudo_id identifies a browser or app instance, not a person.

Sources

• Google for Developers: GA4 e-commerce sample dataset
• Google Analytics Help: GA4 BigQuery Export schema
• Google Ads Help: Rules-based attribution models are going away (2023)
• Adswerve: Four GA4 traffic source fields in the BigQuery export
• Google Cloud: APPROX_QUANTILES