Home / Blog / CTR, CPC and CPM
How-to · 2026

CTR, CPC and CPM: What Your Export Column Is Not Telling You

Three formulas anyone can recite, three columns already in your export, and one property that makes those columns unusable the moment you group by anything.

By · Published August 20, 2026 · 7 min read
Twenty-five years starting companies, consulting, owning a multi-million-dollar P&L and leading business intelligence for large enterprises.

Three formulas, all easy, all already computed for you in the export. The difficulty is not remembering them. It is that the columns the platforms give you are correct for their own row and wrong for every total you build by averaging them.

The three, stated once

CTR  = clicks / impressions
CPC  = spend / clicks
CPM  = 1000 × spend / impressions

Each answers a different question. CTR asks whether the ad earned attention. CPC asks what attention cost. CPM asks what exposure cost, whether or not anyone acted on it.

They are not independent. CPC = CPM / (1000 × CTR), which is worth internalising: a rising CPC is either a more expensive auction or a weaker click rate, and the two have completely different responses. Looking at CPC alone cannot tell you which you have.

The property that matters

All three are ratios, and ratios do not aggregate. Two campaigns in one month:

Campaign   Impressions   Clicks   CTR
Brand        2,000,000     8,000   0.0040
Test               200         2   0.0100
          ----------     -----
Total        2,000,200     8,002

Ratio of sums   8,002 / 2,000,200  =  0.0040
Average of CTR  (0.0040 + 0.0100)/2 =  0.0070

The averaged figure is 75% higher, and it is describing a test campaign that served two hundred impressions. Nothing in a table of averaged CTRs indicates that this has happened; the number simply reads high, and high CTR reads as good news.

The same applies to CPC, CPM and every other rate in those files. The rule is one line: sum the numerator, sum the denominator, divide once at the end.

What each rate is actually sensitive to

CTR moves with audience and creative together, and cannot separate them. A broader audience usually lowers it, a more specific promise usually raises it, and a promise the landing page does not keep raises it while damaging everything after the click. CTR is a good comparison between two creatives shown to the same audience and a poor target in isolation.

CPC is auction pressure and click rate combined, per the identity above. It rises when competitors bid up and falls when your ad earns more clicks per impression — two opposite causes, one direction of travel.

CPM is the cleanest read on the auction itself, because it does not depend on whether anyone clicked. If CPM is stable and CPC is climbing, the auction has not changed and your click rate has. That single comparison is the most useful thing in this piece.

From the reporting side of the table

The averaged-rate error is not usually laziness. It is that the platform put a CTR column in the export, and a column that exists looks like a column you are meant to use. Nobody stops to ask whether it survives a SUM, because it is a percentage and percentages feel like facts rather than like the results of a division.

What broke the habit for me was a table where the CTR did not follow from the clicks and impressions printed beside it. Once the three sit together, the inconsistency is visible to anyone who can divide, and I have not built a rate table without its raw counts since.

Doing it in Excel

Per campaign, from the raw counts:

Impressions  =SUMIFS(Ads[Impressions],Ads[Campaign],$A2)
Clicks       =SUMIFS(Ads[Clicks],Ads[Campaign],$A2)
Spend        =SUMIFS(Ads[Spend],Ads[Campaign],$A2)
CTR          =IFERROR(C2/B2,"")
CPC          =IFERROR(D2/C2,"")
CPM          =IFERROR(1000*D2/B2,"")

Leave the three raw columns in the output. They are the check: anyone can confirm the rates follow from them, and a rate nobody can check is a rate that stays wrong for a long time.

Format the whole CTR column as a percentage or none of it. Mixed conventions in one table is the second most common error here, after the averaging, and it is worse in some ways because it is invisible in a screenshot.

Comparing across platforms

Careful. Meta's link_clicks counts a narrower set of interactions than Google's clicks, so a CTR comparison between the two is not quite like for like even when both are computed correctly. The mapping and its limits are covered in combining Google, Meta and LinkedIn exports.

Comparing a platform against itself over time is sound. Comparing platforms against each other is directional at best, and worth saying out loud when the comparison is what the table is for.

One definition, at every level

Quiriz for paid media computes CTR, CPC and CPM from summed counts, so the campaign figure, the platform figure and the monthly figure are all consistent with one another and with the raw totals. In Excel that is =QUIRIZ.ASK("ctr and cpc by campaign last month", "table").

Rates come back as decimals — 0.0408, not 4.08% — deliberately, so that a figure is never half-formatted into meaning something else.

The honest boundary: these are the platform's counts. Impressions, clicks and conversions are what the platform recorded and reported, and the export gives no independent view of any of them.

If CPM is flat and CPC is rising, your auction has not changed and your click rate has. That one comparison is worth more than either number alone.

Get rates that survive a roll-up

Upload your ad export and ask for CTR, CPC and CPM by campaign or platform in plain English. Free to start.

Try Quiriz free →

Frequently asked questions

What is the formula for CTR?
Clicks divided by impressions. Across a group of rows it is total clicks divided by total impressions, not the average of the per-row CTR column. The two disagree whenever rows differ in size, which is always, and the average version is dominated by your smallest campaigns.
How do you calculate CPM?
One thousand multiplied by spend, divided by impressions — the cost of reaching a thousand impressions. Across a group, use total spend and total impressions. The factor of a thousand is convention only; it exists because the underlying number is inconveniently small.
Why is my average CTR different from total clicks over total impressions?
An average treats every row as one vote. A ratio of sums weights every row by its impressions. A campaign that served two hundred impressions and got two clicks has a CTR of 1%, and in an average it counts as much as a campaign with two million impressions at 0.4%. The ratio of sums is the figure that describes what actually happened.
Is a higher CTR always better?
No. CTR measures whether the ad earned the click, not whether the click was worth having. Broad and cheap targeting can lift CTR while lowering the quality of everything downstream, and a creative that promises more than the page delivers does the same. CTR is a useful diagnostic between two ads for the same audience and a poor target on its own.
Should CTR be shown as a decimal or a percentage?
Either, consistently, and labelled. 0.0408 and 4.08% are the same figure. The failure is a table where one column is a decimal and another is a percentage, which happens constantly when platform exports and hand-built columns sit side by side, and it is read wrong every single time.