Using Flow to Calculate the Email Open Rate

Email messages play an important role in Salesforce environments. It is safe to say that almost every Salesforce environment has at least one automation that sends an email. In some cases these emails are just to notify the recipient. Therefore, it is not critical to track if the recipient opened the email or not. However, in some cases it is important to track the email opens. For example, if you are doing email advertising or sending newsletters, it is good to know the email open rate. By calculating the email open rate, you can decide to continue or stop sending the emails for specific recipients.

Using the Enhanced Email feature, Salesforce saves the emails as EmailMessage records instead of Task records. EmailMessage is a standard Salesforce object that stores information about the emails. Enhanced email allows email message records to be created across all object types where emails can be associated. However, if you disable Enhanced Email, case is the only object where email message records are created. Read this article to learn more about Enhanced Email.

It is possible to track the email opens by marking "Enable Email Tracking" under Activity Settings in the setup. Once you do it, EmailMessage records will have the information about the email open.

Schedule-Triggered Flow to Calculate the Email Open Rate

Let's assume that you are sending newsletters to your subscribers. If a subscriber is not opening your emails, you will probably want to stop sending emails to that person. In this example, we are working with a custom object called Subscriber.

1- Create a custom checkbox field called Email Opt-Out to have an indication on the subscriber. Create number fields to store the emails sent and emails opened. If you want, you can create a formula field that displays the email open rate according to these two number fields.

2- Create a Schedule-Triggered Flow that runs every night. Select Subscriber as the object and Email_Opt_Out__c=false as the condition requirement. Therefore, flow will run on all the subscribers that receives emails from you.

3- Add a Get Records element to get all the email message records that are sent to the subscriber. Use the RelatedToId and Incoming fields in your criteria.

Email Open Rate = (Number of Emails Opened / Number of Delivered Emails) ×100

Since email open rate calculation is according to the delivered emails, add another criteria to get the emails that are not bounced.

4- Collection Filter is a great feature from the Spring '22 release. Collection Filter element creates a new collection that contains only the source items that meet the conditions. Therefore, there is no need to add another Get Records element to bring the opened email messages. Add a Collection Filter element and select the Email Messages collection that you got from the previous step. Add a condition to keep the opened email messages (isOpened=true).

5- Create two number variables to store the total number of emails and the opened emails. Add an Assignment element to assign the record count of the collection that you have.

6- After the 5th step, you have the number of sent emails and opened emails. It is time to calculate the email open rate. If you sent only one email and it is not opened, it doesn't make sense to mark the subscriber as email opt-out. Therefore, the formula should return true only if enough emails were sent. Let's say that this threshold is 5 and the subscriber will be marked as email opt-out if the email open rate is below %20.

7- Add an Update Records element to update the subscriber record. Save the number of sent emails, number of opened emails, and email opt-out formula result.

End of the Flow

At the end, your flow should look like this.

This example was on the recipient level, but you can do it on the organization level or on an email template level to calculate the total email open rate.