This document provides a comprehensive analysis of all Key Performance Indicators (KPIs) calculated in the customer lifetime value SQL query. The query uses a multi-CTE approach to build progressively more sophisticated customer analytics metrics.
FirstPurchaseDate represents the date of a customer’s very first purchase transaction in the system. This establishes the beginning of the customer relationship timeline.
SQL Expression:
MIN(fis.OrderDate) AS FirstPurchaseDate
Mathematical Formula: \(\text{FirstPurchaseDate} = \min(\text{OrderDate}_i) \text{ for all orders } i \text{ of customer}\)
Business Purpose: Identifies when a customer entered the business ecosystem, enabling cohort analysis and customer age calculations.
LastPurchaseDate represents the date of a customer’s most recent purchase transaction. This is critical for recency analysis and identifying at-risk customers.
SQL Expression:
MAX(fis.OrderDate) AS LastPurchaseDate
Mathematical Formula: \(\text{LastPurchaseDate} = \max(\text{OrderDate}_i) \text{ for all orders } i \text{ of customer}\)
Business Purpose: Determines customer recency, helping identify active vs. dormant customers.
TotalOrders counts the number of distinct purchase orders a customer has placed over their entire relationship with the business. This measures purchase frequency at the order level.
SQL Expression:
COUNT(DISTINCT fis.SalesOrderNumber) AS TotalOrders
Mathematical Formula: \(\text{TotalOrders} = |\{\text{SalesOrderNumber}_i : i \in \text{all transactions}\}|\)
| where $ | \cdot | $ denotes the cardinality (count) of unique order numbers. |
Business Purpose: Measures customer engagement frequency and loyalty. Higher values indicate more frequent purchasers.
TotalLineItems counts the total number of individual product line items purchased across all orders. This differs from TotalOrders as a single order can contain multiple line items.
SQL Expression:
COUNT(*) AS TotalLineItems
Mathematical Formula: \(\text{TotalLineItems} = \sum_{i=1}^{n} 1 \text{ for all line items } i\)
Relationship: \(\text{TotalLineItems} \geq \text{TotalOrders}\)
Business Purpose: Measures basket size indirectly and total transaction volume. The ratio TotalLineItems/TotalOrders indicates average items per order.
CustomerTenureDays calculates the number of days between a customer’s first and last purchase, representing the span of their active relationship with the business.
SQL Expression:
(MAX(fis.OrderDate)::DATE - MIN(fis.OrderDate)::DATE) AS CustomerTenureDays
Mathematical Formula: \(\text{CustomerTenureDays} = \text{LastPurchaseDate} - \text{FirstPurchaseDate}\)
where dates are treated as integers (days since epoch).
Business Purpose: Measures the duration of the customer relationship, used for annualizing metrics and understanding customer lifecycle stages.
Note: For customers with only one order, this value is 0.
CustomerTenureYears converts CustomerTenureDays into years using the precise astronomical year length (365.25 days to account for leap years).
SQL Expression:
ROUND(cflp.CustomerTenureDays / 365.25, 2) AS CustomerTenureYears
Mathematical Formula: \(\text{CustomerTenureYears} = \frac{\text{CustomerTenureDays}}{365.25}\)
Business Purpose: Provides a human-readable tenure measure and serves as the denominator for annualized metrics (revenue per year, orders per year, etc.).
DaysSinceLastPurchase calculates the number of days from the customer’s last purchase to the current date. This is the primary recency metric for identifying customer activity status.
SQL Expression:
(CURRENT_DATE - cflp.LastPurchaseDate::DATE) AS DaysSinceLastPurchase
Mathematical Formula: \(\text{DaysSinceLastPurchase} = \text{CURRENT\_DATE} - \text{LastPurchaseDate}\)
Business Purpose: Critical for churn prediction and customer reactivation campaigns. Low values indicate active customers; high values suggest churn risk.
Interpretation Thresholds:
UniquePurchaseDays counts the number of distinct calendar days on which a customer made at least one purchase. This measures purchase day diversity.
SQL Expression:
COUNT(DISTINCT DATE(fis.OrderDate)) AS UniquePurchaseDays
Mathematical Formula: \(\text{UniquePurchaseDays} = |\{\text{DATE}(\text{OrderDate}_i) : i \in \text{all orders}\}|\)
Relationship: \(\text{UniquePurchaseDays} \leq \text{TotalOrders} \leq \text{TotalLineItems}\)
Business Purpose: Differentiates customers who place multiple orders on the same day vs. those who spread purchases over time. Used to calculate activity concentration.
TotalUnits represents the total quantity of product units purchased by the customer across all transactions, regardless of product type.
SQL Expression:
SUM(fis.OrderQuantity) AS TotalUnits
Mathematical Formula: \(\text{TotalUnits} = \sum_{i=1}^{n} \text{OrderQuantity}_i\)
where $n$ is the number of line items.
Business Purpose: Measures total volume consumption and can indicate wholesale vs. retail buying behavior.
LifetimeRevenue is the total sales amount generated by a customer across all their purchases. This is the most fundamental customer value metric and represents the top-line contribution.
SQL Expression:
ROUND(SUM(fis.SalesAmount), 2) AS LifetimeRevenue
Mathematical Formula: \(\text{LifetimeRevenue} = \sum_{i=1}^{n} \text{SalesAmount}_i\)
where $\text{SalesAmount}_i$ is the revenue from line item $i$.
Business Purpose: Primary metric for customer ranking and segmentation. Used in Pareto analysis (80/20 rule) and customer tiering.
Typical Distribution: Often follows Pareto principle where top 20% of customers generate 80% of revenue.
LifetimeGrossProfit calculates the total gross profit (revenue minus product cost) generated by a customer over their lifetime. This represents the actual margin contribution before operating expenses.
SQL Expression:
ROUND(SUM(fis.SalesAmount - fis.TotalProductCost), 2) AS LifetimeGrossProfit
Mathematical Formula: \(\text{LifetimeGrossProfit} = \sum_{i=1}^{n} (\text{SalesAmount}_i - \text{TotalProductCost}_i)\)
\[= \text{LifetimeRevenue} - \text{TotalProductCosts}\]Business Purpose: More accurate customer value metric than revenue alone, as it accounts for cost of goods sold. Critical for profitability analysis.
Note: Customer C is more valuable than Customer B despite lower revenue.
LifetimeMarginPct expresses the gross profit margin as a percentage of revenue, indicating the profitability rate of a customer’s purchases.
SQL Expression:
ROUND(100.0 * SUM(fis.SalesAmount - fis.TotalProductCost) / NULLIF(SUM(fis.SalesAmount), 0), 2) AS LifetimeMarginPct
Mathematical Formula: \(\text{LifetimeMarginPct} = \frac{\text{LifetimeGrossProfit}}{\text{LifetimeRevenue}} \times 100\)
\[= \frac{\sum (\text{SalesAmount}_i - \text{TotalProductCost}_i)}{\sum \text{SalesAmount}_i} \times 100\]Range: 0% to 100% (higher is better)
Business Purpose: Identifies customers who buy high-margin vs. low-margin products. Useful for targeted promotions and product recommendations.
Industry Context: Margins vary by industry. Luxury goods: 60-80%, Electronics: 5-15%, Grocery: 2-5%.
AvgLineItemValue calculates the average sales amount per individual line item (product) purchased. This indicates typical transaction size at the most granular level.
SQL Expression:
ROUND(AVG(fis.SalesAmount), 2) AS AvgLineItemValue
Mathematical Formula: \(\text{AvgLineItemValue} = \frac{\sum_{i=1}^{n} \text{SalesAmount}_i}{n}\)
where $n$ is the total number of line items.
\[= \frac{\text{LifetimeRevenue}}{\text{TotalLineItems}}\]Business Purpose: Indicates price point preference. Low values suggest discount/value buyers; high values suggest premium buyers.
AvgOrderValue (AOV) calculates the average total sales amount per order. This is a critical e-commerce and retail metric indicating typical basket size.
SQL Expression:
ROUND(SUM(fis.SalesAmount) / NULLIF(cflp.TotalOrders, 0), 2) AS AvgOrderValue
Mathematical Formula: \(\text{AvgOrderValue} = \frac{\text{LifetimeRevenue}}{\text{TotalOrders}}\)
\[= \frac{\sum_{i=1}^{n} \text{SalesAmount}_i}{\text{Number of distinct orders}}\]Relationship: \(\text{AvgOrderValue} = \text{AvgLineItemValue} \times \frac{\text{TotalLineItems}}{\text{TotalOrders}}\)
Business Purpose: Key metric for cross-selling and upselling effectiveness. Higher AOV reduces transaction costs and improves profitability.
Optimization Target: Increasing AOV by 10% is often easier than increasing customer count by 10%.
TotalDiscounts sums all discount amounts applied to a customer’s purchases over their lifetime, representing the total price reduction granted.
SQL Expression:
ROUND(SUM(fis.DiscountAmount), 2) AS TotalDiscounts
Mathematical Formula: \(\text{TotalDiscounts} = \sum_{i=1}^{n} \text{DiscountAmount}_i\)
Business Purpose: Measures the customer’s sensitivity to promotions and the cost of acquiring/retaining them. High discounts may indicate margin erosion.
AvgDiscountPct calculates the average discount rate as a percentage of the original price (before discount). This normalizes discount behavior across different purchase sizes.
SQL Expression:
ROUND(100.0 * SUM(fis.DiscountAmount) / NULLIF(SUM(fis.SalesAmount + fis.DiscountAmount), 0), 2) AS AvgDiscountPct
Mathematical Formula: \(\text{AvgDiscountPct} = \frac{\sum \text{DiscountAmount}_i}{\sum (\text{SalesAmount}_i + \text{DiscountAmount}_i)} \times 100\)
\[= \frac{\text{TotalDiscounts}}{\text{Original Price (before discounts)}} \times 100\]Range: 0% to 100%
Business Purpose: Identifies promotional dependency and customer price sensitivity. Used for targeted discount strategies.
Strategy Insight: Customers with >20% avg discount may have learned to wait for sales.
UniqueCategories counts the number of distinct product categories a customer has purchased from, measuring product diversity at the category level.
SQL Expression:
COUNT(DISTINCT pc.EnglishProductCategoryName) AS UniqueCategories
Mathematical Formula: \(\text{UniqueCategories} = |\{\text{ProductCategory}_i : i \in \text{all purchases}\}|\)
Business Purpose: Measures cross-category engagement. Higher values indicate customers exploring the full product range, suggesting stronger loyalty and share-of-wallet.
UniqueProducts counts the number of distinct products (SKUs) a customer has purchased, measuring product diversity at the most granular level.
SQL Expression:
COUNT(DISTINCT fis.ProductKey) AS UniqueProducts
Mathematical Formula: \(\text{UniqueProducts} = |\{\text{ProductKey}_i : i \in \text{all purchases}\}|\)
Relationship: \(\text{UniqueProducts} \leq \text{TotalLineItems}\)
(equality holds when customer never repurchases same product)
Business Purpose: Indicates variety-seeking vs. repeat-buying behavior. High values suggest exploratory customers; low values suggest loyal repurchasers.
RevenuePerYear annualizes the lifetime revenue by dividing by tenure in years, providing a normalized measure of customer value independent of relationship length.
SQL Expression:
ROUND(clm.LifetimeRevenue / NULLIF(clm.CustomerTenureYears, 0), 2) AS RevenuePerYear
Mathematical Formula: \(\text{RevenuePerYear} = \frac{\text{LifetimeRevenue}}{\text{CustomerTenureYears}}\)
Business Purpose: Enables fair comparison between new and longstanding customers. A 3-year customer with $30,000 revenue equals a 1-year customer with $10,000 revenue in annual terms.
Note: Customer B, despite lower total revenue, is more valuable on an annual basis.
ProfitPerYear annualizes the lifetime gross profit, showing the annual margin contribution of a customer.
SQL Expression:
ROUND(clm.LifetimeGrossProfit / NULLIF(clm.CustomerTenureYears, 0), 2) AS ProfitPerYear
Mathematical Formula: \(\text{ProfitPerYear} = \frac{\text{LifetimeGrossProfit}}{\text{CustomerTenureYears}}\)
Business Purpose: More accurate annualized value metric than RevenuePerYear as it accounts for cost structure. Critical for customer acquisition cost (CAC) payback calculations.
OrdersPerYear annualizes the order frequency, indicating how many times per year a customer typically purchases.
SQL Expression:
ROUND(clm.TotalOrders / NULLIF(clm.CustomerTenureYears, 0), 2) AS OrdersPerYear
Mathematical Formula: \(\text{OrdersPerYear} = \frac{\text{TotalOrders}}{\text{CustomerTenureYears}}\)
Business Purpose: Measures purchase frequency rate. Used for subscription conversion targeting and retention program design.
Context:
AvgDaysBetweenOrders calculates the typical number of days between consecutive purchases, representing the customer’s natural purchase cycle.
SQL Expression:
ROUND(clm.CustomerTenureDays / NULLIF(clm.TotalOrders - 1, 0), 2) AS AvgDaysBetweenOrders
Mathematical Formula: \(\text{AvgDaysBetweenOrders} = \frac{\text{CustomerTenureDays}}{\text{TotalOrders} - 1}\)
Rationale: For $n$ orders spanning a time period, there are $n-1$ intervals between orders.
Inverse Relationship: \(\text{AvgDaysBetweenOrders} \approx \frac{365.25}{\text{OrdersPerYear}}\)
Business Purpose: Identifies natural replenishment cycle. Used for timing repurchase reminders and detecting abnormal delays (churn risk).
Application: If customer typically orders every 30 days but it’s been 60 days, trigger reactivation campaign.
ActivityRatioPct measures the percentage of days during the customer tenure on which at least one purchase was made, indicating purchase concentration.
SQL Expression:
ROUND(100.0 * clm.UniquePurchaseDays / NULLIF(clm.CustomerTenureDays, 0), 2) AS ActivityRatioPct
Mathematical Formula: \(\text{ActivityRatioPct} = \frac{\text{UniquePurchaseDays}}{\text{CustomerTenureDays}} \times 100\)
Range: 0% to 100% (theoretical max 100% would mean purchasing every single day)
Business Purpose: Distinguishes concentrated bulk buyers from frequent regular buyers. Low values are normal for most industries; high values indicate exceptional engagement.
Context: Even loyal customers rarely exceed 10% activity ratio. >20% suggests business/reseller account.
RecencyScore assigns a 0-100 score based on how recently the customer made their last purchase, with higher scores indicating more recent activity. This is a bucketed recency metric.
SQL Expression:
CASE
WHEN clm.DaysSinceLastPurchase <= 30 THEN 100
WHEN clm.DaysSinceLastPurchase <= 90 THEN 80
WHEN clm.DaysSinceLastPurchase <= 180 THEN 60
WHEN clm.DaysSinceLastPurchase <= 365 THEN 40
WHEN clm.DaysSinceLastPurchase <= 730 THEN 20
ELSE 0
END AS RecencyScore
Mathematical Formula (Piecewise Function): \(\text{RecencyScore} = \begin{cases} 100 & \text{if } d \leq 30 \\ 80 & \text{if } 30 < d \leq 90 \\ 60 & \text{if } 90 < d \leq 180 \\ 40 & \text{if } 180 < d \leq 365 \\ 20 & \text{if } 365 < d \leq 730 \\ 0 & \text{if } d > 730 \end{cases}\)
where $d = \text{DaysSinceLastPurchase}$
Business Purpose: Recency is the strongest predictor of future purchase in RFM analysis. Recent customers are more likely to purchase again.
FrequencyQuintile ranks customers into five equal groups (quintiles) based on total order count, with 5 being the most frequent purchasers and 1 being the least frequent.
SQL Expression:
NTILE(5) OVER (ORDER BY clm.TotalOrders DESC) AS FrequencyQuintile
Mathematical Formula: \(\text{FrequencyQuintile} = \text{NTILE}_5(\text{TotalOrders}_{\text{desc}})\)
This divides all customers into 5 equally-sized groups based on TotalOrders rank.
Values: 1, 2, 3, 4, 5 where:
Business Purpose: Component of RFM segmentation. Identifies high-frequency customers for loyalty programs.
If there are 1,000 customers:
MonetaryQuintile ranks customers into five equal groups based on lifetime revenue, with 5 being the highest spenders and 1 being the lowest spenders.
SQL Expression:
NTILE(5) OVER (ORDER BY clm.LifetimeRevenue DESC) AS MonetaryQuintile
Mathematical Formula: \(\text{MonetaryQuintile} = \text{NTILE}_5(\text{LifetimeRevenue}_{\text{desc}})\)
Values: 1, 2, 3, 4, 5 where:
Business Purpose: Component of RFM segmentation. Identifies high-value customers. Often correlated with Pareto principle (80% of revenue from top 20% of customers).
If there are 1,000 customers with $10M total revenue:
CustomerValueScore is a composite 0-100 score that combines multiple dimensions of customer value: monetary contribution (40%), purchase frequency (32%), recency (20%), and profitability (8%). This provides a holistic customer ranking metric.
SQL Expression:
ROUND(
(cvm.MonetaryQuintile * 20) + -- 40% weight (20*5 max = 100 * 0.4)
(cvm.FrequencyQuintile * 16) + -- 32% weight (16*5 max = 80 * 0.4)
(cvm.RecencyScore * 0.20) + -- 20% weight (0.20*100 max = 20)
(CASE WHEN cvm.LifetimeMarginPct > 30 THEN 8
ELSE cvm.LifetimeMarginPct * 0.267 END) -- 8% weight
, 2) AS CustomerValueScore
Mathematical Formula: \(\text{CustomerValueScore} = 20M + 16F + 0.20R + P\)
where:
Weights:
Range: Theoretical range is 20-128, but practical range is approximately 20-100 after normalization.
Business Purpose: Single unified metric for customer ranking, segmentation, and resource allocation decisions.
Let me recalculate based on the actual weights:
Actually, looking at the weights again, I think the intended maximum is 100:
But the SQL shows MonetaryQuintile * 20, not * 8. Let me work with what’s actually in the SQL:
Corrected Examples (using actual SQL formula):
This suggests the score can exceed 100. The maximum theoretical score is 208.
Revised Examples:
Note: The maximum possible score is 208 (20×5 + 16×5 + 0.20×100 + 8).
RevenueRank assigns each customer a rank based on their lifetime revenue, with rank 1 being the highest revenue customer.
SQL Expression:
RANK() OVER (ORDER BY cvm.LifetimeRevenue DESC) AS RevenueRank
Mathematical Formula: \(\text{RevenueRank}(c) = |\{c' : \text{LifetimeRevenue}(c') > \text{LifetimeRevenue}(c)\}| + 1\)
Properties:
Business Purpose: Identifies top revenue contributors for VIP treatment, personal account management, and exclusive offers.
ProfitRank assigns each customer a rank based on their lifetime gross profit, with rank 1 being the highest profit contributor.
SQL Expression:
RANK() OVER (ORDER BY cvm.LifetimeGrossProfit DESC) AS ProfitRank
Mathematical Formula: \(\text{ProfitRank}(c) = |\{c' : \text{LifetimeGrossProfit}(c') > \text{LifetimeGrossProfit}(c)\}| + 1\)
Business Purpose: More accurate than revenue rank for identifying truly valuable customers, as it accounts for product mix and margin differences.
Comparison of Revenue vs. Profit Ranks:
Customer B is more valuable despite lower revenue due to high-margin purchases.
CustomerTier assigns customers to one of four categorical tiers (Platinum, Gold, Silver, Bronze) based on their CustomerValueScore, providing a simplified segmentation for business users.
SQL Expression:
CASE
WHEN cvs.CustomerValueScore >= 80 THEN 'Platinum'
WHEN cvs.CustomerValueScore >= 60 THEN 'Gold'
WHEN cvs.CustomerValueScore >= 40 THEN 'Silver'
ELSE 'Bronze'
END AS CustomerTier
Mathematical Formula (Piecewise Classification): \(\text{CustomerTier} = \begin{cases} \text{Platinum} & \text{if } \text{CustomerValueScore} \geq 80 \\ \text{Gold} & \text{if } 60 \leq \text{CustomerValueScore} < 80 \\ \text{Silver} & \text{if } 40 \leq \text{CustomerValueScore} < 60 \\ \text{Bronze} & \text{if } \text{CustomerValueScore} < 40 \end{cases}\)
Business Purpose: Simplified customer segmentation for loyalty programs, service levels, and marketing campaigns. Easier to operationalize than numeric scores.
Typical Distribution:
RevenuePercentile classifies customers into revenue-based percentile groups (Top 1%, Top 5%, Top 10%, Top 25%, Below Top 25%) for easier identification of high-value segments.
SQL Expression:
CASE
WHEN cvs.RevenueRank <= (SELECT COUNT(*) * 0.01 FROM CustomerValueScoring) THEN 'Top 1%'
WHEN cvs.RevenueRank <= (SELECT COUNT(*) * 0.05 FROM CustomerValueScoring) THEN 'Top 5%'
WHEN cvs.RevenueRank <= (SELECT COUNT(*) * 0.10 FROM CustomerValueScoring) THEN 'Top 10%'
WHEN cvs.RevenueRank <= (SELECT COUNT(*) * 0.25 FROM CustomerValueScoring) THEN 'Top 25%'
ELSE 'Below Top 25%'
END AS RevenuePercentile
Mathematical Formula: \(\text{RevenuePercentile} = \begin{cases} \text{Top 1\%} & \text{if } \text{RevenueRank} \leq 0.01N \\ \text{Top 5\%} & \text{if } 0.01N < \text{RevenueRank} \leq 0.05N \\ \text{Top 10\%} & \text{if } 0.05N < \text{RevenueRank} \leq 0.10N \\ \text{Top 25\%} & \text{if } 0.10N < \text{RevenueRank} \leq 0.25N \\ \text{Below Top 25\%} & \text{if } \text{RevenueRank} > 0.25N \end{cases}\)
where $N$ = total number of customers.
Business Purpose: Identifies elite revenue contributors for ultra-personalized treatment. Operationalizes Pareto analysis.
With 10,000 customers:
Strategic Allocation:
The query builds customer analytics through six progressive CTEs:
This comprehensive CLV analysis enables data-driven customer relationship management and strategic resource allocation.