Tracking Customer Cohorts by First Purchase Month

Of the customers who first bought from you in January, how many came back in February? How about March? This is cohort retention analysis, and it answers the question every subscription or repeat-purchase business eventually asks: are we keeping the customers we acquire?

This kind of analysis usually lives inside a BI tool or a gnarly SQL query with multiple CTEs. But you can build it visually in ExploreMyData with a pipeline of six steps and a helper view. Here is how, including the two places where the obvious approach quietly gives you the wrong number.

What you need

An orders table (CSV, Parquet, or Excel) with at least three columns: customer_id, order_date, and order_id. Each row is one order. A customer can appear many times if they placed multiple orders.

If you want to follow along, a sample orders file is available in the blog data folder. Drop it into ExploreMyData and you are ready to go.

Step 1: Find each customer's first purchase date

The textbook move here is a windowed MIN partitioned by customer. That is not on the menu: the Window Function dropdown offers ROW_NUMBER, RANK, DENSE_RANK, LEAD, LAG and RUNNING TOTAL, and stops there. The aggregate you want lives in Group & Aggregate, which collapses rows, and you need the rows.

So compute it somewhere else and bring it back. Views are the tool for this: one file can carry several views, each with its own independent pipeline, and any of them can be joined into any other.

1. Click the + at the end of the view tab strip. A second view over the same orders file appears, with an empty pipeline. Double-click the tab and rename it first_purchase so you can recognise it in the join dropdown later.
2. In that view, add Group & Aggregate from the Aggregate group. Group by customer_id, and add one aggregation: function MIN, column order_date.
3. You get two columns: customer_id and order_date_min. The name is not configurable, it is always the column name plus the function in lower case.

Back on the original view, which still has one row per order, add a Join step from the Data group. Pick the entry ending in "first_purchase" as the table, join type Inner, left key customer_id, right key customer_id.

Then use box 3, "Columns to include from right table", and tick only order_date_min. Leave it empty and the join selects everything from both sides, which means a second customer_id column arriving as customer_id_1. Harmless, but you will trip over it in every dropdown from here on.

Step 2: Extract the cohort month

Add Extract Date Part from the Date group.

1. Column: order_date_min
2. Date part: month
3. Apply results into: New Column, named cohort_month. Skip that and you get order_date_min_month, which is accurate and unreadable.

You get a number from 1 to 12 for the month each customer first showed up. January buyers are cohort 1, February buyers are cohort 2.

Step 3: Extract the order month

A second Extract Date Part step, same date part, this time on the original order_date column, named order_month. One step per part per column, always: there is no way to pull two parts out in one go.

Every row now has two month numbers, the cohort's and this order's.

Step 4: Months since first purchase

Select Math from the Transform group and type the formula straight into the box:

"order_month" - "cohort_month"

Apply results into a New Column called months_since_first. Zero means the order landed in the same month as the customer's first purchase, 1 means the month after, and so on.

This only holds inside a single calendar year. Spanning December into January, plain subtraction gives 1 minus 12, or minus 11. If your file crosses a year boundary, add two more Extract Date Part steps for the years and make the formula ("order_year" - "cohort_year") * 12 + ("order_month" - "cohort_month").

Step 5: One row per customer per period

Here is the step people skip, and it is the one that decides whether the numbers mean anything. Right now a customer who ordered three times in March contributes three rows to the March bucket. A retention table counts people, not orders, so those three have to become one.

The obvious answer is a COUNT DISTINCT in the pivot, and Pivot does not have one. Its function control is COUNT, SUM, AVG, MIN, MAX. So do the distinct part first: add Remove Duplicates from the Filter & Sort group and select two columns, customer_id and months_since_first.

That emits SELECT DISTINCT ON ("customer_id", "months_since_first") *, keeping one row per customer per period. C-1001's two March orders collapse to a single row. After this, a plain COUNT counts distinct customers, because there is no longer any other kind of row to count.

Step 6: Pivot into a cohort table

Now it comes together. Select Pivot from the Aggregate group:

1. Rows (group by): cohort_month
2. Columns (pivot on): months_since_first
3. Values: customer_id, function COUNT

Each row is a cohort. Each column is a period offset. Each cell is how many of that cohort's customers ordered in that period. Here is a run over a sample file of 1,020 customers whose orders span January to May 2025:

Reading the results

Column 0 is each cohort's initial size. Column 1 is how many of those same people came back a month later. Read across a row to watch one cohort decay, read down a column to compare cohorts at the same age.

January: 48 of 200, so 24.0% one-month retention. February: 52 of 185, or 28.1%. March: 61 of 241, or 25.3%. April: 57 of 218, or 26.1%. February is the standout, and if you know what marketing did differently that month, that is your finding.

Watch the zeros. The pivot emits 0 for a cohort and period combination with no rows behind it, and a period that has not arrived yet looks identical to a period where nobody came back. Only compare cells that had a chance to happen. The May cohort has one honest number in it.

For percentages instead of counts, add a Math step after the pivot. The pivot names its columns after the values it spread, so they are literally called 0, 1 and so on, and the formula is ROUND("1" / "0" * 100, 1) into a new column. One Math step per period column, which is tedious past three or four, but you only do it once.

The full pipeline

A second view holding one Group & Aggregate step, and six cards on the main view:

1. Join: pull order_date_min in from the first_purchase view
2. Extract Date Part: month from order_date_min, as cohort_month
3. Extract Date Part: month from order_date, as order_month
4. Math: months_since_first
5. Remove Duplicates: on customer_id and months_since_first
6. Pivot: COUNT of customer_id

Every card is editable in place, so when next month's export lands you reload the file and the same seven steps re-run against it. The one to revisit is step 5: if you ever change what a period means, the deduplication key has to change with it or the counts quietly become order counts again.

Build your cohort analysis →