Formulas

CPA and CAC in Google Sheets

Two formulas that get mixed up, how they differ, and the averaging trap.

About the Google Ads connectorAbout the TikTok Ads connector

CPA and CAC both tell you what it costs to win something, and they often get used as if they were the same number. They aren't, and mixing them up is how a campaign looks cheap in one meeting and expensive in the next.

The formulas

CPA = ad spend ÷ conversions

CAC = acquisition spend ÷ new customers

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

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

The IF stops a campaign with no conversions returning a #DIV/0! error.

The difference

CPA is cost per conversion, as the ad platform counts it. A conversion is whatever action you've told the platform to count, whether that's a purchase, a lead or a sign-up, and one person can convert more than once. The platform also decides which of its own ads gets the credit.

CAC is cost per new customer, and it usually looks across every channel at once. It answers a business question rather than a platform one: what does it cost us to gain a customer?

A worked example

The total row is:

=SUM(B2:B4)/SUM(C2:C4)

For CAC: across Google Ads (1,850.00) and TikTok Ads (650.00), 2,500.00 of spend brought in 125 new customers, so CAC is 20.00.

Never average CPA

=AVERAGE(D2:D4) returns 28.33. The correct figure is 17.68.

It's the same trap as averaging ROAS. Every campaign gets equal weight, so here the small Prospecting campaign at 60.00 drags the average up. Divide total spend by total conversions instead.

What counts in CAC

There's no single agreed definition, so decide yours and write it next to the number. The usual choices:

• Spend: ad spend only, or all sales and marketing costs, including salaries, tools and agency fees.
• Customers: new customers only, or every customer, and whether customers who never touched a paid ad count.
• Timing: spend and customers from the same period, or with a lag for a longer buying cycle.

Keeping the inputs current

A scheduled Fresh Sheets Google Ads report by day, with Cost and Conversions, keeps CPA's inputs current, and a TikTok Ads report can add its Spend and Conversions alongside. The new-customer count for CAC can come from your store or CRM, or from a GA4 report using First time purchasers.

Keep your formulas on a separate tab, since each report replaces its own tab on every refresh. This version finds each column by its heading:

=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)))

Google Ads can report fractional conversions under data-driven attribution, so don't be alarmed by a CPA built on 68.4 of them.