How to Create a Deal Scoring Report

A Deal Scoring Report helps teams evaluate the potential of deals based on a set of predefined criteria. By assigning scores to each deal, you can quickly prioritize high-value opportunities and focus efforts on the most promising ones. This report is particularly useful for sales and management teams to align on which deals to target and monitor, ultimately increasing efficiency and deal conversion rates.

Visualization:

Steps:

Define Deal Score Criteria: Before building the report, establish scoring criteria for deals based on key indicators. For example, this report uses four factors to score deals:

Location = US: 25pts
Number of employees > 5000: 25pts (fewer points for lower employee counts)
Revenue > 100M: 25 pts (fewer points for lower revenues)
EBITDA > $5.00/share: 25 pts (fewer points for lower EBITDA per share)


From the Explore menu, select Pipeline Deals.
In the Field Picker pane under the Deal view, select the following fields:
Deal Title
Country Code
Size ($ m)
Number of Employees
EBITDA ($ m)
Click on Run button at the top right to generate the data.
In the Field Picker section, go to Custom Fields → Click on Add → choose Table Calculation. Add the following calculations for each scoring criterion:



Repeat step 5 to create the following four table calculations:

1. Country Score:

Note: The result will be different for each organization, therefore type "Country" in the Expression box to search for the correct formulation.

if(
  ${dashboard_deal.country_code} = "USA",
  25,
  0
)

For the Format dropdown, select Decimals. Then, from the Decimals dropdown, select "1".

2. Employee Count Score:

Note: The result will be different for each organization, therefore type "Number of Employees" in the Expression box to search for the correct formulation.

if(
  ${dashboard_deal.number_of_employees} > 5000,
  25,
  ${dashboard_deal.number_of_employees} * 25/5000
)

For the Format dropdown, select Decimals. Then, from the Decimals dropdown, select "1".

3. Revenue Score:

Note: The result will be different for each organization, therefore for the deal size option type "Size" in the Expression box to search for the correct formulation.

if(
  ${dashboard_deal.size} > 100,
  25,
  ${dashboard_deal.size} * 25/100
)

For the Format dropdown, select Decimals. Then, from the Decimals dropdown, select "1".

4. EBITDA Score:

Note: The result will be different for each organization, therefore type "Ebitda" and "Number of employees" in the Expression box to search for the correct formulation.

if(
  ${dashboard_deal.ebitda} / ${dashboard_deal.number_of_employees} > 5,
  25, 
  ${dashboard_deal.ebitda} / ${dashboard_deal.number_of_employees} * 25 / 5
)

For the Format dropdown, select Decimals. Then, from the Decimals dropdown, select "1".





Total Score Calculation: Add a final table calculation to sum up the scores for each deal:



In the Visualization pane, choose Table as the visualization type. To declutter the report, hide the individual score calculations by clicking the gear icon next to the field → select Hide this field in visualization.



Apply Conditional Formatting: To make the scores visually impactful, go to Edit → Formatting → Toggle on Enable Conditional Formatting. Add rules to highlight high and low scores with different colors to quickly identify top deals.



By creating this Deal Scoring Report, you’ll streamline the evaluation process, enabling your team to make data-driven decisions and focus on the most valuable deals.

Updated on: 03/10/2024