Concept Flow
Order Date Column (B) --> Extract Month --> Compare to Earliest Month --> Assign Cohort Label
The cohort analysis groups orders by the month of their order date compared to the earliest order month in the data.
0
0
Cohort analysis patterns in Tableau - Cell-by-Cell Formula Trace
Formula
IF DATEPART('month', [Order Date]) = DATEPART('month', {MIN([Order Date])}) THEN 'Cohort 1' ELSE 'Cohort 2+' ENDThis Tableau formula checks if the order month matches the earliest order month and assigns cohort labels accordingly.
Step-by-Step Trace
| Customer ID | Order Date | Revenue | Order Month | Earliest Month | Cohort |
|---|---|---|---|---|---|
| C001 | 2023-01-15 | 100 | 1 | 1 | Cohort 1 |
| C002 | 2023-01-20 | 150 | 1 | 1 | Cohort 1 |
| C001 | 2023-02-10 | 200 | 2 | 1 | Cohort 2+ |
| C003 | 2023-02-15 | 300 | 2 | 1 | Cohort 2+ |
| C002 | 2023-03-05 | 250 | 3 | 1 | Cohort 2+ |
| C004 | 2023-03-10 | 400 | 3 | 1 | Cohort 2+ |
Orders in January are assigned 'Cohort 1', others 'Cohort 2+'.
Variable Tracker
| Variable | Value | Explanation |
|---|---|---|
| MIN([Order Date]) | 2023-01-15 | Earliest order date in dataset |
| DATEPART('month', [Order Date]) | Varies per row (1,2,3) | Extracts month number from each order date |
| DATEPART('month', MIN([Order Date])) | 1 | Month number of earliest order date |
| Cohort Label | 'Cohort 1' or 'Cohort 2+' | Assigned based on month comparison |
Key Moments
What is the earliest order date found in the data?
How does the formula determine which cohort an order belongs to?
What cohort label is assigned to orders not in the earliest month?
Sheet Trace Quiz - 3 Questions
Test your understanding
Which month is considered the earliest order month in the sample data?
Key Result
Cohort analysis groups data by the time period of first activity. Here, orders are grouped by the month of their order date compared to the earliest order month, enabling tracking of customer behavior over time.
Transcript
We start by finding the earliest order date in the data, which is January 15, 2023. Then, for each order, we extract the month number from its order date. We compare this month to the earliest order month. If they match, we assign the label 'Cohort 1'. Otherwise, we assign 'Cohort 2+'. This simple pattern helps us group customers or orders by their first active month, a common approach in cohort analysis.