Finding the Best Liquor Store Location in Iowa
The skills the author demonstrates here can be learned through taking Data Science with Machine Learning bootcamp with NYC Data Science Academy.
Background and Objective
According to Mintel, between 2016 and 2021, spirit sales have outpaced those of beer and wine market. Because of this growing trend, there is opportunity to open a spirit’s store.
A prospective client is looking to tap an untapped niche by opening a liquor store targeting higher income millennials; staffed with people who are skilled in making at-home, primarily lower calorie cocktails. The objective is to identify the optimal location for this new store. Millennials are more calorie conscious about what they drink than other generations. The store would offer free samples of such drinks on the premises and sell not only alcohol but also the other ingredients that go into making mixed drinks. Since this is a bit of a risk, he doesn't want to open too big of a store.
The prospective client wants to open the liquor store in Iowa and is looking for the three best locations.
To do this I needed to:
- understand the existing market landscape of both the products and the stores.
- get an understanding of the demographics of the state.
Data Science Approach
- Perform an unsupervised clustering of the spirits products purchased by Iowa stores from the state managed Iowa Alcoholic Beverages Division.
- Use clusters from (1) to derive features to use in a clustering of the stores to segment the Iowa spirit's market into different store types.
- Leverage output from (2) to determine the distribution of store types in each zip code.
- Pair insights from (1)-(3) with zip code level demographic information available from the Census Bureau to identify the locations with the most promising locations based on both localized competition trends and zip code demographics.
Python Libraries Used
Iowa Liquor Sales Dataset
- This dataset was made publicly available by the state of Iowa and was compiled from sales data recorded by the Iowa Alcoholic Beverages Division over the period 2012 to 2021
- For this project, only 2021 data was used given pre-2021 data was considered to be less directly relevant to a new business entering the market.
- Iowa is an alcoholic beverage control state which means that the state is a monopolistic wholesaler of spirits.
- Retailers purchase spirits directly from the Iowa Alcoholic Beverages Division.
Census Demographic Datasets
- Data from the United States Census Bureau’s American Community Survey for 2020 at the zip code level in Iowa (population, age, housing, income, educational attainment etc.).
- Data from the United States Census Bureau’s American Community Survey for for 2015 at the zip and state level (population).
Zip Code Database
Data from the United States Postal Service (primary city, county and state associated with each zip code).
Data Cleansing and Preparation
Iowa Liquor Sales Dataset
Each row of the Iowa Liquor Sales data is a purchase by a store of a specific product from the state of Iowa. There were 2.6 million purchases (2.6 million rows) in the year 2021. Note that the python csv library was used to extract the data for the year 2021 from the state sales file that dates back to 2012.
Since category names can be inconsistent, a categories DataFrame was created using the most recent category name based on transaction date.
Since the store number might have more than one store name associated with it a stores DataFrame was created using the most recent store name based on transaction date.
Since a product item number might have more than one product description associated with it, a products DataFrame was created using the most recent description.
A transactions DataFrame which contains all the transactional information. It excludes category name, store name and product description.
For each of these DataFrames, csv files were created so that they can be joined together when needed in the project.
The transactions file, the categories file and the products file were used to create the dataset used in the product clustering. Note that products with a volume of fifty milliliters and priced over 10 dollars were excluded because of data quality issues with state data.
After the sub-clustering of the stores, a file called final clusters, where each row is a product, was created. The file is comprised of cluster name, product number, product description and category name.
The csv files final clusters, transactions and stores were used to create a file called store_trans_cluster . To create that file, these steps were taken.
- transactions and stores were merged
- the file from (1) was merged with final clusters
From store_trans_cluster using groupby aggregation three files were created.
- the number of unique items in each store and the sum of revenue in each store called unique_revenue
- the sum of revenue in each cluster for each store
- (1) and (2) were merged to get the percentage of revenue in each store resulting in a file called
Pandas pivot_table was used to create a file from stores_revenue_combined called stores_rev_pct where each row is a store and each column is the percent revenue from each cluster in each store. Columns with missing values were imputed with zero because every store did not have revenue for every cluster.
stores_revenue_combined was concatenated with stores and unique revenue using store number as the index to createstores_clust_info_combined2 which was written to csv to be used in the clustering of the stores.
A file of the number of stores in each zip code was created.
A file with the number of convenience stores, large stores, medium sized stores and smaller sized stores in each zip code was created after the clustering of the stores.
Census Demographic Datasets
The following tables from the American Community Survey were used.
- DP02: Selected Social Characteristics
- DP03: Selected Economic Characteristics
- DP04: Selected Housing Characteristics
- DP05: ACS Demographic and Housing Estimates
The first column of each table is the Label (Grouping) such as: Total Population, Male and Female. The other columns are for each Zip Code : the Estimate, Margin of Error, Percent and Percent Margin of Error
Note the Census Bureau refers to Zip Codes as ZCTA (Zip Code Tabulation Areas).
For DPO3, DP04 and DP05:
- The Label (Grouping), and Zip Code Estimate columns were extracted.
- Based on the Label (Grouping) column values only the rows with the information of interest were selected.
- Using a function and list comprehension a list of new columns names was created so that apart from Label (Grouping) all the other columns names would be the zip codes of the state of Iowa.
- The DataFrame was transposed so that the zip codes became the rows and the values of Label (Grouping) became the columns.
- The DataFrame was exported to csv.
For DP02, the same process was followed except the Zip Code Percent columns were extracted instead of the Estimate columns.
The DataFrames DP03,DP04, DP05, Dp02 and the zip code database from the post office were concatenated using the zip code as the index and a csv was created for analysis.
Commas were removed from numeric values and dashes were replaced with zero. A dash in Census data means that the population of a zip code is too small to make an estimate of a given variable. A file called census_combined4 was written to csv.
The following files were merged with census_combined4 for analysis:
- file of the number of total stores in each zip code
- file with the number of convenience stores, large stores, medium sized stores and smaller sized stores in each zip code based on the store clusters.
The number of total stores, convenience stores, large stores, medium sized stores and smaller sized stores were all imputed with zero.
Initial Product Clusters
Products were clustered using the continuous variables: state bottle price, bottle volume and bottles sold.
Given that there were not any categorical variables other than product category (e.g. whiskies, vodkas etc.) , K-Means was considered optimal to use in this case vs K-Modes and K-Prototypes.
With K-Means, initially K centroids are created at random. Each point is assigned to the centroid that it is closest to using Euclidean distance. Centroids are recalculated after each cluster is formed. A centroid is simply a vector of means. New clusters are formed using the new centroids and points are assigned to their closest cluster. The process continues iteratively until no new clusters can be formed.
As the K-means algorithm will assign every observation to one of k clusters with each computation contributing equally to the computation of the new means for that new centroid, considerable care should be taken to remove any outliers that may otherwise heavily distort these mean computations.
First products with bottle volumes over 3,000 milliliters and products with a retail price above $500 were removed.
A product with over 2 million bottles sold is an additional outlier that was removed.
After outliers were removed, the features were standard scaled using sklearn library's Standard Scaler utility for use in the K-means clustering of the products.
The inertia plot indicated four clusters. The silhouette score indicated the same number of clusters.
The four clusters developed are:
- High End
- Volume For Dollar
- Popular Bottles
- Everything Else
High End stands out because the median prices are so much higher than the products in the other clusters.
Volume For Dollar stands out because the median volume is so much higher than it is for the other clusters
Popular Bottles stands out because the median bottle quantity sold is so much greater compared to other clusters.
Nothing stands out about Everything Else other than it consists of such a large number of unique products and a lot more revenue when compared to the other clusters.
Since Everything Else has nothing to distinguish it from the other clusters and has the most revenue, a sub-clustering of Everything Else was performed. Note that the silhouette scores for five, six and seven clusters were lower than than that of four clusters.
Sub-clustering of Everything Else
A K-means clustering on Bottle Price, Bottle Volume and Bottles Sold was used to develop the sub-clusters.
Before the sub-clusters were developed, the features were standardized.
The inertia plot for the sub-clustering of Everything Else indicates four clusters.
The four sub-clusters are:
- Quick Shots
- Least Expensive
- Most Variety
Quick shots stands out since it has a higher cost per ml than the other sub-clusters partially due to the relatively smaller size of these bottles shown through Quick Shot's median bottle size being only 200 ml versus 750 ml for the three other sub-clusters.
Expensive stands out here. The bottle price is so much higher than it is for the other sub-clusters.
Moderately Priced is the least expensive. The bottle prices of Quick Shots cannot be directly compared to the others because the bottles are so much smaller and are more expensive on a price per ml basis.
Most Variety has the largest variety of products (over 2,000) compared to the other sub-clusters. On the other hand, Least Expensive has the smallest variety with only 136 different products.
Final Product Clusters
There are seven product clusters when the initial product clusters are combined with the sub-clusters of Everything Else.
- Most Variety (Everything Else sub-cluster)
- Expensive (Everything Else sub-cluster)
- Quick Shots (Everything Else sub-cluster)
- Moderately Priced (Everything Else sub-cluster)
- Volume for Dollar
- High End
- Popular Bottles
High End stands out here. Expensive is higher priced than the other remaining clusters.
Popular Bottles generates the most revenue in each product category on a per product basis. Least Expensive follows.
Final Product Clusters Liquor Category Breakdown
For the state of Iowa, whiskies are the number one alcohol type with vodkas ranking #2. This aligns with vodka being one of the most popular alcohol types nationally.
Here the five key product clusters are compared. Whiskey dominates in every product cluster including Expensive. There is also some expensive Tequila.
Given that High End accounts accounts for less than one percent of the revenue of every store on average and given Quick Shots suffered from data quality issues, five of the original seven product clusters were used to feature engineer variables to use in the clustering of the stores.
I clustered the stores using the total number of unique products in each store and five features engineered from the product clusters.
The five features engineered are the percentage of revenue from each store from each of the following product clusters.
- Most Variety
- Moderately priced
- Popular Bottles
- Volume For Dollar
Before clustering, outliers needed to be removed:
First stores with number of unique products over 1,750 were excluded.
Secondly 18 outlier stores, discovered during an initial clustering, were excluded. The outliers are mainly distilleries and not stores. A distillery is where hard liquor like whiskey, gin, vodka, rum, etc. is made on the premises.
Features were standardized before clustering.
The inertia plot for the clustering of the stores indicates four clusters. The silhouette score indicated the same.
The four store clusters are:
- Convenience stores
- Smaller stores
- Medium sized stores
- Large stores
Apart from convenience stores, the clusters were named based on store revenue.
Store Clusters by Top 5 Store Types
Casey’s General Store is the largest convenience store chain in Iowa with 508 stores. Fifty percent of the convenience stores in Iowa are a Casey’s General Store.
Hy-Vee is the largest supermarket chain in Iowa. They typically sell a wide variety of Spirits. They also have dedicated liquor stores and some gas stations.
Fareway Stores is the second largest supermarket chain in Iowa.
Mom and pop stores and small chains fall into the other category.
Large stores have the largest number of unique products and Convenience stores have the smallest.
Convenience stores have limited shelf space and mainly sell Popular Bottles.
Popular Bottles make up a smaller percent of store revenue at Large stores than they do in the other store clusters.
Volume for Dollar with a low price per milliliter are mainly carried at Medium and Large sized stores.
Moderately Priced make up a larger percent of store revenue at Smaller sized stores.
Putting it all together with census data
Since my prospective client's goal is to open a liquor store targeting higher income millennials, the locations selected (zip codes) must fit this demographic.
To assess degree of local competition, I created the following variables at the zip code level.
- 2020 population/2015 population
Median household income index
- (median household income in zip code) / (median household income for the state) * 100
Median home market value index
- (median home market value in zip code) / (median home market value for the state) * 100
Median age index
- (median age in zip code) / (median age in state) * 100
- (total population) / (land area in square miles)
To access competition, I created the following for each zip code.
Total Number of Stores
- Number of Convenience stores
- Number of Smaller stores
- Number of Medium sized stores
- Number of Large stores
- Population per store (excludes convenience stores)
- (total population ) / (number of stores - number of convenience stores)
Population per store is a good indicator of how much competition there is. Convenience stores were left out of the calculation because they mainly carry Popular Bottles. My prospective client is interested in open a niche liquor store which will not be selling just Popular Bottles. Convenience stores are not a direct competitor.
As Convenience stores are not considered to be a direct competitor, only Smaller, Medium and Large stores are considered to be potential competitors to the new store entrant in this analysis.
Location Selection Process
I selected locations (zip codes) that met the following criteria and sorted by population per store
My selection criteria :
- Population growth > 1
- To identify zip codes with positive population growth from 2015 t0 2020
- Median household income index > 100
- Median home market value index > 100
- Population density > 900
- Median age index < 100
My prospective client also wanted an area that is somewhat urban since the market research said to think young, think urban. Initially, instead of using the population density as a selection criteria I used population > 10,000. However, although that resulted in a larger number of locations (18), it included some rather sparsely populated areas. Some zip codes selected using that criteria were spread out over 100 square miles.
A population density over 900 is high for Iowa given the average population density across the entire state is just 56.
When population density was used instead it narrowed it down to just nine.
Of the nine, the top five locations based on population per store were further evaluated. They are
- Iowa City
- Des Moines
Data Analysis of Top Five
While Iowa City has the largest population per store, Ankeny has the largest population.
Des Moines has the zip code which is the highest in population density. Waukee is the lowest.
There was a twenty percent increase in the populations of Urbandale, Ankeny and Waukee. Meanwhile, the populations of Iowa City and Des Moines have increased less than ten percent.
Ages for millennials range from 26 to 41.
The closest that I could come to that using census data is 25 to 44.
Ankeny, Waukee, and Des Moines have a higher percent of the population in the target age group than Iowa City and Urbandale. With respect to median age, Iowa City is the youngest which is most likely because the University of Iowa is located there.
The graph of the percent of households with income $75K plus looks very similar to that of median household income. Urbandale, Ankeny and Waukee are much higher in income than Iowa City and Des Moines. With respect to median household income, Iowa City and Des Moines are just slightly above the median for the state which is $61,836.
The chart below paints detailed picture of the competition. Des Moines clearly has the most competition. It also has a medium sized liquor store and a small liquor store. In addition, it's median income is only slightly above the median for the state and it has the smallest increase in population. For these reasons, I would not recommend this location.
Note that there are other zips code in Des Moines than the ones shown throughout this analysis. It is a city of over two hundred and twenty thousand.
Iowa City has the least competition. However, the median income is only slightly above that of the state, the population has grown less than ten percent from 2015. It has lowest median age which as mentioned is most likely because the University of Iowa is located there. Lastly, it has the smallest percent of the population age 25 to 44 which is just below what it is for the entire state of Iowa. Since it doesn't target my prospective client's desired demographic, I would not recommend this location.
That leaves Urbandale, Ankeny and Waukee. They are all high income and have populations that have grown over twenty percent from 2015. Growing populations mean more customers.
- Competition: It has the least competition with just one medium sized store. A small niche store has a good chance here.
- Geographic Area: It is the smallest in terms of square miles at 10.
- Income: It has the highest median income at $144,180.
- Age Demographics: 25.8% of the population is age 25 to 34 which is two percentage points above what it is for the state as a whole.
- It is the oldest in terms of median age at 36.
- Population: It has the smallest population at almost fifteen thousand people.
- The population has grown 22% since 2015.
- Competition: In terms of competition, it has one large store and two medium sized stores. It has no small stores at all. The large store is a Hy-Vee supermarket. One of the medium sized stores is a Fareway supermarket. The other is a drug store. Those store formats are very different than that of my prospective client. A small niche store could fit in here.
- Geographic Area: It is 21 square miles, the new store could be physically located at a distance from the other stores.
- Income: It has a median income of $91,974
- Age Demographics: 33.8% of the population is age 25 to 44.
- It is the youngest in terms of median age at 32.
- Population: It has the largest population of the three with just over forty thousand people.
- The population has grown 32% since 2015.
- Competition: In terms of competition it has the most with three large stores, one medium sized store and one small store. The three large stores area a Hy-Vee supermarket and two stores that sell liquor and tobacco. The medium sized store is a Fareway supermarket. The smaller store is a small Hy-Vee market. A small niche store could fit in here.
- Geographic Area: It is 27 square miles. A new store could be located at a distance from the others.
- Income: It has a median income of $98,546.
- Age Demographics: 34.6% of the population is age 25-44.
- The median age is 34.
- Population: It has a population of almost twenty seven thousand.
- The population has grown 34.7% since 2015.