🚀 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: