Marketing attribution assigns conversion credit across the touchpoints that preceded a conversion. This guide covers the main attribution models — last touch, first touch, linear, time decay, and data-driven — the SQL to implement them against event-level data, and the trade-offs that determine which model fits which measurement question.
What Marketing Attribution Solves
A user sees a Facebook ad on Monday, clicks a Google search result on Wednesday, opens an email on Friday, and converts after clicking a retargeting display ad on Saturday. Which channel gets credit for the conversion?
Attribution modeling answers this question — and the answer determines how marketing budget is allocated. If last-touch attribution is used, the retargeting display ad gets all the credit and Facebook, search, and email get none. If first-touch is used, Facebook gets everything. If multi-touch is used, credit is distributed across all contributing touchpoints.
The model you choose shapes which channels appear to be performing — and therefore which channels get more or less investment. Getting attribution right (or understanding its limitations) is a core data requirement for marketing analytics.
Attribution Models
**Last Touch (Last Click)**: All conversion credit to the final touchpoint before conversion. Simple to implement, directionally wrong for upper-funnel channels. Systematically under-credits awareness channels (display, social) and over-credits bottom-funnel channels (branded search, retargeting). Default in many ad platforms.
**First Touch (First Click)**: All credit to the first touchpoint in the conversion path. Useful for measuring which channels are best at introducing new users. Ignores the role of nurture and bottom-funnel channels that close conversions.
**Linear**: Equal credit to every touchpoint in the path. No touchpoint is more important than another. Simple and unbiased across funnel position, but often incorrect — a brand awareness impression and a high-intent search click are not equally valuable.
**Time Decay**: Touchpoints closer to conversion receive more credit, with exponential decay for earlier touchpoints. Rationale: recent actions are more causally related to conversion than actions weeks earlier. Common in B2B with long sales cycles where recent demo or proposal activity is more decisive than initial awareness.
**Position-Based (U-Shaped)**: 40% credit to first touch, 40% to last touch, 20% distributed across middle touchpoints. Explicitly values both acquisition (first touch) and conversion (last touch) while acknowledging the role of nurture.
**Data-Driven**: Machine learning model trained on observed conversion paths to assign credit based on the actual marginal contribution of each touchpoint. Requires sufficient conversion volume (typically 1000+ conversions) for the model to train meaningfully. Most accurate but least interpretable.
Implementing Rule-Based Attribution in SQL
Given a touchpoints table (user_id, channel, event_timestamp, is_conversion) and a conversion table, build attribution SQL for each model.
**Setup — touchpoints with path position**:
WITH touch_ordered AS (
SELECT
user_id,
channel,
event_timestamp,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_timestamp) AS touch_position,
COUNT(*) OVER (PARTITION BY user_id) AS total_touches
FROM touchpoints
WHERE event_timestamp <= conversion_timestamp
),
**Last Touch**:
SELECT user_id, channel, 1.0 AS credit
FROM touch_ordered
WHERE touch_position = total_touches
**First Touch**:
SELECT user_id, channel, 1.0 AS credit
FROM touch_ordered
WHERE touch_position = 1
**Linear**:
SELECT user_id, channel, 1.0 / total_touches AS credit
FROM touch_ordered
**Time Decay** (half-life of 7 days):
SELECT
user_id,
channel,
POW(0.5, DATEDIFF('day', event_timestamp, conversion_timestamp) / 7.0) AS raw_weight
FROM touch_ordered
Then normalise raw_weight to sum to 1.0 per user:
SELECT user_id, channel, raw_weight / SUM(raw_weight) OVER (PARTITION BY user_id) AS credit
FROM time_decay_raw
**Position-Based (U-Shaped)**:
SELECT
user_id,
channel,
CASE
WHEN touch_position = 1 AND total_touches = 1 THEN 1.0
WHEN touch_position = 1 THEN 0.4
WHEN touch_position = total_touches THEN 0.4
ELSE 0.2 / NULLIF(total_touches - 2, 0)
END AS credit
FROM touch_ordered
Handle single-touch paths (total_touches = 1) separately — assign full credit to the single touch.
Aggregating Attribution by Channel
Once you have per-touch credit values, aggregate to measure channel contribution:
SELECT
channel,
COUNT(DISTINCT user_id) AS attributed_conversions,
SUM(credit) AS credited_conversions,
SUM(credit * conversion_value) AS attributed_revenue
FROM attribution_credits
JOIN conversions USING (user_id)
GROUP BY channel
ORDER BY attributed_revenue DESC
Compare channels across models to see how their attributed performance changes. A channel that looks weak under last-touch but strong under first-touch is an upper-funnel awareness channel that introduces users who later convert through other channels.
Data-Driven Attribution
Data-driven attribution uses a Shapley value approach from game theory: for each conversion path, compute the marginal contribution of each channel by comparing conversion rates with and without that channel present in the path.
Implementing Shapley values from scratch in SQL is complex. For most organisations, the practical options are:
- **Google Analytics 4 data-driven model**: Available for accounts with sufficient conversions; accessible via GA4 API or BigQuery export
- **Google Ads attribution settings**: Channel-level data-driven attribution within Google Ads campaigns
- **Meta Attribution**: Meta provides its own data-driven attribution using pixel data and ad delivery signals
- **Northbeam, Triple Whale, Rockerbox**: Commercial marketing analytics platforms with data-driven attribution designed for direct-to-consumer brands
Data-driven attribution from ad platforms is self-serving — platforms attribute more credit to themselves. Cross-platform attribution requires independent tooling with access to all touchpoint data.
Attribution Model Selection
No attribution model is universally correct. Choose based on what question you are answering:
- **Measuring awareness channel impact**: First touch or position-based
- **Optimising bottom-funnel conversion**: Last touch
- **Long B2B sales cycles**: Time decay
- **Understanding full-funnel contribution**: Linear or data-driven
- **Budget allocation across channels**: Data-driven if volume supports it; position-based otherwise
Run multiple models in parallel and compare — channels that look consistently strong across models are genuinely performing. Channels that look strong in only one model (especially their own platform's reporting) are likely benefiting from model bias.
Incrementality and Attribution Limits
Attribution models are not causal — they measure correlation between touchpoints and conversions, not whether the touchpoint caused the conversion. A user who was going to convert anyway may encounter your retargeting ad; last-touch attribution credits that ad with a conversion it did not cause.
Incrementality testing (holdout tests) measures whether users exposed to a channel convert at higher rates than users not exposed. This is the causal question attribution models cannot answer. For high-spend channels, incrementality holdout tests are essential validation that attributed conversions are actually incremental.
Our BI strategy practice designs marketing analytics frameworks including multi-touch attribution and incrementality measurement — contact us to discuss your marketing data requirements.
A former Microsoft data architect audits your data foundation, identifies your top priorities, and sends you a written plan. Free. No pitch.
Book a Call →