A funnel that only tells you the start and the end hides the one thing worth knowing: which step bleeds users. The GA4 export lets you measure drop-off between every stage in SQL, with no interface limits in the way. Here is how to build it.

The idea behind a SQL funnel

A funnel in SQL is a count of how many users or sessions reach each ordered step, then the ratio between consecutive steps. Unlike the exploration interface, a SQL funnel is unsampled, fully customizable, and reusable in a scheduled model. The core is deciding the steps and counting distinct entities at each one.

Count users at each step

The simplest funnel counts distinct users who fired each step event, using COUNTIF over a per-user rollup:

WITH per_user AS (
  SELECT
    user_pseudo_id,
    MAX(IF(event_name = 'view_item', 1, 0))      AS s1_view,
    MAX(IF(event_name = 'add_to_cart', 1, 0))    AS s2_cart,
    MAX(IF(event_name = 'begin_checkout', 1, 0)) AS s3_checkout,
    MAX(IF(event_name = 'purchase', 1, 0))       AS s4_purchase
  FROM `project.analytics_123456789.events_*`
  WHERE _TABLE_SUFFIX BETWEEN '20260101' AND '20260131'
  GROUP BY user_pseudo_id
)
SELECT
  SUM(s1_view)     AS viewed,
  SUM(s2_cart)     AS carted,
  SUM(s3_checkout) AS checkout,
  SUM(s4_purchase) AS purchased
FROM per_user

MAX(IF(...)) flags whether each user ever hit a step, and summing the flags counts distinct users per step without double counting anyone.

Turn counts into drop-off

The counts alone are not the insight; the step-to-step conversion is. Compute each stage's pass-through rate as a share of the previous stage:

step conversion = users at step N รท users at step N-1

A stage where that ratio collapses is your leak. A 70% view-to-cart rate followed by a 20% cart-to-checkout rate tells you the checkout entry, not the product page, is where to focus.

Ordered vs any-order funnels

The query above is an any-order funnel: it counts users who did each step at all, in any sequence. A stricter funnel requires the steps to happen in order and often within one session, which you build by comparing event timestamps per user or session. Choose based on the question:

  • Use any-order for a quick read on where interest falls off overall.
  • Use ordered, single-session for a precise checkout flow where sequence matters.
  • State which one you used, because the two can produce noticeably different rates.

Make it reusable

A funnel you rerun by hand each week is a chore, so promote it into a scheduled query or a dbt or Dataform model that writes daily funnel counts to a small table. Dashboards then read that table instead of scanning the raw export every time, which keeps the funnel both fast and cheap. Parameterize the step events so the same model serves signup, checkout, and onboarding funnels.

Segment to explain the leak

A single overall funnel tells you where users drop, not why. Break the same funnel down by device, channel, or new-versus-returning, and the cause often appears: a checkout that leaks only on mobile, or paid traffic that abandons at a rate organic never does. Add the dimension to the per-user rollup and compare the step rates across segments. Where sequence matters, enforce it by comparing timestamps within a session, so a user who bought before ever viewing the product is not miscounted as a clean pass-through.

Measure the time between steps

Drop-off is not only about how many but how long. Computing the median time between begin_checkout and purchase, from event_timestamp differences, surfaces friction a count misses: a stage users complete eventually but slowly is still costing you conversions to fatigue and doubt. Keep the step definitions in one place so every team measures the same funnel, because a signup funnel that means different events to different people produces numbers nobody can reconcile.

A funnel that shows only the start and the end hides the leak, and the export is what lets you measure every step in between. Flag each step per user, turn the counts into step-to-step conversion, choose any-order or strict ordering on purpose, and schedule it so it stays fresh. Do that and the stage quietly losing your users stops hiding and becomes the number you fix next.

Want a stronger data analyst role or a raise? Grab the FREE Product Analyst Playbook and get the exact roadmap to your next offer.