If you are a lighting manufacturer, chances are you are sitting on a goldmine of idle data that could be actioned and distributed to your teams to make them more efficient. Before you can begin to reap the benefits of that data with dynamic dashboard reporting, you must first do the hard work of cleaning the data.
For those unfamiliar with the nuances in data analysis, it may seem silly that we must clean it. Why is cleaning the first step? Without a clean dataset, your reporting will be onerous and misleading. Garbage in, garbage out. I’ll illustrate this with an example.
Analyzing raw invoice data
Let’s say that you are simply going to take your invoice data in its raw form for analysis. Assume that this dataset includes key information on an invoice such as: the customer bill to, ship to, the sales agency or agent, the SKUs, pricing, and quantity.
Without cleaning, the dataset’s value is capped to answering basic questions such as overall sales, sales by agency, sales by customer, sales by SKU, average order by customer, etc. You will run into pitfalls when you start to go deeper to answer the why behind these numbers.
A common pitfall is data inconsistency. For whatever reason (human error, ERP changes, staff changes) data is never entered consistently in your ERP. If you want to see sales for a customer but that customer has 5 different names, then it becomes painful to manually group these five customers every time, or worse, it can be misleading if a user is unaware of this quirk in the dataset. Luckily, some basic renaming can be done to clean the field to make it more analysis friendly.
To see performance at the product family level is also difficult for the same reason. Since the raw dataset only had SKUs, you have to manually group 10-20+ SKUs together to make a family. Such a painstaking task should only be done in data cleaning, or better yet, automated. A solution for this is to add in a product family field, but maintaining this field is not easy. You may have thousands of SKUs and new SKUs are created every week.
Another pitfall occurs when doing comparisons between customers. You may be misled by not comparing apples to apples as some customers may be invoiced by branch and another by a group of branches, causing you to wrongly equate the two.
The difference of a clean dataset
Now let’s compare against a clean dataset. Consistent naming ensures that users are not misled by filters. Customer bill tos are grouped together at the distributor level to ensure fair comparisons. SKUs are also enriched with more useful details such as product family, category, model, voltage, CCT, and mounting, empowering your team to quickly dig deep into product questions.
When you turn this cleaned dataset into dashboards, your users will have a much easier time understanding the filters as it will be consistent and intuitive. Sales can trust the data to use in their next sales call. Product engineering can quickly do analysis on entire product families and its most popular options. Marketing can efficiently target key customers with personalized campaigns. More benefits here.
Automating SKU data cleaning
Despite the clear benefits of cleaning your data, there are huge frictions in practice, particularly at the SKU level. Not only do you need a way to enrich thousands of existing SKUs, but new SKUs are created every week. The data that you add to each SKU must also make sense for the common ways you want to filter, visualize, and report the data.
At my previous company, I used a mix of manual cleaning and automation with excel. The automation included many nested formulas and macros to break down a part number into its atomic bits, which got us data such as voltage, CCT, and control options. Excel, however, could only get us so far. It was useless for accessories and legacy products, which meant we still had to spend enormous effort manually cleaning. It was not a scalable solution for our needs.
Electrify was built to solve this problem as part of our mission to help manufacturers go digital. As you create product pages in Electrify, the SKUs associated to each page will automatically inherit the information of that page. SKUs also inherit the technical specs listed on its product configurator. With an Electrify product portal, your SKUs are cleaned and enriched as soon as they are put onto a product page.
Electrify was something I wish I had at my previous manufacturer, as it would have saved us many hours per week managing SKU data. It also would have made price lists much easier to create and manage. More on price lists in another blog.
