{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"cohorts-and-retention","title":"Cohorts and retention: how a month of new customers behaves afterwards","url":"https://addaly.com/learn/data-and-sql/cohorts-and-retention","position":41,"of":66,"minutes":10,"answer":"A cohort is a group defined by a first event, retention is the share of it active at each age.","keyIdea":"A cohort is a group defined by a first event, retention is the share of it active at each age, and the grid is a triangle you read down a column only as far as every cohort has reached."},"body":{"format":"markdown","text":"## The question a total hides\n\nRevenue went up. Was it because you are keeping customers, or because you are acquiring new ones fast enough to hide that the old ones leave? A total cannot say. A cohort analysis can: take everyone who started in the same month, and watch what share of them is still active one, two, three months later.\n\n## Step one: assign each customer to a cohort\n\nA cohort is defined by a first event, and the first event is a `min()`:\n\n```sql\nWITH first_order AS (\n  SELECT customer_id, date_trunc('month', min(placed_at)) AS cohort_month\n  FROM orders\n  GROUP BY customer_id\n)\n```\n\nOne row per customer, with the month they first bought. This is a decision dressed as a query: \"first order\" could equally be \"first sign-up\" or \"first paid order\", and a customer who bought once in 2023 and returned in 2026 belongs to the 2023 cohort forever. Say which event defines the cohort in the report title.\n\n## Step two: each customer's active months, as an age\n\n```sql\nactivity AS (\n  SELECT o.customer_id,\n         f.cohort_month,\n         date_trunc('month', o.placed_at) AS active_month\n  FROM orders o\n  JOIN first_order f USING (customer_id)\n  GROUP BY 1, 2, 3\n),\naged AS (\n  SELECT cohort_month,\n         customer_id,\n         (extract(year  FROM active_month) - extract(year  FROM cohort_month)) * 12\n       + (extract(month FROM active_month) - extract(month FROM cohort_month)) AS months_since\n  FROM activity\n)\n```\n\nThe `GROUP BY` in `activity` collapses several orders in one month to one row, because retention counts customers, not orders. `months_since` is the customer's age in months at the time of that activity: 0 for the cohort month itself, 1 for the following month, and so on. The arithmetic with `extract` is the portable way; DuckDB has `date_diff('month', cohort_month, active_month)` and does it in one call.\n\n## Step three: the matrix\n\n```sql\nSELECT cohort_month,\n       months_since,\n       count(DISTINCT customer_id) AS active,\n       count(DISTINCT customer_id)::numeric\n         / first_value(count(DISTINCT customer_id))\n             OVER (PARTITION BY cohort_month ORDER BY months_since) AS retention\nFROM aged\nGROUP BY cohort_month, months_since\nORDER BY cohort_month, months_since;\n```\n\nTwo grains in one query, and worth reading slowly. The GROUP BY produces one row per cohort per age, with the number of customers active at that age. The window then runs over those grouped rows, and `first_value` ordered by age within each cohort picks the age-0 count, which is the cohort's size. Divide by it and the `retention` column reads \"of the customers who started in January, 41% bought something in month 3\".\n\nPivoting `months_since` into columns, with the earlier lesson's technique, gives the triangle-shaped grid that cohort reports are known for.\n\n## The triangle, and the comparison it forbids\n\nThe grid is a triangle because a cohort that started three months ago has ages 0 to 3 and nothing further. That shape carries the most common misreading: comparing month-6 retention across cohorts when only the older cohorts have reached month 6. The newer cohorts are not worse; they are absent. Read the grid down a column, comparing the same age across cohorts, and only as far as every cohort in the comparison has reached. A \"retention is falling\" headline is often a triangle read along the wrong edge.\n\n## What \"retained\" means here\n\nThis query counts a customer as retained in a month if they ordered in that month. A customer who orders in months 0, 1 and 3 is retained at age 3 and not at age 2, and the curve can rise from month 2 to month 3. That is correct for purchase behaviour and different from subscription retention, where \"retained\" means \"had not cancelled by then\" and the curve can only fall. Both are called retention; a chart that does not say which will be compared with one of the other kind.\n\n## The habit\n\nA cohort report should state four things in its caption: the event that defines the cohort, the event that counts as active, whether the measure can rise, and the age beyond which cohorts are not comparable. Every one of them is a decision in the query above, and every one changes the shape of the triangle."},"check":{"question":"A cohort grid shows month-6 retention of 38% for the January cohort and blank for the June cohort, and the report is dated end of September. A reader concludes the June cohort has \"collapsed to zero by month 6\". What is wrong?","options":[{"text":"The June cohort's month-6 figure was excluded by a `count(DISTINCT ...)` that returns NULL for empty groups, so it should be read as zero after all.","correct":false,"tempting":"An empty group produces no row rather than a NULL count, and the group is empty because month 6 has not happened, not because nobody was active."},{"text":"The retention window used RANGE instead of ROWS and merged June's ages, leaving month 6 with no row to display.","correct":false,"tempting":"The window here only fetches the age-0 count per cohort; frame mode cannot remove an age from the grouped result."},{"text":"Retention measured by orders cannot fall to zero, so the blank must be a data-loading failure for June.","correct":false,"tempting":"Purchase-based retention can certainly reach zero; the blank is a matter of elapsed time, not of the measure's range."},{"text":"June's cohort is only three months old at the end of September, so its month-6 cell does not exist yet and no comparison at that age is possible.","correct":true}]},"prev":{"slug":"gaps-and-islands","title":"Streaks and sessions: gaps and islands","url":"https://addaly.com/learn/data-and-sql/gaps-and-islands"},"next":{"slug":"date-arithmetic-and-time-zones","title":"Dates, intervals and the two kinds of timestamp","url":"https://addaly.com/learn/data-and-sql/date-arithmetic-and-time-zones"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Cohorts and retention: how a month of new customers behaves afterwards', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/cohorts-and-retention (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Cohorts and retention: how a month of new customers behaves afterwards","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"A cohort is a group defined by a first event, retention is the share of it active at each age, and the grid is a triangle you read down a column only as far as every cohort has reached.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"the-question-a-total-hides","runs":[{"text":"The question a total hides"}]},{"t":"p","runs":[{"text":"Revenue went up. Was it because you are keeping customers, or because you are acquiring new ones fast enough to hide that the old ones leave? A total cannot say. A cohort analysis can: take everyone who started in the same month, and watch what share of them is still active one, two, three months later."}]},{"t":"h2","id":"step-one-assign-each-customer-to-a-cohort","runs":[{"text":"Step one: assign each customer to a cohort"}]},{"t":"p","runs":[{"text":"A cohort is defined by a first event, and the first event is a "},{"text":"min()","code":true},{"text":":"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH first_order AS (\n  SELECT customer_id, date_trunc('month', min(placed_at)) AS cohort_month\n  FROM orders\n  GROUP BY customer_id\n)"},{"t":"p","runs":[{"text":"One row per customer, with the month they first bought. This is a decision dressed as a query: “first order” could equally be “first sign-up” or “first paid order”, and a customer who bought once in 2023 and returned in 2026 belongs to the 2023 cohort forever. Say which event defines the cohort in the report title."}]},{"t":"h2","id":"step-two-each-customer-s-active-months-as-an-age","runs":[{"text":"Step two: each customer’s active months, as an age"}]},{"t":"code","lang":"sql","label":"SQL","text":"activity AS (\n  SELECT o.customer_id,\n         f.cohort_month,\n         date_trunc('month', o.placed_at) AS active_month\n  FROM orders o\n  JOIN first_order f USING (customer_id)\n  GROUP BY 1, 2, 3\n),\naged AS (\n  SELECT cohort_month,\n         customer_id,\n         (extract(year  FROM active_month) - extract(year  FROM cohort_month)) * 12\n       + (extract(month FROM active_month) - extract(month FROM cohort_month)) AS months_since\n  FROM activity\n)"},{"t":"p","runs":[{"text":"The "},{"text":"GROUP BY","code":true},{"text":" in "},{"text":"activity","code":true},{"text":" collapses several orders in one month to one row, because retention counts customers, not orders. "},{"text":"months_since","code":true},{"text":" is the customer’s age in months at the time of that activity: 0 for the cohort month itself, 1 for the following month, and so on. The arithmetic with "},{"text":"extract","code":true},{"text":" is the portable way; DuckDB has "},{"text":"date_diff('month', cohort_month, active_month)","code":true},{"text":" and does it in one call."}]},{"t":"h2","id":"step-three-the-matrix","runs":[{"text":"Step three: the matrix"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT cohort_month,\n       months_since,\n       count(DISTINCT customer_id) AS active,\n       count(DISTINCT customer_id)::numeric\n         / first_value(count(DISTINCT customer_id))\n             OVER (PARTITION BY cohort_month ORDER BY months_since) AS retention\nFROM aged\nGROUP BY cohort_month, months_since\nORDER BY cohort_month, months_since;"},{"t":"p","runs":[{"text":"Two grains in one query, and worth reading slowly. The GROUP BY produces one row per cohort per age, with the number of customers active at that age. The window then runs over those grouped rows, and "},{"text":"first_value","code":true},{"text":" ordered by age within each cohort picks the age-0 count, which is the cohort’s size. Divide by it and the "},{"text":"retention","code":true},{"text":" column reads “of the customers who started in January, 41% bought something in month 3”."}]},{"t":"p","runs":[{"text":"Pivoting "},{"text":"months_since","code":true},{"text":" into columns, with the earlier lesson’s technique, gives the triangle-shaped grid that cohort reports are known for."}]},{"t":"h2","id":"the-triangle-and-the-comparison-it-forbids","runs":[{"text":"The triangle, and the comparison it forbids"}]},{"t":"p","runs":[{"text":"The grid is a triangle because a cohort that started three months ago has ages 0 to 3 and nothing further. That shape carries the most common misreading: comparing month-6 retention across cohorts when only the older cohorts have reached month 6. The newer cohorts are not worse; they are absent. Read the grid down a column, comparing the same age across cohorts, and only as far as every cohort in the comparison has reached. A “retention is falling” headline is often a triangle read along the wrong edge."}]},{"t":"h2","id":"what-retained-means-here","runs":[{"text":"What “retained” means here"}]},{"t":"p","runs":[{"text":"This query counts a customer as retained in a month if they ordered in that month. A customer who orders in months 0, 1 and 3 is retained at age 3 and not at age 2, and the curve can rise from month 2 to month 3. That is correct for purchase behaviour and different from subscription retention, where “retained” means “had not cancelled by then” and the curve can only fall. Both are called retention; a chart that does not say which will be compared with one of the other kind."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"A cohort report should state four things in its caption: the event that defines the cohort, the event that counts as active, whether the measure can rise, and the age beyond which cohorts are not comparable. Every one of them is a decision in the query above, and every one changes the shape of the triangle."}]}],"check":{"question":[{"text":"A cohort grid shows month-6 retention of 38% for the January cohort and blank for the June cohort, and the report is dated end of September. A reader concludes the June cohort has “collapsed to zero by month 6”. What is wrong?"}],"options":[{"text":[{"text":"The June cohort’s month-6 figure was excluded by a "},{"text":"count(DISTINCT ...)","code":true},{"text":" that returns NULL for empty groups, so it should be read as zero after all."}],"correct":false,"tempting":[{"text":"An empty group produces no row rather than a NULL count, and the group is empty because month 6 has not happened, not because nobody was active."}]},{"text":[{"text":"The retention window used RANGE instead of ROWS and merged June’s ages, leaving month 6 with no row to display."}],"correct":false,"tempting":[{"text":"The window here only fetches the age-0 count per cohort; frame mode cannot remove an age from the grouped result."}]},{"text":[{"text":"Retention measured by orders cannot fall to zero, so the blank must be a data-loading failure for June."}],"correct":false,"tempting":[{"text":"Purchase-based retention can certainly reach zero; the blank is a matter of elapsed time, not of the measure’s range."}]},{"text":[{"text":"June’s cohort is only three months old at the end of September, so its month-6 cell does not exist yet and no comparison at that age is possible."}],"correct":true,"tempting":null}],"order":[2,3,0,1],"why":null},"prev":{"slug":"gaps-and-islands","title":"Streaks and sessions: gaps and islands","minutes":9},"next":{"slug":"date-arithmetic-and-time-zones","title":"Dates, intervals and the two kinds of timestamp","minutes":9}}}