Calculating Conversion Rates at Each Step of the Ecommerce Funnel Using BigQuery SQL

Yesterday, we explored how to query GA4 ecommerce events in BigQuery to analyze your checkout funnel.
That post laid the foundation by showing how to pull funnel stage counts from GA4’s raw event data.

Today, we’re going deeper.
We’ll calculate conversion rates at each step of the ecommerce funnel, handle deduplication at both the user and session level, and share tips for interpreting drop-offs so you can find optimization opportunities.

1. Defining Funnel Stages in GA4

In GA4, an enhanced ecommerce funnel typically follows these events:

1. view_item – A user views a product
2. add_to_cart – A product is added to the cart
3. begin_checkout – Checkout process starts
4. purchase – Transaction is completed

📄 Reference: Full GA4 event and parameter definitions are available in the Google Analytics 4 Event Reference.

2. Querying Funnel Counts with Deduplication

User-level deduplication query

WITH funnel AS (
  SELECT
    user_pseudo_id,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 END) AS viewed_item,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 END) AS added_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 END) AS began_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 END) AS purchased
  FROM
    `my_project.my_dataset.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
  GROUP BY
    user_pseudo_id
)
SELECT
  COUNTIF(viewed_item = 1) AS stage_1_view_item,
  COUNTIF(added_to_cart = 1) AS stage_2_add_to_cart,
  COUNTIF(began_checkout = 1) AS stage_3_begin_checkout,
  COUNTIF(purchased = 1) AS stage_4_purchase
FROM
  funnel;

💡 What this query does:

• Uses MAX(CASE WHEN...) to mark if a user reached a stage.
• Groups by user_pseudo_id so each user is counted once per stage.
• Counts the number of users who completed each funnel step in the selected date range.

Session-level deduplication query

WITH funnel AS (
  SELECT
    user_pseudo_id,
    session_id,
    MAX(CASE WHEN event_name = 'view_item' THEN 1 END) AS viewed_item,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 END) AS added_to_cart,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 END) AS began_checkout,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 END) AS purchased
  FROM
    (
      SELECT
        user_pseudo_id,
        event_name,
        event_timestamp,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS session_id
      FROM
        `my_project.my_dataset.events_*`
      WHERE
        _TABLE_SUFFIX BETWEEN '20250101' AND '20250131'
    )
  GROUP BY
    user_pseudo_id, session_id
)
SELECT
  COUNTIF(viewed_item = 1) AS stage_1_view_item,
  COUNTIF(added_to_cart = 1) AS stage_2_add_to_cart,
  COUNTIF(began_checkout = 1) AS stage_3_begin_checkout,
  COUNTIF(purchased = 1) AS stage_4_purchase
FROM
  funnel;

💡 What this query does:

• Pulls ga_session_id from GA4’s event_params to define sessions.
• Groups by both user_pseudo_id and session_id to avoid counting multiple sessions per user as one.
• Ideal for understanding per-session behavior in your funnel.

3. Calculating Step-by-Step Conversion Rates

WITH counts AS (
  SELECT
    COUNTIF(viewed_item = 1) AS stage_1,
    COUNTIF(added_to_cart = 1) AS stage_2,
    COUNTIF(began_checkout = 1) AS stage_3,
    COUNTIF(purchased = 1) AS stage_4
  FROM funnel
)
SELECT
  stage_1,
  stage_2,
  ROUND(stage_2 * 100.0 / stage_1, 2) AS cr_view_to_cart,
  stage_3,
  ROUND(stage_3 * 100.0 / stage_2, 2) AS cr_cart_to_checkout,
  stage_4,
  ROUND(stage_4 * 100.0 / stage_3, 2) AS cr_checkout_to_purchase
FROM counts;

💡 What this query does:

• Counts users/sessions at each stage.
• Divides the count of a stage by the count of the previous stage to get a conversion rate.
• Uses ROUND(..., 2) to make results easier to read.

4. Interpreting Conversion Rate Drop-Offs

• High drop-off at view → add_to_cart: Product pages may need stronger CTAs, clearer pricing, or better images.
• High drop-off at cart → checkout: Review shipping costs, simplify checkout steps, and offer guest checkout.
• High drop-off at checkout → purchase: Improve payment options, remove distractions, and build trust (e.g., security badges).

TL;DR

• Yesterday’s post covered funnel counts — today’s deep dive shows you how to calculate conversion rates at each step.
• Deduplication ensures your data isn’t inflated by repeated actions.
• Step-by-step rates reveal where to focus optimization for maximum impact.