touches(user_id, channel, touched_at) and conversions(conversion_id, user_id, converted_at, revenue)

Split each conversion's revenue across the touches in the 14 days up to and including it. A touch d whole days before the conversion gets raw weight 1 / (1 + d), the weights are normalised so they sum to 1 within that conversion, and revenue is allocated in proportion. A conversion with no eligible touch gives all of its revenue to 'direct'.

Return channel and allocated_revenue rounded to two decimals, ordered by channel.

Every conversion's revenue is accounted for exactly once, so the allocated total equals the revenue total. Normalising across the wrong partition is what breaks that, and it is worth checking the sum when you are done.

Tables

conversions

conversion_id  user_id  converted_at         revenue
-------------  -------  -------------------  -------
1              1        2026-06-10 10:00:00  120.00
2              2        2026-06-10 10:00:00  60.00
3              3        2026-06-10 10:00:00  40.00

touches

user_id  channel  touched_at
-------  -------  -------------------
1        search   2026-06-10 08:00:00
1        email    2026-06-06 08:00:00
1        social   2026-05-21 08:00:00
2        email    2026-06-09 08:00:00
2        email    2026-06-08 08:00:00
3        display  2026-01-01 08:00:00

Expected result

channel  allocated_revenue
-------  -----------------
direct   40.00
email    80.00
search   100.00