Analyze Marketing Attribution and Optimize Spend

This skill applies marketing attribution modeling across single-touch, multi-touch, and marketing mix approaches. Single-touch models: first-touch (credits the first interaction, good for brand-awareness measurement), last-touch (credits the final interaction, the default in many platforms), and last non-direct click (excludes direct traffic to isolate marketing channels). Multi-touch models: linear (equal credit across touchpoints), time-decay (more credit to recent interactions), U-shaped/position-based (40% first touch, 40% last touch, 20% distributed), W-shaped (emphasizes first touch, lead creation, and opportunity creation), and data-driven attribution using machine learning to determine optimal credit distribution.

For data collection it defines a customer-touchpoint schema and an aggregated attribution view:

-- Customer Journey Data Structure
CREATE TABLE customer_touchpoints (
    user_id VARCHAR(255),
    session_id VARCHAR(255),
    touchpoint_timestamp TIMESTAMP,
    channel VARCHAR(100),
    campaign VARCHAR(255),
    medium VARCHAR(100),
    source VARCHAR(100),
    content VARCHAR(255),
    conversion_event BOOLEAN DEFAULT FALSE,
    conversion_value DECIMAL(10,2),
    touchpoint_sequence INTEGER
);

-- Attribution Analysis View
CREATE VIEW attribution_analysis AS
SELECT 
    user_id,
    channel,
    campaign,
    COUNT(*) as touchpoint_count,
    SUM(CASE WHEN conversion_event THEN 1 ELSE 0 END) as conversions,
    SUM(conversion_value) as total_value,
    MIN(touchpoint_timestamp) as first_touch,
    MAX(touchpoint_timestamp) as last_touch
FROM customer_touchpoints
GROUP BY user_id, channel, campaign;

A Python attribution calculator implements first-touch attribution (grouping by first interaction per user), time-decay attribution (exponential decay weighted by a configurable half-life, normalized per user journey), and data-driven attribution (a logistic regression over channel/campaign/time/device features, using coefficient magnitude as a Shapley-value approximation for credit). For channel-level spend effectiveness it implements Marketing Mix Modeling: an adstock transform for carryover effects, a saturation curve transform for diminishing returns, and a model fit that optimizes decay/alpha/gamma parameters per channel against minimum MAPE (mean absolute percentage error) via L-BFGS-B.

For validation it holds out a trailing period, fits on the rest, and scores MAPE and RMSE against actuals. Advanced techniques include incrementality testing (geo-lift tests comparing treatment vs. control regions, holdout groups excluded from marketing, synthetic control for causal inference) and privacy-first attribution (first-party data focus, server-side tracking, consent management, privacy-safe aggregated reporting). Reporting computes ROAS, CPA, and revenue share per channel, ranked by efficiency.

Use it to build or choose an attribution model that matches your business - W-shaped or custom multi-touch for B2B long sales cycles, time-decay or data-driven for e-commerce, view-through windows for brand-awareness campaigns, and a unified framework for cross-channel campaigns. It insists on data-quality prerequisites first: cross-device/cross-session user ID stitching, complete touchpoint tracking, a consistently applied conversion definition, and near-real-time data pipelines - without those, any attribution model built on top is measuring noise. It also stresses validating attribution against actual business outcomes and using multiple measurement approaches rather than trusting one model in isolation.

Input is touchpoint-level customer journey data (channel, campaign, timestamp, conversion) and, for MMM, channel spend and sales time series. Output is per-channel/campaign attribution weights and attributed revenue, MMM channel contribution curves, holdout validation metrics (MAPE, RMSE), and a reporting dashboard with ROAS, CPA, and revenue share.

It combines SQL for touchpoint storage and aggregation, pandas/numpy for attribution calculation, scikit-learn's LogisticRegression for data-driven attribution, and scipy's minimize (L-BFGS-B) for marketing mix model fitting.

Marketing analysts and growth teams who need to measure true channel effectiveness - choosing and validating an attribution model, building a marketing mix model, or running incrementality tests - rather than trusting last-click defaults.