Business Problem

Duolingo's growth marketing team is arguing with finance over which channels actually deserve credit for subscription revenue, because the current last-click model gives all the credit to email even though most users first discovered the app through a paid search ad weeks earlier. Build a multi-touch attribution query that distributes revenue credit across every touchpoint in a customer's journey (first touch, middle touches, last touch) instead of just the final one, broken down by channel, so the team can see each channel's true contribution and reallocate ad spend accordingly.

Dataset & Schema

Customer journey data with customer_id, touchpoint_channel, touchpoint_date, touchpoint_type (first/middle/last); conversion data with customer_id, subscription_date, subscription_value, cohort_month.

CREATE TABLE customer_journey (
  journey_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  touchpoint_channel TEXT,
  touchpoint_date DATE,
  touchpoint_type TEXT
);
CREATE TABLE subscriptions (
  subscription_id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  subscription_date DATE,
  subscription_value DECIMAL(10,2),
  subscription_type TEXT
);

SQL Solution

WITH customer_touchpoints AS (
  SELECT 
    s.customer_id,
    s.subscription_value,
    s.subscription_date,
    COUNT(DISTINCT cj.touchpoint_channel) as unique_channels,
    COUNT(cj.journey_id) as total_touchpoints,
    STRING_AGG(DISTINCT cj.touchpoint_channel, ' -> ' ORDER BY cj.touchpoint_date) as journey_path
  FROM subscriptions s
  LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id 
    AND cj.touchpoint_date < s.subscription_date
  GROUP BY s.customer_id, s.subscription_value, s.subscription_date
),
first_touch_attribution AS (
  SELECT 
    s.customer_id,
    s.subscription_value,
    MIN(cj.touchpoint_channel) KEEP (DENSE_RANK FIRST ORDER BY cj.touchpoint_date) as first_touch_channel,
    'First-Touch' as attribution_model
  FROM subscriptions s
  LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id 
    AND cj.touchpoint_date < s.subscription_date
  GROUP BY s.customer_id, s.subscription_value
),
last_touch_attribution AS (
  SELECT 
    s.customer_id,
    s.subscription_value,
    MAX(cj.touchpoint_channel) KEEP (DENSE_RANK LAST ORDER BY cj.touchpoint_date) as last_touch_channel,
    'Last-Touch' as attribution_model
  FROM subscriptions s
  LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id 
    AND cj.touchpoint_date < s.subscription_date
  GROUP BY s.customer_id, s.subscription_value
),
multi_touch_linear AS (
  SELECT 
    s.customer_id,
    cj.touchpoint_channel,
    s.subscription_value,
    COUNT(*) OVER (PARTITION BY s.customer_id) as channel_count,
    ROUND(s.subscription_value / COUNT(*) OVER (PARTITION BY s.customer_id), 2) as attributed_revenue,
    'Linear' as attribution_model
  FROM subscriptions s
  LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id 
    AND cj.touchpoint_date < s.subscription_date
  WHERE cj.journey_id IS NOT NULL
),
multi_touch_position_based AS (
  SELECT 
    s.customer_id,
    cj.touchpoint_channel,
    cj.touchpoint_type,
    s.subscription_value,
    CASE 
      WHEN cj.touchpoint_type = 'first' THEN ROUND(s.subscription_value * 0.40, 2)
      WHEN cj.touchpoint_type = 'last' THEN ROUND(s.subscription_value * 0.40, 2)
      WHEN cj.touchpoint_type = 'middle' THEN ROUND(s.subscription_value * 0.20 / 
        (COUNT(*) OVER (PARTITION BY s.customer_id, cj.touchpoint_type) - 2), 2)
      ELSE 0
    END as attributed_revenue,
    'Position-Based-40-20-40' as attribution_model
  FROM subscriptions s
  LEFT JOIN customer_journey cj ON s.customer_id = cj.customer_id 
    AND cj.touchpoint_date < s.subscription_date
  WHERE cj.journey_id IS NOT NULL
),
channel_summary_linear AS (
  SELECT 
    touchpoint_channel,
    'Linear' as model,
    COUNT(DISTINCT customer_id) as conversions,
    ROUND(SUM(attributed_revenue), 2) as total_attributed_revenue,
    ROUND(AVG(attributed_revenue), 2) as avg_per_conversion
  FROM multi_touch_linear
  WHERE touchpoint_channel IS NOT NULL
  GROUP BY touchpoint_channel
),
channel_summary_position AS (
  SELECT 
    touchpoint_channel,
    'Position-Based-40-20-40' as model,
    COUNT(DISTINCT customer_id) as conversions,
    ROUND(SUM(attributed_revenue), 2) as total_attributed_revenue,
    ROUND(AVG(attributed_revenue), 2) as avg_per_conversion
  FROM multi_touch_position_based
  WHERE touchpoint_channel IS NOT NULL
  GROUP BY touchpoint_channel
)
SELECT 
  touchpoint_channel,
  model,
  conversions,
  total_attributed_revenue,
  avg_per_conversion,
  ROUND(total_attributed_revenue / SUM(total_attributed_revenue) OVER (PARTITION BY model) * 100, 2) as revenue_percentage
FROM (
  SELECT * FROM channel_summary_linear
  UNION ALL
  SELECT * FROM channel_summary_position
)
ORDER BY model, total_attributed_revenue DESC;

Explanation