Why averaging your daily CPL gives the wrong answer

Open almost any weekly marketing report and you will find a cost per lead that is quietly wrong. Not wrong by a rounding error. Wrong by enough to change what you decide to do next.

It happens because of one habit: computing CPL for each day, then averaging those daily numbers to get the number for the week.

What goes wrong

Consider two days on the same campaign.

Average the two daily figures and you get ₹125. That number describes nothing that happened. The week actually cost ₹20,000 and produced 130 leads, which is ₹154. You have understated your true cost by 19%, and you did it in the direction that makes the campaign look better than it is.

The gap comes from treating both days as equally important. Monday carried 10% of the spend and got the same vote as Tuesday, which carried 90%. That is what an average of ratios does: it discards the weight behind each ratio.

A ratio of sums is not the same as a sum of ratios. Every weighted marketing metric has this property, and every one of them breaks the same way.

Why it survives in real reports

Spreadsheets make the wrong version easier. You already have a CPL column, so dragging AVERAGE() down it takes two seconds, while the correct version means going back to the two underlying columns. The wrong answer is one keystroke closer than the right one.

It also hides well. On a stable campaign the two numbers sit within a few percent of each other, so nobody notices — until a day with unusual spend or a tracking gap pulls them apart, which is precisely the week you are being asked to explain.

The fix

Sum the numerator, sum the denominator, then divide. Once.

-- wrong: averages the daily ratios
SELECT AVG(spend / leads) FROM daily;

-- right: one ratio, computed from the totals
SELECT SUM(spend) / NULLIF(SUM(leads), 0) FROM daily;

The NULLIF matters more than it looks. A day with spend and zero leads is common, and without it the query dies on a division by zero on exactly the days you most want to see.

The same rule governs CTR, CPC, ROAS, conversion rate and every other metric built as one number over another. If you can only remember one thing: never average a column that is already a ratio.

What to check this week

Open your reporting and find every place a rate is aggregated. For each one, recompute it from the underlying totals and compare. On a campaign with even mildly uneven daily spend, expect a difference in the 10 to 20% range.

Then decide whether any call you made in the last quarter would have gone differently at the correct number. That is the part that tells you whether this was a technicality or a real cost.