Skip to content
Tutorials Center
My learning

Topics

Lesson 31 of 35 ยท Projects

Project: Customer Cohort and Retention Analysis

Build a cohort retention heat map in Tableau with a FIXED first-order date, DATEDIFF month index and percent of cohort, checked with SQL and pandas.

"Are customers coming back?" is one of the most valuable questions a business can ask, and a plain sales chart cannot answer it. A cohort analysis groups customers by the month of their first purchase and tracks how many are still active one, two, three months later. The classic output is a retention heat map: cohorts down the side, months since first purchase across the top, and the percentage of the cohort still buying in each cell.

In this project you build that chart in Tableau. It is a perfect showcase for level of detail (LOD) expressions, because the cohort of a customer is a property of the customer that must not change when you filter the view. Tableau runs outside the browser, so the build steps are instructions and the calculated fields are read-only code. The runnable SQL and pandas are stand-ins that compute the same table, so you can check Tableau's numbers.

The data

One table of orders: order ID, customer ID and order date. Twelve customers and 24 orders from January to May 2025 are enough to see the pattern. (In a real project this would be a database table with hundreds of thousands of rows.)

SQL Create the orders and look at them

Press Run (or Ctrl + Enter) to see the result here.

A monthly count of customers (the "active customers" line chart) rises and falls, but it cannot tell you whether the people are new or returning. The cohort view can.

Step 1: the cohort date

In Tableau, create a calculated field named Cohort Month. It is a FIXED expression: for each customer, the date of their first order, truncated to the month.

Text
// Cohort Month
{ FIXED [Customer ID] : DATETRUNC('month', MIN([Order Date])) }

A FIXED LOD is computed per customer before ordinary dimension filters. That is exactly what you need: if a user later filters the dashboard to April, a customer's cohort must still be the month they first bought, not April. (See Order of Operations and Filter Pitfalls.) If you filter the data source or an extract, though, the first order could be cut off, so keep the full history in the extract, or the cohort will be wrong.

Step 2: the month index

Next, a Months Since First Order field:

Text
// Months Since First Order
DATEDIFF('month', [Cohort Month], DATETRUNC('month', [Order Date]))

Month 0 is the month of the first order, month 1 the next calendar month, and so on. Use calendar months, not 30-day windows, so cohorts line up in a grid.

Step 3: counting customers

Create Active Customers, which is COUNTD([Customer ID]). In the view, each cell (cohort x month index) counts the distinct customers who ordered in that month. To turn it into a percentage, you need the cohort size: the customers in month 0 of that cohort.

Text
// Cohort Size
{ FIXED [Cohort Month] : COUNTD([Customer ID]) }

// Retention %
COUNTD([Customer ID]) / MIN([Cohort Size])

Here MIN([Cohort Size]) is an aggregate of a value that is constant within a cohort, so MIN, MAX and ATTR all give the same figure; MIN is the cheapest. Alternatively use a table calculation: put COUNTD([Customer ID]) in the view and apply Percent of Total computed along the index (Table Across), or divide by LOOKUP(COUNTD([Customer ID]), FIRST()).

Three calculated fields

Cohort Month FIXED customer, first order month.
Months Since DATEDIFF from cohort to order.
Retention % Active customers over cohort size.
1 / 2

The retention table in SQL

Here is the same logic in SQL. Check it against Tableau's numbers: the cell counts and percentages must match.

SQL Cohort retention matrix in SQL

Press Run (or Ctrl + Enter) to see the result here.

Read the result like Tableau's heat map: each cohort starts at 100% in month 0 by definition, and later months show what share came back. The numbers in the corner are small in this toy data set, but the shape (a triangle, because newer cohorts have fewer months of history) is the same in any real data.

The same table in pandas

A pivot table gives the familiar matrix layout. This is the table Tableau draws, with cohort on the rows and index on the columns.

Python Retention matrix with pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The pandas table fills the not-yet-reached cells with 0 (look at the bottom right); in Tableau those cells stay empty, because a month that has not happened is not a month of zero retention. The two stand-ins agree otherwise, which is exactly the point: when a number in Tableau looks surprising, rebuild it independently.

Step 4: build the view

  1. Drag Cohort Month to Rows (as a discrete MONTH or "Month Year") and Months Since First Order to Columns (as a discrete dimension, right-click > Convert to Discrete).
  2. Change the mark type to Square and drag Retention % to Color and Text. Use a sequential colour palette, and set it so 0% is the lightest colour and 100% the darkest.
  3. Format Retention % as a percentage with no decimals.
  4. Add Cohort Size to Tooltip and to the row headers (as a second column), so a viewer sees "Jan 2025 (4 customers)".
  5. Remove the null cells (bottom-right of the triangle) rather than showing zeros: these months have not happened yet. A zero would claim that nobody came back, when in truth the month has not arrived.
  6. Add a second sheet, Retention curve: a line chart of average retention % by month index, one line per cohort or a single average line with a reference line at the 30% target.
  7. Combine both on a dashboard with a filter by customer segment or product category. Because Cohort Month is FIXED, the cohorts do not move when the filter changes, but the active counts do.

Watch the last month of the data. It is incomplete, so recent cohorts show artificially low retention in the most recent column. Either exclude the current month with a filter or mark it clearly.

Reading the heat map

Look for three patterns:

  • Row by row: does each new cohort start stronger or weaker? A paler row means a weaker cohort, perhaps after a campaign that attracted one-time buyers.
  • Column by column: where is the biggest drop? Usually month 1. If month 1 is good and month 2 collapses, the problem is in the second order.
  • The diagonal: a vertical stripe of low values across all cohorts in one calendar month signals an event (an outage, a price change), not a customer behaviour problem.

Then act on it: improve onboarding if month 1 is the cliff, add a reminder email at day 25, or compare retention by acquisition channel.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why is Cohort Month a FIXED LOD expression?

    FIXED is evaluated before dimension filters, so each customer keeps the same cohort whatever the filters.
  2. 2. In a retention table, what is the value in month 0 by definition?

    Month 0 is the first-purchase month, so every customer in the cohort is active: 100%.
  3. 3. Why does a cohort heat map form a triangle?

    A cohort that started in May cannot have month 4 yet, so those cells are empty (not zero).
  4. 4. What does the DATEDIFF('month', [Cohort Month], DATETRUNC('month', [Order Date])) field return for a purchase in the cohort's first month?

    Same month gives a difference of 0, the cohort's month 0.

Summary

  • A cohort groups customers by first-purchase month; retention shows how many return over time.
  • Build it with a FIXED Cohort Month, a DATEDIFF month index and a Retention % over the cohort size, shown as a coloured square heat map.
  • Keep the full history in the extract; otherwise the first order (and the cohort) will be wrong.
  • Treat unreached months as empty, and watch the incomplete last month.
  • The SQL and pandas stand-ins produce the matrix to verify Tableau against.

Next: Project: Profitability Analysis and Forecast Story