Cohort Analysis Builder

Ask how many customers came back last month and you get one number. It is almost always the wrong number to steer by. A business that signs up more people every month can look healthier each month while each new group behaves worse than the one before, or the reverse. The single figure mixes customers who arrived three years ago with customers who arrived three days ago, and it cannot tell you which of them changed.

A cohort table fixes that by refusing to mix ages. Every customer goes into a group by the period of their first event, and each group is followed through its own first, second and third period. Read across a row and you watch one group age. Read down a column and you compare groups at the same age. That is the whole idea, and most of the ways it goes wrong are mistakes of counting rather than of statistics.

What a cohort is, and why one average hides change

The word comes from research. A cohort study follows a group of people who share a defining experience in a set period, such as being born in 1948, and looks at them again at intervals. Business analytics borrowed the frame. Cohort analysis splits a data set into related groups before analysing it, so that patterns show up across the life cycle of a customer instead of being sliced across all customers blindly.

Two terms frame the question. Customer retention is the ability to keep customers over a stated period. Churn is the other side: the share that leaves a defined group during a period.

The definitions sound as if the all-customer figure would do, but the churn article makes the key point plainly. For a fast-growing customer base, churn worked out over the whole base understates the true rate compared with a cohort calculation, because a steady arrival of new and still-active customers dilutes the losses among older ones. Growth flatters the average. A shrinking intake does the opposite and can make a steady product look as if it were failing.

The cohort view also forces a choice of starting event. A cohort can begin at a first purchase, a first login, a signup date or anything else that marks a start. The tool uses the first event in your file unless you map a separate signup column. Whichever you choose belongs next to the table, because the same file gives different tables under different starts.

The period, and the clock it runs on

The period is the second choice. Daily cohorts suit products used every day, weekly ones suit habits, and monthly ones suit subscriptions and repeat orders. Weeks need a rule. ISO 8601 weeks start on Monday and end on Sunday, and a week belongs to the year in which its Thursday falls, so week 1 is the week that holds 4 January. A year therefore has 52 or 53 weeks, and Monday 30 December 2024 is the first day of week 1 of 2025.

Time zones decide which day an event belongs to. ISO 8601 writes a moment with Z for a zero offset or with an offset such as +05:30, and a time with no zone information is local time. An order placed at 23:30 in New York is already the next day in UTC. Monthly cohorts barely notice; daily ones do.

The cure is to choose a zone on purpose. The IANA time zone database records the history of local time for representative places, including when daylight saving time applied, and it is updated when governments change their rules. Its code and data are in the public domain. Browsers expose it through Intl.DateTimeFormat, whose timeZone option takes names such as America/New_York, so an event near a clock change lands on the date a person in that place would give it.

Classic, unbounded, and the cells that must stay blank

Retention needs a definition before it needs a number. Classic retention counts a customer in period k when they have at least one event in period k. Unbounded retention counts them when they have an event in period k or in any later period. Classic tells you about rhythm, while unbounded tells you who has not gone for good. Unbounded figures are higher, and for recent cohorts they keep rising as later activity arrives, which deserves a line under any table that shows them.

Then comes the mistake that ruins most homemade tables. Suppose the export ends on 24 October. The cohort that arrived in September has had only twenty-four days of its first full month. Count that month as finished and you compare a partial month with complete ones. Go further, fill every period it has not reached with zero, and you invent losses that have not happened.

Statisticians have a name for this. A value is censored when it is only partly known, and right censoring means you know an event time lies beyond some limit but not by how much, as with a subject still alive when a study ends. A study that stops at a fixed time leaves every remaining subject right-censored. An analysis end date is exactly that kind of stop. Survival analysis keeps such observations apart as right-censored instead of counting them as failures.

The honest response in a table is short. A cell is reported only when its whole period ended on or before the analysis end date. Otherwise it stays blank, marked as blank, never zero.

Weighting, value, and a worked example

Averages need the same care. A column average can treat each cohort as one vote or weigh it by its size. The weighted version adds the retained customers of every cohort that reached the period and divides by the sum of their sizes, so a cohort of 300 counts three times as much as one of 100. The unweighted mean of percentages answers a different question: what a typical cohort looked like, however many people it held. Both are legitimate. Mixing them silently is not.

Only cohorts that reached a period may take part in its average. If a young cohort’s blank month 3 slipped in as zero, it would drag the average down by an amount set purely by how recently that cohort arrived.

Value works the same way. If the export holds an order amount, summing it per cohort and period gives value retention, the value in period k divided by the value in period 0, and cumulative value per customer, the running total divided by the cohort’s size. Value retention can pass 100 percent when customers spend more as they settle in. In the example export, pooling every cohort that finished month 1, the value of month 1 comes to 58.0 percent of the value of month 0. Both are sums of what the file holds, in its own unit, and neither is a forecast.

Here is the example export the tool opens with, counted by calendar month in UTC, classic retention, end date 24 October 2026. The November 2025 cohort holds 179 customers, and 30 of them had an event in their month 3, February 2026: 16.8 percent. Eight cohorts, November 2025 to June 2026, have finished month 3. Together they hold 1,921 customers, of whom 466 were active in their month 3, so the weighted average is 466 divided by 1,921, or 24.3%. The plain mean of the eight cohort percentages is 24.0 percent.

Now make the classic mistake. Treat the four younger cohorts as if their month 3 had happened with nobody returning, and divide the same 466 by all 3,041 customers. The answer is 15.3%, a collapse that exists only in the arithmetic. For month 6 the honest weighted figure is 16.6 percent over five cohorts, while zero-filling gives 6.4 percent. Choose Month, Classic and Weighted in the tool to see every one of these figures.

What the table cannot tell you

The file is the limit. A cohort table counts what was recorded: events that never reached the export do not exist for it, and a customer who appears under two ids is two customers. It cannot see why anyone left, and it does not forecast the blank cells. Estimating those needs a model of how retention decays, such as the methods of survival analysis, and a model is an assumption with a name rather than a measurement.

The export deserves suspicion too. RFC 4180 describes how most programs write CSV: records end in a line break, and a field holding a comma, a quote or a line break is wrapped in double quotes, with any quote inside written twice. The memo also admits that no formal specification exists, so exports vary. A careful reader lists every row it rejects and says why. Large files can be read in slices, since a Blob can hand back a new Blob holding any range of its bytes, with the counting done in a background thread that leaves the page responsive.

One more trap waits on the way out. A spreadsheet treats a cell that begins with an equals sign as a formula, so any table written back to CSV should guard its text cells.

Done this way, a cohort table is a plain record. Blank cells mark what has not happened yet, and every average says whom it averaged.