Introduction

Business Problem

We are investigating the disparity of ice cream topping sales to ice cream. We were able to identify a correlation between the two. We can increase topping sales by running promotions and offering coupons during peak months to incentivise consumers to purchase toppings to compliment ice cream. The promotions would be geared towards households of married couples with kids as this is a category with sales expansion opportunity.

Addressing the Problem

  • Understand which months peak sales occur
  • Identify the correlation between toppings and ice cream
  • Identify who is buying ice cream and toppings
  • Identify which toppings should be displayed near ice cream
  • Gear promotional material towards the demographic with opportunity

Benefits to Regork

The benefits to the company from this report and analysis are as followed: It provides valuable insight into the consumers to identify where we there is room to expand. It will give information about specific products that are often purchased together such as ice cream and toppings. Finally, it can be used as a template for future endeavors regarding specific products and how it sells with complimentary items and who is buying those goods. These findings will give Regork the opportunity to identify new markets and properly run promotions during peak seasons.

Required Packages/Libraries

#Load required packages
library(tidyverse)
library(dplyr)
library(completejourney)
library(ggplot2)

#Simplify data frame names
transactions <- get_transactions()
promotions <- get_promotions()
products <- products
demographics <- demographics

Purpose of Packages

Tidyverse allows us to clean up the code and run it in defined chunks. Dplyr gave us the ability to make filtering data much simpler. We also need completejourney to gain access to the data sets where we are drawing the conclusions from. Finally, ggplot2 gives us the ability to visualize our findings in the form of graphs. The list of data frames (transaction, promotions , demographics and products) are where the data was sourced.

Exploratory Data Analysis

Monthly Ice Cream Sales

The purpose of this table is to identify which months see the highest sales value of ice cream throughout the year. The findings were mostly as expected. June, March, July and May were the months with the largest sales. March is the only unexpected value however, in mid spring there is often a warm up in the region of Regork so that explains the abnormality. This gives us the basic target of promoting sales during the summer months to increase sales.

#Search for ice cream products that are not toppings
ice_cream <- products %>%
  filter(str_detect(product_type, regex("ice cream", ignore_case = TRUE)) 
         & !str_detect(product_type, regex("toppings", ignore_case = TRUE)))

#Take the sum of ice cream sales each month and list them largest to smallest
ice_cream %>%
  inner_join(transactions, by = "product_id") %>%
  group_by(month = month(transaction_timestamp, label = TRUE)) %>%
  summarize(total_sales = sum(sales_value)) %>%
  arrange(desc(total_sales))
## # A tibble: 12 × 2
##    month total_sales
##    <ord>       <dbl>
##  1 Jun          668.
##  2 Mar          587.
##  3 Jul          583.
##  4 May          545.
##  5 Jan          496.
##  6 Aug          495.
##  7 Sep          478.
##  8 Apr          469.
##  9 Oct          460.
## 10 Feb          412.
## 11 Dec          409.
## 12 Nov          328.

Monthly Ice Cream Sale Quantities Visualized

This plot visualizes the quantity of ice cream and related products and their trends over the course of the year. The findings are similar to the above table, there is an obvious peak during May, June and July. This is further evidence that there should be a targeted promotional period during that time.

#Plot the quanity of ice cream products sold each month
ice_cream %>%
  inner_join(transactions, by = 'product_id') %>%
  group_by(month = month(transaction_timestamp, label = TRUE)) %>%
  summarize(total_qty = sum(quantity)) %>%
  ggplot(aes(x = month, y = total_qty)) +
  geom_col() +
  scale_x_discrete("Month") +
  scale_y_continuous("Total Number of Ice Cream Sales") +
  ggtitle("Ice Cream Sales Through The Year", 
          subtitle = "Ice cream sales peak during May, June and July") +
  theme_light()

Monthly Ice Cream Topping Sales

The purpose of this table is to identify which months see the highest sales value of ice cream toppings throughout the year. The findings were similar to those if ice cream although December was the top month. This is likely due to the fact that the toppings are often used in other deserts which are popular during the holiday season. Another finding was that June was the 8th highest month despite it being very high for ice cream. This is the month that should be targeted during the promotional campaign.

#Search for ice cream topping products
toppings <- products %>%
  filter(str_detect(product_type, regex("ice cream toppings", ignore_case = TRUE)))
#Identify the months in which topping sales peak
toppings %>%
  inner_join(transactions, by = "product_id") %>%
  group_by(month = month(transaction_timestamp, label = TRUE)) %>%
  summarize(total_sales = sum(sales_value)) %>%
  arrange(desc(total_sales))
## # A tibble: 12 × 2
##    month total_sales
##    <ord>       <dbl>
##  1 Dec         146. 
##  2 Jul         145. 
##  3 May         128. 
##  4 Aug         124. 
##  5 Sep         114. 
##  6 Apr         111. 
##  7 Nov         111. 
##  8 Jun         104. 
##  9 Oct         104. 
## 10 Jan          94.4
## 11 Feb          83.1
## 12 Mar          73.0

Monthly Ice Cream Toppings Sale Quantities Visualized

This plot visualizes the quantity of ice cream toppings throughout the year. There is a similar pattern to ice cream but we do see that dip in June as mentioned in the above data table findings. There is also the holiday season peak in December. Again, we see the month of June as an opportunity to grow sales for toppings.

#Plot the quantity of topping products sold each month
toppings %>%
  inner_join(transactions, by = 'product_id') %>%
  group_by(month = month(transaction_timestamp, label = TRUE)) %>%
  summarize(total_qty = sum(quantity)) %>%
  ggplot(aes(x = month, y = total_qty)) +
  geom_col() +
  scale_x_discrete("Month") +
  scale_y_continuous("Total Number of Topping Sales") +
  ggtitle("Ice Cream Topping Sales Through The Year", 
          subtitle = "Topping sales peak during May, July, August and December") +
  theme_light()

Comparing Ice Cream and Topping Sales

This plot is intended to confirm there is correlation between the sale of toppings and ice cream. The findings are as follows: The two share similar data relationships. There is an obvious peak during the summer months along with another peak during the holiday season. The disparity being ice cream topping sales during June. Also, the plot is based off of quantity of sales and there is a large difference in that regard. Targeting a promotion for toppings in June is the main takeaway from this data.

#Separate ice cream products and summarize the quantity
IceCream <- products %>%
  filter(str_detect(product_type, regex("ice cream", ignore_case = TRUE))) %>%
  inner_join(transactions, by = 'product_id') %>%
  group_by(x = month(transaction_timestamp, label = TRUE)) %>%
  summarize(total_qty = sum(quantity))
 
#Separate toppings and summarize the quantity sold
IceCreamToppings <- products %>% 
  filter(str_detect(product_type, regex("ice cream toppings", ignore_case = TRUE))) %>%
  inner_join(transactions, by = 'product_id') %>%
  group_by(x = month(transaction_timestamp, label = TRUE)) %>%
  summarize(total_qty = sum(quantity))

#Plot the caparison of ice cream to toppings sale quantity
ggplot() +
  geom_col(data = IceCream, aes(x = x, y = total_qty, color = "Ice Cream Sales"), size = .85) +
  geom_col(data = IceCreamToppings, aes(x = x, y = total_qty, color = "Topping Sales"), size = .85) +
  labs(title = "Ice Cream and Toppings Sales Comparison",
       x = "Month",
       y = "Sales",
       color = "Sales Type") +
  scale_color_manual(values = c("Ice Cream Sales" = "black", "Topping Sales" = "blue")) +
  theme_light()

Demographics of Ice Cream Buyers

This plot is intended to understand who is the main buyer of ice cream. The finding was that married couples with no kids were the primary purchaser of ice cream. However, we believe there is a lot of opportunity for growth in households with kids. This could be done by running a campaign directed towards kids, specifically households with 2 kids.

#Identify the sales value purchased by married couples and how many kids they have
Marital_cream <- ice_cream %>%
  inner_join(transactions, by = "product_id") %>%
  inner_join(demographics, by = "household_id") %>%
  filter(marital_status == "Married") %>%
  group_by(marital_status, kids_count) %>%
  summarize(total_sales = sum(sales_value)) %>%
  arrange(desc(total_sales))

#Plot the sales value of married buyers and separate based on the amount of kids
plot <- ggplot(Marital_cream, aes(x = marital_status, y = total_sales, fill = factor(kids_count))) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Ice Cream Sales by Marital Status and Kids Count",
       x = "Marital Status",
       y = "Total Sales",
       fill = "Kids Count") +
  theme_minimal()

print(plot)

Demographics of Ice cream topping buyers

This plot is intended to better understand the demographics of those purchasing ice cream toppings. The findings were rather surprising. Unmarried households with no kids were the top buyers for ice cream toppings. There following highest categories were married and unknown status with no kids. Once again, there is an untapped market in families with kids relating to topping purchases. All households with kids were among the lowest and this should be noted.

#Plot the amount of toppings consumers buy based on martial status and number of kids
products %>%
  filter(str_detect(product_category, regex("(toppings)", ignore_case = TRUE))) %>%
  inner_join(transactions, by = "product_id") %>%
  inner_join(demographics, by = "household_id") %>%
  group_by(kids_count, marital_status) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  mutate(marital_status = fct_na_value_to_level(marital_status, level = "unknown")) %>%
  ggplot(aes(x = marital_status, y = total_sales)) +
  geom_col(color = "black") +
  facet_wrap(~ kids_count) +
  scale_x_discrete("Marital Status") +
  scale_y_continuous("Toppings Sales", labels = scales::dollar) +
  ggtitle("Who's Buying Toppings",
          subtitle = "Households with no kids buy the most toppings")

The best selling toppings

The following table compiles a list of the top 5 best selling ice cream toppings. These product id’s are worth noting for the merchandising team when putting together a promotion for toppings. They should also be placed near the ice cream to ensure customers see the toppings when buying ice cream.

#Create a list of the top 5 selling topping
products %>%
  filter(str_detect(product_type, regex("ice cream toppings", ignore_case = TRUE))) %>%
  inner_join(transactions, by = "product_id") %>%
  group_by(product_id) %>%
  summarize(total_sales = sum(sales_value)) %>%
  arrange(desc(total_sales)) %>%
  inner_join(products, by = "product_id") %>%
  head(5)
## # A tibble: 5 × 8
##   product_id total_sales manufacturer_id department brand    product_category
##   <chr>            <dbl> <chr>           <chr>      <fct>    <chr>           
## 1 1026448           90.9 1266            GROCERY    National SYRUPS/TOPPINGS 
## 2 871500            83.0 693             GROCERY    National SYRUPS/TOPPINGS 
## 3 831124            82.5 69              GROCERY    Private  SYRUPS/TOPPINGS 
## 4 8249432           75.7 69              GROCERY    Private  SYRUPS/TOPPINGS 
## 5 10281600          72.1 69              GROCERY    Private  SYRUPS/TOPPINGS 
## # ℹ 2 more variables: product_type <chr>, package_size <chr>

Summary

We were able to confirm a correlation between ice cream sales and ice cream topping sales. There was also identification of the key months during the year where there was opportunity to grow topping sales, specifically June is the month where there is a peak in ice cream but not toppings. The demographic of married households with kids was identified as the target demographic. This was addressed through analyzing the provided data sets and making the data easier to read by separating variables and outputting the information into graphs and tables. There were many interesting insights provided by the analysis. First, most of the ice cream and topping sales came from households with no kids. Also, while the peak months are similar for both products, June was ranked 8th for topping sales compared to 2nd for ice cream sales.

Recommendation

Our recommendation to Regork is to run a promotion for ice cream toppings in the month of June and target the advertisements or display art towards kids. This would address two areas where there is room for improvement and would incentivise customers to buy more, therefore, increasing sales. It would also capture a larger market share which could lead to other business ventures to boost sales among families. This analysis also can serve as a template for future endeavors relating to certain products and how they compare to each other along with monthly data and demographic information.

Limitations

There are some limitations to this analysis. First, the transaction data provided was only for the year 2017 so it is hard to base overall trends with information from just 1 year. If there were multiple years of transactions provided there would be more accurate findings. Second, it would be easier to identify which products to promote if there was a list that showed the exact product name matched with its ID. Finally, weather information would have been useful in identifying trends in the data based off of more than typical seasonal months. This could have allowed us to suggest running a promotion right before a heatwave which could have had good results in the past if that information was present.