Midterm Project - Group 9
Claudia Muñoz Farfan, Jenny Ton, Stacy Schniegenberg
February 21, 2024
Introduction
The dairy product category continues to expand and presents a unique growth opportunity for Regork. According to a September 30, 2022 article from Dairy Processor News, data from the USDA Economic Research Service (ERS) shows steady growth in the dairy product category. “U.S. per capita dairy intake has increased 19% over the past 30 years, 9% over the past 15 years, and 4% over the past five years” (Dairy Foods). Michael Dykes, the president and CEO of the International Dairy Food Association attributes this growth to U.S. consumers valuing dairy products for their nutritional value while these products still remain affordable to consumers at all income levels (Dairy Foods).
To take advantage of the increasing demand for dairy products, our goal was to analyze the purchases of dairy products by Regork’s customers and understand their demographics and behaviors. By identifying data trends and relationships, we developed a strategy to capitalize on these insights to boost future sales.
We began our analysis by first determining the demographics of households that were the primary purchasers of dairy products. We analyzed the spending habits of households with and without children and further identified total sales within these households by income level. We also analyzed the product sub-categories of dairy products to determine the most and least frequently purchased products. We then analyzed spending behaviors by income level to determine what days of the week dairy product purchases were being made. Finally, we analyzed customer sensitivity to coupon campaigns by the demographic where most dairy purchases occurred.
The information gathered through data analysis led to the creation of a targeting marketing plan that identified potential areas and strategies to increase demand and profits.
References: https://www.dairyfoods.com/articles/95908-american-dairy-consumption-achieves-a-record-in-2021
Packages Required
The data for this R project can be found on the CompleteJourney
website. It includes information on grocery shopping transactions from
2,469 households. This data covers demographics, products, coupons,
campaigns, and other details collected over the course of one year, from
January 2017 to December 2017.
Load The Packages Required To Run This Report:
library(completejourney)
library(tidyverse)
library(dplyr)
library(stringr)
library(ggplot2)
library(lubridate)
library(scales)Data Preparation
The first task in the data preparation process is to get the full
transactions data set. Each data set in the CompleteJourney package is
previewed in a table-like format called “tibble.”
Get The Full Transactions Data Set:
transactions <- get_transactions()
transactions## # A tibble: 1,469,307 × 11
## household_id store_id basket_id product_id quantity sales_value retail_disc
## <chr> <chr> <chr> <chr> <dbl> <dbl> <dbl>
## 1 900 330 31198570044 1095275 1 0.5 0
## 2 900 330 31198570047 9878513 1 0.99 0.1
## 3 1228 406 31198655051 1041453 1 1.43 0.15
## 4 906 319 31198705046 1020156 1 1.5 0.29
## 5 906 319 31198705046 1053875 2 2.78 0.8
## 6 906 319 31198705046 1060312 1 5.49 0.5
## 7 906 319 31198705046 1075313 1 1.5 0.29
## 8 1058 381 31198676055 985893 1 1.88 0.21
## 9 1058 381 31198676055 988791 1 1.5 1.29
## 10 1058 381 31198676055 9297106 1 2.69 0
## # ℹ 1,469,297 more rows
## # ℹ 4 more variables: coupon_disc <dbl>, coupon_match_disc <dbl>, week <int>,
## # transaction_timestamp <dttm>Grouping All Dairy Products:
Dairy <- products %>%
filter(
str_detect(product_category, regex("( cheese|milk|icecream|cream|butter|yogurt )", ignore_case = TRUE))
) %>%
select(product_category, everything())
Dairy## # A tibble: 1,668 × 7
## product_category product_id manufacturer_id department brand product_type
## <chr> <chr> <chr> <chr> <fct> <chr>
## 1 ICE CREAM/MILK/SHER… 26738 69 GROCERY Priv… TRADITIONAL
## 2 ICE CREAM/MILK/SHER… 26941 69 GROCERY Priv… TRADITIONAL
## 3 ICE CREAM/MILK/SHER… 27030 69 GROCERY Priv… TRADITIONAL
## 4 ICE CREAM/MILK/SHER… 27158 69 GROCERY Priv… TRADITIONAL
## 5 ICE CREAM/MILK/SHER… 27404 69 GROCERY Priv… TRADITIONAL
## 6 ICE CREAM/MILK/SHER… 27522 69 GROCERY Priv… TRADITIONAL
## 7 ICE CREAM/MILK/SHER… 27558 69 GROCERY Priv… TRADITIONAL
## 8 ICE CREAM/MILK/SHER… 27686 69 GROCERY Priv… TRADITIONAL
## 9 ICE CREAM/MILK/SHER… 27762 69 GROCERY Priv… TRADITIONAL
## 10 ICE CREAM/MILK/SHER… 27764 69 GROCERY Priv… TRADITIONAL
## # ℹ 1,658 more rows
## # ℹ 1 more variable: package_size <chr>Grouping Data for Households With Kids:
Households_kids <- Dairy %>%
inner_join(transactions, by = "product_id") %>%
inner_join(demographics, by = "household_id") %>%
filter(kids_count > 0) %>%
group_by(product_id, product_category, household_id, income, transaction_timestamp) %>%
summarize(total_sales = sum(sales_value, na.rm = TRUE) ) %>%
arrange(desc(total_sales))
Households_kids## # A tibble: 17,923 × 6
## # Groups: product_id, product_category, household_id, income [5,978]
## product_id product_category household_id income transaction_timestamp
## <chr> <chr> <chr> <ord> <dttm>
## 1 1133018 FLUID MILK PRODUCTS 458 35-49K 2017-07-19 17:11:21
## 2 1092303 BUTTER 1146 35-49K 2017-04-01 17:21:00
## 3 1062128 ICE CREAM/MILK/SHERBTS 1453 125-149K 2017-12-26 18:36:39
## 4 1062128 ICE CREAM/MILK/SHERBTS 1453 125-149K 2017-09-13 14:35:56
## 5 6463775 ICE CREAM/MILK/SHERBTS 2097 15-24K 2017-02-25 13:37:54
## 6 995242 FLUID MILK PRODUCTS 1467 50-74K 2017-05-02 16:46:34
## 7 1062128 ICE CREAM/MILK/SHERBTS 1453 125-149K 2017-05-17 13:14:00
## 8 1062128 ICE CREAM/MILK/SHERBTS 235 250K+ 2017-02-17 15:00:00
## 9 1062128 ICE CREAM/MILK/SHERBTS 235 250K+ 2017-11-20 12:02:03
## 10 7166861 ICE CREAM/MILK/SHERBTS 2467 75-99K 2017-06-26 21:08:39
## # ℹ 17,913 more rows
## # ℹ 1 more variable: total_sales <dbl>Grouping Data for Households With No Kids:
Households_NOkids <- Dairy %>%
inner_join(transactions, by = "product_id") %>%
inner_join(demographics, by = "household_id") %>%
filter(kids_count == 0) %>%
group_by(product_id, product_category, household_id, income, transaction_timestamp) %>%
summarize(total_sales = sum(sales_value, na.rm = TRUE) ) %>%
arrange(desc(total_sales))
Households_NOkids## # A tibble: 28,024 × 6
## # Groups: product_id, product_category, household_id, income [9,531]
## product_id product_category household_id income transaction_timestamp
## <chr> <chr> <chr> <ord> <dttm>
## 1 1106523 FLUID MILK PRODUCTS 1566 75-99K 2017-12-23 08:34:22
## 2 983795 FLUID MILK PRODUCTS 2455 25-34K 2017-03-04 11:58:43
## 3 1105917 BUTTER 598 25-34K 2017-05-13 17:40:34
## 4 7168058 ICE CREAM/MILK/SHERBTS 2448 50-74K 2017-08-15 16:49:14
## 5 1062128 ICE CREAM/MILK/SHERBTS 1240 Under 1… 2017-12-15 16:19:22
## 6 15741049 BUTTER 2130 50-74K 2017-09-01 16:37:40
## 7 7168058 ICE CREAM/MILK/SHERBTS 2448 50-74K 2017-05-20 18:51:04
## 8 7168058 ICE CREAM/MILK/SHERBTS 2448 50-74K 2017-09-23 18:18:31
## 9 8019396 FLUID MILK PRODUCTS 867 75-99K 2017-10-22 10:36:13
## 10 983795 FLUID MILK PRODUCTS 2085 35-49K 2017-06-25 14:35:30
## # ℹ 28,014 more rows
## # ℹ 1 more variable: total_sales <dbl>Exploratory Data Analysis
Question 1
Do households with or without kids tend to buy more dairy products?
The first set of analyses we ran were to understand the characteristics of the primary purchasers of dairy products. We wanted to understand who was purchasing what type of dairy product. Our first analysis sorted this data for total sales of diary products broken down by sub-category for households that had children. The second data set was run for total sales of diary products broken down by sub-category for households without children.
We discovered that households without children purchase the most
dairy products in total sales and also in each subcategory (butter,
canned milk, fluid milk, ice cream, and milk by-products). The data also
revealed that fluid milk products had the total greatest sales for both
households but that households without children purchased much more milk
($38,000 versus $27,000). For both groups, ice cream sales were second,
followed by milk by-products, butter, and canned milk.
# Total Sales of Dairy Products for Households With Kids
Households_kids %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
scale_y_continuous(labels = scales::dollar, limits = c(0, 40000)) +
labs(title = "Total Sales of Dairy Products for Households with Kids",
x = "Product Category",
y = "Total Sales") +
theme_minimal() +
theme(axis.text.x = element_blank())
# Total Sales of Dairy Products for Households With No Kids
Households_NOkids %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
scale_y_continuous(labels = scales::dollar, limits = c(0, 40000)) +
labs(title = "Total Sales of Dairy Products for Households with No Kids",
x = "Product Category",
y = "Total Sales") +
theme_minimal() +
theme(axis.text.x = element_blank())
We then ran the data sets to determine the income level of the
households with and without children who purchased dairy products. We
discovered that household incomes of $50-74K for both types of
households purchased the most dairy products. Household incomes of
$35-49K for both groups came in at a fairly close second.
# Total Sales of Dairy Products for Households With Kids By Income Range
Households_kids %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
facet_wrap(~ income) +
scale_y_continuous(labels = scales::dollar, limits = c(0, 10000)) +
labs(title = "Total Sales of Dairy Products for Households with Kids",
x = "Product Category",
y = "Total Sales") +
theme_minimal() +
theme(axis.text.x = element_blank())
# Total Sales of Dairy Products for Households With No Kids By Income Range
Households_NOkids %>%
ggplot(aes(x = product_category, y = total_sales, fill = product_category)) +
geom_bar(stat = "identity") +
facet_wrap(~ income) +
scale_y_continuous(labels = scales::dollar, limits = c(0, 10000)) +
labs(title = "Total Sales of Dairy Products for Households with No Kids",
x = "Product Category",
y = "Total Sales") +
theme_minimal() +
theme(axis.text.x = element_blank())
Our takeaway from these analyses indicated that households without children with incomes of $50-74K purchase the most dairy products. We also found that across all demographics, fluid milk was the most purchased product.
Question 2
What day of the week has the largest sales of dairy products?
This set of analyses was run to understand the buying trends for households that purchase dairy products to determine what day of the week they prefer to shop. Our first analysis was run for households with children and was broken down by income level and by product category type. We then ran the same analysis for households without children.
We found similar trends for households with and without children. The
data showed that for the income levels that purchase the most dairy
products, which are $50-74K and $35-49K, there is a sharp increase on
weekends for the purchase of dairy products.
# Consumption of Dairy Products for Households With Kids in The Week
Households_kids %>%
mutate(day_of_week = wday(transaction_timestamp, label = TRUE)) %>%
group_by(day_of_week, product_category, income) %>%
summarize(total_purchases = n()) %>%
ggplot(aes(x = day_of_week, y = total_purchases, color = product_category, group = product_category)) +
geom_line(linewidth = 1.5) +
facet_wrap(~ income) +
labs(title = "Consumption of Dairy Products for Households with Kids in The Week") +
scale_y_continuous(name = "Total Purchases", labels = scales::dollar, limits = c(0, 1000)) +
scale_x_discrete(name = "Day of Week", labels = c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")) +
theme_minimal()
# Consumption of Dairy Products for Households With No Kids in The Week
Households_NOkids %>%
mutate(day_of_week = wday(transaction_timestamp, label = TRUE)) %>%
group_by(day_of_week, product_category, income) %>%
summarize(total_purchases = n()) %>%
ggplot(aes(x = day_of_week, y = total_purchases, color = product_category, group = product_category)) +
geom_line(linewidth = 1.5) +
facet_wrap(~ income) +
labs(title = "Consumption of Dairy Products for Households with No Kids in The Week:") +
scale_y_continuous(name = "Total Purchases", labels = scales::dollar, limits = c(0, 1000)) +
scale_x_discrete(name = "Day of Week", labels = c("Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")) +
theme_minimal()
From these analyses, we determined that households who purchase the most dairy products prefer to shop on weekends (Saturdays and Sundays).
Question 3
Which demographic group is more sensitive to dairy coupons?
This analysis was run to determine if our target households, those
with income levels of $50-74K, redeemed coupons for dairy products. This
analysis was run for households with $50-74K income to show the number
of coupon redemptions broken down by dairy product sub-category and by
households with and without children.
Dairy Product Coupon Redemption on Weekends for Households With Kids That Have Income Range of $50-74K:
Cp_kids <- Households_kids %>%
inner_join(coupons, by = "product_id") %>%
inner_join(coupon_redemptions, by = "coupon_upc") %>%
mutate(day_of_week = wday(transaction_timestamp, label = TRUE)) %>%
filter(day_of_week %in% c("Sat", "Sun")) %>%
group_by(product_category, income) %>%
summarize(coupon_upc = n()) %>%
filter(income == "50-74K") %>%
mutate(Kids = "YES") %>%
print(n = 50)## # A tibble: 4 × 4
## # Groups: product_category [4]
## product_category income coupon_upc Kids
## <chr> <ord> <int> <chr>
## 1 BUTTER 50-74K 1414 YES
## 2 FLUID MILK PRODUCTS 50-74K 57008 YES
## 3 ICE CREAM/MILK/SHERBTS 50-74K 13201 YES
## 4 MILK BY-PRODUCTS 50-74K 15958 YESDairy Product Coupon Redemption on Weekends for Households With No Kids That Have Income Range of $50-74K:
Cp_NOkids <- Households_NOkids %>%
inner_join(coupons, by = "product_id") %>%
inner_join(coupon_redemptions, by = "coupon_upc") %>%
mutate(day_of_week = wday(transaction_timestamp, label = TRUE)) %>%
filter(day_of_week %in% c("Sat", "Sun")) %>%
group_by(product_category, income) %>%
summarize(coupon_upc = n()) %>%
filter(income == "50-74K") %>%
mutate(Kids = "NO") %>%
print(n = 50)## # A tibble: 4 × 4
## # Groups: product_category [4]
## product_category income coupon_upc Kids
## <chr> <ord> <int> <chr>
## 1 BUTTER 50-74K 4780 NO
## 2 FLUID MILK PRODUCTS 50-74K 145942 NO
## 3 ICE CREAM/MILK/SHERBTS 50-74K 34076 NO
## 4 MILK BY-PRODUCTS 50-74K 51629 NODairy Product Coupon Redemption on Weekends for Households That Have Income Range of $50-74K:
union(Cp_kids, Cp_NOkids) %>%
ggplot(aes(reorder(product_category, coupon_upc), coupon_upc, fill = Kids)) +
geom_col(position = "dodge") +
coord_flip() +
facet_wrap(~ income) +
scale_y_continuous(labels = scales::comma) +
theme(legend.title = element_text(size = 8),
plot.title = element_text(hjust = 0.6)) +
labs(title = "Dairy Product Coupon Redemption for Households With Income Range $50-74K",
x = "Product Category",
y = "Coupon Redemptions")
We found that households without children redeemed the most coupons.
However, across both demographics, the product with the most redemptions
was fluid milk, followed by milk by-products, ice cream, and
butter.
From this analysis, we determined that our target demographic of
households with incomes of $50-74K used coupons. The most coupons were
redeemed for fluid milk products and the least were for butter.
Summary
The Problem Statement
Our goal was to determine who the primary purchasers of dairy
products are and establish how we can leverage coupons to target this
market and boost sales revenue.
Methodology To Address The Problem Statement
Our analysis focused on first determining the demographics of
households that were the primary purchasers of dairy products. We
analyzed the spending habits of households with and without children and
further identified total sales within these households by income level.
We also analyzed the product sub-categories of dairy products to
determine the most and least frequently purchased products. We then
analyzed spending behaviors by income level to determine what days of
the week dairy product purchases were being made. Finally, we analyzed
customer sensitivity to coupon campaigns by the demographic where most
dairy purchases occurred.
Interesting Insights From Our Analysis
The most interesting perspective that we gained was that households without children purchased the most dairy products. Within this demographic, households with incomes of $50-74K led sales followed by households with incomes of $35-49K.
The data also showed that fluid milk products were the leading dairy product purchased and that ice cream product sales ranked second. Among all demographics, ice cream sales were roughly about half of the fluid milk sales.
We found that butter was the second least purchased dairy product with canned milk being the least purchased product. This finding of low sales for canned milk was consistent with the most purchased product, fluid milk.
In addition, we found that for both lower and higher income levels, the sales of dairy products were about level throughout the week. However, for households who purchased the most dairy products, which are those households with and without children with incomes in the $50-74K range, dairy product sales on Saturdays and Sundays were significantly higher than on other days of the week.
Data analysis also revealed that households with income levels of
$50-74K without children used dairy coupons most frequently.
Our Implications and Recommendations For Regork
Further demand for dairy products can be generated by introducing targeted coupons aimed at boosting the sales of butter. By capitalizing on the popularity of milk, a top selling item, we can strategically anchor the promotion to drive up butter sales. This proposal targets households without children with an income ranging from $50,000 to $74,000, with coupons available specifically on Saturdays and Sundays.
Our strategy also includes marketing complementary items by
displaying these items in the vicinity of dairy cases to increase
overall product sales. This approach should increase overall sales by
having these items in the line of sight for the dairy product customers.
Complimentary products include specialty breads for butter, crackers for
cheese, or toppings for ice cream. We also recommend alternating weekend
promotions with dairy products being features one weekend and
complementary products being featured the next to increase sales in both
categories.
Limitations of The Analysis
As our analysis was limited to dairy product purchases, it would be helpful to run an analysis on total grocery store purchases broken down by product category and income level. This data would provide a clearer picture of customer spending habits and allow us to determine if there is any correlation between other product categories and dairy. This information would also be helpful in determining marketing strategies for various complementary products for dairy. After implantation of our strategies, another analysis would be helpful to determine the impact on dairy sales across households with various income levels. This would provide valuable insights on how to further refine our strategy.
It would also be helpful to explore how coupons and promotions equate to an increase in sales. This analysis could further explore whether sales or coupons are more effective.
Works Cited
Foods, Dairy. “American Dairy Consumption Achieves a Record in 2021.” Dairy Foods, 30 Sept. 2022, https://www.dairyfoods.com/articles/95908-american-dairy-consumption-achieves-a-record-in-2021.