Forum Discussion
Range Lookup for Marketing Attribution
Dear Community Forum,
I would like to run a formula that assigns a last touch attribtion logic of marketing activity to my orders.
I have:
- an Order table showing which cleints ordered when
- a Marketing table showing what marketing material cleints recieved when
What i would like is a calcualted column either in DAX or maybe done in PowerQuery that:
- Attributes transactions in the Order table to the marketing activity the cleint most recently received (i.e. the marketing activity received closest to the transaction pre-transaction -- it can't simply be closest marketing activity received as it would make no sense to attribute a sale to a marketing activity they recieve post purchase
- This means our attribution windon for a piece of marketing is open from recipit of the marketing activity until the client receives a new piece of marketing OR in the event a client recieves only one piece of marketing activity there should be a default end to the attribution window of 90 days
Below is an illistration of what I am trying to achieve.
I've been trying to solve this problem for 3 days. The closest I got was using a neat range lookup i found from Pragmatic Studios:
Range Lookup in Power BI in the Power Query Editor - YouTube
This approach however just left my PowerQuery editor spinning and I think there is a more elegant (simple?) solution.
Thank you,
Stephen
MARKETING
| Client | Date of Marketing Activity | Marketing Activity |
| 123 | 31/12/2022 | 50% off letter |
| 123 | 03/01/2023 | New Year inspiration letter |
| 124 | 31/12/2022 | 50% off letter |
| 125 | 06/01/2023 | New Year inspiration letter |
| 125 | 07/01/2023 | New Year inspiration letter |
| 126 | 01/01/2016 | Welcome to 2016 |
ORDERS
| Transaction Date | Client | Revenue |
| 01/01/2023 | 123 | 5 |
| 02/01/2023 | 124 | 5 |
| 03/01/2023 | 124 | 5 |
| 04/01/2023 | 123 | 5 |
| 05/01/2023 | 125 | 5 |
| 05/01/2023 | 126 | 5 |
Try this calculated column in the Orders table. There is no relationship between these tables in this example.
Marketing Activity = VAR vTransactionDate = Orders[Transaction Date] VAR vClient = Orders[Client] VAR vMarketingTable = FILTER ( Marketing, Marketing[Client] = vClient && Marketing[Date of Marketing Activity] < vTransactionDate && Marketing[Date of Marketing Activity] >= vTransactionDate - 90 ) VAR vMaxDateMarketingActivity = MAXX ( vMarketingTable, Marketing[Date of Marketing Activity] ) VAR vResult = MAXX ( FILTER ( vMarketingTable, Marketing[Date of Marketing Activity] = vMaxDateMarketingActivity ), Marketing[Marketing Activity] ) RETURN IF ( ISBLANK ( vResult ), "NA", vResult )
3 Replies
- DataInsights
Super User
Try this calculated column in the Orders table. There is no relationship between these tables in this example.
Marketing Activity = VAR vTransactionDate = Orders[Transaction Date] VAR vClient = Orders[Client] VAR vMarketingTable = FILTER ( Marketing, Marketing[Client] = vClient && Marketing[Date of Marketing Activity] < vTransactionDate && Marketing[Date of Marketing Activity] >= vTransactionDate - 90 ) VAR vMaxDateMarketingActivity = MAXX ( vMarketingTable, Marketing[Date of Marketing Activity] ) VAR vResult = MAXX ( FILTER ( vMarketingTable, Marketing[Date of Marketing Activity] = vMaxDateMarketingActivity ), Marketing[Marketing Activity] ) RETURN IF ( ISBLANK ( vResult ), "NA", vResult ) That is genius.
What an elegant solution.
Applied it to my model and it works beautifully at scale.
I owe you a beer.
- DataInsights
Excellent, glad to hear that works!
