Ecommerce Funnel Analysis in BigQuery

Introduction

30 Second Summary

Ever added something to your online shopping cart and then abandoned it? You are not alone. Businesses lose millions of potential sales when shoppers drop off before completing a purchase, and figuring out exactly where people leave is one of the most valuable skills in digital analytics.

In this project, you will query real ecommerce data from the Google Merchandise Store to build a conversion funnel analysis. You will use advanced SQL in BigQuery to track users from their first session all the way through to purchase, calculating where shoppers drop off at each stage.

What You'll Build

A complete ecommerce conversion funnel analysis queried from real GA4 event data using progressively advanced SQL patterns in BigQuery.

By the end of this project, you'll have:

• A series of advanced SQL queries demonstrating CTEs, UNNEST, COUNTIF, and window functions on nested GA4 data.
• A funnel summary table showing user counts, session counts, and drop-off percentages across six stages of the purchase journey.
• A CSV export of your funnel metrics formatted and ready for dashboard ingestion in Power BI.
• Secret Mission: Segment your funnel by device category to identify where mobile users drop off compared to desktop users.

Are there any prerequisites?

You should be comfortable with basic SQL (SELECT, WHERE, GROUP BY, JOIN). You also need a Google Cloud account with the BigQuery API enabled. The free tier is more than sufficient for this project.

Before We Start

Before We Start...

Set Up BigQuery and Access the GA4 Dataset

Before you can analyse a conversion funnel, you need access to the raw event data that captures every user interaction.

Google provides a public sample dataset from the Google Merchandise Store containing real (obfuscated) GA4 event data. In this step, you will locate that dataset in BigQuery, explore its structure, and run a verification query to confirm everything is working.

In this step, get ready to:

• Locate the GA4 public dataset in the BigQuery Explorer pane.
• Explore the dataset structure to understand its tables and schema.
• Run a verification query to confirm access and check the dataset scope.

Confirm your BigQuery console is ready

Your BigQuery console should already be open in your browser with the BigQuery API enabled on your Google Cloud project.

• In the BigQuery console, look at the left side of the screen. You should see the Explorer pane listing your project and any resources.
• If you do not see the left pane, click Expand left pane to open it.

What is the Explorer pane?

The Explorer pane is your navigation hub in BigQuery. It lists all the projects, datasets, and tables you have access to. You will use it to browse public datasets, find specific tables, and search for resources.

Navigate to the GA4 public dataset

BigQuery hosts thousands of public datasets that anyone can query for free. The one you need is the GA4 sample ecommerce dataset from the Google Merchandise Store.

• In the Explorer pane, type ga4_obfuscated_sample_ecommerce into the search field at the top.
• In the search results, look for the dataset under the bigquery-public-data project.
• Click ga4_obfuscated_sample_ecommerce to open it.

You should see a list of tables in the details pane. They all follow the naming pattern events_YYYYMMDD because GA4 creates one table per day.

• Click on any one of these tables (for example, events_20201101).
• Click the Schema tab to see the column definitions.

Notice that some columns have a type of RECORD and a mode of REPEATED. These are nested and repeated fields, which is what makes GA4 data "advanced SQL" to work with.

What are nested and repeated fields?

Standard SQL tables store one value per cell. GA4 stores multiple values inside a single row using arrays (REPEATED) and structs (RECORD). For example, event_params holds all the key-value parameters for an event inside a single array field.

You will learn how to "unwrap" these nested fields using UNNEST in the next step.

Key columns to notice

• event_name stores which action the user took (like page_view or purchase).
• user_pseudo_id is an anonymous identifier for each user.
• event_params (RECORD, REPEATED) holds the parameters attached to each event.
• items (RECORD, REPEATED) holds product-level data for ecommerce events.
• device (RECORD) holds information about the user's device category, browser, and OS.

Run a verification query

Now you will run a quick query to confirm you can access the dataset and understand its scope. This query counts total events, unique users, and the date range covered.

• Open a new query editor by clicking SQL query in the top area of the BigQuery console.
• Paste the following query into the editor:

SELECT
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS user_count,
  COUNT(DISTINCT event_date) AS day_count,
  MIN(event_date) AS first_date,
  MAX(event_date) AS last_date
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

What does this query do?

• COUNT(*) counts every event row across all tables in the dataset.
• COUNT(DISTINCT user_pseudo_id) counts how many unique anonymous users exist.
• COUNT(DISTINCT event_date) tells you how many days of data the dataset spans.
• MIN(event_date) and MAX(event_date) show the first and last date in the dataset.
• The events_* wildcard tells BigQuery to query across all daily tables at once, rather than specifying each table individually.

• Click Run to execute the query.

After a few seconds, you should see results in the Query results pane below the editor. The results confirm the dataset spans from 20201101 to 20210131 (November 2020 to January 2021) and contains hundreds of thousands of unique users.

Query not returning results?

• Make sure you copied the entire query including the backticks around the table name. The backtick character (`) is required for project-qualified table references in BigQuery.
• Check that BigQuery API is enabled on your project. If you see a permissions error, return to the Google Cloud console and verify the API is active.
• If you see a syntax error, make sure you did not accidentally include any extra characters when pasting.

Help me debug my BigQuery verification query.

✔️ Awesome, I've got everything!

Great. Double check you have results showing the dataset date range and user count before moving on.

ⓧ I'd like to double check the full code

Here is the complete verification query. Make sure yours matches exactly:

SELECT
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS user_count,
  COUNT(DISTINCT event_date) AS day_count,
  MIN(event_date) AS first_date,
  MAX(event_date) AS last_date
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`;

Your BigQuery environment is confirmed and you have a feel for the GA4 dataset structure. Next up, you will dive into the nested fields and learn the UNNEST pattern that unlocks GA4 event data.

Explore the GA4 Event Schema and Nested Fields

You've confirmed that the GA4 dataset is accessible and spans three months of ecommerce event data. Now it's time to look inside and understand what that data actually contains.

GA4 stores everything as events, and each event carries extra detail in nested arrays. Before you can build a funnel, you need to learn how to pull values out of those nested structures. This step introduces the patterns you'll use for the rest of the project.

In this step, get ready to:

• Query distinct event names to identify the ecommerce funnel stages.
• Use UNNEST to extract values from nested event parameters.
• Query the items array to explore product-level data.

Query distinct event names in the dataset

GA4 records every user interaction as a named event. To build a conversion funnel, you first need to know which event names exist in the dataset and how frequently they occur.

• In the BigQuery console, click SQL query to open a new query editor tab.
• Paste the following query to count events and users per event name:

SELECT
  event_name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
GROUP BY event_name
ORDER BY event_count DESC;

What does this query do?

• `_TABLE_SUFFIX BETWEEN '20201101' AND '20210131'` filters the wildcard table to only scan data from November 2020 through January 2021, keeping your query fast and free-tier friendly.
• `GROUP BY event_name` groups every row by its event type, so you get one row per unique event.
• `COUNT(DISTINCT user_pseudo_id)` tells you how many unique users triggered each event, not just total occurrences.

• Click Run to execute the query.
• In the results table, look for these six event names: session_start, page_view, view_item, add_to_cart, begin_checkout, and purchase.
• Save the query by pressing Cmd+S (macOS) or Ctrl+S (Windows).
• In the Save query dialog, type explore_events as the name and click Save.

What should I see?

Your results table shows dozens of event names sorted by frequency. The six ecommerce funnel events listed above are your building blocks for the conversion funnel in Step 3. You'll also see events like scroll, click, and first_visit that GA4 tracks automatically.

Query returned zero rows?

• Check that the _TABLE_SUFFIX values are quoted strings ('20201101' not 20201101 without quotes).
• Confirm the dataset path matches exactly: bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*.
• Make sure the backticks surround the full table reference, not single quotes.

Still stuck? Help me debug my event names query in BigQuery.

Extract values from nested event parameters with UNNEST

Each row in the GA4 dataset has an event_params column that stores an array of key-value pairs. You cannot access these values with a simple dot notation. Instead, you use a subquery with UNNEST to reach inside and pull out a specific key.

• Click SQL query to open a new query editor tab.
• Paste the following query to extract session IDs and page locations from page_view events:

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'page_view'
LIMIT 100;

What does this query do?

The subquery pattern (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') unpacks the event_params array, finds the row where key equals 'ga_session_id', and returns the integer value stored there.

This is the standard extraction pattern for GA4 data in BigQuery. You'll use it repeatedly throughout this project whenever you need a specific parameter.

• Click Run to execute the query.
• Verify that the ga_session_id column shows integer values and the page_location column shows URL strings.
• Save the query by pressing Cmd+S (macOS) or Ctrl+S (Windows).
• Name it unnest_params and click Save.

Why int_value for session ID but string_value for page location?

GA4 stores parameter values in a struct with multiple typed fields: int_value, string_value, float_value, and double_value. Only one is populated per parameter, depending on the data type. Session IDs are integers, so you use value.int_value. URLs are strings, so you use value.string_value.

Seeing NULL in the ga_session_id column?

• Make sure you used value.int_value (not value.string_value) for the ga_session_id parameter.
• Check that the key string is exactly 'ga_session_id' with no extra spaces or capital letters.

Help me fix my UNNEST query for event_params.

Query the items array for product-level data

Besides event parameters, GA4 also stores product details in a separate nested array called items. This array holds item IDs, names, and other product attributes for ecommerce events.

• Click SQL query to open a new query editor tab.
• Paste the following query to find the top 10 products added to carts:

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items)
WHERE
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY 1, 2
ORDER BY user_count DESC
LIMIT 10;

What does this query do?

The comma before UNNEST(items) in the FROM clause is a cross join that explodes each item in the array into its own row. This lets you reference item_id and item_name as regular columns.

GROUP BY 1, 2 is shorthand for grouping by the first and second columns in the SELECT list. The result is a ranked list of the most popular products by unique users who added them to cart.

• Click Run to execute the query.
• Verify that your results show 10 rows with product names from the Google Merchandise Store.
• Save the query as unnest_items by pressing Cmd+S (macOS) or Ctrl+S (Windows) and entering the name.

Error about UNNEST in the FROM clause?

• Make sure there's a comma between the table reference and UNNEST(items). The comma creates an implicit cross join.
• Check that you wrote UNNEST(items) (no backticks around items).

Help me fix my UNNEST items query.

✔️ Awesome, I've got everything!

Great. Make sure all three queries are saved in BigQuery (explore_events, unnest_params, unnest_items).

ⓧ I'd like to double check the full code

Here are all three queries from this step:

explore_events.sql

SELECT
  event_name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
GROUP BY event_name
ORDER BY event_count DESC;

unnest_params.sql

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name = 'page_view'
LIMIT 100;

unnest_items.sql

SELECT
  item_id,
  item_name,
  COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`,
  UNNEST(items)
WHERE
  _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  AND event_name IN ('add_to_cart')
GROUP BY 1, 2
ORDER BY user_count DESC
LIMIT 10;

You've mapped the event landscape and unlocked the two core patterns for working with GA4's nested data. Next up, you'll use these patterns to build a full conversion funnel with CTEs and conditional aggregation.

Build the Conversion Funnel with CTEs and COUNTIF

You now know what events exist in the GA4 dataset and how to extract values from nested fields using UNNEST.

In this step, you will combine those skills to build a complete conversion funnel. The goal is to count how many unique users reached each stage of the purchase journey, from session_start all the way to purchase. You will use a Common Table Expression (CTE) to structure the query and COUNTIF for conditional aggregation.

In this step, get ready to:

• Write a CTE that flags which funnel events each user triggered.
• Count distinct users at each of the 6 funnel stages using conditional aggregation.
• Calculate the overall conversion rate from session start to purchase.

Write the funnel query with CTEs and COUNTIF

A CTE lets you define a temporary named result set that you can reference later in the same query. Think of it as creating a mini table on the fly.

Your funnel query uses a CTE to first count how many times each user triggered each event, then a main SELECT to tally how many distinct users reached each funnel stage.

• In the BigQuery console, click the + button to open a new query tab.
• Paste the following query into the editor:

WITH funnel_users AS (
  SELECT
    user_pseudo_id,
    COUNTIF(event_name = 'session_start') AS has_session_start,
    COUNTIF(event_name = 'page_view') AS has_page_view,
    COUNTIF(event_name = 'view_item') AS has_view_item,
    COUNTIF(event_name = 'add_to_cart') AS has_add_to_cart,
    COUNTIF(event_name = 'begin_checkout') AS has_begin_checkout,
    COUNTIF(event_name = 'purchase') AS has_purchase
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  GROUP BY user_pseudo_id
)

SELECT
  COUNT(DISTINCT CASE WHEN has_session_start > 0 THEN user_pseudo_id END) AS users_session_start,
  COUNT(DISTINCT CASE WHEN has_page_view > 0 THEN user_pseudo_id END) AS users_page_view,
  COUNT(DISTINCT CASE WHEN has_view_item > 0 THEN user_pseudo_id END) AS users_view_item,
  COUNT(DISTINCT CASE WHEN has_add_to_cart > 0 THEN user_pseudo_id END) AS users_add_to_cart,
  COUNT(DISTINCT CASE WHEN has_begin_checkout > 0 THEN user_pseudo_id END) AS users_begin_checkout,
  COUNT(DISTINCT CASE WHEN has_purchase > 0 THEN user_pseudo_id END) AS users_purchase,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN has_purchase > 0 THEN user_pseudo_id END),
    COUNT(DISTINCT CASE WHEN has_session_start > 0 THEN user_pseudo_id END)
  ) * 100, 2) AS overall_conversion_rate_pct
FROM funnel_users;

What does this query do?

The query works in two stages:

• The CTE (WITH funnel_users AS) groups all events by user_pseudo_id and uses COUNTIF to count how many times each user triggered each event type. If the count is greater than zero, the user reached that stage.
• The main SELECT uses COUNT(DISTINCT CASE WHEN ... THEN user_pseudo_id END) to tally how many unique users had a non-zero count for each stage. This gives you the total users at each funnel step.
• SAFE_DIVIDE divides purchasers by session starters to get the overall conversion rate. It returns NULL instead of an error if the denominator is zero.

• Click Run to execute the query.

You should see a single row of results with seven columns. Each users_ column shows the count of unique users who reached that stage, and overall_conversion_rate_pct shows the percentage of users who made it from session start all the way to purchase.

Query returned an error?

• Check that the WITH keyword is on the first line with no stray characters before it.
• Make sure there is no semicolon between the closing parenthesis of the CTE and the SELECT keyword. The CTE and the SELECT are part of the same statement.
• Verify the backtick characters around the table name are backticks (`), not single quotes (').

Still stuck? Help me debug my funnel CTE query in BigQuery.

Interpret the step-over-step conversion rates

Your single-row result already contains the data for step-over-step conversion analysis. Each users_ column represents the top of a funnel stage.

• Compare adjacent columns to see where the biggest drop-offs happen. For example, divide users_view_item by users_page_view to get the page-view-to-view-item conversion rate.
• Look at the overall_conversion_rate_pct column. This is the end-to-end funnel efficiency: purchasers divided by total session starters, multiplied by 100.

Why use SAFE_DIVIDE instead of regular division?

If a funnel stage has zero users (possible with filtered date ranges or segments), regular division would throw a division-by-zero error and kill your query. SAFE_DIVIDE returns NULL instead, keeping your query running safely.

What are CTEs good for?

CTEs make complex queries readable by breaking them into named stages. Each stage builds on the previous one, so you can debug and reason about one piece at a time.

In the next step, you will chain multiple CTEs together for session-level analysis. The pattern scales cleanly no matter how many intermediate calculations you need.

✔️ Awesome, I've got everything!

Great. Make sure you have saved your query as funnel_analysis.sql (or keep the tab open) so you can reference it later.

ⓧ I'd like to double check the full code

Here is the complete funnel query for comparison:

WITH funnel_users AS (
  SELECT
    user_pseudo_id,
    COUNTIF(event_name = 'session_start') AS has_session_start,
    COUNTIF(event_name = 'page_view') AS has_page_view,
    COUNTIF(event_name = 'view_item') AS has_view_item,
    COUNTIF(event_name = 'add_to_cart') AS has_add_to_cart,
    COUNTIF(event_name = 'begin_checkout') AS has_begin_checkout,
    COUNTIF(event_name = 'purchase') AS has_purchase
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
  GROUP BY user_pseudo_id
)

SELECT
  COUNT(DISTINCT CASE WHEN has_session_start > 0 THEN user_pseudo_id END) AS users_session_start,
  COUNT(DISTINCT CASE WHEN has_page_view > 0 THEN user_pseudo_id END) AS users_page_view,
  COUNT(DISTINCT CASE WHEN has_view_item > 0 THEN user_pseudo_id END) AS users_view_item,
  COUNT(DISTINCT CASE WHEN has_add_to_cart > 0 THEN user_pseudo_id END) AS users_add_to_cart,
  COUNT(DISTINCT CASE WHEN has_begin_checkout > 0 THEN user_pseudo_id END) AS users_begin_checkout,
  COUNT(DISTINCT CASE WHEN has_purchase > 0 THEN user_pseudo_id END) AS users_purchase,
  ROUND(SAFE_DIVIDE(
    COUNT(DISTINCT CASE WHEN has_purchase > 0 THEN user_pseudo_id END),
    COUNT(DISTINCT CASE WHEN has_session_start > 0 THEN user_pseudo_id END)
  ) * 100, 2) AS overall_conversion_rate_pct
FROM funnel_users;

Your funnel is built. You can now see exactly how many users make it through each stage of the purchase journey. Next up, you will add session-level granularity with window functions to understand individual session behaviour.

Add Session-Level Funnel Analysis with Window Functions

Your user-level funnel from the previous step shows how many unique users reached each stage. This is valuable for overall conversion health, but it hides an important detail.

A single user might visit the store ten times but only purchase once. Session-level analysis reveals which individual visits convert and where sessions typically abandon, giving product teams a much sharper signal for optimization.

In this step, get ready to:

• Extract ga_session_id from nested event parameters to identify individual sessions.
• Calculate step-over-step conversion rates using SAFE_DIVIDE.
• Identify the funnel step with the highest session drop-off.

Build the session-level funnel query

Your previous query in funnel_analysis.sql counted users who ever triggered each event across all their visits. Now you'll write a query that groups events by individual session, so you can see how many sessions (not just users) reach each funnel stage.

• Click the + button next to your open query tabs in the BigQuery editor to open a new query tab.
• Paste the following query into the new editor:

WITH session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name,
    event_timestamp
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('session_start', 'page_view', 'view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),

session_funnel AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS reached_session_start,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) AS reached_page_view,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS reached_view_item,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS reached_add_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS reached_begin_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS reached_purchase
  FROM session_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
),

funnel_counts AS (
  SELECT
    SUM(reached_session_start) AS sessions_at_start,
    SUM(reached_page_view) AS sessions_at_page_view,
    SUM(reached_view_item) AS sessions_at_view_item,
    SUM(reached_add_to_cart) AS sessions_at_add_to_cart,
    SUM(reached_begin_checkout) AS sessions_at_begin_checkout,
    SUM(reached_purchase) AS sessions_at_purchase
  FROM session_funnel
)

SELECT
  sessions_at_start,
  sessions_at_page_view,
  ROUND(SAFE_DIVIDE(sessions_at_page_view, sessions_at_start) * 100, 2) AS pct_start_to_page_view,
  sessions_at_view_item,
  ROUND(SAFE_DIVIDE(sessions_at_view_item, sessions_at_page_view) * 100, 2) AS pct_page_view_to_view_item,
  sessions_at_add_to_cart,
  ROUND(SAFE_DIVIDE(sessions_at_add_to_cart, sessions_at_view_item) * 100, 2) AS pct_view_item_to_add_to_cart,
  sessions_at_begin_checkout,
  ROUND(SAFE_DIVIDE(sessions_at_begin_checkout, sessions_at_add_to_cart) * 100, 2) AS pct_add_to_cart_to_checkout,
  sessions_at_purchase,
  ROUND(SAFE_DIVIDE(sessions_at_purchase, sessions_at_begin_checkout) * 100, 2) AS pct_checkout_to_purchase,
  ROUND(SAFE_DIVIDE(sessions_at_purchase, sessions_at_start) * 100, 2) AS overall_session_conversion_pct
FROM funnel_counts;

What does this query do?

This query chains three CTEs together, each building on the previous one:

• session_events uses the UNNEST subquery pattern to extract ga_session_id from the nested event_params array. It also filters to only the six funnel events.
• session_funnel groups by both user_pseudo_id and ga_session_id, creating one row per session. The MAX(CASE WHEN ...) pattern flags whether each session reached each stage (1 or 0).
• funnel_counts sums those flags to get total session counts at each stage.
• The final SELECT uses SAFE_DIVIDE to calculate step-over-step conversion rates. Each percentage divides sessions at stage N by sessions at stage N-1.

Why SAFE_DIVIDE instead of regular division?

SAFE_DIVIDE returns NULL instead of throwing an error when the denominator is zero. This protects your query from crashing if a funnel stage happens to have zero sessions.

• Click Run to execute the query.
• You should see a single row of results with session counts at each stage interleaved with percentage columns.

Seeing an error about ga_session_id?

• Make sure the UNNEST subquery uses value.int_value (not value.string_value). The ga_session_id parameter is stored as an integer.
• Check that WHERE ga_session_id IS NOT NULL appears in the session_funnel CTE. Some events have a NULL session ID and need to be filtered out.
• Verify that each CTE is separated by a comma after the closing parenthesis, except the last one before the final SELECT.

Help me debug my session-level funnel query in BigQuery.

Identify the highest drop-off step

Your results now show conversion rates between each pair of adjacent funnel stages. The lowest percentage column reveals where the biggest drop-off happens.

• Look at the five pct_ columns in your results row.
• Find the column with the lowest value. This is the funnel step where the most sessions abandon.
• Note the overall_session_conversion_pct at the end. This is the share of sessions that complete a purchase out of all sessions that started.

What does the drop-off tell a product team?

If the biggest drop-off is between page_view and view_item, it suggests users browse but never engage with a specific product. A product team might improve homepage merchandising or category navigation.

If the biggest drop-off is between add_to_cart and begin_checkout, it points to friction in the checkout flow. The team might simplify the checkout page or add guest checkout.

• Save this query as session_funnel by clicking Save then Save query above the editor.

✔️ Awesome, I've got everything!

Great. Double check you've saved your query before moving on.

ⓧ I'd like to double check the full code

Here is the complete session_funnel.sql query:

WITH session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name,
    event_timestamp
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('session_start', 'page_view', 'view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),

session_funnel AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS reached_session_start,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) AS reached_page_view,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS reached_view_item,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS reached_add_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS reached_begin_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS reached_purchase
  FROM session_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
),

funnel_counts AS (
  SELECT
    SUM(reached_session_start) AS sessions_at_start,
    SUM(reached_page_view) AS sessions_at_page_view,
    SUM(reached_view_item) AS sessions_at_view_item,
    SUM(reached_add_to_cart) AS sessions_at_add_to_cart,
    SUM(reached_begin_checkout) AS sessions_at_begin_checkout,
    SUM(reached_purchase) AS sessions_at_purchase
  FROM session_funnel
)

SELECT
  sessions_at_start,
  sessions_at_page_view,
  ROUND(SAFE_DIVIDE(sessions_at_page_view, sessions_at_start) * 100, 2) AS pct_start_to_page_view,
  sessions_at_view_item,
  ROUND(SAFE_DIVIDE(sessions_at_view_item, sessions_at_page_view) * 100, 2) AS pct_page_view_to_view_item,
  sessions_at_add_to_cart,
  ROUND(SAFE_DIVIDE(sessions_at_add_to_cart, sessions_at_view_item) * 100, 2) AS pct_view_item_to_add_to_cart,
  sessions_at_begin_checkout,
  ROUND(SAFE_DIVIDE(sessions_at_begin_checkout, sessions_at_add_to_cart) * 100, 2) AS pct_add_to_cart_to_checkout,
  sessions_at_purchase,
  ROUND(SAFE_DIVIDE(sessions_at_purchase, sessions_at_begin_checkout) * 100, 2) AS pct_checkout_to_purchase,
  ROUND(SAFE_DIVIDE(sessions_at_purchase, sessions_at_start) * 100, 2) AS overall_session_conversion_pct
FROM funnel_counts;

You now have a session-level view of funnel performance that reveals exactly where visits abandon. Next up, you'll export these results into a format ready for Power BI dashboarding.

Export Funnel Results for Dashboard Use

You have a session-level funnel that reveals exactly where users drop off. The next challenge is turning those results into a format that a dashboard tool like Power BI can ingest.

In this step, you will reshape the funnel data into a clean, row-per-stage format using window functions. Then you will save it to a destination table and export it as a CSV file ready for visualization.

In this step, get ready to:

• Write an export-ready funnel query using FIRST_VALUE and LAG window functions.
• Save query results to a BigQuery destination table.
• Download the results as a CSV file and document your KPI definitions.

Write the export-ready funnel query

Dashboard tools expect one row per funnel stage, not a single wide row of counts. You need columns like funnel_step, session_count, user_count, and conversion percentages.

Window functions let you calculate these percentages without self-joins. FIRST_VALUE grabs the top-of-funnel count for "percent of total" and LAG grabs the previous step's count for step-over-step conversion.

• Open a new query tab in the BigQuery query editor.
• Paste the following query to produce the export-ready funnel summary:

WITH session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('session_start', 'page_view', 'view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),

session_funnel AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS reached_session_start,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) AS reached_page_view,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS reached_view_item,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS reached_add_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS reached_begin_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS reached_purchase
  FROM session_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
)

SELECT
  funnel_step,
  session_count,
  user_count,
  ROUND(SAFE_DIVIDE(session_count, FIRST_VALUE(session_count) OVER (ORDER BY step_order)) * 100, 2) AS pct_of_total,
  ROUND(SAFE_DIVIDE(session_count, LAG(session_count) OVER (ORDER BY step_order)) * 100, 2) AS step_conversion_pct
FROM (
  SELECT 1 AS step_order, 'session_start' AS funnel_step, SUM(reached_session_start) AS session_count, COUNT(DISTINCT CASE WHEN reached_session_start = 1 THEN user_pseudo_id END) AS user_count FROM session_funnel
  UNION ALL
  SELECT 2, 'page_view', SUM(reached_page_view), COUNT(DISTINCT CASE WHEN reached_page_view = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 3, 'view_item', SUM(reached_view_item), COUNT(DISTINCT CASE WHEN reached_view_item = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 4, 'add_to_cart', SUM(reached_add_to_cart), COUNT(DISTINCT CASE WHEN reached_add_to_cart = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 5, 'begin_checkout', SUM(reached_begin_checkout), COUNT(DISTINCT CASE WHEN reached_begin_checkout = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 6, 'purchase', SUM(reached_purchase), COUNT(DISTINCT CASE WHEN reached_purchase = 1 THEN user_pseudo_id END) FROM session_funnel
)
ORDER BY step_order;

What does this query do?

This query reuses the same CTE pattern from your session funnel, but reshapes the output into a vertical format with one row per funnel stage.

• The inner subquery uses UNION ALL to stack each funnel stage as its own row, assigning a step_order number for sorting.
• FIRST_VALUE(session_count) OVER (ORDER BY step_order) always returns the session_start count, giving you "percent of total funnel" for each row.
• LAG(session_count) OVER (ORDER BY step_order) returns the previous step's count, so you can calculate step-over-step conversion.
• SAFE_DIVIDE prevents division-by-zero errors if any step has zero sessions.

• Click **Run** to execute the query.
• You should see 6 rows in the results, one for each funnel stage from session_start to purchase, with columns for funnel_step, session_count, user_count, pct_of_total, and step_conversion_pct.

Seeing NULL in step_conversion_pct for session_start?

That is expected. LAG looks at the previous row, and there is no row before session_start. A NULL in the first row's step_conversion_pct column is correct behaviour.

If you see errors instead of results, check that every UNION ALL subquery ends with FROM session_funnel and that you have no trailing commas after the last CTE.

Help me debug my funnel export query in BigQuery.

✔️ Awesome, I've got everything!

Great. Make sure you save this query for reference later.

ⓧ I'd like to double check the full code

Here is the complete funnel_export.sql query:

WITH session_events AS (
  SELECT
    user_pseudo_id,
    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
    event_name
  FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20201101' AND '20210131'
    AND event_name IN ('session_start', 'page_view', 'view_item', 'add_to_cart', 'begin_checkout', 'purchase')
),

session_funnel AS (
  SELECT
    user_pseudo_id,
    ga_session_id,
    MAX(CASE WHEN event_name = 'session_start' THEN 1 ELSE 0 END) AS reached_session_start,
    MAX(CASE WHEN event_name = 'page_view' THEN 1 ELSE 0 END) AS reached_page_view,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 ELSE 0 END) AS reached_view_item,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS reached_add_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS reached_begin_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS reached_purchase
  FROM session_events
  WHERE ga_session_id IS NOT NULL
  GROUP BY user_pseudo_id, ga_session_id
)

SELECT
  funnel_step,
  session_count,
  user_count,
  ROUND(SAFE_DIVIDE(session_count, FIRST_VALUE(session_count) OVER (ORDER BY step_order)) * 100, 2) AS pct_of_total,
  ROUND(SAFE_DIVIDE(session_count, LAG(session_count) OVER (ORDER BY step_order)) * 100, 2) AS step_conversion_pct
FROM (
  SELECT 1 AS step_order, 'session_start' AS funnel_step, SUM(reached_session_start) AS session_count, COUNT(DISTINCT CASE WHEN reached_session_start = 1 THEN user_pseudo_id END) AS user_count FROM session_funnel
  UNION ALL
  SELECT 2, 'page_view', SUM(reached_page_view), COUNT(DISTINCT CASE WHEN reached_page_view = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 3, 'view_item', SUM(reached_view_item), COUNT(DISTINCT CASE WHEN reached_view_item = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 4, 'add_to_cart', SUM(reached_add_to_cart), COUNT(DISTINCT CASE WHEN reached_add_to_cart = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 5, 'begin_checkout', SUM(reached_begin_checkout), COUNT(DISTINCT CASE WHEN reached_begin_checkout = 1 THEN user_pseudo_id END) FROM session_funnel
  UNION ALL
  SELECT 6, 'purchase', SUM(reached_purchase), COUNT(DISTINCT CASE WHEN reached_purchase = 1 THEN user_pseudo_id END) FROM session_funnel
)
ORDER BY step_order;

Save results to a BigQuery destination table

Saving query results to a permanent table keeps your funnel data available without re-running the query. This is optional, but useful if you want to share the results with teammates or schedule refreshes later.

• In the query editor, click **More** and then select **Query settings**.
• Check the box for **Set a destination table for query results**.
• In the **Destination** section, select a dataset in your project (or create a new one called funnel_analysis).
• Enter funnel_export as the **Table Id**.
• Under **Destination table write preference**, select **Overwrite table**.
• Click **Save** to update the query settings.
• Click **Run** to execute the query. The results are now written to your destination table.

How do I verify the table was created?

In the **Explorer** pane on the left, expand your project, then expand the dataset you chose. You should see a table called funnel_export listed under it. Click the table name to view its schema and preview the data.

This substep is optional

If you do not want to create a destination table, you can skip this substep. The CSV download in the next substep works directly from cached query results. Creating a destination table uses a small amount of your 10 GiB free storage quota.

Export results as CSV and document your KPIs

A CSV file is the simplest format for importing into Power BI, Excel, or any other dashboard tool. Your funnel summary is only 6 rows, so the console download is the fastest path.

• Make sure you have query results visible in the **Results** tab (re-run the query if needed).
• Click **Save results** above the results table.
• Select **CSV (local file)** from the dropdown menu.
• The file downloads to your browser's default download location.

What columns are in the CSV?

Your downloaded CSV contains 5 columns: funnel_step, session_count, user_count, pct_of_total, and step_conversion_pct. These map directly to the dimensions and measures you will need in Power BI.

Finally, document your KPI definitions. Anyone reading your dashboard needs to know exactly what each metric means.

• Record the following definitions for your funnel analysis:

• Session: a unique combination of user_pseudo_id and ga_session_id extracted from event_params. One user can have multiple sessions.
• Conversion: a session that fired the purchase event at least once.
• Drop-off: the percentage of sessions lost between two consecutive funnel steps, calculated as 100 minus step_conversion_pct.
• Overall conversion rate: sessions reaching purchase divided by sessions at session_start, shown in pct_of_total for the last row.

Why document KPI definitions?

When you hand a dashboard to a stakeholder, the first question is always "what counts as X?" Having written definitions prevents misinterpretation. It also helps you stay consistent if you re-run the analysis on a different date range later.

Save results button not appearing?

The **Save results** button only appears after a query has finished running and results are displayed in the **Results** tab. If you navigated away or the cached results expired, click **Run** again to regenerate them.

Help me download my BigQuery query results as CSV.

Your funnel data is now structured, saved, and exported. In the Secret Mission, you will slice this same analysis by device category to uncover where mobile users struggle most.

Secret mission

Segment the Funnel by Device Category

Your funnel analysis treats all users the same. But do mobile shoppers drop off at different points than desktop users? Segment your funnel by device category to find out where the mobile experience breaks down.

Clean Up Your Resources

Clean Up Your Resources

This project runs entirely within BigQuery's free tier, so there are no ongoing costs from your queries or saved SQL. If you saved query results to a destination table in Step 5, it uses a small amount of your 10 GiB free storage allocation.

Decide whether to keep your resources available, or delete the destination table to reclaim that storage.

Resources you used:

• BigQuery destination table (created in Step 5 when you saved query results).
• Saved SQL queries in the BigQuery console (no storage cost).
• Downloaded CSV file on your local machine.

Keep everything running

No action needed. Choose this if you plan to continue working with BigQuery or want to reference your queries later.

• Your saved queries cost nothing to keep. They are just stored SQL text.
• The public GA4 dataset is free to query within the 1 TiB/month limit.
• Your destination table uses a tiny fraction of your 10 GiB free storage. It will not incur charges unless you exceed that total allowance across all datasets.

Pause - I'll come back to this later

There is nothing to pause for this project. BigQuery has no running processes to stop. Your queries and destination table sit idle at no cost within the free tier.

• Leave everything as-is. You can return and re-run any saved query at any time.

Delete - I don't want to use this again

Remove the destination table you created. Your saved queries and the public dataset require no cleanup.

• In the BigQuery console, expand your project in the left-hand **Explorer** pane.
• Click **Datasets**, then select the dataset that contains your destination table.
• Click **Overview**, then **Tables**, and select your destination table.
• In the details pane, click **Delete**.
• Type delete in the confirmation dialog, then click **Delete** to confirm.
• Delete the CSV file you downloaded to your local machine.

Nice Work!

Nice Work!

Nice work! You just built a complete ecommerce conversion funnel analysis using real GA4 event data in BigQuery.

You've learned how to:

• Query real GA4 ecommerce data using UNNEST to extract values from BigQuery's nested and repeated fields.
• Build a full conversion funnel across 6 stages using CTEs and COUNTIF for conditional aggregation.
• Apply window functions for session-level analysis and export structured funnel data ready for Power BI dashboarding.
• Secret Mission: Segmented the funnel by device category and identified where mobile users drop off compared to desktop.

Ready to quiz yourself?