Optimize Sales Rep Reporting and Leaderboards in Salesforce


Free Salesforce business guides to help admins/developers, project managers, sales managers, and decision makers. Download the free PDF series now: Reinventing Your Business, Reimagining Your Salesforce®.

Problem: As a Sales Manager, one convenient place is needed to provide visibility into all aspects of a Sales Rep's performance.  

Also, having a Sales leader board would further motivate Sales Reps to increase their productivity.

Solution: A combination of Lookup Helper and Rollup Helper along with Custom Objects enables a full view across performance metrics within a Salesforce record.

  1. Create custom objects for Sales Reps, Reporting Months, and Sales Rep Reporting Months.
  2. On the Sales Rep object, create 2 Number fields for "Total Won" and "Total Opportunities." And create 2 Currency fields for "Won This Month" and "Average Deal Size." 
  3. On the Reporting Month object, create 1 Lookup field for "Sales Rep of the Month," and 2 Currency fields for "Revenue" and "Average Deal Size."
  4. On the Sales Rep Reporting Month object, create 2 Lookup fields for "Reporting Month" and "Sales Rep," and 1 Currency field for "Total Revenue." 
  5. On the Opportunity object, create 2 Lookup fields for "Sales Rep" and "Reporting Month." Use Lookup Helper to relate Opportunities based on the opportunity owner and the close date.
    Lookup for Opportunity to Reporting Month
    Child = Opportunity
    Field = Reporting Month
    Formula for Reporting Months:
    Return Type = Text
    Create new parent records? checked

    Formula for Sales Rep Reporting Month

    Return Type = Text

    IF(IsWon, Sales_Rep__r.Name & l " & Reporting_Month_Formula__c, null)

  6. In Reporting Month, use Rollup Helper to rollup a sum of closed won opportunities for the month, as well as the average deal size across all reps. 
  7. On the Sales Rep object, use Rollup Helper to count the total number of opportunities, as well as the number they have won. Use a formula field to calculate win rate. 

    Formula for Win Rate

    Return Type = Text, Decimal Places =2

    Total_Won_Count__c / Total_Opportunities__c

  8. On the Sales Rep Reporting Month object, use Lookup Helper to populate Reporting Month and Sales Rep. Use Rollup Helper to sum up closed won opportunities for the sales rep in that reporting month. 
  9. For leaderboard reporting - create a rollup to display the "sales rep of the month." This is determined by the rep with the highest closed won opportunities.
    Rollup for Sales Rep of the Month Leaderboard
    Target Object = Reporting Month
    Field for Results = Sales Rep of the Month
    Child Object = Sales Rep Reporting Month
    Sales Rep Reporting Month Field = Sales Rep
    Rollup Type = Text
    Formula for Top Rep:
    Limit of 1 Records
    Defined Sort Order = Total Revenue, Descending
    Criteria:  isDeleted = False

Are there any additional Sales metrics that would make this use case even better?  Please leave us a comment below, or read Sales Rep Reporting, Part 2: Sales Rep Commissions (Rollup Helper).


Have questions? 
Schedule Demo

View Available Personnel in a Geographical Area Lookup Helper Use Case Library Assign Lead to Account Owner and Send New Lead Notification