Discover Funnel Bottlenecks: Step-by-Step Analysis with BigQuery

If you’re running an e-commerce store, you’ve probably been there: people are visiting your site, browsing around, and then… poof, they’re gone without buying anything. Frustrating, right? That’s where funnel analysis comes in. It’s all about figuring out what’s going on at each step of the customer journey and spotting where they’re dropping off.

In this article, I’ll show you how I use BigQuery to break down and analyze the e-commerce funnel step by step. Let’s dive in.

What Is Funnel Analysis?

Okay, so here’s the deal: a funnel is just a fancy name for the journey your customers take on your site, from the moment they land to when (hopefully) they make a purchase.

It looks something like this:

1. Homepage Visits: They land on your site.
2. Product Page Views: They start browsing your products.
3. Add to Cart: They like something enough to add it to their cart.
4. Checkout: They decide to proceed to checkout.
5. Purchase: They complete the order. 🎉

The goal of funnel analysis is simple: figure out how many people make it from one step to the next, and where you’re losing them.

Analyzing Funnel Transitions with BigQuery

If you’re storing event data in BigQuery, you can run queries to track how users move through each step of the funnel. Let me show you how.

Step 1: Querying Funnel Steps

Here’s a simple query that shows how many users move from one step to the next:

WITH funnel_data AS (
  SELECT
    user_pseudo_id,
    event_name,
    MIN(event_timestamp) AS first_event_time
  FROM your_dataset
  WHERE event_name IN ('page_view', 'add_to_cart', 'begin_checkout', 'purchase')
  GROUP BY
    user_pseudo_id, event_name
),
step_transitions AS (
  SELECT
    user_pseudo_id,
    MAX(CASE WHEN event_name = 'page_view' THEN first_event_time END) AS homepage_view_time,
    MAX(CASE WHEN event_name = 'add_to_cart' THEN first_event_time END) AS add_to_cart_time,
    MAX(CASE WHEN event_name = 'begin_checkout' THEN first_event_time END) AS checkout_time,
    MAX(CASE WHEN event_name = 'purchase' THEN first_event_time END) AS purchase_time
  FROM funnel_data
  GROUP BY all
)
SELECT
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  COUNTIF(homepage_view_time IS NOT NULL) AS step_1_homepage,
  COUNTIF(add_to_cart_time IS NOT NULL AND homepage_view_time IS NOT NULL) AS step_2_to_cart,
  COUNTIF(checkout_time IS NOT NULL AND add_to_cart_time IS NOT NULL) AS step_3_to_checkout,
  COUNTIF(purchase_time IS NOT NULL AND checkout_time IS NOT NULL) AS step_4_to_purchase
FROM step_transitions;

Step 2: What This Query Does

1. Tracks the first time each user triggers key funnel events like page_view, add_to_cart, etc.
2. Counts how many users successfully transition from one step to the next.

The output will look something like this:

Interpreting the Results

From the table above, you can see:

• A 55% drop-off between browsing and adding to cart.
• A 40% drop-off between checkout and completing the purchase.

This tells you exactly where to focus your efforts. For example:

• Product Page → Add to Cart: Maybe your product descriptions or images aren’t convincing enough.
• Checkout → Purchase: Could be an issue with shipping costs, or maybe your checkout process is too complicated.

Optimizing Based on Funnel Insights

Once you know where the drop-offs are, it’s time to take action. Here are a few things I’ve tried (and they work):

1. Fix Product Page Issues

If people aren’t adding items to their cart:

• Use higher-quality product images.
• Write more detailed (and engaging!) descriptions.
• Highlight offers like free shipping or discounts.

2. Simplify Checkout

If users drop off during checkout:

• Remove unnecessary steps (nobody likes filling out 10 fields).
• Offer guest checkout—forcing account creation is a killer.
• Be upfront about shipping costs early on.

Tracking Over Time

You can also adjust the query to compare transitions over time—for example, before and after a design update:

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_MICROS(MIN(event_timestamp))) AS date,
  COUNT(DISTINCT user_pseudo_id) AS total_users,
  COUNTIF(add_to_cart_time IS NOT NULL) AS step_2_to_cart,
  COUNTIF(checkout_time IS NOT NULL) AS step_3_to_checkout,
  COUNTIF(purchase_time IS NOT NULL) AS step_4_to_purchase
FROM step_transitions
GROUP BY all
ORDER BY 1;

This way, you can see if your changes are actually making a difference.

In Conclusion 🥳

Funnel analysis doesn’t need to be overwhelming. With a few simple BigQuery queries, you can break down your user journey, pinpoint drop-offs, and start making improvements. Remember, even small tweaks—like better product images or a smoother checkout process—can make a huge difference in your conversion rates.

So, roll up your sleeves, give it a shot, and let’s turn those visitors into customers!

Thank you for your time; sharing is caring! 🌍