Cohort analysis patterns in Tableau - Step-by-Step Guide

Introduction

Cohort analysis helps you see how groups of customers behave over time. It solves the problem of understanding customer retention and trends by grouping people who share a common event, like their first purchase date.

When you want to track how long customers keep buying after their first purchase

When you need to compare different groups of users who started using your product in different months

When you want to see if a marketing campaign improved customer retention over time

When you want to analyze user behavior by signup week or month

When you want to measure how long it takes for customers to make repeat purchases

Steps

Step 1: Connect

- Tableau Data Source pane

Your data with customer IDs and dates loads into Tableau

💡 Make sure your data includes a date field for the event and a unique customer ID

Step 2: Create a calculated field

- Data pane → right-click → Create Calculated Field

A new field to identify the cohort start date is ready

💡 Name it 'Cohort Month' and use a formula like: DATETRUNC('month', [First Purchase Date])

Step 3: Drag 'Cohort Month' to Columns shelf

- Columns shelf

Cohorts appear as columns representing each group's start month

Step 4: Create another calculated field for 'Months Since Cohort'

- Data pane → right-click → Create Calculated Field

A field showing how many months passed since the cohort start

💡 Use formula: DATEDIFF('month', [Cohort Month], DATETRUNC('month', [Purchase Date]))

Step 5: Drag 'Months Since Cohort' to Rows shelf

- Rows shelf

Rows show time periods since the cohort started

Step 6: Drag a measure like 'Number of Customers' to Text on Marks card

- Marks card → Text

Numbers appear showing how many customers made purchases each month after their cohort start

Step 7: Change Marks type to Heat Map

- Marks card → drop-down → select 'Square' and then Color

Colors show retention intensity, making patterns easy to spot

Before vs After

Before

Table shows raw purchase data with customer IDs and dates mixed together

After

Tableau view shows cohorts by start month on columns, months since cohort on rows, and colored squares representing customer retention counts

Settings Reference

Date truncation level

📍 Calculated field editor

Defines the time unit for cohort grouping

Default: month

Mark type

📍 Marks card

Controls how data points are visually represented

Default: Automatic

Color palette

📍 Marks card → Color

Shows intensity or categories in the heat map

Default: Sequential

Common Mistakes

Using purchase date instead of first purchase date for cohort grouping

This mixes different customers' start times and ruins cohort grouping

Create a calculated field to find each customer's first purchase date and use it for cohort assignment

Not truncating dates to the same level (e.g., mixing days and months)

This causes mismatched cohorts and confusing results

Use DATETRUNC to set all dates to the same level like month or week

Summary

Cohort analysis groups customers by their start date to track behavior over time

Tableau uses calculated fields and date truncation to create cohorts and time intervals

Visualizing cohorts as a heat map helps spot retention trends easily

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