Regork Grocery

Introduction

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.

Packages Required & Data Preparation

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 

Data Analysis

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
  )

Summary

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 less than 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. Only 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 recommend that our analysis is further built upon preparing Regork to run a targeted marketing campaign to reach the demographics of ages 19-34 and 55+ and income ranges of under $25k and over $100k.