Common LOD use cases (customer first purchase, cohorts) in Tableau - Dashboard Guide

Dashboard Mode - Common LOD use cases (customer first purchase, cohorts)

Dashboard Goal

Understand customer behavior by identifying their first purchase date and grouping them into monthly cohorts to analyze retention and sales trends over time.

Sample Data

Customer ID	Order Date	Sales Amount
C001	2023-01-15	100
C002	2023-01-20	150
C001	2023-02-10	200
C003	2023-02-15	300
C002	2023-03-05	250
C004	2023-03-20	400
C005	2023-04-01	500

Dashboard Components

• KPI Card: Total Customers
  Formula: COUNTD([Customer ID])
  Result: 5
• KPI Card: Total Sales
  Formula: SUM([Sales Amount])
  Result: 1900
• Calculated Field: First Purchase Date per Customer (LOD)
  Formula: { FIXED [Customer ID] : MIN([Order Date]) }
  Description: Finds the earliest order date for each customer.
• Calculated Field: Customer Cohort Month
  Formula: DATETRUNC('month', [First Purchase Date per Customer])
  Description: Groups customers by the month of their first purchase.
• Bar Chart: Number of Customers by Cohort Month
  Data: Count distinct customers grouped by Customer Cohort Month.
  Purpose: Shows how many customers started in each month.
• Line Chart: Monthly Sales by Cohort
  Data: Sum of sales per month for each cohort group.
  Purpose: Tracks sales trends over time for each cohort.

Dashboard Layout

+----------------------+----------------------+
| Total Customers (KPI) | Total Sales (KPI)    |
+----------------------+----------------------+
|                                              |
|  Bar Chart: Customers by Cohort Month         |
|                                              |
+----------------------------------------------+
|                                              |
|  Line Chart: Monthly Sales by Cohort          |
|                                              |
+----------------------------------------------+

Interactivity

A date filter allows selecting a range of order dates. When applied, all components update to show data only for orders within the selected date range. For example, selecting orders from 2023-02-01 to 2023-03-31 updates the KPIs and charts to reflect customers and sales in that period.

Hovering over bars or lines shows tooltips with exact counts or sales amounts.

Self Check

If you add a filter to show only customers with first purchase in February 2023, which components update?

• Total Customers KPI updates to count only customers who started in February.
• Bar Chart updates to show only the February cohort.
• Line Chart updates to show sales over time for the February cohort only.
• Total Sales KPI updates to sum sales from customers in the February cohort.

Key Result

Dashboard shows customer first purchase cohorts and their sales trends over time using LOD calculations in Tableau.

Practice

1. What is the main purpose of using a Fixed Level of Detail (LOD) expression in Tableau for customer analysis?

easy

A. To calculate the total sales for the current view only

B. To find each customer's first purchase date regardless of filters

C. To change the data source connection

D. To create a new data table outside Tableau

Solution

1. Step 1: Understand Fixed LOD expression role

   Fixed LOD expressions calculate values at a fixed granularity, ignoring filters that affect the view.

2. Step 2: Apply to customer first purchase date

   Using Fixed LOD, you can find the earliest purchase date per customer, even if the view filters change.

3. Final Answer:

   To find each customer's first purchase date regardless of filters -> Option B

4. Quick Check:

   Fixed LOD finds fixed values like first purchase [OK]

Hint: Fixed LOD fixes calculation level ignoring filters [OK]

Common Mistakes:

• Confusing Fixed LOD with simple aggregation
• Thinking LOD changes data source
• Assuming LOD creates new tables

2. Which of the following is the correct syntax for a Fixed LOD expression to find the first purchase date per customer in Tableau?

easy

A. { FIXED [Customer ID] : MIN([Purchase Date]) }

B. { INCLUDE [Customer ID] : MAX([Purchase Date]) }

C. { EXCLUDE [Customer ID] : SUM([Sales]) }

D. { FIXED [Purchase Date] : COUNT([Customer ID]) }

Solution

1. Step 1: Identify Fixed LOD syntax

   Fixed LOD uses curly braces with FIXED keyword, then dimension(s), colon, and aggregation.

2. Step 2: Match expression to find first purchase date

   MIN([Purchase Date]) per [Customer ID] finds earliest purchase date per customer.

3. Final Answer:

   { FIXED [Customer ID] : MIN([Purchase Date]) } -> Option A

4. Quick Check:

   Fixed LOD with MIN date per customer = correct syntax [OK]

Hint: Fixed LOD uses { FIXED [Dimension] : Aggregation } [OK]

Common Mistakes:

• Using INCLUDE or EXCLUDE instead of FIXED
• Using MAX instead of MIN for first purchase
• Fixing on wrong dimension like Purchase Date

3. Given the LOD expression { FIXED [Customer ID] : MIN([Purchase Date]) }, what will be the result if a customer has purchases on 2023-01-10, 2023-02-15, and 2023-03-20?

medium

A. NULL

B. 2023-03-20

C. 2023-02-15

D. 2023-01-10

Solution

1. Step 1: Understand MIN aggregation in LOD

   MIN([Purchase Date]) returns the earliest date for the fixed customer.

2. Step 2: Identify earliest purchase date

   Among 2023-01-10, 2023-02-15, 2023-03-20, the earliest is 2023-01-10.

3. Final Answer:

   2023-01-10 -> Option D

4. Quick Check:

   MIN date per customer = earliest purchase [OK]

Hint: MIN returns earliest date in Fixed LOD [OK]

Common Mistakes:

• Choosing MAX instead of MIN
• Confusing date formats
• Assuming NULL if multiple purchases

4. You wrote this LOD expression to find first purchase date: { FIXED [Customer ID] : MIN([Purchase Date]) }. But the result shows the same date for all customers. What is the most likely error?

medium

A. You forgot to include [Customer ID] in the view or filter context

B. You used MAX instead of MIN

C. You wrote INCLUDE instead of FIXED

D. You used SUM instead of MIN

Solution

1. Step 1: Check LOD expression correctness

   The expression syntax is correct for Fixed LOD with MIN.

2. Step 2: Understand why all customers show same date

   If [Customer ID] is not in the view or filter, Tableau aggregates all customers together, showing one date.

3. Final Answer:

   You forgot to include [Customer ID] in the view or filter context -> Option A

4. Quick Check:

   Missing dimension in view causes same value for all [OK]

Hint: Always include LOD dimension in view to see distinct results [OK]

Common Mistakes:

• Changing aggregation instead of checking view
• Confusing FIXED with INCLUDE
• Ignoring filter context effects

5. You want to create customer cohorts by their first purchase month using LOD expressions. Which approach correctly assigns each customer to their cohort month?

hard

A. { EXCLUDE [Customer ID] : COUNT([Customer ID]) }

B. { INCLUDE [Customer ID] : MAX([Purchase Date]) }

C. { FIXED [Customer ID] : DATETRUNC('month', MIN([Purchase Date])) }

D. { FIXED [Purchase Date] : MIN([Customer ID]) }

Solution

1. Step 1: Identify cohort definition

   Cohorts group customers by their first purchase month, so we need first purchase date truncated to month.

2. Step 2: Use Fixed LOD to get first purchase month per customer

   Fixed LOD with MIN([Purchase Date]) finds first purchase date; DATETRUNC('month', ...) converts it to month start.

3. Final Answer:

   { FIXED [Customer ID] : DATETRUNC('month', MIN([Purchase Date])) } -> Option C

4. Quick Check:

   Fixed LOD + DATETRUNC for cohort month = correct [OK]

Hint: Use DATETRUNC with Fixed LOD for cohort month [OK]

Common Mistakes:

• Using INCLUDE or EXCLUDE instead of FIXED
• Not truncating date to month
• Fixing on wrong dimension