cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apmulhearn
Helper I
Helper I

Email Activity - Most recent for each recipient for EACH unique email sent

Hello,

I am first and foremost hoping to reduce the number of rows I query.

I am querying my HubSpot EmailCampaignEvents table. I need to be able to chart recipient email engagement, but I don't want to have to pull EVERY event type. If an email was OPENED, it was obviously DELIVERED - I don't need the DELIVERED record for that recipient on that email ID.


I WOULD like to prioritize CLICK. So if an email is CLICKED on March 3 and then opened again on March 4, I would like to return the March 3 CLICK Value.

There are 3 ActivityTypes I care about. In order of importance:
CLICK
OPEN

DELIVERED

 

Below is a small sample of Source Data

Data   
EmailCampaignIDRecipientEmailActivityTypeActivityDate
12345amanda@amanda.comOpen2/22/2022
12345amanda@amanda.comClick2/23/2022
12345amanda@amanda.comOpen2/24/2022
12345ashley@ashley.comOpen2/21/2022
12345ashley@ashley.comOpen2/28/2022
98765amanda@amanda.comOpen3/2/2022
98765amanda@amanda.comDelivered3/1/2022
98765ashley@ashley.comDelivered3/1/2022
98765ashley@ashley.comOpen3/3/2022
45678amanda@amanda.comDelivered1/1/2022
45678amanda@amanda.comOpen1/2/2022
45678ashley@ashley.comDelivered1/1/2022

 


And Desired Outcome

Desired Result  
EmailCampaignRecipientActivityTypeActivityDate
12345amanda@amanda.comClick2/23/2022
12345ashley@ashley.comOpen2/28/2022
98765amanda@amanda.comOpen3/7/2022
98765ashley@ashley.comOpen3/3/2022
45678amanda@amanda.comOpen1/2/2022
45678ashley@ashley.comDelivered1/1/2022
1 ACCEPTED SOLUTION
CNENFRNL
Super User
Super User

Simple enough,

CNENFRNL_1-1646772544853.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
CNENFRNL
Super User
Super User

Simple enough,

CNENFRNL_1-1646772544853.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

johnt75
Solution Sage
Solution Sage

I think I would create a summary table broken down by email ID and recipient. You could then use the summary table as the basis for the relationships and measures within the report.

First you would need to add a new calculated column to the base data for the Activity Type sort order

Activity Type Sort Order = SWITCH( 'Base Table'[Activity Type],
"Click", 1,
"Open", 2,
"Delivered", 3
)

You can then create the summary table

Summary Table = GENERATE( SUMMARIZE('Table', 'Table'[EmailCampaignID], 'Table'[RecipientEmail]),
var currentEmailID = [EmailCampaignID]
var currentRecipient = [RecipientEmail]
var result = SELECTCOLUMNS(
CALCULATETABLE(
TOPN( 1, 'Table',
'Table'[Activity Type Sort Order], ASC,
'Table'[ActivityDate], ASC
),
REMOVEFILTERS('Table'),
'Table'[EmailCampaignID] = currentEmailID && 'Table'[RecipientEmail] = currentRecipient
),
"Activity Type", 'Table'[ActivityType],
"Activity Date", 'Table'[ActivityDate]
)
return result
)

 

Hi and thank you. This looks like a good back-up plan, but I was actually hoping to reduce my data load on the query side in the first place. I only want to pull in the most recent activity for each email per each user, but prioritize the CLICK if possible.

You could potentially create a ranking column based on the date and the activity type, e.g. take number of days since 1900 and multiply by 5 for a click and 2 for an open. That would give you a column to sort by, but I'm not sure how you could restrict it to 1 row per email per customer. Perhaps one of the group by aggregations would do it ?

DataInsights
Super User
Super User

@apmulhearn,

 

Try this measure:

 

Activity Date = 
VAR vDateClick =
    CALCULATE (
        MAX ( SourceData[ActivityDate] ),
        ALLEXCEPT (
            SourceData,
            SourceData[EmailCampaignID],
            SourceData[RecipientEmail]
        ),
        SourceData[ActivityType] = "Click"
    )
VAR vDateOpen =
    CALCULATE (
        MAX ( SourceData[ActivityDate] ),
        ALLEXCEPT (
            SourceData,
            SourceData[EmailCampaignID],
            SourceData[RecipientEmail]
        ),
        SourceData[ActivityType] = "Open"
    )
VAR vDateDelivered =
    CALCULATE (
        MAX ( SourceData[ActivityDate] ),
        ALLEXCEPT (
            SourceData,
            SourceData[EmailCampaignID],
            SourceData[RecipientEmail]
        ),
        SourceData[ActivityType] = "Delivered"
    )
VAR vDateToUse =
    COALESCE ( vDateClick, vDateOpen, vDateDelivered )
VAR vResult =
    CALCULATE (
        MAX ( SourceData[ActivityDate] ),
        SourceData[ActivityDate] = vDateToUse
    )
RETURN
    vResult

 

In the visual, use the measure [Activity Date]:

 

DataInsights_0-1646756231601.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi, and thank you. 
I do think this measure will work, but I'd rather reduce my data load on the front, and only bring in the rows I'm looking for. Is there a way to do this at the query editor level?

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors