My team has been tasked with identifying a potential area of growth where Regork could invest resources to increase revenue and profits. We have decided to take a look into alcohol sales by demographic and are these customers also purchasing chips at the same time.
What months of the year are alcohol sales the highest, who is doing that purchasing, and are they also purchasing chips? How can we further increase these sales together.
We will utilize the Regork customer and product data to run some analysis and provide recommendations based upon our findings. We will compare monthly alcohol sales and monthly chip sales. We will also compare the age group and income bracket demographics of who is doing the purchasing of these two items. We will conclude with analyzing if these people are purchasing both alcohol and chips at the same time, or if different demographics are purchasing each. After careful analysis we can identify growth opportunities beneficial to to Regork.
For the analysis we will conduct for Regork, we are using the Complete Journey dataset. The data represents grocery store shopping transactions over one year.
library(completejourney) - data sets characterizing
household level transactions
library(tidyverse) - system of packages for data
manipulation
library(dplyr) - makes data manipulation easier
library(ggplot2) - produces data graphics
library(lubridate) - makes manipulating dates and times
easier
library(ggTimeSeries) - offers date and time
visualizations
library(scales) - helps with legend and axis naming
values
There are a few steps to complete to get ready for the data analysis.
transactions <- get_transactions()
households <- demographics
products <- products
We start by running analysis of alcohol purchases by month. When we do this we see that sales are highest for alcohol during the months of May, June, July, August and December, with the largest spike in sales in the month of July.
products %>%
filter(str_detect(product_type, regex("(beer)", ignore_case = TRUE))) %>%
inner_join(transactions, by = "product_id") %>%
mutate(month = lubridate::month(transaction_timestamp)) %>%
group_by(month) %>%
summarize(alcohol_product_count = n()) %>%
ggplot(aes(x = month, y = alcohol_product_count)) +
geom_line(color = "blue") +
geom_point(color = "black") +
labs(
x = "Month",
y = "Number of Alcohol Sales Transactions",
title = "Alcohol Sales by Month"
) +
theme_minimal() +
scale_x_continuous(breaks = 1:12, labels = month.abb)
And we also need to run analysis of chip purchases by month.When we do this we see the same pattern as alcohol sales, highest during the months of May, June, July, August and December, with the largest spike in sales in the month of July.
products %>%
filter(str_detect(product_type, regex("(chip)", ignore_case = TRUE))) %>%
inner_join(transactions, by = "product_id") %>%
mutate(month = lubridate::month(transaction_timestamp)) %>%
group_by(month) %>%
summarize(alcohol_product_count = n()) %>%
ggplot(aes(x = month, y = alcohol_product_count)) +
geom_line(color = "blue") +
geom_point(color = "black") +
labs(
x = "Month",
y = "Number of Chip Sales Transactions",
title = "Chip Sales by Month"
) +
theme_minimal() +
scale_x_continuous(breaks = 1:12, labels = month.abb)
Let’s run a combined alcohol and chip sales by month, and look particularly at the months of May, June, July, August and December.
# Filter alcohol sales data
alcohol_sales <- products %>%
filter(str_detect(product_type, regex("(beer)", ignore_case = TRUE))) %>%
inner_join(transactions, by = "product_id") %>%
mutate(month = lubridate::month(transaction_timestamp)) %>%
group_by(month) %>%
summarize(alcohol_product_count = n())
# Filter chip sales data
chip_sales <- products %>%
filter(str_detect(product_type, regex("(chip)", ignore_case = TRUE))) %>%
inner_join(transactions, by = "product_id") %>%
mutate(month = lubridate::month(transaction_timestamp)) %>%
group_by(month) %>%
summarize(chip_product_count = n())
# Combine alcohol and chip sales data
combined_sales <- full_join(alcohol_sales, chip_sales, by = "month")
# Plot combined sales data with grouped bars
ggplot(combined_sales, aes(x = month)) +
geom_bar(aes(y = alcohol_product_count, fill = "Alcohol Sales"), stat = "identity", position = "dodge", alpha = 0.7) +
geom_bar(aes(y = chip_product_count, fill = "Chip Sales"), stat = "identity", position = "dodge", alpha = 0.7) +
labs(
x = "Month",
y = "Number of Sales Transactions",
title = "Alcohol and Chip Sales by Month"
) +
scale_fill_manual(values = c("Alcohol Sales" = "blue", "Chip Sales" = "lightblue"),
labels = c("Alcohol Sales", "Chip Sales"),
name = "") +
theme_minimal() +
scale_x_continuous(breaks = 1:12, labels = month.abb) +
theme(legend.position = "top") +
geom_rect(aes(xmin = 4.5, xmax = 8.5, ymin = -Inf, ymax = Inf), fill = NA, color = "red", linetype = "dotted", size=1.5) +
geom_rect(aes(xmin = 11.5, xmax = 12.5, ymin = -Inf, ymax = Inf), fill = NA, color = "red", linetype = "dotted", size = 1.5)
Next, we analyze the purchase patterns of alcohol and chips by demographics, which includes income and age groups. Again, we see mostly the same trend between the two. The age group doing the most purchasing are 45-54 followed by 35-44. We found the income range for the majority of the sales was $25-100k, more specifically the $35-74k range.
Analyze alcohol purchases by demographics first.
# alcohol_df setup
alcohol_df <- products %>%
filter(str_detect(product_type, regex("(beer)", ignore_case = TRUE))) %>%
inner_join(transactions, by = "product_id") %>%
inner_join(households, by = "household_id")
# Group by demographic variables and calculate total alcohol purchases
alcohol_by_demographics <- alcohol_df %>%
group_by(income, age) %>%
summarise(total_alcohol_purchases = n())
# Plot the total alcohol purchases by demographic groups
ggplot(alcohol_by_demographics, aes(x = income, y = total_alcohol_purchases, fill = age)) +
geom_bar(stat = "identity", position = "dodge") +
labs(
title = "Total Alcohol Purchases by Demographic Groups",
x = "Income",
y = "Total Alcohol Purchases",
fill = "Age Groups"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 50, size = 8))
Then analyze chip purchases by demographics.
# chip_df setup
chip_df <- products %>%
filter(str_detect(product_type, regex("(chip)", ignore_case = TRUE))) %>%
inner_join(transactions, by = "product_id") %>%
inner_join(households, by = "household_id")
# Group by demographic variables and calculate total chip purchases
chip_by_demographics <- chip_df %>%
group_by(income, age) %>%
summarise(total_chip_purchases = n())
# Plot the total alcohol purchases by demographic groups
ggplot(chip_by_demographics, aes(x = income, y = total_chip_purchases, fill = age)) +
geom_bar(stat = "identity", position = "dodge") +
labs(
title = "Total Chip Purchases by Demographic Groups",
x = "Income",
y = "Total Chip Purchases",
fill = "Age Groups"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 50, size = 8))
Let’s combine these into one plot to show our findings. For the purpose of this analysis we will cut off income range over $100k because the sales dropped off drastically.
# Calculate total alcohol purchases
alcohol_by_demographics <- alcohol_df %>%
group_by(income, age) %>%
summarise(total_purchases = n())
# Calculate total chip purchases
chip_by_demographics <- chip_df %>%
group_by(income, age) %>%
summarise(total_purchases = n())
# Combine alcohol and chip data
combined_data <- bind_rows(
mutate(alcohol_by_demographics, product = "Alcohol"),
mutate(chip_by_demographics, product = "Chip")
)
# Filter out income over $100k and "250k+"
combined_data <- combined_data %>%
filter(as.numeric(gsub("[^0-9]", "", income)) <= 100000 & income != "250K+")
# Plot combined data
ggplot(combined_data, aes(x = income, y = total_purchases, fill = age)) +
geom_bar(stat = "identity", position = "dodge") +
facet_wrap(~ product, scales = "free_y") +
labs(
title = "Total Purchases by Demographic Groups",
x = "Income",
y = "Total Purchases",
fill = "Age Groups"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 50, size = 8))
Furthermore, let’s dig into the data and find out the top ten selling alcohol and chip products.
# Group by product_id and calculate total sales for alcohol and chips
alcohol_sales <- alcohol_df %>%
group_by(product_id) %>%
summarise(total_sales = n())
chip_sales <- chip_df %>%
group_by(product_id) %>%
summarise(total_sales = n())
# Get the top 10 product_ids for alcohol and chips
top_alcohol_products <- alcohol_sales %>%
top_n(10, total_sales) %>%
arrange(desc(total_sales))
top_chip_products <- chip_sales %>%
top_n(10, total_sales) %>%
arrange(desc(total_sales))
# Print the top 10 alcohol and chip products
print(top_alcohol_products)
## # A tibble: 10 × 2
## product_id total_sales
## <chr> <int>
## 1 849202 202
## 2 910473 151
## 3 878715 135
## 4 906844 118
## 5 1108094 116
## 6 1034176 110
## 7 1065538 104
## 8 1019637 94
## 9 860248 91
## 10 1004436 88
print(top_chip_products)
## # A tibble: 10 × 2
## product_id total_sales
## <chr> <int>
## 1 5568378 753
## 2 9526410 531
## 3 9527290 438
## 4 5569327 373
## 5 5568729 309
## 6 859010 245
## 7 9527494 243
## 8 1022053 238
## 9 921744 230
## 10 7440663 225
Let’s plot the top ten selling products for both alcohol and chips.
# Group by product_id and calculate total sales for alcohol and chips
alcohol_sales <- alcohol_df %>%
group_by(product_id) %>%
summarise(total_sales = n())
chip_sales <- chip_df %>%
group_by(product_id) %>%
summarise(total_sales = n())
# Get the top 10 product_ids for alcohol and chips
top_alcohol_products <- alcohol_sales %>%
top_n(10, total_sales) %>%
arrange(desc(total_sales))
top_chip_products <- chip_sales %>%
top_n(10, total_sales) %>%
arrange(desc(total_sales))
# Combine top alcohol and chip products
top_products <- bind_rows(
mutate(top_alcohol_products, product_type = "Alcohol"),
mutate(top_chip_products, product_type = "Chip")
)
# Plot the top alcohol and chip products
ggplot(top_products, aes(x = reorder(product_id, -total_sales), y = total_sales, fill = product_type)) +
geom_bar(stat = "identity", position = "dodge") +
labs(
title = "Top Alcohol and Chip Products by Total Sales",
x = "Product ID",
y = "Total Sales",
fill = "Product Type"
) +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
How often are the top 10 alcohol and chip products purchased together?
# Filter transactions for the top 10 alcohol and chip products
top_alcohol_transactions <- transactions %>%
filter(product_id %in% top_alcohol_products$product_id)
top_chip_transactions <- transactions %>%
filter(product_id %in% top_chip_products$product_id)
# Find common basket_ids between alcohol and chip transactions
common_basket_ids <- inner_join(
top_alcohol_transactions,
top_chip_transactions,
by = "basket_id"
)
# Count the number of occurrences of common basket_ids
common_basket_counts <- common_basket_ids %>%
group_by(basket_id) %>%
summarise(num_occurrences = n())
# Get the total number of occurrences
total_occurrences <- nrow(common_basket_counts)
# Print the total number of occurrences
print(total_occurrences)
## [1] 192
What percentage of the top 10 alcohol transactions had chip sales also? We find that only 9.61% do.
# Total number of transactions for alcohol and chip products
total_alcohol_sales <- nrow(top_alcohol_transactions)
total_chip_sales <- nrow(top_chip_transactions)
# Calculate the percentage of transactions where both alcohol and chip products are purchased together
percentage_joint_purchases <- (total_occurrences / min(total_alcohol_sales, total_chip_sales)) * 100
# Print the percentage of joint purchases
print(percentage_joint_purchases)
## [1] 9.60961
Let’s take it a step further and find the top five chip products also purchased with alcohol.
# Identify baskets with both alcohol and chip products
common_baskets <- inner_join(
alcohol_df,
chip_df,
by = "basket_id"
)
# Count occurrences of each chip product in common baskets
chip_counts <- common_baskets %>%
filter(product_id.y %in% top_chip_products$product_id) %>%
count(product_id.y) %>%
arrange(desc(n))
# Get the top 5 chip products bought with alcohol
top_chip_with_alcohol <- chip_counts %>%
top_n(5, n) %>%
arrange(desc(n))
# Print the top 5 chip products bought with alcohol
print(top_chip_with_alcohol)
## # A tibble: 5 × 2
## product_id.y n
## <chr> <int>
## 1 5568378 92
## 2 5568729 64
## 3 9527290 53
## 4 9526410 49
## 5 5569327 38
# Define a custom color palette
my_colors <- c("#1f78b4", "#33a02c", "#e31a1c", "#ff7f00", "#6a3d9a")
# Plot the top 5 chip products bought with alcohol
ggplot(top_chip_with_alcohol, aes(x = reorder(product_id.y, n), y = n, fill = reorder(product_id.y, n))) +
geom_bar(stat = "identity") +
scale_fill_manual(values = my_colors) + # Apply custom color palette
labs(
title = "Top 5 Chip Products Bought with Alcohol",
x = "Chip Product ID",
y = "Number of Occurrences"
) +
theme_minimal() +
theme(
axis.text.x = element_text(angle = 45, hjust = 1),
panel.grid.major = element_line(color = "gray", linetype = "dashed"), # Add grid lines
panel.grid.minor = element_blank(), # Remove minor grid lines
legend.position = "none", # Remove legend
plot.title = element_text(size = 16, face = "bold") # Adjust title font size and style
)
We did find that sales for both alcohol and chips followed the same sales trend with the months of May, June, July, August and December being the highest sales months for each. Our analysis also showed that about 10% of purchases have these two products being purchased at the same time.
Our findings included data showing that May, June, July, August and December are the highest sales months for both alcohol and chips. The age group purchasing the most is 45-54 and income group purchasing the most is $35-74k. We found that 9.72% of purchases have a combined alcohol and chips transaction, but we identified the top five selling chip products that sell with alcohol.
Based on our findings we have a few recommendations for Regork. Regork should run promotions on purchasing alcohol and chips together, with a focus on the already top five chip products with alcohol combination. Regork should ramp up store displays showcasing these products together, particularly moving the chips closer to the alcohol. This would lead to more people grabbing each product. We also recommend increasing sales during the lower sales months by offering coupons around key holidays and events such as Superbowl Sunday, St. Patrick’s Day, Easter, Thanksgiving, etc. And lastly, we weren’t able to fully analyze the total sales dollars impact of the transactions but focused more on the frequency of transactions. Additional analysis is recommended to find specific products that may bring in the most dollar value vs just increased sales volume.