What a SignalQL funnel query counts, and what it doesn't
A SignalQL funnel query counts distinct users per step inside the time window and divides the last step by the first. Here is the exact SQL it compiles to, how to read step_1, step_2 and conversion_rate, and what to do when order matters.

A SignalQL funnel query answers a narrower question than most people expect. In the v0.1 reference Postgres compiler, FUNNEL "signup" THEN "activated" counts the distinct users who fired each event somewhere inside the time window, step by step, and divides the last step by the first. It does not check that the steps happened in order, or that the people in step 2 are the people from step 1. That makes it a fast, honest read of activity in a window. It is not a cohort conversion rate, and reading it as one will mislead you.
This guide walks through one real question, the query, the exact SQL it compiles to, and how to read the three numbers it returns.
The question
"Of the people who signed up in the last 28 days, how many activated?"
It is the most common product analytics question there is, and the canonical examples library answers it with a two-step funnel. Before running it, it helps to know which version of that question the query actually answers.
Map the data to the portable model
SignalQL compiles against a small logical model, described in the portable data model guide. A funnel needs only the events table, and only three of its columns:
event_name: the canonical event identifier, heresignupandactivated.user_id: the stable user key when known. It is nullable.timestamp: event time in UTC.
If your warehouse names these differently, an adapter maps them; the query text does not change.
The query
FUNNEL "signup" THEN "activated" FROM events DURING LAST 28 DAYS
This is the funnel example from the library and from fixtures/query-fixtures.json in the public repository. The v0.1 specification requires at least two steps plus FROM and DURING, and the reference compiler accepts between 2 and 6 steps. DURING BETWEEN is valid grammar, but funnel compilation with it is not implemented in the v0.1 reference compiler, so use LAST N DAYS.
The SQL it compiles to
Save the query to funnel.sqlq and compile it with the published CLI, as the first-query guide shows:
npx @signalql/cli compile ./funnel.sqlq --json-params
From the funnel branch of the reference Postgres compiler (@signalql/compiler 0.1.0, the current published version), the generated SQL is:
SELECT COUNT(DISTINCT CASE WHEN event_name = $2 THEN user_id END)::bigint AS step_1, COUNT(DISTINCT CASE WHEN event_name = $3 THEN user_id END)::bigint AS step_2,
CASE WHEN COUNT(DISTINCT CASE WHEN event_name = $2 THEN user_id END) > 0
THEN COUNT(DISTINCT CASE WHEN event_name = $3 THEN user_id END)::float
/ NULLIF(COUNT(DISTINCT CASE WHEN event_name = $2 THEN user_id END)::float, 0)
ELSE NULL END AS conversion_rate
FROM events
WHERE events.timestamp >= now() - ($1::int * interval '1 day')
with the parameters:
[28, "signup", "activated"]
The window length is bound first, then each step name. Nothing from the query text is pasted into the SQL string, so the same plan is safe to run whatever the step names are.
How to read the result
The query returns one row with three columns.
step_1 is the number of distinct users with at least one signup event in the last 28 days.
step_2 is the number of distinct users with at least one activated event in the same 28 days. The SQL does not require those users to appear in step_1.
conversion_rate is step_2 divided by step_1, or NULL when nobody signed up in the window.
Read the SQL once more and four consequences follow:
- Order is not checked. A user who activated on day 3 and signed up on day 20 counts in both steps.
- Step 2 can include people from before the window. Someone who signed up 40 days ago and activated last week counts in
step_2but notstep_1. On a product with a long activation tail, this pushes the rate up. - The rate can exceed 1. If more users activated in the window than signed up in it,
conversion_rateis above 1.0. That follows directly from the definition. - Anonymous events drop out.
COUNT(DISTINCT … user_id)ignoresNULL, so events without auser_idare not counted in any step.
None of this makes the number wrong. It makes it a window measure: "how many people did each thing recently". For a steady product it tracks real conversion well. During a launch, a pricing change or a backfill of old users, it can drift far from it.
Check it against two counts
The library's retention starter uses two plain counts over the same window, and they make a useful sanity check:
COUNT users FROM events WHERE event_name = "signup" DURING LAST 28 DAYS
COUNT users FROM events WHERE event_name = "activated" DURING LAST 28 DAYS
The second one compiles to:
SELECT COUNT(DISTINCT src.user_id)::bigint AS count FROM events AS src WHERE src.event_name = $2 AND src.timestamp >= now() - ($1::int * interval '1 day')
with [28, "activated"]. It is the same distinct-user count over the same rows, so the two results should equal step_1 and step_2. If they do not, look at your adapter's column mapping before you look at your product.
When you need strict order
If the decision depends on "signed up, then activated", you need per-user sequencing, which the v0.1 funnel does not do. Until your implementation offers it, write that one by hand and keep the SignalQL funnel as the quick view. A plain Postgres version over the same portable columns:
WITH per_user AS (
SELECT user_id,
MIN(timestamp) FILTER (WHERE event_name = 'signup') AS first_signup,
MAX(timestamp) FILTER (WHERE event_name = 'activated') AS last_activation
FROM events
WHERE timestamp >= now() - interval '28 days'
AND user_id IS NOT NULL
GROUP BY user_id
)
SELECT COUNT(*) FILTER (WHERE first_signup IS NOT NULL) AS signed_up,
COUNT(*) FILTER (WHERE last_activation >= first_signup) AS activated_after_signup
FROM per_user;
A user counts as converted only if some activation came at or after their first signup in the window. Both counts now describe the same people.
Where this fits
This is the line SignalQL is built to keep: the query retrieves evidence with a fixed, documented meaning, and the interpretation ("is activation improving?") happens above it, in a notebook, a dashboard or an AI assistant. The useful habit is to compile before you trust. npx @signalql/cli compile shows the exact SQL and parameters for any query, and the Postgres local guide runs the same query against seeded sample data, so you can see what each step counts before the number reaches a slide. If an AI tool writes your queries, the repository's MCP server lets it compile them the same way. Start at signalql.org.