Create Sales Rep Reporting Using Rollup Helper and Lookup Helper

Problem: Sales wants detailed reporting, showing the break down of revenue by sales rep each month, including a leaderboard.

Information sales could be interested in includes Total Won, Total Opportunities, Average Deal Size, Win Rates, and Sales Rep of the Month. Ideally, they would like to get all of these metrics in one view. There is no out-of-the-box reporting for this currently, so how can you set this all up easily?

Solution: You can use Rollup Helper and Lookup Helper together!

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
   Filter for Top Rep:
   Limit of 1 Records
   Defined Sort Order = Total Revenue, Descending
   Criteria:  isDeleted = False
   
   

For more detailed steps, watch our video: