Introduction

As a data scientist for Regork, I’m working to identify potential areas of growth where the company could invest in future resources to increase revenue and profits.

I am following the model of the steps of a typical data science project:

Package Requirement and Data Import

I utilized the following packages for data tidying, visualizing, and modeling. I utilized only the transaction and promotion data tables so I could focus on identifying variables of interest that could help grow sales.

knitr::opts_chunk$set(echo = TRUE)

library(tidyverse)
library(tidymodels)
library(lubridate)
library(completejourney)
library(corrplot)
library(visdat)
library(car)
library(effects)
library(vip)
library(randomForest)
library(gridExtra)
library(DT)

transactions <- get_transactions()
dim(transactions)
## [1] 1469307      11
promotions <- get_promotions()
dim(promotions)
## [1] 20940529        5
df <- transactions %>% 
  inner_join(demographics, by = "household_id") %>% 
  inner_join(products, by = "product_id") %>% 
  mutate(price = sales_value / quantity) %>% 
  mutate(quarter = quarter(transaction_timestamp, type = "year.quarter"))

datatable(head(df, 100), options = list(pageLength = 10))

Missing Data Analysis

colSums(is.na(df))
##          household_id              store_id             basket_id 
##                     0                     0                     0 
##            product_id              quantity           sales_value 
##                     0                     0                     0 
##           retail_disc           coupon_disc     coupon_match_disc 
##                     0                     0                     0 
##                  week transaction_timestamp                   age 
##                     0                     0                     0 
##                income        home_ownership        marital_status 
##                     0                220379                140958 
##        household_size        household_comp            kids_count 
##                     0                     0                     0 
##       manufacturer_id            department                 brand 
##                     0                     0                     0 
##      product_category          product_type          package_size 
##                  1240                   761                149377 
##                 price               quarter 
##                  1944                     0

Missing data was found in price, product_category, marital_status, product_type, and package_size. Given the large size of the dataset, I removed NAs when performing regression activities to more easily identify variables of interest to sales value.

Scatterplot Matrix Review

df %>%
  gather(-sales_value, key = "var", value = "value") %>%
  ggplot(aes(x = value, y = sales_value)) +
    geom_point() +
    stat_smooth() +
    facet_wrap(~ var, scales = "free") +
    theme_bw()

Review of plots associated with sales value.

Visualization / Exploratory Data Analysis (EDA)

#Histogram of Sales Value - Approximately normal after log transformation
#graph1 <- 
df %>% ggplot(aes(sales_value)) +
  geom_histogram(binwidth = .25) +
  scale_x_log10(labels = scales::dollar) +
  labs(title = "Histogram of Log Tranformed Outcome Variable",
       x = "Log of Sales Value",
       caption = "Approximately Normal After Log Transformation") +
  scale_y_continuous(labels = scales::comma) +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#Scatter plot of Quantity and Log of Sales Value
#graph2 <- 
df %>% ggplot(aes(quantity, sales_value)) +
  geom_point() +
  scale_y_log10(labels = scales::dollar) +
  scale_x_continuous(labels = scales::comma) +
  labs(title = "Scatterplot of Sales Value(Log) and Quantity",
       x = "Quantity",
       y = "Total Sales (Log)")

#Scatter plot of Quantity and Sales Value after Removing Low Quantities
#graph3 <- 
df %>% filter(quantity > 90) %>% 
  ggplot(aes(quantity, sales_value)) + 
  geom_point() +
  geom_smooth() +
  scale_x_continuous(labels = scales::comma) +
  scale_y_continuous(labels = scales::dollar) + 
  labs(title = "Scatterplot of Sales Value(Log) and Quantity",
       subtitle = "Quantity Greater than 90",
       x = "Quantity",
       y = "Total Sales ($)",
       caption = "Linear Relationship When Removing Low Quantities") +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#Price & Quantity Evaluation
df_price <- df %>% #Review High Price Items
  arrange(desc(price)) %>% 
  filter(price != "Inf") %>% 
  slice_max(price, n = 20)
#df_price
datatable(head(df_price, 100), options = list(pageLength = 10))
df_quantity <- df %>% #Review High Quantity Items
  arrange(desc(quantity)) %>% 
  slice_max(quantity, n = 20)
#df_quantity
datatable(head(df_quantity, 100), options = list(pageLength = 10))
df_quantity_gas <- df %>% #Review High Quantity Items Removing Gas
  filter(product_type != "GASOLINE-REG UNLEADED") %>% 
  arrange(desc(quantity)) %>% 
  slice_max(quantity, n = 20)
#df_quantity_gas
datatable(head(df_quantity_gas, 100), options = list(pageLength = 10))
#Strip Plot Showing Sales Value by Age Group
#graph4 <- 
df %>% 
  group_by(age) %>% 
  filter(sales_value < 100) %>% 
  ggplot(aes(age, sales_value, color = income)) +
  geom_jitter(width = .2) +
  stat_summary(fun = "median", geom = "point", color = "red", size = 2) +
  labs(title = "Strip Plot",
       subtitle = "Sales Values Less Than $100 by Age",
       x = "Age Group",
       y = "Sales Value",
       color = "Income",
       caption = "High Income Earners Spread Throughout Age Groups") +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#Facet Wrap Showing Total Sales by Income Group
df_facet <- df %>% 
  group_by(product_id, income) %>% 
  summarise(total_sales_value = sum(sales_value), total_quantity = sum(quantity))
#graph5 <- 
ggplot(df_facet, aes(total_quantity, total_sales_value, color = income)) +
  geom_point() +
  geom_smooth() +
  facet_wrap(~income) +
  scale_x_continuous(labels = scales::label_number_si()) +
  scale_y_continuous(labels = scales::dollar) %>% 
  labs(title = "Scatterplot Smoothed Facet Wrap",
       subtitle = "Quantity vs. Sales by Income",
       x = "Quantity",
       y = "Total Sales ($)",
       color = "Income Range",
       caption = "Middle Income Earners Spend Greatest Amount") +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#DotPlot Showing Total Sales by Product Category
df_dotplot <- df %>% 
  group_by(product_category) %>% 
  summarise(total_quantity = sum(quantity), total_sales_value = sum(sales_value)) %>% 
  arrange(desc(total_sales_value)) %>% 
  slice_max(total_sales_value, n = 25)
#df_dotplot
  
#graph6 <- 
df_dotplot %>% 
  mutate(pct = total_sales_value / sum(total_sales_value)) %>% 
  ggplot(aes(pct, reorder(product_category, pct))) +
  geom_point(color = "blue") +
  scale_x_continuous(labels = scales::percent) + 
  labs(title = "Dot Plot",
       subtitle = "Product Category Percentage of Total",
       x = "Percent",
       y = "Product Category",
       caption = "Coupon / Misc Items has Greatest % of Spend for Top 25 Product Categories") +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#Sales by Quarter

#graph7 <- 
df %>%
  mutate(quarter = factor(quarter),
         income = as.factor(income)) %>%
  group_by(quarter, income) %>%
  summarise(total_sales = sum(sales_value)) %>%
  ggplot(aes(x = quarter, y = total_sales, color = income)) +
  geom_jitter(width = 0.2) +
  scale_y_continuous(labels = scales::dollar) +
  labs(title = "Jitter Plot of Total Sales by Quarter and Income",
       x = "Quarter",
       y = "Total Sales",
       caption = "Middle Income Groups Produce Highest Sales Each Quarter") +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#graph8 <- 
df %>%
  mutate(quarter = factor(quarter),
         age = as.factor(age)) %>%
  group_by(quarter, age) %>%
  summarise(total_sales = sum(sales_value)) %>%
  ggplot(aes(x = reorder(quarter, total_sales), y = total_sales, fill = age)) +
  geom_bar(stat = "identity", position = position_dodge(), alpha = 0.75) +
  scale_y_continuous(labels = scales::dollar) +
  labs(title = "Bar Plot of Total Sales by Quarter and Age",
       x = "Quarter",
       y = "Total Sales",
       caption = "Ages 35 - 44 and 45 - 54 Produce Highest Sales") +
  theme(plot.caption = element_text(size = 12, color = "Blue"))

#Display plots in a grid
#grid.arrange(graph1, graph2, graph3, graph4, 
             #graph5, graph6, graph7, graph8, 
             #ncol = 2)

Visualization / Exploratory data analysis (EDA) was conducted looking at various attributes.

The dependent variable sales value was logtransformed due to a heavy right skew, which is typical for monetary variables given the values can’t go below zero. I looked at the relationship between quantity and sales value since quantity has a high correlation with sales value (see Correlations tab). However, it’s interesting that when removing the quantities below 90, sales value and quantity have a near linear relationship. This had me investigate price as well utilizing the formula sales value divided by quantity. The highest price items are coupons / misc items (gift cards), patio sets, grills, beef, phone and accessories, television, and seafood. This would insinuate that summer could result in higher sales based on some of these items. When investigating this visually, the sales increased each quarter in 2017, resulting in Q4 being the highest. I also looked at high quantity items, with gasoline being by far the highest. With gasoline removed, the high quantity items are generally low-priced and from the Grocery and Drug GM departments.

Other observations in EDA are the 35 - 44 and 45 - 54 age groups tend to have the highest sales value, as well as a mix of both low and high income earners. However, the $35 - $49K, $50 - $74K and $75 - $99K income earners purchase the highest quantities and have the greatest sales. As far as product category, gift cards and beef were again identified as product categories composed of the highest percentage of spend. Other foods, drinks, alcohol, and cigarettes were also identified as high percentage of total spend.

Correlations

numervars <- df %>% select(c(quantity, sales_value, retail_disc, coupon_disc, coupon_match_disc)) %>% as.tibble()
#numervars

Correl <- cor(numervars)
head(round(Correl, 2))
##                   quantity sales_value retail_disc coupon_disc
## quantity              1.00        0.63        0.05       -0.01
## sales_value           0.63        1.00        0.24        0.06
## retail_disc           0.05        0.24        1.00        0.08
## coupon_disc          -0.01        0.06        0.08        1.00
## coupon_match_disc    -0.01       -0.01        0.01        0.28
##                   coupon_match_disc
## quantity                      -0.01
## sales_value                   -0.01
## retail_disc                    0.01
## coupon_disc                    0.28
## coupon_match_disc              1.00
corrplot(Correl, method = "number")

The numeric variables wwere investigated for correlations. The highest correlation is sales value and quantity, which is to be expected. The other correlations are relatively small.

Model Development / Summary

#Feature Engineering
numervars <- c("quantity", "retail_disc", "coupon_disc", "coupon_match_disc")
trbl_vars <- c("household_id", "store_id", "basket_id", "product_id", "manufacturer_id", "price")
charvars <- c("department", "product_category", "product_type", "package_size")
ordinvars <- c("age", "income", "home_ownership", "marital_status", "household_size", "household_comp", "kids_count")

df_reduced <- df %>% select(-trbl_vars) %>% na.omit(df) %>% filter(is.finite(sales_value))

datatable(head(df_reduced, 100), options = list(pageLength = 10))
#Preprocessing
set.seed(3221)

df <- mutate(df, sales_value = log10(sales_value))

df_split <- initial_split(df_reduced, prop = .7, strata = sales_value)
df_train <- training(df_split)
df_test <- testing(df_split)

df_rec <- 
  recipe(sales_value ~ ., data = df_train) %>% 
  step_unknown(all_nominal_predictors(), new_level = "NA") %>% 
  step_other(all_nominal_predictors(), threshold = .05, other = "other") %>%
  step_dummy(charvars)

#---Linear Model---
lm_model <- linear_reg() %>% set_engine("lm")

#Workflow
lm_wflow <- 
  workflow() %>% 
  add_model(lm_model) %>% 
  add_recipe(df_rec)

#Fit
lm_fit <- lm_wflow %>% 
  fit(data = df_train)

#Statistics
lm_fit %>% 
  extract_fit_parsnip() %>% 
  tidy() %>% 
  mutate(estimate_exp = exp(estimate)) %>% 
  filter(p.value < .05) %>% 
  arrange(desc(statistic))
## # A tibble: 36 × 6
##    term                       estimate std.error statistic  p.value estimate_exp
##    <chr>                         <dbl>     <dbl>     <dbl>    <dbl>        <dbl>
##  1 quantity                      0.554   0.00524     106.  0                1.74
##  2 retail_disc                   0.507   0.00498     102.  0                1.66
##  3 coupon_disc                   1.41    0.0256       54.8 0                4.08
##  4 product_type_other            1.75    0.0334       52.5 0                5.76
##  5 incomeother                   0.487   0.0246       19.8 1.63e-87         1.63
##  6 income150-174K                0.475   0.0273       17.4 1.12e-67         1.61
##  7 income125-149K                0.374   0.0263       14.2 7.04e-46         1.45
##  8 product_category_other        0.302   0.0226       13.4 1.05e-40         1.35
##  9 household_comp2 Adults No…    1.08    0.0862       12.5 6.60e-36         2.94
## 10 home_ownershipHomeowner       0.219   0.0179       12.2 1.94e-34         1.25
## # ℹ 26 more rows
#10-Fold CV Regression
set.seed(6408)
kfolds <- vfold_cv(df_train, v = 10, strata = sales_value)

keep_pred <- control_resamples(save_pred = TRUE, save_workflow = TRUE)

lm_fit_cv <- lm_wflow %>% 
  fit_resamples(kfolds)

collect_metrics(lm_fit_cv)
## # A tibble: 2 × 6
##   .metric .estimator  mean     n std_err .config             
##   <chr>   <chr>      <dbl> <int>   <dbl> <chr>               
## 1 rmse    standard   2.46     10 0.0349  Preprocessor1_Model1
## 2 rsq     standard   0.174    10 0.00601 Preprocessor1_Model1
collect_metrics(lm_fit_cv, summarize = FALSE) %>% 
  filter(.metric == 'rmse')
## # A tibble: 10 × 5
##    id     .metric .estimator .estimate .config             
##    <chr>  <chr>   <chr>          <dbl> <chr>               
##  1 Fold01 rmse    standard        2.53 Preprocessor1_Model1
##  2 Fold02 rmse    standard        2.43 Preprocessor1_Model1
##  3 Fold03 rmse    standard        2.33 Preprocessor1_Model1
##  4 Fold04 rmse    standard        2.35 Preprocessor1_Model1
##  5 Fold05 rmse    standard        2.65 Preprocessor1_Model1
##  6 Fold06 rmse    standard        2.41 Preprocessor1_Model1
##  7 Fold07 rmse    standard        2.45 Preprocessor1_Model1
##  8 Fold08 rmse    standard        2.37 Preprocessor1_Model1
##  9 Fold09 rmse    standard        2.44 Preprocessor1_Model1
## 10 Fold10 rmse    standard        2.63 Preprocessor1_Model1
collect_metrics(lm_fit_cv, summarize = FALSE) %>% 
  filter(.metric == 'rsq')
## # A tibble: 10 × 5
##    id     .metric .estimator .estimate .config             
##    <chr>  <chr>   <chr>          <dbl> <chr>               
##  1 Fold01 rsq     standard       0.151 Preprocessor1_Model1
##  2 Fold02 rsq     standard       0.166 Preprocessor1_Model1
##  3 Fold03 rsq     standard       0.170 Preprocessor1_Model1
##  4 Fold04 rsq     standard       0.184 Preprocessor1_Model1
##  5 Fold05 rsq     standard       0.208 Preprocessor1_Model1
##  6 Fold06 rsq     standard       0.178 Preprocessor1_Model1
##  7 Fold07 rsq     standard       0.176 Preprocessor1_Model1
##  8 Fold08 rsq     standard       0.183 Preprocessor1_Model1
##  9 Fold09 rsq     standard       0.180 Preprocessor1_Model1
## 10 Fold10 rsq     standard       0.138 Preprocessor1_Model1
#Fit to Data
#Train
lm_fit %>% 
  predict(df_train) %>% 
  bind_cols(df_train %>% select(sales_value)) %>% 
  rmse(truth = sales_value, estimate = .pred)
## # A tibble: 1 × 3
##   .metric .estimator .estimate
##   <chr>   <chr>          <dbl>
## 1 rmse    standard        2.47
#Test
lm_fit %>% 
  predict(df_test) %>% 
  bind_cols(df_test %>% select(sales_value)) %>% 
  rmse(truth = sales_value, estimate = .pred)
## # A tibble: 1 × 3
##   .metric .estimator .estimate
##   <chr>   <chr>          <dbl>
## 1 rmse    standard        2.52
#Variable Importance
#lm_fit %>% 
  #vip::vip(num_features = 25)

#---Random Forest---

rf_model <- 
  rand_forest(trees = 1000) %>% 
  set_engine("ranger") %>% 
  set_mode("regression")

#Workflow
rf_wflow <- 
  workflow() %>% 
  add_formula(sales_value ~ quantity + retail_disc + coupon_disc + coupon_match_disc +  
                department + product_category + product_type + package_size +
                age + income + home_ownership + marital_status + household_size + 
                household_comp + kids_count) %>% 
  add_model(rf_model)

#Fit
rf_fit <- rf_wflow %>% 
  fit(data = df_train)

#Statistics
rf_fit %>% 
  extract_fit_parsnip() #%>% vip(num_features = 20)
## parsnip model object
## 
## Ranger result
## 
## Call:
##  ranger::ranger(x = maybe_data_frame(x), y = y, num.trees = ~1000,      num.threads = 1, verbose = FALSE, seed = sample.int(10^5,          1)) 
## 
## Type:                             Regression 
## Number of trees:                  1000 
## Sample size:                      294999 
## Number of independent variables:  15 
## Mtry:                             3 
## Target node size:                 5 
## Variable importance mode:         none 
## Splitrule:                        variance 
## OOB prediction error (MSE):       1.930243 
## R squared (OOB):                  0.7391276

Feature Engineering

Feature engineering included the following:

  • Dummy variables were created for the following character variables: department, product_category, product_type, and package_size.
  • The other character variables were removed from the analysis since it resulted in excessive dummy variable creation: household_id, store_id, basket_id, product_id, and manufacturer_id.
  • Ordinal variables were included and left as factors.

Preprocessing

The following preprocessing steps were taken:

  • The log of sales value was created.
  • The data was split between training and test (70% and 30% respectively).
  • Any unknowns of nominal variables were recategorized as NA.
  • 5% of nominal variables were lumped into the “other” category.
  • Dummy variables were created for the above variables mentioned.

Model Results

A linear model was run, and statistics were extracted and sorted on the statistic. The coefficients were exponentiated in the estimate_exp column for interpretation since the log was taken of the outcome variable. The results are read as the following: For every one-unit increase in the estimate, the sales increase by the associated factor (or percent if multiplied by 100). Although most variables came back as significant read by their p-value, the large size of the sample plays a factor. As a model as a whole, the r-square value is low at approximately 0.17.

A random forest model was also run with a much improved r-square of above 0.7. This could be due to the non-linearity and interaction effects not being taken into consideration in the linear model.

Summary

I addressed the need and capability of Regork increasing sales. This was accomplished through exploratory data analysis and modeling. I identified age groups, income groups, and mechanisms to target and utilize for sales growth.

Based on the results of the regression model, investment in retail and coupon discounts is key to increasing sales. This includes what they are currently being used for and expansion to other products. Including the results in visualization, targeting high-priced products utilized in “warm months” in their respective quarter/season with retail and coupon discounts may help increase sales. Both graphing and regression results identified income groups $50 - $74K and $75 - $99K as being targets in higher sales potential.

Limitations

Limitations of this analysis is there are many other data sets that can be correlated with what I’ve used in this report. As I mentioned previously, not including non-linear terms restricted the associations with sales. More models could be considered, and model tuning could be performed to enhance model performance. Multicollinearity could be examined, and dimension reduction techniques could be performed to narrow in on truly significant variables for a parsimonious model.

Further Visualization

#DotPlot Showing Total Coupon Discounts by Product Category
df_vis1 <- df %>% 
  group_by(product_category) %>% 
  summarise(total_coup_disc = sum(coupon_disc), total_ret_disc = sum(retail_disc)) %>%  
  arrange(desc(total_coup_disc)) %>% 
  slice_max(total_coup_disc, n = 20)
#df_vis1
  
#graph9 <- 
df_vis1 %>% 
  mutate(pct = total_coup_disc / sum(total_coup_disc)) %>% 
  ggplot(aes(pct, reorder(product_category, pct))) +
  geom_point(color = "blue") +
  scale_x_continuous(labels = scales::percent) + 
  labs(title = "Dot Plot",
       subtitle = "Product Category Percentage of Total Coupon Discount",
       x = "Percent",
       y = "Product Category")

#DotPlot Showing Total Coupon Discounts by Product Category
df_vis2 <- df %>% 
  group_by(product_category) %>% 
  summarise(total_coup_disc = sum(coupon_disc), total_ret_disc = sum(retail_disc)) %>%  
  arrange(desc(total_ret_disc)) %>% 
  slice_max(total_ret_disc, n = 20)
#df_vis1
  
#graph10 <- 
df_vis2 %>% 
  mutate(pct = total_ret_disc / sum(total_ret_disc)) %>% 
  ggplot(aes(pct, reorder(product_category, pct))) +
  geom_point(color = "red") +
  scale_x_continuous(labels = scales::percent) + 
  labs(title = "Dot Plot",
       subtitle = "Product Category Percentage of Total Retail Discount",
       x = "Percent",
       y = "Product Category")

df_vis1a <- df %>% 
  group_by(product_category) %>% 
  summarise(avg_price = mean(price)) %>% 
  arrange(desc(avg_price)) %>% 
  slice_max(avg_price, n = 25)
#df_vis1a

datatable(head(df_vis1a, 100), options = list(pageLength = 10))

Following the results of the regression model, I further investigated retail and coupon discounts being offered given they were significantly associated with sales. Other than beef, the higher price items identified in EDA are not included as high in discounts. Given their ability to increase sales based on regression results, it may be advantageous to include more discount capabilities. The target time frame could be in the summer when those products are more likely to be bought. Another interesting observation is cigarettes are identified as high in coupon discounts. On average, smoking cessation products by product category have the highest price. This is consistent with cigarette companies also being involved in smoking cessation products. Offering discounts in that area could be profitable as well.