Advertising budgets are allocated based on which marketing channel drove each purchase. Under last-touch attribution, all credit for a conversion goes to the most recent channel the user interacted with before buying. Using the purchases and marketing_touches tables, return purchase_id, user_id, amount, last_touch_channel, and last_touch_time, ordered by purchase_id.
purchases
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| amount | NUMERIC |
| purchase_time | TIMESTAMP |
marketing_touches
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| channel | TEXT |
| touch_time | TIMESTAMP |
purchases
| id | user_id | amount | purchase_time |
|---|---|---|---|
| 1 | 1 | 150.00 | 2024-01-05 14:00:00 |
| 2 | 2 | 80.00 | 2024-01-08 11:00:00 |
marketing_touches
| id | user_id | channel | touch_time |
|---|---|---|---|
| 1 | 1 | 2024-01-01 09:00:00 | |
| 2 | 1 | paid_search | 2024-01-03 10:00:00 |
| 3 | 1 | social | 2024-01-06 08:00:00 |
| 4 | 2 | organic | 2024-01-02 12:00:00 |
| 5 | 2 | 2024-01-07 15:00:00 |
| purchase_id | user_id | amount | last_touch_channel | last_touch_time |
|---|---|---|---|---|
| 1 | 1 | 150.00 | paid_search | 2024-01-03 10:00:00 |
| 2 | 2 | 80.00 | 2024-01-07 15:00:00 |
User 1 purchased on Jan 5; their most recent touch before purchase was paid_search on Jan 3 (the social touch on Jan 6 is after the purchase, so it is excluded). User 2 purchased on Jan 8; their most recent prior touch was email on Jan 7.