Exponential Half-Life Multi-Touch Marketing Attribution Normalized to 100%

WITH weighted_touches AS
  (SELECT t.touch_id,
          t.channel,
          c.order_revenue,
          pow(0.5, date_diff('second', t.click_time, c.conversion_time) / (7.0 * 86400.0)) AS raw_weight
   FROM purchase_conversions c
   JOIN touchpoints t ON c.user_id = t.user_id
   AND t.click_time <= c.conversion_time
   AND t.click_time >= c.conversion_time - INTERVAL 30 days),
     total_weight AS
  (SELECT *,
          sum(raw_weight) OVER () AS sum_weights
   FROM weighted_touches)
SELECT touch_id,
       channel,
       round(order_revenue * (raw_weight / sum_weights), 2) AS attributed_revenue
FROM total_weight
ORDER BY touch_id ASC;

CREATE TABLE touchpoints (touch_id INT, user_id INT, channel VARCHAR, click_time TIMESTAMP);


CREATE TABLE purchase_conversions (order_id INT, user_id INT, order_revenue decimal(10, 2),
                                                                            conversion_time TIMESTAMP);


INSERT INTO touchpoints
VALUES (1, 42, 'Google Search', '2026-03-01 12:00:00'),
       -- 14 days before (weight = 0.25)
 (2, 42, 'Facebook Ad', '2026-03-08 12:00:00'),
 -- 7 days before  (weight = 0.50)
 (3, 42, 'Email Promo', '2026-03-15 12:00:00'); -- 0 days before  (weight = 1.00)


INSERT INTO purchase_conversions
VALUES (101, 42, 350.00, '2026-03-15 12:00:00');