General information
BitCurb’s dashboard is comprised of several widgets, which provide analytics and can give you an insight of any trends and patterns in your company’s data. Mostly the data reconciliation operations (a.k.a crunch operations) will benefit from this type of analytics, because when you filter your data you can see right away what the result is.
All examples in this article are based on the BitCurb Code Samples, which can be found on our GitHub repository. You can have a look at them and see how you can integrate BitCurb API in a few easy steps.
We provide out of the box the following widgets:
Last 10 Crunches
This widget will show you the last 10 crunch operations that users in your organization have run. On the Y axis you can see the name of the rules and on the X axis on a scale from 0 to 100 we show the success rate which is measured in percent. In case the crunch operation was run as an ad hoc crunch operation through the API, e.g. no predefined rule in the system was used, you will see No Name [API] on the Y axis because we don’t have a name to show.
Additional details per rule are shown when you hover over the bars – name and time the rule was run at along with the success rate and the person, who ran the rule in question.
Top Performance
This widget shows historically the best 10 performing rules which means the rules with highest success rate. This provides great information of what works best for your reconciliation process, so you can tweak and improve your process even further if possible. This bar chart again shows the names of the rules on the Y axis and the success rate on the X axis.
Additional details per rule are shown when you hover over the bars – name and time the rule was run at along with the success rate and the person who ran the rule in question.
Worst Performance
This one is the complete opposite of the Top Performance widget showing the 10 rules with the worst success rate. The information you can get from this widget might be even more beneficial compared to the top performing rules because it allows you to take informed decisions and act accordingly as to what needs improvement in your process.
Additional details per rule are shown when you hover over the bars – name and time the rule was run at along with the success rate and the person, who ran the rule in question.
Success Rate
This widget shows the average success rate for the last 10 unique rules’ executions. It is pretty much the same as the Last 10 Crunches if no rule has been run more than once. If some of the rules have been run more than once then it shows the average success rate over the period of these last 10 executions. Again, on the Y axis are the rule names and the success rate is on the X axis.
Additional details per rule are shown when you hover over the bars – name and success rate of the rule in question.
Client Crunches
This widget shows how the last 10 crunch and filter operations were spread among different type of client consumers. When you hover over the pie chart you will see the exact number of crunches we have from each consumer. At the moment this is the only widget that can give you some kind of analytical information regarding filtering operations.
Data Analysis
In this section we will try to show how everything fits together starting from defining crunch rules and templates and analyzing them using the dashboard widgets. All examples are based on BitCurb Code Samples which can be found here.
What we will cover is the data reconciliation of a fictitious bank statement and general ledger account. We have 2 separate data source files – bank.csv and gl.csv that we need to compare in order to reconcile them. We start off by creating 3 separate crunch rules that we will run individually and then we will create a crunch template adding the already created crunch rules into it.
- CS – One To Many w/o Var
The first rule is one to many requiring an exact match in the data reconciliation process.
$1.AccountIdentification == $2.AccountIdentification AND $1.TransactionValueDate == $2.TransactionValueDate AND $1.TransactionType == $2.TransactionType AND FIND($2.AccountServingInstitution, $1.AccountServingInstitution) >= 1
- CS – One To Many with Var
This rule is one to many that allows us to reconcile without having an exact match meaning we can create variances based on the balance difference which can be useful in scenarios where we have some sort of a fee or tax applied:
$1.AccountIdentification == $2.AccountIdentification AND $1.TransactionValueDate == $2.TransactionValueDate AND $1.TransactionType == $2.TransactionType AND $1.AccountServingInstitution == $2.AccountServingInstitution
- CS – One To One
The third rule is one to one using the following expression:
$1.AccountServingInstitution == $2.AccountServingInstitution AND $1.Amount == $2.Amount
Now that we have our rules ready we can execute each of them separately passing the data from the two source files. Here is what kind of analytics we can get and what we can do with it.
Since we have only run 3 rules so far, the data in all bar charts is similar. But if we look at the Top Performance widget we can see that the “CS – One to One” rule is at the top. It managed to reconcile a little over 50% of the data while the other 2 rules managed to match just half of what the top rule did. We can conclude that our one to one rule if added to a crunch template can be set as the first rule in this template, because it has the best success rate among the 3 rules leaving less data for the rules coming after it to work on. Less data for crunch operations means less work on our side and faster response times while at the same time you don’t overuse unnecessary your licence quota.
Let’s run our template and see what we have on the dashboard page:
We now start to see quite a different picture on the dashboard, because we have more data for analysis. The Last 10 Crunches widgets for example shows that the last rule that was part of the template didn’t match any data at all while the previous rule had 60 % success rate. If we look at the Worst Performance we again see this very same rule at the top as having the worst success rate among the 3 rules we have defined. The “CS – One To One” rule keeps its position as top performing rule though. Seeing how inefficient “CS – One To Many with Var” performs, is a reason that you may need to make modifications to its definition, either your data is not what you expect it to be or the rule itself needs tweaking.
Let’s look at the details we can get for each of the rules if we hover over the bars:
As you can see we have a tooltip showing the name of the rule and the last time it was run. On the following line we see success rate of the rule and the user that last ran this rule.
BitCurb’s dashboard is provided to you at no additional cost and can prove to be of a real difference, when you need to understand your data reconciliation process. It also might help you to spare some of the remaining records from your licence quota if you analyze the data properly.