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.

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

Use the Click Here to Generate Pivot button to create the Pivot View.
The report will be generated. Hide the Sub-totals and Grand total.
Click the cell and select Conditional Formatting . Set the Conditions to highlight the Team Performance rate.