Formulas

CPM formula

Cost per thousand impressions (CPM) is what you pay for a thousand views of an ad.

About the TikTok Ads connectorAbout the Meta Ads connector

It is how most display, video and social inventory is priced, so it is the number to watch when you are buying reach rather than clicks. The formula is a division and a multiplication, and the multiplication is where it goes wrong. What counts as a good CPM depends on the platform and the audience, so compare against your own history (see what is a good CPM).

The formula

CPM = (ad spend ÷ impressions) × 1,000

In Google Sheets, with spend in column B and impressions in column C:

=IF(C2=0, "", B2/C2*1000)

Format the column as currency to two decimal places. The IF stops a campaign with no impressions returning a #DIV/0! error.

A worked example

The total row divides total spend by total impressions, then multiplies:

=IF(SUM(C2:C4)=0, "", SUM(B2:B4)/SUM(C2:C4)*1000)

Retargeting costs three times as much per thousand as awareness. That is normal. A small, specific audience costs more to reach than a broad one.

The common mistake

Losing the thousand.

Spend divided by impressions gives the cost of one impression: 2,000.00 ÷ 260,000 = 0.0077, less than a penny, and a figure nobody can compare with anything. Multiply by 1,000 and you have 7.69. The reverse error is just as common. If the impressions column already holds thousands (260 rather than 260,000) and the formula still multiplies by 1,000, the answer is 7,692.31, and someone will spend a morning looking for the campaign that cost seven thousand pounds. Decide once whether the column holds units or thousands, and write it in the heading so the next person knows.

Averaging the three CPMs gives 9.73 rather than 7.69, for the reason set out on the ROAS page: an average gives the 20,000-impression campaign the same weight as the 150,000 one. Divide the totals.

What counts as good

A CPM says what the reach cost, not what it was worth. A low CPM on an audience that never buys is still money lost. Read it beside CPC and CPA before deciding a campaign is cheap.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Spend and Impressions from TikTok Ads up to date in their own tab, for example one called TikTok Ads, with one row per campaign.

Fresh Sheets replaces that tab's contents on every refresh, so keep your formulas on a separate tab. This version finds each column by its heading (use the headings as they appear in your tab), so it still works if you add a field and the columns move:

=SUM(INDEX('TikTok Ads'!A:Z, 0, MATCH("Spend", 'TikTok Ads'!1:1, 0))) / SUM(INDEX('TikTok Ads'!A:Z, 0, MATCH("Impressions", 'TikTok Ads'!1:1, 0))) * 1000

A Meta Ads report works the same way in a tab of its own. Its spend column is called Amount spent:

=SUM(INDEX('Meta Ads'!A:Z, 0, MATCH("Amount spent", 'Meta Ads'!1:1, 0))) / SUM(INDEX('Meta Ads'!A:Z, 0, MATCH("Impressions", 'Meta Ads'!1:1, 0))) * 1000

Work out your CPM with the free calculator