Introduction

Our Business Problem

A common concern that is often present among college students revolves around what they will be eating. Whether it be hamburgers, hot dogs, or frozen pizzas, it is important to note that college students contribute to the many sales that grocery stores make across the country. The goal of our report was to locate the exact times that this age range makes the most purchases, and help generate more sales by marketing this group products that they may find interesting.

How We Addressed the Problem

By analyzing the data collected from Regork’s various chains across the country, we not only found a key time of the year where this group generated a large quantity of sales, but we also saw that there were a few key products that this group purchased the most. Specifically, we targeted sales among 19-24 year old people as this age range is the most likely to be college students. We also filtered the sales for specific products to see which ones were generating the most sales. Through these steps, we were able to find a clear conclusion.

Why is This Report Useful

It is always important for companies to know their clients since this is the best way to maximize sales. Therefore, this report allows Regork to make a good business decision on how to market to this specific age group, so they do not miss out on a potential sales opporutinity.

Required Packages

The following are the packages we used while creating our report:

completejourney - This package gave us all of the data we needed to conduct the analysis.

tidyverse - This package allowed us to analyze the data.

ggplot2 - This package allowed us to create plots to visualize the data.

DT - This package allows us to view our data in tables.

library(completejourney)
library(tidyverse)
library(ggplot2)
library(DT)

Preparing The Data

Before we could dive straight into our analysis, we first had to prepare our data. Since we were specifically focusing on college students, we need to link demographics data with transactions data so we could compare generated sales with age. We also wanted to look at which specific stores were generating the most sales among specific products, so we need to link transactions data with product data. After viewing total sales of this age range, we saw a specific time that there was a noticeable spike in sales, so we then needed to filter the data for this time period. After completing this, we were ready to begin our analysis.

# Load the data into R
transactions <- get_transactions()
demographics <- demographics
products <- products

# Join data to correlate sales with age and location
transactions_filtered <- transactions %>%
  inner_join(demographics, by = "household_id") %>%
  inner_join(products, by = "product_id")

# Filtered for specific spike in sales
transactions_filtered_for_products <- transactions %>%
  inner_join(products, by = "product_id") %>%
  inner_join(demographics, by = "household_id") %>%
  filter((week == "31" | week == "32") & age == "19-24")

Exploratory Data Analysis

#This is base plot of yearly sales
transactions_trial_9 <- transactions_filtered %>%
  select(age, sales_value, product_category, week) %>%
  filter(age == "19-24") %>%
  group_by(week) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE))

#Viewing this plot
DT::datatable(transactions_trial_9)

The above data table shows the sales generated per week for the age group of 19-24.

# Line chart of total year sales
transactions_trial_9 %>%
  ggplot(aes(x = week, y = total_sales)) +
  geom_line(color = "blue", size = 1.2) + 
  geom_point(color = "red", size = 2) +  
  labs(title = "Total Sales by Week for Age Group 19-24",
       x = "Week",
       y = "Total Sales",
       caption = "Source: Regork's various chains") +  
  theme_minimal() + 
  theme(
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),  
    axis.title = element_text(face = "bold"),
    axis.text = element_text(size = 10)
  )

This line chart displays the above data table in a better visualization. We noticed that around week 32, there was a large spike in sales and we wanted to investigate the cause of this.

For the rest of our data, we filtered sales for only weeks 31 and 32. The next step was to find the products that generated the most sales.

transactions_filtered_for_products %>%
     group_by(product_category) %>%
       summarise(total_sales = sum(sales_value, na.rm = TRUE)) %>%
       arrange(desc(total_sales)) %>%
       slice_head(n = 10)
## # A tibble: 10 × 2
##    product_category       total_sales
##    <chr>                        <dbl>
##  1 COUPON/MISC ITEMS             693.
##  2 SOFT DRINKS                   329.
##  3 BEEF                          202.
##  4 FROZEN PIZZA                  199.
##  5 CHEESE                        171.
##  6 BAG SNACKS                    145.
##  7 BAKED BREAD/BUNS/ROLLS        134.
##  8 FLUID MILK PRODUCTS           133.
##  9 BEERS/ALES                    122.
## 10 FRZN MEAT/MEAT DINNERS        120.

As seen above, the three most sold products during these two weeks were Coupons/Misc Items, soft drinks, and beef. Therefore, these three product categories are essential to market, as they are the most likely to generate more sales.

Next, we wanted to break the sales up across stores to see which specific stores are selling the most amount of these products. This will allow Regork to focus on advertising these products more in stores that will effectively generate more revenue.

# Sales by top 10 stores for coupon items
transactions_total <- transactions_filtered_for_products %>%
  filter(product_category == "COUPON/MISC ITEMS") %>%
  group_by(store_id) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_sales)) %>%
  slice_head(n = 10)

# Graph for top 10 store coup/misc
transactions_total %>%
  ggplot(aes(x = store_id, y = total_sales, fill = total_sales)) +
  geom_bar(stat = "identity") +
  theme_minimal() +
  labs(title = "Total Sales of Coupons/Misc Items Across Different Stores", 
       x = "Store ID", 
       y = "Total Sales") +
  scale_fill_gradient(low = "darkgreen", high = "yellow") +  
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1), 
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),  
    axis.title = element_text(face = "bold"),
    axis.text = element_text(size = 10)
  )

This plot shows the Store 369 sold the most coupons and other miscellaneous items, so they should continue to advertise heavily for these products.

#Soft drinks sales
transactions_total_softdrinks <- transactions_filtered_for_products%>%
  filter(product_category == "SOFT DRINKS") %>%
  group_by(store_id) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_sales)) %>%
  slice_head(n = 10)

# Graph of Soft Drinks sales
transactions_total_softdrinks %>%
  ggplot(aes(x = store_id, y = total_sales, fill = total_sales)) +
  geom_bar(stat = "identity") +
  theme_minimal() +
  labs(title = "Total Sales of Soft Drinks Across Different Stores", 
       x = "Store ID", 
       y = "Total Sales") +
  scale_fill_gradient(low = "cyan", high = "darkblue") +  
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),  
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),  
    axis.title = element_text(face = "bold"),
    axis.text = element_text(size = 10)
  )

This plot shows that store 319 clearly sold the most in soft drinks, so they should continue advertising this product.

#Sales of Beef
transactions_total_beef <- transactions_filtered_for_products %>%
  filter(product_category == "BEEF") %>%
  group_by(store_id) %>%
  summarise(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_sales)) %>%
  slice_head(n = 10) 

# Graph of Beef sales
transactions_total_beef %>%
  ggplot(aes(x = store_id, y = total_sales, fill = total_sales)) +
  geom_bar(stat = "identity") +
  theme_minimal() +
  labs(title = "Total Sales of Beef Across Different Stores", 
       x = "Store ID", 
       y = "Total Sales") +
  scale_fill_gradient(low = "purple", high = "red") +  
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),  
    plot.title = element_text(hjust = 0.5, face = "bold", size = 14),  
    axis.title = element_text(face = "bold"),
    axis.text = element_text(size = 10)
  )

This last plot shows that store 370 made the most sales of beef over these two weeks. Therefore, they should continue to advertise this product to this age range.

Summary

Summary of the Problem Addressed

The goal of our analysis was to identify a key time when college students made the most purchases from Regork. We also wanted to identify which stores sold the most of the top three products, so we could maximize total sales at these specific stores. After conducting this analysis, we made a clear conclusion when the best time is to market to this age range.

Summary of How the Problem was Addressed

Our group addressed this problem by joining the demographics, products, and transactions data provided by Regork in order to identify customer trends.The first step we took was to filter for sales made by 19-24 year old, as this age range makes up the most college students. Then, we identified a jump in sales in early August via the use of a line chart which helped us identify a time of the year that we could maximize profit. After seeing this spike in sales, we then wanted to figure out which products were generating the most sales. After filtering for weeks 31 and 32, we created a simple summary of total sales and filtered for the top 10 most selling products. This allowed us to identify the top three most selling products during these weeks which helped us draw a conclusion. The last problem we wanted to address was to figure out which stores were selling the most of these products. We created three bar charts (one for each product) and looked at the sales of these products among the top ten selling stores. This allowed us to identify which stores were selling the most of these products.

Summary of Insights that the Analysis Provided

Ultimately, the analysis that our group conducted provided us with clear insights. Firstly, as there was an immense spike in sales during the 31st and 32nd weeks of the year, we believe that this spike was due to back to school sales. Since we were only looking at sales made by the 19-24 age range, it would make sense for students to be buying products in bulk before school begins in order to avoid the stresses of shopping during the first week of school. We noticed that the purchase of coupons and other miscellaneous items made up the bulk of sales with soft drinks and beef following. All three of these items would align with back to school needs since miscellaneous items may include school supplies, soft drinks are drank by many college students, and beef is a typically easy meal to prepare when on a time crunch.

When identifying which stores sold the most of these top three products, we were able to assess where it would be best to focus on promoting the products. For example, Store 369 sold the most coupons and other miscellaneous items showing that it is already successful in reaching college students. Hence, Regork should focus on accelerating promotional efforts in other stores such as 363 and 370 which are low-performing and lagging in terms of sales volume.

These insights allowed us to make specific recommendations to the higher level managers at Regork in order to know what products to market the most at specific stores in order to increase total sales for college students.

What Reccommendations can be Made?

After drawing conclusions from our analysis, there are two clear recommendations to make to higher level managers at Regork.

The first recommendation is to create a “Back-to-School” sale during the last week of July and first week of August. This sale would include promotions such as buy one get one free on select soft drinks, beef, or other miscellaneous items.

During this sale, it would be important for the marketing department to advertise these three specific products the most, as they are popular among this age range.

The next recommendation would be to bundle products. Our recommendation is to create sales promotions during these weeks that create deals on the purchase of two of these items at the same time. For example, store 370 saw a significant amount of sales of both beef and soft drinks, thus they should create promotion that if a person were to buy a pound of beef at this store, they would receive a discount on soft drinks if they were to buy the products together.

Ultimately, we feel that these recommendations would cause an even greater spike in sales during this time frame.

Limitations of the Analysis

There were a couple key limitations that we experienced when conducting this analysis. The first limitation was that we were under the assumption that all of the people in this age range were college students. Obviously, it is unlikely that all of these transactions were made by college student as not every 19-24 year old is a college student. Therefore, this reality may have falsely contributed to the idea that this increase in sales was due to back to school shopping.

The other limitation that we experienced was that we were only able to measure sales over the course of one year. It would be important to analyze if this spike in sales in August was a yearly occurrence, or if it was just a one time occurrence.