cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors