Formulas

CPC formula: how to calculate cost per click

Cost per click (CPC) is what you paid, on average, for each click on your ads.

About the Google Ads connector

The formula is one division. The mistakes come from which cost and which clicks go into it, and from averaging CPCs that were never meant to be averaged.

The formula

CPC = cost ÷ clicks

In Google Sheets, with cost in column B and clicks in column C:

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

The IF leaves a campaign with no clicks blank rather than returning #DIV/0!. Format the column as currency to two decimal places.

Use the cost figure your ad platform reports for the same date range as the clicks. Google Ads already shows an average CPC per campaign. A spreadsheet is for the CPC across several accounts or platforms, which it cannot give you.

A worked example

The total row divides total cost by total clicks: =B5/C5, which is 1,220 ÷ 1,550 = 0.79.

The common mistake

=AVERAGE(D2:D4) returns 1.03. The correct account CPC is 0.79.

An average of the three CPCs gives the prospecting campaign, with 150 clicks, the same weight as the shopping campaign, with 1,000. Its £2.00 CPC drags the average up by about a third. The clicks you actually bought were mostly cheap ones, and the account figure should say so. Divide total cost by total clicks instead, and apply the same rule when you combine weeks or ad groups.

The same thing happens the other way round. If the expensive campaign had the most clicks, averaging would understate what you really paid.

What counts as good

There is no useful general answer. A CPC is only good or bad against what a click is worth to you, which depends on how often a click converts and what a conversion is worth. A £2.00 click that converts once in twenty costs £40.00 per conversion; a £0.30 click converting once every two hundred costs £60.00. Judge CPC alongside conversion rate and CPA. If you want a published figure for your industry, what is a good CPC covers what the benchmarks can and cannot tell you.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Cost and Clicks for every campaign up to date in a tab of their own, for example one called Google Ads.

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, so it still works if you add a field and the columns move:

=SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0))) / SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Clicks", 'Google Ads'!1:1, 0)))

Work out your CPC with the free calculator