CPA vs CPL, and Why Your Number Is Wrong

The naming argument is twenty seconds long. The arithmetic error underneath it survives in almost every spreadsheet, produces a number that is off by tens of percent, and looks entirely reasonable.

The naming question takes twenty seconds: CPA and CPL are the same formula. Spend divided by conversions. Whether you call the conversion an acquisition or a lead is a definition you chose, not a different metric.

The question worth eight minutes is the arithmetic underneath, because there are two ways to compute it, they disagree, and the wrong one is the default in almost every spreadsheet.

Two ways to compute one number

Ratio of sums    SUM(spend) / SUM(conversions)      correct
Average of rates AVERAGE(daily CPA column)          wrong

They look interchangeable and they are not. A worked week:

Day     Spend   Conversions   Daily CPA
Mon     2,000        50           40.00
Tue     1,800        45           40.00
Wed     2,200        55           40.00
Thu     1,900        38           50.00
Fri         2         1            2.00
       ------      ----
Total   7,902       189

Ratio of sums     7,902 / 189  =  41.81
Average of rates  (40+40+40+50+2)/5 =  34.40

Seventeen percent apart, and the gap is entirely one Friday where a campaign spent two currency units before being paused. In the average it carries the same weight as Monday. In the correct figure it carries the weight it deserves, which is almost none.

Real exports are far worse than this example. A month with a few near-zero days, a paused campaign, or a weekend on a small budget will move an averaged CPA by tens of percent, always downwards, and always in the direction that makes the account look better.

Why the wrong version is the default

Because the platforms hand you a rate column. Google Ads, Meta and LinkedIn all export a per-row cost-per-conversion, and it is right there next to the data, and averaging it is one function.

That column is correct for its own row. It is not an ingredient. The moment you aggregate across rows — by campaign, by month, by platform — the only correct move is to go back to spend and conversions and divide the totals. A rate column cannot be re-aggregated, and neither can any of the other rate columns in those exports.

Which means a report that gives you CPA and no conversion count is not a report you can roll up. That is worth checking before building anything on top of an export.

Days with spend and no conversions

The most informative rows in the file, and the ones a rate column destroys. Spend of 400 and zero conversions produces a division by zero. It errors, someone wraps it in IFERROR, and the day disappears from the average.

In a ratio of sums it behaves correctly with no special handling: 400 joins the numerator, zero joins the denominator, and the blended figure rises exactly as it should.

It is also worth counting them on their own:

=COUNTIFS(Ads[Spend],">0",Ads[Conversions],0)

A campaign with thirty such days is telling you something no CPA figure at any level of aggregation will.

Doing it in Excel

With an export in a table named Ads, per campaign:

Spend        =SUMIFS(Ads[Spend],Ads[Campaign],$A2)
Conversions  =SUMIFS(Ads[Conversions],Ads[Campaign],$A2)
CPA          =IFERROR(B2/C2,"no conversions")

Note the IFERROR text. A campaign with spend and no conversions is not an error, and printing a blank hides it. Saying so in words puts it in front of whoever reads the table.

Adding a month column with =EOMONTH([@Date],0) and a second criterion gives the monthly series, and the arithmetic stays correct at every level because sums aggregate and rates do not. That property is the whole reason to work this way.

CPA is a weak target on its own

Even computed correctly, it answers a narrow question. It says what a conversion cost and nothing about what the conversion was worth. A campaign driving cheap conversions on your lowest-margin product will hit every CPA goal you set and lose money doing it.

The pairing that works is CPA against contribution margin per order — which needs the store side of the data, and that join is its own piece of work, covered in joining Shopify and ad exports. If you only ever look at one number, ROAS at least carries value in the numerator, though it carries the platform's opinion of that value.

One definition, computed the same way every time

Quiriz for paid media defines cost per conversion as summed spend over summed conversions — never as the average of a rate column — and the same holds for CPC, CPM, CTR and ROAS. In Excel that is =QUIRIZ.ASK("cost per conversion by campaign last month", "table").

Rates come back as decimals rather than percentages, so a CTR reads 0.0408 rather than 4.08%. Worth knowing before your first answer looks wrong.

The honest boundary: a conversion count is the platform's claim, not what your business banked. We compute what your export says. Whether those conversions happened, and whether the platform that claimed them caused them, is a question the export cannot answer — see why your conversions do not match Shopify.

Frequently asked questions