Analyzing campaign results in a spreadsheet

TL;DR:

• Raw counts hide the truth. Divide clicks by emails sent to get a click rate you can compare between campaigns.

• Keep one row per campaign with the same columns, and let formulas calculate the rates.

• Small groups produce shaky rates. A difference of a few clicks may be luck, so don't overreact.

You sent three emails and got 30, 45 and 20 clicks. Which one worked best? If you simply pick the biggest number, you are ignoring that the three emails may have gone to very different numbers of people. A count tells you what happened. A rate tells you how well it happened relative to the size of the audience.

This guide shows how to line up campaign results in a spreadsheet, calculate rates with formulas that work in Excel and Google Sheets, and read them honestly.

When a spreadsheet is enough

For a handful of campaigns a month, a spreadsheet is the right tool. It is visible, you can check every formula, and anyone on your team can open it. If you find yourself pasting data from dozens of sources every week, then you might want something more automated, but the thinking stays the same.

Set up the sheet

Export the numbers from your email tool, or copy them by hand, into a sheet with one row per campaign. Keep the raw counts you typed or pasted in their own columns, and calculate everything else with formulas. That way you can trace any rate back to its inputs.

Use the same definitions for every row. If one email's "sent" includes bounces and another's does not, the rates are not comparable. Check how your email tool defines each number on its help page, and note the definition in a header or comment.

From counts to rates

A rate is a part divided by a whole. Click rate here is clicks ÷ emails sent. Multiply by 100 for a percentage, or format the cell as a percentage and let the sheet do it.

Put sent in column B and clicks in column C. In D2, the click rate formula is below. Format D as a percentage, then copy the formula down the column.

=C2/B2

If a row might have zero sent, guard the formula so it doesn't show an error: =IF(B2=0,"",C2/B2). It returns an empty cell instead of a divide-by-zero error.

Worked example: comparing three emails' clicks

Here are three example emails. The numbers are made up for this walk-through.

Looking at raw clicks, B wins with 45. Looking at rates (clicks ÷ sent), C wins with 20 ÷ 400 = 0.05. Neither reading is the whole story. Email C went to a smaller group, so its rate rests on fewer people.

Check the arithmetic by going backwards. 5% of 400 is 0.05 × 400 = 20, which matches. 4.5% of 1000 is 0.045 × 1000 = 45. 0.03 × 1000 = 30. All three agree.

Be careful with small numbers

Email C has 20 clicks. If just 3 of those people had not clicked, the count would be 17, and the rate would drop to 17 ÷ 400 = 0.0425, which is 4.25%, below email B. A few people deciding differently flips the ranking. With 1000 sent, three fewer clicks moves B from 4.5% to 42 ÷ 1000 = 4.2%, a smaller shift.

So with groups this size, the honest conclusion is that B and C look similar and both did better than A, but you cannot say which of B or C is truly better. A difference of 0.5 percentage points on a few hundred people is within what chance can produce. Statistics can quantify this, but a plain rule of thumb helps: the fewer people, the less you should read into small gaps.

Better evidence comes from repeating. If B-style how-to emails beat A-style product news across several sends, you can trust the pattern much more than any single comparison.

Mistakes to avoid

• Comparing counts when audiences are different sizes.

• Mixing definitions between rows, such as sent versus delivered.

• Calling a winner from a small gap and a small list.

• Overwriting raw numbers with formulas. Keep inputs and outputs in separate columns.

• Ignoring context: a holiday week, a different subject line style or a different audience can explain a difference as well as the content can.

Doing it faster

Once your layout is stable, keep one sheet and add a row per send, so the formulas fill themselves. Sorting by click rate and adding a column for notes on what was different about each email makes patterns easier to see. If you have a messy export, see cleaning up a spreadsheet first. If you want to talk through a formula while looking at your own sheet, DuckyHelper can see your screen and help.

Frequently asked questions

Should I use opens or clicks?

Clicks are usually a more direct sign of interest, since opens depend on how the email tool and the reader's mail app count them. Check how your tool defines each.

How many sends do I need before trusting a difference?

There is no single number. The bigger the difference and the larger the groups, the more you can trust it. Repeating the comparison across several sends is stronger than any one result.