SQL Attribution Patterns

Marketing attribution assigns conversion credit to the touchpoints that preceded it. Platform tools (GA4, Meta Ads Manager) do this for you, but they suffer from walled-garden bias, cross-device blindness, and silent methodology fallbacks. Building attribution in SQL gives you transparency, cross-platform visibility, and full control over the logic.

Every attribution model operates on the same input: a touchpoint table that joins marketing interactions to conversions within a lookback window. The models differ only in how they distribute the conversion’s revenue across those touchpoints.

The touchpoint table

All patterns below assume a touchpoint table with this shape:

The lookback window filter (WHERE touchpoint_timestamp >= TIMESTAMP_SUB(conversion_timestamp, INTERVAL N DAY)) scopes which touchpoints qualify. Set this based on your sales cycle: 7-14 days for impulse e-commerce, 30-45 days for considered purchases, 90-180 days for B2B.

First-touch and last-touch

Single-touch models assign 100% of credit to one touchpoint. The SQL pattern uses ROW_NUMBER() to pick either the earliest or latest touchpoint per conversion.

WITH touchpoints_numbered AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    touchpoint_timestamp,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp ASC  -- DESC for last-touch
    ) AS touch_position
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
)
SELECT
  user_id,
  transaction_id,
  channel,
  revenue AS attributed_revenue
FROM touchpoints_numbered
WHERE touch_position = 1

First-touch (ASC) answers “what channels fill the top of the funnel?” Use it for brand awareness and acquisition analysis. Last-touch (DESC) answers “what closes deals?” Use it for direct response and retargeting evaluation. The limitation of both: they ignore every other touchpoint in the journey.

Linear attribution

Linear distributes revenue equally across all touchpoints. Instead of picking a winner, every touchpoint gets the same share.

WITH touchpoints_counted AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    COUNT(*) OVER (
      PARTITION BY user_id, transaction_id
    ) AS total_touches
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
)
SELECT
  user_id,
  transaction_id,
  channel,
  revenue / total_touches AS attributed_revenue
FROM touchpoints_counted

Five touchpoints on a $100 conversion each get $20. Linear provides a balanced multi-touch view and works well as a default when you have no strong hypothesis about which journey positions matter most. The downside: it over-credits low-impact touchpoints in long journeys where a single email open sits alongside a high-intent paid search click.

Position-based (U-shaped)

Position-based models weight touchpoints by their location in the journey. The standard U-shaped model assigns 40% to first touch, 40% to last touch, and splits the remaining 20% equally among middle touchpoints.

WITH touchpoints_positioned AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, transaction_id
      ORDER BY touchpoint_timestamp ASC
    ) AS position,
    COUNT(*) OVER (
      PARTITION BY user_id, transaction_id
    ) AS total_touches
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
)
SELECT
  user_id,
  transaction_id,
  channel,
  CASE
    WHEN total_touches = 1 THEN 1.0
    WHEN total_touches = 2 THEN 0.5
    WHEN position = 1 THEN 0.4
    WHEN position = total_touches THEN 0.4
    ELSE 0.2 / (total_touches - 2)
  END * revenue AS attributed_revenue
FROM touchpoints_positioned

The CASE statement handles edge cases first: single-touchpoint conversions get 100%, two-touchpoint conversions split 50/50. Only then does the 40-20-40 logic apply. Skipping the edge case handling produces division-by-zero errors and incorrect weights.

Variations: B2B SaaS teams often use a W-shaped model (30-30-30-10) that adds weight to a key middle event like a demo request. High-consideration products may shift to 45-10-45 to emphasize endpoints further.

Time-decay attribution

Time-decay assigns more credit to touchpoints closer to conversion using exponential decay with a configurable half-life:

Weight(touchpoint) = 2^(-time_before_conversion / half_life)

With a 7-day half-life, a touchpoint from 7 days ago gets 50% weight, 14 days gets 25%, 21 days gets 12.5%.

WITH decay_weights AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    POW(
      0.5,
      TIMESTAMP_DIFF(conversion_timestamp, touchpoint_timestamp, HOUR)
        / (7.0 * 24)  -- 7-day half-life in hours
    ) AS raw_weight
  FROM touchpoints
  WHERE touchpoint_timestamp >= TIMESTAMP_SUB(
    conversion_timestamp, INTERVAL 30 DAY
  )
),
normalized AS (
  SELECT
    *,
    SUM(raw_weight) OVER (
      PARTITION BY user_id, transaction_id
    ) AS total_weight
  FROM decay_weights
)
SELECT
  user_id,
  transaction_id,
  channel,
  (raw_weight / total_weight) * revenue AS attributed_revenue
FROM normalized

The normalization step (dividing by total_weight) ensures attributed revenue sums to actual revenue per conversion. Without it, the raw weights are relative, not absolute.

Choose the half-life to match your sales cycle: 3-7 days for impulse e-commerce, 7-14 days for considered B2C, 14-30 days for B2B mid-market. A half-life that’s too short under-credits awareness channels; too long approaches linear attribution.

Algorithmic models: Markov chains and Shapley values

Heuristic models (everything above) assign credit based on assumptions about position or timing. Algorithmic models calculate credit from observed conversion patterns.

Markov chain attribution models journeys as state transitions between channels. It measures the removal effect: how much does conversion probability drop when a channel is removed entirely? If removing paid search drops overall conversion probability from 50% to 35%, paid search gets a removal effect of 30%. Removal effects are normalized to sum to 100% of conversions.

The SQL portion extracts journey paths and calculates transition probabilities between channel states. The matrix operations for removal effect calculation typically move to Python (ChannelAttribution or marketing-attribution-models packages), since SQL is not well-suited to iterative matrix algebra.

Shapley value attribution comes from cooperative game theory. It calculates each channel’s average marginal contribution across all possible subsets of channels. Shapley values satisfy strong fairness properties (credits sum to total, equal contributors get equal credit, zero-value channels get nothing), but computation scales at 2^n for n channels. Monte Carlo approximation makes this tractable for real channel counts.

Both approaches require more data than heuristic models: aim for several hundred conversions minimum, and collapse low-volume channels (under 2% of touchpoints) into an “Other” group to keep transition matrices stable. See Attribution Channel Grouping Strategy for guidance on channel grouping decisions.

Choosing a model

Running multiple models in parallel and using disagreement as signal is a common approach. When all models rank a channel similarly, confidence is higher. When rankings diverge sharply, the divergence can reveal either genuine insight or a data quality problem worth investigating.

Validation

Every multi-touch model should pass a basic integrity check: attributed revenue per conversion must sum to actual revenue. In dbt, a singular test catches drift:

-- tests/attribution_revenue_integrity.sql
SELECT transaction_id, SUM(attributed_revenue) AS attributed, MAX(revenue) AS actual
FROM {{ ref('mrt__attribution__linear') }}
GROUP BY transaction_id
HAVING ABS(SUM(attributed_revenue) - MAX(revenue)) > 0.01

For incremental attribution models, apply a lookback buffer (re-process the last N days) to handle late-arriving conversions and retroactive touchpoint updates that would otherwise create silent gaps in attributed revenue.