2. Not “Just” Sales Reports – part 2
In the second blog post in this series, we asked the DBI team to give examples of some key projects they have worked on which illustrate that working with our customers often extends to include some development projects that take us far from the basic sales reports, which are a given in most implementations.
Typically when we speak about our jobs, we tend to over-simplify our roles and say something broad like, “we create reports using our customers’ data to enable them to identify where they need to take actions, to help improve their business outcomes”. It’s easier to use sales reports as an example as these are available in just about every ERP or BI system. But as you will find out from the examples described below, we have to be flexible and creative in finding ways to plug information gaps or free up business process deadlocks, as most data comes from silos that cannot speak to each without some external mechanism that can bring it all together.
Here are 5 more examples from: Olatz Iturbe, Jer Colbert, James Grace, Anthony Wright and YY Tsang.
1. Olatz Iturbe (Senior BI Consultant DBI)
As a kind of side-effect of sales processing we can also deliver insights into Customer and Product profiling. This involves working out margin values over an agreed period (e.g. 18 months) which we generally parameterise to best fit the organisation we are working with. We apply categories to a sorted list of either Customers or Product by the margin, high to low, making A the highest category delivering e.g. 10% of the margin, B the next category delivering the next 20 % of margin and so on until we have 4 categories A, B, C, D plus a fifth category Z, where that group has delivered no margin over the agreed analysis period. This quickly identifies if a business is too heavily reliant on an unusually small number of products or customers (e.g less than 3 % of customer or products providing 10 % of margin), or if they have large numbers of products that are slow moving or never selling. It also allows them to address the problem of customers who have drifted away over the last 18 months – after a few years using this type of analysis there should be very little in the Z class.
Because all of our reports allow filtering by Customer or Product classifications, this ABC analysis can be fine-tuned to allow for seasonality or buying trends and the outliers can be taken care of by staff who specialise in the different product classes.
2. Jer Colbert (BI Consultant DBI)
Another application that relates to managing the relationships between wholesalers, retailers and manufacturers is our centralised portal for supplier trading agreements. I work with one of our larger customers using this SaaS, supporting their suppliers. Typically each year, suppliers need to sign a trading agreement with a retailer that details service level agreements, including financial data from the previous year, rebates, other supplier contributions like trade shows and marketing supports, and sets out how the vendor will deal with aged stock, shortages and their plans for the next year.
In the Trading Agreement portal, we create all the pages, including Terms and Conditions, that are required in the final document for signature. The dynamic data elements for each supplier are populated by Diver scripts, taking the relevant data from the ERP system or from user inputs to the portal.
This results in a streamlined, more automated process each year and saves a lot of administrative time and effort.
3. James Grace (BI Consultant DBI)
As an in-house application, we have a timesheets system that allows us to keep track of our consultancy time, so that we can keep accurate records about which customers we have worked for on what date, and whether the time is support (non-chargeable) or application development, report or dashboard design (chargeable).
We also use a case management tool which automatically generates cases for each request that comes in, and we allocate each case to the correct customer and status. Fields from the case management tool are used as identification fields in our timesheets database. Our Diver application scripts then read all the timesheet data and build a Timesheets data model. This is the source for all of our customer reports for contracts, billing, support management and tracking projects, and is at the core of what we do every day.
4. Anthony Wright (Senior BI Consultant DBI)
I’ve worked on quite a number of non-standard projects over the years – mainly in the retail sector. One project involved us integrating and processing large volumes of disparate retail data which had been fed back to a wholesale organisation so that it was in the correct, standardised format required by another third-party application. This enabled the wholesaler to use predictive intelligence to make daily forecasts for their retailer customers about which products they needed to purchase to fulfil demand.
Another major project using the Input Technology (that John used in the Rebates module) has been to create an application to manage Retail Promotions. In much the same way as rebates, the promotions are typically managed using multiple spreadsheets and these have to be shared and edited by many different users. Allowing all the data to be centralised into an online portal using Diver input tables for edits means that every stage of the promotions process is accessible and editable to everyone in the team, at the point they need to work on it. Diver also allows us to take care of any complex calculations and business rules using Diver Workbench tools, so it’s all bundled up in a neat package.
5. YY Tsang (Senior BI Consultant DBI)
Nearly all of my projects involve designing something new that would not be classed as “just” sales reports. I’ve worked a lot on pharmacy and dispensary data: in addition to sales I have created insights on patient visits, drug schemes, claims and drug ingredients.
Another application we have delivered helps a wholesale company manage the incoming sales files from over 800 retail stores. It collects meta-data or information about these files coming in from each store and presents details of data that is either present or missing, by store and by data supplier, according to each store’s opening days. The information is presented on a dedicated portal accessible to those responsible for providing the data. This allows missing files to be identified immediately and then uploaded to the data centre. As part of this module the application allows the data providers to add details of when stores were closed due to refurbishments or other unusual reasons, and also captures changes in providers or symbol type. This application is central to the continuous capture of all the data required to help monitor the complexities of supply and demand which keep the retail stores running at optimum stock levels.
In part 1 of “Not Just Sales Reports” we heard from Bob Corr, John Spilllane, Paul Duggan, Debbie Lonsdale, Elaine Breen and Lorenzo Lumanlan.
