Cohort analysis patterns in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a product analyst at an online subscription service company.

📋 Request: Your manager wants to understand how different groups of customers behave over time after their first purchase. They ask you to create a cohort analysis to track customer retention monthly.

📊 Data: You have customer purchase data with columns: CustomerID, PurchaseDate, and Revenue. Each row represents a purchase made by a customer on a specific date.

🎯 Deliverable: Create a Tableau dashboard showing monthly retention rates for customer cohorts based on their first purchase month.

Sample Data

CustomerID	PurchaseDate	Revenue
C001	2023-01-15	50
C002	2023-01-20	70
C001	2023-02-10	50
C003	2023-02-25	60
C004	2023-03-05	80
C002	2023-03-15	70
C005	2023-03-20	90
C001	2023-04-10	50
C003	2023-04-15	60
C006	2023-04-25	100

1

Step 1: Connect your data source in Tableau and load the purchase data.

Import the table with CustomerID, PurchaseDate, and Revenue columns.

Expected Result

Data is loaded and visible in Tableau data pane.

2

Step 2: Create a calculated field to find the first purchase month for each customer.

First Purchase Month = DATETRUNC('month', { FIXED [CustomerID] : MIN([PurchaseDate]) })

Expected Result

Each purchase row now has the customer's cohort month based on their first purchase.

3

Step 3: Create a calculated field to find the purchase month for each transaction.

Purchase Month = DATETRUNC('month', [PurchaseDate])

Expected Result

Each purchase row has the month of the purchase.

4

Step 4: Create a calculated field to calculate the number of months since the first purchase.

Months Since First Purchase = DATEDIFF('month', [First Purchase Month], [Purchase Month])

Expected Result

Each purchase row shows how many months after the first purchase it occurred.

5

Step 5: Create a cohort retention view: Drag First Purchase Month to Columns, Months Since First Purchase to Rows, and count distinct CustomerID to Text.

Use COUNTD([CustomerID]) as the measure.

Expected Result

A matrix showing how many customers from each cohort made purchases in each month after their first purchase.

6

Step 6: Calculate cohort size for each First Purchase Month to use for retention rate calculation.

Cohort Size = { FIXED [First Purchase Month] : COUNTD(IF [Months Since First Purchase] = 0 THEN [CustomerID] END) }

Expected Result

Each cohort month has a fixed total number of customers who made their first purchase.

7

Step 7: Create a calculated field for retention rate by dividing monthly active customers by cohort size.

Retention Rate = COUNTD([CustomerID]) / [Cohort Size]

Expected Result

Retention rate values between 0 and 1 for each cohort month and months since first purchase.

8

Step 8: Build a heatmap: Place First Purchase Month on Columns, Months Since First Purchase on Rows, and Retention Rate on Color.

Use color gradient from light (low retention) to dark (high retention).

Expected Result

Heatmap visually showing retention patterns over time for each cohort.

9

Step 9: Format the dashboard with clear titles, axis labels, and tooltips explaining retention rates.

Add descriptive text and ensure color contrast is accessible.

Expected Result

Dashboard is easy to read and interpret for non-technical users.

Final Result

Cohort Analysis Heatmap Dashboard

First Purchase Month ->
| Month 0 | Month 1 | Month 2 | Month 3 |
-----------------------------------------
Jan 2023 | ██████  | ████    | ██      | ░      |
Feb 2023 | ██████  | ████    | ░       |        |
Mar 2023 | ██████  | ██      |         |        |
Apr 2023 | ██████  |         |         |        |

Legend: ██████ = 100% retention, ░ = low retention

✓Customers tend to have the highest retention in their first month after purchase.

✓Retention decreases steadily over the following months.

✓Later cohorts (e.g., March and April) show lower retention in month 1 compared to earlier cohorts.

✓This pattern helps identify when customers are most likely to stop purchasing.

Bonus Challenge

Add revenue retention analysis by calculating total revenue per cohort month and months since first purchase, then visualize it alongside customer retention.

Show Hint

Create a calculated field summing Revenue per cohort and month, then divide by total cohort revenue to get revenue retention rate.