Cross-Device Session Stitching and Multi-Touch Attribution in GA4 Using BigQuery

In today’s multi-device world, users often start a journey on mobile, continue on desktop, and convert on a tablet. GA4 UI can deduplicate users but splits sessions across devices, making session counts and marketing attribution unreliable.

This creates a major challenge for marketers: Which touchpoint truly drove the conversion?

BigQuery provides the ability to reconstruct sessions across devices using session stitching rules, giving accurate insights into user journeys, engagement, and campaign ROI.

The Problem: Session Counts and Attribution Don’t Align

GA4 sessions can be split due to:

1. Device Changes: Mobile → desktop → tablet sessions are counted separately.
2. Inactivity Timeout: Sessions end after 30 minutes of inactivity.
3. Traffic Source Changes: Switching campaigns mid-session triggers a new session.
4. Midnight Cutoff: Sessions crossing midnight are split for daily reporting.

Impact:

• Misattribution of conversions to the last session or last channel
• Overcounting sessions across devices
• Confusion when comparing GA4 UI to BigQuery exports

Use Case: Multi-Touch Attribution Across Devices

Scenario:
An e-commerce site runs multiple campaigns (email, paid search, social, affiliates). Users may interact with several devices and channels before purchasing.

Problem:

• GA4 splits sessions and may attribute the conversion incorrectly.
• Analysts struggle to track true marketing ROI across devices.

Solution:

• Reconstruct sessions in BigQuery using cross-device session stitching:
  • Inactivity timeout >30 minutes
  • Traffic source / campaign changes
  • Midnight cutoff
  • Device changes and cross-device linking via user_id
• Assign session IDs per user-event sequence
• Map conversions to stitched sessions to perform multi-touch attribution

Outcome:

• Accurate session counts and conversion paths
• True ROI per marketing channel
• Improved marketing optimization across devices

Simple SQL Example: Count Session Starts

For basic session counts, you can start with the session_start event:

SELECT
  user_pseudo_id,
  COUNT(event_name) AS sessions
FROM
  `your_project.analytics_XXXX.events_*`
WHERE
  event_name = 'session_start'
GROUP BY user_pseudo_id
ORDER BY sessions DESC;

Explanation:
Counts every session_start event per user. Simple, but doesn’t handle multi-device or session-splitting scenarios.

Deep Dive SQL: Cross-Device Session Stitching

Here’s a full session-stitching SQL for GA4 in BigQuery:

-- Step 1: Collect events and previous context per user
WITH events_with_lag AS (
  SELECT
    user_pseudo_id,
    user_id, -- Use for cross-device tracking
    event_timestamp,
    event_name,
    traffic_source.source AS traffic_source,
    traffic_source.medium AS traffic_medium,
    device.category AS device_category,
    LAG(event_timestamp) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS prev_ts,
    LAG(traffic_source.source) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS prev_source,
    LAG(device.category) OVER(PARTITION BY user_pseudo_id ORDER BY event_timestamp) AS prev_device
  FROM
    `your_project.analytics_XXXX.events_*`
),

-- Step 2: Identify session boundaries
session_flags AS (
  SELECT
    *,
    CASE
      WHEN TIMESTAMP_DIFF(TIMESTAMP_MICROS(event_timestamp), TIMESTAMP_MICROS(prev_ts), MINUTE) > 30 THEN 1
      WHEN traffic_source != prev_source THEN 1
      WHEN EXTRACT(DATE FROM TIMESTAMP_MICROS(event_timestamp)) != EXTRACT(DATE FROM TIMESTAMP_MICROS(prev_ts)) THEN 1
      WHEN device_category != prev_device THEN 1
      ELSE 0
    END AS new_session_flag
  FROM events_with_lag
),

-- Step 3: Assign session numbers per user
sessionized_events AS (
  SELECT
    *,
    SUM(new_session_flag) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ROWS UNBOUNDED PRECEDING) + 1 AS session_number
  FROM session_flags
)

-- Step 4: Aggregate sessions and map conversions
SELECT
  user_pseudo_id,
  user_id,
  session_number,
  MIN(TIMESTAMP_MICROS(event_timestamp)) AS session_start_time,
  MAX(TIMESTAMP_MICROS(event_timestamp)) AS session_end_time,
  COUNT(*) AS events_in_session,
  ARRAY_AGG(DISTINCT traffic_source) AS traffic_sources_in_session,
  ARRAY_AGG(DISTINCT device_category) AS devices_in_session,
  SUM(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS conversions_in_session
FROM sessionized_events
GROUP BY user_pseudo_id, user_id, session_number
ORDER BY user_pseudo_id, session_start_time;

Key Features:

• Tracks inactivity >30 minutes
• Detects traffic source changes
• Handles midnight cutoff
• Accounts for device changes and cross-device user journeys
• Maps conversions to reconstructed sessions for multi-touch attribution

Key Takeaways

• Multi-device session stitching is essential for accurate marketing attribution.
• Simple SQL counts of session_start are insufficient for multi-touch or cross-device analysis.
• Deep session stitching in BigQuery allows analysts to:
  • Track true session paths
  • Measure multi-touch conversions accurately
  • Optimize campaigns across devices and channels

TL;DR

• GA4 splits sessions for inactivity, source changes, midnight, and device changes.
• Simple SQL counts sessions via session_start.
• Deep-dive BigQuery SQL stitches sessions across devices, sources, and time, enabling accurate multi-touch attribution and marketing insights.