Perform a Cohort Analysis

In this example, you divide customers into cohorts based on the time between the first and second order. This builds on the the Days Between the First and a Second Order tutorial, so do that tutorial first. 

Begin with a list of customers, day of first order, and days to second order, and end with the breakdown of cohorts shown below. You can follow the same steps to create cohorts based on any numeric metric.

1. Determine the cohorts. This example divides the customers into three cohorts based on days between the first and second order: One Week, One Month, Over a Month.
   Tip: Click the column menu and select Column Details to get a high level overview of how your data is spread out. That can help you choose your initial cohorts. 
2. Translate your categories into numbers that correspond to your data. Using the number of days between sales, there are three ranges that define the cohorts.
   • One Week: <=7 days
   • One Month: <=30 days
   • Over a Month: >30 days
3. Create the three ranges or "bins" using the BinRange function. BinRange assigns data to user-defined bins. Each number entered in the BinRange function represents the inclusive lower limit of the bin.
   • Create a column and name it [Cohort Bins].
   • Use the formula:

     BinRange([First to Second Order], 8, 31)

     Sigma creates three bins:
     • Bin 1 is everything less than 8 (<=7 days).
     • Bin 2 is the number 8 and everything between 8 and 31 (<=30 days).
     • Bin 3 is everything 31 and greater (>30 days).
       The outputs are the numbers 1, 2, and 3.
4. This is a good time to check that the cohorts are sorted the way you expect. You can spot check in the data, and use Column Details to check how the bins are distributed.
5. (Optional) You can label the 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. Use the column [Cohort Bins] as the input values, and provide a list of names that correspond to the bins.
   • Create a new column named [Time to Sale].
   • Use the formula:

     Choose([Cohort Bins], “One Week”, “One Month”, “Over a Month”)

6. Check again to make sure the names of your bins are lining up to your cohorts correctly.
7. Group the table to see the cohort analysis. To see stats for the cohorts by month, group by month, and then by the cohorts:
   • First 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 the new column.
   • Create a Group By with [Cohort Bins] and [Time to Sale] as the keys.
8. Count the number of customers in each cohort for each month:
   • Create a new column called [Count].
   • Use the formula:

     Count()

9. Get the percentage in each cohort each month by calculating 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].
     • Use the formula:

       Count()

   • Create a new column under the cohort level named [% Cohort].
   • Use the formula:

     [Count] / [Total Monthly Customers]

   • Use the column menu to change the format to Percent.
10. After you assigned the cohorts, you can group the data in several ways to get different views of the cohorts. You can assign several cohorts to the data, and try different analysis to reveal different trends in the data.