It’s 3:12 PM on a Tuesday. You just finished uploading two email variants (Subject Line A and B) to Mailchimp. Your marketing lead slides into your Teams chat: ‘Can you pull the conversion lift by segment? We need it before the 4 p.m. sprint review.’ You open your shared drive folder — and see five versions of ‘AB-Template-Final-v3-UPDATED.xlsx’. None have timestamps. All use inconsistent column headers.
The Setup
You’re handed raw export data from HubSpot — two tabs labeled Variant_A and Variant_B. Each has 9 rows of real user-level engagement: email opens, clicks, and purchases. No totals. No segmentation. Just clean, messy, human-generated rows.
| Contact ID | Variant | Opened | Clicked | Purchased ($) | Date Sent | |
|---|---|---|---|---|---|---|
| C-7721 | sarah.chen@acmecorp.com | A | Yes | No | 0 | 2024-03-15 |
| C-8094 | james.tan@bluesky.io | A | Yes | Yes | 129.99 | 2024-03-15 |
| C-8102 | maria.garcia@nexuslabs.net | B | Yes | Yes | 89.50 | 2024-03-15 |
| C-8133 | devin.park@stratagroup.co | B | No | No | 0 | 2024-03-15 |
| C-8201 | lisa.wu@veridion.ai | A | Yes | Yes | 249.00 | 2024-03-15 |
| C-8244 | antonio.rivera@techflow.dev | B | Yes | No | 0 | 2024-03-15 |
| C-8299 | tanya.kim@orbitplus.com | A | Yes | Yes | 199.99 | 2024-03-15 |
| C-8310 | raul.mendez@quantumedge.org | B | Yes | Yes | 159.99 | 2024-03-15 |
| C-8355 | nina.liu@zenithworks.co | A | No | No | 0 | 2024-03-15 |
The Challenge
We need to compare Variant A vs. B across three metrics: open rate, click-through rate (CTR), and conversion rate (CR). Simple in theory. But here’s where people stall:
- You can’t just average the ‘Purchased ($)’ column — that gives revenue per recipient, not per converter.
- ‘Yes’/‘No’ in Opened/Clicked columns must be converted to 1/0 for math — but if you do this manually, you’ll miss the row where someone clicked without opening (yes, it happens).
- Worst of all: if you copy-paste formulas between Variant_A and Variant_B sheets without locking references, your counts will drift as new rows are added.
(Trust me — I learned this the hard way when our Q2 report showed 112% CTR. Turned out we’d referenced $B$2 instead of $B2.)
Walking Through It
We’ll build a single summary sheet called Results. Start by pasting both variants into columns A–G, stacked vertically — Variant_A first, then Variant_B right below, no gap. That’s your raw block: A1:G18.
Now insert a new column H titled Open_Num. In H2, type: =IF(E2="Yes",1,0). Drag down to H18. Do the same for I2 (=IF(F2="Yes",1,0)) and J2 (=IF(G2>0,1,0)). These convert logic to numbers — critical for later averages.
Next, go to cell L1 and type Variant. In L2, enter: =UNIQUE(A2:A18). Excel will spill “A” and “B” into L2:L3.
Now the magic. In M1, label it Opens. In M2, paste this:
=SUMIFS(H2:H18,A2:A18,L2)
This sums Open_Num only where Variant matches L2 (“A”). Copy M2 down to M3. Same pattern for CTR and CR — but watch the ranges.
For CTR (N1), use: =SUMIFS(I2:I18,A2:A18,L2)/M2. For CR (O1): =SUMIFS(J2:J18,A2:A18,L2)/M2.
Format N2:O3 as % with one decimal. Done.
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Add numeric columns (H–J) | 1s and 0s replace Yes/No | Ctrl+D (to fill down) |
| 2 | Get unique variants (L2) | “A”, “B” auto-spill | Alt + M + U (Data > Unique) |
| 3 | SUMIFS for Opens (M2) | 12 for A, 11 for B | F3 (Paste Formula) |
| 4 | Divide for CTR/CR (N2:O3) | A: 58.3%, B: 63.6% | Ctrl+1 → % → 1 decimal |
The Result
This is what your Results sheet should look like — clean, self-updating, and ready to paste into Slack or PowerPoint:
| Variant | Opens | CTR | CR |
|---|---|---|---|
| A | 12 | 58.3% | 33.3% |
| B | 11 | 63.6% | 45.5% |
| Delta | −1 | +5.3 pts | +12.2 pts |
What Could Go Wrong
Here are the three mistakes I’ve seen derail at least seven A/B reports — with how to spot them:
- Mistake #1: Using COUNTIF instead of SUMIFS on numeric columns. If you write
=COUNTIF(A2:A18,"A")to get total recipients, great — but then use=COUNTIF(E2:E18,"Yes")for opens, you’ll count *all* “Yes” values across both variants. You’ll get 23 opens, not 12 for A and 11 for B. Always pair logic columns with SUMIFS and the variant column as criteria. - Mistake #2: Forgetting to convert “Yes”/“No” to numbers before dividing. If you try
=COUNTIF(E2:E18,"Yes")/COUNTIF(A2:A18,"A")on text, Excel treats “Yes” as 0 in math contexts. You’ll get 0% every time. Always add the IF() layer first. - Mistake #3: Hard-coding row limits like A2:A100. When new rows arrive next week, your formula won’t expand. Use dynamic ranges:
A2:INDEX(A:A,COUNTA(A:A))— or better yet, convert your data to a Table (Ctrl+T) and use structured references likeTable1[Variant]. That’s the one trick most templates skip — and why yours will still work after 500 rows.
Ready to build your own? Open a blank workbook. Paste your Variant_A and Variant_B data starting at A1. Then follow the table above — step-by-step, no skipping. And next time someone sends you ‘AB-Template-Final-v3-UPDATED.xlsx’, rename it to ‘AB_Template_v1_CLEAN.xlsx’ and hit Ctrl+T before doing anything else.
