Marketing Qualified Leads (MQL)

-- Calculate Marketing Qualified Lead metrics and conversion rates
WITH lead_scoring AS (
  SELECT 
    l.lead_id,
    l.email,
    l.created_date,
    l.source,
    l.campaign_id,
    l.lead_status,
    -- Demographic scoring
    CASE 
      WHEN le.job_title IN ('CEO', 'CTO', 'VP', 'Director', 'Manager') THEN 20
      WHEN le.job_title IN ('Senior', 'Lead', 'Principal') THEN 15
      WHEN le.job_title IN ('Analyst', 'Specialist', 'Coordinator') THEN 10
      ELSE 5
    END as demographic_score,
    -- Firmographic scoring
    CASE 
      WHEN le.company_size >= 1000 THEN 25
      WHEN le.company_size >= 100 THEN 20
      WHEN le.company_size >= 50 THEN 15
      WHEN le.company_size >= 10 THEN 10
      ELSE 5
    END as firmographic_score,
    -- Industry fit scoring
    CASE 
      WHEN le.industry IN ('Technology', 'Financial Services', 'Healthcare') THEN 15
      WHEN le.industry IN ('Manufacturing', 'Retail', 'Education') THEN 10
      ELSE 5
    END as industry_score,
    le.job_title,
    le.company_size,
    le.industry,
    DATE_TRUNC('month', l.created_date) as lead_month
  FROM leads l
  LEFT JOIN lead_enrichment le ON l.lead_id = le.lead_id
  WHERE l.created_date >= CURRENT_DATE - INTERVAL '12 months'
),
behavioral_scoring AS (
  SELECT 
    wa.lead_id,
    COUNT(DISTINCT wa.page_url) as pages_visited,
    COUNT(*) as total_page_views,
    SUM(wa.time_on_page_seconds) as total_time_on_site,
    COUNT(DISTINCT CASE WHEN wa.page_type = 'pricing' THEN wa.page_url END) as pricing_pages,
    COUNT(DISTINCT CASE WHEN wa.page_type = 'demo' THEN wa.page_url END) as demo_pages,
    COUNT(DISTINCT CASE WHEN wa.page_type = 'case_study' THEN wa.page_url END) as case_studies,
    -- Behavioral score calculation
    LEAST(20, COUNT(DISTINCT wa.page_url) * 2) +  -- Max 20 points for page diversity
    LEAST(15, COUNT(DISTINCT CASE WHEN wa.page_type = 'pricing' THEN wa.page_url END) * 10) +  -- Pricing intent
    LEAST(20, COUNT(DISTINCT CASE WHEN wa.page_type = 'demo' THEN wa.page_url END) * 20) +  -- Demo intent
    LEAST(10, SUM(wa.time_on_page_seconds) / 60) as behavioral_score  -- Time engagement
  FROM website_activity wa
  WHERE wa.activity_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY wa.lead_id
),
engagement_scoring AS (
  SELECT 
    la.lead_id,
    COUNT(CASE WHEN la.activity_type = 'email_open' THEN 1 END) as email_opens,
    COUNT(CASE WHEN la.activity_type = 'email_click' THEN 1 END) as email_clicks,
    COUNT(CASE WHEN la.activity_type = 'form_submission' THEN 1 END) as form_submissions,
    COUNT(CASE WHEN la.activity_type = 'content_download' THEN 1 END) as content_downloads,
    COUNT(CASE WHEN la.activity_type = 'webinar_attendance' THEN 1 END) as webinar_attendance,
    -- Engagement score calculation
    LEAST(15, COUNT(CASE WHEN la.activity_type = 'email_click' THEN 1 END) * 3) +
    LEAST(20, COUNT(CASE WHEN la.activity_type = 'form_submission' THEN 1 END) * 10) +
    LEAST(15, COUNT(CASE WHEN la.activity_type = 'content_download' THEN 1 END) * 5) +
    LEAST(25, COUNT(CASE WHEN la.activity_type = 'webinar_attendance' THEN 1 END) * 25) as engagement_score
  FROM lead_activities la
  WHERE la.activity_date >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY la.lead_id
),
mql_qualification AS (
  SELECT 
    ls.lead_id,
    ls.email,
    ls.created_date,
    ls.source,
    ls.campaign_id,
    ls.lead_status,
    ls.lead_month,
    ls.demographic_score,
    ls.firmographic_score,
    ls.industry_score,
    COALESCE(bs.behavioral_score, 0) as behavioral_score,
    COALESCE(es.engagement_score, 0) as engagement_score,
    ls.demographic_score + ls.firmographic_score + ls.industry_score + 
    COALESCE(bs.behavioral_score, 0) + COALESCE(es.engagement_score, 0) as total_score,
    -- MQL qualification criteria
    CASE 
      WHEN (ls.demographic_score + ls.firmographic_score + ls.industry_score + 
            COALESCE(bs.behavioral_score, 0) + COALESCE(es.engagement_score, 0)) >= 70 THEN 1
      ELSE 0
    END as is_mql,
    bs.pages_visited,
    bs.pricing_pages,
    bs.demo_pages,
    es.email_clicks,
    es.form_submissions,
    es.content_downloads
  FROM lead_scoring ls
  LEFT JOIN behavioral_scoring bs ON ls.lead_id = bs.lead_id
  LEFT JOIN engagement_scoring es ON ls.lead_id = es.lead_id
),
mql_metrics AS (
  SELECT 
    source,
    lead_month,
    COUNT(*) as total_leads,
    SUM(is_mql) as mql_count,
    ROUND(100.0 * SUM(is_mql) / COUNT(*), 1) as mql_rate,
    ROUND(AVG(total_score), 1) as avg_lead_score,
    ROUND(AVG(CASE WHEN is_mql = 1 THEN total_score END), 1) as avg_mql_score,
    -- Score component analysis
    ROUND(AVG(demographic_score), 1) as avg_demographic_score,
    ROUND(AVG(firmographic_score), 1) as avg_firmographic_score,
    ROUND(AVG(behavioral_score), 1) as avg_behavioral_score,
    ROUND(AVG(engagement_score), 1) as avg_engagement_score
  FROM mql_qualification
  GROUP BY source, lead_month
)
SELECT 
  source,
  lead_month,
  total_leads,
  mql_count,
  mql_rate,
  avg_lead_score,
  avg_mql_score,
  avg_demographic_score,
  avg_firmographic_score,
  avg_behavioral_score,
  avg_engagement_score,
  LAG(mql_rate) OVER (PARTITION BY source ORDER BY lead_month) as prev_month_mql_rate,
  ROUND(mql_rate - LAG(mql_rate) OVER (PARTITION BY source ORDER BY lead_month), 1) as mql_rate_change,
  RANK() OVER (PARTITION BY lead_month ORDER BY mql_rate DESC) as source_rank
FROM mql_metrics
WHERE total_leads >= 10  -- Minimum sample size
ORDER BY lead_month DESC, mql_rate DESC;

-- Analyze MQL to SQL and customer conversion rates
WITH mql_progression AS (
  SELECT 
    mq.lead_id,
    mq.email,
    mq.created_date,
    mq.source,
    mq.total_score,
    mq.is_mql,
    -- Sales qualification
    CASE 
      WHEN o.opportunity_id IS NOT NULL THEN 1 
      ELSE 0 
    END as became_sql,
    o.created_date as sql_date,
    EXTRACT(days FROM AGE(o.created_date, mq.created_date)) as days_to_sql,
    -- Customer conversion
    CASE 
      WHEN o.stage = 'Closed Won' THEN 1 
      ELSE 0 
    END as became_customer,
    o.close_date as customer_date,
    o.amount as deal_value,
    EXTRACT(days FROM AGE(o.close_date, mq.created_date)) as days_to_customer
  FROM mql_qualification mq
  LEFT JOIN opportunities o ON mq.lead_id = o.lead_id
  WHERE mq.is_mql = 1  -- Focus on MQLs only
),
conversion_funnel AS (
  SELECT 
    source,
    DATE_TRUNC('month', created_date) as mql_month,
    COUNT(*) as total_mqls,
    SUM(became_sql) as sql_conversions,
    SUM(became_customer) as customer_conversions,
    ROUND(100.0 * SUM(became_sql) / COUNT(*), 1) as mql_to_sql_rate,
    ROUND(100.0 * SUM(became_customer) / COUNT(*), 1) as mql_to_customer_rate,
    ROUND(100.0 * SUM(became_customer) / NULLIF(SUM(became_sql), 0), 1) as sql_to_customer_rate,
    ROUND(AVG(days_to_sql), 1) as avg_days_to_sql,
    ROUND(AVG(days_to_customer), 1) as avg_days_to_customer,
    ROUND(AVG(deal_value), 0) as avg_deal_value,
    SUM(deal_value) as total_revenue
  FROM mql_progression
  GROUP BY source, DATE_TRUNC('month', created_date)
),
cohort_analysis AS (
  SELECT 
    mp.source,
    DATE_TRUNC('month', mp.created_date) as cohort_month,
    COUNT(*) as cohort_size,
    -- Month 1 conversions
    SUM(CASE WHEN mp.sql_date <= mp.created_date + INTERVAL '30 days' THEN 1 ELSE 0 END) as month_1_sql,
    SUM(CASE WHEN mp.customer_date <= mp.created_date + INTERVAL '30 days' THEN 1 ELSE 0 END) as month_1_customers,
    -- Month 3 conversions
    SUM(CASE WHEN mp.sql_date <= mp.created_date + INTERVAL '90 days' THEN 1 ELSE 0 END) as month_3_sql,
    SUM(CASE WHEN mp.customer_date <= mp.created_date + INTERVAL '90 days' THEN 1 ELSE 0 END) as month_3_customers,
    -- Month 6 conversions
    SUM(CASE WHEN mp.sql_date <= mp.created_date + INTERVAL '180 days' THEN 1 ELSE 0 END) as month_6_sql,
    SUM(CASE WHEN mp.customer_date <= mp.created_date + INTERVAL '180 days' THEN 1 ELSE 0 END) as month_6_customers
  FROM mql_progression mp
  WHERE mp.created_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY source, DATE_TRUNC('month', mp.created_date)
  HAVING COUNT(*) >= 10  -- Minimum cohort size
)
SELECT 
  source,
  cohort_month,
  cohort_size,
  month_1_sql,
  month_3_sql,
  month_6_sql,
  month_1_customers,
  month_3_customers,
  month_6_customers,
  ROUND(100.0 * month_1_sql / cohort_size, 1) as month_1_sql_rate,
  ROUND(100.0 * month_3_sql / cohort_size, 1) as month_3_sql_rate,
  ROUND(100.0 * month_6_sql / cohort_size, 1) as month_6_sql_rate,
  ROUND(100.0 * month_1_customers / cohort_size, 1) as month_1_customer_rate,
  ROUND(100.0 * month_3_customers / cohort_size, 1) as month_3_customer_rate,
  ROUND(100.0 * month_6_customers / cohort_size, 1) as month_6_customer_rate
FROM cohort_analysis
ORDER BY cohort_month DESC, month_6_customer_rate DESC;

-- Analyze MQL quality by scoring components and characteristics
WITH mql_quality_analysis AS (
  SELECT 
    mq.source,
    mq.lead_month,
    -- Score component ranges
    CASE 
      WHEN mq.demographic_score >= 20 THEN 'High Demographic Fit'
      WHEN mq.demographic_score >= 15 THEN 'Medium Demographic Fit'
      ELSE 'Low Demographic Fit'
    END as demographic_tier,
    CASE 
      WHEN mq.behavioral_score >= 30 THEN 'High Engagement'
      WHEN mq.behavioral_score >= 15 THEN 'Medium Engagement'
      ELSE 'Low Engagement'
    END as behavioral_tier,
    CASE 
      WHEN mq.engagement_score >= 25 THEN 'High Intent'
      WHEN mq.engagement_score >= 15 THEN 'Medium Intent'
      ELSE 'Low Intent'
    END as intent_tier,
    mq.total_score,
    mp.became_sql,
    mp.became_customer,
    mp.deal_value,
    mp.days_to_sql,
    mp.days_to_customer
  FROM mql_qualification mq
  LEFT JOIN mql_progression mp ON mq.lead_id = mp.lead_id
  WHERE mq.is_mql = 1
),
quality_metrics AS (
  SELECT 
    demographic_tier,
    behavioral_tier,
    intent_tier,
    COUNT(*) as mql_count,
    ROUND(100.0 * SUM(became_sql) / COUNT(*), 1) as sql_conversion_rate,
    ROUND(100.0 * SUM(became_customer) / COUNT(*), 1) as customer_conversion_rate,
    ROUND(AVG(days_to_sql), 1) as avg_days_to_sql,
    ROUND(AVG(days_to_customer), 1) as avg_days_to_customer,
    ROUND(AVG(deal_value), 0) as avg_deal_value,
    ROUND(SUM(deal_value) / COUNT(*), 0) as revenue_per_mql
  FROM mql_quality_analysis
  GROUP BY demographic_tier, behavioral_tier, intent_tier
  HAVING COUNT(*) >= 5  -- Minimum sample size
),
source_performance AS (
  SELECT 
    source,
    COUNT(*) as total_mqls,
    ROUND(100.0 * SUM(became_sql) / COUNT(*), 1) as sql_conversion_rate,
    ROUND(100.0 * SUM(became_customer) / COUNT(*), 1) as customer_conversion_rate,
    ROUND(AVG(total_score), 1) as avg_mql_score,
    ROUND(AVG(CASE WHEN became_customer = 1 THEN total_score END), 1) as avg_converting_score,
    ROUND(AVG(deal_value), 0) as avg_deal_value,
    ROUND(SUM(deal_value) / COUNT(*), 0) as revenue_per_mql,
    SUM(deal_value) as total_revenue
  FROM mql_quality_analysis
  GROUP BY source
  HAVING COUNT(*) >= 20  -- Minimum sample size
),
scoring_effectiveness AS (
  SELECT 
    CASE 
      WHEN total_score >= 90 THEN '90-100'
      WHEN total_score >= 80 THEN '80-89'
      WHEN total_score >= 70 THEN '70-79'
      ELSE '60-69'
    END as score_range,
    COUNT(*) as mql_count,
    ROUND(100.0 * SUM(became_sql) / COUNT(*), 1) as sql_rate,
    ROUND(100.0 * SUM(became_customer) / COUNT(*), 1) as customer_rate,
    ROUND(AVG(deal_value), 0) as avg_deal_value
  FROM mql_quality_analysis
  WHERE total_score >= 60  -- MQL threshold
  GROUP BY 1
  ORDER BY MIN(total_score) DESC
)
SELECT 
  score_range,
  mql_count,
  sql_rate,
  customer_rate,
  avg_deal_value,
  RANK() OVER (ORDER BY customer_rate DESC) as performance_rank
FROM scoring_effectiveness
UNION ALL
SELECT 
  CONCAT('Source: ', source) as score_range,
  total_mqls as mql_count,
  sql_conversion_rate as sql_rate,
  customer_conversion_rate as customer_rate,
  avg_deal_value,
  RANK() OVER (ORDER BY customer_conversion_rate DESC) as performance_rank
FROM source_performance
ORDER BY performance_rank;