Main Content
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.
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:
Your marketing team can measure engagement, improve retention and secure revenue by isolating customers into defined groups.
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.
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.
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).
See how the decisions you make today will affect your bottom line tomorrow, with Vena.
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.
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:
Now, the number in each square represents the number of customers lost per period.
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.
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.
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.
Grow Your Career With Access to Free CPE/CPD Courses & Certifications.
Start LearningNadia Petkova is a Senior Director of Enterprise Information Management at Vena where she has a successful track record of delivering large-scale strategic projects and acting as a business partner. Nadia specializes in DevOps, agile teams, change management, portfolio management, customer loyalty, data integration, operational improvements, business intelligence and finance. She’s also an expert in driving operational improvements, streamlining processes and delivering results that positively impact the bottom line. Her focus on excellence in leadership and creating a high-functioning team culture motivates her colleagues to reach their full potential. In her free time, Nadia likes to stay up to date with emerging technology trends.
