dbt Weighted Attribution Models

Position-based and time-decay attribution models use weights that should be configurable, not hard-coded. dbt variables make this clean: define weights in dbt_project.yml, reference them with var() in model SQL, and override them at runtime for experimentation. This note covers the dbt-specific implementation on top of the core concepts in Position-Based Attribution Models and Time-Decay Attribution Model.

Project structure

Add position-based and time-decay models alongside your existing attribution marts:

models/
├── intermediate/
│   └── int__touchpoints.sql
├── marts/attribution/
│   ├── mrt__attribution_first_touch.sql
│   ├── mrt__attribution_last_touch.sql
│   ├── mrt__attribution_linear.sql
│   ├── mrt__attribution_position_based.sql   # New
│   └── mrt__attribution_time_decay.sql       # New

Each model follows the mart naming convention and reads from the same intermediate touchpoint model, maintaining consistency with the rest of the attribution suite.

Position-based model with configurable weights

Use var() to make the first-touch and last-touch weights configurable. The middle weight derives from whatever’s left:

-- mrt__attribution_position_based.sql
{% set first_touch_weight = var('attribution_first_weight', 0.4) %}
{% set last_touch_weight = var('attribution_last_weight', 0.4) %}
{% set middle_weight = 1.0 - first_touch_weight - last_touch_weight %}

WITH touchpoints_positioned AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    source,
    medium,
    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 {{ ref('int__touchpoints') }}
)
SELECT
  user_id,
  transaction_id,
  channel,
  source,
  medium,
  touchpoint_timestamp,
  CASE
    WHEN total_touches = 1 THEN 1.0
    WHEN total_touches = 2 THEN 0.5
    WHEN position = 1 THEN {{ first_touch_weight }}
    WHEN position = total_touches THEN {{ last_touch_weight }}
    ELSE {{ middle_weight }} / (total_touches - 2)
  END * revenue AS attributed_revenue,
  'position_based' AS attribution_model
FROM touchpoints_positioned

The {% set middle_weight = 1.0 - first_touch_weight - last_touch_weight %} line is the key design choice. By deriving the middle weight rather than configuring it independently, you guarantee weights sum to 1.0. If someone sets first and last to 0.45 each, middle automatically becomes 0.10. No validation logic needed.

The edge case handling (total_touches = 1, total_touches = 2) is critical. Without it, single-touchpoint conversions only get 40% credit and two-touchpoint conversions trigger a division-by-zero for the middle weight calculation. See Position-Based Attribution Models for the full explanation.

Time-decay model with configurable half-life

-- mrt__attribution_time_decay.sql
{% set half_life_days = var('attribution_half_life_days', 7) %}

WITH decay_weights AS (
  SELECT
    user_id,
    transaction_id,
    channel,
    source,
    medium,
    revenue,
    touchpoint_timestamp,
    conversion_timestamp,
    POW(
      0.5,
      TIMESTAMP_DIFF(conversion_timestamp, touchpoint_timestamp, HOUR) / ({{ half_life_days }} * 24.0)
    ) AS raw_weight
  FROM {{ ref('int__touchpoints') }}
),
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,
  source,
  medium,
  touchpoint_timestamp,
  (raw_weight / total_weight) * revenue AS attributed_revenue,
  'time_decay' AS attribution_model
FROM normalized

The normalization step (dividing by total_weight) ensures attributed revenue sums to actual revenue per conversion. This makes time-decay inherently self-correcting: regardless of the half-life value, the math always balances. That’s a nice property compared to position-based, where you have to manually guarantee the weights sum correctly.

Configuration in dbt_project.yml

Centralize all attribution parameters:

vars:
  attribution_first_weight: 0.4
  attribution_last_weight: 0.4
  attribution_half_life_days: 7

This gives you three benefits:

1. Single source of truth. Anyone on the team can see the current weights without reading SQL.
2. Easy experimentation. Override at runtime: dbt run --vars '{"attribution_half_life_days": 14}' to test a different half-life without changing code.
3. Audit trail. Changes to dbt_project.yml are tracked in Git, so you know when weights changed and why.

To test different weight configurations systematically, run the model with several parameter values and compare channel rankings. If rankings are stable across a range of half-lives (say 5 to 14 days), the exact parameter matters less. If they shift dramatically, invest more thought into getting the right value — or accept that the sensitivity itself is informative.

Testing weighted models

Your tests should verify that weights sum correctly. The core integrity check: attributed revenue per conversion must equal actual revenue. This test catches calculation errors in the weighting logic:

-- tests/attribution_weights_sum_to_revenue.sql
WITH model_totals AS (
  SELECT
    transaction_id,
    SUM(attributed_revenue) AS total_attributed
  FROM {{ ref('mrt__attribution_position_based') }}
  GROUP BY transaction_id
),
actual_revenue AS (
  SELECT
    transaction_id,
    MAX(revenue) AS actual_revenue
  FROM {{ ref('int__touchpoints') }}
  GROUP BY transaction_id
)
SELECT
  m.transaction_id,
  m.total_attributed,
  a.actual_revenue
FROM model_totals m
JOIN actual_revenue a
  ON m.transaction_id = a.transaction_id
WHERE ABS(m.total_attributed - a.actual_revenue) > 0.01

This returns rows only when attribution doesn’t match actual revenue. Zero rows means the model is balanced. Any rows identify exactly which conversions have calculation errors, making debugging straightforward.

Write the same test for the time-decay model. The normalization step should guarantee balance, but floating-point arithmetic can introduce tiny errors — the 0.01 tolerance handles that. For a more comprehensive approach using the comparison model, see the revenue integrity tests that validate all models simultaneously.

Integrating with the comparison layer

Once these models exist, add them to the comparison model union. The attribution_model column in each model’s output ('position_based' and 'time_decay') becomes the model_type discriminator in the comparison table, automatically appearing in dashboard dropdowns without any BI layer changes.

The comparison pattern makes adding these models straightforward: create the model, add a CTE to the comparison union. Choosing the right weights and half-life requires business judgment about how marketing attribution works.