Introduction

The data analytics team at Regork is interested in finding a new opportunity for the marketing team to capitalize on. We started out by exploring the relationship between transactions purchasing a frozen ice product and transactions purchasing a plastic cup product.

Data Loading and Libraries for Analysis

We will be working with data from the year of 2017 to help us discover opportunities. There are many relational tables within the completejourney package, such as: demographics, transactions, and products.

library(tidyverse)
library(repurrrsive)
library(completejourney)


products_df <- products

transactions_df <- get_transactions()

transactions_df <- transactions_df %>% 
                   mutate(transaction_date = date(transaction_timestamp))

Generate Dataframes for Frozen Ice and Plastic Cup Analysis

#Generate Plastic Cup Dataframe
plastic_cup_products <- products_df %>%
                        filter(product_type == "PLASTIC CUPS")
unique_plastic_cuP_product_ids <- unique(plastic_cup_products$product_id)
plastic_cup_transactions <- transactions_df %>%
                            filter(product_id %in% unique_plastic_cuP_product_ids) %>%
                            mutate(transaction_date = date(transaction_timestamp))


#Generate Frozen Ice Dataframe
frozen_ice_products <-  products_df %>%
  filter(product_category == "FRZN ICE")


unique_frozen_ice_product_ids <-  unique(frozen_ice_products$product_id)

frozen_ice_transactions <- transactions_df %>%
  filter(product_id %in% unique_frozen_ice_product_ids) %>%
  mutate(transaction_date = date(transaction_timestamp))

Code to Generate Density Plot for 2 Categories (Frozen Ice and Plastic Cups)

density_plot_plastic_cups <- ggplot() +
  geom_density(data = plastic_cup_transactions,aes(x =transaction_date,color = "Plastic Cups"),fill = "chocolate1",alpha = 0.5) +
  geom_density(data = frozen_ice_transactions,aes(x =transaction_date, color = "Frozen Ice"),fill = "cadetblue1",alpha = 0.5) +
  labs(title = "Density of Frozen Ice vs. Plastic Cups Transactions In 2017",
       x = "Transaction Date",
       y = "Density",
      color = "Legend")+
scale_color_manual(name = "Product",
                   values = c( "Frozen Ice" = "cadetblue1", "Plastic Cups" = "chocolate1"),
                   labels = c("Frozen Ice", "Plastic Cups"),
                   guide = guide_legend(override.aes = list(fill = c("cadetblue1","chocolate1")))
)

Visualize Density Plot

plot(density_plot_plastic_cups)

Summary of Findings on Relationship Between Densities

We are unable to say that there is a perfect correlating trend between the density of frozen ice product transactions and plastic cup product transactions. We do notice a very,large peak in frozen ice transactions in July, presumably due to the July 4th holiday. We also notice denser amounts of plastic cup transactions during late April - May, as well as December 2017, presumably ‘Graduation Season’ and New Year’s parties, respectively.

Further Exploration into Summer Trends

From here since there seems to be some peaks in both products during the Summer season, we want to explore what top product categories are for the season. And how does that differ from other seasons? And if we notice something, maybe there is a package deal we can offer to consumers to attract them in even more.

Let’s get into it.

Season Mapping Function

Before we get to analyzing transactions, we have to define our methodology for saying when each season is. As well as describe this mapping through an R function.

There will be 5 periods of data within our transactions data set, namely: Winter 2017, Spring 2017,Summer 2017, and Winter 2018. Winter 2018 will occur at the end of 2017 after Fall 2017 occurred from the beginning of September 2017 to the end of November 2017.

The relationship described in the paragraph above is the main caveat to the way we define seasons, but if you’d like to see how each season is applied individually, you should be able to understand it through the visualization of the code below.

find_season <- function(input_date){
  
  #Winter 2017 is defined as the winter that started towards the end of 2016.
  #While Winter 2018 is the winter that started towards the end of 2017.
  start_Winter2017 <- as.Date("2017-01-01")
  end_Winter2017 <-  as.Date("2017-02-28")
  
  start_Spring <- as.Date("2017-03-01")
  end_Spring <- as.Date("2017-05-31")
  
  start_Summer <- as.Date("2017-06-01")
  end_Summer <- as.Date("2017-08-31")
  
  start_Fall <-as.Date("2017-09-01")
  end_Fall <- as.Date("2017-11-30")
  
  case_when(
    input_date <= end_Winter2017 ~ "Winter 2017",
    input_date <= end_Spring ~ "Spring 2017",
    input_date <= end_Summer ~ "Summer 2017",
    input_date <= end_Fall ~ "Fall 2017",
    TRUE ~ "Winter 2018"
  )

  
}

Apply Season Mapping Function to Transactions Dataframe

transactions_with_season_applied <- transactions_df %>%
  mutate(transaction_date = date(transaction_timestamp)) %>%
  mutate(season = find_season(transaction_date))

Find Top 15 Product Categories for Each Season

#Generate Visual Dataframe for Winter 2017
top_15_winter2017_product_categories <- transactions_with_season_applied %>%
                                 inner_join(products_df, by = "product_id") %>%
                                 filter(season == "Winter 2017") %>%
                                 group_by(product_category) %>%
                                 summarize(Total_sales = sum(sales_value)) %>%
                                 arrange(desc(Total_sales)) %>%
                                 head(15)
top15_winter2017_visual <- knitr::kable(top_15_winter2017_product_categories,format = "html",caption = "Top 15 Products Winter 2017")


#Generate Visual Dataframe for Spring 2017
top_15_spring_product_categories <- transactions_with_season_applied %>%
                                 inner_join(products_df, by = "product_id") %>%
                                 filter(season == "Spring 2017") %>%
                                 group_by(product_category) %>%
                                 summarize(Total_sales = sum(sales_value)) %>%
                                 arrange(desc(Total_sales)) %>%
                                 head(15)
top15_spring_visual <- knitr::kable(top_15_spring_product_categories,format = "html",caption = "Top 15 Products Spring 2017")

#Generate Visual Dataframe for Summer 2017
top_15_summer_product_categories <- transactions_with_season_applied %>%
                                 inner_join(products_df, by = "product_id") %>%
                                 filter(season == "Summer 2017") %>%
                                 group_by(product_category) %>%
                                 summarize(Total_sales = sum(sales_value)) %>%
                                 arrange(desc(Total_sales)) %>%
                                 head(15)

top15_summer_visual <- knitr::kable(top_15_summer_product_categories,format = "html",caption = "Top 15 Products Summer 2017")

#Generate Visual Dataframe for Fall 2017
top_15_fall_product_categories <- transactions_with_season_applied %>%
                                 inner_join(products_df, by = "product_id") %>%
                                 filter(season == "Fall 2017") %>%
                                 group_by(product_category) %>%
                                 summarize(Total_sales = sum(sales_value)) %>%
                                 arrange(desc(Total_sales)) %>%
                                 head(15)

top15_fall_visual <- knitr::kable(top_15_fall_product_categories,format = "html",caption = "Top 15 Products Fall 2017")

#Generate Visual Dataframe for Winter 2018
top_15_winter2018_product_categories <- transactions_with_season_applied %>%
                                 inner_join(products_df, by = "product_id") %>%
                                 filter(season == "Winter 2018") %>%
                                 group_by(product_category) %>%
                                 summarize(Total_sales = sum(sales_value)) %>%
                                 arrange(desc(Total_sales)) %>%
                                 head(15)

top15_winter2018_visual <-knitr::kable(top_15_winter2018_product_categories,format = "html",caption = "Top 15 Products Winter 2018")




#Visualize Top 15 Product Categories of Each Season
top15_winter2017_visual
Top 15 Products Winter 2017
product_category Total_sales
COUPON/MISC ITEMS 51140.62
SOFT DRINKS 30054.34
BEEF 28393.95
FLUID MILK PRODUCTS 18978.17
CHEESE 17670.35
FRZN MEAT/MEAT DINNERS 15898.04
FROZEN PIZZA 14356.93
BAG SNACKS 13350.49
BAKED BREAD/BUNS/ROLLS 12615.11
BEERS/ALES 11836.25
COLD CEREAL 10075.79
CHICKEN 9433.51
SOUP 9349.75
DELI MEATS 9160.05
LUNCHMEAT 8825.10 
top15_spring_visual
Top 15 Products Spring 2017
product_category Total_sales
COUPON/MISC ITEMS 100041.24
SOFT DRINKS 50055.58
BEEF 46634.95
FLUID MILK PRODUCTS 28577.09
CHEESE 26321.02
FRZN MEAT/MEAT DINNERS 23342.73
BAG SNACKS 21355.34
BAKED BREAD/BUNS/ROLLS 20698.84
BEERS/ALES 19564.67
FROZEN PIZZA 18985.94
COLD CEREAL 15890.32
DELI MEATS 14751.68
CHICKEN 13770.91
PORK 12699.42
CIGARETTES 12648.50 
top15_summer_visual
Top 15 Products Summer 2017
product_category Total_sales
COUPON/MISC ITEMS 113160.80
SOFT DRINKS 46474.69
BEEF 45575.53
FLUID MILK PRODUCTS 28523.91
CHEESE 25140.32
BEERS/ALES 23661.91
BAG SNACKS 21582.02
BAKED BREAD/BUNS/ROLLS 21540.27
FRZN MEAT/MEAT DINNERS 21052.74
FROZEN PIZZA 19603.18
DELI MEATS 16401.75
COLD CEREAL 16226.49
CIGARETTES 14476.23
LUNCHMEAT 13969.63
ICE CREAM/MILK/SHERBTS 13372.93 
top15_fall_visual
Top 15 Products Fall 2017
product_category Total_sales
COUPON/MISC ITEMS 89809.54
BEEF 41612.74
SOFT DRINKS 40665.54
FLUID MILK PRODUCTS 29313.49
CHEESE 27302.25
FRZN MEAT/MEAT DINNERS 24559.59
BAKED BREAD/BUNS/ROLLS 20622.56
BAG SNACKS 20574.65
FROZEN PIZZA 20274.44
BEERS/ALES 19197.74
COLD CEREAL 15416.38
SOUP 15010.21
DELI MEATS 14451.07
CIGARETTES 13423.01
CHICKEN 12403.81 
top15_winter2018_visual
Top 15 Products Winter 2018
product_category Total_sales
COUPON/MISC ITEMS 31819.65
SOFT DRINKS 14876.15
BEEF 14397.37
FLUID MILK PRODUCTS 10968.02
CHEESE 10577.67
FRZN MEAT/MEAT DINNERS 8170.64
BAG SNACKS 8081.35
FROZEN PIZZA 7954.55
BEERS/ALES 7778.88
BAKED BREAD/BUNS/ROLLS 6952.92
COLD CEREAL 5400.37
DELI MEATS 5395.13
CANDY - PACKAGED 5287.74
SOUP 5192.37
CIGARETTES 5059.93

Direction of Analysis

We notice that the top product category across all seasons is “COUPON/MISC ITEMS”, and that the Summer Season has significantly more in sales than this product category than any other Season. Summer 2017 has about 113,000 in total sales for the category, where the next greatest is Spring 2017 with about 100,000 in sales.

As this product category isn’t very descriptive, we would like to see if the product types are a little more descriptive, and if they are descriptive, which product types compose the “COUPON/MISC ITEMS” product category.

Analyzing Top Product Types for the Coupons/Misc Items Product Category

seasonal_misc_product_category <- transactions_with_season_applied %>%
                                 inner_join(products_df,by = "product_id") %>%
                                 filter(product_category == "COUPON/MISC ITEMS") %>%
                                 group_by(product_type,season, .add = TRUE) %>%
                                 summarize(total_sales = sum(sales_value)) %>%
                                 arrange(desc(total_sales))

knitr::kable(seasonal_misc_product_category,format = "html",caption = "Top Product Types in Misc/Coupon Product Category By Season")
Top Product Types in Misc/Coupon Product Category By Season
product_type season total_sales
GASOLINE-REG UNLEADED Summer 2017 108109.86
GASOLINE-REG UNLEADED Spring 2017 96348.50
GASOLINE-REG UNLEADED Fall 2017 84573.85
GASOLINE-REG UNLEADED Winter 2017 47999.96
GASOLINE-REG UNLEADED Winter 2018 27084.95
OUTSIDE VENDORS GIFT CARDS Winter 2018 1625.00
ELECTRONIC GIFT CARDS ACTIVATI Winter 2017 1441.30
ELECTRONIC GIFT CARDS REFRESH Fall 2017 1413.00
ELECTRONIC GIFT CARDS ACTIVATI Spring 2017 1224.48
ELECTRONIC GIFT CARDS ACTIVATI Summer 2017 1202.80
ELECTRONIC GIFT CARDS ACTIVATI Winter 2018 1184.00
ELECTRONIC GIFT CARDS ACTIVATI Fall 2017 727.13
MEAT SUPPLIES Summer 2017 698.45
MEAT SUPPLIES Spring 2017 690.86
MASTERCARD GIFT CARD Winter 2018 682.35
OUTSIDE VENDORS GIFT CARDS Summer 2017 619.92
ELECTRONIC GIFT CARDS REFRESH Summer 2017 585.00
MEAT SUPPLIES Fall 2017 579.08
MEAT SUPPLIES Winter 2017 522.57
MISCELLANEOUS H & B AIDS Fall 2017 518.47
MISCELLANEOUS H & B AIDS Summer 2017 471.11
MISCELLANEOUS H & B AIDS Spring 2017 458.64
MISC SALES TRANS Summer 2017 442.88
MISC SALES TRANS Fall 2017 406.20
MISC SALES TRANS Spring 2017 390.04
MASTERCARD GIFT CARD Fall 2017 352.43
MISC SALES TRANS Winter 2017 317.98
MISCELLANEOUS H & B AIDS Winter 2017 300.18
MISCELLANEOUS H & B AIDS Winter 2018 289.35
ELECTRONIC GIFT CARDS REFRESH Winter 2018 270.00
OUTSIDE VENDORS GIFT CARDS Fall 2017 259.99
FLORAL DEPT KEY RING Spring 2017 247.22
PRODUCE DEPT KEY RING Summer 2017 242.13
TICKETS Summer 2017 240.00
AMERICAN EXPRESS GIFT CARD Fall 2017 221.75
MASTERCARD GIFT CARD Summer 2017 218.95
PRODUCE DEPT KEY RING Fall 2017 214.27
PRODUCE DEPT KEY RING Spring 2017 207.53
OUTSIDE VENDORS GIFT CARDS Spring 2017 199.98
MISC SALES TRANS Winter 2018 182.05
PRODUCE DEPT KEY RING Winter 2017 179.88
MEAT SUPPLIES Winter 2018 179.39
CAN DOG FOOD RATION (TRIX/VETS Fall 2017 169.05
FLORAL DEPT KEY RING Fall 2017 157.27
FLORAL DEPT KEY RING Winter 2017 151.25
AMERICAN EXPRESS GIFT CARD Summer 2017 144.75
MISC. BATH PRODUCTS Fall 2017 140.73
PRODUCE DEPT KEY RING Winter 2018 136.31
CAN DOG FOOD RATION (TRIX/VETS Spring 2017 118.29
OUTSIDE VENDORS GIFT CARDS Winter 2017 80.00
FLORAL DEPT KEY RING Summer 2017 70.37
AMERICAN EXPRESS GIFT CARD Winter 2018 57.90
MASTERCARD GIFT CARD Spring 2017 56.98
CAN DOG FOOD RATION (TRIX/VETS Winter 2018 55.90
CAN DOG FOOD RATION (TRIX/VETS Summer 2017 48.33
CAN DOG FOOD RATION (TRIX/VETS Winter 2017 43.81
TICKETS Winter 2017 42.00
FLORAL DEPT KEY RING Winter 2018 40.96
SOFT DRINKS CAN NON-CARB (EXCE Spring 2017 35.03
AMERICAN EXPRESS GIFT CARD Spring 2017 28.95
BULK CANDY Summer 2017 27.55
MASTERCARD GIFT CARD Winter 2017 27.49
SOFT DRINKS CAN NON-CARB (EXCE Fall 2017 26.46
SOFT DRINKS CAN NON-CARB (EXCE Summer 2017 22.36
BULK CANDY Fall 2017 18.96
MISC. BATH PRODUCTS Winter 2017 17.40
SOFT DRINKS CAN NON-CARB (EXCE Winter 2017 16.80
MISC. BATH PRODUCTS Spring 2017 15.73
CENTRAL SUPPLIES Winter 2018 15.00
BULK CANDY Winter 2018 11.50
JUICE Fall 2017 10.95
MISC. BATH PRODUCTS Summer 2017 10.37
TICKETS Fall 2017 10.00
TICKETS Spring 2017 10.00
COFF SHOP: RETAIL PACK BEVERAG Fall 2017 9.95
BULK CANDY Spring 2017 9.01
JUICE Summer 2017 5.97
MISC. BATH PRODUCTS Winter 2018 3.99
SOFT DRINKS CAN NON-CARB (EXCE Winter 2018 1.00
CENTRAL SUPPLIES Fall 2017 0.00
CENTRAL SUPPLIES Winter 2017 0.00

Conclusions from Table Above

We can see from the table above that about of the 113,000 in sales for the Misc/Coupon Product Category for Summer 2017, 108,000 of those sales came from fuel sales, more specified unleaded gasoline sales.

We also see that fuel sales compromised most of the product categories for all of the seasons, but we still see Summer 2017 has the most fuel sales of any of the seasons.

Recommendations to Marketing Team

Our analysis has led us to the finding that fuel sells best during the Summer season and there is evidence that there are higher sales for frozen ice products during the summer, as well as peaks in sales for plastic cups in May.Given this finding, the data analysis recommends to the marketing team that every gas station at Regork have an ice chest; paying special attention to stock it from May - August. The data analytics team also suggests that each gas station have plastic cups for sale at the convenience center at each of our fuel stations ready for purchase from May-August. We anticipate that we can have markups on these two products, plastic cups and frozen ice, at our gas stations due to the convenience factor.

Ice Chest Near Gas Station
Ice Chest Near Gas Station

Aside from the recommendation above we encourage the marketing team to craft a package deal involving a fuel purchase, frozen ice, and plastic cups. It could be a campaign focused on people getting out there with friends and family, and going out on a summer camping trip. Since we recommended to mark up the cost above, during the summer season we could mark down from our standard price, so that consumers really notice how good of a deal the package is.

Camping Trip
Camping Trip