1.Introduction

The problem we aim to solve is how to increase the revenue and profit of this mature chain supermarket by analyzing past sales data.
The United States is the world’s second-largest beer consumption market, second only to China, with a total consumption exceeding $110 billion in 2022. Analysis shows that single individuals are the largest consumer group of beer, and the population trend in the United States is an increasing proportion of singles, indicating significant growth potential for beer. Therefore, in this study, we used several databases, including Transaction, demographics, and promotions, to identify who purchases the most beer, when beer sales peak, and which promotional methods are most effective. Additionally, we used Transaction and product data to identify products with the lowest sales amounts and quantities, which will serve as a crucial basis for reducing inventory.

2.Packages Required

ggprism:Provides various themes, palettes, and other functions that are used to customise ggplots to look like they were made in ‘GraphPad Prism’.
ggalt:A package containing additional geoms, coords, stats, scales & fonts for ggplot2 2.0+.
patchwork:The ‘ggplot2’ package provides a strong API for sequentially building up a plot, but does not concern itself with composition of multiple plots.
library(completejourney)
library(dplyr)
library(ggplot2)
library(readr)
library(tidyverse)
library(ggalt)
library(ggcharts)
library(lubridate)
library(forcats)
library(scales)
library(ggprism)
library(patchwork)
library(gridExtra)
library(data.table)
library(readr)
library(patchwork)

3.Data Preparation

# Load data of transactions,promotions,demographics and products

transactions <- get_transactions()
promotions <- get_promotions()
demog <- demographics
prod <- products

# Join transactions,promotions and products

df <- transactions %>% inner_join(promotions) %>% inner_join(products)

# Join transactions and demographics

demo_transa <- inner_join(demog, transactions)

# Join transactions and demographics

demo_transa_prod <- left_join(demo_transa, prod)

4.Exploratory Data Analysis

4.1 Increase Revenue(Increase sales volume)

4.1.1 Beer Sales Demographics: Targeting the Key Customers

Before we find solution to increase the sales of beer, it will be good to know the demographics of beer buyers. This information is crucial for tailoring marketing and promotional efforts to the key customers.

Findings:

It highlights that people aged 45-54, with an income range of 50k-99k, and no kids are the primary consumers of beer.

# Extract month information from column "transaction_timestamp"
# Convert the numeric month value to three-letter abbreviations
# Convert the month abbreviations to factor values with a specified order

demo_transa_prod$month <- month(demo_transa_prod$transaction_timestamp)
demo_transa_prod$month <- month.abb[demo_transa_prod$month]
month_order <- c("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
demo_transa_prod$month <- factor(demo_transa_prod$month, levels = month_order)

# Set the format for values about sales
custom_format <- function(x) {
  paste0("$ ", format(x, big.mark = ",", scientific = FALSE))
}    

# As we embark on our research focused on beer-related data, let's narrow our dataset to exclusively include information pertaining to 'BEERS/ALES'.

beer_data <- demo_transa_prod %>% 
  filter(product_category == "BEERS/ALES")
# Plot 2017 Beer total sales by age group
p1 <- beer_data %>% 
  group_by(age) %>% 
  summarise(total_sales = sum(sales_value)) %>% 
  ggplot(aes(x = age, y = total_sales)) +
  geom_bar(stat = "identity", fill = "#C5944E") +
  labs(x = "Age",
       y = "Total Sales Value") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 90, hjust = 1)) + 
  theme_prism(base_fontface = "plain", 
              base_family = "serif", 
              base_size = 10,  
              base_line_size = 0.8, 
              axis_text_angle = 90) + 
  scale_y_continuous(labels = custom_format)


# Plot 2017 Beer total sales by income group

p2 <- beer_data %>%
  mutate(income = fct_collapse(income, 
                               "< 50K" = c("Under 15K", "15-24K", "25-34K", "35-49K"),
                               "50-99K" = c("50-74K", "75-99K"),
                               "100-149K" = c("100-124K", "125-149K"), 
                               "150-199K" = c("150-174K", "175-199K"))) %>%
  group_by(income, product_type) %>%
  summarise(total_sales = sum(sales_value)) %>%
  ggplot(aes(x = income, y = total_sales)) +
  geom_bar(stat = "identity", fill = "#D79A40") +
  labs(x = "Income",y = "") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 90, hjust = 0.5)) + 
  theme_prism(base_fontface = "plain", 
              base_family = "serif", 
              base_size = 10,  
              base_line_size = 0.8, 
              axis_text_angle = 90) + 
  scale_y_continuous(labels = custom_format)

# Plot 2017 Beer total sales by kids count

p3 <- beer_data %>% 
  group_by(household_comp, product_type) %>% 
  summarise(total_sales = sum(sales_value)) %>% 
  ggplot(aes(x = household_comp, y = total_sales)) +
  geom_bar(stat = "identity", , fill = "#C0B57B") +
  labs(x = "Household\nComposition",
       y = "") +
  theme_minimal() + theme(axis.text.x = element_text(angle = 90, hjust = 0.5)) +
  theme_prism(base_fontface = "plain", 
              base_family = "serif", 
              base_size = 10,  
              base_line_size = 0.8, 
              axis_text_angle = 90) + 
  scale_y_continuous(labels = custom_format)

# Put 3 plots on the same page

horizontal_arrangement <- p1 + p2 + p3 +
  labs(title = "2017 Beer Sales by Age, Income, and Household Composition") +
  theme(plot.title = element_text(hjust = 1.1, size = 12, face = "bold"))

horizontal_arrangement

4.1.2 Consumer Preferences for Beer Types and Packaging

There are different types and packages of beer, so we would want to know the preference of the customers.

Findings:

The plots offers a clear picture of the most preferred beer types and package sizes among customers.
It’s evident that alcoholic beer in 12 oz packages significantly outperforms other options in terms of sales.

This information is crucial for inventory management and marketing strategies.
By prioritizing the stock of alcoholic beer in 12 oz packages, the grocery store can cater to the preferences of its customer base and potentially increase sales even further.

# Show the preference of beer type by total sales

beer_type_sales <- beer_data %>% 
  group_by(product_type) %>% 
  summarise(total_sales = sum(sales_value)) 

mycols <- c("#EFC000FF", "#C0B57B", "#CD534CFF")
type <- ggplot(beer_type_sales, aes(x = 2, y = total_sales, fill = product_type)) +
  geom_bar(stat = "identity", color = "white") +
  coord_polar(theta = "y", start = 0) +
  geom_text(
    aes(y = total_sales, label = ifelse(total_sales > 36, 
                                     dollar_format(big.mark = ",", prefix = "$")
                                     (total_sales), "")), 
    color = "white",
    position = position_stack(vjust = 0.5)
  ) +
  scale_fill_manual(values = mycols) +
  theme_void() + 
  xlim(0.5, 2.5) + 
  theme(legend.position = "bottom") + 
  guides(fill = guide_legend(nrow = 3, keywidth = 0.5, 
                             keyheight = 0.5, title.position = "top", title.hjust = 0.5)) 

# Show the preference of beer package
library(ggcharts)
spec <- highlight_spec(
  what = "12 OZ",
  highlight_color = "#D79A40", 
  other_color = "#C0B57B"  
)

package <- beer_data %>%
  group_by(package_size) %>%
  summarise(total_sales = sum(sales_value)) %>%
  bar_chart(x = package_size, 
            y = total_sales, 
            top_n = 10,
            highlight = spec) + 
  theme(
    axis.text.x = element_text(size = 9),  # Adjust the size of x-axis labels
    axis.text.y = element_text(size = 9),
    axis.title.x = element_text(size = 10),
    axis.title.y = element_text(size = 10),
    plot.title = element_text(size = 14, hjust = 0.5),
    plot.background = element_rect(fill = "white")
  ) +
  labs(
    x = "Package size",  # Set the x-axis label
    y = "Total sales"    # Set the y-axis label
  ) + 
  scale_y_continuous(labels = custom_format) + 
  ggtitle("Top 10 Sales of Beer Package Sizes")

type_package <- type + package +
  labs(title = "Consumer Preferences for Beer Types and Packaging") +
  theme(plot.title = element_text(hjust = 2.5, size = 12, face = "bold"))

type_package

4.1.6 What is the best display location?

Display is crucially important for all items in a store, and it appears that products in different locations do not have the same level of visibility. We analyzed the sales of beer in various display locations to identify the best placement.

Findings:

Upon examining the bar chart, it becomes clear that the best locations are not necessarily the most conspicuous ones, such as the store front or front end cap. Surprisingly, ‘no display’ seems to be an even better choice.

locations <- c("0" = "no display", 
                      "1" = "store front",
                      "2" = "store rear",
                      "3" = "front end cap",
                      "4" = "mid-aisle end cap",
                      "5" = "rear end cap",
                      "6" = "side aisle end cap",
                      "7" = "in-aisle",
                      "9" = "secondary location display",
                      "A" = "in-shelf")
 # calculate the sales of beers that were in different display locations   
df %>% 
  filter(str_detect(product_category,"BEER")) %>%
  group_by(display_location) %>%
  summarize(total_value = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_value)) %>%
  
# Plot the result  
  ggplot(aes(x = factor(display_location, levels =  names(locations), labels = locations), 
             y = total_value)) +
  geom_bar(stat = "identity", width = 0.8, fill = "orange") +
  theme(axis.text.x = element_text(angle = 55, hjust = 1)) +
  labs(title = "What is the best display location?")

4.2 Increase Revenue(Increase sales value)

4.3 Decrease Expenditure(Decrease Storage)

4.3.1 Manufacturer Sales Analysis: Contrasting Top Performers and Bottom Performers

In addition to the type and packaging data, we’ve also identified the presence of ‘manufacturer_id’ in the dataset. Now, let’s explore the sales rankings based on manufacturer_id.

Findings:

This plot presents a compelling visual contrast between the top 10 and bottom 10 manufacturers in terms of yearly beer sales.
The disparity is striking, with the top manufacturer achieving sales of $19,977.25, while the bottom 10 had yearly sales no more than $11.
The top manufacturer’s dominance in the market is evident, emphasizing the significance of their products in driving overall beer sales.
It suggests that the grocery need to reevaluate the inclusion of bottom brands in the store’s inventory. Focusing on partnerships with successful manufacturers can be a strategic move to enhance beer sales.

# Plot the top 10 manufacuturers
top_manufacturers <- beer_data %>% 
  group_by(manufacturer_id) %>% 
  summarise(total_sales = sum(sales_value)) %>% 
  arrange(desc(total_sales)) %>% 
  slice_head(n = 10) 

top10 <- ggplot(top_manufacturers) +
  geom_col(aes(x = reorder(manufacturer_id, -total_sales), y = total_sales),
           fill = "#C0B57B") +
  geom_text(aes(x = reorder(manufacturer_id, -total_sales), y = total_sales, label = dollar(total_sales, prefix = "$")),
            vjust = 0.25, size = 2.5) +
  labs(title = "Top 10 Beer Manufacturers",
       x = "Manufacturer", 
       y = "Sales") + 
  theme_minimal() + 
  theme_prism(base_fontface = "plain", 
              base_family = "serif", 
              base_size = 10,  
              base_line_size = 0.8, 
              axis_text_angle = 90) + 
  scale_y_continuous(labels = dollar_format(prefix = "$"))

# Plot the bottom 10 manufacturers
bottom_manufacturers <- beer_data %>% 
  group_by(manufacturer_id) %>% 
  summarise(total_sales = sum(sales_value)) %>% 
  arrange((total_sales)) %>% 
  slice_head(n = 10) 

bottom10 <- ggplot(bottom_manufacturers) +
  geom_col(aes(x = reorder(manufacturer_id, total_sales), y = total_sales),
           fill = "#D79A40") +
  geom_text(aes(x = reorder(manufacturer_id, -total_sales), y = total_sales, label = dollar(total_sales, prefix = "$")),
            vjust = 0.25, size = 2.5) +
  labs(title = "Bottom 10 Beer Manufacturers",
       x = "Manufacturer", 
       y = "Sales") + 
  theme_minimal() + 
  theme_prism(base_fontface = "plain", 
              base_family = "serif", 
              base_size = 10,  
              base_line_size = 0.8, 
              axis_text_angle = 90) + 
  scale_y_continuous(labels = dollar_format(prefix = "$"))

manufacturer_vertical <- top10 / bottom10

manufacturer_vertical

4.3.2 Find out the “low-value” products.

Clearing out low_value products through clearance sales and discounts can have a significant impact on cost control.

Findings:

Products that have never been sold amount to 235 items, while there are 15,351 items with low prices(unit_price<$2) and low sales volume(sales_volume within 1:10). The total number of items is 68,509. Therefore, targeting these specific products for strategic handling can have a significant impact on overall revenue.

# Set varibles:"total_quantity","total_sale","unit_price" for each product_id

Tot_qua <- transactions %>% group_by(product_id ) %>%
         summarise(total_quantity = sum(quantity)) %>% as.data.frame()
Tot_sale <- transactions %>% group_by(product_id ) %>%
         summarise(total_sale = sum(sales_value)) %>% as.data.frame()
price_vs_quantity <- Tot_sale %>% inner_join(Tot_qua) %>%
         filter(total_quantity != 0) %>%
         mutate(unit_price = total_sale/total_quantity) %>%
         as.data.frame()
# Set "sales_volume_rating" levels and "unit_price_rating" for each product_id.Rank the total sales and unit price of each item.
price_vs_quantity <- price_vs_quantity %>%
         mutate(
         sales_volume_rating = case_when(
                  total_quantity %in% 1:10 ~ "1(n=1-10)",
                  total_quantity %in% 11:50 ~ "2(n=11-50)",
                  total_quantity %in% 51:100 ~ "3(n=51-100)",
                  total_quantity %in% 101:500~  "4(n=101-500)",
                  TRUE                       ~   "5(n=more than 500)" )) %>%
         mutate(
         unit_price_rating = case_when(
                  (unit_price >= 0  & unit_price<=2 )~ "lower",
                  (unit_price > 2 & unit_price<=5 )~ "low",
                  (unit_price > 5 & unit_price<=10) ~ "medium",
                  (unit_price > 10 & unit_price<=20) ~ "high",
                  (unit_price > 20 & unit_price<=40)~  "higher",
                  TRUE                       ~   "super high" ))
# Filter products with a sales volume of 0 and products with low sales volume and low prices
P1 <- price_vs_quantity %>% inner_join(products) %>%
         filter(sales_volume_rating == "1(n=1-10)" ) %>%
         filter(unit_price_rating == "lower") %>%
         ggplot(aes(department)) +
         geom_bar(color = "orange") +
            labs(title = "Poor sales product",
        subtitle = "sales_volume_rating=1,
unit_price_rating=lower",
        x = "department",
        y = "count") +
        coord_flip()
Tot_qua_0 <- transactions %>% group_by(product_id) %>%
         summarise(total_quantity = sum(quantity)) %>% filter(total_quantity == 0)
P2 <- Tot_qua_0 %>% inner_join(products) %>%
         ggplot(aes(department)) +
         geom_bar(color = "orange") +
            labs(title = "Sales volume = 0",
            x = "department",
            y = "count") +
            coord_flip()
grid.arrange(P2,P1,ncol = 2)

4.4 Decrease Expenditure(Decrease cost)

4.4.1 How about the mailer location?

Advertising is another common method to capture people’s attention and boost sales. Therefore, we conducted a small research project using the promotion dataset to determine the best mailer location.

Findings:

Surprisingly, the optimal location is ‘not on ad.’ As a result, it appears that investing too much money in advertising may not be a wise choice. Additionally, it’s evident that beer is considered a necessity.

m_locations <- c("0" = "not on ad", 
                      "A" = "interior page feature",
                      "C" = "interior page line item",
                      "D" = "front page feature",
                      "F" = "back page feature",
                      "H" = "wrap front feature",
                      "J" = "wrap interior coupon",
                      "L" = "wrap back feature",
                      "P" = "interior page coupon",
                      "X" = "free on interior page",
                      "Z" = "free on front page, back page, or wrap"
               )
# calculate the sales of beers that were in various mailer locations              

df %>% 
  filter(str_detect(product_category,"BEER")) %>%
  group_by(mailer_location) %>%
  summarize(total_value = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_value)) %>%

  # plot the result
  ggplot(aes(x = factor(mailer_location, levels = names(m_locations), labels = m_locations), 
             y = total_value)) +
  geom_bar(stat = "identity", width = 0.8, fill = "brown") +
  theme(axis.text.x = element_text(angle = 55, hjust = 1)) +
  labs(title = "What is the wisest investment?")

4.5 Data Limitations

4.5.1 The amount of data provided by stores varies

The amount of data uploaded by each store varies.The amount of data uploaded by 25% of stores is less than 6.The amount of data uploaded by 50% of stores is less than 27.If stores with limited data upload more information, it could potentially impact the analysis results.

# Organize the amount of data uploaded by each store.
Store_qua <-
transactions %>% count(store_id) %>% arrange(desc(n))
Store_qua %>%
         ggplot(aes("var",n)) +
         geom_boxplot() +
         scale_y_log10(labels = scales::number,breaks = quantile(Store_qua$n)) +
         labs(title = "Data volume provided by the store percentile ")

5.Summary

The data shows that the side aisle end cap is one of the best display locations in stores, and the optimal mailer location is surprisingly “not on ad.” Beer sales are not significantly affected by price fluctuations, indicating that beer is a necessity.
Based on the fact that the largest consumer group for beer is singles aged between 35 and 54 with incomes below $99,000, and considering the significant increase in beer sales during holidays (mainly National Day, Thanksgiving, Christmas, New Year, and the two weeks prior to them) and the Super Bowl period, we can conclude that beer is an important social tool. This is because singles require more social interaction, and holidays and the Super Bowl are the best times for social gatherings.
In addition, there are over 200 products with zero annual sales, and over 10,000 products priced below $2 have annual sales of less than 10 units. The actual cost of these products has exceeded the revenue they bring to the company, so we believe that such products should be reduced in inventory or discontinued to make better use of store space.
Recommendations:
Reduce advertising expenses for beer. If advertising is necessary, it should be targeted to the largest consumer group.
Bundle pizza and beer sales during holidays and the Super Bowl, such as offering a party package to sell more pizza alongside beer.
Reduce inventory of low-value products or discontinue the sale of them.
Limitations of our analysis:
Some stores have very little data, so the data itself is not comprehensive, which may affect the accuracy of the results. It’s advisable to request all stores to upload as much data as possible.