Given a table of marketing campaigns and a table of user bookings, write a query to attribute bookings to the correct campaign using a first-touch attribution logic for StyleSeat.
Use campaign touches that occurred at or before each booking. Include bookings without an eligible campaign touch, and resolve simultaneous touches using the lowest touch identifier.
booking_at, then booking_id.booking_id, user_id, booking_at, booking_amount, attributed_campaign_id, attributed_campaign_name, and first_touch_at.| Column | Type | Description |
|---|---|---|
| campaign_touch_idPK | INT | Unique identifier for a campaign touch |
| user_id | VARCHAR(20) | StyleSeat user exposed to the campaign |
| campaign_id | VARCHAR(20) | Marketing campaign identifier |
| campaign_name | VARCHAR(100) | Human-readable campaign name |
| channel | VARCHAR(40) | Marketing channel that generated the touch |
| touch_at | TIMESTAMP | Timestamp when the user received or interacted with the campaign |
| Column | Type | Description |
|---|---|---|
| booking_idPK | INT | Unique booking identifier |
| user_id | VARCHAR(20) | StyleSeat user who made the booking |
| booking_at | TIMESTAMP | Timestamp when the booking was created |
| booking_amount | NUMERIC(10,2) | Booking value in dollars |