How To Perform Cohort Analysis in Excel

Information drives business analytics. However, having too much information—or being unable to sort it productively—can be as ineffective as no information at all. That's where performing cohort analysis in Excel—categorizing and analyzing specific data—can help you gain valuable and actionable insights from your data.

Organizing information into defined categories allows you to draw more powerful insights into your customer base. This tool provides a mechanism to better understand and cater to a target audience. Compared to total impressions and sales numbers, cohort analysis evaluates defined characteristics of specific groups of customers.

So how can you run an impactful cohort analysis in Excel? Read on to discover how you can create an insightful cohort analysis quickly and easily in seven steps.

Key Takeaways

• Cohort analysis enables your BI team to analyze customer retention over time based on specified customer characteristics. By drilling into each "cohort" of your consumer base, you can predict future customer behavior. 
• There are several types of cohort analysis. Three of the most common are time-based, size-based and segment-based cohorts. 
• Some businesses experience more turnover than others. Understanding this turnover or churn can help you understand why customers leave and make preemptive changes to maintain the relationship. 
• Another important use of cohort analysis is to track customer engagement, whether you define "engagement" as website or app use, purchases, interaction with certain tools or others.

What Is Cohort Analysis?

Excel's built-in features enable comprehensive data analysis. One of those analytic tools is cohort analysis.

At its most basic, cohort analysis in Excel is the process of analyzing customers' behavior when categorized into defined and specific groups over time. With these defined categories, you can determine whether or how group characteristics influence customer behavior.

For example, you may define cohorts according to:

• Time: Group customers according to a certain time frame, such as those who subscribed to your email list each quarter.

• Size: Group customers based on their potential budget, such as freelancers versus large companies. This informs how much to spend on each targeted ad campaign.

• Segment: Group customers according to a specified segment, such as subscription type or some other "tier." A customer with a premium subscription has different needs from one with an entry-level plan. 

Your marketing team can measure engagement, improve retention and secure revenue by isolating customers into defined groups.

How To Perform Cohort Analysis in Excel

It's possible to complete cohort analysis with Excel alone. However, some marketing teams find that native software makes the analysis more effective.

Whether you use Excel or enhance it with software, take the following steps to build a cohort analysis in Excel.

1. Import Data

First, you need your data. 

You can either input customer information manually or import it from your customer database. Confirm the imported data has the characteristics you want to group your customers.

Source: Yemi Johnson

For example, if you're tracking subscription numbers each month, you'll want to ensure that you include that data.

2. Create Your Cohorts

At the end of your spreadsheet, create a column labeled "Cohort." You'll need a formula for categorizing the customer in that row according to the characteristic you've selected. Working with our subscription example, you could use a formula such as:

"date(YEAR(X), MONTH(X), 1)"

Once you press "Enter," Excel will extract the month and year from the customer's subscription date and replace them with "1." For example, "02/10/2022" and "2/23/22" would both become "02/01/2022." You'll need to format the cells by right-clicking and selecting "Custom." After you remove the "dd/" from the "Type" field, the date will reach "mm/yyyy." 

After you select "ok," be sure to copy the formula to the entire Cohort column. You can double-click the fill handle in the bottom-right section of the corner. This time, all dates will show only the month and date (e.g., 2/2022).

Agile Scenario Modeling Mitigates Risk

See how the decisions you make today will affect your bottom line tomorrow, with Vena.

3. Define the "Months" Column

Since we're tracking subscriptions across months, you need a "Months" column beside the "Cohort" column. The following formula will produce the result we need:

"IF(ISNUMBER(X), ROUND((X-Y)/30,0), "active")"

In this formula, "X" represents the date the customer subscribed and "Y" represents the date the customer unsubscribed, if at all. When the formula is input, a customer still subscribed will be shown as "active."

If the customer has unsubscribed, then the formula will determine the number of months they remained active.

4. Create a PivotTable

Now, we need a PivotTable. From the "Insert" tab, select "PivotTable." Determine whether you want the table to appear in the current worksheet or a new one and select "ok." Then, take the following steps:

• Move "months" from fields to the "columns" square. 
• Move "cohort" from the fields to the "rows" square. 
• Move "customer" to the "values" square.

Now, the number in each square represents the number of customers lost per period.

5. Create Your Retention Rates Sheet

By right-clicking on the PivotTable, select "copy." Create a new sheet titled "Retention Rates" in your workbook and past the PivotTable's contents. 

Now, select "Grand Total," then right-click to select "cut." Insert this data into a column after the "Cohort" column. Finally, copy the entire table and paste it anywhere below the current table.

6. Determine the Number of Customers Retained per Month

From the first table on top, select the first value following "Grand Total" and type "=." Reference the first value from the "Grand Total" column, enter a "-" and reference the corresponding cell from the second table. 

After you press "enter," move the fill handle to all cells in the row. Select all values from your first table and copy the formula to all cells.

7. Calculate Your Retention Rates

Now, copy the second table and paste it above the first table. In this new table, enter "=" beside the first cell to the right of the "Grand Total" column. Reference the corresponding cell in the second table. This time, type the "divide" symbol before referencing the cell immediately to the left. 

After pressing "enter," copy the formula to all cells in the row. Format the cells to percentages. At long last, you have retention data based on customer subscriptions.

Ready to start your own growth journey?