Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
StephenClarke
Frequent Visitor

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. 

StephenClarke_0-1674294858685.png

 

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

ClientDate of Marketing Activity Marketing Activity
12331/12/202250% off letter
12303/01/2023New Year inspiration letter
12431/12/202250% off letter
12506/01/2023New Year inspiration letter
12507/01/2023New Year inspiration letter
12601/01/2016Welcome to 2016

 

ORDERS

Transaction DateClientRevenue
01/01/20231235
02/01/20231245
03/01/20231245
04/01/20231235
05/01/20231255
05/01/20231265
1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@StephenClarke,

 

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 )

 

DataInsights_0-1674317157638.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
StephenClarke
Frequent Visitor

That is genius. 

What an elegant solution. 

Applied it to my model and it works beautifully at scale.

I owe you a beer.

Excellent, glad to hear that works!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DataInsights
Super User
Super User

@StephenClarke,

 

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 )

 

DataInsights_0-1674317157638.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.