Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I use PowerBI to analyze efficiency of our mass email campaigns. I get a table that contains all emails that were sent.
Each row is for one email message to one email address, e.g.:
first@gmail.com 1st email 4/5/19
first@gmail.com 2nd email 4/7/19
first@gmail.com 3rd email 4/9/19
I aslo see if the email was openned, clicked, replied (I have the corresponding date and time columns. they are empty if no action).
I have a column with the email number I sent, e.g. 1 for the first email, 2 for the second etc.
Also, I have a column that aggregates dates of the interaction with my emails (either open or click or reply or unsubscribe date).
I want to get a list with unique email addresses with the lattest interaction (if any happened).
I tried to sort by filter by Interaction date and then remove duplicates in Editor. However, I always get the list with the number 1 email in the list.
I use this table to merge with the other one so it is important for me to have the unique emails, but it would be awesome if I could get the latest interaction date for further analysis.
Any ideas how to accomplish it?
Hello,
It feels difficult to answer without a clear table structure, but one idea you can try in Power Query would be to make a Group by email address and define the new column as Max of Interaction Date.
Regards,
ElenaN
@ElenaN , thank you, but it didn't help. However now I know about this feature 🙂
I still get the list with the only first email
Here is what I did:
Hello,
Reacted Date should not be in the grouping but only in the new column part.
Regards,
ElenaN
Elena, thanks! Well, I could get a list of email addresses and date, but if I add more fields (which I need), this solution stops working for me 😞
I need to get a list of emails with three more columns: subject, email number and reacted date
Hello,
So, under Group By section you should pick the Email, Subject and Email # fields and under New column name you should have MAX Reacted Date.
If you have these, what happens? What is your end result?
Regards,
ElenaN
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |