Expand my Community achievements bar.

Announcing the launch of new sub-community for Campaign Web UI to cater specifically to the needs of Campaign Web UI users!
SOLVED

Using Aggregates

Avatar

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

Avatar

Correct answer by
Level 4

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

Avatar

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

Avatar

Correct answer by
Level 4

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,