The manual (but also free) sales diagnosis

Gain valuable insights even if you don't have a lot of sales data

Rutger van der Pol
Rutger van der Pol
Co-founder The Sales Strategist

This Diagnostic Sales Report template is the second level of a set of three, each with different amounts of required data. We have developed this method so you can easily perform the right analysis that fits the amount of data you have from your sales process. Please refer to the main article for a clear overview of all three levels and choose which one suits your organisation best.

In this guide

About this second level of diagnostic sales analysis

What makes this dashboard great, in comparison to the first level: ‘the easiest sales breakdown’, is that we now measure a number of fixed reference points in the sales cycle. Without getting completely lost in all individual touchpoints, we look at just the most important milestones: first touch, lead, opportunity, customer, and ex-customer. By only measuring these finish lines, a very clear picture of the sales process emerges. While many companies tend to start by collecting far too much information on all the individual touchpoints, they actually find it hard to make that information actionable. This structure of multiple finish lines makes the data much more actionable, allowing useful insights to emerge sooner. By combining this knowledge with other data points, such as lead source channel and use case, we can then compare the different initiatives in your go-to-market: for example, which acquisition channel has the shortest sales cycle, and which subscription level generates the most successful or profitable customers?

By reducing the analysis to only this amount of data, we make it:

  • Easier to capture and gather all the data needed;
  • A much more reliable analysis;
  • More straightforward to get valuable insights.

Just like the first level, this analysis is based on a Google Sheets template that you can easily fill in, but where we now add more data points about the different phases and timings in the sales process, as well as more information that you gather about your (prospective) customers during the actual sales cycle. If you think you won’t be able to collect this data, read more about the first level of analysis.

This analysis is perfect for you if you are already collecting some information during your sales cycle, but do not yet have a very comprehensive suite of reporting and analysis tools. You can do this analysis with completely free tools (i.e. Google Sheets and Data Studio), but you will have to export the data from your CRM and prepare it manually for use in Data Studio. If you prefer to do this fully automated, read more about level three of this diagnostic sales analysis.

The Google Sheets template we created for this report

These data are needed for this analysis:

  • Who are your prospects, leads, opportunities, customers, and ex-customers;
  • Dates from your sales process on when these companies reached these stages;
  • Firmographics such as sector, revenue and employee range, and geography;
  • The Customer Value Score (read more about this below);
  • The role of the main buying contact (persona);
  • The main use case of your value proposition they are interested in;
  • What product/service you sell to them;
  • What you earn from them (in this template MRR, but can also be one-off or total revenue sum);

From this dashboard you can analyse and gain insight into:

  • Current status of leads, opportunities, and clients;
  • Basic revenue and churn overview;
  • How well your sales process is working: conversion journeys from first touch to customer;
  • A detailed overview of conversion rates and average time per phase of the sales cycle;
  • Sales insights about who you sell to;
  • Extensive insight into who you should sell to: your ideal customer profile.

To dig deeper into this analysis, you can segment all reports on:

  • Sector;
  • Customer status (prospect, lead, opportunity, customer, ex-customer);
  • Revenue range;
  • Employee range;
  • Customer value score;
  • Buyer role;
  • Geography;
  • Source;
  • Use case;
  • Product;
  • MRR.

In addition, clicking on any part of a graph filters all other graphs on the dashboard to that segment.

The components in this diagnostic sales analysis

The dashboard starts with a general diagnostics section. In this second level of diagnostic sales analysis, we collect more data on the various finish lines in the sales cycle and therefore we can make a much more comprehensive analysis of the process diagnostics than in the first most basic level. In addition to active leads and opportunities (those more than 30 days old are no longer counted as active), we see some basic financial data and churn data.

General diagnostics

In the next part, things get really interesting: we see important information about the sales process displayed in both a concise and a more detailed version. The concise version shows the average time and average conversion between the different phases of the sales process. The more detailed version also shows values like the numbers you generate and the drop-off rates in the various stages of the sales process. By using the filters on the dashboard, it is possible to see per characteristic where the bottlenecks are or where you need to focus your efforts.

Concise overview of the sales process
Deep dive of the sales process

The next page of this dashboard shows your sales insights. These are based on what your customers and ex-customers look like. This section explains in different characteristics how many customers there are, and how much you earn from them, whether they are mainly customers or ex-customers, and what products you sell to them. This is made visible for sector, employee range, buyer role, and geography, and can also be further specified by using the filters. By analysing this section, you can find out which typology of customers you win the most and also with which typology you are most successful.

Some of the graphs that show who you sell to

Whereas in the first level we could only look at who you sell to, in this second level we also have important information about how you sell. This includes information about lead source channels and the use cases that are doing well or are lacking behind. By using the filters at the top of the dashboard, or by clicking on any segment in any of the graphs, you can further filter the entire dashboard in terms of the characteristics you wish to examine.

Insights into how you sell

Customer value scoring

The last section in this sales breakdown allows you to analyse what your ideal customer profile looks like. This section uses a score that you have to assign to your customers yourself: the Customer Value Score, which you can read a short description of below and an extensive description in this article we wrote about it.

The Customer Value Score describes how well a company fits yours as a customer: how much effort does it cost to service and retain them, and how much potential does the account have (for further growth)? You can see this as an importance score for this account: how hard will you fight for this customer or otherwise how ok are you with this client churning. You score it on a scale from 1 to 10 based on the following components, and be honest about the answers:

  • Does this company fit the general profile you are looking for?
  • Is their current need (pain point) a good fit for your product/solution?
  • Is it a low-effort customer to maintain and service?
  • Are they bringing in high (enough) revenue (-potential)?

Because the Customer Value Score is an aggregation of various attributes, it adds a thick layer of knowledge to your analysis that you can cross-reference with other firmographic attributes of your customers. In combination with revenue generated, we use the Customer Value Score as an indicator to define the ideal customer profile.

The ideal customer profile can be explored in quadrants that show us which combinations of properties have above-average Customer Value Score and MRR. Furthermore, we can cross-reference this with the other data points in this analysis: you can refine this further by using the filters on the dashboard to zoom in on the different segments you discovered in the previous section.

A quadrant view that reveals ideal customer typologies

This second level of diagnostic sales analysis consists of three parts: a comprehensive overview of the sales process, sales insights on who and how you sell to, and an extensive section in which the ideal customer profile can be examined by cross-referencing MRR and the Customer Value Score against different characteristics of your customers.

Google Sheets and Data Studio templates