Forum Discussion

sebastian_ebg's avatar
sebastian_ebg
Helper I
9 years ago

Multi-table DAX Attribution Model

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 ...
Need Help
sebastian_ebg's avatar
sebastian_ebg
Helper I
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    sebastian_ebg
    Helper I
    9 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        sebastian_ebg
        Helper I
        9 years ago

        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.