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

1. Perform an unsupervised clustering of the spirits products purchased by Iowa stores from the state managed Iowa Alcoholic Beverages Division.
2. 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.
3. Leverage output from (2) to determine the distribution of store types in each zip code.
4. 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

• csv
• pandas
• numpy
• sklearn
• matplotlib
• seaborn

Datasets

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).

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.

Outlier Treatments

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
• Expensive
• 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.

They are:

• 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.

Clustering 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
• Expensive
• Moderately priced
• Popular Bottles
• Volume For Dollar

Outliers

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.

Inertia Plot

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.

Feature Engineering

To assess degree of local competition, I created the following variables at the zip code level.

Population growth

• 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

Population density 

• (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
• Urbandale
• Ankeny
• Wauke
• 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.

Recommended Cities

Urbandale

• 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.

Ankeny

• 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.

Waukee

• 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.