Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Using Aggregates

christopherc21
Level 1
Level 1

I need to count raw data from deliveries, to aggregate all recipients who were sent, clicked and opened an email. I need to present these metrics based on the country of the recipient, not the country of the campaign that sent the email.  So individual recipients have received more than one delivery, but i don't care, I just need to aggregate the recipients by country and then calculate the open rates and click rates.  So the question here is how to aggregate, based on primary key the number of emails sent to each of these recipients.  Please help!!

1 Accepted Solution
asktam1410
Correct answer by
Level 3
Level 3

Chris,

This is what you can do - Start with Broad log and include all deliveries period you want to include

[target/delivery/label] included in =''

and [target/status]='Sent'

Contact Date on or after <> and Contact date On or Before <>.

In the compliment , Include Country code from recipient and Broad log id ( primary key ) and Delivery Id . Then enrich to tracking log based on Broad log id and delivery Id and include URL/type as an additional data . You could see more records after this enrichment due to more than one action.

Add a query next to enrichment and choose as a temporary schema. In the compliment add country code in group by then

CountDistinct(broadlogId) as Email_Sent,

Iif(type=1 ,broadlogid,'') as email_Click,

Iif(type=2 ,broadlogid,'') as email_Open,

View solution in original post

2 Replies
wodnicki
Community Advisor
Community Advisor

Hi,

Join recipient to broadlog and trackinglog, then group by recipient's country, summing sends, opens, and clicks.

Primary key isn't used here, as you're grouping by country.

Thanks,

-Jon

asktam1410
Correct answer by
Level 3
Level 3

Chris,

This is what you can do - Start with Broad log and include all deliveries period you want to include

[target/delivery/label] included in =''

and [target/status]='Sent'

Contact Date on or after <> and Contact date On or Before <>.

In the compliment , Include Country code from recipient and Broad log id ( primary key ) and Delivery Id . Then enrich to tracking log based on Broad log id and delivery Id and include URL/type as an additional data . You could see more records after this enrichment due to more than one action.

Add a query next to enrichment and choose as a temporary schema. In the compliment add country code in group by then

CountDistinct(broadlogId) as Email_Sent,

Iif(type=1 ,broadlogid,'') as email_Click,

Iif(type=2 ,broadlogid,'') as email_Open,

View solution in original post