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

Using Aggregates

Avatar

Avatar
Validate 1
Level 1
christopherc21
Level 1

Like

1 like

Total Posts

1 post

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile

Avatar
Validate 1
Level 1
christopherc21
Level 1

Like

1 like

Total Posts

1 post

Correct Reply

0 solutions
Top badges earned
Validate 1
Boost 1
View profile
christopherc21
Level 1

20-11-2019

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!!

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar
Validate 1
Level 3
asktam1410
Level 3

Likes

18 likes

Total Posts

37 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile

Avatar
Validate 1
Level 3
asktam1410
Level 3

Likes

18 likes

Total Posts

37 posts

Correct Reply

6 solutions
Top badges earned
Validate 1
Boost 5
Boost 3
Boost 10
Boost 1
View profile
asktam1410
Level 3

22-11-2019

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,

Answers (1)

Answers (1)

Avatar

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile

Avatar
Establish
MVP
wodnicki
MVP

Likes

961 likes

Total Posts

1,090 posts

Correct Reply

509 solutions
Top badges earned
Establish
Affirm 500
Contributor
Shape 1
Give Back 100
View profile
wodnicki
MVP

21-11-2019

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