Cohort analysis patterns in Tableau - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Monthly Cohort Size Using LOD Expression

You have a dataset with customer sign-up dates and purchase dates. Which Tableau LOD expression correctly calculates the number of customers who signed up in each month (cohort size)?

A { INCLUDE DATETRUNC('month', [Purchase Date]) : COUNTD([Customer ID]) }

B { EXCLUDE [Customer ID] : COUNTD([SignUp Date]) }

C { FIXED [Customer ID] : COUNTD(DATETRUNC('month', [SignUp Date])) }

D { FIXED DATETRUNC('month', [SignUp Date]) : COUNTD([Customer ID]) }

💡 Hint

Think about fixing the cohort by sign-up month and counting unique customers.

The correct LOD expression fixes the calculation at the month level of the sign-up date and counts distinct customers. Option D does this correctly.

Option D fixes by purchase month, which is not the cohort definition.

Option D excludes customer ID incorrectly and counts sign-up dates, which is wrong.

Option D fixes by customer ID, which is not the cohort grouping.

❓ visualization

intermediate

Best Visualization for Monthly Cohort Retention

You want to show how many customers from each monthly cohort make repeat purchases over the next 6 months. Which visualization type best communicates this pattern in Tableau?

A Line chart with purchase dates on X-axis and total sales on Y-axis

B Pie chart showing total customers per cohort

C Heatmap with cohorts on rows and months since sign-up on columns, colored by retention count

D Bar chart showing total purchases per month

💡 Hint

Think about showing retention over time for each cohort distinctly.

A heatmap with cohorts as rows and months since sign-up as columns colored by retention count clearly shows retention patterns over time.

Pie charts and bar charts do not show retention over time per cohort.

Line charts with purchase dates show overall sales trends, not cohort retention.

🧠 Conceptual

advanced

Understanding Cohort Analysis Purpose

Why is cohort analysis important in business intelligence when analyzing customer behavior?

A It groups customers by shared characteristics to track behavior over time, revealing retention and lifecycle patterns.

B It aggregates all customers into one group to calculate total sales only.

C It predicts future sales using only last month’s data.

D It removes outliers from the dataset to improve accuracy.

💡 Hint

Think about how grouping by time of first action helps understand behavior changes.

Cohort analysis groups customers by shared starting points (like sign-up month) to observe how their behavior changes over time, which helps understand retention and lifecycle.

Options B, C, and D do not describe cohort analysis correctly.

❓ data_modeling

advanced

Designing Data Model for Cohort Analysis

Which data model design best supports efficient cohort analysis in Tableau?

A A fact table with customer transactions linked to a customer dimension table containing sign-up date

B A single flat table with all data duplicated for each transaction without keys

C Separate tables for sales and marketing with no relationship

D Only aggregated monthly sales data without customer details

💡 Hint

Think about how to connect transactions to customer cohorts efficiently.

Option A provides a normalized model linking transactions to customer info including sign-up date, enabling cohort grouping.

Option A duplicates data causing inefficiency.

Option A lacks relationships needed for analysis.

Option A lacks customer-level data needed for cohorts.

🔧 Formula Fix

expert

Identify the Error in Cohort Retention Calculation

You wrote this Tableau calculated field to compute retention rate for each cohort month:

SUM([Repeat Purchases]) / COUNTD([Customer ID])

But the retention rates are incorrect and inconsistent. What is the most likely cause?

Tableau

SUM([Repeat Purchases]) / COUNTD([Customer ID])

A COUNTD([Customer ID]) counts repeat purchases instead of unique customers.

B The denominator counts all customers, not just those in the cohort month, causing incorrect retention rates.

C The formula should use AVG instead of SUM for repeat purchases.

D SUM aggregation cannot be used on [Repeat Purchases] because it is a string field.

💡 Hint

Check if the denominator matches the cohort group correctly.

The denominator must count only customers in the specific cohort month to calculate correct retention. Counting all customers inflates the denominator and skews rates.

Option B is incorrect because repeat purchases is numeric.

Option B is wrong because SUM is appropriate for counting repeat purchases.

Option B is incorrect because COUNTD counts unique customers, not purchases.

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