Google Ads: last-touch attribution in a 7-day window

Constraints

1. 1Each conversion is credited exactly once. The nine conversions here must add up to nine across the output.
2. 2The qualifying click is the latest one on or before converted_at with at most 7 days between them.
3. 3A click older than 7 days does not qualify, even if it is the user's only click.
4. 4A conversion with no qualifying click is reported under the campaign name 'direct'.
5. 5attributed_value is the sum of conversion_value for the conversions credited to that campaign, rounded to 2 decimal places.
6. 6Order by attributed_value descending, then campaign.

Worked example

User g1 clicked brand-search on 1 February, shopping on the 9th and display on the 12th, then converted for 120 on the 13th. Last touch inside the window is display, so display gets 120 and the other two campaigns get nothing. Join every earlier click instead and that one conversion is counted three times — 120 credited to each of three campaigns. Across the whole dataset the naive join reports 14 conversions worth 1360 against a real 9 worth 880. Conversions 103, 105 and 107 make the opposite point: an inner join drops them, so 330 of genuine revenue disappears rather than being labelled direct.

What this tests

Picking exactly one related row per fact, in the presence of a time window — and the double failure of the obvious join, which multiplies the conversions that have clicks and deletes the ones that do not.