Cohort Retention and Churn Analysis

Cohort analysis is one of the most powerful techniques in product and growth analytics. It groups users by their acquisition period (the month they first purchased) and then tracks their behaviour over subsequent months. This reveals whether users acquired in more recent months are more or less loyal than users from earlier periods — a crucial signal for whether product improvements are working.

Understanding retention is the foundation of sustainable growth: acquiring new users means nothing if they don't come back.

What Is a Cohort?

A cohort is a group of ec_customers who share a common characteristic at a common time — typically their first purchase month. All ec_customers who first bought in January form the "January cohort," February buyers form the "February cohort," etc.

The cohort grid shows: of the N ec_customers in each cohort, what percentage returned to buy again 1 month, 2 months, 3 months later? This creates a triangular matrix of retention percentages.

Why Cohort Analysis Beats Aggregate Retention

Aggregate retention (e.g., "40% of all users return each month") hides critical information. If newer cohorts retain at 20% and older cohorts at 60%, the aggregate 40% number masks a serious problem. Cohort analysis reveals this immediately.

A healthy business shows either:

1. Stable retention across cohorts (consistent product quality)
2. Improving retention in newer cohorts (product improvements working)

Declining retention in newer cohorts is an early warning that something has broken.

Step 1 — Build the Cohort Assignment

Every customer is assigned to a cohort based on their first purchase month. This is permanent — a customer's cohort never changes.

WITH first_order AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM ec_orders
    WHERE status NOT IN ('cancelled', 'refunded')
    GROUP BY customer_id
)
SELECT cust.email, fo.cohort_month
FROM first_order fo
JOIN ec_customers cust ON cust.customer_id = fo.customer_id
ORDER BY fo.cohort_month, cust.email;

What This Returns

Every customer with their cohort month. A customer who first bought on March 15th has cohort_month = '2024-03-01'. This is their permanent "birth month" in the system.