Click to Sign-up to Purchase Funnel per Campaign
Each funnel event is tagged with the campaign the shopper came from.
- Difficulty: Hard
- Topics: Conditional Logic, Joins, Subqueries
- Dialect: MySQL
- Problem: #294
Problem statement
Each funnel event is tagged with the campaign the shopper came from. For a campaign, a shopper clicked if they have a click event for it; signed up if they also have a sign-up event for it at or after their first click on it; purchased if they also have a purchase event for it at or after their first such sign-up. Events out of order (a purchase before any qualifying sign-up) don't count.
For every campaign with at least one click, return campaign_id, clicked, signed_up, purchased (numbers of distinct shoppers at each stage) and click_to_purchase_pct = 100 × purchased ÷ clicked rounded to 2 decimals. Order by campaign_id.
Tables
Table: FunnelEvent
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| campaign_id | int |
| event_type | enum(click, signup, purchase) |
| event_at | datetime |
Primary key: event_id.
One row per tracked funnel event; a shopper can repeat any event.
Examples
Example 1
FunnelEvent
| event_id | user_id | campaign_id | event_type | event_at |
|---|---|---|---|---|
| 1 | 1 | 10 | click | 2024-09-01 10:00:00 |
| 2 | 1 | 10 | signup | 2024-09-01 10:05:00 |
| 3 | 1 | 10 | purchase | 2024-09-02 18:00:00 |
| 4 | 2 | 10 | click | 2024-09-01 11:00:00 |
| 5 | 2 | 10 | click | 2024-09-01 11:20:00 |
| 6 | 2 | 10 | signup | 2024-09-01 11:30:00 |
| 7 | 3 | 10 | signup | 2024-09-01 09:00:00 |
| 8 | 3 | 10 | click | 2024-09-01 12:00:00 |
| 9 | 3 | 10 | purchase | 2024-09-01 12:10:00 |
| 10 | 4 | 20 | click | 2024-09-03 08:00:00 |
| 11 | 5 | 30 | purchase | 2024-09-03 09:00:00 |
Output
| campaign_id | clicked | signed_up | purchased | click_to_purchase_pct |
|---|---|---|---|---|
| 10 | 3 | 2 | 1 | 33.33 |
| 20 | 1 | 0 | 0 | 0 |
How to solve Click to Sign-up to Purchase Funnel per Campaign
A funnel is a chain of ordered conditions, so build it stage by stage per (campaign, shopper). clicks holds each shopper's first click. signups joins their sign-up events that happen at or after that click and keeps the earliest, the time the next stage must follow. buyers keeps shoppers with a purchase at or after that sign-up. Each CTE only narrows the one before, so the stages are nested by construction: shopper 3 above signed up before clicking, so that sign-up does not count and neither does the purchase after it.
The final query LEFT JOINs the later stages back onto the clickers, so COUNT(*) is the clickers and COUNT(s.user_id), COUNT(b.user_id) count only matched shoppers. Every row is one shopper (each CTE is unique per campaign and shopper), so nothing is double counted. The rate divides two exact counts and is rounded last; the denominator is never zero because only clicked campaigns appear.
The alternative replaces the joins with EXISTS flags per clicker. Its purchase test asks for some qualifying sign-up followed by a purchase — equivalent, because if a purchase follows any qualifying sign-up it also follows the earliest one. With an index on (campaign_id, user_id, event_type, event_at) every stage is a short range scan.
Reference solution (MySQL)
WITH clicks AS (
SELECT campaign_id, user_id, MIN(event_at) AS first_click
FROM FunnelEvent WHERE event_type = 'click'
GROUP BY campaign_id, user_id
), signups AS (
SELECT c.campaign_id, c.user_id, MIN(e.event_at) AS first_signup
FROM clicks c
JOIN FunnelEvent e ON e.campaign_id = c.campaign_id AND e.user_id = c.user_id
AND e.event_type = 'signup' AND e.event_at >= c.first_click
GROUP BY c.campaign_id, c.user_id
), buyers AS (
SELECT DISTINCT s.campaign_id, s.user_id
FROM signups s
JOIN FunnelEvent e ON e.campaign_id = s.campaign_id AND e.user_id = s.user_id
AND e.event_type = 'purchase' AND e.event_at >= s.first_signup
)
SELECT c.campaign_id, COUNT(*) AS clicked, COUNT(s.user_id) AS signed_up, COUNT(b.user_id) AS purchased,
ROUND(100 * COUNT(b.user_id) / COUNT(*), 2) AS click_to_purchase_pct
FROM clicks c
LEFT JOIN signups s ON s.campaign_id = c.campaign_id AND s.user_id = c.user_id
LEFT JOIN buyers b ON b.campaign_id = c.campaign_id AND b.user_id = c.user_id
GROUP BY c.campaign_id
ORDER BY c.campaign_idAnother way
WITH clicks AS (
SELECT campaign_id, user_id, MIN(event_at) AS first_click FROM FunnelEvent WHERE event_type = 'click' GROUP BY campaign_id, user_id
), staged AS (
SELECT c.campaign_id,
CASE WHEN EXISTS (SELECT 1 FROM FunnelEvent s WHERE s.campaign_id = c.campaign_id AND s.user_id = c.user_id
AND s.event_type = 'signup' AND s.event_at >= c.first_click) THEN 1 ELSE 0 END AS su,
CASE WHEN EXISTS (SELECT 1 FROM FunnelEvent s JOIN FunnelEvent p ON p.campaign_id = s.campaign_id AND p.user_id = s.user_id
WHERE s.campaign_id = c.campaign_id AND s.user_id = c.user_id AND s.event_type = 'signup'
AND s.event_at >= c.first_click AND p.event_type = 'purchase' AND p.event_at >= s.event_at) THEN 1 ELSE 0 END AS pu
FROM clicks c
)
SELECT campaign_id, COUNT(*) AS clicked, SUM(su) AS signed_up, SUM(pu) AS purchased,
ROUND(SUM(pu) * 100 / COUNT(*), 2) AS click_to_purchase_pct
FROM staged GROUP BY campaign_id ORDER BY campaign_id← Ad-Click Sessions per Shopper · Median Daily Spend of Each Channel →