Empowering your sales team with the right tools for success is crucial. A well-designed sales rep scorecard in Google Sheets acts as a powerful instrument for tracking performance, identifying strengths and weaknesses, and fostering a healthy competitive spirit. 

This comprehensive guide will walk you through every step of building a customized salesperson scorecard that drives results. At the end of the blog, you can even download a free sales scorecard template.

What is a Predictive Scorecard?

A predictive scorecard, also known as a predictive analytics scorecard, is an advanced tool used in business intelligence and analytics to forecast future outcomes based on historical data, patterns, and predictive models. Unlike traditional scorecards that primarily focus on measuring past performance and current status, predictive scorecards go a step further by analyzing data to anticipate future trends, behaviors, and outcomes.

The primary goal of a predictive scorecard is to provide actionable insights and foresight into potential scenarios, allowing businesses to make informed decisions, mitigate risks, and capitalize on opportunities proactively.

By leveraging predictive analytics techniques such as machine learning, statistical modeling, and data mining, predictive scorecards can uncover hidden patterns and correlations within large datasets, enabling organizations to anticipate customer behavior, market trends, and business performance with greater accuracy and confidence.

Creating a Sales Representative Scorecard in Google Sheets Utilizing HubSpot Data.

Building a sales rep scorecard in Google Sheets using HubSpot data involves several steps, including extracting relevant data from HubSpot, organizing it in Google Sheets, defining key metrics and calculations, and visualizing the results.  If you're using HubSpot or any other CRM, the ability to integrate data directly into Google Sheets through an API to Google Sheets connection can make the entire process seamless. APIs allow you to pull real-time data from HubSpot (or other platforms) into Google Sheets automatically, ensuring that your scorecards always reflect the most up-to-date performance metrics. 

Here's a step-by-step guide to help you create a comprehensive sales rep scorecard:

1. Data Preparation in HubSpot:

Identify Relevant Data:

Log in to HubSpot and determine the data points crucial for your sales rep scorecard. Common choices include:

• Sales rep names

• Closed deals (amount and date)

• Opportunities created and closed (win/loss)

• Activities (calls, emails, meetings)

• Customer satisfaction scores

Export the Data:

Once you've identified the data points, export them from HubSpot. You can achieve this easily using Superjoin. 

The dataset provided below has been extracted from HubSpot into Google Sheets.

For establishing a seamless connection between HubSpot and Google Sheets, use Superjoin. For Detailed instructions please refer to our blog “How to Integrate HubSpot to Google Sheets Seamlessly”.

2. Setting Up Your Google Sheet:

Once the dataset is successfully exported it will look something like this. Our dataset comprises several rows, each representing a sales representative and their corresponding performance metrics. These metrics include Closed Deals (Amount), Opportunities Created, Opportunities Closed (Win/Loss), Activities (Calls, Emails, Meetings), and Customer Satisfaction Scores. 

3. Formatting the Table:

Before we move forward, let’s format the table with some colors for easier distinction:

Step 1: Fill Colour Icon for Background Color Change:

• Select the cells in the table.

• Use the Fill Color icon in the toolbar to change the background color.

Step 2: Text Colour for Text Colour Change:

• Select the cells in the table.

• Use the Text Colour icon in the toolbar to change the text color.

Step 3: Add Borders:

• Select the cells in the table.

• Use the Borders tool to add borders around the cells.

Now let’s label the key metrics:

• In cell A23, type "Key Metrics".

• In cell B23, type "Total Closed Deals".

• In cell C23, type "Total Opportunities Created".

• In cell D23:E23, type "Win Rate", and use the Merge Cells option to accomplish this.

• In cells F23:H23, type "Average Activities", and use the Merge Cells option to accomplish this.

• In cell I23, type "Average Customer Satisfaction Score".

With these formatting and labels applied, the table is now easier to read and understand.

4. Calculate Key Metrics:

For each sales rep, calculate key metrics such as:

• Total Opportunities Created: Sum of the Opportunities Created column.

• Win Rate: Number of Opportunities Closed with "Win" status divided by Total Opportunities Closed.

• Average Activities: Average of the Activities (Calls, Emails, Meetings) column.

• Average Customer Satisfaction Score: Average of the Customer Satisfaction Scores.

• You can add more metrics based on your specific requirements.

Step 1: Go to the bottom cell beneath the Closed Deals column, i.e., B24.

Step 2: Type the formula: =SUM(B3:B22)

Step 3: Press Enter to calculate the total closed deals for all sales reps.

Step 4: The result will appear in cell B24.

Total Opportunities Created:

Step 1: Go to the bottom cell beneath the Opportunities Created column, i.e., C24.

Step 2: Type the formula: =SUM(C3:C22)

Step 3: Press Enter to calculate the total opportunities created for all sales reps.

Step 4: The result will appear in cell C24.

Win Rate:

Step 1: Go to the bottom cell beneath the Win/Loss column, i.e., D24:E24.

Step 2: Type the formula: =SUM(D3:D22)/ (SUM(D3:D22) + SUM(E3:E220))

Step 3: Press Enter to calculate the win rate for all sales reps.

Step 4: The result will appear in cell range D24:E24.

Average Activities:

Step 1: Go to the bottom cell beneath the Activities column, i.e., F24:H24

Step 2: Type the formula: =AVERAGE(F3:H23)

Step 3: Press Enter to calculate the average activities for all sales reps.

Step 4: The result will appear in cell range  F24:H24.

Average Customer Satisfaction Score:

Step 1: Go to the bottom cell beneath the Customer Satisfaction Scores column, i.e., I24.

Step 2: Type the formula: =AVERAGE(I3:I22)

Step 3: Press Enter to calculate the average customer satisfaction score for all sales reps.

Step 4: The result will appear in cell I24.

5. Insert Target Values Columns:

Step 1: Label the cell below the “Key Metrics” as “Target Values”. In our case, it is cell A25.

Step 2: Determine target values for each metric based on historical performance, industry benchmarks, or organizational goals.

Step 3: Enter these target values in the respective column for each metric. For example, if you have a column for "Total Closed Deals", you would enter the target number of closed deals for each sales rep.

In our case, we went with some modest values as you can see.

6. Calculate Achievement Percentage:

Step 1: After entering the target values, use formulas to calculate the achievement percentage for each metric, comparing the Actual Performance to the Target Values.

You can use formulas similar to: =(Actual Performance / Target Value) * 100.

In our case, the Achievement Percentage for Total Closed Deals: =(B24/B25)*100 

Pro tip: Drag horizontally to calculate the same for all other metrics. In cases where you have merged cells, consider writing a formula with cell range; otherwise, you may encounter errors. Here’s an example,

After calculating all the Achievement percentages, the table should look like this.

7. Apply Conditional Formatting

Enhance the scorecard's visual appeal and readability by applying conditional formatting.

To apply conditional formatting to the dataset in Google Sheets, you can follow these steps:

Step 1: Select the Range:

• Click and drag to select the range of cells where you want to apply conditional formatting. For example, you might want to apply conditional formatting to the columns representing each metric.

Pro tip: Please format each metric separately.

Step 2: Open the Conditional Formatting Menu:

• Go to the menu bar and click on "Format" > "Conditional formatting."
  
  

Step 3: Choose the Formatting Rules:

• In the Conditional format rules pane on the right, choose the type of conditional formatting you want to apply. For instance, you have the option to select either "Single color" or "Color scale" based on your preference. In our case for the “Closed Deals” column, we will use the “Color scale”.
  
  

Step 4: Set the Conditions:

• Define the conditions for the formatting. For instance, you can set rules such as "Minpoint," "Midpoint," "Maxpoint," etc.
  
  

• Enter the criteria and choose the formatting style for cells that meet the condition. For example, setting the Midpoint percentile to 50.
  
  

Step 5: Apply the Formatting:

• Click on the "Done" button to apply the conditional formatting to the selected range of cells.
  
  

Step 6: Repeat the same for other metrics, but make sure to not choose a similar color for consecutive columns. This would help in distinguishing top values in a column.

Step 7: Review and Adjust:

• Review the applied formatting to ensure it reflects the intended conditions and formatting styles.

• If necessary, make adjustments to the formatting rules or range selection.
  
  

Step 8: Additional Formatting (Optional):

• You can add multiple rules for different conditions or combine different formatting styles to create a more comprehensive visual representation of your data.

8. Creating a Column Chart to Compare Total Closed Deals for Each Sales Rep

Step 1: Select Data Range:

Click and drag to select the range of data you want to include in the chart. For this example, select the range of cells containing the Rep Names and their corresponding Total Closed Deals.

Step 2: Insert the Chart:

• Go to the menu bar and click on "Insert" > "Chart."
  
  

• This will open the Chart editor on the right side of the window.

Step 3: Choose Chart Type:

• In the Chart editor, select "Chart types" (the icon that looks like a column chart).
  
  

• Choose "Column Chart" from the list of options.

Step 4: Customize Chart (optional):

• In the Chart editor, you can customize various aspects of the chart such as title, axis labels, legend, and colors.

Step 5: Review and Adjust:

• Review the chart to ensure it accurately reflects the Total Closed Deals for each sales rep.

• Make any necessary adjustments to the chart title, axis labels, or formatting.

• Move the chart below the table.
  
  

9. Creating a Stacked Column Chart for Win/Loss Metric

Step 1: Select Data Range:

• Highlight the data range, including the sales reps' names and the corresponding Win and Loss counts. 
  
  

Step 2: Insert the Chart:

• Go to the menu bar and click on "Insert" > "Chart."

Step 3: Choose Chart Type:

• In the Chart editor, select "Chart types" (the icon that looks like a column chart).

• Choose “Stacked Column Chart” from the options.
  
  

Step 4: Customize Chart (optional):

• In the Chart editor, you can customize various aspects of the chart such as title, axis labels, legend, and colors.

Step 5: Review and Adjust:

• Review the chart to ensure it accurately reflects the Total Closed Deals for each sales rep.

• Make any necessary adjustments to the chart title, axis labels, or formatting.

• Move the chart below the table.
  
  

Follow the similar steps to create a column chart for activities (Calls, Emails, Meetings) vs Reps.

10. Creating a Scorecard Chart for Actual Total Closed Deals Versus Targeted Closed Deals

Step 1: Select the cells B24 and B25.

Step 2: Insert the Chart:

• Go to the menu bar and click on "Insert" > "Chart."

Step 3: Choose Chart Type:

• In the Chart editor, select "Chart types" (the icon that looks like a column chart).

• Choose “Scorecard Chart” from the options.
  
  

Step 4: Check the box against the Switch rows/columns option.

Step 5: Close the Chart Editor and Position the Score Card Chart.

Hurrah! Our Sales Rep Scorecard is ready.

Download Your Free Sales Scorecard Template Here.

Explore Superjoin Template Gallery for more Templates!

Conclusion

The scorecard provides valuable insights into key metrics, enabling sales managers to identify trends, recognize achievements, and address areas for improvement effectively. Moreover, by involving sales reps in the target-setting process and providing ongoing support and development opportunities, organizations can cultivate a sense of ownership and accountability that drives individual and collective success.

A well-designed sales rep scorecard in Google Sheets is a powerful tool, not a rigid system. Regularly review and refine your scorecard and KPIs as your business evolves. By focusing on the right metrics, providing clear targets and feedback, and fostering a culture of continuous improvement, you can empower your team to consistently exceed expectations and propel your business forward.

Say Goodbye To Tedious Data Exports!

Are you tired of spending hours manually exporting CSVs from different tools and importing them into Google Sheets? 

Superjoin is a data connector for Google Sheets that connects your favorite SaaS tools to Google Sheets automatically. You can get data from these platforms into Google Sheets automatically to build reports that update automatically. 

Try Superjoin out for free or schedule a demo.