Customer Lifetime Value (LTV) is one of the most strategically important numbers in any business, and one of the least consistently calculated. Without knowing what a customer is worth over their lifetime, you can't make rational decisions about how much to spend to acquire one. This guide shows you how to calculate LTV in Google Sheets, simply and correctly.
The Basic LTV Formula
LTV = Average Purchase Value × Purchase Frequency × Customer Lifespan
For a subscription business: LTV = Average MRR × Gross Margin % / Monthly Churn Rate
Both approaches are valid — use whichever fits your business model.
For E-commerce and Transaction Businesses
You need three inputs from your order history data:
Average Purchase Value: total revenue divided by total orders.
=SUM(Revenue)/COUNTA(OrderID)
Purchase Frequency: total orders divided by total unique customers.
=COUNTA(OrderID)/COUNTA(UNIQUE(CustomerID))
Customer Lifespan (in years): 1 divided by your annual churn rate. If 20% of customers don't come back each year, average lifespan is 5 years.
LTV: multiply all three together. Multiply by gross margin % to get gross profit LTV.
LTV by Customer Segment
LTV varies significantly across segments — enterprise customers typically have much higher LTV than SMB. Build a pivot table grouping customers by segment or acquisition channel, then calculate average order value, purchase frequency, and lifespan for each group separately.
A customer acquired through referral might have 3x the LTV of one acquired through paid ads. This information directly informs how much you should be willing to pay per channel.
LTV:CAC by Segment and Channel
Combine your LTV by segment with your CAC by acquisition channel. The ratio tells you where the best investment is. If referral customers have an LTV of $2,400 and a CAC of $200, that's a 12:1 ratio — invest more. If paid search customers have an LTV of $600 and a CAC of $400, the economics barely work.
The Easy Way: Using SheetXAI in Google Sheets
Example 1: You have order history data already in the spreadsheet.
"I have customer order data on Sheet 1 with customer ID, order date, and order value. Calculate LTV by customer segment using average order value, purchase frequency, and average customer lifespan, and compare LTV to CAC by acquisition channel from Sheet 2."
SheetXAI reads both sheets, calculates LTV by segment, pulls CAC data, and builds the LTV:CAC comparison.
Example 2: Your order data lives in Shopify or your CRM.
"Pull customer purchase history from Shopify and calculate LTV by acquisition channel and customer segment, then compare to our CAC from Google Ads and Meta."
SheetXAI connects to Shopify and your ad platforms and builds the full LTV analysis.
Try SheetXAI free and see what it builds for you.
Published May 2026. See also: How to Build a SaaS Metrics Dashboard in Google Sheets, How to Analyze Customer Churn in Google Sheets, and Google Sheets AI Guide.
