E-commerce funnel analysis with GA4 data in BigQuery

2023-03-14 | Article | Insights

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 (
   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
 #--- replace with you own ga4 data

 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,
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:

For users who dropped out of the funnel:
  • A/B testing: By testing different A/B strategies of the checkout process, we can identify what works best and makes the checkout funnel smoother.
  • Set audiences: We could consider campaigns for sending reminder emails about the shopping cart to remind users to come back and close their purchases.
  • Anomaly detection: In case of significant drops in each funnel step, an analysis of detection potential tracking errors can be done. E.g. a specific browser/OS causing the main part of the drop.
For users who completed the funnel:
  • Purchase deep dive: As this stage is critical in the e-commerce funnel, it should be simple and straightforward as possible. By analysing metrics such as average needed time from checkout to purchase we can minimise and eliminate obstacles.

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!

Do you need more Info?