In this blog post, we’ll show you how you can take your commission analytics to the next level! We’ll unleash the power of data mining to analyze sales commissions. For those of you who are not familiar with Sales Cookie, we automate sales commissions. Using Sales Cookie, you can:
- Eliminate the need to constantly generate / revise / distribute commission spreadsheets
- Increase commission accuracy and avoid mistakes
- Motivate your reps via online dashboards, goals, increased transparency, etc.
- Deliver commission reporting spanning all reps
- Improve commission agility
- Ensure compliance with legal & accounting standards
Pulling Commission Data
First, let’s grab our Sales Cookie data access URL. Simply login to Sales Cookie, and browse to Accounts > My Profile > Security. Copy your data access URL:
Now, we can connect using various tools such as Tableau, Microsoft Power BI, or Excel. For this blog post, we’ll use Microsoft Power BI. Start Microsoft Power BI, and connect using OData. Here my URL is “localhost” because I’m using a test environment:
We’ll just select a few essential data tables among many available to us:
We can see that Power BI magically understands all the relationships between selected tables. This means we can formulate queries without having to explicitly specify how to join between tables:
Commission Natural Language Query
We can certainly handcraft custom reports (with drilldowns, charts, filters, groups, etc.). However, let’s be a bit lazy and use a natural language query to quickly create a simple report. We can use the Insert > Q&A function, and ask a question like this:
That’s pretty awesome! We built a report (mind you, with table joins, multi-level grouping, and data aggregation) simply by typing text! Let’s change the visualization to waterfall. We now have a stack ranking visualization. This highlights differences in payouts between individuals. Many other visualizations are also available (from pie chart to heat maps).
It’s equally easy to switch to table mode and view the underlying data:
It’s time to take our analysis of commissions to the next level. But first, let’s run additional calculations within our Sales Cookie demo workspace. We’ve also added manual commission adjustments for good measure:
Commissions can be lump-sum amounts (ex: cash bonuses). Or, commissions can paid on a per-transaction basis. For our analysis, we want to focus on per-transaction commission payouts. Let’s pull in data from some additional tables, including the CalculationCommission table. Make sure to also include Transactions (not listed below).
Let’s plot the average per-transaction payout by payee and by product:
This looks interesting, but it’s hard to know what is really going on. Which factors impact commission amounts the most? Is it the customer? Is it the product? Is it the commission plan? Let’s add a Decomposition Tree visual. At this point, it looks a bit bare:
Next, select the metric to analyze. Here, we’ll select the average value from table CalculationCommission. This table stores per-transaction payouts, and the value is the payout.
Suppose that we want to analyze which factors impact commission payouts the most – for example:
- The commission plan’s name?
- The transaction’s customer?
- The transaction’s product?
We can drag and drop those fields to the “Explain by” list:
We can now expand the Decomposition Tree based on “high value” impact. The algorithm finds which criteria is the most meaningful, in terms of yielding higher payouts. We can see that the customer name is the #1 most significant factor (the average per-transaction commission amount is $111.95 for “Candy Fancy Inc.”). Then, the #2 most significant factor is the product name (the average per-transaction commission amount is $506.99 for “Lemon Cookies”) . Finally, the #3 factor is the plan name.
Above, we let a data mining algorithm discover which factors have the most impact on commission payouts. However, we can also draw a decision tree based on our own choices. Below, we generated a decision tree which shows the average per-transaction payout by plan, then by product (because that’s what we want to do).
Commission Root Cause Analytics
Let’s try a different but related type of analytics – key influencers. Below, we can see how certain fields impact the amount credited to rep (for quota retirement). The event name (purchase vs. refund) appears to be a key influencer. No big surprises here. Other influencers are revenue and order quantity.
Here is some really cool advanced analytics! We want to know what might be causing a slight trend of decreasing credited revenue. It looks like sales to customer “Fancy Treats” has something to do with this downward trend. To get this visualization, all we had to do is a/ plot credited revenue by date, b/ add a trend line, and c/ ask Power BI to explain the trend.
Find Commission Anomalies
Next, we can plot the commission amount by transaction date and detect anomalies. In this example, all commission amounts are within the expected band. If there were anomalies, they would be highlighted.
We can also create a commission table with various fields like this:
And find clusters:
Below, we can spot a huge anomaly. This was a 5K manual commission reward which we added to see if we could identify it. We can see that this amount has no relationship to the transaction’s revenue nor its order quantity.
In this blog post, we showed a few examples of advanced analytics you can use to analyze your sales commissions. In the end, it’s all about having access to all commission data. Sales Cookie is a powerful, open platform which makes analyzing commissions a breeze. Using Sales Cookie, you have unlimited power to slice and dice commission data any way you want – and even run data mining algorithms to better understand root causes and detect anomalies. Visit us online to learn more!