Revenue Attribution and Multi-Touch Models

Attribution answers: "Which marketing channel deserves credit for this conversion?" Every sale is the result of multiple touchpoints — a customer might see a Facebook ad, click an email link, then convert via organic search three days later. Which channel gets the credit?

Attribution models answer this question differently, and the choice of model can completely change how marketing budgets are allocated. SQL handles all common attribution models natively.

Why Attribution Is Contested

Different stakeholders prefer different models:

• Facebook Ads team: Prefers last-click (because Facebook is often the last touch before conversion)
• Email team: Prefers first-touch (because email often introduces ec_customers to the brand)
• CMO: Wants data-driven multi-touch that reflects reality

The truth is that no model is objectively "correct" — they're all approximations. Running all three models and comparing gives you the most complete picture.

The Four Attribution Models

Dataset

-- ec_events: customer_id, event_type, created_at, properties (jsonb with channel info)
-- ec_orders: id, customer_id, created_at, total_amount
-- Channel touch ec_events have properties->>'channel' set (e.g., 'paid_search', 'email', 'organic')

Step 1 — Last-Touch Attribution

The simplest model: the last channel the customer interacted with before purchasing gets 100% credit. This is the default model in Google Analytics.

The query uses ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) to number each touch in reverse chronological order. WHERE recency_rank = 1 selects only the most recent touch before each conversion.

WITH purchases AS (
  SELECT customer_id, created_at AS purchase_time, total_amount
  FROM ec_orders
  WHERE status = 'completed'
),
touches AS (
  SELECT
    e.customer_id AS customer_id,
    e.created_at,
    ec.acquisition_channel AS channel,
    ROW_NUMBER() OVER (
      PARTITION BY e.customer_id
      ORDER BY e.created_at DESC
    ) AS recency_rank
  FROM ec_events e
  JOIN ec_customers ec ON ec.customer_id = e.customer_id
  JOIN purchases p ON p.customer_id = e.customer_id
    AND e.created_at <= p.purchase_time
  WHERE e.event_type IN ('view','purchase')
    AND ec.acquisition_channel IS NOT NULL
),
last_touch AS (
  SELECT customer_id, channel AS last_channel
  FROM touches
  WHERE recency_rank = 1
)
SELECT
  lt.last_channel,
  COUNT(DISTINCT p.customer_id) AS conversions,
  SUM(p.total_amount) AS attributed_revenue,
  ROUND(SUM(p.total_amount)::numeric / SUM(SUM(p.total_amount)) OVER () * 100, 2) AS revenue_share_pct
FROM last_touch lt
JOIN purchases p ON p.customer_id = lt.customer_id
GROUP BY lt.last_channel
ORDER BY attributed_revenue DESC;

What This Returns

Revenue attributed to each channel under last-touch rules. revenue_share_pct shows the percentage of total revenue each channel receives credit for. The limitation: this model ignores all the earlier touches that built awareness and consideration — which is why paid search often looks artificially powerful (ec_customers often search directly after seeing ads elsewhere).