Report of the Week - Team Performance Report

Report of the Week - Team Performance Report

This week we will see how to create a performance report for each sales agent, based on your CRM data. This will help you monitor your team productivity, and identify your top and under performers. 




Here, we will use Zoho CRM data as an example. This can also be achieved in other CRMs like Salesforce CRM and Microsoft Dynamics CRM, by slightly modifying the queries. 

To create the Team Performance report, you need to create the following Query Tables. 

  • Customer Start Date
  • Invoice and Recurring Month
  • Invoice Count by Owner

Customer Start Date

This Query Table obtains information on the date of subscription for each customer.


SELECT

"Customer ID",

STR_TO_DATE( min ( "Date of Subscription" ), '%Y-%m-%d' ) "Date" /* fetches the start date from subscription created time */

FROM    "Invoice"

GROUP BY   "Customer ID"


 

Invoice and Recurring Month

This Query Table obtains information on the month and year of the customer's subscription, along with Invoice details.

 

SELECT

INV. "Invoice ID" "Invoice ID" ,

INV. "Date of Subscription" "Date" ,

INV. "Customer ID" "Customer ID" ,

INV. "Invoice Owner" "Invoice Owner" ,

STR_TO_DATE(CUS. "Date" , '%Y-%m-%d' ) 'Min Date' ,

date_format(CUS. "Date" , '%Y%m' ) "Month and Year" , /*Extracts the year and month */

period_diff(date_format(INV. "Date of Subscription" , '%Y%m' ), date_format(CUS. "Date" , ' %Y%m' )) ' Month'   /*Finds the duration in months */

FROM    "Customer Start Date" CUS

INNER JOIN   "Invoice" INV ON INV. "Customer ID"   = CUS. "Customer ID"  

 


  Invoice Count by Owner

This Query Table obtains the Invoice Owner wise Invoice Count. 

 

SELECT

  "Invoice Owner" ,

COUNT ( "Invoice ID" )   "Invoice Count"

FROM   "Invoice"  

GROUP BY   "Invoice Owner"

 


Join Tables

Now join the table Invoice Count by Owner and Invoice and Recurring Month using LookUp Column.

Creating Report

Create a new Pivot View over the Invoice Count by Owner  Query Table by following the below steps. 

  1. Drag and drop the columns into the shelves as given below:  
    • Column shelf  -  Month  with  Actual (Dimension)  from  Invoice and Recurring Month  table  
    • Row shelf  - Drop columns as follows 
      • Invoice Owner  with  Actual  from  Invoice and Recurring Month  table 
      • Invoice Count  with  Actual (Dimension)  from Invoice Count by Owner  table
    • Data shelf  -  Invoice ID  with  Count > % of Previous Value from  Invoice and Recurring Month  table                                    
  2. Use the Click Here to Generate Pivot button to create the Pivot View.  
  3. The report will be generated. Hide the Sub-totals and Grand total.



  4. Click the cell and select Conditional Formatting . Set the Conditions to highlight the Team Performance rate. 



Your Team Performance Report is created.