Business Problem

A Hotjar customer — a mid-size SaaS company — is worried about a leaky signup funnel and wants to know exactly where users drop off between landing on the page and becoming a paying customer. Trace users through each funnel stage (Landing → Signup → Trial Start → Trial Completed → Paid Conversion), calculate the conversion rate at each step along with cumulative conversion from the top, and make the biggest drop-off point immediately obvious so the product team knows exactly which stage to redesign first.

Dataset & Schema

User funnel events with user_id, event_type (Landing, Signup, Trial-Start, Trial-Completed, Paid-Conversion), event_date, campaign_source.

CREATE TABLE funnel_events (
  event_id INTEGER PRIMARY KEY,
  user_id INTEGER,
  event_type TEXT,
  event_date DATE,
  campaign_source TEXT
);

SQL Solution

WITH funnel_stages AS (
  SELECT 
    user_id,
    MAX(CASE WHEN event_type = 'Landing' THEN 1 ELSE 0 END) as landed,
    MAX(CASE WHEN event_type = 'Signup' THEN 1 ELSE 0 END) as signed_up,
    MAX(CASE WHEN event_type = 'Trial-Start' THEN 1 ELSE 0 END) as trial_started,
    MAX(CASE WHEN event_type = 'Trial-Completed' THEN 1 ELSE 0 END) as trial_completed,
    MAX(CASE WHEN event_type = 'Paid-Conversion' THEN 1 ELSE 0 END) as converted_paid
  FROM funnel_events
  GROUP BY user_id
),
stage_summary AS (
  SELECT 
    COUNT(DISTINCT user_id) as total_landed,
    COUNT(DISTINCT CASE WHEN signed_up = 1 THEN user_id END) as total_signups,
    COUNT(DISTINCT CASE WHEN trial_started = 1 THEN user_id END) as total_trial_starts,
    COUNT(DISTINCT CASE WHEN trial_completed = 1 THEN user_id END) as total_trial_completed,
    COUNT(DISTINCT CASE WHEN converted_paid = 1 THEN user_id END) as total_paid_conversions
  FROM funnel_stages
),
conversion_rates AS (
  SELECT 
    total_landed,
    total_signups,
    total_trial_starts,
    total_trial_completed,
    total_paid_conversions,
    ROUND(total_signups::float / NULLIF(total_landed, 0) * 100, 2) as landing_to_signup_rate,
    ROUND(total_trial_starts::float / NULLIF(total_signups, 0) * 100, 2) as signup_to_trial_rate,
    ROUND(total_trial_completed::float / NULLIF(total_trial_starts, 0) * 100, 2) as trial_completion_rate,
    ROUND(total_paid_conversions::float / NULLIF(total_trial_completed, 0) * 100, 2) as trial_to_paid_rate,
    ROUND(total_paid_conversions::float / NULLIF(total_landed, 0) * 100, 2) as end_to_end_conversion_rate
  FROM stage_summary
)
SELECT 
  'Landing' as funnel_stage,
  total_landed as users_at_stage,
  NULL as conversion_rate_pct,
  total_landed as cumulative_users,
  'Entry Point' as stage_description
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT 
  'Signup',
  total_signups,
  landing_to_signup_rate,
  total_signups,
  'Signup Form Completion'
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT 
  'Trial-Start',
  total_trial_starts,
  signup_to_trial_rate,
  total_trial_starts,
  'Activated Trial'
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT 
  'Trial-Completed',
  total_trial_completed,
  trial_completion_rate,
  total_trial_completed,
  'Finished 7-Day Trial'
FROM (SELECT * FROM conversion_rates)
UNION ALL
SELECT 
  'Paid-Conversion',
  total_paid_conversions,
  trial_to_paid_rate,
  total_paid_conversions,
  'Subscribed to Paid Plan'
FROM (SELECT * FROM conversion_rates);

Explanation