Email campaigns live or die by open rate (opens over delivered) and click rate (clicks over delivered) — plus click-to-open, which isolates how compelling the content was.
The example
2,000 delivered, 800 opens, 120 clicks.
| A | B | |
|---|---|---|
| 1 | Metric | Value |
| 2 | Open rate | 40% |
| 3 | Click rate | 6% |
The formula
The formula:
How it works
How it works:
- Open rate = opens ÷ delivered — subject-line and sender strength.
- Click rate = clicks ÷ delivered — overall campaign pull.
- Click-to-open = clicks ÷ opens — isolates how compelling the content was for those who opened.
- Use delivered (sent minus bounces), not sent, as the base.
Click-to-open separates two problems. A low click rate could be a weak subject line (few opens) or weak content (opens but no clicks). Click-to-open (clicks / opens) isolates the content: high CTOR with low opens means fix the subject line; low CTOR means fix the email body or offer.
Try it: interactive demo
Delivered, opens, clicks.
Variations
Click rate
Of delivered:
Click-to-open (CTOR)
Content pull:
Unsubscribe rate
List health:
Pitfalls & errors
Use delivered, not sent. Subtract bounces for an honest base.
Open rates are noisy. Privacy features inflate opens — lean on clicks for true engagement.
CTOR isolates content. Use it to separate subject-line from body problems.
Practice workbook
Frequently asked questions
How do I calculate email open and click rates in Excel?
What is click-to-open rate?
Why are open rates unreliable?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class