Funnel conversion analysis

Funnel conversion analysis tracks what percentage of people make it through each step of a multi-stage process, so you can see exactly where the biggest drop-offs occur.

Try this lesson free →

About 5–10 minutes · no sign-up needed

Example

Funnel conversion

SELECT status, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM orders GROUP BY status ORDER BY stage_count DESC

Result:

status	stage_count	pct_of_total
cart	3	42.9
payment	2	28.6
address	1	14.3
confirmation	1	14.3

Step by step

Run the query without COUNT so you can see what the unfiltered values look like.

SELECT status, *, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM orders GROUP BY status ORDER BY stage_count DESC;

COUNT tells you how many rows match - perfect for sizing a table.

SELECT status, COUNT(*), ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM orders GROUP BY status ORDER BY stage_count DESC;

AS gives the COUNT result a friendly column name so the output reads cleanly.

SELECT status, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM orders GROUP BY status ORDER BY stage_count DESC;

Practice

Try each one in your head first, then open the answer.

1. On orders, build a funnel report: count rows per order_id value and show each stage's percent of the overall total.

SELECT order_id, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM orders GROUP BY order_id ORDER BY stage_count DESC

2. On store_sales, build a funnel report: count rows per sale_id value and show each stage's percent of the overall total.

SELECT sale_id, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM store_sales GROUP BY sale_id ORDER BY stage_count DESC

3. On products, build a funnel report: count rows per product_id value and show each stage's percent of the overall total.

SELECT product_id, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM products GROUP BY product_id ORDER BY stage_count DESC

4. On reviews, build a funnel report: count rows per review_id value and show each stage's percent of the overall total.

SELECT review_id, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM reviews GROUP BY review_id ORDER BY stage_count DESC

5. On employees, build a funnel report: count rows per employee_id value and show each stage's percent of the overall total.

SELECT employee_id, COUNT(*) AS stage_count, ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct_of_total FROM employees GROUP BY employee_id ORDER BY stage_count DESC

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed