Formulas

CAC payback period formula

CAC payback period is how many months of margin from a new customer it takes to earn back what you spent acquiring them.

About the Shopify connectorAbout the Google Ads connectorAbout the Meta Ads connector

It turns a cost per customer into a date, which is why finance teams like it. The formula is one division. The mistakes are using revenue where margin belongs, and putting an annual or first-order figure in a denominator that has to be monthly.

The formula

CAC payback (months) = CAC ÷ monthly gross margin per customer

Monthly gross margin per customer = monthly revenue per customer × gross margin

CAC is acquisition spend divided by new customers; the CAC formula page covers what goes into each half.

In Google Sheets, with CAC in column D and monthly gross margin per customer in column E:

=IF(E2=0, "", D2/E2)

The IF stops a channel with no margin figure yet returning a #DIV/0! error.

A worked example

Gross margin is 50% across the board. Monthly revenue per customer is 30.00 for search, 25.00 for shopping and 20.00 for prospecting, which halves to the margin column below.

The total row's CAC is total spend over total new customers (12,000.00 ÷ 230 = 52.17), and its margin per customer is weighted by how many customers each channel brought (3,000.00 ÷ 230 = 13.04). Its payback divides the whole spend by the whole monthly margin:

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

That gives 12,000.00 ÷ 3,000.00 = 4.0 months. Prospecting customers take ten months to pay back, which is a long time to be out of pocket on someone who may not stay that long (see retention rate).

The common mistake

Using revenue instead of gross margin.

Monthly revenue per customer across all channels is 26.09, so revenue payback is 52.17 ÷ 26.09 = 2.0 months. Margin payback is 4.0. The revenue version says the money is back in two months. It is not, because half of each pound went on the goods, and the goods do not pay for ads.

The second mistake is the time window. The denominator has to be a monthly figure: annual revenue per customer divided by twelve, or a run rate from the last few months. A customer's first order is not their monthly revenue, and if you use it the payback for a one-off buyer is one month followed by nothing.

Keeping the inputs current

A scheduled Fresh Sheets report can keep Cost from Google Ads in one tab, for example Google Ads, and your Shopify orders in another, for example Shopify. Which customers are new 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 the columns move. It 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)

That is CAC. Divide it by monthly margin per customer for the payback period.

If you also advertise on Meta Ads, a Meta Ads tab adds its spend to the Google Ads cost the same way, with the heading "Amount spent".