Optimize Sales Rep Reporting and Leaderboards in 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
   TEXT(MONTH(CloseDate))&"-"&TEXT(YEAR(CloseDate))
   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?