__STYLES__
This is a simple case study that I wanted to do for fun. During my last couple of weeks working in retail, I figured I would collect some data from the jewelry department for analysis.
COMPANY
Hobby Lobby, Inc. is a collection of over 900 retail stores across 48 states, which sell mainly crafting supplies, home décor and seasonal merchandise. In 1970, David and Barbara Green sold picture frames from their home and started this business that continues to grow. Hobby Lobby is now the largest privately owned arts-and-crafts retailer in the world with over 43,000 employees. Hobby Lobby offers over 70,000 items featuring crafts, art supplies, baskets, candles, cards and party items, fabrics, florals, frames, garden, hobbies (plastic models, trains, science projects), home accents, jewelry-making supplies, needlework, scrapbooking products, holiday items, small furniture, wearable art, and picture frames.
GOAL
The main goal of this project was to explore which types of “Color Gallery” beads are more profitable in past sales and if there are any connections in sales based on product and merchandise layout. Results can potentially be used to make better decisions when choosing what, how and where to place certain beads in the layout to gain more profit in bead sales.
Questions:
DATA
The data set I sued is an Excel spreadsheet created by myself, using data directly sourced from physical corporate made shipping data sheets for only our store in Sterling Heights, MI. These pages list the products that were shipped on that date, and include details like names, SKUs, quantity shipped, retail price, etc. I personally and carefully entered all the data into the spreadsheet myself.
Data Limitations:
PROCESS
The majority of this project was done using just Microsoft Excel and Tableau. I did plug my data into SQL for some extra experience. I did find it helpful to grab some quick number totals, but I could have done those in Excel also.
Clean Data: Using Excel Spreadsheets Checked for missing data. Using the “Filter” function, I was able to check for any missing fields. I found several descriptions, measurements, colors, and strand quantities that were either missing or unclear in the shipping documents. I did some research on the company e-commerce website to find the missing details for each SKU.
Format data: Made sure each column had corrected formatting (dates formatted as dates, numbers formatted as integers, descriptions, colors, and materials formatted as strings, etc.).
Manipulate Data:
Results: Based on these quick findings, I can see that the top selling beads were Glass (219), followed by Stone (138). The top selling color, by a landslide, is the mixture of multiple colors in one SKU (66), followed by Green (39). Green may have sold more during this time due to the Holiday season. The other two most selling colors are Purple (34) and Crystal AB (32). These seem legit, as I remember ordering a lot of these two colors over the years. The most popular selling bead size is 8mm.
Based on the average numbers for Panel, Section and Peg, it appears that most sales come from the middle. Panel 3 is close to the middle of the 5 total panels, section 8 is in the middle of the 16 total sections and peg 7 is in the middle of the 14 total pegs in each section. This is an interesting result that will be further analyzed.
ANALYZE
Query Data Using SQL Uploaded the data set into SQL BigQuery on GoogleCloud for further analysis. This also ended up being a very quick way to get some totals.
Analysis Using “WHERE”, I was able to filter through my data table and find how many results returned for each Panel, Section and Peg numbers.
This shows most sales came from panel 5, followed by 1 and 4. Panel 5 consists of mostly Crystal, Crystal AB, White, Silver, Gray and Black colors. Panel 1 is Red, Orange, and Yellow. Panel 4 has Pink and Purple. The top selling section is number 9, followed by 3 and 13 and then 8. Sections are important to show us position within the panel. These are consistent with being within the middle and near the top area of the panel. The top selling peg number is 4, followed by peg 10 and 9. This shows a unique trend in sales that shows higher sales around the middle of the sections, but not exactly in the middle.
Using the same method, I tweaked the code to show me only the numbers for each specific section.
VISUAL
Created data visualizations using Tableau:
CONCLUSIONS
Based on this analysis, I can conclude the following:
What Action Do We Take?
Beads:
Location/Layout:
Concluding Notes: It would be interesting to test a layout based on material (or any other variable) and conduct the same exact analysis to see how it would compare. This would give us an even better insight into whether these variables make a difference in sales.
I also noticed that not having a complete SKU list and full inventory list of all Color Gallery Beads for the weeks recorded put me at a disadvantage. Having that information could give us more insights into the orders and products overall.
Overall, this project is not a great sample to make a full analysis for decision-making. I believe this would be more accurate and useful with a larger data set, collected over the span of a year or longer.