Position-Based Attribution Models

Position-based attribution assigns credit based on where a touchpoint falls in the customer journey, rather than treating all touches equally (like linear attribution) or using a single touch. The core idea: the touchpoint that introduced a customer and the touchpoint that closed the deal probably matter more than the random email they opened in between.

The U-shaped model (40-20-40)

The most common position-based model distributes credit as follows:

• First touchpoint: 40%
• Last touchpoint: 40%
• All middle touchpoints: 20% split equally

The formula:

Credit(first) = 0.40
Credit(last) = 0.40
Credit(middle_i) = 0.20 / (n - 2)

For a customer with 5 touchpoints and a $100 conversion:

• Touch 1: $40 (40%)
• Touch 2: $6.67 (20% / 3)
• Touch 3: $6.67 (20% / 3)
• Touch 4: $6.67 (20% / 3)
• Touch 5: $40 (40%)

This works well for most B2C and B2B scenarios where you want to value both the channel that drove initial awareness and the channel that triggered conversion. It’s a pragmatic middle ground — it acknowledges that journey endpoints carry more strategic weight while still giving middle touches some credit rather than ignoring them entirely.

The W-shaped model (30-30-30-10)

B2B companies with longer sales cycles often use W-shaped attribution, which adds weight to a key middle touchpoint like lead creation or a demo request:

Credit(first) = 0.30
Credit(key_middle) = 0.30
Credit(last) = 0.30
Credit(other) = 0.10 / (n - 3)

W-shaped requires identifying that key middle moment in your data, which adds implementation complexity. You need a reliable way to flag which touchpoint represents the qualification or intent signal — a form fill, a demo booking, a pricing page visit. If that event isn’t cleanly tracked, the model degrades to a noisy approximation.

Edge cases matter

Single-touchpoint conversions get 100% credit to that touchpoint. Two-touchpoint conversions split credit 50/50. Your SQL needs to handle these explicitly, or you’ll get incorrect results.

The reason is arithmetic: if you have 2 touchpoints and try to apply the 40-20-40 formula, the middle weight calculation becomes 0.20 / (2 - 2) — a division by zero. If you have 1 touchpoint and try to assign 40% to “first” and 40% to “last” (which is the same touchpoint), you only attribute 80% of the revenue.

Always handle edge cases before the main formula in your CASE statement.

BigQuery implementation

A complete implementation of the 40-20-40 U-shaped model:

WITH touchpoints_positioned AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    revenue,
    touchpoint_timestamp,
    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

What each piece does:

• ROW_NUMBER() identifies each touchpoint’s position in the journey (see Window Function Patterns for Analytics SQL)
• COUNT(*) OVER() gives the total touchpoints per conversion
• The CASE statement handles edge cases first (1 and 2 touchpoints), then applies the 40-20-40 split
• Multiplying the weight by revenue produces the attributed amount

Industry weight variations

The 40-20-40 split isn’t universal. Adjust based on your business model and sales cycle:

The weights are business decisions, not mathematical truths. A 40-20-40 split says “we believe first and last touches are equally important and together account for 80% of the value.” That’s a hypothesis. You can test it by running position-based alongside time-decay and other models, then looking at where they agree and disagree.

When to use position-based attribution

Position-based models work best when:

• You believe first and last touches are genuinely more impactful than middle touches
• Your funnel has clear “discovery” and “conversion” moments
• You want to credit both awareness and closing channels
• Sales cycles are relatively consistent in length

If your sales cycles vary widely — some customers convert in a day, others take three months — position-based can be misleading. A touchpoint that’s “first” in a 2-day journey and “first” in a 90-day journey carry very different strategic meaning but get the same weight. In that case, time-decay attribution may better reflect what’s actually happening.

For implementation in dbt with configurable weights, see dbt Weighted Attribution Models.