Level 4 · A conversion funnel by device

A funnel follows people through a sequence of steps and counts how many survive each one. Product teams use it to find the step that loses the most people, because that is where a fix pays off.

Stages as conditional counts

When every stage is a condition on the same row, a funnel is one GROUP BY with a count per stage:

SELECT
  device,
  COUNT(*)                                  AS sessions,
  COUNT(*) FILTER (WHERE pages_viewed >= 3) AS engaged_sessions,
  COUNT(*) FILTER (WHERE converted)         AS converted_sessions
FROM web_sessions
GROUP BY device;

FILTER (WHERE ...) counts only the rows that match. It works in DuckDB and Postgres; SUM(CASE WHEN pages_viewed >= 3 THEN 1 ELSE 0 END) does the same everywhere.

Check that the stages nest

A funnel only makes sense if each stage is a subset of the one before. At Harbor & Pine every converted session viewed at least 3 pages, which you can confirm with COUNT(*) FILTER (WHERE converted AND pages_viewed < 3): it returns 0. If it did not, a “step rate” could exceed 100% and the funnel would be lying.

Worked example: 2025 by device

device	sessions	engaged	converted	engaged %	engaged → converted %	overall %
mobile	9,407	4,195	237	44.6	5.6	2.52
desktop	5,252	2,311	217	44.0	9.4	4.13
tablet	1,566	707	46	45.1	6.5	2.94

The overall rate alone says “mobile converts worse”. The steps say where: mobile visitors engage as often as desktop visitors, then fall off at the last step. That points the product team at checkout on mobile, not at the landing pages.

Two kinds of rate

• Step conversion: this stage divided by the previous stage. It locates the leak.
• Overall conversion: the last stage divided by the first. It is the headline number.

Name which one you report. “Conversion is 9.4%” and “conversion is 4.13%” are both true for desktop.

Common mistakes

• Integer division. In Postgres and SQL Server, 237 / 9407 is 0. Multiply by 100.0 first.
• Counting the wrong unit. These are session rates. A customer-level funnel counts distinct customers per stage and gives different numbers.
• Stages that do not nest, as described above.

In SQL Server

There is no FILTER clause and converted would be a BIT, so use CASE:

SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) AS converted_sessions,
ROUND(100.0 * SUM(CASE WHEN converted = 1 THEN 1 ELSE 0 END) / COUNT(*), 2)
  AS overall_conversion_pct  -- 100.0, not 100