Salesforce: sales representative custom dashboard

We'll build a simple spreadsheet that will let you change the name of the sales representative and see the opportunity amounts lost and won each month.

What do I get out of it?


We'll build a simple spreadsheet that will let you change the name of the sales representative and see 

  • The opportunity amounts lost and won each month for that specific sales rep. 

We keep it simple on purpose, you could add any metrics you want to this dashboard. We mainly want to show the logic here. 


How does it work?


1.The first step is to import your opportunities in a sheet

  • Create a new sheet called "Salesforce opportunities"
  • Click on the import button
  • In the data source dropdown, choose your Salesforce account
  • In the "Table to import" dropdown, choose "Opportunity"
  • Choose the columns that you want to use: You'll need at least the "Opportunity ID", "Created Date", "Owner ID", "Stage" and "Amount" columns

2. Import Users

As you can see, we have the id of the owner, and not their name. We'd like to get their name. To do that, we'll have to import the User table and lookup the name

  • Create a new sheet called "Salesforce users"
  • Let's start by importing the table User with at least the following columns: "User ID", "Full Name"

3.Looking up the Full Name

We're going to look up the Full Name to get it in the Opportunities table. 

  • From the Opportunities table, add a column called "Full Name" by placing your cursor on the cell to the right of your table headers and writing "Full Name" and pressing enter
  • Write then the formula:

=LOOKUP(#'Owner ID', 'Salesforce users'!#'User ID', 'Salesforce users'!#'Full Name')

4. Add a month column

Then we have to add a calculated column to return the created month of each deal in a handy format: {year}_{monthnumber}

  • In the sheet "Salesforce opportunities", place your cursor on the call to the right of your table headers
  • Write the title of your column ("Month") and press enter
  • Then enter your formula =YEAR(#'Created Date')&"_"&MONTH(#'Created Date')

5. Layout of the dashboard

  • Create a new sheet called "Sales rep dashboard". 
  • Copy the layout in the screenshot
  • If you're using your own Salesforce data, put in B3 the name of an actual sales rep

6. Let's calculate the Amount lost and Amount won

  • In C6, calculate the amount lost: =SUMIFS('Salesforce opportunities'!#Amount,'Salesforce opportunities'!#Month,$B6,'Salesforce opportunities'!#'Full Name',$B$3,'Salesforce opportunities'!#Stage,"Closed lost")
  • Copy paste or drag downward
  • Same with amount won: =SUMIFS('Salesforce opportunities'!#Amount,'Salesforce opportunities'!#Month,$B6,'Salesforce opportunities'!#'Full Name',$B$3,'Salesforce opportunities'!#Stage,"Closed won")

To fix cell references (adding the $), use the shortcut option + 4 (Mac) or F4 (Windows). You have to be editing the formula and your cursor must be on the said cell reference

7.See it in action

Nice! Now you just have to change the name of the sales rep to see their specific metrics. You can use this pattern for many things: see metrics per country, deal stage, industry, etc



X
Please wait...
Oops! Something went wrong while submitting the form.