Formulas

Email open rate formula: how to calculate open rate

Email open rate is the share of delivered emails that were opened.

About the Klaviyo connectorAbout the Mailchimp connector

An "open" is an image loading, and plenty of images load without a person looking, so treat the figure with some care. The formula is a division. The mistakes are in the denominator and in which opens you count.

The formula

Open rate = unique opens ÷ delivered emails

Delivered is sent minus bounces. Unique opens counts each recipient once, however many times they opened.

In Google Sheets, with delivered in column D and unique opens in column E:

=IF(D2=0, "", E2/D2)

Format as a percentage to two decimal places. If your export gives you sent and bounced rather than delivered, add a column for it: =B2-C2.

A worked example

The total row divides total unique opens by total delivered: =E5/D5, which is 4,810 ÷ 19,650 = 24.48%. Averaging the three rates would give 28.33%, because it lets the small welcome series count as much as the sale that went to five times as many people.

The common mistake

Counting total opens instead of unique opens.

The sale email was opened 3,675 times in all, because some people opened it more than once. Put that in the numerator and the rate is 3,675 ÷ 9,800 = 37.50%. The real figure, one open per person, is 25.00%. Most platforms report both, side by side, and the larger one is the one that ends up in the deck. Check the column heading before you build on it.

Some opens are not people. Mail clients and privacy features can load an email's images before the recipient has done anything, and each of those loads registers as an open. Rates from before those features arrived and rates from after them are not measuring the same thing. The largest of those features is Apple's Mail Privacy Protection, which went live in September 2021; by August to October 2022 it accounted for 54.6% of all the opens Litmus measured across nearly five billion of them (Litmus, email client market share, 2022). Clicks are the better signal of whether anyone read the thing; see email click-through rate.

What counts as good

It depends on who is on the list and why they joined, more than on anything you do in the subject line. A list of recent buyers opens more than a list collected from a giveaway, and a welcome email opens more than a monthly newsletter. Compare each campaign type against its own history. Published averages are covered on average email open rate, with their caveats.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Delivered and Opens for each Klaviyo send in a tab of its own, for example one called Klaviyo. The same works for Mailchimp, whose columns are Unique opens and Emails sent.

Fresh Sheets replaces that tab's contents on every refresh, so keep your formulas on a separate tab. This version finds each column by its heading, so it still works if you add a field and the columns move:

=SUM(INDEX('Klaviyo'!A:Z, 0, MATCH("Opens", 'Klaviyo'!1:1, 0))) / SUM(INDEX('Klaviyo'!A:Z, 0, MATCH("Delivered", 'Klaviyo'!1:1, 0)))

Change the quoted headings to match the ones in your tab.