Marketing Attribution Lineage

First-touch attribution joining sessions, conversions, and campaigns. The lineage shows which session columns become which attribution columns.

The lineage

The SQL

CREATE TABLE sessions (
  session_id STRING,
  user_id STRING,
  campaign_id STRING,
  started_at TIMESTAMP
);

CREATE TABLE conversions (
  conversion_id STRING,
  user_id STRING,
  amount NUMBER(12,2),
  converted_at TIMESTAMP
);

CREATE TABLE campaigns (
  campaign_id STRING,
  channel STRING,
  campaign_name STRING
);

CREATE OR REPLACE VIEW v_first_touch AS
WITH ranked AS (
  SELECT
    s.user_id,
    s.session_id,
    s.campaign_id,
    s.started_at,
    ROW_NUMBER() OVER (PARTITION BY s.user_id ORDER BY s.started_at) AS rn
  FROM sessions s
)
SELECT
  c.conversion_id,
  c.user_id,
  c.amount AS conversion_amount,
  cm.channel AS first_touch_channel,
  cm.campaign_name AS first_touch_campaign
FROM conversions c
JOIN ranked r ON r.user_id = c.user_id AND r.rn = 1
JOIN campaigns cm ON cm.campaign_id = r.campaign_id;

What the lineage shows

• first_touch_channel traces back through ranked → sessions → campaigns.
• conversion_amount is a clean pass-through from conversions.amount.
• Window functions are tracked as part of the lineage of the projected columns.