CRM Lifecycle Marketing & Growth Analytics
Email Deliverability, CTOR vs CTR & List Churn Formula
Calculate Delivered Rate, Open Rate on Delivered, Click-to-Open Rate (CTOR = Unique_Clicks / Unique_Opens) vs Unique CTR, Opt-Out Rate, and Net Subscriber List Growth.
Separate inbox placement, subject-line hook, and body creative conversion!
Two numerator/denominator mistakes distort lifecycle CRM reporting: (1) dividing Unique Opens and Clicks by Total Sent instead of Delivered_Emails = Sent - Bounces (confounding list hygiene with creative performance), and (2) relying solely on Unique Click-Through Rate CTR = Unique_Clicks / Delivered, which mixes subject-line open rate with in-email offer conversion! Computing Click-to-Open Rate CTOR = Unique_Clicks / Unique_Opens = CTR / Open_Rate cleanly isolates body layout and call-to-action effectiveness among recipients who actually opened the message!
Verify on Total_Sent = 50,000 (B2), Bounced_Emails = 1,000 (B3 -> Delivered = 49,000), Unique_Opens = 12,250 (B4), Unique_Clicks = 1,960 (B5), Opt_Outs_Plus_Spam_Complaints = 245 (B6), and Period_New_Signups = 1,500 (B7): Delivery Rate is 49,000 / 50,000 = 0.9800 (98.00%); Open Rate on Delivered is 12,250 / 49,000 = 0.2500 (25.00%, vs naive 24.50% on Sent!); Unique CTR on Delivered is 1,960 / 49,000 = 0.0400 (4.00%), while Click-to-Open Rate CTOR is 1,960 / 12,250 = 0.1600 (16.00%); Opt-Out Rate on Delivered is 245 / 49,000 = 0.0050 (0.50%); and Net Active List Growth (suppressing bounces + opt-outs) is (1,500 - 1,000 - 245) / 50,000 = +0.0051 (+0.51%)!
| CRM Email Funnel Metric (Sent=50k, Bounces=1k, Opens=12,250, Clicks=1,960) | Formula | Verified Output |
|---|---|---|
| Inbox Delivery Rate | (Sent - Bounces) / Sent | 0.9800 (98.00% — 49,000 Delivered) |
| Open Rate (on Delivered) | Unique_Opens / Delivered | 0.2500 (25.00%) |
| Click-to-Open Rate (CTOR) | Unique_Clicks / Unique_Opens | 0.1600 (16.00%; CTR = 4.00%) |
| Opt-Out Rate & Net List Growth | OptOuts / Deliv; (New - Bounces - OptOuts) / Sent | 0.0050 Opt-Out; +0.0051 Net |
Click-to-Open Rate (CTOR) With Strict Funnel Monotonicity Guard Formula
Enforce Sent > Bounces >= 0 and Delivered >= Unique_Opens >= Unique_Clicks >= 0 so mis-mapped total clicks (exceeding unique opens) are caught immediately!
=LET(sn_em, B2, bn_em, B3, op_uq, B4, cl_uq, B5, dl_em, sn_em - bn_em, IF(OR(sn_em <= 0, bn_em < 0, dl_em <= 0, op_uq <= 0, op_uq > dl_em, cl_uq < 0, cl_uq > op_uq, B6 < 0, B6 > dl_em, B7 < 0), "Invalid CRM Funnel Input", ROUND(cl_uq / op_uq, 4)))Net Active Subscriber List Growth Rate Formula
Subtract both suppressed bounces and unsubscribes/complaints from new signups to track true reachable list velocity!
=IF(ISTEXT(E4), E4, ROUND((B7 - B3 - B6) / B2, 4))Try the example
Use the original values or change them to test your understanding.