Measuring a Hybrid Product-Led + Sales-Led Funnel in SaaS

-- Unified lead acquisition analysis
WITH unified_leads AS (
  SELECT 
    user_id,
    email,
    signup_date AS created_date,
    'Product Signup' AS lead_type,
    utm_source,
    utm_medium,
    utm_campaign
  FROM product_signups
  
  UNION ALL
  
  SELECT 
    NULL AS user_id,
    email,
    created_date,
    lead_source AS lead_type,
    utm_source,
    utm_medium,
    utm_campaign
  FROM crm_leads
  WHERE created_date >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT 
  COALESCE(utm_source, 'Direct') AS source,
  lead_type,
  COUNT(*) AS lead_count,
  COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS percentage_of_total
FROM unified_leads
WHERE created_date >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY utm_source, lead_type
ORDER BY lead_count DESC;

-- Activation analysis with segmentation
WITH activation_events AS (
  SELECT 
    u.user_id,
    u.signup_date,
    u.company_size,
    u.utm_source,
    MIN(e.event_timestamp) AS first_activation_date,
    COUNT(DISTINCT e.event_name) AS activation_events_completed
  FROM users u
  LEFT JOIN product_events e ON u.user_id = e.user_id
    AND e.event_name IN ('project_created', 'data_connected', 'first_query_run', 'dashboard_created')
    AND e.event_timestamp BETWEEN u.signup_date AND u.signup_date + INTERVAL '7' DAY
  WHERE u.signup_date >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY u.user_id, u.signup_date, u.company_size, u.utm_source
)
SELECT 
  company_size,
  utm_source,
  COUNT(*) AS total_signups,
  COUNT(first_activation_date) AS activated_users,
  COUNT(first_activation_date) * 100.0 / COUNT(*) AS activation_rate,
  AVG(EXTRACT(EPOCH FROM (first_activation_date - signup_date))/3600) AS avg_hours_to_activate
FROM activation_events
GROUP BY company_size, utm_source
ORDER BY activation_rate DESC;

-- Comprehensive lead scoring for hybrid funnel
WITH lead_scoring AS (
  SELECT 
    u.user_id,
    u.email,
    u.company_name,
    u.company_size,
    u.industry,
    u.signup_date,
    
    -- Product engagement score (0-40 points)
    CASE 
      WHEN activation_events.activation_count >= 3 THEN 40
      WHEN activation_events.activation_count >= 2 THEN 30
      WHEN activation_events.activation_count >= 1 THEN 20
      ELSE 0
    END AS product_engagement_score,
    
    -- Company fit score (0-40 points)
    CASE 
      WHEN u.company_size = 'Enterprise' THEN 40
      WHEN u.company_size = 'Mid-Market' THEN 30
      WHEN u.company_size = 'SMB' THEN 20
      ELSE 10
    END AS company_fit_score,
    
    -- Behavioral score (0-20 points)
    CASE 
      WHEN recent_activity.days_active >= 5 THEN 20
      WHEN recent_activity.days_active >= 3 THEN 15
      WHEN recent_activity.days_active >= 1 THEN 10
      ELSE 0
    END AS behavioral_score
    
  FROM users u
  LEFT JOIN (
    SELECT 
      user_id,
      COUNT(DISTINCT event_name) as activation_count
    FROM product_events 
    WHERE event_name IN ('project_created', 'data_connected', 'first_query_run')
    GROUP BY user_id
  ) activation_events ON u.user_id = activation_events.user_id
  LEFT JOIN (
    SELECT 
      user_id,
      COUNT(DISTINCT DATE(event_timestamp)) as days_active
    FROM product_events 
    WHERE event_timestamp >= CURRENT_DATE - INTERVAL '7' DAY
    GROUP BY user_id
  ) recent_activity ON u.user_id = recent_activity.user_id
  WHERE u.signup_date >= CURRENT_DATE - INTERVAL '30' DAY
)
SELECT 
  user_id,
  email,
  company_name,
  company_size,
  (product_engagement_score + company_fit_score + behavioral_score) AS total_score,
  CASE 
    WHEN (product_engagement_score + company_fit_score + behavioral_score) >= 80 THEN 'Hot PQL'
    WHEN (product_engagement_score + company_fit_score + behavioral_score) >= 60 THEN 'Warm PQL'
    WHEN (product_engagement_score + company_fit_score + behavioral_score) >= 40 THEN 'Cold Lead'
    ELSE 'Nurture'
  END AS lead_grade
FROM lead_scoring
ORDER BY total_score DESC;

-- Sales touch effectiveness analysis
WITH sales_engagement AS (
  SELECT 
    l.user_id,
    l.email,
    l.pql_date,
    l.lead_score,
    MIN(s.activity_date) AS first_sales_touch,
    COUNT(s.activity_id) AS total_sales_activities,
    MAX(CASE WHEN s.activity_type = 'Demo Completed' THEN s.activity_date END) AS demo_date,
    MAX(CASE WHEN s.activity_type = 'Proposal Sent' THEN s.activity_date END) AS proposal_date
  FROM pql_leads l
  LEFT JOIN sales_activities s ON l.email = s.contact_email
    AND s.activity_date >= l.pql_date
  WHERE l.pql_date >= CURRENT_DATE - INTERVAL '90' DAY
  GROUP BY l.user_id, l.email, l.pql_date, l.lead_score
),
conversion_outcomes AS (
  SELECT 
    se.*,
    c.conversion_date,
    c.plan_type,
    c.mrr
  FROM sales_engagement se
  LEFT JOIN customers c ON se.email = c.email
    AND c.conversion_date >= se.pql_date
)
SELECT 
  CASE 
    WHEN lead_score >= 80 THEN 'Hot PQL'
    WHEN lead_score >= 60 THEN 'Warm PQL'
    ELSE 'Cold PQL'
  END AS pql_tier,
  COUNT(*) AS total_pqls,
  COUNT(first_sales_touch) AS sales_accepted,
  COUNT(first_sales_touch) * 100.0 / COUNT(*) AS acceptance_rate,
  COUNT(demo_date) AS demos_completed,
  COUNT(demo_date) * 100.0 / COUNT(first_sales_touch) AS demo_rate,
  COUNT(conversion_date) AS conversions,
  COUNT(conversion_date) * 100.0 / COUNT(*) AS overall_conversion_rate,
  AVG(EXTRACT(EPOCH FROM (conversion_date - pql_date))/86400) AS avg_sales_cycle_days
FROM conversion_outcomes
GROUP BY 
  CASE 
    WHEN lead_score >= 80 THEN 'Hot PQL'
    WHEN lead_score >= 60 THEN 'Warm PQL'
    ELSE 'Cold PQL'
  END
ORDER BY avg_sales_cycle_days ASC;

-- Conversion path analysis for hybrid funnel
WITH conversion_paths AS (
  SELECT 
    u.user_id,
    u.email,
    u.signup_date,
    u.company_size,
    c.conversion_date,
    c.plan_type,
    c.mrr,
    c.annual_contract_value,
    
    -- Determine conversion path
    CASE 
      WHEN sq.first_sales_touch IS NULL THEN 'Pure Self-Serve'
      WHEN sq.first_sales_touch < c.conversion_date 
        AND u.signup_date < sq.first_sales_touch THEN 'Sales-Assisted'
      WHEN sq.first_sales_touch >= c.conversion_date THEN 'Self-Serve (Post-Sale Touch)'
      ELSE 'Hybrid'
    END AS conversion_path,
    
    EXTRACT(EPOCH FROM (c.conversion_date - u.signup_date))/86400 AS days_to_convert
    
  FROM users u
  JOIN customers c ON u.email = c.email
  LEFT JOIN (
    SELECT 
      email,
      MIN(activity_date) as first_sales_touch
    FROM sales_activities
    GROUP BY email
  ) sq ON u.email = sq.email
  WHERE u.signup_date >= CURRENT_DATE - INTERVAL '90' DAY
)
SELECT 
  conversion_path,
  company_size,
  COUNT(*) AS conversions,
  COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() AS percentage_of_conversions,
  AVG(mrr) AS avg_mrr,
  AVG(annual_contract_value) AS avg_acv,
  AVG(days_to_convert) AS avg_days_to_convert,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY days_to_convert) AS median_days_to_convert
FROM conversion_paths
GROUP BY conversion_path, company_size
ORDER BY avg_acv DESC;

-- Expansion opportunity identification
WITH account_usage AS (
  SELECT 
    c.account_id,
    c.current_plan,
    c.current_seats,
    c.mrr,
    COUNT(DISTINCT u.user_id) AS active_users,
    COUNT(DISTINCT pe.event_name) AS features_used,
    SUM(CASE WHEN pe.event_name = 'advanced_analytics_used' THEN 1 ELSE 0 END) AS advanced_feature_usage,
    MAX(pe.event_timestamp) AS last_activity_date
  FROM customers c
  JOIN users u ON c.account_id = u.account_id
  LEFT JOIN product_events pe ON u.user_id = pe.user_id
    AND pe.event_timestamp >= CURRENT_DATE - INTERVAL '30' DAY
  GROUP BY c.account_id, c.current_plan, c.current_seats, c.mrr
),
expansion_scores AS (
  SELECT 
    *,
    -- Seat expansion opportunity
    CASE 
      WHEN active_users > current_seats * 0.8 THEN 'High'
      WHEN active_users > current_seats * 0.6 THEN 'Medium'
      ELSE 'Low'
    END AS seat_expansion_opportunity,
    
    -- Plan upgrade opportunity  
    CASE 
      WHEN current_plan = 'Basic' AND advanced_feature_usage > 10 THEN 'High'
      WHEN current_plan = 'Basic' AND advanced_feature_usage > 5 THEN 'Medium'
      WHEN current_plan = 'Pro' AND features_used > 15 THEN 'High'
      ELSE 'Low'
    END AS plan_upgrade_opportunity
  FROM account_usage
  WHERE last_activity_date >= CURRENT_DATE - INTERVAL '7' DAY -- Active accounts only
)
SELECT 
  current_plan,
  seat_expansion_opportunity,
  plan_upgrade_opportunity,
  COUNT(*) AS account_count,
  SUM(mrr) AS total_mrr,
  AVG(active_users::FLOAT / current_seats) AS avg_seat_utilization,
  AVG(features_used) AS avg_features_used
FROM expansion_scores
GROUP BY current_plan, seat_expansion_opportunity, plan_upgrade_opportunity
ORDER BY 
  CASE seat_expansion_opportunity WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END,
  CASE plan_upgrade_opportunity WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END;