Wash your data before analyzing
Growing up my parents drilled it into my head to always wash my hands before eating. Little did I know that this innocent task would go on to consume a majority of my time in the coming years…
As part of the UC Davis Business Analytics program, I have the opportunity to work with Lucky Brand, a denim fashion company headquartered in L.A. Lucky looks to optimize its inventory allocation process using the power of data analytics. Armed with our analytical skills, my team and I were extremely excited to embark on this journey.
It took us a couple of weeks to wrap our heads around the industry jargons. The various fashion terms like fit, size, dimension, wash, leg opening, etc. were slowly growing on us and no sooner had we got a little comfortable than we were introduced to the dataset. We got access to Lucky’s inventory and sales data each file amounting to a couple of gigs in size.
As we traversed through the millions of rows of data the various irregularities and inconsistencies started popping up. Same fit names had different style names, same wash had different color IDs and then there was the surprise visit from the infamous ‘ND’ or no-dimension tag associated with some of the jeans. These inconsistencies were more than enough but is a dataset really complete without the occasional NULLs and N/As?
Get Ashish Ranshinge’s stories in your inbox
Join Medium for free to get updates from this writer.
So far I had only read of the 80/20 rule but never really had the opportunity to experience it. The 80/20 rule suggests that 80% of your output comes from 20% of your input. This idea originally introduced by Pareto really hit home. In the data science community too this phenomenon is observed. The 80/20 dilemma in the data science world suggests that analysts spend 80% of their time in cleaning and manipulating the data and only 20% in the actual analysis.
Before we could come up with any analysis it became evidently clear as to the importance of a clean structured database. Getting it into a standardized form was the first step we had to take before we deep dive into the analysis. We spent weeks just cleaning the data, trying to anticipate our current and future needs to structure the database, all the while ensuring there was no loss in information. In the process, we had created close to 10 new tables.
Eventually, we ran out of table names to assign and resorted to the infamous, ever-confusing ‘final’ suffix: tbl_inventory_final, tbl_inventory_final_v2, tbl_inventory_final_final…
Once we were done with our cleaning we were free to do what got us here in the first place. The actual analysis. Within a weeks time, we had our first product delivered to our client exceeding everyone’s expectations. Then came the tricky part. We were asked to further build on our recommendation and customize it for the different channels Lucky currently operates in. It should have been a straightforward procedure but it took a significant amount of time. The cause? Bad data. Turns out there were a couple of fields that were incorrectly populated and were a part of our calculations. But wait, that implied our previous calculations could also have been wrong! This was worrying. Immediately the entire team was alerted, we re-ran through our entire process and checked and double checked the answers to ensure there were no mistakes. Having resolved the discrepancy and avoided a potential meltdown, we executed the next part of the project and delivered our final recommendation.
In March, 2019 we officially wrapped up the project, hoping all those infinite versions had been effectively used. Our next project involves analyzing customer data. This dataset is even bigger than the previous one. With over 11 million rows and 100 columns, we have our work cut out to us. But before we move on to our next project one major item on everyone’s to-do list is to get rid of all the unwanted tables that are still lying idle in our repository.
