Color Gallery Product Analysis

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:

1. Which materials sell the most/least?
2. Which colors sell the most/least?
3. Which panel sells the most/least beads?
4. Which Section sells the most/least beads?
5. Which peg sells the most/least beads?
6. What size beads sell the most/least?
7. Do multiple strand beads sell better than single strands?

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:

1. Data was entered into the spreadsheet manually – Not ideal, as humans do make mistakes.
2. Data does not include beads in other branded categories, such as “Jewelry Shoppe” and “Metal Gallery”.
3. Time of the year the data recorded has items labeled as “N”. This means new products are being added to the layout that have been shipped, but do not have a location yet, nor any order history to review. These new beads cannot be included in this project.
4. Many SKUs were out of stock at the main warehouse for several weeks, causing them to be ordered every week until they were back in stock. If I include all these repeating SKU counts, it may skew the actual weekly sales data. Therefore, I will be only looking at beads that are in stock and were able to be shipped.
5. This project does not reflect any results or recommendations for placement of “Color Gallery” beads within the department or store. Our findings can only be applied within the panels already in place.
6. Data collected only represents one store. With a total of over 900 stores across the country, this is not a good representation of the entire company’s sale trends. To get an accurate sample size, we would need to gather data from at least 90 stores.
7. Data was only collected for 9 weeks. Data collected over a longer time span would have more accurate and dependable results.
8. Data was also collected during peak Christmas holiday shopping season. Holiday shoppers aren’t necessarily buying as many crafting supplies as they would toys, gifts and other seasonal products. It may not be representative of bead sales throughout the whole year.
9. Because of the holiday rush and holiday stock, time was cut short for normal ordering hours, causing some weeks to decline drastically in overall orders. This is not a good representation of normal weekly sales.

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:

1. First, I started a new sheet and copied the entire table over. This way I will not be manipulating the original datasheet.
2. Because I am only looking at products that shipped, I removed all SKU entries that were out of stock and not shipped. Using the “Filter” function, I filtered to only show SKUs with the shipping Qty of “0” and deleted these rows. Then un-filtered the data.
3. In another sheet, I did some simple Excel calculations to look at a few totals. I used the “COUNTA()”, “COUNTIF()” and “UNIQUE()” functions to count rows given certain criteria. I used the “AVERAGE()” function to find averages.

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:

1. Imported final CSV data files into Tableau Public to create visualizations for each analysis.
2. For design, I was able to grab a snippet of the Color Gallery tag to create a PNG Logo using Adobe Photoshop. for my dashboard. I used the colors from the logo and incorporated them into my data visuals. This gives the dashboard a cohesive color scheme.
3. I used basic line and bar graphics to keep things simple.
4. I added heat map visuals for the layout analysis. Because I was unable to create a full 5 panel view, I divided it up to show Panels, Sections and then Pegs.

CONCLUSIONS

Based on this analysis, I can conclude the following:

1. Multi-colored beads are the highest seller with over $442.00 in sales. Copper is the worst seller with just over $15.00 in sales.
2. Glass beads make up 49% of the beads sold. Bone is the least selling material at 0% with only 1 sale.
3. Strands with multiple bead sizes brought in 115 with over $811.00. 11mm beads only brought in 1 sale at $4.99. 20mm and 22mm also only had 1 sale each, but with a higher price tag.
4. Single-strand SKUs are far more popular than multiple strands with 945 orders totaling $1,899.
5. The total number of sales made from panel 5 was significantly higher than others. Panel 3 had the least. (Note: There were a lot of SKUs in panel 3 that were out of stock at the warehouse.)
6. There was a small trend of more sales coming from the middle areas, slightly towards the top. There is an interesting significant drop in section 10.
7. Pegs # 4 and # 10 have the most sales, which shows customers are drawn to the innermost pegs, but not directly center, as shown with the low totals on peg 8.

What Action Do We Take?

Beads:

1. Customers like getting more for their money.
2. They enjoy getting a variety of colors in a single purchase and tend to buy strands of beads that include more than one size bead. Company buyers can use this information to help with decision making when choosing new beads for future sales.

Location/Layout:

1. Panel 5 consists of black, white, clear, and silver beads. These colors seem to be a staple for any jewelry crafter.
2. Because there were a significant number of out-of-stock SKUs in panel 3, I cannot fully commit to the conclusion that most sales will come from the center of the panels.
3. I do see a trend in sales where items are near eye-level on the panel, so people do tend to shop more when they don't have to look or reach up or down. With more data, we can confirm this is the case. This information is very useful for the company to create layouts that will sell products better. It can also be useful at the store level. Managers and employees can create visuals and merchandise products at eye-level to help drive sales.

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.