During the 2020 COVID-19 pandemic, grocery stores saw significant increases in alcohol consumption with the channel’s market share growing to 43.8% - the highest it had been in five years.1 This increase is notable to grocery stores as a 2018 study found the when a consumer purchases wine, they spend nearly 60% more during their shopping trip. 2 With inflation causing changes in consumers’ shopping habits, it important for Regork to understand consumers’ alcohol purchasing habits to maintain its market share.
Using data on households’ transactions, including their demographics and products purchased, we can determine who is purchasing what alcoholic products and when.
These insights allow us to make strategic recommendations to Regork’s CEO regarding which customer segments to target with specific alcoholic products. By understanding what products in this category are being purchased and the primary segments purchasing them, Regork can tailor promotions and displays to entice customers to make a purchase.
The completejourney data set is a data set characterizing household level transactions over one year from a group of 2,469 households who are frequent shoppers at a grocery store.
completejourney - data sets characterizing household level transactions
tidyverse - collection of packages for data manipulation, exploration and visualization
ggplot2 - visualization
dplyr - data manipulation
stringr - for easier statistical computing environment working with strings
lubridate - for easier statistical computing environment working with date-time data
RColorBrewer - collection of color palettes for graphs
DT - helps make data tables easier to read
library(completejourney)
library(tidyverse)
library(RColorBrewer)
library(DT)
For this analysis, I began by identifying the different alcoholic product categories, which are beers/ale, wine, and liquor. While liquor has its own department, Spirits, beer and wine are included in the Grocery department. I filtered the products data set for all products in the Spirits department and the alcoholic products in the Grocery department.
alcohol <- products %>%
filter(str_detect(department, "SPIRIT|GROCERY")) %>%
filter(str_detect(product_category, regex("wine|beer|liquor", ignore_case = TRUE)))
After filtering for these products, I joined the alcohol data set with the transactions and demographics data sets for easier analysis.
Alcohol Transactions
transactions <- get_transactions()
alc_transactions <- transactions %>%
inner_join(alcohol) %>%
inner_join(demographics)
All Transactions
df <- transactions %>%
inner_join(products) %>%
inner_join(demographics)
Liquor Transactions
In the Spirits department, there are over 35 product types. These include distinctions like “Flavored Rum” vs. “Traditional Rum” or a “42 Under Proof” version of a spirit. To allow for a more general understanding of liquor purchases, I adjusted the product_type variable to consolidate different product types for the same spirit into one product_type (ex. different types of rum into one “RUM” product_type).
liquor <- alc_transactions %>%
filter(str_detect(product_category, regex("liquor", ignore_case = TRUE))) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("whiskey|SCOTCH", ignore_case = TRUE)), "WHISKEY/SCOTCH", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("gin", ignore_case = TRUE)), "GIN", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("tequila", ignore_case = TRUE)), "TEQUILA", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("vodka", ignore_case = TRUE)), "VODKA", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("rum", ignore_case = TRUE)), "RUM", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("cocktail|cktls", ignore_case = TRUE)), "PREPACKAGED COCKTAILS & MIXES", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("LIQUEUR", ignore_case = TRUE)), "LIQUEURS", product_type)) %>%
mutate(product_type = ifelse(str_detect(product_type, regex("SCHNAPPS", ignore_case = TRUE)), "SCHNAPPS", product_type))
This analysis begins with understanding overall alcohol sales, including which product categories are performing best and sales trends throughout the year.
alc_transactions %>%
group_by(product_category) %>%
mutate(product_category = ifelse(str_detect(product_category, regex("WINE", ignore_case = TRUE)), "WINE", product_category)) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
labs(title = "Total Sales by Product Category",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2")
We then look at overall alcohol sales throughout the year.
alc_transactions %>%
mutate(transaction_month = month(as.POSIXct(transaction_timestamp, format="%Y-%m-%d %H:%M:%S %Z"))) %>%
mutate(product_category = ifelse(str_detect(product_category, regex("WINE", ignore_case = TRUE)), "WINE", product_category)) %>%
group_by(transaction_month, product_category) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(transaction_month, desc(total_sales)) %>%
ggplot(aes(x = transaction_month, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
scale_x_continuous(breaks = 1:12, labels = month.abb) +
labs(title = "Total Sales by Month and Product Category",
x = "Month",
y = "Total Sales",
fill = "Product Category") +
theme(legend.position = "right") +
scale_fill_brewer(palette = "Dark2")
While there is some fluctuation throughout the year, there is a significant increase in sales during the month of December.
alc_transactions %>%
filter(month(as.POSIXct(transaction_timestamp, format="%Y-%m-%d %H:%M:%S %Z")) == 12) %>%
mutate(day_of_month = day(as.POSIXct(transaction_timestamp, format="%Y-%m-%d %H:%M:%S %Z"))) %>%
mutate(product_category = ifelse(str_detect(product_category, regex("WINE", ignore_case = TRUE)), "WINE", product_category)) %>%
group_by(day_of_month, product_category) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(day_of_month, desc(total_sales)) %>%
ggplot(aes(x = day_of_month, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity", position = "stack") +
labs(title = "Total Sales by Day in December",
x = "Day of December",
y = "Total Sales",
fill = "Product Category") +
theme(legend.position = "right") +
scale_fill_brewer(palette = "Dark2")
As to be expected, alcohol sales tend to increase right before major holidays in the month of December, such as Christmas and New Year’s Eve.
Demographics
This analysis focuses on the relationship between alcohol purchases and consumers’ ages and income levels. We first examine these demographic factors separately to identify the most prominent groups.
alc_transactions %>%
group_by(product_category, age) %>%
mutate(product_category = ifelse(str_detect(product_category, regex("WINE", ignore_case = TRUE)), "WINE", product_category)) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
facet_wrap(~age) +
labs(title = "Total Sales by Age Group",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
alc_transactions %>%
group_by(product_category, income) %>%
mutate(product_category = ifelse(str_detect(product_category, regex("WINE", ignore_case = TRUE)), "WINE", product_category)) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
facet_wrap(~income) +
labs(title = "Total Sales by Income",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
From this analysis we can identify the top age group and income level for alcohol purchases. 45-54 year olds are the top age group for alcohol purchases, and people with an income of $50-74k lead in sales for all three alcohol categories.
We can then look at the purchasing habits of 45-54 year olds with an income of $50-74k.
alc_transactions %>%
filter(age == "45-54", income == "50-74K") %>%
group_by(product_category) %>%
mutate(product_category = ifelse(str_detect(product_category, regex("WINE", ignore_case = TRUE)), "WINE", product_category)) %>%
summarise(total_sales = sum(sales_value)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
labs(title = "Alcohol Product Categories for Age 45-54 and Income 50-74k",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
It is also important for us to understand what other products this segment is primarily purchasing.
df %>%
filter(age == "45-54", income == "50-74K", department == "GROCERY") %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Categories for Age 45-54 and Income 50-74k",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
While the beer product category is primarily one product type, the wine and liquor categories can be further analyzed by their various products. These also serve as the biggest growth opportunities for individuals aged 45-54 with an income level of $50-74k who primarily purchase beer but are leading consumers of all three categories.
The two primary product types in this category are Domestic Wines and Imported Wines. We want to understand which of these types are most popular and who is purchasing them.
wine_pct <- alc_transactions %>%
filter(str_detect(product_category, regex("Wine", ignore_case = TRUE))) %>%
group_by(product_category) %>%
summarise(total_sales = sum(sales_value)) %>%
mutate(percentage = round(total_sales / sum(total_sales) * 100, 2)) %>%
arrange(desc(percentage))
datatable(wine_pct)
alc_transactions %>%
filter(str_detect(product_category, regex("Domestic Wine", ignore_case = TRUE))) %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types for Domestic Wine by Total Sales",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
We also want to see the top products for the demographics we are focusing on, which are age and income level.
alc_transactions %>%
filter(str_detect(product_category, regex("Domestic Wine", ignore_case = TRUE))) %>%
group_by(product_type, income) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types for Domestic Wine by Income Level",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "RdYlGn") +
facet_wrap(~income) +
theme(axis.text.x = element_blank())
alc_transactions %>%
filter(str_detect(product_category, regex("Domestic Wine", ignore_case = TRUE))) %>%
group_by(product_type, age) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types for Domestic Wine by Age",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "RdYlGn") +
facet_wrap(~age) +
theme(axis.text.x = element_blank())
alc_transactions %>%
filter(str_detect(product_category, regex("Domestic Wine", ignore_case = TRUE))) %>%
filter(age == "45-54", income == "50-74K") %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Domestic Wine Products for Age 45-54 and Income 50-74k",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
There is a wide variety of preferences across age and income levels in the domestic wine category. Younger consumers appear to prefer both Premium and Popular 750mL wines. The 45-54 year olds do not show a clear preference for any type, and the 55-64 year olds lead Boxed wine sales. A wide variance is also observable across income levels, but it is worth noting that households with an income of $50-74k show a strong preference for a good deal with domestic win and prefer Boxed wines and Value Glass wine.
alc_transactions %>%
filter(str_detect(product_category, regex("Imported Wine", ignore_case = TRUE))) %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types for Imported Wine by Total Sales",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
We also want to see the top products for the demographics we are focusing on, which are age and income level.
alc_transactions %>%
filter(str_detect(product_category, regex("Imported Wine", ignore_case = TRUE))) %>%
group_by(product_type, age) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types for Imported Wine by Age Group",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "RdYlGn") +
facet_wrap(~age) +
theme(axis.text.x = element_blank())
alc_transactions %>%
filter(str_detect(product_category, regex("Imported Wine", ignore_case = TRUE))) %>%
group_by(product_type, income) %>%
summarise(total_sales = sum(sales_value)) %>%
arrange(desc(total_sales)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types for Imported Wine by Income Level",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "RdYlGn") +
facet_wrap(~income) +
theme(axis.text.x = element_blank())
alc_transactions %>%
filter(str_detect(product_category, regex("Imported Wine", ignore_case = TRUE))) %>%
filter(age == "45-54", income == "50-74K") %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Imported Wine Products for Age 45-54 and Income 50-74k",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
Across all age levels, Australian/New Zealand wines are the most popular, and the same is true for almost every income level with the exception of Italian wines being most popular among households with an income of $125-149k. However, for the specific segment we are analyzing, French wines are most popular.
We next want to better understand consumers’ purchases in the Spirits department.
liquor_pct <- liquor %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
mutate(percentage = round(total_sales / sum(total_sales) * 100, 2)) %>%
arrange(desc(percentage))
datatable(head(liquor_pct,10), options = list(pageLength = 10))
liquor %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Types in Liquor Category",
x = "Product Type",
y = "Total Sales",
fill = "Product Type") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
The top products are Pre-packaged Cocktail Mixes and Whiskey/Scotch. Together, they make up over 50% of liquor sales.
Since we have identified individuals aged 45-54 with an income of $50-74k as a top segment, we want to understand what liquor products they are primarily purchasing.
liquor %>%
filter(age == "45-54", income == "50-74K") %>%
group_by(product_type) %>%
summarise(total_sales = sum(sales_value)) %>%
slice_max(order_by = total_sales, n = 5) %>%
ggplot(aes(x = reorder(product_type, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity") +
labs(title = "Top 5 Product Categories for Age 45-54 and Income 50-74k",
x = "Product Category",
y = "Total Sales",
fill = "Product Category") +
scale_fill_brewer(palette = "Dark2") +
theme(axis.text.x = element_blank())
Similar to overall liquor sales, this demographic group’s top product is Whiskey/Scotch. However, Rum is more popular than prepackaged cocktails and mixes, indicating a stronger preference for straight liquor.
In this project, I sought to answer the business question What consumers are purchasing alcohol and what are they purchasing? to help Regork continue to capture these sales that increased significantly during the COVID-19 pandemic.
How I Answered This Business Question
I started by identifying how alcohol transactions were categorized in the data because products like wine and beer are considered grocery while liquor is housed in a separate department. After filtering for all alcohol transactions across departments, I was able to analyze the performance of the three categories - beer, wine, and liquor. I looked at the performance of each category throughout the year and across two demographic groups: age and income level. I identified the top segments as ages 45-54 and income levels of $50-74k. I then further dissected the data and looked at purchasing trends within the different wine and liquor product types both overall and with a focus on these demographic groups. I wanted to identify the top products in this demographic group, so Regork can offer specific deals tailored to the segment’s preferences.
My Recommendations
This analysis showed customers aged 45-54 with an income level of $50-74k have a strong preference for beer, but I wanted to identify new opportunities for the segment. In the wine category, this group lead purchases of Boxed wines, Value Glass wines, and other value wines. In the liquor category, they prefer Whiskey/Scotch and Rum, unlike other segments who prefer Pre-packaged Cocktails to straight liquor. I also found this segment’s other top purchases include Juice and Soft Drink cans.
Since this segments other top products include non-alcoholic beverages that are also commonly used as mixers, I would recommend Regork sending coupons to this segment for commonly purchased non-alcoholic beverages or offering deals on these products in stores. The store should then establish displays for this products near liquor products, so customers will associate the two together and purchase the liquor they normally mix with these beverages. For example, if Regork offers a deal on cans of Coca-Cola and sets up a display outside of the liquor store, customers will grab a bottle of their favorite Whiskey when they see the soda is on sale.
As for wine, this data can guide Regork’s selection of in-store wine samples. Alcohol samples for shoppers 21 and older is becoming more common, and while stores often highlight new products or more premium products to encourage those purchases, Regork should offer samples of popular boxed and value wines during the times most popular for 45-54 year olds to shop. If they are offered a sample of a wine product and then notice the great price on the product, they will be encouraged to purchase it.
Limitations
Since this analysis indicated a strong preference for price and value among the targeted segment, I would love to have been able to further delve into the association between coupons and promotions and alcohol purchases. It will be important to better understand the ideal price point for this demographic when creating coupons and selecting the products to promote to the group.
While I identified the top month and days within that month for alcohol purchases, this analysis would be improved by someone further exploring seasonal trends in alcohol purchases.