Formulas

CAC formula

Customer acquisition cost (CAC) is what you spend, on average, to win one new customer.

About the Google Ads connectorAbout the Shopify connector

Divide what you spent by how many new customers you got. The mistakes are in both halves: counting orders instead of new customers, and counting media spend as if it were the whole cost. What counts as a good CAC depends entirely on what a customer is worth to you, which is the LTV to CAC ratio page's job.

The formula

CAC = acquisition spend ÷ new customers acquired

Acquisition spend is everything spent to win customers in the period: media, plus agency fees and creative if you want the fully loaded figure. New customers are people who placed their first order in the period, not people who placed an order.

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

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

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

A worked example

The total row divides total spend by total new customers:

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

Averaging the three months would give 42.50, which happens to be close here. It will not be close when one month's spend is three times another's, so divide the totals and never rely on the coincidence.

The common mistake

Dividing by all orders.

September had 450 orders: 180 from new customers and 270 from people who had bought before. Spend over orders is 9,000.00 ÷ 450 = 20.00, which looks like a CAC of twenty pounds. The 270 repeat buyers were already acquired, in some earlier month, by some earlier spend. Their September orders are retention, not acquisition, and they do not belong in the denominator. The real CAC is 50.00, two and a half times the figure that counted everyone.

This is the difference between CAC and CPA. CPA divides spend by conversions as the ad platform counts them, which includes repeat buyers and whatever else has been set up as a conversion. It is a useful number for managing campaigns and a poor one for deciding what a customer costs. The spend half has its own version of the mistake: media alone understates it, and the blended CAC page covers what else to include.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Cost from Google Ads up to date in one tab, for example Google Ads, and your Shopify orders in another, for example Shopify. Which of those orders were first orders is something the orders report cannot tell you, because it carries no customer field; it comes from Shopify's own orders export (Orders, then Export), whose Email column identifies the customer. Source: Shopify Help Centre, Exporting orders. Keep a Customers tab built from it, with one row per customer and, in column C, the month of their first order.

Fresh Sheets replaces each tab's contents on every refresh, so keep your formulas on a separate tab. This version finds the cost column by its heading, so it still works if you add a field and the columns move, and divides by the number of customers on the Customers tab whose first order fell in the month typed in A2 (as a date, the first of the month):

=SUM(INDEX('Google Ads'!A:Z, 0, MATCH("Cost", 'Google Ads'!1:1, 0))) / COUNTIF(Customers!C:C, A2)

Add other channels' cost tabs to the numerator as you connect them.

Work out your CAC with the free calculator