Formulas

CPA formula

Cost per acquisition (CPA) is what you paid, on average, for each conversion the ad platform recorded.

About the Google Ads connector

"Acquisition" here means whatever the platform counts as a conversion: a purchase, a sign-up, a form. It is not the same as customer acquisition cost, which counts new customers and nothing else. The formula is one division. The mistake is reading the answer before the inputs have finished arriving.

The formula

CPA = ad spend ÷ conversions

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

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

Format the column as currency. The IF stops a campaign with no conversions yet returning a #DIV/0! error.

A worked example

The total row divides total cost by total conversions:

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

=AVERAGE(D2:D4) would return 26.67. The account CPA is 21.03, because Shopping carries two thirds of the conversions and the average does not know that. The reason is spelled out on the ROAS page and it is the same for every ratio.

The common mistake

Reading last week's CPA before last week's conversions have arrived.

Google Ads records a conversion against the day of the click that led to it, not the day the purchase happened; the columns with "(by conv. time)" in the name are the exception, and report by the day of the conversion. Source: Google Ads Help, Understand your conversion tracking data. Someone who clicks on a Tuesday and buys on the Saturday adds a conversion to Tuesday. So a recent period's conversion count keeps rising for days after the period ends, and its CPA keeps falling. Read on 1 October, the Prospecting campaign's September showed 300.00 of cost and 4 conversions, a CPA of 75.00. Read again two weeks later, the same September showed 6 conversions and a CPA of 50.00. Nothing changed except the date someone looked.

Compare two periods only once both are old enough for their conversions to have landed, and give each the same gap. A dashboard that reads this week against last week is reading a finished number against an unfinished one, and it will always say this week got worse.

What counts as good

A CPA only means something next to what a conversion brings in. If a conversion is an order, the figure to put beside it is the contribution margin per order: a CPA of 21.03 is fine when an order leaves 30.00 of margin and a slow loss when it leaves 15.00. If a conversion is a lead, the comparison runs through the share of leads that become customers, which is a longer sum and the reason CAC exists.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Cost and Conversions from Google Ads up to date in their own tab, for example one called Google 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, 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("Conversions", 'Google Ads'!1:1, 0)))

Each run asks Google Ads for the whole date range again, plus the seven days before it, and replaces the tab, so late conversions for dates in that window still arrive. Late credit for an earlier date never reaches the tab.