Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
apmulhearn
Helper III
Helper III

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
Community Champion
Community Champion

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 still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

6 REPLIES 6
CNENFRNL
Community Champion
Community Champion

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 still way beyond their comprehension!

DAX is simple, but NOT EASY!

johnt75
Super User
Super User

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.