In this article, we focus on e-commerce funnel reports in BigQuery. This report provides insights into the different stages of the e-commerce funnel. How users move through the funnel, how far they go, and where they drop off. Based on these insights, we can identify potential stages of the funnel that can be optimised to push users further down and ideally increase conversion rates.

To obtain an e-commerce funnel visualisation like the example below, the following metrics are required in the query.

We start with the users as the report is on a user level and then in the case of having the above e-commerce events, we would replace it with that event's timestamp. Since a user can have multiple e-commerce events we would use the MAX function for the time stamp event to identify only one (in this case, the last one) of the e-commerce events. In the last part of the query we use the COUNT function to count all unique users, unique users who had viewed an item(s), added product(s) to the cart, and users who completed purchase(s).

WITH funnel AS (
 SELECT
   user_pseudo_id,
   MAX(CASE WHEN event_name = 'view_item' THEN event_timestamp END) AS view_item_time,
   MAX(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS add_to_cart_time,
   MAX(CASE WHEN event_name = 'begin_checkout' THEN event_timestamp END) AS begin_checkout_time,
   MAX(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS purchase_time
 FROM
 #--- replace with you own ga4 data
   `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*`
 GROUP BY
   user_pseudo_id
)


SELECT
 COUNT(DISTINCT user_pseudo_id) AS users,
 COUNT(DISTINCT CASE WHEN view_item_time IS NOT NULL THEN user_pseudo_id END) AS view_item_users,
 COUNT(DISTINCT CASE WHEN add_to_cart_time IS NOT NULL THEN user_pseudo_id END) AS add_to_cart_users,
 COUNT(DISTINCT CASE WHEN begin_checkout_time IS NOT NULL THEN user_pseudo_id END) AS checkout_users,
 COUNT(DISTINCT CASE WHEN purchase_time IS NOT NULL THEN user_pseudo_id END) AS purchase_users,
FROM
 funnel

Query 1: Analysis of the E-commerce funnel

Using the Google merchandise data, the query would produce the following funnel step results:

By analysing the above funnel, we can identify where users are dropping off the most and take action to optimise it. It is also possible to drill down on some steps of the funnel to answer follow-up questions. Here are some tips on how to optimise each step of the funnel:

This query can be adapted to meet the needs of individual setups and include additional metrics or dimensions as needed. For example, It can be broken down by product category to provide a more detailed picture of user behaviour and improve e-commerce performance at a granular level.

Happy querying!