Clustering and Cohort Analysis

Introduction to Module 3

Module 3 shifts from descriptive analytics (pivot tables, dashboards, logical segmentation) to strategic analytics — understanding why something happened, who it happened to, and what is likely to happen next. This is where data meets marketing strategy.

Four core techniques are introduced:

Module Flow

Each technique builds on the previous: cohort analysis provides retention baselines → CLTV puts a dollar value on that retention → RFM adds a behavioural ranking → K-Means automates segmentation without guesswork.

Exam tip: The progression from rule-based segmentation (RFM) to statistical clustering (K-Means) is a classic conceptual bridge. Understand that RFM requires manual thresholds, while K-Means uses distance metrics to form groups objectively.

Key takeaways

• Module 3 moves from what happened to why, who, and what next.
• Four techniques: cohort analysis, CLTV, RFM, K-Means clustering.
• Cohort analysis tracks retention over time; CLTV values a customer lifetime.
• RFM ranks by recency, frequency, monetary value; K-Means finds groups mathematically.
• All methods are applied to real customer data — theory is immediately operational.

What is Cohort Analysis?

A cohort is a group of users who share a common characteristic, most often the month they signed up or made their first purchase. Cohort analysis tracks how each cohort behaves over time, answering questions like:

• How many users remain after their first month?
• Are customers from January more loyal than those from March?
• Is retention improving over time?

It is widely used in:

The core idea: for each cohort, count how many users return in each subsequent period and build a table showing retention across time.

---

The Data: Understanding the Invoice Dataset

A real transactional dataset (≈400,000 rows) with columns:

Important structure: An invoice can contain multiple line items (same Invoice number, same Customer ID, multiple rows). Raw data is at the invoice line-item level, not summarized per order.

Data cleaning essentials (always verify):

• ✅ All quantities are positive – delete rows with negative quantity (returns/cancellations).
• ✅ All prices are positive – remove zero or negative rows.
• ✅ Remove test product codes or customer IDs (e.g., "test").
• ✅ Ensure InvoiceDate is in correct date format (Excel may auto-group).

Exam tip: In any real dataset, cleaning is non-negotiable. Cohort results are only as reliable as the underlying data.

---

Building the Base Table: From Line Items to Customer Orders

Step 1 – Aggregating Line Items into Invoices

Create a pivot table from the clean data:

• Rows: Customer ID, then Invoice (and optionally InvoiceDate, Country)
• Values: Sum of Price (or Count of line items to get order size)

This collapses multiple line items into one row per invoice, giving a customer-level purchase history.

Step 2 – Extracting Signup Date (First Transaction per Customer)

Sort the pivot table by Customer ID and then InvoiceDate (oldest first). The first row for each customer is their signup date (cohort anchor). Use VLOOKUP (exact match) to copy this date into a new column for every row of that customer. Because we sorted ascending, VLOOKUP returns the first – i.e., earliest – date.

Step 3 – Encoding Month Numbers

Since the data spans two years (Jan 2009 – Dec 2010), create a continuous month number for each date:

$$\text{MonthNumber} = \begin{cases} \text{MONTH(date)} & \text{if YEAR(date) = 2009} \\ \text{MONTH(date)} + 12 & \text{if YEAR(date) = 2010} \end{cases}$$

Apply this to:

• Signup month → becomes the cohort number (e.g., Jan 2009 = 1, Dec 2009 = 12, Jan 2010 = 13).
• Invoice month → becomes order month.

Each customer is permanently assigned to one cohort based on their signup month.

Step 4 – Calculating Retention Period

For each transaction, compute:

$\text{RetentionPeriod} = \text{OrderMonth} - \text{CohortNumber}$

• 0 = the same month as signup (zeroth month)
• 1 = one month after signup
• 6 = six months after signup, etc.

This number tells us when a customer made a purchase relative to their own joining month – the key metric for retention tracking.

Worked Example

Consider a customer with:

• Signup date: 14 Dec 2009 → CohortNumber = 12
• Another transaction on 18 June 2010 → OrderMonth = 6 (June) + 12 = 18

$\text{RetentionPeriod} = 18 - 12 = 6$

This customer made a purchase in the 6th month after signing up. One row in the final dataset will have CohortNumber = 12 and RetentionPeriod = 6.

---

Data Preparation Workflow

---

Exam tip: The retention period is not the number of consecutive months a customer was active – it only marks the months in which a transaction occurred. A customer may have gaps; each gap period simply lacks a row. The final cohort table (pivot) counts unique customers active in each period, not cumulative.

Key takeaways

• Cohort = group sharing a common event (here, first purchase month).
• Retention = activity in a specific month after signup, measured as order month minus cohort month.
• Data must be at invoice level (not line-item) before extracting signup dates.
• VLOOKUP with sorted data correctly fetches the earliest transaction date.
• Month numbering across years uses an IF-based offset (year → month + 12).
• The prepared dataset contains for each row: Customer ID, CohortNumber, RetentionPeriod – ready for a pivot table that counts customers per cohort per period.

Cohort Analysis: Building and Interpreting Retention Tables

A cohort retention table tracks what fraction of customers from a given signup period (cohort) remain active in each subsequent period. The goal: see where customers drop off and compare retention across cohorts.

Building the Retention Cohort Table

1. Raw pivot – Place:

   • Rows = SignupCohortNumber (the month they first ordered)
   • Columns = OrderMonth
   • Values = Distinct count of Customer ID

   The raw result shows, for each cohort, how many distinct customers placed an order in each calendar month. This is not retention — it mixes months before and after signup and doesn’t align the time axis.

2. The retention fix – Replace OrderMonth with RetentionMonth = month offset from the cohort’s start. Example: if a customer signed up in January (cohort 1) and placed an order in March, retention month = 2. This is typically computed by subtracting the cohort’s start month from the order month.

3. Result – Columns become 0, 1, 2, … months after signup.

   • Column 0 = month of signup (all customers who ever ordered are counted).
   • Column 1 = customers who placed at least one order in the next month, etc.

   Cohort	Month 0	Month 1	Month 2	…
   1	95	53	44	…
   2	88	50	38	…

   Important: Use distinct count of customers, not order count. A customer who orders multiple times in the same month still counts as one retained.

Interpreting the Raw Table

The raw numbers are absolute counts — a cohort with more signups naturally has larger absolute retention. To compare cohorts directly, standardize:

• Percentage of the original cohort that returned in each subsequent month.

  $\text{Retention rate}_{c,m} = \frac{\text{\# distinct customers in cohort } c \text{ in month } m}{\text{\# distinct customers in cohort } c \text{ in month } 0} \times 100\%$

• Apply conditional formatting (e.g., red–white–green heat map) to quickly spot patterns.

Example: Cohort 1

• Month 0: 95 (100%)
• Month 1: 53 → 55.8%
• Month 2: 44 → 46.3%
• Month 3: 52 → 54.7% (possible variation)

After 12 months, sharp drop-off.

Analyzing the Cohort Table

Analysis works along three axes:

Horizontal  →  For one cohort: how retention evolves over time
Vertical    →  For one month offset: how retention changes across cohorts
Diagonal    →  For equal customer age: compare cohorts at same distance from signup

Horizontal (within a cohort)

• Look at a single row from left to right.
• What it reveals: The natural decay of customer activity. A steep drop suggests a specific churn trigger.
• Every cohort shows a severe drop after month 11 or 12 → likely a 12‑month subscription ending.

Vertical (across cohorts, same offset)

• Compare retention rates for the same month offset across different cohorts.
• What it reveals: Whether later cohorts are “stickier” or less engaged from the start.
• Recent cohorts have lower month‑1 retention than older cohorts → the company’s initial engagement activities may have degraded (fewer calls, worse onboarding).

Diagonal (equal customer age)

• Follow cells where cohort + offset = constant (e.g., all customers exactly 12 months old).
• What it reveals: External events affecting all cohorts equally (e.g., a platform change, marketing shift).
• Observation: After the 12th month, diagonals turn red → something systemic happened (new software, changed marketing manager).

Business Insights and Next Steps

• 12‑month churn → Investigate: Are retention emails not going out? Does the subscription auto‑expire?
• Declining early retention → Review onboarding, first‑month engagement, or product changes.
• Systemic drop → Cross‑check with company events (software update, leadership change).

The same cohort framework can be reused for:

• Average order value per cohort per month.
• Geographic cohorts (e.g., by region).
• Acquisition channel cohorts.

This feeds directly into customer lifetime value (LTV) calculation in the next step.

Exam tip: The most common mistake is using raw order counts instead of distinct customers. Also, never compare absolute numbers across cohorts – always use percentages.

Key takeaways

• A retention cohort table rows = signup cohorts, columns = months since signup.
• Use distinct count of customers and offset months.
• Standardize to percentage for cross‑cohort comparability.
• Analyze horizontally (cohort decay), vertically (cohort quality trend), and diagonally (external shocks).
• Drops after a fixed month (e.g., 12) suggest a systemic cause; declining early retention points to weakening initial engagement.

Customer Lifetime Value (LTV) Calculations

Customer Lifetime Value (LTV) predicts the total revenue or profit a business expects from a single customer over the entire relationship. Intuitively: if you acquire a customer today, how much money will they bring before they stop buying? LTV is the bridge between user behavior and business value.

Why LTV Matters

• Marketing – determines how much to spend to acquire a customer (compare with Customer Acquisition Cost (CAC)).
• Product – identifies high-value users to design better experiences.
• Finance – estimates long-term revenue potential.

  Fundamental rule: If $LTV > CAC$, the business model works; otherwise, you lose money on every customer.

LTV Formulas

Two common versions:

Where:

• AOV = Average Order Value (e.g., ₹100)
• Frequency = average purchases per time period (e.g., 2 per month)
• Lifespan = average duration a customer remains active (e.g., 5 months)
• Gross Margin = profit percentage on each order (e.g., 30%)

Worked Example

A customer has: lifespan = 5 months, frequency = 2 orders/month, AOV = ₹100, gross margin = 30%.

Revenue LTV: $\text{LTV}_{\text{rev}} = 100 \times 2 \times 5 = \text{Rs. }1000$

Profit LTV: $\text{LTV}_{\text{profit}} = 1000 \times 0.30 = \text{Rs. }300$

If CAC is ₹150, the business retains ₹150 per customer to cover overheads.

Estimating Lifespan from Retention / Churn

Often companies provide a retention rate (e.g., monthly 80%). The churn rate is the complement: $\text{Churn} = 1 - \text{Retention}$.

For constant churn, average lifespan is: $\text{Average Lifespan} = \frac{1}{\text{Churn Rate}}$

Example: 80% monthly retention → 20% churn → average lifespan = $1/0.20 = 5$ months.

Exam tip: The $1/\text{churn}$ formula assumes constant churn over time. For more accuracy—especially when retention changes across cohorts—use a weighted average from cohort data.

Cohort‑Based LTV Calculation

Building LTV per cohort from retention data gives more granular insight. Three components must be derived from the pivot tables:

1. Average Order Value (AOV) – average of invoice values per cohort.
2. Purchase Frequency – average number of orders per month per customer.
3. Lifespan – weighted average of how many months customers remain active.

Step‑by‑Step (using Excel/Pivot Tables)

Step 1: AOV per cohort Create a pivot with Cohort Number on rows and Average of Price (invoice value) as values. This gives the mean order value for each cohort.

Step 2: Frequency per cohort

• Create a pivot with Cohort Number on rows, Order Month on columns.
• Add Distinct Count of Invoice (number of orders) and Distinct Count of Customer ID (active customers per month).
• For each month, compute: $\text{Frequency}_{\text{cohort, month}} = \frac{\text{Orders}}{\text{Customers}}$
• Then average those monthly frequencies across all available months to get a single average frequency for the cohort.

Step 3: Lifespan per cohort

• Create a pivot of Distinct Count of Customer ID per cohort per month (like a retention table).

• Compute weighted average lifespan using the surviving customer counts:

  $\text{Lifespan}_{\text{cohort}} = 1 + \frac{\sum_{t=0}^{T} (\text{Customers at month } t) \times t}{\sum_{t=0}^{T} \text{Customers at month } t}$

  (Add 1 because month 0 represents the first month of activity.)

Example: If 12 customers total: 6 survive to month 0, 4 survive to month 1, 2 survive to month 2:

$\text{Lifespan} = 1 + \frac{(6 \times 0) + (4 \times 1) + (2 \times 2)}{6+4+2} = 1 + \frac{0+4+4}{12} = 1 + 0.667 = 1.667 \text{ months}$

Step 4: Calculate LTV For each cohort:

$\text{LTV}_{\text{cohort}} = \text{AOV}_{\text{cohort}} \times \text{Frequency}_{\text{cohort}} \times \text{Lifespan}_{\text{cohort}}$

Multiply by gross margin for profit‑based LTV.

Example of Cohort LTV (using the example data)

As lifespan and frequency decline across cohorts, LTV shrinks—highlighting the need to analyze which cohorts are truly valuable.

Insights from Cohort LTV Analysis

• A high initial order volume does not guarantee retention; frequency can drop sharply (e.g., post‑month 12).
• Comparing LTV vs. CAC per cohort shows whether marketing spend is justified.
• Tracking LTV trends over cohorts reveals whether customer quality is improving or degrading.

Key takeaways

• LTV = AOV × Frequency × Lifespan (revenue) or × Gross Margin (profit).
• Lifespan can be estimated as $1/\text{churn rate}$ (constant churn) or via weighted average from cohort retention data.
• Cohort‑based LTV uses AOV, frequency, and lifespan calculated from pivot tables on transactional data.
• LTV > CAC is the fundamental viability condition.
• Cohort LTV analysis reveals differences in customer value over time and across acquisition periods.

RFM Segmentation

RFM (Recency, Frequency, Monetary) is a data-driven method to segment customers based on three behavioral indicators. Intuitively: a customer who bought yesterday, buys often, and spends a lot is highly valuable; one who hasn't bought in months needs re-engagement. RFM quantifies these dimensions and assigns each customer a score or category.

What Each Dimension Captures

Why RFM Matters

RFM enables targeted marketing by matching the message to customer behaviour:

• High recency + high frequency + high monetary → VIP treatment.
• High frequency but low monetary → potential for upselling.
• Low recency → re-engagement campaign needed.

Unlike arbitrary business‑intuition buckets (e.g., “over ₹1,00,000 is good”), RFM uses data‑driven quartiles or percentiles, making segmentation objective and repeatable.

Computing RFM in Excel (Practical Walkthrough)

The process starts with transaction-level data: each row contains a customer ID, invoice date, quantity, and price. The key data preparation step is computing invoice value = Quantity × Price (not price alone).

1. Monetary

   • From a pivot table with Customer ID as rows, add Sum of Invoice Value to values. This gives total spend per customer.
   • Optionally add Average of Invoice Value for average order value.

2. Recency

   • Add Invoice Date to values and set its aggregation to Max (gives the most recent purchase date). Format as short date.

3. Frequency

   • Frequency = number of orders per time unit (e.g., orders per month).
   • To compute: count the number of invoices per customer (using Count of Invoice Date) and divide by the number of months in the dataset.
   • Alternative: create a numeric month index (e.g., month 1 for Jan 2009, month 13 for Jan 2010) and use it to compute orders per month directly.

Exam tip: Always use Quantity × Price for monetary value, not price alone. A single item price does not reflect total spend; the product gives the true value of an invoice.

Data‑Driven Segmentation vs. Arbitrary Buckets

The previous module used manual revenue brackets (e.g., > ₹1,00,000 = “very good”). These were business intuition‑based buckets — subjective and not replicable. RFM replaces them with quartile or percentile thresholds that are statistically derived, making segmentation consistent and comparable.

Key Takeaways

• RFM stands for Recency, Frequency, Monetary — three behavioural dimensions of customer value.
• High recency → active; high frequency → loyal; high monetary → high spender.
• RFM enables targeted messaging: VIP, upsell, re-engage.
• Computation: Sum of Value (Monetary), Max Date (Recency), Count of Invoices ÷ Periods (Frequency).
• Always compute invoice value as quantity × price.
• RFM segmentation is data‑driven (quartiles) and superior to arbitrary revenue brackets.

RFM Segmentation-II

RFM (Recency, Frequency, Monetary) segments customers based on how recently they purchased, how often they buy, and how much they spend. The goal: turn raw transaction data into integer buckets (e.g., 1–4) so that higher is better for all three dimensions, then combine them into interpretable customer groups.

Step 1: Compute raw R, F, M metrics

Exam tip: Recency uses months since last order – lower is better. Frequency and monetary: higher is better. To keep a consistent “higher is better” scale, you must invert recency before bucketing.

---

Step 2: Normalize to percentile buckets

Manual thresholds (e.g., “orders > ₹1,00,000”) break when data shifts month to month. Instead, use percentile rank to create stable, data-driven buckets.

1. Compute percentile for each raw metric using PERCENTRANK.EXC(array, value, significance). – Returns a number between 0 and 1 representing where the value lies in the distribution.
2. Convert to bucket (e.g., 4 quartiles): $\text{bucket} = \text{CEILING}(\text{percentile} \times \text{num\_buckets}, 1)$
   • Multiply by 4 for quartiles, by 10 for deciles, etc.
   • CEILING rounds up to the nearest integer → ensures every value gets a bucket from 1 to num_buckets.

Applying to each RFM dimension:

• Monetary (Total & Average): higher percentile → higher bucket → better.
• Frequency: higher yearly frequency → higher bucket → better.
• Recency: because raw months-since-last-order is lower when better, invert the measure before the percentile step: $\text{inverted\_recency} = \text{max\_months} - \text{months\_since\_last\_order}$ where max_months = 24 (the full time span of the data). Then compute percentiles and buckets on inverted_recency. Now a bucket of 4 means the customer ordered recently.

---

Step 3: Combine buckets into segments

With each customer having three bucket values (R_bucket, F_bucket, M_bucket), you can define interpretable segments:

You can create custom logic (e.g., “if all four are 4 → Champions”, “if R≥3, F≥3, M≥3 → Loyal”) depending on business goals.

---

Why percentile-based bucketing matters

• No manual thresholds – adapts automatically to changes in purchasing patterns.
• Stable across time – top 10% this month remains top 10% next month, even if absolute values drop.
• Interpretable – quartiles (1–4) are easy to explain and combine.

---

Key takeaways

• RFM = Recency (months since last order), Frequency (yearly count), Monetary (total & average order value).
• Convert raw metrics to percentile buckets (e.g., 4 quartiles) to remove manual bias and ensure stability.
• Invert recency before bucketing so that higher buckets always mean better behaviour.
• Use PERCENTRANK.EXC + CEILING to get integer buckets.
• Combine buckets into segments (e.g., “Super Loyal”, “At Risk”) for targeted marketing and churn analysis.
• Percentile-based bucketing works even when absolute values vary month to month – the top 10% stay the top 10%.

Summary of Module 3

This module delivers four interlocking tools to understand customer behavior and drive retention. Together they form an analytics toolkit for smarter marketing and product decisions.

1. Cohort Analysis

Cohort analysis tracks groups of customers who share a common first-action date (e.g., first purchase) and measures their retention over time. It reveals how user engagement decays after the initial experience, and which cohorts behave differently.

2. Customer Lifetime Value (CLV)

Customer lifetime value (CLV) quantifies the total revenue (or profit) a customer is expected to generate over their entire relationship with the business. It turns retention data into a financial metric, enabling investment decisions about acquisition and retention spend.

3. RFM Segmentation

RFM segmentation classifies customers using three behavioural dimensions:

• Recency – time since last purchase
• Frequency – number of purchases
• Monetary value – total spend

These dimensions create clear, human‑interpretable segments (e.g., “best customers”, “at‑risk”); each segment can be targeted with tailored campaigns and engagement strategies.

4. Clustering

Clustering applies statistical algorithms (e.g., k‑means) to let the data form its own natural groupings. Unlike RFM (where rules are defined manually), clustering is machine‑driven and can uncover unexpected patterns that complement human‑designed segments.

RFM vs. Clustering

Exam tip: The core distinction is human‑defined rules (RFM) vs. machine‑driven discovery (clustering). Know that they are complementary, not competing – clustering can reveal segments RFM would miss, and RFM segments are easier to act on directly.

Key takeaways

• Cohort analysis, CLV, RFM, and clustering together answer: Who are our customers, how much are they worth, and how do they change over time?
• Cohort analysis tracks retention of groups over time; CLV gives a dollar value per customer.
• RFM is rule‑based and intuitive; clustering is algorithmic and exploratory.
• Use both RFM and clustering to combine actionability with discovery.