Imagine you are playing the Heads or Tails game and count a successful conversion (win) when the coin lands Heads. A fair coin will land 50% of the time Heads and 50% of the time Tails. If you were playing a game with only a single coin toss in each round, you would have expected to see a fairly equal number of Heads and Tails in the long run. For example, after 10 rounds:

1 |
H,O,H,O,O,O,O,O,H,O |

In a more complex game, you may want to toss multiple coins in each round. For example, 3 rounds and 10 coin tosses in each:

1 |
TTTHHHHHHT,HHHTHTHHHT,HHTHHTHHHT |

Count the number of Heads (wins/conversions) in each round. In the example above it is 6, 7 and 7 successes respectively. The probability of observing \(x\) successes (Heads) in \(n\) trials (tosses) when the probability of Heads is \(p\) can be calculated with the binomial distribution:

\[P(x,n,p) = {n \choose x} p^x (1-p)^{n-x}\]

It can be easily calculated in Excel. For example, to calculate the probability of 3 successes in 10 trials with 0.5 probability of success use this formula:

1 |
=BINOM.DIST(3, 10, 0.5, FALSE) |

The result is 11% chance of seeing 3 Heads.

Conversion Rates (and Click-through Rates etc.) can be easily modeled with a coin toss game. Each impression of an ad will be a coin toss and each click a will be a success (coin landing Heads). The probability of landing Heads is the Conversion Rate.

Let us get back to our game. Imagine you do not know if the coin is fair or not. To play the game we need to somehow estimate the probability of it landing Heads (you would not play a game where the odds are against you). The simplest way to estimate the probability of success would be to make a lot of trials (tosses) and calculate the proportion of Heads to Tails.

\[P(H) = \frac{H}{n}\]

Every marketer knows the formula for Conversion Rate as the number of clicks divided by the number of ad impressions. OK, let us get back to our coin. We perform the experiment and in 10 coin tosses we observe:

1 |
TTTHTTTTTT |

Just a single success! The probability of this outcome assuming the coin is fair can be calculated in Excel:

1 |
=BINOM.DIST(1,10,0.5,FALSE) |

The result is 0.1% which is not really convincing that the coin is fair.

OK – I will make things a bit more difficult. What if you could only toss the coin once? Just one go to determine if it is fair or not. You toss it and it lands Tails. The standard equation for Conversion Rate would give you an estimate of 0% chance for Heads. Is this correct? OK, what if you had two trials and observe **HH**. Does this mean a 100% Conversion Rate? No – this does not feel right. The problem is in the sample size. The natural way of estimating Conversion Rate with a proportion works only **provided** you have enough samples/trials. The same principle applies to marketing campaigns and ads. If you had just a single visitor and no sales you would not assume the Conversion Rate is 0%. You need a larger sample.

Now, if you are a marketer pause here and go check your campaigns. Notice that the smaller the number of ad impressions the higher the estimated Conversion Rate. You now know the reason why.

Imagine you have 1000 completely independent campaigns each with a true Conversion Rate of 4% and each with just 10 impressions. From the Binomial Distribution, the probability of observing no conversions for a campaign is 66%, of observing 1 conversion 27%, of observing 2 conversions 5% etc. This means that from the 1000 campaigns on average you can expect 665 campaigns to have no conversions (your campaigning software may show a 0% estimated Conversion Rate), 278 campaigns with 1 conversion (for a 10% estimated Conversion Rate) and 52 campaigns with 2 conversions (for a 20% estimated Conversion Rate). None of these estimates is even close to the actual 4%.

Luckily there is a solution – Confidence Intervals. Instead of saying that the Conversion Rate is equal to a single number, we will provide an interval where it lies with a certain probability of making an error. For example, instead of "The Conversion Rate for this campaign is 20%" you will say "With 5% error, the Conversion Rate lies between 0.1% and 25%".

Exact Confidence Intervals for Binomial Proportion can be calculated easily in Excel. No need to use R or SAS. I will assume you store the desired error chance in cell B1, the number of ad impressions in column A starting from row 5 and the number of clicks in column B starting from row 5 (see the screenshot below). The formula for the lower bound for Conversion Rate Confidence Interval is:

1 |
=IF(B5=0,0,BETA.INV($B$1/2,B5,A5-B5+1)) |

For the upper bound:

1 |
=IF(B5=A5,1,BETA.INV(1-$B$1/2,B5+1,A5-B5)) |

I use the IF function to take care of edge cases.

For 1 click after 10 impressions, with an error chance of 5%, our Conversion Rate lies between 0.25% and 44.50%. The more impressions you have, the more accurate the estimate. After 1000 impressions and 10 clicks the Confidence Interval narrows down to the 0.48% to 1.83% range.

In day-to-day analysis, it is often better to exclude campaigns with low impression numbers. A good rule of thumb is to only analyze ads with more than 1000 impressions.