How to Create User and Session Segments in BigQuery Using GA4 Data

May 10, 2026

When you connect Google Analytics with Google BigQuery, you unlock access to raw event-level data that can be analyzed far beyond the standard GA4 reports.

One of the most useful applications of BigQuery is segmentation.

Instead of relying only on predefined audiences inside GA4, you can create highly customized user and session segments using SQL queries directly on exported event data.

In this article, we will explore:

  • User-level segmentation
  • Session-level segmentation
  • How window functions simplify audience creation
  • Practical use cases for GA4 raw data analysis

Why Segment Data in BigQuery?

The GA4 interface is excellent for reporting, but BigQuery gives you much more control over behavioral analysis.

With BigQuery, you can:

  • Analyze complete user journeys
  • Build advanced audiences
  • Combine multiple engagement signals
  • Create custom funnel logic
  • Work with raw unsampled data
  • Join GA4 data with CRM or backend systems

This is especially valuable for businesses running:

  • Ecommerce stores
  • SaaS platforms
  • Online learning portals
  • Lead generation websites
  • Subscription products

Understanding GA4 Event Export Tables

When GA4 exports data into BigQuery, every interaction becomes an event row.

A typical export contains fields like:

FieldPurpose
user_pseudo_idAnonymous user identifier
event_nameName of the triggered event
event_timestampEvent time
event_paramsAdditional event metadata
device.categoryDevice information

Your exported tables usually look like this:

project_id.dataset_name.events_YYYYMMDD

Each daily table contains all tracked events for that date.

Example 1: User-Level Segmentation

User-level segmentation helps identify users who completed a specific action at least once.

For example:

  • Users who downloaded a resource
  • Users who watched a webinar
  • Users who started a trial
  • Users who clicked a pricing button

In this example, we will identify users who triggered a newsletter_signup event.

SQL Query

SELECT

  user_pseudo_id,

  device.category,

  event_name,

  event_timestamp

FROM

  `your-project-id.your_dataset.events_YYYYMMDD`

QUALIFY LOGICAL_OR(event_name = ‘newsletter_signup’)

OVER (

  PARTITION BY user_pseudo_id

);

How the Query Works

The key part of this query is:

LOGICAL_OR(event_name = ‘newsletter_signup’)

This checks whether the condition becomes TRUE for any event belonging to the same user.

The partition:

PARTITION BY user_pseudo_id

groups all events for each individual user.

As a result, the query returns all event rows belonging to users who signed up for the newsletter at least once.

Why Window Functions Are Useful

Window functions simplify segmentation significantly.

Instead of creating complicated subqueries, you can evaluate conditions across groups of rows directly.

This makes queries:

  • Easier to maintain
  • Faster to read
  • More scalable for large datasets

You can easily replace the event name with other custom events such as:

trial_started

demo_booked

file_download

contact_form_submit

subscription_renewed

Example 2: Session-Level Segmentation

Sometimes user-level analysis is too broad.

You may want to identify sessions where multiple interactions happened together.

For example:

  • Sessions with both a search and a product comparison
  • Sessions containing pricing page views and contact requests
  • Sessions where users watched a tutorial and started onboarding

Session-based segmentation is extremely useful for funnel optimization.

Extracting Session IDs from GA4 Data

GA4 stores session IDs inside nested event parameters.

To work with sessions properly, we first extract the ga_session_id.

SQL Query

WITH session_events AS (

  SELECT

    user_pseudo_id,

    device.category,

    event_name,

    event_timestamp,

    CAST(

      (

        SELECT value.int_value

        FROM UNNEST(event_params)

        WHERE key = ‘ga_session_id’

      ) AS STRING

    ) AS session_id

  FROM

    `your-project-id.your_dataset.events_YYYYMMDD`

)

SELECT

  user_pseudo_id,

  device.category,

  event_name,

  event_timestamp

FROM session_events

QUALIFY LOGICAL_AND(

  event_name IN (‘search’, ‘generate_lead’)

)

OVER (

  PARTITION BY user_pseudo_id, session_id

);

Breaking Down the Session Query

Step 1: Extract the Session ID

GA4 stores session identifiers inside event_params.

We extract it using:

SELECT value.int_value

FROM UNNEST(event_params)

WHERE key = ‘ga_session_id’

The UNNEST() function converts nested parameters into queryable rows.

Step 2: Group Events by Session

This section:

PARTITION BY user_pseudo_id, session_id

groups all events that belong to the same user session.

Step 3: Apply Session Conditions

The query checks whether the session contains both:

search

generate_lead

This helps identify sessions where users searched for something and later became leads.

Practical Use Cases

SaaS Funnel Analysis

Identify sessions where users:

  • Viewed feature pages
  • Started free trials
  • Requested demos

Content Marketing

Analyze sessions where users:

  • Downloaded guides
  • Signed up for newsletters
  • Consumed multiple blog posts

Ecommerce Optimization

Track sessions with:

  • Product discovery
  • Wishlist activity
  • Coupon usage
  • Checkout abandonment

This creates much more accurate attribution and audience analysis.

Final Thoughts

BigQuery gives you complete control over GA4 audience analysis.

By using SQL window functions and raw event data, you can build advanced user and session segments that go far beyond standard analytics reports.

Whether you are analyzing SaaS funnels, ecommerce behavior, or lead generation journeys, BigQuery segmentation can help uncover insights that are difficult to find inside the GA4 interface alone.