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)
# 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)
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
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
We will now explore the year-long trend in beer sales to gain deeper insights into the market dynamics and consumer behaviors.
Findings:
Several key observations can be made from this plot:
Seasonal Impact:
Beer consumption is notably affected by seasonal factors and activities.
Sales peak in July, reflecting the increased demand during the summer
months, which often involve outdoor gatherings and events.
Post-Peak Decline:
Following the July peak, there is a consistent decrease in sales, which
may be attributed to the end of peak summer activities and
vacations.
Holiday Influence:
An intriguing trend emerges in December, where beer sales experience a
significant bounce. This uptick is likely due to the holiday season and
festive celebrations, such as Christmas and New Year’s Eve.
For the grocery store, this data offers actionable insights for inventory management and marketing strategies. The understanding of seasonal fluctuations allows for better preparation, ensuring that beer stock aligns with consumer demand during peak periods. Additionally, targeted promotions and marketing campaigns can be timed to coincide with these seasonal trends for maximum impact on sales.
# Calculate the beer sales by month, then plot.
beer_data %>%
group_by(month) %>%
summarise(total_sales = sum(sales_value)) %>%
ggplot(aes(x = factor(month), y = total_sales)) +
geom_point(size = 3, color = "#EABB77") +
geom_line(aes(group = 1), color = "#EABB77") +
theme(
plot.title = element_text(size = 15, hjust = 0.5, face = "bold", vjust = -1),
panel.background = element_rect(fill = "white"),
panel.grid.major = element_line(color = "gray"),
text = element_text(family = "serif")
) +
xlab("Month") +
ylab("Total sales") +
ggtitle("Beer Sales by Month") +
guides(fill = "none") +
scale_y_continuous(labels = scales::dollar) +
scale_x_discrete(breaks = unique(factor(demo_transa_prod$month)))
However, another important question arises:
Does the sales decline in the second half of the year imply fewer
opportunities to increase beer consumption?
To answer this question, we’ll dive into the weekly beer sales trends throughout the year. In the meanwhile, the sales trend of soft drinks is showed together, to compare with beer.
Findings:
This plot provides a valuable comparative analysis of beer and soft drink sales trends by week.
Super Bowl Impact:
In February, beer sales spike to levels comparable to those in July.
This surge is attributed to the Super Bowl, a significant sporting event
where beer consumption typically increases. This highlights the
potential for strategic beer promotions around major sporting
occasions.
Halloween Sales:
The second half of the year sees a resurgence in beer sales during the
Halloween season. This trend could indicate an opportunity for themed
beer promotions or bundles aligned with Halloween festivities.
Soft Drink Peaks:
Comparatively, soft drink sales exhibit more pronounced peak points
throughout the year, indicating strong consumer demand. These peak
periods for soft drinks could potentially serve as opportunities for
cross-promoting beer, especially for customers looking for variety in
their beverage choices.
For the grocery store, this data suggests the potential for leveraging soft drink peak periods to promote beer sales. By strategically aligning beer promotions with the high-demand periods for soft drinks, the store can capture the attention of customers and encourage them to explore beer offerings during their shopping trips. It’s an opportunity to cross-sell and diversify customer choices.
# Calculate beer sales by week
beer <- beer_data %>%
group_by(week) %>%
summarise(total_sales = sum(sales_value))
# Calculate sot drink sales by week
soft_drink <- demo_transa_prod %>%
filter(product_category == "SOFT DRINKS") %>%
group_by(week) %>%
summarise(total_sales = sum(sales_value))
# merge weekly sales of beer and soft drink
merged_data <- merge(beer, soft_drink, by = "week", all = FALSE)
merged_data <- merged_data %>%
rename(beer = total_sales.x, soft_drinks = total_sales.y)
merged_data <- merged_data %>%
pivot_longer(cols = c(beer:soft_drinks),names_to = 'category',values_to = 'sales')
# Plot the trend in lines
skip_weeks <- 2
phy.cols <- c("#EA5D2D", "#EABB77")
ggplot(data = merged_data, aes(x = week, y = sales, group = category, colour = category)) +
geom_point(stroke = 1) + # Replace 'size' with 'stroke'
labs(x = "Week", y = "Sales in a week") +
geom_xspline(spline_shape = -0.5) +
scale_colour_manual(values = phy.cols) +
scale_x_continuous(breaks = unique(merged_data$week)[seq(1, length(unique(merged_data$week)), skip_weeks)])
In addition to comparing with soft drinks, we’re interested in
understanding the sales trend of pizza.
This is important because it provides insights into the potential for
promoting beer and pizza together as a package deal.
Findings:
We can observe that pizza sales are not significantly influenced by
seasonal variations. However, what’s particularly intriguing is the
presence of certain time periods when pizza sales remain strong while
beer experiences a substantial decline in sales.
This observation presents a valuable opportunity for the grocery store to consider promoting pizza and beer as a package deal during these specific periods. Such a strategy could capitalize on the steady demand for pizza and potentially offset the decline in beer sales, ultimately resulting in increased overall sales and customer satisfaction.
# Calculate pizza sales by week
pizza <- demo_transa_prod %>%
filter(
str_detect(product_category, regex("(pizza|frozen|frzn)", ignore_case = TRUE)) &
str_detect(product_type, regex("pizza", ignore_case = TRUE))
) %>%
group_by(week) %>%
summarise(total_sales = sum(sales_value))
# Merge weekly sales of beer and pizza
beer_pizza <- merge(beer, pizza, by = "week", all = FALSE)
beer_pizza <- beer_pizza %>%
rename(beer = total_sales.x, pizza = total_sales.y)
beer_pizza <- beer_pizza %>%
pivot_longer(cols = c(beer:pizza),names_to = 'category',values_to = 'sales')
# Plot the sales trend in lines with differen color
phy.cols <- c("#EA5D2D", "#69b3a2")
ggplot(data = beer_pizza,aes(x = week,y = sales,
group = category, colour = category)) +
geom_point(size = 3) +
labs(x = "Week", y = "Sales in a week") +
geom_xspline(spline_shape = -0.5) +
scale_colour_manual(values = phy.cols) +
scale_x_continuous(breaks = unique(beer_pizza$week)[seq(1, length(unique(beer_pizza$week)), skip_weeks)])
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?")
We are investigating whether the growth in beer sales can be
attributed to a reduction in beer prices.
To explore this, we are comparing the trends in weekly beer sales with
the trends in the average weekly beer prices.
Additionally, we are also exploring the possibility of increasing beer
prices to potentially enhance the growth of sales.
Findings:
This comparison presents a comprehensive view of beer sales trends alongside the average price trends by week, offering valuable insights:
Price Sensitivity:
The analysis indicates that reducing the price of beer doesn’t
significantly impact beer sales. This suggests that consumers may not be
highly price-sensitive when it comes to beer purchases.
Super Bowl Exception:
An interesting exception is observed during the Super Bowl season, where
beer sales peak in July despite an increase in beer prices. This unique
scenario could be attributed to the special occasion of the Super Bowl,
where consumers may prioritize the enjoyment of the event over price
considerations.
For the grocery store, these insights suggest that price reductions alone may not be the most effective strategy for increasing beer sales. Instead, focusing on other factors such as promotions, themed events, and product variety may have a more substantial impact. Understanding the context of sales peaks, as seen during the Super Bowl, can help tailor strategies for specific events and occasions where consumers are willing to spend more on beer. Maybe try to increase beer price will be a direction to increase sales.
# Calculate the average price in a week for beer
beer_price <- beer_data %>%
group_by(week) %>%
summarise(
total_sales = sum(sales_value),
total_quantity = sum(quantity)
) %>%
mutate(price = total_sales / total_quantity)
# As the values of price and sales are very different, so plot the trends of price and sales differently
price <- ggplot(data = beer_price, aes(x = week, y = price)) +
geom_point(size = 3, color = "darkorange4") + # Set point color to darkorange4
labs(x = "Week", y = "Average beer price by week") +
geom_xspline(spline_shape = -0.5, color = "darkorange4") + # Set line color to darkorange4
scale_x_continuous(breaks = unique(beer_price$week)[seq(1, length(unique(beer_price$week)), skip_weeks)])
sales <- ggplot(data = beer, aes(x = week, y = total_sales)) +
geom_point(size = 3, color = "#EA5D2D") +
labs(x = "Week", y = "Beer sales by week") +
geom_xspline(spline_shape = -0.5, color = "#EA5D2D") +
scale_x_continuous(breaks = unique(beer$week)[seq(1, length(unique(beer$week)), skip_weeks)])
sales_price <- sales / price
sales_price
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
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)
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?")
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 ")