AT&T Marketing Campaign Analytics & Reporting

Executive Summary

Business Challenge

AT&T's marketing operations team faced critical inefficiencies in their data infrastructure and reporting processes:

• Manual Data Extraction: Marketing analysts spent 15-20 hours weekly manually exporting data from Workfront, Salesforce, and ZoomInfo into Excel spreadsheets for campaign reporting
• Data Silos: No single source of truth for marketing performance — project data in Workfront, CRM data in Salesforce, contact enrichment in ZoomInfo, with no joined analytics layer
• Reporting Lag: Campaign performance reports took 5-7 days to compile, preventing real-time optimization and budget reallocation
• Data Quality Issues: 27% of reports contained errors from manual data entry, copy-paste mistakes, and formula errors in Excel workbooks
• Limited Analytics: No ability to perform advanced analytics like campaign attribution modeling, asset performance correlation, or multi-touch lead scoring
• Scalability Constraints: Manual workflows couldn't handle growing data volumes (500K+ marketing events monthly across 200+ campaigns)
• Stakeholder Frustration: Marketing leadership lacked visibility into campaign ROI, asset utilization rates, and resource allocation efficiency

Solution Architecture

Implementation Process

-- models/intermediate/marketing/int_enriched_leads.sql
-- Enriches Salesforce leads with ZoomInfo firmographic and intent data.
-- Uses domain normalization to improve match rate (38% raw -> 91% after cleanup).
{{
    config(
        materialized='incremental',
        unique_key='lead_id',
        cluster_by=['enrichment_date', 'industry'],
        tags=['marketing', 'enrichment']
    )
}}

WITH salesforce_leads AS (
    SELECT
        lead_id,
        email,
        company_name,
        -- Normalize email domain for ZoomInfo match
        LOWER(SPLIT_PART(email, '@', 2)) AS email_domain,
        campaign_id,
        lead_source,
        created_date AS enrichment_date,
        stage,
        owner_id
    FROM {{ ref('stg_salesforce__leads') }}
    {% if is_incremental() %}
        WHERE created_date > (SELECT MAX(enrichment_date) FROM {{ this }})
    {% endif %}
),

-- Domain alias mapping (legal entity vs. corporate email domain)
domain_aliases AS (
    SELECT
        canonical_domain,
        alias_domain
    FROM {{ ref('seed_domain_aliases') }}
),

zoominfo_accounts AS (
    SELECT
        zi.account_id,
        COALESCE(da.canonical_domain, zi.website_domain) AS matched_domain,
        zi.company_name        AS zi_company_name,
        zi.employee_count,
        zi.industry,
        zi.revenue_range,
        zi.intent_score,         -- buying intent from ZoomInfo Bombora
        zi.tech_stack_array
    FROM {{ ref('stg_zoominfo__accounts') }} zi
    LEFT JOIN domain_aliases da
        ON zi.website_domain = da.alias_domain
),

enriched AS (
    SELECT
        sl.lead_id,
        sl.email,
        sl.email_domain,
        sl.campaign_id,
        sl.lead_source,
        sl.enrichment_date,
        sl.stage,
        sl.owner_id,
        -- ZoomInfo firmographic enrichment
        zi.zi_company_name,
        zi.employee_count,
        zi.industry,
        zi.revenue_range,
        zi.intent_score,
        zi.tech_stack_array,
        CASE WHEN zi.account_id IS NOT NULL THEN TRUE ELSE FALSE END AS is_enriched,
        CASE
            WHEN zi.intent_score >= 70 THEN 'High'
            WHEN zi.intent_score >= 40 THEN 'Medium'
            ELSE 'Low'
        END AS intent_tier
    FROM salesforce_leads sl
    LEFT JOIN zoominfo_accounts zi
        ON sl.email_domain = zi.matched_domain
)

SELECT
    {{ dbt_utils.generate_surrogate_key(['lead_id']) }} AS enriched_lead_sk,
    *,
    CURRENT_TIMESTAMP() AS _loaded_at
FROM enriched

-- models/marts/marketing/fct_campaign_attribution.sql
{{
    config(
        materialized='incremental',
        unique_key='attribution_id',
        cluster_by=['attribution_date', 'campaign_id'],
        tags=['marketing', 'attribution']
    )
}}

WITH leads AS (
    SELECT
        lead_id,
        lead_created_date AS attribution_date,
        campaign_id,
        opportunity_id,
        is_qualified_lead,
        is_closed_won,
        deal_amount
    FROM {{ ref('stg_salesforce__leads') }}
    {% if is_incremental() %}
        WHERE lead_created_date > (SELECT MAX(attribution_date) FROM {{ this }})
    {% endif %}
),

campaigns AS (
    SELECT
        campaign_id,
        campaign_name,
        campaign_type,
        channel,
        start_date,
        end_date,
        budget_amount,
        campaign_owner
    FROM {{ ref('stg_workfront__campaigns') }}
),

firmographic AS (
    SELECT
        account_id,
        campaign_id,
        industry,
        employee_count,
        intent_score
    FROM {{ ref('int_zoominfo__account_enrichment') }}
),

-- Multi-touch attribution using linear model
lead_touchpoints AS (
    SELECT
        opportunity_id,
        COUNT(DISTINCT campaign_id) AS touchpoint_count,
        ARRAY_AGG(DISTINCT campaign_id) AS campaign_journey,
        MIN(lead_created_date) AS first_touch_date,
        MAX(lead_created_date) AS last_touch_date,
        DATEDIFF('day', MIN(lead_created_date), MAX(lead_created_date)) AS journey_length_days
    FROM leads
    GROUP BY opportunity_id
),

-- Revenue allocation (equal credit to all touchpoints)
attributed_revenue AS (
    SELECT
        l.opportunity_id,
        l.campaign_id,
        l.lead_created_date,
        CASE 
            WHEN l.is_closed_won THEN 
                l.deal_amount / lt.touchpoint_count
            ELSE 0
        END AS attributed_revenue,
        1.0 / lt.touchpoint_count AS attribution_weight
    FROM leads l
    INNER JOIN lead_touchpoints lt ON l.opportunity_id = lt.opportunity_id
)

SELECT
    {{ dbt_utils.generate_surrogate_key(['l.lead_id', 'l.campaign_id']) }} AS attribution_id,
    l.lead_created_date AS attribution_date,
    l.campaign_id,
    camp.campaign_name,
    camp.channel,
    l.lead_id,
    l.opportunity_id,
    l.is_qualified_lead,
    l.is_closed_won,
    lt.touchpoint_count,
    lt.journey_length_days,
    ar.attribution_weight,
    ar.attributed_revenue,
    
    -- Campaign metrics
    camp.budget_amount AS campaign_budget,
    COALESCE(f.intent_score, 0) AS zoominfo_intent_score,
    COALESCE(f.industry, 'Unknown') AS lead_industry,
    
    -- Calculated metrics
    CASE 
        WHEN camp.budget_amount > 0 THEN 
            ar.attributed_revenue / camp.budget_amount
        ELSE NULL 
    END AS campaign_roi,
    
    -- Metadata
    CURRENT_TIMESTAMP() AS _loaded_at
    
FROM leads l
INNER JOIN campaigns camp ON l.campaign_id = camp.campaign_id
LEFT JOIN lead_touchpoints lt ON l.opportunity_id = lt.opportunity_id
LEFT JOIN attributed_revenue ar ON l.lead_id = ar.lead_id AND l.campaign_id = ar.campaign_id
LEFT JOIN firmographic f ON l.campaign_id = f.campaign_id

{% if is_incremental() %}
WHERE l.lead_created_date > (SELECT MAX(attribution_date) FROM {{ this }})
{% endif %}

Results & Business Impact

Technical Challenges & Solutions

Key Learnings & Best Practices

Technologies & Tools