Funnel analytics without GA4: querying your own orders

Every funnel question I actually care about has the same shape. Of the people who entered this funnel, how many reached checkout, how many paid, and did the ones who dropped at step two look different from the ones who converted?

That is four clauses. It should be one query. In GA4 it is a project.

Why the shape fights the tool

GA4’s unit of analysis is an event, inside a session, belonging to a user it identified. That model is excellent for the question it was built for: what happened across a site, and where did the traffic come from.

A funnel is a different shape. It is an ordered sequence of steps that terminates in a captured payment, and the payment does not live in your analytics tool. It lives in your store.

So to ask whether people who abandoned at shipping came from a different campaign than people who paid, you need three things co-located: the step events, the order rows, and a stable key that joins them. GA4 hands you the first. The second is only there if you push it, which means purchase events carrying custom parameters (awkward to reconcile, and effectively impossible to keep honest across refunds) or a BigQuery export and a warehouse to land it in. The third is where it usually collapses, because GA4’s identity is GA4’s, the order’s identity is your store’s, and welding them together is a custom dimension you must remember to set on every page and hope survives consent mode.

I want to be fair, because this is where funnel-tool marketing normally starts lying. GA4 is better than anything I am about to describe at a whole category of work: cross-site behaviour, channel modelling, audience building, and any comparison across properties you do not control. If the question is “which campaign deserves more budget”, GA4 plus the ad platforms are the right tools and a table in your own Postgres will not replace them.

My claim is narrower. For the step-leak question specifically, owning both sides of the join beats having a nicer analytics UI.

One table, one join

Autonnel writes every funnel behaviour event into a single Postgres table, user_activity_events. The schema comment on the model states the design directly: it is the “Single append-only event source for all funnel analytics”, and dashboards, stats, diagnostics and attribution “query this table directly (joined with orders).”

The row is deliberately thin:

visitorId, sessionId, funnelId, pageId, stepId,
kind, url, referrer, metadata, occurredAt

kind is the event name. Event-specific detail goes in metadata as JSON, which is why the column list does not grow every time a new event is added.

The known catalogue is 17 kinds, defined in one array in the analytics domain module: page_view, add_to_cart, remove_from_cart, coupon_applied, coupon_removed, checkout_view, shipping_submitted, payment_method_selected, payment_button_click, payment_submit, payment_success, payment_error, upsell_view, upsell_accept, upsell_decline, page_leave, js_error.

Seventeen is the built-in catalogue, not a ceiling. kind is deliberately an open set: the ingest code has a comment saying so, and an unknown kind is accepted rather than rejected. If your funnel has a step worth naming that is not in the list, emit it and it lands in the same table with the same columns, queryable by the same joins. Seventeen is simply the number I can point at in a file, which is the number I would rather quote.

The other half is orders. The relevant columns are status, capturedTotal, and attribution, a JSON blob that carries funnelId and visitorId. That is the entire trick. The same visitor identifier the beacon wrote on the landing page gets written into the order at capture. No stitching, no export, no shared key negotiated between two vendors.

The query

This is the shape autonnel actually runs to compute entering and converting visitors for one funnel and one time window:

WITH entering AS (
  SELECT DISTINCT e."visitorId"
  FROM "user_activity_events" e
  WHERE e."tenantId" = $1
    AND e."funnelId" = $2
    AND e."kind" = 'page_view'
    AND e."occurredAt" >= $3
    AND e."occurredAt" <= $4
),
funnel_orders AS (
  SELECT DISTINCT o."attribution"->>'visitorId' AS "visitorId"
  FROM "orders" o
  WHERE o."tenantId" = $1
    AND o."attribution"->>'funnelId' = $2
    AND o."status"::text = ANY($5)
)
SELECT (SELECT COUNT(*) FROM entering) AS entering,
       (SELECT COUNT(*) FROM entering e
          JOIN funnel_orders fo ON fo."visitorId" = e."visitorId") AS converting;

Two details worth stealing even if you never run autonnel.

The converting count intersects with entering rather than counting paid orders outright. The code comment explains why: tying conversions to the entering set keeps conversion rate at or below 100%. Count orders independently and you will eventually publish a dashboard showing 112% conversion, because someone landed last week and paid today, and then you get to explain that to a client.

And the whole thing is one round trip against two tables in the same database. There is no ETL step where the numbers can drift, and no window in which events and orders disagree because one pipeline is behind.

Once you have that skeleton, the cohort question is a small edit rather than a new system. Swap page_view for shipping_submitted, turn the inner join into a LEFT JOIN ... WHERE fo."visitorId" IS NULL, and you have the exact set of visitor IDs that submitted shipping and never paid. Join that set back against the earliest page_view row per visitor and you have their landing URL and referrer, which is where campaign and channel come from. That second query is one you write yourself, not something that ships, but it is a twenty-minute exercise rather than a warehouse project, and that difference is the entire argument of this post.

If you would rather not write SQL

The same numbers come out of the read API with a Bearer key:

curl -H "Authorization: Bearer YOUR_KEY" \
  https://your-shop.com/api/v1.1/stats/funnels/clxyz789

All stats metrics there are counted by unique user, consistent with the query above. The MCP server exposes the equivalent as get_stats, so an agent can ask the same question without you writing the join at all. Details in the external endpoints reference and the MCP docs.

Where DuckDB comes in, precisely

Analytical scans over a growing append-only table are exactly the workload a column store is good at, which is why DuckDB is in this stack. It is worth being precise about how, because it is not a separate service and it is not a dependency in the application.

It arrives as pg_duckdb, a Postgres extension, and it is opt-in: you swap the db service image in docker-compose.yml for a Postgres build that carries the extension, as documented in the installation guide. With it, aggregations over accumulated page-visit rows get noticeably faster. Without it, everything still works on plain Postgres and the same queries just slow down as the table grows.

Two consequences worth knowing before you plan around it. No widely available managed Postgres provider ships pg_duckdb, so this is a lever you can pull on infrastructure you control and generally cannot pull on a hosted database. And it is a performance choice rather than a capability one: the queries in this post are ordinary SQL either way, so nothing about your analysis changes if you never turn it on.

Plain Postgres with the right indexes carries a small store comfortably. Reach for the extension when the scans start to hurt, not before.

What this costs you

Four limits, stated plainly, because a post that only lists advantages is an ad.

The visitor identifier is a first-party cookie. It is read from the URL, then a server-set value, then the cookie, and generated if none exist. Cross-device journeys break. Someone who clears cookies becomes a new person. The A/B testing docs already say the same thing about sticky assignment. This is behavioural analytics, not identity resolution, and treating it as the latter will produce confident wrong answers.

The table is append-only with no rollup. Three indexes cover the windowed queries, keyed on tenant with occurredAt, visitorId and funnelId. They do their job. But nothing prunes and nothing pre-aggregates, so a funnel buying serious traffic accumulates rows indefinitely and you own that operational problem.

Event metadata is budgeted, and over-budget metadata is silently dropped. The ingest caps metadata at 4096 bytes, 32 keys and 4 levels of nesting. Bust any of those and the metadata is discarded while the event itself is kept. That is the right call for a public beacon (one request must not be able to buy unbounded storage) but it means a custom event with a fat payload can quietly lose its payload.

Analytics stops at payment capture, on purpose. The funnel event enum carries a comment marking it a hard boundary: adding any post-capture kind is forbidden. Refunds, shipping and delivery belong to the order state machine, not the event stream. So revenue in these queries is captured revenue, not revenue net of refunds, and if you need the second number you compute it from orders.

Why it works

None of this is clever engineering. The SQL is ordinary. The advantage is entirely structural: the events and the orders are in the same database, written by the same system, sharing a key that was never negotiated between two vendors. Every hard part of funnel analytics is a consequence of those two facts not being true, and no amount of dashboard polish fixes it from the outside.

That is also the honest reason to care about where your funnel builder keeps its data. A hosted tool that owns your checkout but exports your analytics has made this join someone else’s product. If you want to see how that plays out against the incumbents, the ClickFunnels comparison covers the data-ownership side specifically, and pricing covers what running it yourself costs.

Use GA4 for acquisition. Use your own database for the funnel.