Building Cohort Tables From an Excel Export With SQL

Aggregate figures hide what happens to customers over time. Monthly active customers can be flat while every group of new customers fades quickly and a long-lived group props up the total. A cohort table shows this. Rows are the month a customer first bought, columns are months since, and cells are the share of that group still buying. Building one in a spreadsheet takes a pile of pivot tables and helper columns; in SQL it is a short query on an order export. This post builds it, with the care needed for the edges of the triangle. It is a spoke of doing proper analysis on Excel data with SQL.

The data

An export of orders as a table orders(order_id, customer_id, order_date):

Before building anything, check the identifier. Customer ids must be stable and unique per person: no a versus A, no trailing spaces, no customer with two ids (finding duplicate rows in an Excel sheet with code). A guest checkout with a null id needs a rule, or exclusion with a count. Cohort numbers are only as good as this column.

Step 1: each customer's cohort

The cohort is the month of the customer's first order:

WITH first_order AS (
    SELECT customer_id,
           date_trunc('month', MIN(order_date))::date AS cohort_month
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM first_order ORDER BY customer_id;

customer_id | cohort_month
------------+-------------
A           | 2025-01-01
B           | 2025-01-01
C           | 2025-02-01
D           | 2025-03-01

Use the first order, not the signup date, if you mean purchasing cohorts; use signup date if you mean acquisition cohorts and have it. Say which.

Step 2: age of each order

For each order, compute how many months after the cohort month it happened:

WITH first_order AS (
    SELECT customer_id, date_trunc('month', MIN(order_date))::date AS cohort_month
    FROM orders GROUP BY customer_id
),
activity AS (
    SELECT DISTINCT
           f.cohort_month,
           o.customer_id,
           date_diff('month', f.cohort_month, date_trunc('month', o.order_date)::date) AS age
    FROM orders o
    JOIN first_order f USING (customer_id)
)
SELECT * FROM activity ORDER BY cohort_month, customer_id, age;

DISTINCT collapses several orders in the same month to one row per customer per age, since retention asks whether the customer was active, not how often. In PostgreSQL, compute age as (EXTRACT(YEAR FROM o.order_date) - EXTRACT(YEAR FROM f.cohort_month)) * 12 + EXTRACT(MONTH FROM o.order_date) - EXTRACT(MONTH FROM f.cohort_month), since it has no date_diff with a month unit.

The result for the sample:

cohort_month | customer_id | age
-------------+-------------+----
2025-01-01   | A           | 0
2025-01-01   | A           | 1
2025-01-01   | A           | 2
2025-01-01   | B           | 0
2025-01-01   | B           | 2
2025-02-01   | C           | 0
2025-02-01   | C           | 1
2025-03-01   | D           | 0

Customer A bought in months 0, 1 and 2; B in months 0 and 2 (skipping 1); C in months 0 and 1 (March is one month after their February start); D only in month 0.

Step 3: counts and rates

Count active customers per cohort and age, and divide by the cohort size:

WITH first_order AS (...), activity AS (...),
size AS (
    SELECT cohort_month, COUNT(*) AS cohort_size FROM first_order GROUP BY cohort_month
)
SELECT a.cohort_month, a.age,
       COUNT(*) AS active,
       s.cohort_size,
       ROUND(100.0 * COUNT(*) / s.cohort_size, 1) AS retention_pct
FROM activity a
JOIN size s USING (cohort_month)
GROUP BY a.cohort_month, a.age, s.cohort_size
ORDER BY a.cohort_month, a.age;

cohort_month | age | active | cohort_size | retention_pct
-------------+-----+--------+-------------+--------------
2025-01-01   | 0   | 2      | 2           | 100.0
2025-01-01   | 1   | 1      | 2           | 50.0
2025-01-01   | 2   | 2      | 2           | 100.0
2025-02-01   | 0   | 1      | 1           | 100.0
2025-02-01   | 1   | 1      | 1           | 100.0
2025-03-01   | 0   | 1      | 1           | 100.0

Age 0 is always 100 percent, by definition: everyone bought in their first month. The January cohort shows 100 percent at age 2, because B returned after skipping a month. This measure is "active in that month", not "ever returned" and not "still a customer". If you want "returned at least once by this age", compute cumulatively; if you want "bought again within 90 days", define it with dates. State the definition under the table.

The size of the cohort in the sample is tiny, and with a handful of customers a percentage is noise. Show the counts beside the rates, and be careful reading small cohorts.

Step 4: pivot into a triangle

The long form is what you chart. People read the triangle:

PIVOT (
    SELECT cohort_month, age, ROUND(100.0 * active / cohort_size, 1) AS retention_pct
    FROM retention_long
)
ON age USING MAX(retention_pct)
GROUP BY cohort_month
ORDER BY cohort_month;

DuckDB's PIVOT statement does it; elsewhere use conditional aggregation (pivoting rows into columns in PostgreSQL). The result is a triangle: January has ages 0 to 2, February 0 to 1, March 0 only. The cells in the lower right are not zero; they are unknown, because those months have not happened. Showing them as 0 percent, or letting a heat map color them as the worst retention, is the most common mistake with cohort tables. Mask them. Compute the maximum possible age for each cohort from the last date in the data, and null out any cell beyond it:

WHERE a.age <= date_diff('month', a.cohort_month, (SELECT date_trunc('month', MAX(order_date))::date FROM orders))

Also treat the latest month as partial if the export ends mid-month: the last column of every row understates retention, because the month is not over. Drop the incomplete month or flag it.

Reading the table

Look across a row to see how one group decays. Look down a column to see whether newer groups behave better or worse than older ones at the same age; that comparison is the point of cohorts. A column that improves down the rows suggests a better product or onboarding. A drop in a calendar diagonal (the same month across cohorts) suggests an external event in that month, such as an outage or a promotion, rather than a cohort effect.

Revenue cohorts use the same structure with SUM(amount) per cell in place of the customer count. Divide by the cohort's first-month revenue for a net-revenue-retention view, with the caution that large customers dominate it.

Sources of error

Several things distort a cohort table without raising an error.

• Identifier problems. A customer who shows up under two ids is counted as new twice, inflating new-customer counts and lowering retention.

• Returns and cancellations. An order that was returned is still an order in the export. Filter to completed orders and say so.

• Timezones. An order at 23:30 UTC belongs to the next day locally; month boundaries shift for a few customers.

• Export boundaries. If the export starts at a given date, everyone who bought before it appears as new. Start the cohorts at the first month of the data and note that earlier customers are mixed in the first cohort, or exclude them with a prior-purchase flag.

• Changing definitions. If "active" meant something else last year, cohorts that span the change are not comparable.

Check the numbers

The cohort sizes must add up to the number of distinct customers: SELECT COUNT(DISTINCT customer_id) FROM orders equals the sum of cohort_size. The age 0 count of each cohort equals its size. The total of active over all cells for age 0 equals the number of customers. Pick one customer and trace them through the table by hand. Compare the January cohort at age 2 with a direct count of January-cohort customers who ordered in March.

When a cohort table is the wrong analysis

There are too few customers. With a few dozen per cohort the percentages swing wildly. Aggregate to quarters, or report counts.

Purchases are rare. A durable goods business where customers buy once in five years has nothing to retain monthly.

You need causes. A cohort table shows who stays, not why. Pair it with segment cuts (channel, plan, region) and, for causes, with an experiment.

The question is revenue concentration. A Pareto view by customer answers it more directly.

See DuckDB's pages on PIVOT and date functions. To convert a small result to JSON, use the CSV to JSON converter.