To track cold email reply rate properly, you don’t need a sales platform. You need one spreadsheet, a few columns you fill in by hand, and a handful of formulas that tell you what’s actually working.
That matters because the number most people watch, open rate, has become hard to trust. Apple Mail’s privacy feature fetches images, including tracking pixels, even when the recipient never views the message, so it over-reports opens (Kajabi’s explanation). Replies are different. Someone had to read your email and decide to answer. If you send emails yourself as a freelancer, job seeker, or small business owner, replies are the cleanest signal you have.
This guide shows you exactly how to set up the sheet, which four numbers to calculate, how to read them without fooling yourself, and how to use AI for a quick weekly review.
Contents
Four Numbers, Four Different Questions
Before building anything, it helps to know what each metric is for. Here’s how they compare:
| Metric | What it answers | How reliable | Use it for |
|---|---|---|---|
| Open rate | Did the email load? | Low, since software can trigger opens | A rough hint only |
| Reply rate | Did a real person answer? | High | Judging subject lines and openers |
| Positive reply rate | Did they answer with interest? | High, needs your labeling | Judging the offer and targeting |
| Next-step rate | Did it lead to a call, meeting, or request? | Highest | Judging whether the whole effort pays |
A common mistake is to stop at reply rate. A reply of “please remove me” counts the same as “yes, let’s talk” in that number. If your subject line is attention-grabbing but misleading, reply rate can go up while real opportunities go down. That’s why you track all three of the bottom rows.
Set Up the SheetOpen a new spreadsheet and make these columns in row 1:
- A: Name
- B: Company
- C: Version (A, B, or whatever label you give a subject line)
- D: Date sent
- E: Delivery (Delivered or Bounced)
- F: Replied (Y or blank)
- G: Reply type (Interested, Not now, No, Automatic)
- H: Reply date
- I: Next step (Y or blank)
- J: Follow-up # (0 if they answered your first email, 1 or 2 if it was a later one)
- K: Days to reply, which is a formula
In K2, enter:
=IF(H2="","",H2-D2)
Copy it down. Each row now shows how many days the reply took.
The Delivery column is easy to skip and it’s one of the most important. If an email bounced, nobody saw it, so it shouldn’t count against your subject line. We’ll use it in a moment.
The Formulas That Do the Work
Make a second tab called “Summary.” In the first column, list your versions (A and B). Then use these formulas. They assume your data runs through row 500; adjust the range if your list is longer, and replace the “A” with a cell reference like A2 if you’d rather copy them down.
Emails sent
=COUNTIFS(Data!C2:C500,"A")
Delivered
=COUNTIFS(Data!C2:C500,"A",Data!E2:E500,"Delivered")
Real replies (Automatic replies excluded)
=COUNTIFS(Data!C2:C500,"A",Data!F2:F500,"Y",Data!G2:G500,"<>Automatic")
Reply rate
=IFERROR(RealReplies/Delivered,0)
Positive reply rate
=IFERROR(COUNTIFS(Data!C2:C500,"A",Data!G2:G500,"Interested")/Delivered,0)
Next-step rate
=IFERROR(COUNTIFS(Data!C2:C500,"A",Data!I2:I500,"Y")/Delivered,0)
Typical days to reply (works in Google Sheets)
=IFERROR(MEDIAN(FILTER(Data!K2:K500,Data!C2:C500="A",ISNUMBER(Data!K2:K500))),"")
Replace the names like RealReplies and Delivered with the actual cells where you put those results. Format the rate cells as percentages.
Why Delivered Beats Sent as Your Denominator
Imagine you send 100 emails and 10 bounce. Five people reply. Is your reply rate 5% or 5.6%?
Dividing by delivered emails (90) gives 5.6%. Dividing by sent (100) gives 5%. The difference is small here, but when bounces climb, the gap grows. Worse, if one version of your email happens to hit more bad addresses, using “sent” quietly punishes it for something the subject line didn’t cause.
Use delivered as the standard. Whichever you pick, stay consistent so your rounds can be compared.
Reading the Numbers Without Fooling Yourself
A tidy summary table can feel more certain than it is. These checks keep you honest.
Check the size of the group first. If each version went to 100 people, a reply count of 5 versus 9 is within the range of chance. The companion guide on how to test cold email subject lines on a small list explains how big a gap has to be before it counts.
Compare like with like. Don’t compare Monday’s batch with last month’s batch from a different list. Compare versions sent to a randomly split list at the same time.
Look at the mix, not just the total. Suppose Version A gets 8 replies and Version B gets 6, but A’s are mostly “No” and B’s are mostly “Interested.” B is the better line for the goal you actually care about.
Watch the time lag. If the typical reply arrives three to four days after sending, judging a test after 24 hours is premature. Wait until the median reply window has passed, then add a few more days.
Separate first emails from follow-ups. That’s what the Follow-up # column is for. If one subject line sits on top of a first email and a later follow-up gets the answer, the credit belongs partly to the follow-up. Count first-email replies on their own so you can see how the opener performed by itself.
Worked Example for Cold Email Reply Rate Tracking
Here is how it looks in practice. The figures below are invented for illustration, not real benchmarks.
Say you sent each version to 100 people. In Version A, 4 emails bounced, so 96 were delivered. You got 6 real replies, 3 of them interested, and 1 turned into a call. Version B had 2 bounces, so 98 delivered. It got 8 real replies, but only 2 were interested and none led to a call.
- Version A reply rate: 6 ÷ 96 ≈ 6.3%. Positive: 3 ÷ 96 ≈ 3.1%. Next step: 1 ÷ 96 ≈ 1.0%.
- Version B reply rate: 8 ÷ 98 ≈ 8.2%. Positive: 2 ÷ 98 ≈ 2.0%. Next step: 0%.
If you only watched reply rate, B would look better. Once you track cold email reply rate at all three levels, A looks like the stronger line for what you actually want, even though the gap is small enough that you’d want a second round before deciding. That’s the kind of reading a spreadsheet makes possible and a single percentage hides.
A Short Weekly ReviewSet a calendar reminder for the same time each week. Then run this routine:
- Fill in replies and dates for the past week. Do it before looking at any totals, so you don’t bend the labels toward the result you want.
- Update Delivery. Mark anything that bounced.
- Glance at the Summary tab. Note reply rate, positive reply rate, next-step rate, and typical days to reply.
- Write one sentence. For example: “Version B got more replies, but most were polite no’s.” A single plain sentence forces you to decide what the numbers mean.
- Choose one change for next week, and only one.
The sentence is the real output. A sheet full of numbers you never interpret is just a log.
Let AI Read the Summary, Not Your Contacts
AI can be a useful second pair of eyes on your totals. The key is to give it only the summary, with no names, companies, or email addresses. Copy the small Summary table and paste it in with this prompt:
Here is a summary of two cold email versions. Each went to the same number of people at the same time.
[paste summary table: sent, delivered, real replies, interested replies, next steps, typical days to reply for each version]
Please:
1. Say which version looks better on each metric.
2. Tell me, in plain language, whether the sample is large enough to be confident, and what extra data would help.
3. Suggest ONE single-variable change to try next.
4. Do not invent numbers or assume anything I haven't given you. If something is unclear, ask me.The prompt above is my own wording, not an official or published template.
Rule three is the one that matters most. AI tends to sound confident about small samples, so asking it directly about sample size makes it flag the weakness for you. Treat its answer as a prompt for your own thinking, not a ruling.
If you also want to sort messy replies into labels, the labeling prompt in the earlier guide on testing subject lines handles that step.
Common Slip-Ups and Quick Fixes
- Counting auto-replies – Reply rate looks great, nothing happens. Fix: Label them Automatic and exclude them.
- Ignoring bounces – One version looks worse than it is. Fix: Divide by delivered, not sent.
- Judging too early – Winner changes day to day. Fix: Wait past the typical reply window.
- Editing labels after seeing totals – Results suspiciously match your hopes. Fix: Label replies before opening the Summary tab.
- Mixing lists – Big swings with no clear cause. Fix: Compare only versions sent to the same random split.
- Tracking only replies – Lots of replies, no calls. Fix: Add the next-step column.
Questions About Cold Email Reply Rate Tracking
Is it worth tracking if I only send 50 emails a month? Yes, but expect slow learning. Keep the sheet running over several months, and treat it as a record of what you tried rather than a source of quick verdicts.
Should I track opens at all? You can note them if your tool shows them, but don’t base decisions on them. A change in reply rate is more meaningful.
What counts as a next step? Anything concrete you wanted: a call booked, a request for your portfolio, an introduction to the right person, or an invitation to apply.
Do I need Google Sheets, or will Excel work? Either works. The FILTER formula is easiest in Google Sheets. In Excel, you can use AVERAGEIFS for average days to reply instead of the median.
How do I handle people who reply to a follow-up weeks later? Log the reply date as usual and set Follow-up # to the number of the message they answered. Your days-to-reply calculation will reflect the real delay.
Start With the Sheet, Then Add One Column at a Time
If building all of this feels like a lot, begin with just Name, Version, Date sent, Replied, and Reply type. Add Delivery and Next step once you’ve sent your first batch. When you track cold email reply rate with a plain spreadsheet, you end up with something better than a dashboard of vanity numbers: a clear record of what you tried, what people did in response, and what to try next.
Sources:
Leave a Reply