In this example, we’ll divide customers into cohorts based on the time to second sale. We’ll start with a list of customers, time of first order, and time to second sale, and we’ll end with the breakdown of cohorts shown below. You can follow the same steps to create cohorts based on any numeric metric.
- Determine your cohorts. We’ll divide customers into 3 cohorts: sale in Year 1, sale in Year 2, sale in Year 3+.
- Translate your categories into numbers that correspond to your data. Using the number of days between sales, we have 3 ranges that define our cohorts.
Sale in Year 1: <=365
Sale in Year 2: 366-730
Sale in Year 3+: 731=< - Create your bins using the BinRange function. BinRange assigns data to user-defined bins. Each number in the BinRange function represents the inclusive lower limit of the bin. Read more about using BinRange to assign your data to categories.
Create a column called ‘Cohort Bins’ and use the function BinRange([Time to 2nd Order], 366, 731). This creates 3 bins: Bin 1 is everything less than 366, Bin 2 is the number 366 and everything between 366 and 731, Bin 3 is everything 731 and greater. The outputs will be the number 1, 2 or 3; this is a good time to check that the cohorts are being sorted the way you expect. - If you like, you can now name your cohort bins using the Choose function. This helps keep track of what the bin numbers mean. With Choose, the value returned depends on the number inputted. When the input value is 1, Choose returns the first provided value, and so on. We will use the column ‘Cohort Bins’ as the input values, and provide a list of names that correspond to our bins.
- Create a new column named ‘Time to Sale’ and use the formula Choose([Cohort Bins], “Year 1”, “Year 2”, “Year 3+”). Check again to make sure the names of your bins are lining up to your cohorts correctly.
- Now that you have your bins, you can group your worksheet to see your cohort analysis. To see stats for your cohorts by month, group by month, and then by your cohorts. Use the column menu to select Truncate Date, then Month to create a column of dates that only includes the month and year data. Create a Group By with your new column. Next, create a Group By with [Cohort Bins] and [Time to Sale] as the keys.
- Let’s perform some cohort analysis! To count the number of customers in each cohort for each month, create a new column called ‘Count’ and use the formula Count().
To see the percentage in each cohort each month, we need to calculate the number in each cohort divided by the total customers. To find the Total number of customers each month, create a new column on the month level named ‘Total Monthly Customers’ and enter the formula Count(). Create a new column under the cohort level called ‘% Cohort’ and enter in the formula [Count] / [Total Monthly Customers]. Use the column menu, and change the format to Percent.
Once you have your cohorts assigned, you can group your data in several ways to get different views of your cohorts. You can assign several cohorts to the data, and try different analysis to reveal different trends in the data. The possibilities are unlimited!