Ten Campaigns, One Real Problem: Auditing a Live Email Export
Objective: Given a real 10-campaign email export, read the actual open/click/unsubscribe numbers against the lesson's own benchmark table, and separate the one campaign with a genuine problem from two others that only look risky.
Freshworks is demand-testing a browser-based project-management add-on for remote teams (the same beta the paid-search team is running search ads for) before committing engineering budget. The email list, built from the product's waitlist and existing free-tier users, has sent 10 campaigns over the last month: a welcome automation, five weekly product digests, a feature launch, a customer case study, a re-engagement send, and a pricing announcement. You've been handed the raw export and one question: which of these 10 sends is actually working, and which one is quietly costing the list?
You're not grading each campaign against a single number. You're cross-checking two or three numbers at once against the lesson's own benchmark table, the way a real audit works, and writing down what to do about each one you flag.
Before you start
What you'll need
Free path (everything below is enough to finish)
Free, and every step in this project is doable with basic sort/filter/formula columns, no paid analytics tool required.
Free tier shows exactly this kind of per-campaign report (opens, clicks, unsubscribes, revenue) natively, no CSV export needed once you're inside a real account.
Free tier shows post-click engagement time and conversion by landing URL, the actual evidence behind a 'clicks are fine, the page after isn't' call.
Paid upgrades (optional, faster/deeper)
The free path (Sheets + a free Klaviyo account) is complete for every step above. Databox only earns its keep once you're running this audit monthly and want it automated.
Pulls ESP data into a live dashboard with alert thresholds, useful once this becomes a recurring monthly job rather than a one-off exercise.
The process
4 steps
Step 01 of 04
The lesson's benchmark table sets Open Rate at 30-42%, CTR at 2-3%, CTOR at 10-15%, and Unsubscribe Rate under 0.2%. It also warns: 'A high open rate but low CTR means your subject line is great but your body copy or CTA needs work.'
Scan all 10 rows for open rate AND unsubscribe rate together, not open rate alone. Which single campaign fails the lesson's benchmark on more than one metric at once?
Procedure
- Open email-campaign-export.csv and add a column: unsub_rate = unsubscribes / delivered * 100.
- Sort the sheet by unsub_rate, highest first.
- For each of the top 3 rows, check open_rate against the lesson's 30-42% band.
- Flag any row that breaks BOTH the unsub ceiling and the open-rate floor at the same time, that combination is the real signal, not either number alone.
Sorted by unsub_rate (desc) sent open% unsub# unsub% 1 Re-engagement - 30 Day Inactive 6,180 11.91% 53 0.87% 2 Pricing Update Announcement 25,100 41.77% 134 0.54% 3 Weekly Product Digest - W20 24,480 32.32% 47 0.19% 4 Weekly Product Digest - W22 24,790 34.94% 44 0.18% 5 Case Study - Agency Team 19,750 29.64% 35 0.18% 6 Feature Launch - Kanban Boards 22,890 34.02% 28 0.12% 7 Weekly Product Digest - W19 24,310 31.21% 26 0.11% 8 Weekly Product Digest - W18 24,150 33.29% 24 0.10% 9 Weekly Product Digest - W21 24,620 34.51% 24 0.10% 10 Welcome Series - Email 1 8,420 66.07% 6 0.07% Benchmark: open rate 30-42%, unsub rate under 0.2%
Healthy
Open rate lands inside the lesson's 30-42% band AND unsubscribe rate stays under the 0.2% ceiling, together, not just one of the two.
Unhealthy
Either metric alone breaching its benchmark, especially unsubscribe rate above 0.2%, the one metric on this table with effectively zero acceptable slack at scale.
What this means
Row 1, Re-engagement - 30 Day Inactive, fails BOTH ceilings at once: 11.91% open rate sits 18 points under the lesson's 30% floor, and its 0.87% unsub rate is more than 4x the 0.2% ceiling. That combination, not either number alone, is what marks it as the one campaign with a real problem. Rows 2-5 each break only the unsub ceiling, worth a second look, not a rewrite.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| Unsubscribe rate above 0.2% on any single send | Pull that segment out of the next scheduled send and diagnose before resending to it again | 30 min |
| Open rate under the lesson's 30% floor on a re-engagement segment specifically | Treat it as a list-hygiene job, not a copywriting job: apply the lesson's 3-6 month inactive-subscriber removal rule instead of rewriting the email | half day |
Step 02 of 04
The lesson's list-hygiene guidance: 'Remove inactive subscribers every 3-6 months. A smaller engaged list has better deliverability than a large disengaged one.' It separately notes segmented campaigns generate 760% more revenue than non-segmented sends.
The re-engagement send only reached 723 openers out of 6,180 sent, but of the people who DID open, 22.68% still clicked, above the lesson's 10-15% CTOR benchmark and above this export's own 9-campaign average. What does that split tell you about whether the content or the audience is the actual problem?
Procedure
- Add a CTOR column: clicks / opens * 100.
- Calculate Re-engagement's CTOR: 164 / 723 = 22.68%.
- Calculate the average CTOR of the other 9 campaigns and compare.
- Decide: is the low reach (open rate) or the content (CTOR) the actual failing part?
Re-engagement - 30 Day Inactive Sent 6,180 | Delivered 6,078 | Opens 723 (11.91%) | Clicks 164 (2.70% of sent) CTOR (clicks / opens) = 164 / 723 = 22.68% Average CTOR of the other 9 campaigns = 21.72% (Welcome 38.86%, W18 13.16%, Feature Launch 30.89%, W19 24.01%, W20 21.93%, Case Study 15.00%, W21 20.92%, Pricing 16.91%, W22 13.84%)
Healthy
CTOR at or above the library's own average (~21.7%) signals the content itself still resonates with whoever actually opens it.
Unhealthy
CTOR far below the library average alongside a low open rate would mean both reach AND content are broken, a much bigger fix than list hygiene alone.
What this means
Re-engagement's 22.68% CTOR beats the other 9 campaigns' 21.72% average. The small number of people who do open still click at a healthy, above-average rate, the email itself works. The problem is entirely upstream: this segment has decayed past the point where sending to it is worth the unsubscribe cost, exactly the 'remove inactive subscribers every 3-6 months' rule the lesson gives.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| CTOR at or above average despite a broken open rate | Don't touch the email copy. File a list-hygiene ticket: tag anyone who hasn't opened in 90+ days and route them to a one-time win-back send before suppression. | half day |
| A decayed segment repeatedly receiving standard sends | Add a 90-day inactivity filter to the automation trigger so this segment stops receiving routine sends automatically | dev ticket |
Step 03 of 04
The lesson's segmentation warning: 'sending every email to your entire list... tanks your engagement rates... Segment your list from day one: buyers vs. non-buyers, active vs. inactive... Segmented campaigns generate 14.31% higher open rates (Mailchimp).'
Pricing Update Announcement has the second-highest open rate in the whole export (41.77%) and an above-benchmark CTOR (16.91%), yet its unsubscribe rate (0.54%) is over 2.5x the lesson's 0.2% ceiling, second only to the campaign you already flagged. If the content is clearly landing with a lot of people, what does the elevated unsub rate actually point to?
Procedure
- Pull the full row for Pricing Update Announcement: sent 25,100, opens 41.77%, CTOR 16.91%, unsub 0.54%.
- Compare its `sent` count to a routine Weekly Product Digest send, both went to essentially the whole list.
- Ask who a pricing-change announcement is actually relevant to versus who received it.
Pricing Update Announcement Sent 25,100 | Delivered 24,933 | Opens 10,414 (41.77%) | Clicks 1,761 (7.06% of sent) CTOR = 1,761 / 10,414 = 16.91% (above the lesson's 10-15% benchmark) Unsubscribes = 134 (0.54% of delivered, 2.7x the lesson's 0.2% ceiling) Weekly Product Digest - W22 (same list, routine send, for comparison) Sent 24,790 | Open rate 34.94% | Unsub rate 0.18%
Healthy
Unsubscribe rate stays under 0.2% even on a high-open, high-CTOR send.
Unhealthy
Unsubscribe rate rising sharply on a specific one-off announcement compared to routine campaigns sent to the same list, even while open/click numbers look fine.
What this means
This wasn't sent to a broken list, it was sent to the WHOLE list, roughly the same reach as a routine digest. A pricing-change announcement is only relevant to current paying customers; non-customers, who have no reason to care, are the likely source of the extra unsubscribes. This is the 'buyers vs. non-buyers' segment split the lesson names, applied to a single send instead of the whole program.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| Unsub rate spikes on a specific announcement relative to routine campaigns to the same list | Before the next pricing/policy announcement, filter the send to the customers segment only and skip prospects entirely | 30 min |
| No buyer vs. non-buyer segment exists in the ESP yet | Create a persistent 'customer' tag so future account-specific announcements don't need to be rebuilt each time | dev ticket |
Step 04 of 04
The lesson's own diagnostic rule: 'A high open rate but low CTR means your subject line is great but your body copy or CTA needs work. A high CTR but low conversion means your landing page is the problem.'
Weekly Product Digest sends click at a perfectly respectable average CTOR (18.77% across all 5), not far below Feature Launch - Kanban Boards' 30.89%, yet Digest generates far less revenue per 1,000 sent. Per the lesson's own rule, where does that gap actually live?
Procedure
- Compute revenue per 1,000 sent for Feature Launch: 9,440.13 / 22,890 * 1,000 = $412.42.
- Compute the same for the 5-digest average: $13,979.73 total revenue / 122,350 total sent * 1,000 = $114.27.
- Compute what Digest's revenue-per-1,000-sent WOULD be if it scaled proportionally with its own CTOR (using Feature Launch's revenue-per-CTOR-point as the baseline): 18.77 * (412.42 / 30.89) = $250.60.
- Compare that predicted number to Digest's actual $114.27.
Feature Launch - Kanban Boards Sent 22,890 | CTOR 30.89% | Revenue $9,440.13 | Revenue per 1,000 sent = $412.42 Weekly Product Digest (5-send average, W18-W22) Avg sent 24,470 | Avg CTOR 18.77% | Avg revenue per 1,000 sent = $114.27 If revenue scaled with CTOR the way Feature Launch's does: predicted Digest revenue/1,000 sent = $250.60 actual Digest revenue/1,000 sent = $114.27 (46% of predicted)
Healthy
Revenue-per-1,000-sent roughly proportional to CTOR across campaign types within the same program.
Unhealthy
Revenue-per-1,000-sent far below what CTOR alone would predict, a post-click (landing page/offer) problem, not an email problem.
What this means
Digest generates only 46% of the revenue-per-1,000-sent that its own click quality would predict, compared to Feature Launch's ratio. The clicks are landing at a reasonable rate, the shortfall shows up after the click. Per the lesson's own rule, that's a landing-page or offer problem, not something a better subject line fixes.
So what do I do about it?
| Symptom | Action | Effort |
|---|---|---|
| Revenue per subscriber persistently low across a recurring campaign type despite normal click behavior | Audit the destination page/offer these digest links point to, not the email copy | half day |
| No per-campaign-type revenue tracking exists yet | Add a revenue-per-1,000-sent column to the standing reporting template so this pattern surfaces automatically each month | 30 min |
Final deliverable
A ranked list of all 10 campaigns from most to least healthy, with the specific benchmark(s) each failing campaign broke and a one-line fix recommendation for each, distinguishing 'rewrite the email' fixes from 'fix the segment/list/landing page' fixes.
See a reference example
Running the same audit against Klaviyo's own outbound program: 'Product Update - Q3 Roadmap' had an 11% open rate and a 0.9% unsub rate, both breaking the benchmark together, but its CTOR (23%) matched the library average, so the fix was pruning 90-day-inactive subscribers before the next send, not rewriting the copy. 'Annual Plan Price Change' opened above 40% with a healthy CTOR, but its 0.6% unsub rate traced back to hitting the full list instead of the paying-customer segment. The Q3 feature-launch email beat every other campaign on revenue per 1,000 sent, proof the program converts fine when it's actually targeted.
Success criteria
You're done when you can:
- Correctly identifies 'Re-engagement - 30 Day Inactive' as the campaign failing both the open-rate floor and the unsubscribe ceiling at once
- Flags 'Pricing Update Announcement' as a second, different kind of risk (high engagement, still elevated unsubscribes) rather than lumping it in with the re-engagement problem
- Diagnosis for the re-engagement campaign is list hygiene/segmentation, not a copy rewrite, and cites the CTOR-among-openers number as evidence
- Diagnosis for Weekly Product Digest's low revenue-per-subscriber points to the landing page/offer, not email copy, citing the CTOR-vs-revenue gap
- Names Welcome Series - Email 1 and Feature Launch - Kanban Boards as the two clearly healthy campaigns, with a reason for each