Formulas

Customer lifetime value formula

Customer lifetime value (CLV, or LTV) is an estimate of the margin one customer brings in over the time they stay with you, built from averages of other customers.

About the Shopify connector

The simple formula below is enough for deciding what you can afford to pay to acquire a customer. The mistake is leaving margin out of it, which doubles the answer and everything built on it.

The formula

CLV = average order value × orders per year × years as a customer × gross margin

Years as a customer is 1 ÷ annual churn rate: if a fifth of customers leave each year, the average customer stays five years. See the churn rate formula for the churn half and AOV for the order value half.

In Google Sheets, with AOV in column C, orders per year in D, years in E and gross margin in F:

=C2*D2*E2*F2

If years comes from churn in column G rather than being typed in, guard the division:

=IF(G2=0, "", 1/G2)

A worked example

The total row weights each segment's CLV by how many customers are in it:

=IF(SUM(B2:B4)=0, "", SUMPRODUCT(B2:B4, G2:G4)/SUM(B2:B4))

That is (500 × 240.00 + 1,500 × 52.50 + 3,000 × 15.00) ÷ 5,000 = 243,750.00 ÷ 5,000 = 48.75. A plain average of the three segments gives 102.50, which describes a customer who does not exist: three in five customers buy once and leave.

The common mistake

Leaving gross margin out.

Without the last term, a subscriber is "worth" 480.00 and the blended figure is 97.50. That is revenue, and half of it was spent on the goods before it reached you. Put a 97.50 lifetime value beside a 32.50 acquisition cost and the LTV to CAC ratio reads 3:1. With margin in, it is 48.75 to 32.50, or 1.5:1, which is a different business.

"Years as a customer" is a forecast. If you have been trading for eighteen months you have not observed a two-year lifespan; you have assumed one. Mark the cell as an assumption and revisit it as the cohorts age (see cohort analysis).

Keeping the inputs current

A scheduled Fresh Sheets report can keep your Shopify orders in their own tab, for example one called Shopify. Average order value comes from that tab; orders per customer per year needs a customer against each order, which the report does not carry, so it comes from Shopify's own orders export (Orders, then Export), whose Email column identifies the customer. Source: Shopify Help Centre, Exporting orders. Gross margin comes from your own cost figures, so keep it on a tab of its own.

Fresh Sheets replaces the Shopify tab's contents on every refresh, so keep your formulas on a separate tab. This version finds the net payment and orders columns by their headings, so it still works if you add a field and the columns move. Net payment is what the store received after refunds:

=SUM(INDEX(Shopify!A:Z, 0, MATCH("Net payment", Shopify!1:1, 0))) / SUM(INDEX(Shopify!A:Z, 0, MATCH("Orders", Shopify!1:1, 0)))

That is AOV. The other three terms stay on your own assumptions tab.

Work out your customer lifetime value with the free LTV calculator