Track Lead Conversion
Google Ads + HubSpot

-- Lead Conversion Tracking: Google Ads to HubSpot Revenue
WITH google_ads_data AS (
  SELECT 
    campaign_id,
    campaign_name,
    ad_group_name,
    keyword,
    DATE_TRUNC('month', click_date) AS month,
    SUM(clicks) AS total_clicks,
    SUM(cost) AS total_cost,
    COUNT(DISTINCT session_id) AS sessions
  FROM google_ads_clicks
  WHERE click_date >= '2024-01-01'
  GROUP BY campaign_id, campaign_name, ad_group_name, keyword, month
),

hubspot_conversions AS (
  SELECT 
    c.contact_id,
    c.email,
    c.created_date,
    c.original_source,
    c.utm_campaign,
    c.utm_source,
    c.utm_medium,
    c.utm_term,
    
    -- Lead qualification
    c.lead_score,
    c.lifecycle_stage,
    CASE WHEN c.lead_score >= 50 THEN 1 ELSE 0 END AS is_qualified_lead,
    
    -- Deal information
    d.deal_id,
    d.deal_amount,
    d.deal_stage,
    d.close_date,
    CASE WHEN d.deal_stage = 'closedwon' THEN 1 ELSE 0 END AS is_customer,
    CASE WHEN d.deal_stage = 'closedwon' THEN d.deal_amount ELSE 0 END AS revenue
    
  FROM hubspot_contacts c
  LEFT JOIN hubspot_deals d ON c.contact_id = d.contact_id
  WHERE c.created_date >= '2024-01-01'
    AND c.original_source LIKE '%google%'
),

conversion_analysis AS (
  SELECT 
    ga.campaign_name,
    ga.month,
    ga.total_clicks,
    ga.total_cost,
    
    -- Lead metrics
    COUNT(DISTINCT hc.contact_id) AS total_leads,
    SUM(hc.is_qualified_lead) AS qualified_leads,
    
    -- Conversion rates
    ROUND(COUNT(DISTINCT hc.contact_id) * 100.0 / NULLIF(ga.total_clicks, 0), 2) AS click_to_lead_rate,
    ROUND(SUM(hc.is_qualified_lead) * 100.0 / NULLIF(COUNT(DISTINCT hc.contact_id), 0), 2) AS lead_qualification_rate,
    
    -- Revenue metrics  
    SUM(hc.is_customer) AS customers,
    SUM(hc.revenue) AS total_revenue,
    ROUND(SUM(hc.revenue) / NULLIF(COUNT(DISTINCT hc.contact_id), 0), 2) AS revenue_per_lead,
    
    -- Cost metrics
    ROUND(ga.total_cost / NULLIF(COUNT(DISTINCT hc.contact_id), 0), 2) AS cost_per_lead,
    ROUND(ga.total_cost / NULLIF(SUM(hc.is_qualified_lead), 0), 2) AS cost_per_qualified_lead,
    ROUND(ga.total_cost / NULLIF(SUM(hc.is_customer), 0), 2) AS customer_acquisition_cost,
    
    -- ROI calculation
    ROUND((SUM(hc.revenue) - ga.total_cost) / NULLIF(ga.total_cost, 0) * 100, 1) AS roi_percentage
    
  FROM google_ads_data ga
  LEFT JOIN hubspot_conversions hc ON ga.campaign_name = hc.utm_campaign
                                  AND DATE_TRUNC('month', hc.created_date) = ga.month
  GROUP BY ga.campaign_name, ga.month, ga.total_clicks, ga.total_cost
)

SELECT 
  campaign_name,
  month,
  total_clicks,
  total_cost,
  total_leads,
  qualified_leads,
  customers,
  click_to_lead_rate || '%' AS click_to_lead_conversion,
  lead_qualification_rate || '%' AS qualification_rate,
  cost_per_lead,
  cost_per_qualified_lead,
  customer_acquisition_cost,
  total_revenue,
  revenue_per_lead,
  roi_percentage || '%' AS campaign_roi
FROM conversion_analysis
WHERE total_leads > 0
ORDER BY total_revenue DESC, roi_percentage DESC;