How to do a Customer Cohort Analysis: Time to 2nd Sale Example

What Is a Cohort Analysis?

A cohort analysis is an analytical technique that focuses on analyzing the behavior of a subset of customers that share common behaviors –– referred to as a cohort –– over time. Businesses use cohort analyses to identify the highest or lowest-performing customer cohorts and uncover insights about improving them over time.

An example of a cohort analysis

Imagine you are an ecommerce merchandiser. A returning customer saw an ad for a flash sale on cosmetics, and after browsing through your wares, purchased a 3-step skincare package.

After observing this behavior, you ask, “Are similar customers returning as a result of the flash sale? And if so, how fast would they come back, and what value do they present? Fortunately, you can track this cohort of customers’ behavior to see if they return and make a purchase and how frequent those returns are.

After about six months, you sit back down and look at all the customers that visited your site from that specific promotion and bought a 3-step skincare package. You found that 70% of the people who purchased the trial set never came back (bummer!), while 20% came back to your store at least once but didn’t buy anything. The remaining 10% bought something in these four months.

From here, you could begin to form and test hypotheses into why this happened and how to fix it. We recommend teams looking to identify their highest-value targets start their cohort analysis by grouping users according to the metrics they identified in the previous step — e.g., by acquisition cost, lifetime value, etc.

How To Do a Customer Cohort Analysis in Sigma

What you’ll need:

A BI and analytics solution like Sigma

Customer cohort data, including but not limited to:

• Time of first order
• Time of the second sale
• Customer acquisition cost (CAC)
• Customer lifetime value (LTV)
• Basket size
• Average order value (AOV)
• Net profit margin
• % of product returned

How to do a cohort analysis in Sigma

Cohort analyses typically require several steps, technical skills, and extensive coding in other BI tools. In traditional spreadsheets like Excel, for example, the process is very involved. But in Sigma, you can perform a customer cohort analysis in seconds on millions or billions of records.

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.

Step 1: Determine your customer cohorts.

Here, we’ll divide customers into three cohorts: sale in week 1, sale in week 2, and sale in week 3+ like in the screenshot below.

Step 2: Translate your categories into numbers that correspond to your cohort data.

In this example, we have three ranges that define our customer cohorts. We determine this by using the number of days between sales:

• Sales in week 1: 1-7 days
• Sales in week 2: 8-14 days
• Salse in week 3+: 15 days and beyond

Step 3: 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.

Create a column called ‘Cohort Bins’ and use the function BinRange([Time to 2nd Order], 8, 15). This creates three bins: Bin 1 is everything less than 8, Bin 2 is 8 to 14, Bin 3 is everything 15 and greater. The outputs will be the number 1, 2, or 3; this is an excellent time to check that the cohorts are bucked and binned correctly.

Step 4: 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 corresponding to our bins.

Step 5: Create a new column named ‘Time to Sale’ and use the formula Choose([Cohort Bins], “Week 1”, “Week 2”, “Week 3+)

Tip: Check again to make sure your bins’ names are lining up to your cohorts correctly.

Step 6: Now that you have your bins, you can group your worksheet to see your cohort analysis.

But first, let's add a couple of levels/groups; one for Month of First Order, and another for Cohort Bin. If you need any help with that, see our resource on Using Levels and Groups.

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 each cohort’s percentage each month, we need to calculate each cohort’s number 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 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 analyses to reveal different data trends. For example, in this video, a “new customers” cohort is isolated to determine the highest lifetime value customers, the year of their first purchase, and the percentage they contribute to total sales.

Identify customer cohorts in seconds with Sigma

Cohort analysis is a powerful tool that can organizations can use to learn more about customer retention, engagement, and loyalty. But accuracy and insightfulness of the results depend primarily on the tool teams use to conduct the analysis. For best results, use a modern, cloud-native analytics solution that can leverage the power of the cloud to enable you to crunch high volumes of data across dozens of sources quickly.

Let’s Sigma together! Schedule a demo today.