🚀 Data Analyst Roadmap — Part 20
🧠 SQL Level 10 — Cohort Analysis, Retention & Customer Analytics
Now we're moving from writing SQL queries to using SQL for real analytical problems.
Customer analytics is one of the most important areas because businesses want to know:
👥 Who are our customers?
🛒 When did they first purchase?
🔄 Do they come back?
📉 When do they stop returning?
💰 Which customers generate most revenue?
📊 How does behavior change over time?
One of the most powerful techniques for this is Cohort Analysis.
🔹 1. What Is Cohort Analysis?
A cohort is a group who share a common starting point.
• Jan 2026 Cohort = first purchase in Jan 2026
• Feb 2026 Cohort = first purchase in Feb 2026
Instead of mixing everyone, we track each group over time.
🔹 2. Why It Matters
Suppose total monthly customers are increasing.
That sounds positive.
But what if new customers are increasing while existing customers stop returning?
A simple monthly report may hide this problem.
Cohort analysis separates New vs Returning customers.
This makes retention problems much easier to identify.
🔹 3. Step 1 — Find Each Customer's First Purchase
SELECT
Customer_ID,
MIN(Order_Date) AS First_Order_Date
FROM Orders
GROUP BY Customer_ID;
This gives us the first purchase date for every customer.
Customer | First Order
C101 | 2026-01-10
C102 | 2026-01-18
C103 | 2026-02-05
🔹 4. Step 2 — Assign a Cohort Month
We can convert the first purchase into a month-level cohort.
First Purchase Date → Cohort Month
C101 → 2026-01
C103 → 2026-02
The exact month-truncation syntax varies between SQL databases.
🔹 5. Step 3 — Join Cohort Back to Orders
Now we need both:
Customer's cohort
and
Customer's subsequent activity
WITH Customer_Cohorts AS (
SELECT Customer_ID, MIN(Order_Date) AS First_Order_Date
FROM Orders GROUP BY Customer_ID
)
SELECT o.Customer_ID, c.First_Order_Date, o.Order_Date, o.Sales
FROM Orders o
JOIN Customer_Cohorts c ON o.Customer_ID = c.Customer_ID;
Now every transaction knows which cohort the customer belongs to.
🔹 6. Cohort Month vs Activity Month
• Cohort Month: When first purchased
• Activity Month: When purchase happened
Customer | Cohort | Activity
C101 | Jan | Jan
C101 | Jan | Feb
C101 | Jan | Mar
🔹 7. Measuring Retention
Retention measures how many customers from a cohort remain active in later periods.
Retention = Active in Period / Original Cohort * 100
• Jan cohort: 100 customers
• Feb: 60 active → 60%
• Mar: 40 active → 40%
🔹 8. Retention Month
Months Since Cohort = Activity - Cohort
Eg:
Customer | Cohort | Activity | Months_Since_Cohort
C101 | Jan | Jan | 0
C101 | Jan | Feb | 1
C101 | Jan | Mar | 2
🔹 9. Cohort Retention Matrix
Conceptually, the final result may look like:
Cohort | Month0 | Month1 | Month2 | Month3
Jan | 100% | 60% | 40% | 30%
Feb | 100% | 65% | 45% | —
Mar | 100% | 70% | — | —
This is often called a cohort retention matrix.
It immediately shows whether newer customer cohorts are retaining better or worse.
🔹 10. Customer Lifetime Value (CLV)
Another important customer metric is Customer Lifetime Value (CLV/LTV).
A simplified version can be based on:
Total Revenue Generated by Customer
A more advanced business model may consider:
• Revenue
• Gross margin
• Purchase frequency
• Retention
• Customer lifespan
• Acquisition cost
🔹 11. Average Order Value (AOV)
A basic customer metric is:
Average Order Value = Total Sales ÷ Number of Orders
In SQL:
