Automate Your Commissions Tracking with Zoho Analytics
No More Monthly, Manual Calculations!
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.
Initial Questions to Ask and Settings to Tweak
A few questions for you to ask yourself:
- Do all salespeople have the same commission rate?
- If not, you must create a custom field in the “Users” section of Books to specify each salesperson’s commission rate.
- Are all your items commissionable?
- If not, create a boolean custom field in the “Items” section. Then, go through your item catalog to mark whether certain items are commissionable or non-commissionable.
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:
- The built-in module is kind of broken.
- You can’t associate individual commission rates to salespeople in the built-in module.
- There’s additional functionality we get by going this route besides commission tracking. Feel free to reach out if you have questions.
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.
Syncing Your Data
Ensure the Zoho Finance suite (or Zoho CRM) is synced to 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.
Reviewing Our Query
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!
Do You Use Quickbooks/Xero/Another Platform?
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.
To Learn More, Look at Our Analytics Courses!
Our courses below teach you all the skills you would need to build out some commission dashboards.
- SQL code in Zoho Query tables
- Using Pivot and Summary tables
- Building and sharing dashboards
Learn how to set up a system where you can automate the distribution of pre-filtered dashboards to clients, partners, or whoever!