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:
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))
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.
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.
#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.
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.
#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:
department, product_category,
product_type, and package_size.household_id, store_id,
basket_id, product_id, and
manufacturer_id.Preprocessing
The following preprocessing steps were taken:
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.
#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.