Challenge Scenario
In email marketing and CRM lifecycle automation, marketing teams track a 3-step conversion funnel for every broadcast: Open Rate (Opens / Delivered), Click-to-Open CTR (Clicks / Opens), and Order Conversion Rate (Orders / Clicks).
As an Email Marketing Specialist, you are analyzing four newsletter broadcasts:
- Open Rate % (Column F): Divide total opens by delivered emails:
=C2/B2.
- Click-Through Rate CTR % (Column G): Divide unique clicks by total opens:
=D2/C2.
- Conversion Rate % (Column H): Divide completed orders by link clicks:
=E2/D2.
- Total Completed Orders (Cell E7): Sum all orders generated across editions using
SUM(E2:E5).
- Average Click-Through Rate (Cell G7): Compute average CTR using
AVERAGE(G2:G5).
Your Sheet Layout
Understanding the email campaign funnel table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:E5 |
Edition, Delivered, Opens, Clicks, Orders |
Broadcast Log |
Email counts at each step of the customer journey |
Read-only email analytics counts. |
F2:F5 |
Open Rate % |
Top of Funnel |
=C2/B2 |
Percentage of recipients who opened the email. |
G2:G5 |
CTR % |
Engagement Rate |
=D2/C2 |
Percentage of openers who clicked links. |
H2:H5 |
Conv % |
Bottom of Funnel |
=E2/D2 |
Percentage of clickers who made purchases. |
E7 |
Total Orders |
Summary |
=SUM(E2:E5) |
Combined purchases across all broadcasts (512). |
G7 |
Average CTR |
Summary |
=AVERAGE(G2:G5) |
Portfolio average click rate (22.5%). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells F2:F5, calculate Open Rate % (= Opens / Delivered).
2
In cells G2:G5, calculate CTR % (= Clicks / Opens).
3
In cells H2:H5, calculate Conversion Rate % (= Orders / Clicks).
4
In cell E7, sum total completed orders, and in G7 calculate average CTR.
Solution & Formula Breakdown
Let's review the funnel engagement ratios step-by-step:
1. Open Rate % Calculation (F2:F5)
=C2/B2
- Edition #41: 2,500 opens / 10,000 delivered = 25.0%.
- Edition #42: 3,600 opens / 12,000 delivered = 30.0%.
- Edition #43: 1,600 opens / 8,000 delivered = 20.0%.
- Edition #44: 4,500 opens / 15,000 delivered = 30.0%.
2. Click-to-Open CTR % Calculation (G2:G5)
=D2/C2
- Edition #41: 500 clicks / 2,500 opens = 20.0%.
- Edition #42: 900 clicks / 3,600 opens = 25.0%.
- Edition #43: 320 clicks / 1,600 opens = 20.0%.
- Edition #44: 1,125 clicks / 4,500 opens = 25.0%.
3. Order Conversion Rate % Calculation (H2:H5)
=E2/D2
- Edition #41: 75 orders / 500 clicks = 15.0%.
- Edition #42: 180 orders / 900 clicks = 20.0%.
- Edition #43: 32 orders / 320 clicks = 10.0%.
- Edition #44: 225 orders / 1,125 clicks = 20.0%.
4. Funnel Totals (E7 & G7)
- Cell E7 (Total Orders):
=SUM(E2:E5) → 512 orders.
- Cell G7 (Average CTR):
=AVERAGE(G2:G5) → 22.5%.
Click-to-Open Rate (CTOR = Clicks / Opens) measures how compelling your email body and call-to-action were to people who actually opened the message!