Click to Sign-up to Purchase Funnel per Campaign

Hard
294.

Click to Sign-up to Purchase Funnel per Campaign

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

ColumnTypeValues
event_idint
user_idint
campaign_idint
event_typeenumclick, signup, purchase
event_atdatetime

Primary key: event_id.

One row per tracked funnel event; a shopper can repeat any event.

Example

The rows must come back in the order shown.

FunnelEvent

event_iduser_idcampaign_idevent_typeevent_at
1110click2024-09-01 10:00:00
2110signup2024-09-01 10:05:00
3110purchase2024-09-02 18:00:00
4210click2024-09-01 11:00:00
5210click2024-09-01 11:20:00
6210signup2024-09-01 11:30:00
7310signup2024-09-01 09:00:00
8310click2024-09-01 12:00:00
9310purchase2024-09-01 12:10:00
10420click2024-09-03 08:00:00
1 more row

Output

campaign_idclickedsigned_uppurchasedclick_to_purchase_pct
1032133.33
201000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
event_idint
user_idint
campaign_idint
event_typeenum(click, signup, purchase)
event_atdatetime

Primary key: event_id.

One row per tracked funnel event; a shopper can repeat any event.

Examples

Example 1

FunnelEvent

event_iduser_idcampaign_idevent_typeevent_at
1110click2024-09-01 10:00:00
2110signup2024-09-01 10:05:00
3110purchase2024-09-02 18:00:00
4210click2024-09-01 11:00:00
5210click2024-09-01 11:20:00
6210signup2024-09-01 11:30:00
7310signup2024-09-01 09:00:00
8310click2024-09-01 12:00:00
9310purchase2024-09-01 12:10:00
10420click2024-09-03 08:00:00
11530purchase2024-09-03 09:00:00

Output

campaign_idclickedsigned_uppurchasedclick_to_purchase_pct
1032133.33
201000

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_id

Another 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 →