Building Cross-Channel Analytics: Unifying Ad Spend with Closed Revenue

1. Tracking Infrastructure & Schema Setup

To connect ad spend across networks (Google, Meta, LinkedIn) to backend sales (Stripe, Shopify, CRM), you must enforce consistent query string parameters and capture click identifiers (`gclid`, `fbclid`) at touchpoint events.

Standardize all destination URLs across ad platforms:

https://example.com/landing-page?utm_source=meta&utm_medium=cpc&utm_campaign=q4_promo&utm_content=v1

Persist these parameters into user session storage or cookies on landing, then pass them into your backend database or checkout system during transaction execution.

2. Warehouse Staging Layer Schema

Extract spend data via network APIs (e.g., Google Ads API, Meta Marketing API) and load raw data into a central warehouse (BigQuery, Snowflake, or PostgreSQL). Establish dedicated staging tables for spend and backend sales.

-- Staging Table: Ad Spend
CREATE TABLE stg_ad_spend (
    ad_date DATE NOT NULL,
    channel VARCHAR(50) NOT NULL,
    campaign_id VARCHAR(100) NOT NULL,
    campaign_name VARCHAR(255),
    spend NUMERIC(10, 2) NOT NULL,
    clicks INT,
    impressions INT
);

-- Staging Table: Real Sales / Orders
CREATE TABLE stg_orders (
    order_id VARCHAR(100) PRIMARY KEY,
    order_timestamp TIMESTAMP NOT NULL,
    customer_id VARCHAR(100),
    revenue NUMERIC(10, 2) NOT NULL,
    utm_source VARCHAR(50),
    utm_campaign VARCHAR(255),
    click_id VARCHAR(255)
);

3. SQL Data Transformation Model

Join spend data with verified closed revenue on date and normalized campaign identifiers using a FULL OUTER JOIN. This ensures zero-spend organic sales and zero-revenue spend days remain visible.

WITH daily_spend AS (
    SELECT
        ad_date AS reporting_date,
        LOWER(channel) AS channel,
        LOWER(campaign_name) AS campaign_name,
        SUM(spend) AS total_spend,
        SUM(clicks) AS total_clicks
    FROM stg_ad_spend
    GROUP BY 1, 2, 3
),

daily_sales AS (
    SELECT
        DATE(order_timestamp) AS reporting_date,
        LOWER(utm_source) AS channel,
        LOWER(utm_campaign) AS campaign_name,
        COUNT(DISTINCT order_id) AS total_orders,
        SUM(revenue) AS total_revenue
    FROM stg_orders
    GROUP BY 1, 2, 3
)

SELECT
    COALESCE(s.reporting_date, r.reporting_date) AS reporting_date,
    COALESCE(s.channel, r.channel) AS channel,
    COALESCE(s.campaign_name, r.campaign_name) AS campaign_name,
    COALESCE(s.total_spend, 0) AS spend,
    COALESCE(r.total_revenue, 0) AS revenue,
    COALESCE(r.total_orders, 0) AS orders,
    CASE 
        WHEN COALESCE(s.total_spend, 0) > 0 
        THEN ROUND(COALESCE(r.total_revenue, 0) / s.total_spend, 2)
        ELSE NULL 
    END AS roas
FROM daily_spend s
FULL OUTER JOIN daily_sales r
    ON s.reporting_date = r.reporting_date
    AND s.channel = r.channel
    AND s.campaign_name = r.campaign_name;

4. Technical Limitations & Normalization

• Campaign ID Mapping: Campaign string names often change in ad managers. Where possible, dynamic URL tags should pass immutable campaign IDs (e.g., utm_campaign={{campaign.id}}) to join against explicit platform API IDs.
• Attribution Window Lag: Closed sales frequently happen days or weeks after an ad click. Configure scheduled transformation jobs to re-run over a rolling 30-day window to backfill delayed sales conversions accurately.
• Signal Decay: Browser tracking restrictions (iOS 14.5+, ITP, ad blockers) prevent 100% deterministic tracking. Deterministic SQL joining should be viewed as a directional baseline rather than absolute truth.

Need this done fast? order analytics setup on FreelanceHunt.