Data Analysis: Diving Deep into the Hy-Vee Stores
The skills I demoed here can be learned through taking Data Science with Machine Learning bootcamp with NYC Data Science Academy.
Will Han Github | LinkedIn
Gary Lin Github | LinkedIn
Background Information
Here is a data-related quote from The Simpsons:
“Here’s to alcohol, the cause of, and solution to, all life’s problems.”
Although the statement is in jest, it is undeniable that the underlying sentiment—that alcohol consumption pervades our everyday life—holds true. It is no surprise then that alcohol sales make up an important channel of grocery stores’ revenue streams. According to this Rabobank article, "alcohol accounts for 4.6% of total revenues" in a typical B&M supermarket.
To gain more insight into alcohol sales and trends, we decided to look at liquor sales data from Iowa. The Iowa public data had class E license liquor sales records in the state from 2012 to the present, with 24 explanatory features including date, store number, county, and other details of the alcohols sold. The data had more than 20 million observations.
To be able to provide actionable insights, we decided to narrow down our focus and perform a deep dive into Hy-Vee stores. Our goals were to provide valuable insights for Hy-Vee through:
- Initial data cleaning to handle inconsistencies in the data
- Store and product analysis by categories and location over time
- Store segmentation to find the most valuable store
- Market basket analysis
- Time series and sales forecasting to predict future sales
Data Cleaning
The data contained various columns on transaction details including ~300 vendors, ~2,400 liquor stores (before limiting to Hy-Vee), and ~9,400 liquor products. Due to the nature of a large amount of data collected over a long period of time, there were multiple inconsistencies that needed to be fixed including:
- Same liquor getting mapped to different categories or names
- Same store associated with non-unique longitude & latitude coordinates and non-standardized store names
- Standardization needed to be applied on store name, city, and county
- Same vendor and store having more than one name over time
To fix the inconsistencies, we decided to use the most recent labeling based on the unique IDs in order to be up-to-date. Our reasoning was that data management improves with time and using the latest labels would theoretically minimize labeling errors.
After that, we limited the data to Hy-Vee specific records and created a relational database using SQLite as shown in Figure 1 to make table sizes more manageable. We also removed duplicates while creating each table in order to prevent repetition and redundancy.
Figure 1 Data
Store & Product Data Analysis
Now focusing on Hy-Vee specific data, we first looked at the count of stores in Iowa by county as shown in Figure 2. Polk County stood out with 31 stores, followed by other counties averaging around 5. We also pulled the county population data from the Census Bureau and plotted the count of stores vs. population by county as shown in Figure 3. Generally, they seemed to have a positive correlation: more populated counties had more stores and vice versa.
Figure 2
Figure 3 Data
Then Hy-Vee stores were grouped into four different categories: food & drug, food, general, and wine & spirits. We looked at the number of unique stores by category over the entire time period in the data as shown in Figure 4 and saw that there was a huge growth in general stores over time when the other types stayed fairly stable.
In order to look into this further, we graphed mean store life for each group and general stores seemed to have the lowest at ~1,500 compared to the other types at more than 3,000 days as shown in Figure 5. That, however, is most likely due to the large number of general stores opening starting 2019. We will return to this point below.
Figure 4 Data
Figure 5 Data
In order to see what kind of sales trend triggered so many general stores opening, we looked into average sales per store by category as shown in Figure 6. Average sales seemed to be increasing overall, with food & drug stores leading in sales most likely due to the size of stores. General stores, however, showed a decrease in average sales starting in 2019 again resulting from new stores opening. So increase in general stores was probably not caused by an increase in liquor sales.
Figure 6 Data
What was causing the overall increase in sales? In order to answer this, we grouped the products into ten categories and graphed their sale volume over time as shown in Figure 7. Whiskies and vodka were the products driving the sales with a growing trend over the years (almost x3 over 10 years). Other categories, on the other hand, were fairly stable throughout.
Figure 7 Data
Store Clustering
We then looked into how the stores could be grouped using an unsupervised clustering algorithm DBSCAN to see what stores were similar. Two different methods were used: by product category sales (Figure 8) and by location (Figure 9). The location clustering was as expected where counties with many Hy-Vee stores were grouped together. The product category sales clustering, however, was very interesting as some stores in Polk County seemed to have similar sales behavior as the ones in Linn County.
Comparing the two clustering maps, we see that the cluster groups are quite different, depending on the features used for clustering. The Hy-Vee management team may take that into consideration when planning what products to place in different stores using the grouping shown in Figure 8.
Figure 8 Data
Figure 9 Data
Data on Store Segmentation
Then we used another unsupervised learning and the Recency, Frequency, and Monetary Value (RFM) method to segment stores into the following three categories:
- Low Value: infrequent buyers and generate low revenues
- Mid Value: somewhat frequent buyers and generate modest revenues
- High Value: frequent buyers and generate high revenues
The RFM method uses the following dimensions for segmentation:
- Recency: how recently did the customer purchase?
- Frequency: how often do they purchase?
- Monetary Value: how much do they spend?
We used the elbow method on k-means clustering to come up with the number of groups for store segmentation and decided on four groups. The stores were run through the RFM method, each getting assigned a score of 0-3 based on recency, frequency, and monetary value. Then an overall score was calculated by adding the three scores, determining what category the store belongs to. Figure 10 shows the overall RFM score vs. sales volume and we clearly saw that one high-value store (store #2633) had dominating sales volume.
Figure 10
Store segmentation was then plotted on the map as shown in Figure 11. This was very interesting as Polk County had a lot of low-value stores where Linn and Scott (the second and third populated counties following Polk) had more mid to high-value stores.
We also plotted a heat map of the number of stores per 1,000 population as shown in Figure 12. This showed that southern Iowa had a low number of stores compared to other counties, but it lined up with Figure 11 where they were determined to be low-value.
Figure 11
Figure 12
Market Basket Analysis (MBA) Data
Next, we wanted to see what products at Hy-Vee stores are sold together the most so the management team can optimize the product placement.
To make this analysis more feasible, we limited the MBA to the top store (store #2633) and the top 100 items from the store. Hawkeye Vodka seemed to be a very popular product as it was a value purchase with 1.75ml of alcohol for less than $15. Some of the other items that we saw were also low to mid-price points. So we could see that the Hy-Vee customers may be working with a fairly low price point, although there was still a good variety of liquor purchases even at that price.
Time Series & Sales Forecasting
Lastly, we tried to look into how Hy-Vee stores can allocate their resources more efficiently by predicting what future sales will look like through time series forecasting. First, we performed decomposition on sales volume into trend, seasonality, and residuals to look at variations in all stores as shown in Figure 13. The following were observed from the graphs:
- It showed a clear break in the trend around 2019, accelerating the increase
- There was a clear seasonality towards the end of the year coinciding with the national holidays
- There seemed to be more variance in residuals towards the latter years (post-2019) which hints some unobserved factors playing a big factor
Figure 13
For comparison purposes, we applied the same decomposition method to the high-value store (store #2633) as shown in Figure 14. The following were observed from the graphs:
- There was a steep decline and a break in trend starting late 2019
- There was a huge variation of residuals which again hints that some unobserved factor unrelated to historical trend and seansonality that is driving the change in sales volume
Figure 14
Then for forecasting, we experimented with two models: ARIMA and SARIMAX. For the ARIMA model, we investigated the parameters and chose the best one manually whereas the SARIMAX library automatically optimized the model. As seen in the comparison of both models in Figure 15, ARIMA seemed to result in a better in-sample prediction at least visually. However, we decided to use the SARIMAX model since there is obvious seasonality in liquor sales. SARIMAX also ended up performing better in out-of-sample prediction, as we demonstrate below.
Figure 15
In order to calculate the accuracy of our SARIMAX forecasting model, we downloaded another iteration of the liquor sales data with more runout (the original data was pulled in April 2021 and the refresh was pulled in August 2021). We then ran the forecasting model through the April 2021 version of the model to predict sales through August and compared the prediction against actual sales as shown in Figure 16.
Figure 17 shows the mean absolute error of the prediction, which averaged ~$800,000 over the five months. Compared to a mean sale of ~$8,000,000, the error was not negligible, but given that we used a very naive SARIMAX model without using any population data or economic indicators, we concluded that the model performed reasonably well as a first attempt.
Future improvements to the model include collecting additional covariates, including Iowa population and demographic trends and economic indicators.
Figure 16
Figure 17
Conclusion & Recommendations
After performing multiple analyses on store locations and the evolution of sales volume by categories and stores, one potential thing for Hy-Vee management to look into is whether Hy-Vee is opening too many stores too close to each other. If so, there could be a substantial substitution effect between different stores where the consumers are going to the most convenient stores, causing other stores to not sell up to their potential.
Another important observation we had was the steep decline and break in the trend of the high-value store (store #2633). It seemed like the break occurred before COVID-19, so it is not 100% attributable to the pandemic. This is most likely due to some outside factor that was not accounted for in our decomposition and is worth looking into starting from the stores that are in proximity. One possible explanation is that Hy-Vee is attempting to increase its market share presence by opening more stores.
Lastly, we recommend Hy-Vee to use our forecasting model to inform decisions about resource allocation between stores. One way is to allocate resources proportional to each stores' current share of the total sales volume and use the predictions for future staffing.
About Authors
William Han
View all posts by William Han >