Who Is Worth More? Customer Lifetime Value Ranking

UrbanMart's marketing team wants to rank customers by lifetime value to target high-value segments. But LTV is a composite of average order value, purchase frequency, and customer lifespan — each customer excels at different components. The analyst must manually decompose and compute LTV for 10 customers before the next campaign budget is set.

# Aplly.xyz Case Study Submission

## Title
Who Is Worth More? Customer Lifetime Value Ranking

## Type
Data Analytics

## Difficulty
Intermediate

## Estimated Time
75 minutes

## Overview
UrbanMart's marketing team wants to rank customers by lifetime value to target high-value segments. But LTV is a composite of average order value, purchase frequency, and customer lifespan — each customer excels at different components. The analyst must manually decompose and compute LTV for 10 customers before the next campaign budget is set.

## Case Details

Function Focus: Multi-component metric decomposition (AOV × frequency × lifespan), customer ranking under partial data, churn-adjusted LTV, segment identification

Scenario:
UrbanMart's CMO wants to allocate the next ₹10 lakh campaign budget to the "highest-value customer segment." The data team has purchase history for 10 representative customers. The marketing director says LTV is simple: total revenue to date. The analyst argues that total revenue biases toward old customers — a newer customer with high average order value and frequent purchases may have higher future LTV. She proposes decomposing LTV into three components: Average Order Value (AOV), Purchase Frequency (orders per year), and Customer Lifespan (years since first purchase). She must manually compute and rank all 10 customers — and flag which ones have churned (no purchases in the last 6 months), because their LTV should be calculated differently.

LTV Calculation Rules (Draft):

For active customers (purchased within last 6 months):
- AOV = Total Revenue ÷ Total Orders
- Annual Frequency = Total Orders ÷ (Current Date − First Purchase Date in years)
- Annual LTV Rate = AOV × Annual Frequency
- This represents their expected yearly value going forward

For churned customers (no purchase in last 6 months):
- Total LTV = Total Revenue to date (no future value)
- Annual LTV Rate = Total Revenue ÷ (First Purchase to Last Purchase in years)

Tasks:
1. For each of the 10 customers, manually compute: (a) months since first purchase, (b) AOV, (c) annual frequency, (d) annual LTV rate — show full arithmetic for at least 2 customers (one active, one churned)
2. Classify each customer as Active or Churned using the 6-month rule (current date: Oct 2026) — then apply the correct LTV formula for each
3. Rank the 10 customers by annual LTV rate — identify the top 3 and bottom 3 and describe the profile of each (e.g., "high AOV but low frequency" or "low AOV but buys every month")
4. Compare your LTV ranking against a simple total-revenue ranking — identify which customer moves up most and which moves down most, and explain why the LTV view changes their relative value
5. Only after submitting your manual analysis, re-run all calculations in a spreadsheet or AI tool and report any discrepancies — then recommend whether the marketing team should target the top 3 by LTV or by total revenue

Expected Output:
A customer value analysis memo containing: (a) full LTV decomposition table for all 10 customers with sample arithmetic, (b) active/churned classification and which formula was used for each, (c) LTV ranking with top-3 and bottom-3 profiles, (d) shift analysis comparing LTV ranking vs revenue ranking, (e) campaign targeting recommendation, and (f) a round-trip discrepancy note.

Evaluation Criteria:
Correct AOV, frequency, and LTV arithmetic for all 10 customers, correct application of active vs churned formulas, meaningful customer profiling (not just numbers but qualitative description of each segment), insightful shift analysis, and a well-reasoned targeting recommendation.

## Data Sources

Customer Purchase History (as of Oct 2026):
| Customer ID | First Purchase | Last Purchase | Total Orders | Total Revenue (₹) |
|---|---|---|---|---|
| C001 | Jan 2024 | Sep 2026 | 22 | 2,20,000 |
| C002 | Mar 2025 | Oct 2026 | 8 | 1,20,000 |
| C003 | Jun 2021 | Sep 2026 | 55 | 4,40,000 |
| C004 | Dec 2025 | Oct 2026 | 5 | 1,25,000 |
| C005 | Aug 2022 | Sep 2026 | 30 | 3,60,000 |
| C006 | Feb 2023 | Sep 2026 | 14 | 2,10,000 |
| C007 | Apr 2024 | Feb 2026 | 4 | 60,000 |
| C008 | Oct 2020 | Sep 2026 | 70 | 5,60,000 |
| C009 | Mar 2026 | Aug 2026 | 3 | 18,000 |
| C010 | Dec 2021 | Oct 2026 | 45 | 7,20,000 |

Churn Rule:
- Current date: October 2026
- Churned = last purchase more than 6 months before current date (i.e., before April 2026)
- Active = last purchase within the last 6 months (April 2026 or later)

Context:
- Campaign budget: ₹10 lakh
- Target segment size: top 3 customers by value
- Marketing team currently targets customers by total revenue (C010 #1 → C008 #2 → C003 #3)
- CMO wants to know: "Are we targeting the right people?"

Date Reference Table (for manual computation):
| Month | Year |
|---|---|
| Jan 2021 | 69 months ago |
| Jun 2021 | 64 months ago |
| Oct 2020 | 72 months ago |
| Dec 2021 | 58 months ago |
| Feb 2023 | 44 months ago |
| ... (solver to compute relative to Oct 2026) |

## Solution Frameworks
Lifetime value decomposition, cohort analysis, churn-adjusted valuation, customer ranking, segment profiling

## Solver Guidance & Tutorials
Link to: "Customer Lifetime Value: Manual Decomposition and Ranking" tutorial

## What You'll Learn
- Decomposing LTV into AOV, frequency, and lifespan components
- Adjusting LTV calculations for churned customers
- Comparing LTV-based ranking vs simple revenue ranking
- Identifying high-potential customers who are early in their lifecycle

## Tags
customer LTV, lifetime value, customer ranking, segmentation, marketing analytics

## Registration Links
- Register as Solver
- Register as Evaluator

Customer Purchase History (as of Oct 2026):
| Customer ID | First Purchase | Last Purchase | Total Orders | Total Revenue (₹) |
|---|---|---|---|---|
| C001 | Jan 2024 | Sep 2026 | 22 | 2,20,000 |
| C002 | Mar 2025 | Oct 2026 | 8 | 1,20,000 |
| C003 | Jun 2021 | Sep 2026 | 55 | 4,40,000 |
| C004 | Dec 2025 | Oct 2026 | 5 | 1,25,000 |
| C005 | Aug 2022 | Sep 2026 | 30 | 3,60,000 |
| C006 | Feb 2023 | Sep 2026 | 14 | 2,10,000 |
| C007 | Apr 2024 | Feb 2026 | 4 | 60,000 |
| C008 | Oct 2020 | Sep 2026 | 70 | 5,60,000 |
| C009 | Mar 2026 | Aug 2026 | 3 | 18,000 |
| C010 | Dec 2021 | Oct 2026 | 45 | 7,20,000 |

Churn Rule:
- Current date: October 2026
- Churned = last purchase more than 6 months before current date (i.e., before April 2026)
- Active = last purchase within the last 6 months (April 2026 or later)

Context:
- Campaign budget: ₹10 lakh
- Target segment size: top 3 customers by value
- Marketing team currently targets customers by total revenue (C010 #1 → C008 #2 → C003 #3)
- CMO wants to know: "Are we targeting the right people?"

Date Reference Table (for manual computation):
| Month | Year |
|---|---|
| Jan 2021 | 69 months ago |
| Jun 2021 | 64 months ago |
| Oct 2020 | 72 months ago |
| Dec 2021 | 58 months ago |
| Feb 2023 | 44 months ago |
| ... (solver to compute relative to Oct 2026) |

Lifetime value decomposition, cohort analysis, churn-adjusted valuation, customer ranking, segment profiling