Properly Attributing Transactions to Marketing Campaigns

Properly Attributing Transactions to Marketing CampaignsJump to Best Answer
Nancy Peterson
Nancy PetersonJun 21, 2022 12:32 PM
I have data from our marketing automation database and our transactions database. I’m trying to create …
1. Properly Attributing Transactions to Marketing Campaigns

Silver Contributor
Nancy Peterson
Posted Jun 21, 2022 12:32 PM | 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)

pbixBasic Model Campaign Revenue Attribution.pbix

2. RE: Properly Attributing Transactions to Marketing Campaigns

Bronze Contributor
James Watts
Posted Jun 22, 2022 03:29 AM
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
Nancy Peterson
Posted Jun 22, 2022 03:41 PM
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 Size = CALCULATE( DISTINCTCOUNT(ContactsInList[vid]) )

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

4. RE: Properly Attributing Transactions to Marketing Campaigns

Bronze Contributor
James Watts
Posted Jun 23, 2022 08:29 AM
I would normalise your tables as a first step.

——————————
James Watts
Business Intelligence Analyst
——————————

5. RE: Properly Attributing Transactions to Marketing Campaigns

Silver Contributor
Nancy Peterson
Posted Jun 23, 2022 09:25 AM
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
James Watts
Posted Jun 23, 2022 09:43 AM
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
Nicolas MENDEZ
Posted Jun 24, 2022 09:38 AM
Edited by Nancy Peterson Jun 28, 2022 08:33 AM | 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)

pbixBasic Model Campaign Revenue AttributionNiko.pbix

8. RE: Properly Attributing Transactions to Marketing Campaigns

Silver Contributor
Nancy Peterson
Posted Jun 27, 2022 07:28 PM
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
Nicolas MENDEZ
Posted Jun 28, 2022 02:46 AM
Good news !!! Could you please set the topic as solved ?

Enjoy Power BI

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