How to Analyze Sales Rep Performance in Google Sheets

Last Modified: June 30, 2023 - 5 min read

Julian Alvarado

Analyzing sales rep performance is crucial for understanding the effectiveness of your team’s productivity in the field. Metrics like average deal size and win rate are valuable indicators of how each rep is contributing to the bottom line.

But accurately assessing performance is often a manual, time-consuming process. Thankfully, you can easily track sales rep performance in Google Sheets using Coefficient’s AI Copilot.

This step-by-step guide will walk you through how to analyze sales performance by each rep in Google Sheets using Coefficient’s powerful GPTX functions.

Let’s dive in!

Step-by-Step Walkthrough: Analyze Sales Rep Performance in Your Spreadsheet

1. Install Coefficient

Coefficient is a free Google Sheets add-on that allows you to connect your business systems to Google Sheets and pull real-time data into your spreadsheet.

Coefficient also comes with several powerful AI features that make sales rep analysis much simpler.

First, let’s install Coefficient. Open Google Sheets. Click Extensions on the menu bar and select ‘Add-ons’ -> ‘Get add-ons.’

google sheets addon coefficient

This will launch Google Workspace Marketplace. In the search bar at the top, search for “Coefficient” and select the Coefficient app.

coefficient data connector

Coefficient will now ask you for permission to access your Google account. Press ‘Allow’ to complete the installation.

coefficient permissions

Once completed, return to your Google Sheets menu and click on Extensions -> Coefficient -> Launch.

google sheets addon

Coefficient will now appear on the sidebar of your Google Sheet.

2. Import Live Salesforce Data

Now you must import your live Salesforce CRM data into Google Sheets.

This data will provide the foundation for your analysis, giving you insights into deal sizes, win rates, and revenue generated by each sales rep.

Click ‘Import from…’ on the Coefficient sidebar menu.

import live data google sheets

Select Salesforce as your data source.

salesforce data imports google sheets

Click ‘From Objects & Fields.’

objects and fields salesforce

Under ‘New Import,’ choose the ‘Opportunity’ object.

salesforce opportunity google sheets

Click ‘Select fields…’

Select Salesforce object

Then select the following fields: ‘Name,’ ‘Full Name,’ ‘Closed,’ ‘Won,’ ‘Expected Revenue,’ and ‘Close Data.’

salesforce fields google sheets

Once all the fields are selected, name your import and click ‘Import’ to send your data to Google Sheets.

sales performance by rep

3. Analyze Sales Rep Performance

Now let’s calculate each sales rep’s average deal size and win rate by creating custom formulas with Coefficient’s GPTX functions.

Go back to Coefficient’s main menu and click ‘GPT Copilot.’

gpt copilot google sheets

Select ‘Pivot Builder.’

pivot builder google sheets

Use your cursor to select your data range. For this example, the range is Opportunity!A2:F102.

expected revenue google sheets

Next, describe the pivot you wish to build in the text box. In this example, the prompt we’ll use is: “average expected revenue for won is true for each full name”.

Now click ‘Build’ and insert your pivot table in an empty cell on your spreadsheet.

pivot builder google sheets

Next, we’ll create a custom formula with the Formula Builder to calculate individual rep win rate.

formula builder google sheets

Describe the formula you wish to build.

For this example, our prompt is: “starting in B3 count all of the true values in column D:D when the name in b:b matches divided by count of the occurrences of name in column b:b”.

Coefficient Excel Google Sheets Connectors
314,000 Pros Sync Live Data from Their Business Systems into Spreadsheet

Stop exporting data manually. Sync data from your business systems into Google Sheets or Excel with Coefficient and set it on a refresh schedule.

formula builder prompt

Create a new column called ‘Win Rate Calc,’ and copy and paste the formula builder’s output into the cell below it. For this example, the formula is:

=COUNTIFS(B:B,B3, D:D, TRUE)/countif(B:B, B3)

formula builder google sheets prompt

Drag the formula down for all your sales reps. Now format the row by percentage. Click the % icon on the toolbar.

win rate google sheets calculation

And finally, remove a decimal to make the data easier to read. Click the .0 icon on the toolbar.  

win rate calculation

Next, we’ll build a pivot table to track sales rep sales performance over time. Return to the GPT Copilot menu and select ‘Pivot Builder.’

pivot builder gpt copilot

Use your cursor to select the data range and describe the pivot you wish to create.

In this example, the prompt is: “full name as the rows and the values is the max win calc”.

pivot builder prompt

Insert the formula into your spreadsheet.

pivot table spreadsheet sales data

Next, we’ll group by salesperson and pivot by month and year.

Select the data range. Enter this prompt in the text box: “group by expected revenue by full name where won is true, pivot by close data year and close date month”.

win rate sales rep google sheets

Select ‘Build’. Now insert the formula into an empty cell.

insert pivot table google spreadsheet

As a last step, let’s calculate the number of deals won and revenue generated per rep.

Use your cursor to select the data range. Then describe the table you want to build in Pivot Builder.

In this example, the prompt is: “Sum number of deals won, and sum total revenue generated when won is true for each full name”.

calculate expected revenue google sheets

Select ‘Build’. Insert the formula into a cell to generate the calculation.

how to use pivot builder

And there you have it! Now you can analyze the performance of your individual sales reps directly inside Google Sheets.

Sales Rep Performance Analysis in Google Sheets Is Easy with Coefficient

Coefficient combines the power of AI and Google Sheets, allowing you to perform sales rep analysis, sales forecasting, and other critical sales calculations in a fraction of the time.

Get started with Coefficient for free to bring enhanced sales analysis to Google Sheets with the power of AI.

Sync Live Data into Your Spreadsheet

Connect Google Sheets or Excel to your business systems, import your data, and set it on a refresh schedule.

Try the Spreadsheet Automation Tool Over 350,000 Professionals are Raving About

Tired of spending endless hours manually pushing and pulling data into Google Sheets? Say goodbye to repetitive tasks and hello to efficiency with Coefficient, the leading spreadsheet automation tool trusted by over 350,000 professionals worldwide.

Sync data from your CRM, database, ads platforms, and more into Google Sheets in just a few clicks. Set it on a refresh schedule. And, use AI to write formulas and SQL, or build charts and pivots.

Julian Alvarado Content Marketing
Julian is a dynamic B2B marketer with 8+ years of experience creating full-funnel marketing journeys, leveraging an analytical background in biological sciences to examine customer needs.
350,000+ happy users
Wait, there's more!
Connect any system to Google Sheets in just seconds.
Get Started Free

Trusted By Over 20,000 Companies