Multi-touch attribution with TrackLayer: from raw events to warehouse queries

Why in-platform attribution lies

Platform dashboards usually answer a last-click or platform-native credit question, not a business-wide journey question. Even when a UI says data-driven, the model still lives inside that platform’s own click graph, event eligibility rules, and reporting window. The result is operationally useful, but it systematically overstates whichever platform happened to be closest to the conversion.

Self-reporting makes the problem worse. Meta gives Meta credit for Meta interactions. Google gives Google credit for Google interactions. TikTok, Pinterest, Reddit, and affiliate platforms do the same. If a customer saw paid social, clicked paid search, then returned direct and bought, multiple tools may each claim a legitimate version of the same purchase because none of them owns the complete path.

iOS ATT reduced the reliability of view-through attribution even further. Once cross-app identity got weaker, modeled reporting became more important and impression-based credit became harder to verify at the user level. Clicks still carry explicit context more often than views do, so post-ATT readouts tend to bias toward click-observable channels and away from view-heavy social influence.

The MTA pipeline

A credible MTA system is a sequence of data engineering steps, not a dashboard toggle. TrackLayer sits at the front of the pipeline by preserving the event facts. The warehouse owns the logic that turns those facts into journeys and credit.

The identity step deserves special attention. Most teams do not have a single stable user ID on the first visit, so the graph is built gradually from anonymous browser identifiers, email hashes, order identifiers, and CRM links. Union-find is a practical data structure here because it handles transitive merges well: if an anonymous cookie later maps to a checkout email, and that email later maps to a CRM contact, the full connected component can be resolved into one person key for attribution.

Warehouse SQL template

This template computes time-decay MTA from tracklayer.events with a 30-day lookback and a seven-day half-life. It is written in BigQuery style first, but the same steps translate cleanly to Snowflake by swapping JSON and timestamp functions.

/* Time-decay MTA template for BigQuery with Snowflake notes inline */
/* Replace JSON_VALUE with payload:value::string and TIMESTAMP_DIFF with DATEDIFF in Snowflake */
WITH base_events AS (
  SELECT
    event_id,
    event_name,
    event_time,
    COALESCE(
      NULLIF(JSON_VALUE(custom_data, '$.resolved_user_id'), ''),
      NULLIF(JSON_VALUE(custom_data, '$.customer_id'), ''),
      NULLIF(user_data.external_id, ''),
      NULLIF(user_data.em_hash, ''),
      NULLIF(user_data.anonymous_id, '')
    ) AS person_key,
    LOWER(
      COALESCE(
        NULLIF(JSON_VALUE(custom_data, '$.channel'), ''),
        NULLIF(JSON_VALUE(custom_data, '$.utm_source'), ''),
        NULLIF(JSON_VALUE(custom_data, '$.source'), ''),
        'direct'
      )
    ) AS channel,
    SAFE_CAST(JSON_VALUE(custom_data, '$.value') AS NUMERIC) AS event_value,
    JSON_VALUE(custom_data, '$.order_id') AS order_id
  FROM \`tracklayer.events\`
  WHERE event_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 90 DAY)
    AND event_name IN (
      'page_view',
      'session_start',
      'view_content',
      'add_to_cart',
      'begin_checkout',
      'purchase',
      'lead'
    )
),
touchpoints AS (
  SELECT
    event_id,
    event_time,
    person_key,
    channel
  FROM base_events
  WHERE person_key IS NOT NULL
    AND event_name != 'purchase'
),
conversions AS (
  SELECT
    event_id AS conversion_event_id,
    order_id,
    person_key,
    event_time AS conversion_time,
    COALESCE(event_value, 0) AS conversion_value
  FROM base_events
  WHERE person_key IS NOT NULL
    AND event_name = 'purchase'
),
eligible_paths AS (
  SELECT
    c.conversion_event_id,
    c.order_id,
    c.person_key,
    c.conversion_time,
    c.conversion_value,
    t.event_id AS touch_event_id,
    t.channel,
    t.event_time AS touch_time,
    TIMESTAMP_DIFF(c.conversion_time, t.event_time, HOUR) AS hours_before_conversion
  FROM conversions c
  JOIN touchpoints t
    ON c.person_key = t.person_key
   AND t.event_time <= c.conversion_time
   AND t.event_time >= TIMESTAMP_SUB(c.conversion_time, INTERVAL 30 DAY)
),
scored_paths AS (
  SELECT
    conversion_event_id,
    order_id,
    person_key,
    conversion_time,
    conversion_value,
    touch_event_id,
    channel,
    touch_time,
    hours_before_conversion,
    POW(0.5, SAFE_DIVIDE(hours_before_conversion, 24 * 7)) AS raw_weight
  FROM eligible_paths
),
normalized_paths AS (
  SELECT
    conversion_event_id,
    order_id,
    person_key,
    conversion_time,
    conversion_value,
    touch_event_id,
    channel,
    touch_time,
    hours_before_conversion,
    raw_weight,
    SAFE_DIVIDE(
      raw_weight,
      SUM(raw_weight) OVER (PARTITION BY conversion_event_id)
    ) AS normalized_weight
  FROM scored_paths
),
touchpoint_credit AS (
  SELECT
    conversion_event_id,
    order_id,
    person_key,
    conversion_time,
    channel,
    touch_event_id,
    touch_time,
    hours_before_conversion,
    normalized_weight,
    conversion_value * normalized_weight AS attributed_revenue
  FROM normalized_paths
),
channel_rollup AS (
  SELECT
    channel,
    COUNT(DISTINCT conversion_event_id) AS conversions_touched,
    COUNT(DISTINCT touch_event_id) AS touchpoints_count,
    SUM(attributed_revenue) AS revenue_credit,
    AVG(hours_before_conversion) AS avg_hours_before_conversion
  FROM touchpoint_credit
  GROUP BY channel
)
SELECT
  channel,
  conversions_touched,
  touchpoints_count,
  ROUND(revenue_credit, 2) AS revenue_credit,
  ROUND(avg_hours_before_conversion, 1) AS avg_hours_before_conversion,
  ROUND(
    SAFE_DIVIDE(
      revenue_credit,
      SUM(revenue_credit) OVER ()
    ) * 100,
    2
  ) AS revenue_share_pct
FROM channel_rollup
ORDER BY revenue_credit DESC;

The point of the template is not the exact decay curve. The point is the flow: identify conversions, pull the eligible touches, assign a recency weight, normalize to 100 percent per conversion, then roll the credit back up by channel. Once that works, you can add campaign, ad set, product, geography, or net revenue logic on top.

Markov chain attribution

Markov attribution models a journey as a set of transitions between states such as Start, Paid Social, Organic Search, Email, Direct, Conversion, and Null. Instead of choosing a fixed rule like linear or position-based weighting, it estimates how the probability of conversion changes when one channel is removed from the path graph. That removal effect becomes the channel’s credit.

This makes Markov useful when channel order matters and the middle of the journey is more than filler. It can capture interaction patterns that simple rules miss, but it still depends on clean path construction, sane lookback windows, and a stable taxonomy. If the paths are noisy, the transition matrix will be noisy too.

# Python skeleton
import pandas as pd

journeys = pd.read_parquet("mta_journeys.parquet")
states = journeys.groupby("conversion_id")["channel"].apply(list)

# 1. Add START and CONVERSION / NULL states
# 2. Estimate transition matrix
# 3. Compute baseline conversion probability
# 4. Remove one channel at a time
# 5. Attribute credit from removal effect

# R skeleton
library(ChannelAttribution)

paths <- read.csv("mta_paths.csv")

# Expected columns: path, conversions, conversion_value
result <- markov_model(
  Data = paths,
  var_path = "path",
  var_conv = "conversions",
  var_value = "conversion_value",
  out_more = TRUE
)

print(result$result)

Shapley value attribution

Shapley value attribution borrows from cooperative game theory. It asks how much each channel contributes on average across all possible channel coalitions. In plain terms, it measures the marginal contribution of Paid Social, Search, Email, or Affiliate not just in one path order, but across many combinations of who appeared with whom.

The attraction is fairness. The warning is computational complexity. Once you move beyond a handful of channels, the number of subsets grows fast. Five channels can still be manageable. Past that, exact Shapley calculations become expensive enough that most teams either constrain the channel set or use approximation methods.

When to use each model

Platform-friendly output

A warehouse model is only half the story. The most useful MTA teams publish an internal truth table, then create a second output that ad platforms can ingest. The practical pattern is to keep the original platform conversion event for measurement parity, while also sending adjusted values derived from warehouse attribution.

For Meta, that often means sending server-side purchase or lead events with a value field that reflects the warehouse credit you want the campaign to learn from, or importing offline results tied back to a click identifier where your workflow supports it. For Google, the equivalent is usually an enhanced conversion or offline conversion adjustment path keyed to the relevant click or customer identifiers. The exact import surface varies by account setup, but the principle is stable: the warehouse computes the value, the platform receives a compatible event or adjustment record.

Do not send one fractional touchpoint row for every impression and expect bidding systems to stay clean. Aggregate first. Most teams collapse warehouse MTA into one platform-friendly value per conversion or per eligible click, then preserve the full touchpoint-level detail only in BI and analytics tables.

Cost estimates

BigQuery cost depends on bytes scanned, not the number of rows alone. A 10 million event table with an average scanned row width around 1 KB is roughly a 10 GB scan, which is about $0.05 on on-demand pricing. If your rows are wider because of nested JSON or you skip partition filters, the same query can easily become a 40 to 80 GB scan and still remain inexpensive per run, but the bad habits add up once dashboards and scheduled jobs multiply.

On Snowflake, the more relevant planning unit is warehouse time. A weekly MTA recompute on a small or extra-small warehouse often lands in the range of roughly 0.25 to 1 credit if the model is partition-aware and the touchpoint join is bounded to a recent window. The real cost spike usually comes from repeated ad hoc exploration, not the scheduled attribution job itself.

Common mistakes

FAQ