Multi-table DAX Attribution Model

[sebastian_ebg]

I'm trying to generate an attribution model for our company's emails based off a few different related tables. I'm fairly new to DAX, so I've been fumbling around a bit searching for examples of how to achieve the result we're looking for.

The attribution model we're looking at using is linear attribution because it works best with the way our industry operates.

The logic behind this is as follows:

[Attributed Individual Mailing Sales] =
[Total converted customer purchase value] / [Total customer opens]

If I were to plot a simple example, where the X axis is the individual mailings, the Y axis is the individual customers, and the values are the total customer conversion divided by the number of emails opened, it would look something like this:

CustomerKey	10	11	12	13	14	Conversion
1				$24.99	$24.99	$49.97
2		$39.50	$39.50			$79.00
3		$-
4
5				$74.00	$74.00	$148.00
6			$23.00		$23.00	$46.00
7			$-
8		$71.98	$71.98		$71.98	$215.95
9		$-
10	$3.80	$3.80	$3.80	$3.80	$3.80	$19.00
Attribution	$3.80	$115.28	$138.28	$102.79	$197.77	$557.92

The data model that I have created is in an example file hosted here.

I've tried creating a few measures and calculated columns to determine the total conversion value in the LineItems table which is properly filtering to only customers who have a direct conversion and have opened emails. The measure is returning the correct value ($557.92), while still properly obeying filtering.

CALCULATE(SUM(LineItems[Value]),LineItems[MailOpens] > 0,
LineItems[Direct Attribution] = TRUE()) 

The problem I'm having is that I now need to be able to apply that on a per-mailing basis, while still obeying the product filtering. This is where I'm falling apart, as the calculation isn't returning the attribution values (the final total row) in the table above, which was manually calculated.

Can anyone point me in the right direction for the correct calculation using this data model? Or perhaps even a way to re-work the model that will make it work in PBI/DAX as I have access to my own ETL tools?

[v-shex-msft]

Hi @sebastian_ebg,

Can you share us a sample file to test? It is hard to know your table relationships from your screenshot.

Regards,

Xiaoxin Sheng

[sebastian_ebg]

I've updated the post to clarify the problem and added an example with simplified data.

I've also uploaded the example file here to hopefully get some help with it.

Regards,

Sebastian

[v-shex-msft]

Hi @sebastian_ebg,

Based on research on your sample file, I can't get the cost of specify mailing item and mailiing key. There is no direct relationship between custom to item or item to price.

Regards,

Xiaoxin Sheng

[sebastian_ebg]

I'm not sure if I'm following. There definitely is a relationship between all those fields. The relationship flows as follows:

MailingDataFull[CustomerKey] Many:1 CustomerDetails[CustomerKey]
CustomerDetails[CustomerKey] 1:Many LineItems[CustomerKey]
LineItems[SKU] Many:1 ProductDetails[SKU]

The price is determined from the LineItems[Value] field. You're right in that there's no direct relationship between MailingDataFull and LineItems, however there is a unique relationship between the CustomerKeys in MailingDataFull and the CustomerKeys in LineItems.

[v-shex-msft]

Hi @sebastian_ebg,

>>You're right in that there's no direct relationship between MailingDataFull and LineItems, however there is a unique relationship between the CustomerKeys in MailingDataFull and the CustomerKeys in LineItems.

Based on those relationships, I can only get total items and the total spend value, but I can't analysis the detail cost of each mail items. (Your goal is get the detail spend list of each customer per pack)

Regards,
Xiaoxin Sheng

[sebastian_ebg]

Ohh, I see the confusion. That's not exactly what I want. What I want, is the total amount that the customer spends, divided equally between each mailing.

That way, there doesn't need to be a direct relationship between the MailingDataFull and the LineItems. As an example (using the above data):

Customer 2 spends $79 total. He opens emails 11 and 12. He has opened two emails total.

This means that, for this indidivual:
Email 11 is worth $79/2 = $39.5
Email 12 is worth $79/2 = $39.5

Another example,
Customer 8 spends $215.95 total. She opens emails 11, 12 and 14. She has opened three emails total.

This means that, for this individual:
Email 11 is worth $215.95/3 = $71.98
Email 12 is worth $215.95/3 = $71.98
Email 14 is worth $215.95/3 = $71.98

Hopefully this clarifies it.

[v-shex-msft]

Hi @sebastian_ebg,

I create a new calculate table to deal with your requirement, you can refer to below steps:

Add a measure to lineitem table to calculate the total spend per customer.

Total Spend = SUMX(FILTER(ALL(LineItems),[CustomerKey]=MAX([CustomerKey])&&RELATED(ProductDetails[AdvertisedFlag])=TRUE()),[Value])

Calculate table:

ResultTable = SELECTCOLUMNS(FILTER(MailingDataFull,[OpenCount]>0),"CustomKey",[CustomerKey],"MailingKey",[MailingKey],"Total",[Total Spend])

Add calculate column to calculate the average spend.

Average = [Total]/COUNTROWS(FILTER(ResultTable,[CustomKey]=EARLIER(ResultTable[CustomKey])))

Result table:

Visual:

Regards,

Xiaoxin Sheng

[sebastian_ebg]

Thanks so much for all your help Xiaoxin! This is so close to working now.

The only part that's not working is that the [Conversion Value] measure isn't obeying other filtering when applied to the result table.

Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE())

Example: If you bring the [Conversion Value] measure in as a card, and filter by "Primary" for the ProductDetails[ProductType], it properly shows $557.92. However, once applied to the ResultTable calculation, it shows the total of $1655.64, even when filtering by Primay product type.

If you change the [ConversionValue] measure to the following, it works fine - but I want to be able to apply the filter manually.

Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE(), ProductDetails[ProductType] = "Primary")

Is there any way to get it to obey filtering at the measure level, so that the measure updates based on filters without needing to hard code it?

Regards,

Sebastian

[v-shex-msft]

Hi @sebastian_ebg,

Did you create the relationship between the result table and original table?

Regards,

Xiaoxin Sheng

[sebastian_ebg]

I can link it to the Mailing table, but not to the Customer table because it creates a circular dependancy.

I don't think that would be the issue though, as the measure obeys the filtering when applied as a card, but when used within another measure/table, it ignores filtering. Reading up on it a bit, it may be something to do with cross-directional filtering, but I'm definitely out of my depth there.

[v-shex-msft]

Hi @sebastian_ebg,

You can try to use values function to enable the slicer affect:

Conversion Value = CALCULATE(SUM(LineItems[Value]), CustomerDetails[MailOpens] > 0, Attribution[DirectAttribution] = TRUE(), Values(ProductDetails[ProductType]))

Regards,

Xiaoxin Sheng

[sebastian_ebg]

Unfortunately that doesn't have any effect. I believe it's because of the context of the calculation. I might need to leave this for a while and re-visit it with a fresh look.

[sebastian_ebg]

Unfortunately it's very difficult for me to share a sample file as it contains sensitive data. I may spend a day anonymising and reducing the data so that I can share it because it still has me stumped.

In the mean time, this is the relationships of the tables:

LineItems[SKU]* - ProductDetails[SKU]1
LineItems[CustomerKey]* - CustomerDetails[CustomerKey]1
MailingDataFull[CustomerKey]* - CustomerDetails[CustomerKey]1

Hopefully this helps. If not, I will work on anonymising the data.