Regork case study
Ryan Rogala
2024-02-23
Introduction
Determining potential growth for high sales volume per unit products
The business problem I am trying to address is how can we potentially sell more products with the highest sales volume per unit as we currently do not sell many of these units. This can be accomplished by first identifying these products then further analyzing to find trends and patterns that can help us achieve more sales.
How this could potentially impact the company
Offering deals as well as campaigning for these high value items may potentially draw customers away from competitors that are selling the same products but at a higher volume than us.
packages used
suppressWarnings(suppressMessages(library(tidyverse)))
suppressWarnings(suppressMessages(library(completejourney)))
suppressWarnings(suppressMessages(library(dplyr)))
suppressWarnings(suppressMessages(library(purrr)))
suppressWarnings(suppressMessages(library(ggplot2)))
suppressWarnings(suppressMessages(library(rmarkdown)))
suppressWarnings(suppressMessages(library(lubridate)))data used
completejourney::campaign_descriptions## # A tibble: 27 × 4
## campaign_id campaign_type start_date end_date
## <chr> <ord> <date> <date>
## 1 1 Type B 2017-03-03 2017-04-09
## 2 2 Type B 2017-03-08 2017-04-09
## 3 3 Type C 2017-03-13 2017-05-08
## 4 4 Type B 2017-03-29 2017-04-30
## 5 5 Type B 2017-04-03 2017-05-07
## 6 6 Type C 2017-04-19 2017-05-21
## 7 7 Type B 2017-04-24 2017-05-28
## 8 8 Type A 2017-05-08 2017-06-25
## 9 9 Type B 2017-05-31 2017-07-02
## 10 10 Type B 2017-06-28 2017-07-30
## # ℹ 17 more rowscompletejourney::get_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>completejourney::products## # A tibble: 92,331 × 7
## product_id manufacturer_id department brand product_category product_type
## <chr> <chr> <chr> <fct> <chr> <chr>
## 1 25671 2 GROCERY Natio… FRZN ICE ICE - CRUSH…
## 2 26081 2 MISCELLANEOUS Natio… <NA> <NA>
## 3 26093 69 PASTRY Priva… BREAD BREAD:ITALI…
## 4 26190 69 GROCERY Priva… FRUIT - SHELF S… APPLE SAUCE
## 5 26355 69 GROCERY Priva… COOKIES/CONES SPECIALTY C…
## 6 26426 69 GROCERY Priva… SPICES & EXTRAC… SPICES & SE…
## 7 26540 69 GROCERY Priva… COOKIES/CONES TRAY PACK/C…
## 8 26601 69 DRUG GM Priva… VITAMINS VITAMIN - M…
## 9 26636 69 PASTRY Priva… BREAKFAST SWEETS SW GDS: SW …
## 10 26691 16 GROCERY Priva… PNT BTR/JELLY/J… HONEY
## # ℹ 92,321 more rows
## # ℹ 1 more variable: package_size <chr>completejourney::demographics## # A tibble: 801 × 8
## household_id age income home_ownership marital_status household_size
## <chr> <ord> <ord> <ord> <ord> <ord>
## 1 1 65+ 35-49K Homeowner Married 2
## 2 1001 45-54 50-74K Homeowner Unmarried 1
## 3 1003 35-44 25-34K <NA> Unmarried 1
## 4 1004 25-34 15-24K <NA> Unmarried 1
## 5 101 45-54 Under 15K Homeowner Married 4
## 6 1012 35-44 35-49K <NA> Married 5+
## 7 1014 45-54 15-24K <NA> Married 4
## 8 1015 45-54 50-74K Homeowner Unmarried 1
## 9 1018 45-54 35-49K Homeowner Married 5+
## 10 1020 45-54 25-34K Homeowner Married 2
## # ℹ 791 more rows
## # ℹ 2 more variables: household_comp <ord>, kids_count <ord>completejourney::campaigns## # A tibble: 6,589 × 2
## campaign_id household_id
## <chr> <chr>
## 1 1 105
## 2 1 1238
## 3 1 1258
## 4 1 1483
## 5 1 2200
## 6 1 293
## 7 1 529
## 8 1 536
## 9 1 568
## 10 1 630
## # ℹ 6,579 more rowscompletejourney::coupons## # A tibble: 116,204 × 3
## coupon_upc product_id campaign_id
## <chr> <chr> <chr>
## 1 10000085207 9676830 26
## 2 10000085207 9676943 26
## 3 10000085207 9676944 26
## 4 10000085207 9676947 26
## 5 10000085207 9677008 26
## 6 10000085207 9677052 26
## 7 10000085207 9677385 26
## 8 10000085207 9677479 26
## 9 10000085207 9677791 26
## 10 10000085207 9677878 26
## # ℹ 116,194 more rowscompletejourney::coupon_redemptions## # A tibble: 2,102 × 4
## household_id coupon_upc campaign_id redemption_date
## <chr> <chr> <chr> <date>
## 1 1029 51380041013 26 2017-01-01
## 2 1029 51380041313 26 2017-01-01
## 3 165 53377610033 26 2017-01-03
## 4 712 51380041013 26 2017-01-07
## 5 712 54300016033 26 2017-01-07
## 6 2488 51200092776 26 2017-01-10
## 7 2488 51410010050 26 2017-01-10
## 8 1923 53000012033 26 2017-01-14
## 9 1923 54300021057 26 2017-01-14
## 10 1923 57047091041 26 2017-01-14
## # ℹ 2,092 more rowsgett <- get_transactions()Finding products with the highest sales volume per unit
product_summary <- gett %>%
group_by(product_id) %>%
summarize(total_sales_value = sum(sales_value),
total_quantity = sum(quantity))
product_summary <- product_summary %>%
filter(total_quantity > 0)
product_summary <- product_summary %>%
mutate(sales_value_per_unit = total_sales_value / total_quantity)
product_summary <- product_summary %>%
arrange(desc(sales_value_per_unit))Plotting these products with names
top_products_with_names <- product_summary %>%
left_join(products, by = "product_id")
top_products_with_names <- head(top_products_with_names, 25)
TPN <- top_products_with_names
# The plot ----------------------------------------------------------------
average_sales_per_type <- TPN %>%
group_by(product_type) %>%
summarize(average_sales_value_per_unit = mean(sales_value_per_unit))
average_sales_per_type <- average_sales_per_type %>%
mutate(product_type = reorder(product_type, -average_sales_value_per_unit))
ploths <- ggplot(average_sales_per_type, aes(x = product_type, y = average_sales_value_per_unit)) +
geom_bar(stat = "identity", fill = "lightblue") +
labs(title = "Average Sales Value Per Unit by Product Type",
x = "Product Type",
y = "Average Sales Value Per Unit") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
print(ploths)
It is apparent the top 3 most expensive items we sell are roll up blinds, patio sets, as well as artificial trees.
Digging further into the data we can assess when these products are sold, as well as the amount sold throughout the year.
Acquiring product ids for the top 3 highest sales volume per unit items
patio_setid <- TPN %>%
filter(product_type == "PATIO SETS")
print(patio_setid$product_id)## [1] "13506935" "13213287" "13381576" "13213237" "12607736" "13381271" "13381195"
## [8] "13212980"##"13506935" "13213287" "13381576" "13213237" "12607736" "13381271" "13381195" "13212980"
rollupid <- TPN %>%
filter(product_type == "OUTDOOR ROLL-UP BLINDS")
##"13212959"
treeid <- TPN %>%
filter(product_type == "ARTIFICIAL TREES")
##"17242362"Using these IDs to plot time and volume of sales per item type
patio_set_ids <- c(13212959, 13506935, 13213287, 13381576, 13213237, 12607736, 13381271, 13381195, 13212980)
artificial_tree_ids <- c(17242362)
roll_up_blind_ids <- c(13212959)
patio_set_transactions <- gett[gett$product_id %in% patio_set_ids, ]
artificial_tree_transactions <- gett[gett$product_id %in% artificial_tree_ids, ]
roll_up_blind_transactions <- gett[gett$product_id %in% roll_up_blind_ids, ]
ggplot() +
geom_point(data = patio_set_transactions, aes(x = transaction_timestamp, y = sales_value, color = "Patio Sets")) +
geom_point(data = artificial_tree_transactions, aes(x = transaction_timestamp, y = sales_value, color = "Artificial Trees")) +
geom_point(data = roll_up_blind_transactions, aes(x = transaction_timestamp, y = sales_value, color = "Roll-up Blinds")) +
scale_color_manual(values = c("Patio Sets" = "blue", "Artificial Trees" = "red", "Roll-up Blinds" = "green")) +
labs(title = "Transactions by Date",
x = "Date",
y = "Sales Value",
color = "Product Type") +
theme_minimal()
Synopsis
After analyzing this data I decided to spend most of my effort on patio set product types as they have more potential for sale throughout the year compared to Artificial trees and Roll-up blinds. As an analyst I would personally discourage the sale of roll up blinds and artificial trees as they are expensive products that are nearly never purchased.
Products often purchased WITH patio set type products
First I select basket id’s that contain patio set product types
patio_set_ids <- c(13212959, 13506935, 13213287, 13381576, 13213237, 12607736, 13381271, 13381195, 13212980)
patio_set_basket_ids <- gett %>%
filter(product_id %in% patio_set_ids) %>%
select(basket_id) %>%
distinct()
selected_product_ids <- c(13212959, 13506935, 13213287, 13381576, 13213237, 12607736, 13381271, 13381195, 13212980)
selected_basket_ids <- gett %>%
filter(product_id %in% selected_product_ids) %>%
select(basket_id)
selected_basket_ids <- c(32187143334, 32589406457, 32715766842, 32872550792, 32932606815, 33028063235, 33192060862, 33192060862, 33240466943, 33348172087, 33421338406, 33493612750, 33657746421, 33769986599, 33848336516)
selected_baskets <- gett %>%
filter(basket_id %in% selected_basket_ids)List of products that are often purchased with patio set product types
patio_set_ids <- c(13212959, 13506935, 13213287, 13381576, 13213237, 12607736, 13381271, 13381195, 13212980)
patio_set_transactions <- gett[gett$product_id %in% patio_set_ids, ]
patio_set_basket_ids <- unique(patio_set_transactions$basket_id)
products_bought_with_patio_sets <- gett[gett$basket_id %in% patio_set_basket_ids & !gett$product_id %in% patio_set_ids, ]
product_ids <- products_bought_with_patio_sets$product_id
products_bought_with_patio_sets_names <- products %>%
filter(product_id %in% product_ids)
product_counts <- products_bought_with_patio_sets %>%
group_by(product_id) %>%
summarise(count = n())
products_bought_with_patio_sets_counts <- merge(products_bought_with_patio_sets_names, product_counts, by = "product_id")
products_bought_with_patio_sets_counts <- products_bought_with_patio_sets_counts %>%
arrange(desc(count))
print(products_bought_with_patio_sets_counts[, c("product_type", "count", "product_id")])## product_type count product_id
## 1 BANANAS 2 1082185
## 2 CHEESE: NATURAL BULK 1 1040894
## 3 CHEWING GUM 1 1054123
## 4 HANGING BASKETS (BLOOMING) 1 1066095
## 5 TOILET TISSUE 1 1075214
## 6 MARGARINE: TUBS AND BOWLS 1 1083840
## 7 SPECIALTY CRACKERS 1 1098286
## 8 BLENDED JUICE&COMBINATIONS (50 1 12604479
## 9 PLSTC CTLRYTBLCLTHSTTHPKSST 1 12670635
## 10 MINTS CANDY & BREATH (NOT LIF 1 12731667
## 11 BEERALEMALT LIQUORS 1 1277455
## 12 CARDS SEASONAL 1 13159269
## 13 CATSUP 1 6391436
## 14 PREMIUM ANNUALS (OUTDOOR) 1 848059
## 15 FRZN CONC ALLIEDS OVER 50% JUI 1 898023
## 16 COOKING CHOCOLATE (EX SMI-SWT) 1 923005
## 17 CANDY BARS (MULTI PACK) 1 9245417
## 18 SWEET GOODS - FULL SIZE 1 925437
## 19 APPLES OTHER (BULK&BAG) 1 927263
## 20 CANDY BARS (MULTI PACK) 1 954966
## 21 SFT DRNK 2 LITER BTL CARB INCL 1 989157Given this information, I believe it could potentially be beneficial to offer some of these products for free or for a discount along with our patio sets to encourage the sale of these high value items. Items often purchased with patio sets are relatively cheap food items, so offering these items for free or for a discount could potentially increase revenue when purchased with the more expensive patio sets.
Best selling products that are often bought with patio sets
I believe it is a good idea to further examine products that are often purchased with patio sets to see which ones would draw the most attention from a customer when offered a deal.
topsellid <- c(
1082185,
1040894,
1054123,
1066095,
1075214,
1083840,
1098286,
12604479,
12670635,
12731667,
1277455,
13159269,
6391436,
848059,
898023,
923005,
9245417,
925437,
927263,
954966,
989157)
top_selling_transactions <- gett %>%
filter(product_id %in% topsellid)
top_selling_summary <- top_selling_transactions %>%
group_by(product_id) %>%
summarize(total_sales_value = sum(sales_value))
top_selling_summary <- top_selling_summary %>%
arrange(desc(total_sales_value))
top_selling_summary_with_type <- top_selling_summary %>%
left_join(products, by = "product_id")
ggplot(top_selling_summary_with_type, aes(x = reorder(product_type, -total_sales_value), y = total_sales_value)) +
geom_point() +
labs(title = "Top Selling Products by Product Type", x = "Product Type", y = "Total Sales Value") +
theme(axis.text.x = element_text(angle = 45, hjust = 1))
Bananas and toilet paper seem to be a high selling products throughout the year and are unlikely to attract customers to our patio set products
Seeing which products are sold in the same timeframe our patio sets are sold (APR - JUL)
product_transactions <- gett[gett$product_id %in% topsellid , ]
product_transactions <- product_transactions %>%
left_join(products, by = "product_id")
product_transactions$transaction_timestamp <- as.Date(product_transactions$transaction_timestamp)
product_transactions$month <- format(product_transactions$transaction_timestamp, "%Y-%m")
product_transactions_filtered <- product_transactions %>%
filter(month >= "2017-04" & month <= "2017-07")
average_transactions_per_month <- product_transactions_filtered %>%
group_by(product_type, month) %>%
summarise(average_transactions = n())## `summarise()` has grouped output by 'product_type'. You can override using the
## `.groups` argument.average_transactions_per_month$product_type <- reorder(average_transactions_per_month$product_type, average_transactions_per_month$average_transactions)
my_colors <- c("lightblue", "blue", "darkblue", "black")
ggplot(average_transactions_per_month, aes(x = month, y = product_type, fill = average_transactions)) +
geom_tile() +
labs(title = "Average Number of Transactions Per Month by Product Type",
x = "Month",
y = "Product Type",
fill = "Average Transactions") +
theme_minimal() +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
scale_fill_gradientn(colors = my_colors, limits = c(0, 200), breaks = c(50, 100, 150, 200))
Looking at this data for these 4 months it is apparent that certain
seasonal items including candy bars, cards, and hanging baskets are sold
more in these months compared to the rest of the year. I believe it
would be greatly beneficial to sell one or more of these products for a
discount when basketed with patio sets. This would greatly encourage the
sale of our patio sets, one of our best selling and most expensive
items.