Full-Funnel Dashboard
GA4 + HubSpot + Salesforce

-- Full Funnel Performance Dashboard
WITH funnel_data AS (
  -- GA4 Traffic Data
  SELECT 
    DATE_TRUNC('month', event_date) AS month,
    traffic_source.source AS source,
    traffic_source.medium AS medium,
    traffic_source.campaign AS campaign,
    COUNT(DISTINCT user_pseudo_id) AS website_visitors,
    COUNTIF(event_name = 'form_submit') AS form_submissions,
    COUNTIF(event_name = 'purchase' OR event_name = 'generate_lead') AS ga4_conversions
  FROM ga4_events
  WHERE event_date >= '2024-01-01'
  GROUP BY month, source, medium, campaign
),

hubspot_leads AS (
  -- HubSpot Lead Data
  SELECT 
    DATE_TRUNC('month', createdate) AS month,
    original_source_type AS source,
    original_source_data_1 AS medium,
    original_source_data_2 AS campaign,
    COUNT(*) AS total_leads,
    COUNTIF(lifecyclestage = 'marketingqualifiedlead') AS mqls,
    COUNTIF(lifecyclestage = 'salesqualifiedlead') AS sqls,
    COUNT(DISTINCT CASE WHEN dealstage IS NOT NULL THEN contact_id END) AS leads_to_opportunities
  FROM hubspot_contacts c
  LEFT JOIN hubspot_deals d ON c.contact_id = d.primary_contact_id
  WHERE c.createdate >= '2024-01-01'
  GROUP BY month, source, medium, campaign
),

salesforce_opportunities AS (
  -- Salesforce Opportunity Data
  SELECT 
    DATE_TRUNC('month', o.createddate) AS month,
    l.leadsource AS source,
    l.lead_source_detail__c AS medium,
    l.utm_campaign__c AS campaign,
    COUNT(*) AS total_opportunities,
    COUNT(CASE WHEN o.stagename = 'Closed Won' THEN 1 END) AS closed_won_deals,
    SUM(CASE WHEN o.stagename = 'Closed Won' THEN o.amount ELSE 0 END) AS total_revenue,
    AVG(CASE WHEN o.stagename = 'Closed Won' THEN o.amount END) AS avg_deal_size
  FROM salesforce_opportunities o
  JOIN salesforce_leads l ON o.lead_id__c = l.id
  WHERE o.createddate >= '2024-01-01'
  GROUP BY month, source, medium, campaign
),

combined_funnel AS (
  SELECT 
    COALESCE(f.month, h.month, s.month) AS month,
    COALESCE(f.source, h.source, s.source) AS source,
    COALESCE(f.medium, h.medium, s.medium) AS medium,
    COALESCE(f.campaign, h.campaign, s.campaign) AS campaign,
    
    -- Funnel Metrics
    COALESCE(f.website_visitors, 0) AS website_visitors,
    COALESCE(f.form_submissions, 0) AS form_submissions,
    COALESCE(h.total_leads, 0) AS total_leads,
    COALESCE(h.mqls, 0) AS mqls,
    COALESCE(h.sqls, 0) AS sqls,
    COALESCE(s.total_opportunities, 0) AS total_opportunities,
    COALESCE(s.closed_won_deals, 0) AS closed_won_deals,
    COALESCE(s.total_revenue, 0) AS total_revenue,
    COALESCE(s.avg_deal_size, 0) AS avg_deal_size
    
  FROM funnel_data f
  FULL OUTER JOIN hubspot_leads h ON f.month = h.month 
                                   AND f.source = h.source 
                                   AND f.medium = h.medium 
                                   AND f.campaign = h.campaign
  FULL OUTER JOIN salesforce_opportunities s ON COALESCE(f.month, h.month) = s.month 
                                              AND COALESCE(f.source, h.source) = s.source 
                                              AND COALESCE(f.medium, h.medium) = s.medium 
                                              AND COALESCE(f.campaign, h.campaign) = s.campaign
)

SELECT 
  month,
  source,
  medium,
  campaign,
  website_visitors,
  total_leads,
  mqls,
  sqls,
  total_opportunities,
  closed_won_deals,
  total_revenue,
  avg_deal_size,
  
  -- Conversion Rates
  ROUND(SAFE_DIVIDE(total_leads, website_visitors) * 100, 2) AS visitor_to_lead_rate,
  ROUND(SAFE_DIVIDE(mqls, total_leads) * 100, 2) AS lead_to_mql_rate,
  ROUND(SAFE_DIVIDE(sqls, mqls) * 100, 2) AS mql_to_sql_rate,
  ROUND(SAFE_DIVIDE(total_opportunities, sqls) * 100, 2) AS sql_to_opportunity_rate,
  ROUND(SAFE_DIVIDE(closed_won_deals, total_opportunities) * 100, 2) AS opportunity_to_customer_rate,
  
  -- Overall Conversion Rate
  ROUND(SAFE_DIVIDE(closed_won_deals, website_visitors) * 100, 4) AS overall_conversion_rate

FROM combined_funnel
WHERE website_visitors > 0 OR total_leads > 0
ORDER BY month DESC, total_revenue DESC;

-- Multi-Touch Attribution for Revenue
WITH customer_touchpoints AS (
  SELECT 
    sf.contact_email,
    sf.opportunity_id,
    sf.close_date,
    sf.amount,
    
    -- First Touch (GA4)
    ga.source AS first_touch_source,
    ga.medium AS first_touch_medium,
    ga.campaign AS first_touch_campaign,
    ga.event_date AS first_touch_date,
    
    -- Lead Creation (HubSpot)
    hs.original_source_type AS lead_source,
    hs.original_source_data_1 AS lead_medium,
    hs.original_source_data_2 AS lead_campaign,
    hs.createdate AS lead_create_date,
    
    -- Last Touch Before Opportunity
    sf.leadsource AS last_touch_source,
    sf.lead_source_detail__c AS last_touch_medium,
    sf.utm_campaign__c AS last_touch_campaign
    
  FROM salesforce_opportunities sf
  LEFT JOIN hubspot_contacts hs ON sf.contact_email = hs.email
  LEFT JOIN (
    SELECT 
      user_id,
      MIN(event_date) AS event_date,
      FIRST_VALUE(traffic_source.source) OVER (
        PARTITION BY user_id ORDER BY event_timestamp ASC
      ) AS source,
      FIRST_VALUE(traffic_source.medium) OVER (
        PARTITION BY user_id ORDER BY event_timestamp ASC
      ) AS medium,
      FIRST_VALUE(traffic_source.campaign) OVER (
        PARTITION BY user_id ORDER BY event_timestamp ASC
      ) AS campaign
    FROM ga4_events
    WHERE user_id IS NOT NULL
    GROUP BY user_id
  ) ga ON sf.contact_email = ga.user_id
  
  WHERE sf.stagename = 'Closed Won'
    AND sf.close_date >= '2024-01-01'
),

attribution_revenue AS (
  SELECT 
    'First Touch' AS attribution_model,
    first_touch_source AS source,
    first_touch_medium AS medium,
    first_touch_campaign AS campaign,
    COUNT(DISTINCT opportunity_id) AS deals_attributed,
    SUM(amount) AS revenue_attributed,
    AVG(amount) AS avg_deal_size
  FROM customer_touchpoints
  WHERE first_touch_source IS NOT NULL
  GROUP BY first_touch_source, first_touch_medium, first_touch_campaign
  
  UNION ALL
  
  SELECT 
    'Lead Creation' AS attribution_model,
    lead_source AS source,
    lead_medium AS medium,
    lead_campaign AS campaign,
    COUNT(DISTINCT opportunity_id) AS deals_attributed,
    SUM(amount) AS revenue_attributed,
    AVG(amount) AS avg_deal_size
  FROM customer_touchpoints
  WHERE lead_source IS NOT NULL
  GROUP BY lead_source, lead_medium, lead_campaign
  
  UNION ALL
  
  SELECT 
    'Last Touch' AS attribution_model,
    last_touch_source AS source,
    last_touch_medium AS medium,
    last_touch_campaign AS campaign,
    COUNT(DISTINCT opportunity_id) AS deals_attributed,
    SUM(amount) AS revenue_attributed,
    AVG(amount) AS avg_deal_size
  FROM customer_touchpoints
  WHERE last_touch_source IS NOT NULL
  GROUP BY last_touch_source, last_touch_medium, last_touch_campaign
)

SELECT 
  attribution_model,
  source,
  medium,
  campaign,
  deals_attributed,
  ROUND(revenue_attributed, 2) AS revenue_attributed,
  ROUND(avg_deal_size, 2) AS avg_deal_size,
  ROUND(revenue_attributed / SUM(revenue_attributed) OVER (PARTITION BY attribution_model) * 100, 1) AS revenue_share_pct
FROM attribution_revenue
WHERE deals_attributed >= 1
ORDER BY attribution_model, revenue_attributed DESC;