This document provides a comprehensive analysis of all Key Performance Indicators (KPIs) calculated in the customer lifecycle stage SQL query. The query tracks customer progression through lifecycle stages and identifies stage transition opportunities.
GrossProfit calculates the gross profit for each individual transaction by subtracting the total product cost from the sales amount. This measures the margin contribution of each sale.
SQL Expression:
fis.SalesAmount - fis.TotalProductCost AS GrossProfit
Mathematical Formula: \(\text{GrossProfit} = \text{SalesAmount} - \text{TotalProductCost}\)
Business Purpose: Provides transaction-level profitability for margin analysis and customer value assessment.
PurchaseSequence assigns a sequential order number to each transaction within a customer’s purchase history, starting from 1 for their first purchase. This enables analysis of customer behavior evolution over time.
SQL Expression:
ROW_NUMBER() OVER (PARTITION BY fis.CustomerKey ORDER BY fis.OrderDate) AS PurchaseSequence
Mathematical Formula: \(\text{PurchaseSequence}_i = i \text{ where } i \text{ is the rank of transaction by OrderDate within customer}\)
For customer $c$, if transactions are ordered by date as $t_1, t_2, …, t_n$, then: \(\text{PurchaseSequence}(t_i) = i\)
Business Purpose: Enables cohort analysis, first-order vs. repeat-order comparisons, and tracking of customer journey progression.
For Customer A’s purchases:
Analysis Applications:
CumulativeOrders counts the running total of distinct orders a customer has placed up to and including each transaction date. This shows the customer’s order count at each point in their history.
SQL Expression:
COUNT(DISTINCT fis.SalesOrderNumber) OVER (PARTITION BY fis.CustomerKey ORDER BY fis.OrderDate) AS CumulativeOrders
Mathematical Formula: \(\text{CumulativeOrders}(t_i) = |\{o : o \in \text{orders}, \text{OrderDate}(o) \leq \text{OrderDate}(t_i)\}|\)
| where $ | \cdot | $ denotes count of distinct orders up to transaction $t_i$. |
Business Purpose: Tracks customer engagement progression and identifies when customers cross engagement thresholds (e.g., 5 orders, 10 orders).
Customer B’s timeline:
Note: Multiple line items in same order have same CumulativeOrders value.
CumulativeRevenue calculates the running total of sales amount for a customer up to and including each transaction, showing their lifetime value progression over time.
SQL Expression:
SUM(fis.SalesAmount) OVER (PARTITION BY fis.CustomerKey ORDER BY fis.OrderDate) AS CumulativeRevenue
Mathematical Formula: \(\text{CumulativeRevenue}(t_i) = \sum_{j=1}^{i} \text{SalesAmount}(t_j)\)
where transactions are ordered by date: $t_1, t_2, …, t_i$.
Business Purpose: Tracks customer value accumulation, identifies high-value moments, and supports lifetime value projection models.
Customer C’s revenue progression:
Analysis:
PreviousOrderDate retrieves the date of the customer’s immediately preceding purchase, enabling calculation of inter-purchase intervals.
SQL Expression:
LAG(fis.OrderDate) OVER (PARTITION BY fis.CustomerKey ORDER BY fis.OrderDate) AS PreviousOrderDate
Mathematical Formula: \(\text{PreviousOrderDate}(t_i) = \begin{cases} \text{OrderDate}(t_{i-1}) & \text{if } i > 1 \\ \text{NULL} & \text{if } i = 1 \end{cases}\)
Business Purpose: Foundation for calculating time between purchases, detecting changes in purchase frequency, and identifying purchase cycle patterns.
Customer D’s purchase dates:
DaysSincePreviousOrder calculates the number of days between consecutive purchases for the same customer, measuring inter-purchase interval.
SQL Expression:
(fis.OrderDate::DATE - (LAG(fis.OrderDate) OVER (PARTITION BY fis.CustomerKey ORDER BY fis.OrderDate))::DATE) AS DaysSincePreviousOrder
Mathematical Formula: \(\text{DaysSincePreviousOrder}(t_i) = \begin{cases} \text{OrderDate}(t_i) - \text{OrderDate}(t_{i-1}) & \text{if } i > 1 \\ \text{NULL} & \text{if } i = 1 \end{cases}\)
Business Purpose: Identifies natural purchase cycles, detects anomalies (unusually long/short intervals), and predicts next purchase timing.
Customer E’s inter-purchase intervals:
Alert Trigger: If typical interval is 30 days but current is 90 days, trigger re-engagement campaign.
FirstPurchaseDate identifies the date of the customer’s very first purchase, establishing the customer relationship start date.
SQL Expression:
MIN(cpt.OrderDate) AS FirstPurchaseDate
Mathematical Formula: \(\text{FirstPurchaseDate} = \min_{i}(\text{OrderDate}_i)\)
Business Purpose: Defines customer cohort membership, enables age-based analysis, and serves as baseline for tenure calculations.
LastPurchaseDate identifies the date of the customer’s most recent purchase, critical for recency analysis.
SQL Expression:
MAX(cpt.OrderDate) AS LastPurchaseDate
Mathematical Formula: \(\text{LastPurchaseDate} = \max_{i}(\text{OrderDate}_i)\)
Business Purpose: Determines current activity status and is used to calculate days since last purchase for churn prediction.
CustomerAgeDays calculates the total number of days since the customer’s first purchase to the current date, representing the customer’s age/tenure with the business.
SQL Expression:
(CURRENT_DATE - MIN(cpt.OrderDate)::DATE) AS CustomerAgeDays
Mathematical Formula: \(\text{CustomerAgeDays} = \text{CURRENT\_DATE} - \text{FirstPurchaseDate}\)
Business Purpose: Differentiates new vs. longstanding customers, enables cohort-based comparisons, and supports lifecycle stage classification.
Stage Thresholds:
DaysSinceLastPurchase calculates the number of days from the customer’s last purchase to the current date, the primary recency metric.
SQL Expression:
(CURRENT_DATE - MAX(cpt.OrderDate)::DATE) AS DaysSinceLastPurchase
Mathematical Formula: \(\text{DaysSinceLastPurchase} = \text{CURRENT\_DATE} - \text{LastPurchaseDate}\)
Business Purpose: Primary indicator of churn risk. Used to trigger retention campaigns and classify lifecycle stages.
Critical Thresholds:
ActiveLifespanDays measures the time span between a customer’s first and last purchase, representing the duration of their active purchasing period (distinct from CustomerAgeDays which measures from first purchase to today).
SQL Expression:
(MAX(cpt.OrderDate)::DATE - MIN(cpt.OrderDate)::DATE) AS ActiveLifespanDays
Mathematical Formula: \(\text{ActiveLifespanDays} = \text{LastPurchaseDate} - \text{FirstPurchaseDate}\)
Relationship: \(\text{CustomerAgeDays} = \text{ActiveLifespanDays} + \text{DaysSinceLastPurchase}\)
Business Purpose: Measures actual engagement duration. A customer with 1000 CustomerAgeDays but 100 ActiveLifespanDays was only active for 100 days then churned.
Interpretation:
TotalOrders counts the total number of distinct orders a customer has placed over their lifetime.
SQL Expression:
COUNT(DISTINCT cpt.SalesOrderNumber) AS TotalOrders
Mathematical Formula: \(\text{TotalOrders} = |\{\text{SalesOrderNumber}_i : i \in \text{all transactions}\}|\)
Business Purpose: Primary frequency metric. Used to classify customers into engagement tiers and lifecycle stages.
Stage Classification Thresholds:
MaxOrders retrieves the maximum value of CumulativeOrders, which should equal TotalOrders. This is a verification metric to ensure window function calculations are correct.
SQL Expression:
MAX(cpt.CumulativeOrders) AS MaxOrders
Mathematical Formula: \(\text{MaxOrders} = \max_{i}(\text{CumulativeOrders}_i) = \text{TotalOrders}\)
Business Purpose: Quality assurance check. MaxOrders should always equal TotalOrders.
TotalRevenue sums all sales amounts across all customer transactions, representing lifetime monetary contribution.
SQL Expression:
ROUND(SUM(cpt.SalesAmount), 2) AS TotalRevenue
Mathematical Formula: \(\text{TotalRevenue} = \sum_{i=1}^{n} \text{SalesAmount}_i\)
Business Purpose: Primary customer value metric. Used for ranking, segmentation, and ROI analysis.
TotalGrossProfit sums the gross profit across all customer transactions, representing lifetime margin contribution.
SQL Expression:
ROUND(SUM(cpt.GrossProfit), 2) AS TotalGrossProfit
Mathematical Formula: \(\text{TotalGrossProfit} = \sum_{i=1}^{n} \text{GrossProfit}_i = \sum_{i=1}^{n} (\text{SalesAmount}_i - \text{TotalProductCost}_i)\)
Business Purpose: More accurate value metric than revenue, accounting for cost structure and margin differences.
Insight: Customer B is more valuable per dollar than Customer C despite lower revenue.
AvgTransactionValue calculates the mean sales amount per line item across all customer purchases.
SQL Expression:
ROUND(AVG(cpt.SalesAmount), 2) AS AvgTransactionValue
Mathematical Formula: \(\text{AvgTransactionValue} = \frac{1}{n}\sum_{i=1}^{n} \text{SalesAmount}_i\)
where $n$ is the total number of line items.
Business Purpose: Indicates typical basket size and price point preference.
AvgDaysBetweenOrders calculates the mean number of days between consecutive purchases, indicating the customer’s natural purchase cycle.
SQL Expression:
ROUND(AVG(cpt.DaysSincePreviousOrder), 2) AS AvgDaysBetweenOrders
Mathematical Formula: \(\text{AvgDaysBetweenOrders} = \frac{1}{n-1}\sum_{i=2}^{n} \text{DaysSincePreviousOrder}_i\)
where $n$ is the total number of orders.
Business Purpose: Identifies natural replenishment cycle. Used for timing re-engagement campaigns and predicting next purchase.
Application: If customer typically orders every 30 days but hasn’t ordered in 60 days, trigger campaign.
AvgPurchaseCycleDays calculates an alternative measure of inter-purchase interval by dividing the total active lifespan by the number of purchase intervals. This provides a different perspective than AvgDaysBetweenOrders.
SQL Expression:
ROUND(CAST((MAX(cpt.OrderDate)::DATE - MIN(cpt.OrderDate)::DATE) AS DOUBLE) / NULLIF(COUNT(DISTINCT cpt.SalesOrderNumber) - 1, 0), 2) AS AvgPurchaseCycleDays
Mathematical Formula: \(\text{AvgPurchaseCycleDays} = \frac{\text{ActiveLifespanDays}}{\text{TotalOrders} - 1} = \frac{\text{LastPurchaseDate} - \text{FirstPurchaseDate}}{\text{TotalOrders} - 1}\)
Relationship: \(\text{AvgPurchaseCycleDays} \approx \text{AvgDaysBetweenOrders}\)
These should be similar but may differ slightly due to calculation method.
Business Purpose: Alternative measure of purchase frequency that’s less sensitive to individual transaction variations.
Note: For single-order customers, this is NULL (division by zero protection).
CustomerAgeYears converts CustomerAgeDays into years for more intuitive understanding of customer tenure.
SQL Expression:
ROUND(ccs.CustomerAgeDays / 365.25, 2) AS CustomerAgeYears
Mathematical Formula: \(\text{CustomerAgeYears} = \frac{\text{CustomerAgeDays}}{365.25}\)
Business Purpose: Human-readable tenure metric for reporting and segmentation.
LifecycleStage classifies customers into one of seven strategic lifecycle stages based on their age, engagement level, and recency. This is the core classification metric that drives all lifecycle-based strategies.
SQL Expression:
CASE
WHEN ccs.DaysSinceLastPurchase > 365 THEN 'Churned'
WHEN ccs.DaysSinceLastPurchase > 180 AND ccs.TotalOrders >= 3 THEN 'At-Risk'
WHEN ccs.CustomerAgeDays <= 90 AND ccs.TotalOrders <= 2 THEN 'New'
WHEN (ccs.CustomerAgeDays BETWEEN 91 AND 180) OR (ccs.TotalOrders BETWEEN 2 AND 4) THEN 'Developing'
WHEN (ccs.CustomerAgeDays BETWEEN 181 AND 365) OR (ccs.TotalOrders BETWEEN 5 AND 10) THEN 'Growing'
WHEN ccs.CustomerAgeDays > 365 AND ccs.TotalOrders >= 11 AND ccs.DaysSinceLastPurchase <= 180 THEN 'Mature'
ELSE 'Inactive'
END AS LifecycleStage
Stage Definitions:
Criteria: $\text{DaysSinceLastPurchase} > 365$
Profile: No purchase in over 1 year, regardless of prior engagement.
Characteristics:
Example: Customer last purchased Jan 2023, analyzed Jan 2025 → Churned
Criteria: $\text{DaysSinceLastPurchase} > 180$ AND $\text{TotalOrders} \geq 3$
Profile: Was previously engaged (3+ orders) but hasn’t purchased in 6-12 months.
Characteristics:
Example: Customer with 15 orders, last purchase 250 days ago → At-Risk
Criteria: $\text{CustomerAgeDays} \leq 90$ AND $\text{TotalOrders} \leq 2$
Profile: Recent first-time or second-time buyers, still in onboarding phase.
Characteristics:
Example: Customer joined 60 days ago, made 1 purchase → New
Criteria: ($\text{CustomerAgeDays}$ BETWEEN 91 AND 180) OR ($\text{TotalOrders}$ BETWEEN 2 AND 4)
Profile: Transitioning from new to regular customer, building engagement.
Characteristics:
Example:
Criteria: ($\text{CustomerAgeDays}$ BETWEEN 181 AND 365) OR ($\text{TotalOrders}$ BETWEEN 5 AND 10)
Profile: Regular customers with established patterns, actively growing engagement.
Characteristics:
Example:
Criteria: $\text{CustomerAgeDays} > 365$ AND $\text{TotalOrders} \geq 11$ AND $\text{DaysSinceLastPurchase} \leq 180$
Profile: Long-term, highly engaged customers who are actively purchasing.
Characteristics:
Example: Customer joined 3 years ago, 45 orders, last purchase 60 days ago → Mature
Fallback: Doesn’t fit any other category
Profile: Low engagement customers who don’t meet criteria for other stages.
Characteristics:
Example: Customer 200 days old, 2 orders, last purchase 100 days ago → Inactive
Business Purpose: Provides clear, actionable segmentation for lifecycle marketing strategies. Each stage has distinct needs and optimal interventions.
Lifecycle Progression Examples:
Customer Journey 1 (Successful):
Customer Journey 2 (At Risk):
Customer Journey 3 (Quick Churn):
CustomerCount aggregates the number of customers in each lifecycle stage, showing stage distribution.
SQL Expression:
COUNT(*) AS CustomerCount
Mathematical Formula: \(\text{CustomerCount}_s = |\{c : \text{LifecycleStage}(c) = s\}|\)
for each stage $s$.
Business Purpose: Reveals customer base health and identifies which stages need attention.
Distribution across 10,000 customers:
Health Indicators:
StagePct calculates what percentage of total customers each lifecycle stage represents.
SQL Expression:
ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM LifecycleStageAssignment), 2) AS StagePct
Mathematical Formula: \(\text{StagePct}_s = \frac{\text{CustomerCount}_s}{\sum_{s'} \text{CustomerCount}_{s'}} \times 100\)
Range: 0% to 100%, sum = 100%
Business Purpose: Enables proportional comparison and benchmark tracking over time.
AvgCustomerAgeYears calculates the mean customer age in years within each lifecycle stage, validating stage definitions.
SQL Expression:
ROUND(AVG(lsa.CustomerAgeYears), 2) AS AvgCustomerAgeYears
Mathematical Formula: \(\text{AvgCustomerAgeYears}_s = \frac{1}{n_s}\sum_{c \in s} \text{CustomerAgeYears}(c)\)
Business Purpose: Quality check for stage classification. Verifies age-based stage logic is working correctly.
Expected averages by stage:
AvgDaysSinceLastPurchase calculates the mean days since last purchase for customers in each stage.
SQL Expression:
ROUND(AVG(lsa.DaysSinceLastPurchase), 0) AS AvgDaysSinceLastPurchase
Mathematical Formula: \(\text{AvgDaysSinceLastPurchase}_s = \frac{1}{n_s}\sum_{c \in s} \text{DaysSinceLastPurchase}(c)\)
Business Purpose: Validates recency-based stage logic and sets campaign timing.
Expected averages by stage:
AvgTotalOrders calculates the mean lifetime order count for customers in each stage.
SQL Expression:
ROUND(AVG(lsa.TotalOrders), 2) AS AvgTotalOrders
Mathematical Formula: \(\text{AvgTotalOrders}_s = \frac{1}{n_s}\sum_{c \in s} \text{TotalOrders}(c)\)
Business Purpose: Validates engagement-based stage logic and characterizes stage behavior.
Expected averages by stage:
AvgLifetimeRevenue calculates the mean total revenue per customer within each lifecycle stage.
SQL Expression:
ROUND(AVG(lsa.TotalRevenue), 2) AS AvgLifetimeRevenue
Mathematical Formula: \(\text{AvgLifetimeRevenue}_s = \frac{1}{n_s}\sum_{c \in s} \text{TotalRevenue}(c)\)
Business Purpose: Quantifies average value by stage, informing investment levels and prioritization.
Expected averages by stage:
Strategic Insight: At-Risk customers with $8,000 average justify high retention investment.
AvgLifetimeProfit calculates the mean gross profit per customer within each lifecycle stage.
SQL Expression:
ROUND(AVG(lsa.TotalGrossProfit), 2) AS AvgLifetimeProfit
Mathematical Formula: \(\text{AvgLifetimeProfit}_s = \frac{1}{n_s}\sum_{c \in s} \text{TotalGrossProfit}(c)\)
Business Purpose: More accurate value metric than revenue for determining intervention budgets.
Expected averages by stage:
AvgTransactionValue calculates the mean transaction size for customers in each lifecycle stage.
SQL Expression:
ROUND(AVG(lsa.AvgTransactionValue), 2) AS AvgTransactionValue
Mathematical Formula: \(\text{AvgTransactionValue}_s = \frac{1}{n_s}\sum_{c \in s} \text{AvgTransactionValue}(c)\)
This is the average of averages across customers in the stage.
Business Purpose: Identifies whether basket size changes across lifecycle stages.
Insight: If Mature customers have higher average transaction value, upselling is working.
AvgDaysBetweenOrders calculates the mean inter-purchase interval for customers in each lifecycle stage.
SQL Expression:
ROUND(AVG(lsa.AvgDaysBetweenOrders), 0) AS AvgDaysBetweenOrders
Mathematical Formula: \(\text{AvgDaysBetweenOrders}_s = \frac{1}{n_s}\sum_{c \in s} \text{AvgDaysBetweenOrders}(c)\)
Business Purpose: Identifies typical purchase frequency by stage for campaign timing.
Expected averages by stage:
TotalStageRevenue sums the total revenue generated by all customers within a lifecycle stage.
SQL Expression:
ROUND(SUM(lsa.TotalRevenue), 2) AS TotalStageRevenue
Mathematical Formula: \(\text{TotalStageRevenue}_s = \sum_{c \in s} \text{TotalRevenue}(c)\)
Business Purpose: Identifies which stages drive total business value, often revealing concentration in Mature stage.
With $50M total revenue:
Insight: Mature stage drives disproportionate value despite smaller customer count.
RevenueSharePct calculates what percentage of total company revenue each lifecycle stage generates.
SQL Expression:
ROUND(100.0 * SUM(lsa.TotalRevenue) / (SELECT SUM(TotalRevenue) FROM LifecycleStageAssignment), 2) AS RevenueSharePct
Mathematical Formula: \(\text{RevenueSharePct}_s = \frac{\text{TotalStageRevenue}_s}{\sum_{s'} \text{TotalStageRevenue}_{s'}} \times 100\)
Range: 0% to 100%, sum = 100%
Business Purpose: Reveals value concentration and guides resource allocation decisions.
Typical distribution:
Risk Assessment: 10% revenue from At-Risk represents major vulnerability.
AvgYearlyIncome calculates the mean annual income of customers within each lifecycle stage.
SQL Expression:
ROUND(AVG(lsa.YearlyIncome), 2) AS AvgYearlyIncome
Mathematical Formula: \(\text{AvgYearlyIncome}_s = \frac{1}{n_s}\sum_{c \in s} \text{YearlyIncome}(c)\)
Business Purpose: Links lifecycle behavior to economic capacity and demographics.
NextTargetStage identifies the next lifecycle stage a customer should progress to, creating clear advancement goals.
SQL Expression:
CASE lsa.LifecycleStage
WHEN 'New' THEN 'Developing'
WHEN 'Developing' THEN 'Growing'
WHEN 'Growing' THEN 'Mature'
WHEN 'Mature' THEN 'Retain as Mature'
WHEN 'At-Risk' THEN 'Reactivate to Growing'
WHEN 'Churned' THEN 'Win Back to New'
WHEN 'Inactive' THEN 'Activate to Developing'
END AS NextTargetStage
Stage Progression Paths:
Business Purpose: Provides clear, stage-specific goals for customer development strategies.
StageProgressionGap quantifies what’s needed for a customer to advance to the next lifecycle stage, providing specific, actionable metrics.
SQL Expression:
CASE lsa.LifecycleStage
WHEN 'New' THEN CONCAT(GREATEST(0, 3 - lsa.TotalOrders), ' more orders OR ', GREATEST(0, 91 - lsa.CustomerAgeDays), ' more days')
WHEN 'Developing' THEN CONCAT(GREATEST(0, 5 - lsa.TotalOrders), ' more orders needed for Growing stage')
WHEN 'Growing' THEN CONCAT(GREATEST(0, 11 - lsa.TotalOrders), ' more orders needed for Mature stage')
WHEN 'At-Risk' THEN CONCAT('Purchase within ', GREATEST(0, 180 - lsa.DaysSinceLastPurchase), ' days to avoid churn')
WHEN 'Churned' THEN 'Win-back campaign required'
ELSE 'N/A'
END AS StageProgressionGap
Gap Calculations by Stage:
Gap: $\max(0, 3 - \text{TotalOrders})$ orders OR $\max(0, 91 - \text{CustomerAgeDays})$ days
Logic: Need 3 orders or 91 days to graduate to Developing
Examples:
Gap: $\max(0, 5 - \text{TotalOrders})$ orders
Logic: Need 5 orders to reach Growing stage
Examples:
Gap: $\max(0, 11 - \text{TotalOrders})$ orders
Logic: Need 11 orders (and be over 365 days old with recency < 180 days)
Examples:
Gap: Purchase within $(180 - \text{DaysSinceLastPurchase})$ days
Logic: Must purchase before reaching 365 days of inactivity (churned threshold)
Examples:
Note: Negative values indicate already past safe threshold.
Business Purpose: Translates strategic goals into concrete, measurable actions. Enables personalized messaging (e.g., “Just 2 more orders to unlock Growing benefits!”).
RecommendedAction provides specific, actionable marketing tactics for each customer based on their lifecycle stage.
SQL Expression:
CASE lsa.LifecycleStage
WHEN 'New' THEN 'Onboarding campaign: Product education, repeat purchase incentive'
WHEN 'Developing' THEN 'Engagement campaign: Cross-sell, loyalty program enrollment'
WHEN 'Growing' THEN 'Expansion campaign: Premium tiers, volume discounts'
WHEN 'Mature' THEN 'Retention campaign: VIP benefits, exclusive access'
WHEN 'At-Risk' THEN 'URGENT: Re-engagement campaign, win-back offer'
WHEN 'Churned' THEN 'Win-back campaign: Reactivation incentive'
WHEN 'Inactive' THEN 'Activation campaign: Limited-time promotion'
END AS RecommendedAction
Actions by Stage:
Action: “Onboarding campaign: Product education, repeat purchase incentive”
Tactics:
Goal: Convert to second purchase within 30 days
Action: “Engagement campaign: Cross-sell, loyalty program enrollment”
Tactics:
Goal: Increase order frequency and expand product usage
Action: “Expansion campaign: Premium tiers, volume discounts”
Tactics:
Goal: Accelerate path to Mature stage
Action: “Retention campaign: VIP benefits, exclusive access”
Tactics:
Goal: Maintain engagement and prevent movement to At-Risk
Action: “URGENT: Re-engagement campaign, win-back offer”
Tactics:
Goal: Reactivate before becoming Churned
Action: “Win-back campaign: Reactivation incentive”
Tactics:
Goal: Bring back to New stage
Action: “Activation campaign: Limited-time promotion”
Tactics:
Goal: Stimulate engagement and move to Developing
Business Purpose: Translates lifecycle stage into operational marketing playbooks. Enables immediate action for campaign teams.
StageRevenueRank ranks customers within their lifecycle stage based on total revenue, with rank 1 being the highest value customer in that stage.
SQL Expression:
RANK() OVER (PARTITION BY lsa.LifecycleStage ORDER BY lsa.TotalRevenue DESC) AS StageRevenueRank
Mathematical Formula: \(\text{StageRevenueRank}_s(c) = |\{c' \in s : \text{TotalRevenue}(c') > \text{TotalRevenue}(c)\}| + 1\)
for customer $c$ in stage $s$.
Business Purpose: Prioritizes within-stage actions. Identifies highest-value customers in At-Risk stage for immediate intervention.
In At-Risk stage with 500 customers:
Application:
InterventionPriority assigns a priority level (High, Medium, Low, Stable) to each customer based on their lifecycle stage, guiding resource allocation and urgency.
SQL Expression:
CASE
WHEN sto.LifecycleStage = 'At-Risk' THEN 'High'
WHEN sto.LifecycleStage IN ('New', 'Developing') THEN 'Medium'
WHEN sto.LifecycleStage = 'Churned' THEN 'Low'
ELSE 'Stable'
END AS InterventionPriority
Priority Levels:
Stage: At-Risk
Rationale:
Resource Allocation: Maximum attention, personal touches, aggressive offers
Stages: New, Developing
Rationale:
Resource Allocation: Systematic campaigns, moderate investment, automation with personalization
Stage: Churned
Rationale:
Resource Allocation: Low-cost reactivation attempts, may exclude from paid campaigns
Stages: Growing, Mature, Inactive
Rationale:
Resource Allocation: Standard nurture campaigns, monitoring, no urgent action
Business Purpose: Directs limited resources to customers where intervention has highest impact and urgency.
The query builds lifecycle analysis through five progressive CTEs:
Positive Flow: New → Developing → Growing → Mature → (Retained)
Risk Flow: Any stage → At-Risk → Churned
Recovery Flow: Churned → Win Back to New; At-Risk → Reactivate to Growing
This lifecycle framework provides a complete, actionable customer development system that translates customer age and engagement into strategic interventions and clear progression goals.