Performing Cohort Analysis Tableau LOD: 5 Easy Steps

Sales volume is a key metric in any business setting. The decision-makers in any organization are always interested in knowing the volume of sales contributed by each Customer Cohort. Customers are normally divided into different Cohorts based on the date they made their first purchase with the business. The trend of each Cohort can then be observed in terms of their total contribution to the total company sales. 

Before Cohort Analysis Tableau LOD expressions were added to Tableau, one had to use custom SQL queries or other mechanisms to create the customer start date if that date was not available in the underlying dataset. 

However, this has been made easier by LOD calculations. This article will help you understand how you can easily use Cohort Analysis Tableau LOD expressions to perform an in-depth and accurate analysis of your data.

Prerequisite

Before deep-diving into Tableau LOD expression, understanding the meaning of cohort analysis is important. Let’s first understand the definition of Cohort Analysis.

What is Cohort?

A cohort is a group of individuals or subjects that share common traits, Cohort can be defined as a set of users or customers that are grouped together because they have a common identifier. Cohorts can be identified based on the date they became customers, age, behavior, experience, or other characteristics that can be used to group groups of people.

What is Cohort Analysis?

Cohort Analysis is a type of behavioral analysis that divides a large amount of complex data into related groups and performs the analysis. These small groups or cohorts tend to share common traits or experiences within a particular time period. This process allows organizations to identify key trends and patterns throughout the user/customer lifecycle through a cohort rather than accessing individual customers.

What are Tableau LOD Expressions?

In Tableau, LOD (Level of Detail) expressions provide users with a way to easily calculate aggregations that are not at the same level of granularity as the visualization. You can then use arbitrary ways to integrate those values within visualizations. With LOD expressions, you get more control to determine granularity levels in your computations. The reason is that you can compute values at both the Data Source and Visualization Levels. 

With Tableau LOD expressions, you can change the most granular metrics on which analysis takes place. An analysis like AVG(Sales) by State includes a dimension that you’re slicing and dicing the measure by as well as the aggregation of the measure. 

Before Tableau 9, without clever hacking like leveraging table calculations or duplicating a data source, you could be stuck using the same details for the whole view. Tableau now allows you to change the level of detail for specific measures using which you can compare and contrast numbers at different granularities on one chart. The LOD expressions in Tableau have now made it possible to calculate things like AVG(Sales) by State minus AVG(Sales) for the whole dataset to know how the sales per State compare to the overall average sales. 

This gives you much flexibility in your data analyses since you can explicitly state the level of detail for various numbers in your view. Tableau LOD expressions are good for doing Cohort Analysis or looking for totals or averages across segments. 

The three types of LOD calculations are as follows:

• Fixed LOD Calculation
• Include LOD Calculation
• Exclude LOD Calculation

1) Fixed LOD Calculation

With Fixed LOD calculations, you specify the aggregations required for the specific dimensions, regardless of whether these dimensions are on your visualization or not. 

2) Include LOD Calculation

 With the INCLUDE LOD calculations, you state the dimensions you need to aggregate your measures against in addition to the dimensions in the view. 

3) Exclude LOD Calculation

With the EXCLUDE LOD calculation, you state the dimensions (included in the view) to exclude when aggregating your measures.

A sample Tableau LOD expression is as follows:

Steps to Perform Cohort Analysis Using Tableau LOD Expressions

A Cohort refers to a data subset that shares common characteristics for a particular period. So, instead of looking at the entire data set, it is broken into related groups. Cohorts help data analysts detect changes in trends and determine the outcomes that are associated with the subset. The related data should have a time-based list of events whose behavior is to be analyzed. 

In Cohort Analysis, you must determine how the Cohorts will be defined. If it has not been represented as a dimension in your dataset, you will have to create calculated fields or sets. 

The following steps can help you create Cohort Analysis Tableau LOD expressions:

• Step 1: Model your Data
• Step 2: Explore your Dataset
• Step 3: Define the Cohorts
• Step 4: Define the Start Point
• Step 5: Create Calculated Fields

Cohort Analysis Tableau LOD Step 1: Model your Data

The first step should be to evaluate the point of the analysis and model your data.

Let’s say you’re using Covid-19 data to perform Cohort Analysis. The spread of Covid-19 worldwide occurred at different times, starting in China, then in Europe, and then in America. It’s difficult to compare countries since the spread started in different countries at different times. Hence, it’s necessary for you to define a common characteristic that overlaps the well-known growth curves of the reported positive cases. You can then come up with a solution to overlap the curves and help in explaining the evolution of the virus in different countries about the number of days since a particular number of positive cases was reached. 

Cohort Analysis Tableau LOD Step 2: Explore your Dataset

Check your data to see whether the metrics for analysis have been defined. 

In the Covid-19 example, this could be the number of cases. The dataset may provide two different types of cases, that is, Confirmed Cases and Deaths. 

In most datasets, the Cohort is not defined as a dimension and the date field may not fit the definition of a Cohort. In such cases, you should create calculated fields and use them to deal with this. 

Cohort Analysis Tableau LOD Step 3: Define the Cohorts

The following are the key parameters that should be considered when building a Cohort:

• The Attribute Defining the Cohort: For example, the number of days since the N^th case was reported. 
• Cohort Size: This is the time interval over which the Cohort should be defined. It can be the number of days. 
• Period: For the Covid-19 example used in this article, the Cohort behavior should be analyzed for as long as the pandemic lasts. 
• Key Metrics for Analysis: You should determine the key metrics that are to be analyzed. For example, the number of Covid-19 reported cases and the deaths it has caused. 

Cohort Analysis Tableau LOD Step 4: Define the Start Point

A parameter that will define the starting number of cases should be created. This number will simply limit the start date point of the analysis as a threshold. 

In Tableau Desktop, go to a sheet and right-click inside the Parameters area, and then choose “Create Parameter”. Type a name for the parameter and choose its data type, enter its current value, and the range of values, and click the OK button. In this case, the parameter has been given the name “Start with N Cases”. 

Cohort Analysis Tableau LOD Step 5: Create Calculated Fields

In this case, a calculated field to determine the daily number of confirmed cases for each country using LOD expressions for the specified dimensions can be created. LOD calculations are very useful when running queries that involve many dimensions at the Data Source Level.

You can use the following expression to calculate values for this field:

{ FIXED [Country_Region],[Date] : sum([Total Confirmed]) }

The above field has been named “Case by date”. 

You can also create another calculated field that determines the date on which the total number of cases in a country exceeded the threshold defined by the parameter that was created in Step 4. 

You can use the following LOD expression:

if [Cases by date]>=[Start with N cases] then [Date] end

This field will be named “Date with minimum cases”. 

Let’s also create another LOD expression that determines the starting date per country:

{ FIXED [Country_Region]: MIN([Date with minimum cases])}  

Let’s also calculate the relative day of the metrics in the analysis within the Cohort. In this example, this should be the number of days that passed with respect to the date on which the first case of the virus was reported in the country. 

The following LOD expression can be used to perform this calculation:

DATEDIFF('day',[Start Date],TODAY())
-
DATEDIFF('day',if [Date] >= [Start Date] then [Date] end,TODAY())

Limitations of Performing Cohort Analysis Using Tableau LOD Expressions

The following are the limitations of performing Cohort Analysis using Tableau LOD expressions:

• LOD expressions are complex to write and they require programming knowledge. 
• When blending data, you have to ensure that the linking field from your Primary Data Source is available in the view before using the LOD expression from your Secondary Data Source.

Conclusion

This article provided you with 5 easy steps using which you can perform Cohort Analysis using Tableau LOD expression. Most businesses now perform analysis on data that is coming from multiple sources. Even though Tableau provides an option to connect additional data sources, it can be considered a good practice to integrate all data sources first and then perform an analysis of that data.

Since Data masking is an important aspect of protecting data, Having a solution that provides a secure ETL and data pipeline is important and that is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 150+ pre-built Integrations that you can choose from.

visit our website to explore hevo[/hevoButton]

Hevo can help you Integrate your data from numerous sources and load them into a destination to Analyze real-time data with a BI tool such as Tableau. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

SIGN UP for a 14-day free trial and see the difference!

Share your experience of learning about Cohort Analysis Tableau LOD in the comments section below.