Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Properly Attributing Transactions to Marketing Campaigns

    Silver Contributor
    Posted 10 days ago
      |   view attached
    I have data from our marketing automation database and our transactions database. I'm trying to create an attribution report but I believe the relationships are limiting a proper result.

    The marketing automation database has 4 tables I'm working with Contacts, ContactLists, ContactsinList, and MarketingEmails. I've also created a bridge table between MarketingEmails and ContactsinLists so there is a 1-to-many relationship between the two. The Contacts table matches to the Orders table via the email address.


    Because of the direction of the relationships, I'm not able to filter on a marketing email and show the contacts on the list. Which also means that any revenue associated with the contacts that received the email aren't properly calculating.

    Please help me figure out via DAX or relationship modifications how to fix this problem. I've attached a mockup of the model in a pbix file. Thanks.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------

    Attachment(s)



  • 2.  RE: Properly Attributing Transactions to Marketing Campaigns

    Bronze Contributor
    Posted 9 days ago
    The relationship between ContactsInList and Contacts is going the wrong way - change it to 'both'

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 3.  RE: Properly Attributing Transactions to Marketing Campaigns

    Silver Contributor
    Posted 9 days ago
    Thanks @James Watts. This is providing the contacts and revenue. But performance in my real model is now an issue.

    I have over 196,000 Contacts and the ContactsInList table has over 6 million records. When I select an email, it can take up to 10 seconds for the page to load with the basic metrics shown in the pbix file I uploaded earlier. What recommendations are out there to speed up the queries so the load time is acceptable? Here are the 3 measures currently in place.

    Attributed Revenue =
    VAR MailDate = SELECTEDVALUE(MarketingEmails[PublishDate])
    Return
    CALCULATE([Ttl Revenue],
    'Orders'[OrderDate]>=MailDate,
    'Orders'[OrderDate]<=MailDate+90)

    Ttl Revenue =
    CALCULATE(
    SUMX(Orders,Orders[Revenue]),
    USERELATIONSHIP(Dates[Date],Orders[OrderDate]))

    List SizeCALCULATE( DISTINCTCOUNT(ContactsInList[vid]) )


    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 4.  RE: Properly Attributing Transactions to Marketing Campaigns

    Bronze Contributor
    Posted 8 days ago
    I would normalise your tables as a first step.

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 5.  RE: Properly Attributing Transactions to Marketing Campaigns

    Silver Contributor
    Posted 8 days ago
    Can you provide an example of where to start? I thought my model was already normalized.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 6.  RE: Properly Attributing Transactions to Marketing Campaigns

    Bronze Contributor
    Posted 8 days ago
    For example, your Contacts table has columns with multiple identical entries.  Replace them with IDs, and add another table with the IDs/values.

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 7.  RE: Properly Attributing Transactions to Marketing Campaigns
    Best Answer

    Top Contributor
    Posted 7 days ago
    Edited by Nancy Peterson 3 days ago
      |   view attached
    Hi Nancy & James,

    Many things :
    1- Normalizing here is not the good option for me, because
    - Power BI works with an in memory colomnar storage and not a row storage like in any t-sql database.
    - Relationships are not joins in Power BI, but filters !
    Hence, a data model in Power bi has a real different behaviour than a data model in SQL Server, Oracle... databases

    2- Power query is often used to only clean the datas, but it also have the role to help you recombine tables in a proper way to build the good data model in Power BI. The best option is the star schema, so you have to denormalize partialy your model and merge some tables to reach the goal

    3- AVOID bi directionnal relationships !! they create ambiguity in filters propagation and potentially bad behaviours or bad results !!!

    4- When handling dates, the creation of a date table is mandatory !!! when done, mark it as Date table !! (table tools/ mark as date table) to make it the date reference in your data model

    Here is my suggestion with the file attached... I have made some transformations in Power query, and changed somme measures expressions...

    I hope this is a good star for you....

    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------

    Attachment(s)



  • 8.  RE: Properly Attributing Transactions to Marketing Campaigns

    Silver Contributor
    Posted 3 days ago
    Thanks @Nicolas MENDEZ. I applied your ideas to my model and have succeeded in achieving the required results. ​

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 9.  RE: Properly Attributing Transactions to Marketing Campaigns

    Top Contributor
    Posted 3 days ago
    Good news !!! Could you please set the topic as solved ?

    Enjoy Power BI

    ------------------------------
    Nicolas MENDEZ
    Data & BI consultant
    Becom Consulting
    ------------------------------