Task 1 of 6
In B4:C4, calculate each group's conversion rate and give those cells the Percentage format.
Lesson theory
In an A/B test, website visitors are randomly split into two groups: A sees the current page and B sees the new one (a different button, price, or text). The metric is usually the conversion rate: the share of visitors who bought. If B converts better, that isn't a win yet. You need to check that it isn't chance.
For two proportions, use a z-test:
- the conversion rates pA = purchasesA / visitorsA and pB;
- the pooled conversion rate p̂ = (purchasesA + purchasesB) / (visitorsA + visitorsB). This is what conversion would look like if there were no difference;
- the standard error SE = √(p̂·(1-p̂)·(1/nA + 1/nB));
- z = (pB - pA) / SE;
- the two-tailed p-value =
2*(1-NORM.S.DIST(ABS(z),TRUE)). Compare it with α.
Uplift = pB / pA - 1 is the relative gain. "Conversion rose from 4.0% to 4.5%" is +0.5 percentage points, but an uplift of +12.5%.
Rules for an honest test:
- choose the primary metric, α, and the sample size in advance. Don't stop the test as soon as p "turns green": frequent peeking inflates false wins;
- run the test for full weeks so weekdays and weekends land in both groups;
- check that the groups split about evenly: a strong skew in visitor counts points to a bug in the split.
Test yourself
Practice is part of Pro
The live spreadsheet, task checking and .xlsx files in all 170 lessons. Your progress is saved in this browser; sync across devices is coming soon.