Common LOD use cases (customer first purchase, cohorts) in Tableau - Real Business Scenario

Scenario Mode

👤 Your Role: You are a data analyst at an online retail company.

📋 Request: Your manager wants to understand when customers made their first purchase and group customers into monthly cohorts based on that first purchase date.

📊 Data: You have a sales dataset with columns: Customer ID, Order Date, and Sales Amount.

🎯 Deliverable: Create a Tableau dashboard showing each customer's first purchase month and a cohort analysis of monthly customer groups with total sales.

Sample Data

Customer ID	Order Date	Sales Amount
C001	2023-01-15	120
C002	2023-01-20	80
C001	2023-02-10	150
C003	2023-02-25	200
C004	2023-03-05	50
C002	2023-03-15	100
C005	2023-03-20	300
C006	2023-04-01	75
C003	2023-04-10	125
C007	2023-04-15	90

1

Step 1: Create a calculated field to find each customer's first purchase date using FIXED LOD expression.

{ FIXED [Customer ID] : MIN([Order Date]) }

Expected Result

For example, Customer C001's first purchase date is 2023-01-15.

2

Step 2: Create a calculated field to extract the first purchase month from the first purchase date.

DATETRUNC('month', [First Purchase Date])

Expected Result

Customer C001's first purchase month is 2023-01-01.

3

Step 3: Create a cohort group by assigning customers to cohorts based on their first purchase month.

Use the First Purchase Month field as the cohort group dimension.

Expected Result

Customers grouped by their first purchase month, e.g., cohort '2023-01' includes C001 and C002.

4

Step 4: Build a view showing cohort groups on rows and order months on columns with SUM of Sales Amount as values.

Rows: First Purchase Month (cohort), Columns: DATETRUNC('month', [Order Date]), Values: SUM([Sales Amount])

Expected Result

A matrix showing sales by cohort over time, e.g., cohort 2023-01 sales in Jan, Feb, Mar, etc.

5

Step 5: Create a line chart showing total sales over time for each cohort to visualize retention and sales trends.

Use First Purchase Month as color, Order Month on X-axis, SUM(Sales Amount) on Y-axis.

Expected Result

Line chart showing how sales from each cohort change month by month.

6

Step 6: Add filters for Order Date range and Customer ID to allow interactive exploration.

Add Order Date filter and Customer ID filter to the dashboard.

Expected Result

User can filter data to specific periods or customers.

Final Result

Cohort Analysis Dashboard

+----------------+----------------+----------------+----------------+
| First Purchase | Jan 2023       | Feb 2023       | Mar 2023       |
| Month (Cohort) | Sales          | Sales          | Sales          |
+----------------+----------------+----------------+----------------+
| 2023-01        | $200           | $150           | $100           |
| 2023-02        |                | $200           |                |
| 2023-03        |                |                | $450           |
| 2023-04        |                |                |                |
+----------------+----------------+----------------+----------------+

(Line chart below showing sales trends by cohort over months)

✓Customers who first purchased in January 2023 generated sales in subsequent months, showing repeat purchases.

✓The February 2023 cohort shows strong sales in its first month but no data beyond that yet.

✓March 2023 cohort has high initial sales, indicating strong new customer acquisition.

✓April 2023 cohort is just starting with initial sales data.

Bonus Challenge

Create a calculated field to compute customer retention rate by cohort month over time.

Show Hint

Use LOD to count distinct customers per cohort and per order month, then divide to find retention percentage.