Common mistake

SQL Funnels: Why COUNT(*) Miscounts Each Stage

COUNT(*) counts rows, and a funnel's rows are pageviews or events. Anyone who reloads a page counts twice. Count distinct sessions or users at every stage.

The symptom

You build a funnel from the Website Analytics pageviews: how many visitors reached the home page, the features page, and checkout, and what share of home-page visitors made it to checkout. The numbers look reasonable, and the conversion rate comes out 34.6%. The real figure is 40.9%.

Why it happens

A pageview table has one row per view, not per visitor. Reload the home page, or come back to it with the back button, and the same session adds another row. count(*) counts every one of them: 26 rows for the home page, from only 22 sessions.

Repeat views pile up at the top of a funnel, where people browse, and thin out at checkout, where they don't. So the inflated top stage makes every rate below it look worse than it is.

The fix

Count the thing a funnel measures: count(distinct session_id) at every stage, including the denominator.

Wrong: count(*) counts pageviews

select path as stage,
       count(*) as visitors,
       round(100.0 * count(*) / (
         select count(*) from pageviews where path = '/'
       ), 1) as pct_of_home
from pageviews
where path in ('/', '/features', '/checkout')
group by path
order by visitors desc

Right: count distinct sessions

select path as stage,
       count(distinct session_id) as visitors,
       round(100.0 * count(distinct session_id) / (
         select count(distinct session_id) from pageviews where path = '/'
       ), 1) as pct_of_home
from pageviews
where path in ('/', '/features', '/checkout')
group by path
order by visitors desc

Checkout is 9 either way, because nobody here viewed it twice. The home page drops from 26 to 22 and the features page from 11 to 10, so the rate to checkout rises from 34.6% to 40.9%.

Sessions or users?

Pick the unit the question is about, and use it at every stage. A session funnel asks "how many visits got this far". A user funnel, count(distinct user_id), asks "how many people did", and counts someone who needed two visits once. Here many sessions have no user_id (they never signed in), so sessions are the unit that covers everyone.

Reaching a stage isn't the same as passing through the ones before

This funnel counts each stage on its own. Some sessions reach checkout without viewing the features page, so a stage can even be larger than the one before it. A strict funnel counts only sessions that reached every earlier stage as well. The conversion lesson and the funnel interview questions measure stage reach too, and say when you need the strict version.