Announcing the 2022 Movers of Strategic Finance!
Meet the Movers
Financial Insights

4 Pitfalls of Doing Your Cohort Analysis in Excel

Cohort analysis is key to making decisions that will have big impacts on your business. While doing your cohort analysis in Excel works, here’s why spreadsheets aren’t the best tool for them.

Joe Michalowski

Content Strategist

Published on June 24, 2021
Financial Insights
4 Pitfalls of Doing Your Cohort Analysis in Excel

Cohort analysis is key to making decisions that will have big impacts on your business. While doing your cohort analysis in Excel works, here’s why spreadsheets aren’t the best tool for them.

A cohort analysis—analyzing the behavior of your customers based on their similarities within a given timeframe—is a powerful way to understand net revenue retention, growth, and customer lifetime value (LTV).

But if you’re defaulting to cohort analysis in Excel or Sheets, you may be losing countless hours on data entry, formula configuration, and data manipulation that could be better spent on growth activities like figuring out how to lower your customer acquisition cost (CAC) or optimizing your sales ramp.

Instead of relying on spreadsheets for your customer cohort analysis, shift to a strategic finance platform to automate the tedious steps so you can focus on unlocking key insights about your customer base.

The Pitfalls of Spreadsheets for Cohort Analysis

Spreadsheets have always gotten the job done for finance teams conducting cohort analysis. But just because you can set up your cohorts in Excel, doesn’t mean you should (or should have to).

The modern finance team has to be agile—capable of providing answers to strategic questions quickly and keeping pace with rapid business change. Complex, error-prone spreadsheets are no longer the best tools for the job.

They’re Prone to Manual Error

Creating a cohort analysis in a spreadsheet means handling large amounts of data points from numerous data sources. You need to pull contract start dates as well as retention and renewal data from your CRM and combine all that with revenue and collection data from your ERP. And if you’re building out your cohort analysis for a board deck or executive review, you’ll need to go through this manual data-entry process at least quarterly.

The sheer amount of manual work involved in this process creates typos, misaligned rows, copy/paste mistakes, missed cells, and other errors. There are plenty of spreadsheet horror stories from simple data entry mistakes—like when a missing minus led Fidelity to report a $1.3 billion gain to shareholders instead of a loss.

But errors in manual data entry don’t just cause reporting mistakes. Inaccurate data in your cohort analysis could lead you to double down on certain sales and marketing strategies that seemed to be working, but actually weren’t. And that kind of strategic misguidance could hurt your company’s growth potential.

They Take Hours to Set Up (At Best)

Pulling data from your business systems is only the first step to cohort analysis in Excel. Once you have all the raw data in your spreadsheet, you still have to spend hours cleaning that data before even starting on the time-consuming process of building visualizations.

Beyond data collection and cleansing, grouping cohorts is one of the most time-intensive aspects of spreadsheet-based analysis. Are you doing time-based cohorts? Segment-based cohorts for specific product lines or service levels? Size-based cohorts to analyze the breakdown of your customer segments? Customizing your cohort analysis spreadsheet with these groupings can be tedious. And if you’re creating multiple views of your cohort analysis to unlock more strategic insights about the customer base, you’ll need to go through the configuration process multiple times.

A cohort analysis is only as valuable as the strategic insights you can get from it. If you’re spending all your time setting up a spreadsheet, your insights will be stale by the time they make it to your business partners.

They Make Collaboration Difficult

Once you’ve created your spreadsheet-based cohort analysis, using it to collaborate with other colleagues, company leadership, or stakeholders isn’t easy.

It’s great that you’ve taken the time to scrub all your data and build your cohort view. But the real value of the exercise lies in analyzing the trends and helping your business partners understand why one cohort is outperforming or underperforming the rest. Spreadsheets make it almost impossible to aggregate data and drill down into it in a way that highlights these kinds of insights.

Instead, spreadsheet-based cohort analyses can feel like a complex mix of financial data that doesn’t mean much to people outside of finance. A collaborative finance function speaks the same language as the business, which is why a more flexible solution that uncovers the “why” behind your numbers is so valuable.

They Can be Biased

The amount of time it takes to pull data and build cohort groupings opens the door for bias in your analysis. A basic time-based cohort analysis may be objective, showing quarterly revenue changes based on customer start date. But bias comes in when you start to further segment the data and dig deeper.

Because spreadsheet-based cohort analysis takes so much time to set up, you have to may have to limit your groupings and segments for the sake of speed. Selection and personal biases in those choices can hurt the objectivity of your analysis and limit the value of strategic insights. However, automating the data collection and grouping processes ensures you’re working from an objective baseline of customer information. Having that baseline will improve the quality of your strategic guidance—even if the insights are more subjective as you dig deep into the story behind your numbers.

How to Simplify Cohort Analysis with a Strategic Finance Platform

Mosaic is a Strategic Finance Platform that uses automation and accessible financial business intelligence to make your team more agile in planning and analysis.

The modern finance team doesn’t just report on the numbers—it tells the story behind the numbers to help guide strategic decision-making in the business. Ideally, cohort analysis should give business leaders (not just in finance) an easy way to understand how different strategic decisions have impacted upsells, net dollar retention, and usage over time. But the pitfalls of spreadsheet-based cohort analysis make it impossible to reach that ideal state.

Moving financial analytics out of Excel and into Mosaic eliminates the traditional pitfalls, simplifying your cohort analysis and helping you focus more on bringing strategic value to your business.

Automate Data Integration from Core Business Systems

Mosaic eliminates human error in your cohort analysis by automating data integration from your core business systems. Integrations with your CRM, ERP, HRIS, and billing systems ensure all necessary financial data remains updated in real time.

Real-time actuals give you a richer view of your customers for cohort analysis while ensuring you don’t have to worry about inaccuracies due to formatting or copy/paste errors in spreadsheets. The integrated data layer is your foundation for more dynamic cohort analyses that bring strategic value to the business.

Set Up Cohort Analysis Tables and Graphs in a Few Clicks

Mosaic’s financial metrics catalog gives you on-demand access to the KPIs that matter most to your business—including a variety of automated views of your customer cohorts. Below, you can see four different ways to view cohorts in Mosaic, which you can generate with just a couple of clicks.

Customer cohorts by average ARR in Mosaic

Customer cohorts by average ARR in Mosaic

The four baseline views for cohort analysis in Mosaic are:

  • Customer cohorts by average ARR
  • Customer cohorts by net dollar retention percentage
  • Customer cohort percent retention by ARR
  • Customer cohorts by average MRR

But once you have these baseline cohort views, you can start manipulating the data and dig deeper for strategic insights.

Get Easy Access to Granular Cohort Data

Mosaic makes it easy to get more granular in your cohort analysis by adding more attributes to the baseline views listed above.

In any of the baseline views, you can add attributes like account name, industry, product line, support tier, and number of employees. Below, you can see a cohort analysis of net dollar retention broken out at the account level:

Customer cohorts by net dollar retention, broken out at the account level in Mosaic

Customer cohorts by net dollar retention, broken out at the account level

This kind of account granularity is where you can start investigating what’s going right or wrong in terms of customer retention, upsell strategies, and overall business growth. And it’s what helps you start building trust with business partners as you provide more actionable insights instead of complex financial data.

Quickly Build Visualizations for Better Cross-Functional Collaboration

Collaboration is easy with Mosaic because it enables you to build visualizations quickly and easily share access with business partners and board members. The automated platform eliminates version control issues with spreadsheets while also giving people outside of finance a more approachable environment for working with financial data.

Any of the cohort analysis tables shown above can quickly convert into graphs that are most impactful for your business. The net dollar retention chart easily becomes an area chart broken down by product line:

Customer cohorts by net dollar retention in an area graph

Customer cohorts by net dollar retention in an area graph

You no longer have to provide your cohort analysis findings to your leadership team using spreadsheets, PDF files, or presentation decks during regular performance meetings. You can log into your Mosaic canvas and walk them through your newest updates together and manipulate data to answer questions on the spot. You can even have members of your leadership team create their own customized dashboards so they can see the cohort analysis data they need to track most as soon as they log in.

Make Cohort Analysis Work Better for your Business by Choosing the Right Tools

The finance world might never replace spreadsheets, but that doesn’t mean Excel and Sheets are perfect tools. There’s plenty of room to automate certain aspects of reporting and analysis to make finance more agile—and cohort analysis is a perfect example.

Don’t waste time configuring cohorts and visualizing data in complex spreadsheets. With our Strategic Finance Platform, you can work faster and more seamlessly with more reliable information empowering you to make the best choices for your business.‍

Request a demo today to see for yourself how easy cohort analysis can be.

The latest Mosaic Insights, straight to your inbox

business decisions

Request a demo
Watch video