Skip to main content

Hi Admins!  I am looking to determine which of our accounts are most engaged with our Emails sent via JO.  Has anybody built out a report to determine the email open rates for accounts in a defined time period?   If so, which object did you use and how did you configure the fields? 

 

Looking for something like the below for this year:

Account 1 - 100 Emails Sent - 20 Opened - 20% Open Rate

@zdsiegert I have a version of this built using Data Designer, and I used the AO Participants object.

@zdsiegert ^ I’m planning to do the same thing that @heather_hansen has!

Which fields in the AO Participants Object did you use? @heather_hansen - trying to do the same thing!

@mattjay98 I actually built something recently on Email LogV2 instead.  For mine, I was looking for open and click through rates at the account level instead of the participant level, but I think you could use the same process to get it by account if you wanted.

Here are the fields I used:

  • Source Name is your program name
  • Opened (YES or NO)
  • Link Clicked Date
  • Company GSID
  • Company Name
  • Contact Name (it’s just Name in this object)

From that raw data, you’d then need to get a total number of participants per account, then, a sum of opens and a sum of clicks per account as well.  For my purposes, I don’t care about multiple opens or multiple clicks, so I’m just looking for it to happen once.

 

I’d split the Email Logs V2 into 2 different datasets, one to count the total number of contacts, and then, one to calculate the sum of the opens and clicks.

 

The Contacts count dataset would just a transform to group by the Company Name and count the number of contacts.

For the Opens and Clicks, I did a transform with 2 case statements.  For Opens, if Opened = YES, then, 1, and if Opened = NO, then 0.  For Clicks, if the Link Clicked Date is NOT NULL, then 1, and if it is NULL, then 0.  Then, a second transform to group by Company Name again and get the SUM of the Open and Click case statement fields.  

 

Then, merge that back together with the Contact Count dataset using Company Name, and you should get something like this:
 

 

Then, you can do all your calculations from there.  There may be an easier way, but that’s what I’ve come up with.  Hope that helps!
 

Thanks so much @heather_hansen ! This was so useful!

I did something similar without case statements- I just used sent date & open date as my ‘Count’ values and grouped on GSID. 

Didn’t think about doing it like you did - may keep that in mind.

 

All the best!

Matt

Reply


Terms & ConditionsCookie settings