Big data is the big focus of much of the current analytics press; huge volumes of unstructured or semi-structured data that can be mined to find those gems of information you didn’t know you had.
Sentiment analysis and remarketing algorithms may be all well and good for Facebook and Amazon, but if you only have relatively small volumes of highly structured data, and already have reports coming out of your ERP or MRP system, what added value can you get from adding a BI layer? Like people in many organisations without a real analysis tool, are you currently just seeing your information without getting insights from it?
Why Have a BI Tool for Small Data?
Here are just some of the reasons why having a BI layer gives you a Return-On-Investment even with small data volumes:
1. Data Integrity Issues
However small your data set, you almost certainly have issues with the data: it may not be clean; or may not contain all the information you need. So you end up manually discounting certain records, working round them on spreadsheets, renaming fields, re-grouping them and adding in the missing pieces. All by hand: every week, every month or every quarter.
This all takes time – which costs money – and is open to error.
A BI Tool automatically takes care of the cleaning, regrouping, renaming and data joining, once and for all.
2. Business Rules
You all have them – business rules that you have to apply to the raw data to make sense of what is actually happening. The trouble is, however simple or small your data-set, business rules can be complex. Creating these algorithms for each different department, or scenario, may not be something that you want to set in stone within your ERP system.
You may want to vary the rules but still have them running automatically. So one department may want to see how sales people are performing against Sales Budget, whilst another department may want to know how they are performing against Scrap Rate Targets.
A BI Tool can be configured quickly and easily to take into account new business rules, and then apply them automatically to your data.
3. Automatic Date Comparisons
Even when your data volumes are small, the figures make no sense unless you analyse the data in context – this is generally some sort of time comparison. Knowing percentages of uplift on sales are just as critical on 100,000 rows of data to a small business, as they are on 100,000 million rows to a bigger one. Comparing any dimension by values measured in time-related intervals, and being able to dive into the details, allows you quickly to discover things like: which sales person is underperforming this month; which product isn’t selling as much as it was last week, and which customers and products are giving you the most profit this year.
A BI Tool gives you interfaces to change the date comparisons on any dimension automatically, so you can interrogate your data within a meaningful context every time.
ABC for Analysis on Sales Data
So let’s go from being data miners to insight divers. Here is one example of an algorithm you can apply even to small data sets, giving you a much more accurate understanding of your most profitable customers and products.
Revenue is Vanity, Margin is Sanity
To start we’re going to find our Gross Margin and Gross Margin % (simple enough to calculate if you have both the unit price and unit cost in your data – Gross Margin % = (Unit Price – Cost)/Unit Price). When applied across all the sales data – (Sales Value – Cost Value)/Sales Value), this measure alone unlocks a few areas for us: for example, it can give us a way of checking that none of our sales people are offering larger discounts than we are expecting.
To do this we can focus on a few key products, and view their sales over a chosen period by the “Salesperson” dimension. We can sort the field “Gross Margin %” low to high. If the first salesman on the list is reporting a much lower margin than the rest, we can dive into the customers he/she sold to and then the individual products, to pinpoint if it’s a general problem or specific to particular customers or products.
ABC Analysis
Sometimes we assume that we know which products and customers are our most profitable – but we can often be surprised and even shocked once we perform an “ABC Analysis”, as they’re not always the ones that we gain the most revenue from.
Step 1
Using our BI Tool’s integrator interface, which allows us to carry out any number of calculations on the data, we work out the following (separately for both Products and Customers):
• Total Gross Margin for say 6 months (this can be any desired time period) on all products and customers
• Gross Margin per Product/Customer for the same period
Step 2
Sort the Products/Customers high to low by Margin Value for that period then define the Classification Thresholds e.g:
• Class A = Products/Customers making up Top 20% of Profit
• Class B = Products/Customers making up Next 50% of Profit
• Class C = Products/Customers making up Bottom 30% of Profit
• Class Z = Products/Customers that didn’t give us any Profit in that period
The Z Class Products could either be seasonal or non-selling items which we need to put on promotion and help our cash flow. Z Class Customers are those that have stopped buying from us and may need a call or a visit.
We are now seeing real insights – an accurate list of our key Customers and Products in terms of profit, which is the healthiest way of looking at sales. Maybe we will discover something else about them, for example that we are discounting too heavily on certain unique items that have no real competition and are in high demand, or accounts that are drifting away.
Fine Tuning the ABC Analysis
If we had Customer Category or Product Group we could run our ABC analysis within these areas separately, so we wouldn’t end up comparing, for example, our sales of cars against our sales of car parts.
We could also run an extra analysis on new customers and new products, which would allow us to calculate dynamically how many customers we retain or lose when looking at the current month versus previous months; and how new products are doing when they haven’t had time to add much to the total Gross Margin over the selected ABC Analysis period.
From this post you can see just a few examples of the phenomenal amount of extra information that can be derived from smaller datasets. So even though you may not have Big Data, you can still achieve Big Insights if you know where to look. Maybe you didn’t realise you had that bag on your head?
For a case study that highlights this approach, see our case study, Kellett Group case study.
About Debbie Lonsdale
Debbie Lonsdale has been working with the Diver Solution as a BI Consultant at Dynamic Business Informatics since 2008. Her previous experience includes computer programming, analytical and technical roles, team management, account management, sales and marketing in a variety of market sectors, including the Travel Industry and Distribution. She combines this experience as an all round ICT professional in the BI sector.
