6 BigQuery SQL Tips That Power GA4 Session Stitching & Attribution

Introduction

Analyzing GA4 data in BigQuery can be incredibly powerful — but it’s easy to end up with slow, expensive queries as your logic grows.

After writing complex GA4 SQL for things like session stitching and consent-aware attribution, I’ve learned some valuable techniques to keep things efficient, readable, and scalable.

In this post, I’m sharing 6 practical SQL tips drawn directly from:

• 👉 How to Stitch GA4 Sessions in BigQuery
• 👉 Rebuilding Attribution Models After Consent Delay

Let’s dive in.

1. Use COALESCE() for Clean Identifier Fallbacks

📍 Used in: How to Stitch GA4 Sessions in BigQuery

COALESCE(user_id, user_pseudo_id) AS stitched_user

GA4 users may or may not have a user_id depending on login or consent. COALESCE() lets you fall back gracefully to user_pseudo_id without writing complex logic. It’s perfect for stitching users across anonymous and known sessions.

2. Use FIRST_VALUE() for Consent-Aware Attribution

📍 Used in: Attribution Post

FIRST_VALUE(traffic_source.medium) OVER (
  PARTITION BY user_pseudo_id
  ORDER BY event_timestamp
) AS first_known_medium

FIRST_VALUE() makes it easy to recover the earliest known source/medium once consent is granted. It helps rebuild attribution paths that would otherwise be blank in GA4’s UI.

3. Combine PARTITION BY + ORDER BY for Session Logic

📍 Used in: How to Stitch GA4 Sessions in BigQuery

ROW_NUMBER() OVER (
  PARTITION BY stitched_user
  ORDER BY event_timestamp

This setup allows you to sequence events per user, which is essential for funnel modeling or reconstructing journeys across split sessions.

4. Filter Early to Reduce Query Cost

📍 Used in: How to Stitch GA4 Sessions in BigQuery ; Rebuilding Attribution Models After Consent Delay

WHERE
  _TABLE_SUFFIX BETWEEN '20250701' AND '20250728'
  AND event_name IN ('page_view', 'purchase')

Always filter early. Reducing scanned rows at the beginning (by date, event type, or traffic source) improves performance and saves money.

5. Materialize Reusable Logic

📍 Used in: How to Stitch GA4 Sessions in BigQuery

CREATE OR REPLACE TABLE ga4_stitched_sessions AS (...)

If you’re reusing stitched sessions or attribution logic across dashboards or reports, save the output as a materialized table. It speeds up future queries and makes downstream analysis more stable.

Related Posts

• How to Stitch GA4 Sessions in BigQuery
• Rebuilding Attribution Models After Consent Delay

TL;DR: Cheat Sheet

Want More?

Stay tuned for Friday’s Cheatsheet, where I’ll compile all this week’s GA4 + BigQuery SQL into one downloadable resource.

Or subscribe here to get new SQL drops and walkthroughs every week!