Another very popular analysis is cohort analysis, which is used to research the behavior of a group of people over time. In MicroStrategy Desktop, we can perform cohort analysis in order to study different groupings to understand customers' behavior, cause and effect relationships, and so on. In business, cohort analysis is very popular for exploring customer retention over time. Usually, a cohort is defined by the date of first purchase or registration; sometimes we can use demographic attributes. Using cohort analysis we can simply track customer engagement.
Our dataset has the data for first purchases. We can build cohorts by grouping people by the year and month of their first purchase and then see how they perform.
Before we start we should build some derived elements in order to get the months between the first order and all other orders:
- The date of the first purchase has a string format. We should convert it to the data. Right-click on any attribute and choose Create Attribute.
- Use this function in order to convert the string data to the real data format:
ToDateTime([Datefirstpurchase@ID])
Save this attribute as Date First Order.
- Create a new attribute
MonthsBetweenusing this syntax:MonthsBetween([[Date First Order]@ID], [Orderdate@ID])
- Create a new attribute Year Month Order:
Concat(Year(Orderdate@ID), Month(Orderdate@ID))
- Drag and drop Months Between to the rows, Year Month Order to the columns, and Orderquantity to the metrics.
- Change Year Month Order to descending order.
As a result, we have built a cohort table:
We can easily visualize it. Moreover, we can use shortcut metrics in order to calculate cohorts in percentages.