Campaign Performance & ROI Optimization

Business Challenge

• Fragmented Data Sources: Campaign data siloed across Google Ads, Bing, Facebook, internal CRM, billing systems — no unified view of performance
• Manual Reporting: Account managers spending 20+ hours weekly compiling spreadsheets for client reports, delaying optimization decisions
• Attribution Gaps: Inability to track customer journey across multiple touchpoints; last-click attribution severely undervalued upper-funnel efforts
• ROI Uncertainty: Clients demanding clearer connection between ad spend and business outcomes (calls, store visits, purchases)
• Audience Insights: Limited segmentation capabilities; campaigns treated all customers the same despite varying lifetime values
• Competitive Pressure: Local advertising market becoming commoditized; needed differentiation through superior analytics and insights

Campaign Performance Dashboard

Real-time analytics dashboard providing comprehensive visibility into campaign performance across Google Ads, Bing, Facebook, and display networks. Enabled account managers and clients to monitor KPIs, identify optimization opportunities, and track ROI across the customer journey.

Data Architecture & Integration

Implementation Phases

Key Code Example: Multi-Touch Attribution

-- Multi-touch attribution model with weighted credit
-- First touch: 40%, Last touch: 40%, Middle touches: 20% distributed

WITH customer_touchpoints AS (
    SELECT 
        c.customer_id,
        c.conversion_date,
        c.conversion_revenue,
        ct.touchpoint_date,
        ct.campaign_id,
        ct.channel,
        ct.ad_spend,
        ROW_NUMBER() OVER (
            PARTITION BY c.customer_id, c.conversion_date 
            ORDER BY ct.touchpoint_date ASC
        ) AS touch_sequence,
        COUNT(*) OVER (
            PARTITION BY c.customer_id, c.conversion_date
        ) AS total_touches
    FROM conversions c
    INNER JOIN campaign_touchpoints ct
        ON c.customer_id = ct.customer_id
        AND ct.touchpoint_date <= c.conversion_date
        AND ct.touchpoint_date >= DATEADD(day, -30, c.conversion_date)
),
attributed_revenue AS (
    SELECT
        customer_id,
        conversion_date,
        conversion_revenue,
        campaign_id,
        channel,
        ad_spend,
        touch_sequence,
        total_touches,
        CASE 
            -- First touch gets 40%
            WHEN touch_sequence = 1 THEN conversion_revenue * 0.40
            -- Last touch gets 40%
            WHEN touch_sequence = total_touches THEN conversion_revenue * 0.40
            -- Middle touches split remaining 20%
            ELSE conversion_revenue * 0.20 / NULLIF(total_touches - 2, 0)
        END AS attributed_revenue
    FROM customer_touchpoints
)
SELECT
    campaign_id,
    channel,
    SUM(ad_spend) AS total_ad_spend,
    SUM(attributed_revenue) AS total_attributed_revenue,
    SUM(attributed_revenue) / NULLIF(SUM(ad_spend), 0) AS roas,
    COUNT(DISTINCT customer_id) AS influenced_customers
FROM attributed_revenue
GROUP BY campaign_id, channel
ORDER BY total_attributed_revenue DESC;

Technical Challenges & Solutions

Key Learnings

• Last-click attribution significantly undervalues upper-funnel campaigns. Multi-touch models revealed that awareness and consideration channels drove 35% more conversions than credited under last-click.
• Audience segmentation unlocks 2-3x performance improvements. Treating all customers the same wastes budget — targeting high-LTV segments dramatically improved ROAS.
• Self-service analytics empowers faster decision-making. Giving account managers and clients direct dashboard access eliminated bottlenecks and enabled real-time optimization.
• Automated reporting is non-negotiable at scale. Manually compiling reports for 500+ clients is impossible — automation was critical to scaling the business.
• Data quality is the foundation of trust. Early investment in validation rules and anomaly detection prevented countless errors and maintained stakeholder confidence.
• Collaboration with stakeholders ensures adoption. Iterative dashboard design with account managers (not in isolation) ensured the final product met real business needs.