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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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