Common LOD use cases (customer first purchase, cohorts) in Tableau - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to calculate the first purchase date per customer using LOD expression.

Tableau

{ FIXED [Customer ID] : MIN([1])}

Drag options to blanks, or click blank then click option'

A[Purchase Date]

B[Order Date]

C[Transaction Date]

D[Sale Date]

Attempts:

3 left

💡 Hint

Common Mistakes

Using a date field that is not related to purchase, like shipping date.

The LOD expression fixes the calculation at the customer level and finds the earliest order date, which represents the first purchase date.

2fill in blank

medium

Complete the LOD expression to count unique customers who made their first purchase in each cohort month.

Tableau

COUNTD({ FIXED DATETRUNC('month', [Order Date]) : IF DATETRUNC('month', [Order Date]) = [1] THEN [Customer ID] END })

Drag options to blanks, or click blank then click option'

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

BDATETRUNC('month', [Order Date])

CDATETRUNC('year', [Order Date])

DDATETRUNC('day', [Order Date])

Attempts:

3 left

💡 Hint

Common Mistakes

Using order date instead of first purchase date for cohort grouping.

This expression compares the order month to the customer's first purchase month to identify cohort members.

3fill in blank

hard

Fix the error in the LOD expression to calculate the number of months since a customer's first purchase.

Tableau

DATEDIFF('month', [1], [Order Date])

Drag options to blanks, or click blank then click option'

A[First Purchase Date]

B[Customer First Purchase]

CMIN([Order Date])

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

Attempts:

3 left

💡 Hint

Common Mistakes

Using a field that is not aggregated or calculated per customer.

The first purchase date must be calculated using a FIXED LOD expression to get the minimum order date per customer.

4fill in blank

hard

Fill both blanks to create a cohort calculation that assigns customers to their first purchase month and calculates retention month.

Tableau

DATETRUNC('month', [1]) AS CohortMonth, DATEDIFF('month', [2], [Order Date]) AS RetentionMonth

Drag options to blanks, or click blank then click option'

A[Order Date]

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

C[First Purchase Date]

D[Customer ID]

Attempts:

3 left

💡 Hint

Common Mistakes

Using order date directly instead of first purchase date for cohort assignment.

Both blanks use the FIXED LOD expression to get the customer's first purchase date for cohort and retention calculations.

5fill in blank

hard

Fill all three blanks to write a cohort retention calculation: assign cohort month, calculate retention month, and count distinct customers.

Tableau

{ FIXED DATETRUNC('month', [1]), DATEDIFF('month', [2], [Order Date]) : COUNTD([3]) }

Drag options to blanks, or click blank then click option'

A[Order Date]

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

C[Customer ID]

D[First Purchase Date]

Attempts:

3 left

💡 Hint

Common Mistakes

Counting orders instead of customers, or using order date instead of first purchase date.

The cohort month and retention month use the fixed first purchase date, and the count distinct is on Customer ID to count unique customers.

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