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.

Practice

1. What is the main purpose of cohort analysis in Tableau?

easy

A. To create pie charts for sales distribution

B. To filter data by geographic location only

C. To group users by their start time and track their behavior over time

D. To calculate total revenue without time context

Solution

1. Step 1: Understand cohort analysis concept

   Cohort analysis groups users based on when they started using a product or service.

2. Step 2: Identify its purpose in Tableau

   It tracks user behavior or retention over time, not just static metrics like revenue or location.

3. Final Answer:

   To group users by their start time and track their behavior over time -> Option C

4. Quick Check:

   Cohort analysis = group by start time and track behavior [OK]

Hint: Remember: Cohorts track groups by start time over periods [OK]

Common Mistakes:

• Confusing cohort analysis with simple filtering
• Thinking cohort analysis is only about total sales
• Ignoring the time dimension in cohort grouping

2. Which of the following calculated fields correctly defines a cohort start month in Tableau?

easy

A. DATEDIFF('day', [User Signup Date], TODAY())

B. DATEPART('year', [User Signup Date]) + 1

C. SUM([User Signup Date])

D. DATETRUNC('month', [User Signup Date])

Solution

1. Step 1: Understand cohort start date calculation

   Cohort start is usually the first day of the period, here month, so DATETRUNC('month', date) is correct.

2. Step 2: Evaluate each option

   Calculating days to today gives elapsed time, not the start month; adding 1 to the year part shifts cohorts incorrectly; summing dates is invalid.

3. Final Answer:

   DATETRUNC('month', [User Signup Date]) -> Option D

4. Quick Check:

   Start month = DATETRUNC('month', date) [OK]

Hint: Use DATETRUNC to get cohort period start date [OK]

Common Mistakes:

• Using DATEPART instead of DATETRUNC for cohort start
• Calculating date differences instead of truncating
• Applying aggregation functions on dates incorrectly

3. Given the cohort start month calculated as DATETRUNC('month', [Signup Date]) and the current month as DATETRUNC('month', TODAY()), what does this calculation return?
DATEDIFF('month', DATETRUNC('month', [Signup Date]), DATETRUNC('month', TODAY()))

medium

A. The number of days since the user signed up

B. The number of months since the user signed up

C. The user's signup date truncated to the day

D. The total count of users signed up this month

Solution

1. Step 1: Analyze the DATEDIFF function

   DATEDIFF('month', start, end) returns the number of whole months between two dates.

2. Step 2: Apply to given dates

   It calculates months between the user's signup month and the current month, showing how many months have passed.

3. Final Answer:

   The number of months since the user signed up -> Option B

4. Quick Check:

   DATEDIFF('month', signup, today) = months since signup [OK]

Hint: DATEDIFF with 'month' counts months between dates [OK]

Common Mistakes:

• Confusing months with days in DATEDIFF
• Thinking it returns a date instead of a number
• Assuming it counts users instead of time difference

4. You created a calculated field for cohort period as:
DATEDIFF('month', [Cohort Start], [Order Date])
but the results show negative values. What is the most likely cause?

medium

A. [Order Date] is earlier than [Cohort Start], causing negative differences

B. The DATEDIFF function does not support 'month' as an interval

C. The calculation should use DATEADD instead of DATEDIFF

D. [Cohort Start] is not a date field but a string

Solution

1. Step 1: Understand DATEDIFF behavior

   DATEDIFF returns negative values if the first date is after the second date.

2. Step 2: Check date order in calculation

   If [Order Date] is before [Cohort Start], the difference is negative, which explains the issue.

3. Final Answer:

   [Order Date] is earlier than [Cohort Start], causing negative differences -> Option A

4. Quick Check:

   Negative DATEDIFF means first date > second date [OK]

Hint: Check date order: earlier date first to avoid negatives [OK]

Common Mistakes:

• Assuming DATEDIFF can't use 'month' interval
• Confusing DATEDIFF with DATEADD function
• Not verifying data types of date fields

5. You want to create a heatmap in Tableau showing user retention by cohort month and months since signup. Which combination of fields and visualization best achieves this?

hard

A. Rows: Cohort Month (DATETRUNC), Columns: Months Since Signup (DATEDIFF), Color: Count of Users

B. Rows: User ID, Columns: Signup Date, Color: Total Sales

C. Rows: Order Date, Columns: Product Category, Color: Average Price

D. Rows: Months Since Signup, Columns: Total Revenue, Color: Cohort Month

Solution

1. Step 1: Identify correct cohort and period fields

   Cohort Month groups users by signup month; Months Since Signup tracks time elapsed.

2. Step 2: Choose visualization layout

   Heatmap uses rows and columns for cohort and period, color shows user counts for retention.

3. Final Answer:

   Rows: Cohort Month (DATETRUNC), Columns: Months Since Signup (DATEDIFF), Color: Count of Users -> Option A

4. Quick Check:

   Heatmap = cohort by period with user count color [OK]

Hint: Heatmap axes: cohort start and months since signup, color by users [OK]

Common Mistakes:

• Using unrelated fields like product category or revenue
• Placing total revenue on columns instead of cohort period
• Not using count of users for color intensity