Challenge Scenario
In digital marketing and e-commerce analytics, Customer Lifetime Value (CLV) is the total revenue a single customer is expected to generate throughout their relationship with the business. Understanding CLV helps marketing leads allocate ad spend profitably without overpaying to acquire customers.
As a Growth Marketing Analyst, you are analyzing four customer segments:
- Average Order Value (Column D): Divide historical total spend by order count:
=B2/C2.
- Estimated CLV (Column G): Multiply AOV by annual purchase frequency and average customer lifespan in years:
=D2*E2*F2.
- Average AOV across Segments (Cell D7): Compute the average AOV using
AVERAGE(D2:D5).
- Total Combined Segment CLV (Cell G7): Sum total projected CLV using
SUM(G2:G5).
Your Sheet Layout
Understanding the CLV forecasting table:
| Cell Range |
Column Name |
Section |
Formula / Approach |
What It Does |
A2:C5 |
Segment, Spend, Orders |
Historical Orders |
Historical transactions and buyer volume |
Read-only transaction history. |
D2:D5 |
AOV ($) |
Basket Size |
=B2/C2 |
Calculates Average Order Value per purchase. |
E2:F5 |
Orders/Yr, Lifespan |
Retention Model |
Estimated frequency and retention years |
Read-only retention metrics. |
G2:G5 |
Estimated CLV ($) |
Lifetime Value |
=D2*E2*F2 |
Calculates total lifetime customer revenue. |
D7 |
Average AOV |
Summary |
=AVERAGE(D2:D5) |
Portfolio average order size ($2,125.00). |
G7 |
Total Segment CLV |
Summary |
=SUM(G2:G5) |
Combined segment lifetime value ($256,000.00). |
Complete the objectives below in the live spreadsheet editor. Each objective card will turn green once your formula evaluates successfully.
1
In cells D2:D5, calculate Average Order Value (= Total Spend / Order Count).
2
In cells G2:G5, calculate Estimated CLV (= AOV * Orders/Yr * Lifespan).
3
In cell D7, calculate the average AOV across all segments.
4
In cell G7, sum the total combined CLV across all segments.
Solution & Formula Breakdown
Let's examine how basket size and purchase frequency predict lifetime value:
1. Average Order Value Calculation (D2:D5)
=B2/C2
- Enterprise: $120,000 / 20 orders = $6,000.00 AOV.
- Mid-Market: $45,000 / 30 orders = $1,500.00 AOV.
- SMB: $18,000 / 36 orders = $500.00 AOV.
- Startup: $8,000 / 16 orders = $500.00 AOV.
2. Customer Lifetime Value Projection (G2:G5)
=D2*E2*F2
- Enterprise: $6,000 AOV × 8 orders/yr × 4 yrs = $192,000.00.
- Mid-Market: $1,500 AOV × 12 orders/yr × 3 yrs = $54,000.00.
- SMB: $500 AOV × 6 orders/yr × 2 yrs = $6,000.00.
- Startup: $500 AOV × 4 orders/yr × 2 yrs = $4,000.00.
3. Portfolio Metrics (D7 & G7)
- Cell D7 (Average AOV):
=AVERAGE(D2:D5) → $2,125.00.
- Cell G7 (Total CLV):
=SUM(G2:G5) → $256,000.00.
If your customer acquisition cost (CAC) for Enterprise is $10,000, but Enterprise CLV is $192,000, your LTV:CAC ratio is over 19x—indicating an exceptionally healthy and scalable sales channel!