Want Better Deal Analysis? 9 Sales Reports To Run in Your CRM

Introduction

All of the metrics below should be reported on both the macro and micro level (i.e. view as a total and then by rep, by quarter, by product or service, and for won, lost, and open deals as needed). 

These are all sales reports that you should be able to set up in your CRM, which I highly recommend you do. Based on your system, create these reports, group them into multiple dashboards, and if possible, set up automated emails to send them out to key stakeholders on a monthly basis. We will go through a dashboard example in detail at the very end.

How This Works:

Down below I explain why each sales report is important. I then show the report (see example immediately below), then lay out the rows, the columns, the values, and any sort of filter that needs to be set up in each one.

	Column 1	Column 2	Column 3
Row 1	data	data	data
Row 2	data	data	data
Row 3	data	data	data

Table of Contents

1. Total Contract Value (TCV), License Fees, Professional Services (PS), and Annual Recurring Revenue (ARR) – any currency metric

This sales report shows you the total amounts for each of your deals metrics by year. This enables you to see how your team as a whole has progressed year over year for each deal metric.

	Count of Deals	TCV	License Fees	PS	ARR
2023	20	$1,000,000	$300,000	$100,000	$600,000
2022	18	$810,000	$261,000	$90,000	$459,000
2021	12	$480,000	$144,000	$57,600	$278,400

Rows: Close Date (by Year)

Columns:  Deal Name (Count of Deals), Total Contract Value (TCV), License Fees, Professional Services (PS), Annual Recurring Revenue (ARR)

Values: Set all of the data to total amounts except Deal Name which is a count

Filters: All Open Deals, No Renewals, No Statements of Work (i.e. CapEx and Opex deals only)

2. Average TCV, License Fees, Professional Services, and ARR – any currency metric

This sales report is the exact same as the previous one, except that here we look at average amounts instead of totals. This enables you to see the average deal amounts and how those averages have progressed year over year.

	Count of Deals	Avg TCV	Avg License Fees	Avg PS	Avg ARR
2023	20	$50,000	$15,000	$5,000	$20,000
2022	18	$45,000	$14,500	$5,000	$18,000
2021	12	$40,000	$12,000	$4,800	$15,000

Rows: Close Date (by Year)

Columns:  Deal Name (Count of Deals), Total Contract Value, License Fees, Professional Services, ARR

Values: Set all of the data to average amounts except Deal Name which is a total count

Filters: All Open Deals, No Renewals, No Statements of Work (i.e. CapEx and Opex deals only)

3. Number of Products or Services Sold

This sales report enables you to see the number of products sold each year. It’s helpful to know how well each of your products and services sell, and whether the amounts trends upward or downward year over year. 

	Product 1	Product 2	Product 3
2023	11	6	3
2022	13	4	1
2021	10	2	0

Rows: Close Date (by Year)

Columns:  Products or Services

Values: Set all of the data to count

Filters: All Won Deals, No Renewals, No Statements of Work (i.e. CapEx and Opex deals only)

4. Won vs. Lost vs. Qualified Out Deals

This sales report enables you to see the number of closed deals each year. When you distinguish between “lost” and “qualified out,” you can properly analyze and learn from failures and allocate resources more efficiently in the future towards opportunities with real promise. Furthermore, when you compare those values vs. what is won, you get a picture of how effective your reps are at deal qualification. Check out our post, 15 Essential Deal Fields You Need in a B2B CRM, for a full in-depth breakdown on this topic.

Don’t forget to create a second report with the total amounts (e.g. Total Contract Value, ARR, etc.) so you can see the value of what was won, lost, and qualified out each year.

	Won	Lost	Qualified Out
2023	20	6	15
2022	18	6	10
2021	12	10	5

Rows: Close Date (by Year)

Columns:  Deal Stage

Values: Set all of the data to count

Filters: Won, Lost, and Qualified Out Deals; No Renewals, No Statements of Work (i.e. CapEx and Opex deals only)

5. Churn Rate

Here we look at the number of renewals that opted to continue vs. those that did not on a year over year basis. When we measure the churn rate, it helps us to understand how many of our customers are satisfied with our offering.

Don’t forget to create a second report with the total amounts (e.g. Total Contract Value, ARR, etc.) so you can see the value of what renewed and churned. It’s vital to know these amounts in order for your team to understand ARR and cash flow for the upcoming period. 

	Won	Lost/Churn
2023	10	3
2022	7	2
2021	5	1

Rows: Close Date (by Year)

Columns:  Deal Stage

Values: Set all of the data to count

Filters: Won and Lost Deals, Renewals Only

6. Deal Creation to Close Time

In this sales report, we look at the summary of all won deals by year and how long they stayed in each stage on average. This data helps us to understand how long the sales team as a whole spends, on average, at each deal stage. We can assess this data by rep, product, service, CapEx vs. Opex, won vs. lost vs. qualified out deals, and more. We should use this data to make improvements to our sales efficiency.

Let’s assume that Stage 4 is the contract phase. In the chart below, we can see that the longest amount of time spent among all stages is at stage 4, for all 3 years, and has not improved. From this data, we know that we should talk with the team to evaluate what occurs at the contract phase that causes it to take more time than any other stage. Perhaps the answer is related to the software the team uses, maybe there is too much bureaucracy in the organization that can be eliminated, or perhaps it’s because contracts are given to customers too soon, before they are ready to purchase. Whatever the reason may be, assess and discuss with your team.

	Avg Stage 1	Avg Stage 2	Avg Stage 3	Avg Stage 4
2023	6 days	12 days	20 days	44 days
2022	8 days	15 days	25 days	45 days
2021	7 days	18 days	32 days	44 days

Rows: Close Date (by Year)

Columns:  Deal Stage

Values: Set the Open Duration field (see special note below) to average amount

Filters: All Won Deals, No Renewals, No Statements of Work (i.e. CapEx and Opex deals only)

Special Note: Your CRM may automatically calculate the time a deal spends at each stage before it closes. If not, you may need to skip this one for now. We have an entire post on how to create deal stage duration in your CRM set for the future. Subscribe and stay tuned.

7. Average Term Length

This sales report enables you to see the average term length (i.e. contract length) of the deals you won each year by product. This is helpful to know in the event that you seek to close long term deals. Customers on long term deals mean more revenue stability, reduced churn, and reduced sales costs from not having to renegotiate contracts each year.

	Product 1	Product 2	Product 3
2023	2.2 Years	1.8 Years	2 Years
2022	1.9 Years	1.8 Years	1.2 Years
2021	1 Year	1.2 Years	1 Year

Rows: Close Date (by Year)

Columns:  Products or Services

Values: Set the Term Length data to average amounts

Filters: All Won Deals, No Renewals, No Statements of Work (i.e. CapEx and Opex deals only)

8. New Deals vs. Upsells (for numbers 1-7 above)

This sales report can be set up in a ton of different ways. It all depends on the data that you want to analyze. When you assess this data, a key point to look out for is how your deal values change year over year for new deals and upsells. 

I would recommend that you create additional reports; replace the years in the rows for the reps, replace deal amount for deal count, and filter by a given year for won deals only. This helps you to see how your team performs on new deals against upsells on a rep by rep basis.

You may find that some reps close a ton of upsells, but few-to-no new deals. With this knowledge, you could talk with your team to understand why that’s the case. It may be that one rep has a saturated market where there are few new customers to be attained. Accordingly, that may be why they only close upsells.

Perhaps in this scenario, it would be best to expand this person’s territory. Before we make a correction, we need to find out what’s wrong or what’s not fully optimized. Good quality data in your system and proper reports are the first steps to ameliorate these issues.

	New Deals – Avg. ARR	Upsells – Avg. ARR
2023	$22,000	$16,000
2022	$20,000	$15,000
2021	$17,000	$12,000

Rows: Close Date (by Year)

Columns:  New Deals and Upsells

Values: Total Contract Value, ARR, PS, License Fees, Product, Deal Stage, or any other field where you want to compare how you performed on New Deals vs. Upsells. Set all of the data to count, total amount, or average; it depends on what you want to analyze.

Filters: This depends on what you want to see. Perhaps you want to filter on Deal Stage, Product, Service, etc. The only field to definitely filter on is CapEx and Opex only; no renewals or statements of work.

9. CapEx vs. Opex (for numbers 1-7 above)

Much like the New Deals vs. Upsells report, this too can be set up in a variety of different ways. The setup depends on the data that you want to analyze. A key point to look out for when you assess CapEx vs. Opex opportunities is how your deal values change year over year. This information can help you to understand which deal types contribute most to your business, the value proposition customers are most interested in, and it can help you to plan strategically for the future based on customer preferences.

Also similar to the New Deals vs. Upsells report, you can replace the years in the rows for the reps, replace ARR for deal count, and filter by a given year for won deals only. You may find that some reps close a ton of CapEx deals, but no Opex deals.

Look back to the 15 Essential Deal Fields You Need in a B2B CRM article if you need a recap on why we track CapEx vs. Opex deals.

	CapEx – Avg. ARR	Opex – Avg. ARR
2023	$15,000	$25,000
2022	$14,000	$23,000
2021	$12,000	$20,000

Rows: Close Date (by Year)

Columns:  CapEx and Opex for Total Contract Value, ARR, PS, Product, Deal Stage, or any other field where you want to compare how you performed

Values: Set all of the data to count, total amount, or average; it depends on what you want to analyze.

Filters: This depends on what you want to see. Perhaps you want to filter on Deal Stage, Product, Service, New Deals, Upsells, etc. The only field to definitely filter on is CapEx and Opex only; no renewals or statements of work.

Bonus: Dashboards

We just went through 9 different types of sales reports, and as you probably gleaned, there are a large number of ways to set up each one; it all depends on the level of detail that you want to analyze as related to the goal you aim to achieve. 

In number 8 above, New Deals vs. Upsells, we saw how there were a ton of ways to look at data; these alone could be a dashboard. Think about the data that you and your team want to look at, and create groupings of reports (i.e. dashboards) that makes sense for your organization. In most CRMs, you can create more than one dashboard; be sure to do so.

Below is an example of 1 dashboard that contains 6 reports:

1. Total contract value by year by rep for CapEx & Opex deals only
2. ARR by year by rep for CapEx & Opex deals only
3. Professional Services by year by rep for CapEx & Opex deals only
4. Total number of deals won by year by rep for CapEx & Opex deals only
5. Total number of deals lost by year by rep for CapEx & Opex deals only
6. Total number of deals qualified out by year by rep for CapEx & Opex deals only

It may sound like a daunting task to create all of these sales reports given the permutations of reps, years, products, services, won deals, lost deals, open deals, new deals, upsells, CapEx, Opex, and more. However, you don’t need to create 300 reports today. 

Every company captures and places a level of importance on different metrics. Talk with your team. See what they need access to. One report is better than none. Start simple. Don’t inundate your team on day 1 with 15 dashboards that contain 10 reports each; it’s too much. Start with one dashboard and go from there. You will open some eyes with this information. 

Wrap Up

Remember that all of the sales reports listed above are year over year results, not the deal by deal results. Update reports so you can look at them by deal where needed. Be sure to add the close date to the filter, otherwise you end up with data for all years.