Attribution Models

Why Attribution Models matter for a Marketing Analyst

Attribution models assign conversion credit to touchpoints across the customer journey. As a Marketing Analyst, this skill lets you fairly evaluate channels, set budgets, and explain performance without over-crediting last clicks or under-valuing upper-funnel work.

• Answer practical questions like: Which channels start journeys? Which accelerate conversions? What deserves more budget?
• Communicate trade-offs: single-touch simplicity vs multi-touch fairness vs incrementality realism.
• Build trust with reproducible logic and transparent assumptions.

What you'll learn

• Implement last-click, first-click, linear, time-decay, and position-based models.
• Understand multi-touch and bias trade-offs.
• Design lookback windows and session rules.
• Audit data quality and interpret model differences.
• Think in incrementality and lift to sanity-check attribution.

Who this is for

• Marketing Analysts optimizing channel mix and budgets.
• Growth marketers and performance teams needing fair credit.
• Analysts migrating from platform-reported numbers to neutral, business-owned models.

Prerequisites

• Comfort with basic SQL (SELECT, WHERE, GROUP BY, JOIN, window functions).
• Familiarity with marketing channels, UTMs, sessions, and conversions.
• Basic statistics intuition (averages, ratios, confidence concept).

Quick mental model

Think of credit as a pie that must be distributed across touchpoints leading to a conversion. Different rules slice that pie differently.

Practical roadmap

1. Define conversion and sources
   • Agree on the conversion event (e.g., purchase, signup).
   • List event fields: user_id, event_time, channel, session_id, campaign, conversion_flag.
2. Clean and standardize
   • Normalize channels (e.g., group utm_source into Paid Search, Organic Search, Paid Social).
   • Sessionize if needed; ensure timestamps are in a single timezone.
3. Create a training dataset
   • For each conversion, collect the ordered touchpoints within a lookback window (e.g., 30 days).
   • Exclude touches after the conversion timestamp.
4. Build baselines
   • Implement Last-click and First-click first. Compare with platform-reported numbers to sanity-check.
5. Add multi-touch
   • Implement Linear, Time-decay, and Position-based.
   • Document the exact rules and parameters (e.g., half-life = 7 days).
6. Review, bias-check, and report
   • Compare model outputs side-by-side; explain differences.
   • Highlight known biases and where incrementality tests are needed.

Worked examples

-- 1) Get conversions
WITH conv AS (
  SELECT user_id, event_time AS conv_time, session_id AS conv_session
  FROM events
  WHERE event_type = 'purchase'
),
-- 2) Candidate touches within 30 days before conversion
candidates AS (
  SELECT e.user_id, e.session_id, e.event_time, e.channel, c.conv_time
  FROM events e
  JOIN conv c USING (user_id)
  WHERE e.event_time <= c.conv_time
    AND e.event_time >= c.conv_time - INTERVAL '30 days'
    AND e.event_type = 'touch'
),
-- 3) Pick the last touch per conversion
ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, conv_time
           ORDER BY event_time DESC
         ) AS rn
  FROM candidates
)
SELECT user_id, conv_time, channel AS last_click_channel, 1.0 AS credit
FROM ranked
WHERE rn = 1;

-- inputs: touch_time, conv_time
WITH touches AS (
  SELECT user_id, conv_time, channel, touch_time,
         EXTRACT(EPOCH FROM (conv_time - touch_time))/86400.0 AS days_diff
  FROM your_touch_table
),
weighted AS (
  SELECT *, POWER(0.5, days_diff / 7.0) AS raw_w
  FROM touches
),
normalized AS (
  SELECT *, raw_w / SUM(raw_w) OVER (PARTITION BY user_id, conv_time) AS credit
  FROM weighted
)
SELECT user_id, conv_time, channel, credit
FROM normalized;

WITH ordered AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY user_id, conv_time ORDER BY touch_time) AS pos,
         COUNT(*) OVER (PARTITION BY user_id, conv_time) AS n
  FROM your_touch_table
),
alloc AS (
  SELECT user_id, conv_time, channel,
    CASE
      WHEN n = 1 AND pos = 1 THEN 1.0
      WHEN n = 2 THEN 0.5
      WHEN n >= 3 AND pos = 1 THEN 0.4
      WHEN n >= 3 AND pos = n THEN 0.4
      WHEN n >= 3 THEN 0.2 / NULLIF(n - 2, 0)
    END AS credit
  FROM ordered
)
SELECT * FROM alloc;

Mini tasks & drills

• Write a query to get the first touch per conversion (mirror Example 1, reverse ORDER).
• Compute linear credit for a path of 4 touches; verify each gets 25%.
• Create a 14-day vs 30-day lookback comparison and note which channels are most sensitive.
• Change time-decay half-life from 7 to 3 days; measure how much credit shifts to last touches.
• Identify and group raw UTMs into a clean channel taxonomy with fewer than 10 buckets.

Common mistakes and debugging tips

• Leaky lookback window: Including touches after the conversion. Fix: filter touch_time <= conv_time.
• Over-crediting Brand Search: Pure last-click bias. Compare with first-click and time-decay; examine assist roles.
• Duplicate touches in same session: Multiple events inflate credit. Deduplicate by channel per session if appropriate.
• Unclear direct traffic rules: Decide whether to inherit the last known channel when the current session is Direct.
• Identity gaps: Cross-device users look like multiple people. Document limitations; avoid over-precision claims.
• Ignoring business cycle: Half-life or lookback not aligned to buying window. Tune with real journey lengths.

Mini project: Build a simple multi-touch attribution pipeline

1. Assemble data
   • Define conversion and a 30-day lookback.
   • Create a table of touchpoints with (user_id, touch_time, channel, conv_time).
2. Implement models
   • Last-click, First-click, Linear, Time-decay (half-life 7d), Position-based (40-20-40).
3. Aggregate and compare
   • For each model, sum credit by channel and compute CPA or ROAS if revenue is available.
4. Explain results
   • Write one paragraph per model: strengths, weaknesses, recommended use.
5. Optional sanity check
   • Pick a region or week where a channel was paused; compare model credit shifts to conversion changes (directional incrementality check).

Learning path

1. Master Last-click and First-click to set baselines.
2. Add Linear to understand assists.
3. Add Time-decay to respect recency.
4. Add Position-based to emphasize first/last roles.
5. Study incrementality and biases; use small experiments to validate big shifts.

Subskills

• Last Click Attribution: Assigns full credit to the final touch before conversion; great baseline for lower-funnel actions.
• First Click Attribution: Highlights acquisition sources that start journeys; useful for top-of-funnel budget planning.
• Linear Attribution: Equal credit to all touches; good for long, multi-touch journeys.
• Time Decay Attribution: More credit to recent touches using a half-life; aligns to short decision cycles.
• Position Based Attribution: Emphasizes first and last interactions (e.g., 40-20-40).
• Multi Touch Attribution Basics: Concepts to combine all touches fairly and interpret assist roles.
• Incrementality And Lift Thinking: Causality mindset to validate or correct attribution stories.
• Interpreting Attribution Biases: Recognize and explain model-driven shifts and channel incentives.

Next steps

• Implement at least two models end-to-end on your data; compare outputs.
• Document your rules and share a one-page explainer for stakeholders.
• When stakes are high, plan a small geo/holdout test to check incrementality.