Create dashboards according to any business’ criteria that update regularly in Analytics and avoid the extra monthly or quarterly work of manually calculating commissions.
This sample code is built off of the Invoice module in Zoho Books. However, it can be generalized to Zoho CRM as well. For each step described below for Books, think about the parallel steps to build the same report with CRM data.
A couple things to think about: do all salespeople have the same commission rate and are all items commissionable? If not, you must create a custom field in the “Users” section of Books to specify each salesperson’s commission rate. Also, in the “Items” section, create a custom field to mark if each item or service is commissionable or not.
Additionally, in each record module (invoices, sales orders, etc.) you will have to add a custom lookup field to the Users module to relate each record to a specific salesperson. But, in CRM, this would just be the record owner. Then, ensure the Zoho Finance suite (or Zoho CRM) is synced to Analytics.
It is pretty straightforward. Most table names in Zoho are generalized and if not the same, should be similar. The key components are the total sales amount and that it be filterable by Salesperson (record owner in CRM). Bonus information would be the date of the sale and if individual items can be marked commissionable or not.
This code creates a list (base table of Invoice Items) of all items invoiced that are commissionable and draws in information from the Users (the salespeople of the business), Invoices, and Items tables. (Here we use the Invoices table because we want to filter by if is has been paid for already, but almost any module with a total dollar amount could theoretically work) A simple mathematical calculation gives the amount of commissions attributed to the salesperson for each item.
This report itself is not ideal but is a means to an end; it is not in summary format. So, you can run another query on top of it to aggregate totals and sort by salesperson, or you can simply just use the charts or tables within Zoho Analytics to create nice-looking reports! This can also be modified in many ways. Total sales by salesperson, filter by certain big-ticket items, calculate a manager’s commissions (if his/hers depend on the sales of an entire team), etc. The possibilities are endless.
Click here to copy these scripts. For more Zoho-wizardry, check out our GitHub page.
When scripting, ask yourself this question – will this value ever be null? If it’s a yes, that’s a place for a null check! Here are 3 tips and best practices to help you kickstart the habit.
Learn to create a customized inventory report with Zoho Analytics. This tutorial contains a link to our GitHub page for SQL code that will help with your table creations.
How nice would it be if you could, at the press of a button, send clients an email w/a Zoho Books invoice? Replete with “Pay Now” buttons that link to PayPal/Stripe/other payment gateways?
Whether you work primarily out of CRM or the Zoho Finance Suite, you can use Analytics to build commissions dashboards. This involves some fairly simple SQL code.
If you are an inventory manager, this blog post could change your life. If you are not, it will at least teach you how to build some wicked inventory tracking for your business.
Convert fields, related activities, attachments, notes and more from one record to another across modules via custom function.
