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.
- Create custom objects for Sales Reps, Reporting Months, and Sales Rep Reporting Months.
- 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."
- 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."
- On the Sales Rep Reporting Month object, create 2 Lookup fields for "Reporting Month" and "Sales Rep," and 1 Currency field for "Total Revenue."
- 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)
- 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.
- 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
- 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.
- 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).
|View Available Personnel in a Geographical Area||Lookup Helper Use Case Library||Assign Lead to Account Owner and Send New Lead Notification|