Go beyond CRM dashboards and reports to leverage data that will enable better sales and marketing results.
There is tremendous value in a well configured CRM dashboard. There is no doubt that counting new leads, measuring revenue from won opportunities, and the attribution of ROI to campaigns are all good things to do.
But no matter how much time you spend creating charts or configuring a CRM dashboard your insights won’t be as robust as they should be.
In my experience, CRM reporting and dashboarding shortcomings come down to 2 main limitations: CRM dashboards and reports aren’t designed to display large amounts of data in a meaningful way and, aside from basic calculator-type functionality, CRMs aren’t designed to crunch numbers.
But software you’re already using, Microsoft Excel, is great at organizing data and crunching numbers! Even though exporting CRM data to Excel adds steps to the data review process, the insights generated are worth the effort.
Why Bother Analyzing CRM Opportunity Data?
An understanding of opportunity data can improve sales/marketing processes and results like
- Optimizing marketing spend by identifying the highest performing lead sources
- Having a baseline for how many “at-bats” you can expect and how many opportunities you will likely win in a given month
- Understanding the seasonality of your sales for more confident forecasting
I consider the analysis outlined below the absolute minimum amount of understanding required. Anything less and you can’t have an informed discussion about what you’re actually doing or relay the story that your data is telling. There really is no excuse to know any less than this about your sales and marketing data so let’s get started!
Download Sample Data or BYOD (Bring Your Own Data)
I’ve created an Excel file for analysis that has basic Closed Opportunity information you’d be exporting from your CRM, like Salesforce. You can download it here to follow along. The parameters for this report are Closed Opportunities, sorted by close month.
For simplicity’s sake, the sample data doesn’t contain fields like Sales Rep, Campaign, and Reason Won/Lost. If you’re BYOD, be sure you include those fields in your export so you can dig even deeper. If you’re following along, now is the time to open your data file.
Step 1: Scrub your Data
The data you’ve exported from your CRM is probably not optimized for analysis. You may need to simplify picklist items down to binary choices like closed won or closed lost. Full dates (MM/DD/YYYY) will probably need to be broken out into separate Month, Day, Year fields and so forth.
After figuring out how you want to simplify options, the Find and Replace function in Excel, CTRL + H, is the way to make bulk updates to your data.
It will be obvious if your data requires additional transforms because your outputs will either not make sense or you’ll hit a wall. Trust me, you’ll know if you need to keep scrubbing.
Step 2: Organize Your Data
Next, we need to organize our data. I like to use a combination of Pivot Tables and “insight tables.” Pivot Tables are a well-known Excel function and insight tables are things you create that allow you to fill in the blanks for key findings.
Pivot Tables
Pivot Tables allow you to organize and summarize a lot of data quickly. With the right mix of Filters, Columns, Rows, and Values we are moving from a “database” view to a straightforward summary view.
You can get all the help you need on Pivot Tables with a Google search but the basics are to highlight your data > Insert Pivot Table > Drag and Drop your fields between the Filters, Columns, Rows, and Values options until it shows you what you want to see.
Before. Raw data output.
After.
Nicely laid out in a Pivot Table
Step 3: Compiling Average, High, and Low Values
The next step is to create fill-in-the-blank insights tables. We’re going to feed our example with the below fields. If you BYOD to this example, use the same idea of Average/High/Low with your additional fields. As you’d imagine, there are some very interesting personnel-based insights you can generate by including sales rep or sales region.
- Average Sale Amount by Month and Year
- High/Low Sales Amount by Month
- Average Quantity Monthly/Yearly Opportunities
- Win Rate: by Month and Year
- Most/Least Effective Lead Source
- High/Low Speed to Close by Lead Source
The Fast Way to Populate Insight Tables
A combination of rearranging your pivot table values, using =GETPIVOT to fill cells and applying formulas to these cells will help you fill in this insight table quickly.
When using =GETPIVOT, be sure to Paste as Values so data in your insight table doesn’t change when your pivot table setup changes.
Populate Insight Tables with =COUNTIFS
If for some reason you don’t love Pivot Tables, you can also use =COUNTIF and =COUNTIFS to populate your insight tables. These formulas allow you to specify parameters like “closed won deals in the month of March from the website lead source.”
Even though populating each cell on the insight table with a formula is more time consuming, there are scenarios where it’s worth the effort.
Important Note: When calculating Average Sale Amount, Average Monthly Total Opp Value, or any other averaged value across multiple years be sure to divide by the number of years in your data set. This example uses only two years so the divisor in the example spreadsheet is 2. If your data set has five years, the divisor would be 5, etc.
Another Important Note: Avoid calculating the average of averages because it’s 1) lazy and 2) generally inaccurate. Only in scenarios where the number of units being compared across categories is the same will an average of averages be correct. Check this out if you’re interested in the mathematical proof.
Final Important Note: the averages we’re calculating are meant to be used as guideposts that help inform the narrative around your data. These averages will not be predictive tools you can use with high degrees of confidence.
Seasonality Trending
The final breakdown we’re going to cover is seasonality trending. Some industries have very strongly defined seasonality like retail and Black Friday. The point of understand the seasonality in your sales is to know when where your big months and letdown months happen.
This process is straightforward: select a cell in your pivot table > select PivotTable Analyze from the main ribbon > select PivotChart and you’re halfway done. Expect to manipulate the pivot table to display what you need. For instance, the chart will likely display all data on one line. The solution is to drag and drop your date field to “Columns” for individual groupings, like Year, to be displayed as separate and stacked lines.
Suggestions for Leveraging Data Insights for Better Sales and Marketing Results
Optimize Your Marketing Spend
By analyzing which lead sources produce the highest revenue, have the fastest sales cycle, etc you’re able to stratify your highest performing sources of deals. With your own data, you’ll want to calculate an ROI to go along with this comparative “best to worst” ranking to determine where to increase investment or where to refine your strategy.
Understanding Opportunity Flow
Having the averages from the insights tables to use as guideposts is undoubtedly an advantage. The ability to compare your actual vs. expected quantities of opportunities (total/to win/to lose) in a given month is useful for both sales forecasting and refining sales/marketing deal creation activity. Two examples here
- Forecasting: your sales team commits 7 deals to close this month out of a possible 14 deals. A month where you close 50% of available deals isn’t impossible but it does represent a near doubling of your historical 30% win-rate. Review the forecasted deals to be sure they are all in a position to close and refine the forecast from there.
- Lead Generation Activity: opposite the example above, in a month there are only 2 deals that are forecasted to close out of 6 available deals. To have 6 viable deals in a given month represents less than 50% of the expected quantity of deals. That’s a problem!
- Further, we know from our insights table that our average sales cycle is about 75 days. What happened (or didn’t happen) two to three months ago that caused the lack of viable deals for this month? Is it still happening? How can you ensure this trend doesn’t continue? The sooner you can take corrective action the better, obviously.
Wrap Up
The key takeaway here is that the methodology used scales easily, especially if you’re curious about the data. We’ve only skimmed the surface of what you can do with your opportunity data and, in broader terms, your CRM data. Regardless of how much data or how many fields you’re analyzing the outline of High/Low/Average or Best/Worst/Expected will continue to work for you. Your insights tables may become huge but you’ll manage!
Finally, please don’t treat averages as oracles. Averages are meant to provide a general idea of what might happen, not exactly what will happen. They are still useful when they’re used the right way but will fail you if you are “averaging” your way to sales projection.
I’d love to hear from you!
Please let me know what you think of this methodology by submitting a message via the contact form or leaving a comment below.
