Using Aggregates

[Former Community Member]

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

[Jonathon_wodnicki]

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 · Accepted solution]

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,