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

Attribute each conversion to the most recent touch in the 30 days up to and including the conversion whose channel is not 'direct'. When there is no such touch, attribute it to 'direct'.

Return conversion_id, attributed_channel and revenue, ordered by conversion_id.

A 'direct' touch sitting closer to the conversion than a paid one must not win, which is the whole point of the rule: direct traffic is where attribution gives up, not a channel that earns credit.

Tables

conversions

conversion_id  user_id  converted_at         revenue
-------------  -------  -------------------  -------
1              1        2026-06-10 10:00:00  100.00
2              2        2026-06-10 10:00:00  50.00
3              3        2026-06-10 10:00:00  25.00

touches

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

Expected result

conversion_id  attributed_channel  revenue
-------------  ------------------  -------
1              search              100.00
2              direct              50.00
3              direct              25.00