
Automate Your Commissions Tracking with Zoho Analytics
- Peter
- Difficulty: Beginner
- Estimated reading time: 15 mins
Zoho’s all-in-one software package makes Zoho Analytics a very powerful tool to analyze data from all over the Zoho Suite.
This eliminates the need to export data monthly and create reports manually within Excel — just set up a connection to Zoho Analytics and it will do the heavy lifting for you.
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 — you can easily find this code in our GitHub page.
A few questions for you to ask yourself:
Important point to note: if you’re using Zoho Finance, this table makes use of a custom user lookup field called “Salesperson”, rather than the built-in “Salesperson” module. There are a couple reasons for this:
Knowing that, you’ll now need to make sure you set up custom user lookup fields in each of the modules you’d like to track commissions on (invoices, sales orders, etc.)
If you’re using Zoho CRM, you’re good — we’ll just use the “Record Owner” field.
Ensure the Zoho Finance suite (or Zoho CRM) is synced to and integrated with Analytics. This is done via the Data Import page in Zoho Analytics.
Another aside: sync CRM first and then add the Finance Suite to that same workspace if you haven’t already done so. That way, you can run all of your reports in a single workspace.
This Query is pretty straightforward. Most table names in Zoho are generalized and if not the same, are similar.
Of course, it is very customizable and more (or less) information can be pulled in to satisfy any reporting needs.
This code creates a list 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 only pay commission on Invoices that we’ve received payments for, 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.
The resulting table 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 code is specifically written to work with data from Zoho CRM/Zoho Finance. However, Zoho Analytics has built-in syncs with other accounting platforms (Xero and Quickbooks Online being the 2 big ones).
If you would like to sync from those platforms, we’d be happy to help you answer questions about how to adapt the above instructions for your platform.
For more information on Zoho Analytics, check out our Zoho Analytics vs Tableau comparison. And, if you need help learning how to use Analytics, our courses below teach you all the skills you would need to build out some commission dashboards.