How to Measure Trial-to-Paid Conversion for PLG SaaS

-- Trial-to-Paid Conversion Analysis with Cohort Breakdown
WITH trial_cohorts AS (
  SELECT 
    u.user_id,
    u.trial_start_date,
    u.acquisition_channel,
    u.utm_source,
    u.utm_campaign,
    DATE_TRUNC('week', u.trial_start_date) AS cohort_week,
    DATE_TRUNC('month', u.trial_start_date) AS cohort_month,
    -- Calculate trial end date (typically 14 days)
    u.trial_start_date + INTERVAL '14 days' AS trial_end_date
  FROM users u
  WHERE u.trial_start_date IS NOT NULL
    AND u.trial_start_date >= '2024-01-01'
),

activation_events AS (
  SELECT 
    e.user_id,
    COUNT(DISTINCT CASE WHEN e.event_name = 'project_created' THEN e.event_id END) AS projects_created,
    COUNT(DISTINCT CASE WHEN e.event_name = 'invite_sent' THEN e.event_id END) AS invites_sent,
    COUNT(DISTINCT CASE WHEN e.event_name = 'integration_connected' THEN e.event_id END) AS integrations_connected,
    COUNT(DISTINCT CASE WHEN e.event_name = 'first_dashboard_view' THEN e.event_id END) AS dashboards_viewed,
    MIN(CASE WHEN e.event_name = 'project_created' THEN e.event_timestamp END) AS first_project_created_at,
    COUNT(DISTINCT DATE(e.event_timestamp)) AS active_days_in_trial
  FROM events e
  JOIN trial_cohorts tc ON e.user_id = tc.user_id
  WHERE e.event_timestamp BETWEEN tc.trial_start_date AND tc.trial_end_date
    AND e.event_name IN ('project_created', 'invite_sent', 'integration_connected', 'first_dashboard_view')
  GROUP BY e.user_id
),

conversions AS (
  SELECT 
    s.user_id,
    MIN(s.subscription_start_date) AS first_payment_date,
    MIN(s.plan_type) AS first_plan_type,
    MIN(s.billing_cycle) AS first_billing_cycle,
    SUM(s.mrr) AS initial_mrr
  FROM subscriptions s
  WHERE s.subscription_status = 'active'
    AND s.subscription_start_date IS NOT NULL
  GROUP BY s.user_id
),

cohort_analysis AS (
  SELECT 
    tc.user_id,
    tc.cohort_week,
    tc.cohort_month,
    tc.acquisition_channel,
    tc.utm_source,
    tc.utm_campaign,
    tc.trial_start_date,
    tc.trial_end_date,
    
    -- Activation metrics
    COALESCE(ae.projects_created, 0) AS projects_created,
    COALESCE(ae.invites_sent, 0) AS invites_sent,
    COALESCE(ae.integrations_connected, 0) AS integrations_connected,
    COALESCE(ae.dashboards_viewed, 0) AS dashboards_viewed,
    COALESCE(ae.active_days_in_trial, 0) AS active_days_in_trial,
    ae.first_project_created_at,
    
    -- Conversion metrics
    c.first_payment_date,
    c.first_plan_type,
    c.first_billing_cycle,
    c.initial_mrr,
    
    -- Calculate conversion status and timing
    CASE 
      WHEN c.first_payment_date IS NOT NULL THEN 1 
      ELSE 0 
    END AS converted_to_paid,
    
    CASE 
      WHEN c.first_payment_date <= tc.trial_end_date THEN 1 
      ELSE 0 
    END AS converted_during_trial,
    
    CASE 
      WHEN c.first_payment_date > tc.trial_end_date THEN 1 
      ELSE 0 
    END AS converted_after_trial,
    
    -- Time to conversion in days
    CASE 
      WHEN c.first_payment_date IS NOT NULL 
      THEN EXTRACT(days FROM c.first_payment_date - tc.trial_start_date)
      ELSE NULL 
    END AS days_to_conversion,
    
    -- Activation score (weighted combination of key events)
    (COALESCE(ae.projects_created, 0) * 3 +
     COALESCE(ae.invites_sent, 0) * 2 +
     COALESCE(ae.integrations_connected, 0) * 4 +
     COALESCE(ae.dashboards_viewed, 0) * 1) AS activation_score
     
  FROM trial_cohorts tc
  LEFT JOIN activation_events ae ON tc.user_id = ae.user_id
  LEFT JOIN conversions c ON tc.user_id = c.user_id
)

-- Final aggregated results
SELECT 
  cohort_month,
  acquisition_channel,
  
  -- Cohort size and basic conversion
  COUNT(*) AS trial_users,
  SUM(converted_to_paid) AS paid_conversions,
  ROUND(100.0 * SUM(converted_to_paid) / COUNT(*), 2) AS conversion_rate_pct,
  
  -- Conversion timing breakdown
  SUM(converted_during_trial) AS converted_during_trial,
  SUM(converted_after_trial) AS converted_after_trial,
  ROUND(AVG(days_to_conversion), 1) AS avg_days_to_conversion,
  
  -- Activation metrics for converted vs non-converted
  ROUND(AVG(CASE WHEN converted_to_paid = 1 THEN projects_created END), 2) AS avg_projects_converted_users,
  ROUND(AVG(CASE WHEN converted_to_paid = 0 THEN projects_created END), 2) AS avg_projects_non_converted_users,
  
  ROUND(AVG(CASE WHEN converted_to_paid = 1 THEN active_days_in_trial END), 1) AS avg_active_days_converted,
  ROUND(AVG(CASE WHEN converted_to_paid = 0 THEN active_days_in_trial END), 1) AS avg_active_days_non_converted,
  
  ROUND(AVG(CASE WHEN converted_to_paid = 1 THEN activation_score END), 1) AS avg_activation_score_converted,
  ROUND(AVG(CASE WHEN converted_to_paid = 0 THEN activation_score END), 1) AS avg_activation_score_non_converted,
  
  -- Revenue metrics
  SUM(initial_mrr) AS total_initial_mrr,
  ROUND(AVG(initial_mrr), 2) AS avg_mrr_per_conversion,
  
  -- Plan mix
  ROUND(100.0 * SUM(CASE WHEN first_billing_cycle = 'annual' THEN 1 ELSE 0 END) / NULLIF(SUM(converted_to_paid), 0), 1) AS annual_plan_pct

FROM cohort_analysis
WHERE cohort_month >= '2024-01-01'
GROUP BY cohort_month, acquisition_channel
ORDER BY cohort_month DESC, conversion_rate_pct DESC;