From: Theo Grant, marketing associate
Question: Which campaign brings in orders most cheaply?
"Each order can follow several campaign clicks. Our rule: a click counts if it happened in the 14 days up to and including the order date. Show me revenue by campaign under last touch (the latest qualifying click gets the credit) and first touch (the earliest), then the cost per attributed order under last touch. Leave cancelled orders out."
Data: orders, campaign_touches (UTC), campaigns (budget is the total spend).
Deliverable: the comparison, then the campaign with the lowest cost per order.
Attribution decides which marketing touch gets credit for a sale. Two simple models frame most discussions: last touch credits the click right before the purchase, first touch the first click that brought the customer in. They often disagree, which is the point of showing both.
The build: join each order to its customer's qualifying clicks (a range join on dates), number the clicks per order from both ends with ROW_NUMBER, and keep number 1. Orders with no qualifying click are unattributed, not errors.