Using Aggregates | Community
Skip to main content
A
Anonymous
November 20, 2019
Solved

Using Aggregates

  • November 20, 2019
  • 2 replies
  • 3013 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by asktam1410

    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,

    2 replies

    Jonathon_wodnicki
    Community Advisor
    Jonathon_wodnickiCommunity Advisor
    Community Advisor
    November 21, 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

      asktam1410
      asktam1410Accepted solution
      Level 3
      November 22, 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,

        Terms & ConditionsAccessibility statement

        Loading footer...