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. You can use column details to get a high level overview of how your data is spread out. That can help you choose your initial cohorts. We are going to divide our customers into three cohorts: One week, One Month, Over a Month.
- Translate your categories into numbers that correspond to your data. Using the number of days between sales, we have three ranges that define our cohorts.
One Week: <=7
One Month: <=30
Over a Month: 30<
- 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([First to Second Order], 8, 31). This creates three bins: Bin 1 is everything less than 8, Bin 2 is the number 8 and everything between 8 and 31, Bin 3 is everything 31 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. You can spot check in the data, and use Column Details to check how the bins are distributed.
- 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], “One Week”, “One Month”, “Over a Month”). 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!