Introduction

As a national grocery chain, Regork faces the challenge of identifying potential growth areas to boost revenue and profitability in an increasingly competitive market. We must constantly be on the lookout for opportunities to capitalize on trends in our customer purchasing behavior data and optimize our product offerings. Through leveraging our customer and transaction data, we can explore potential areas where Regork can strengthen its market position. The business question we explore in this report is: “Which customer demographic or product group has the most potential for increasing revenue and profit?”

To address this question, we utilize our company’s sales data, which includes transaction-level data such as product sales, as well as customer demographics and promotional activities. We approach this analysis by cleaning and joining the relevant datasets, then creating visualizations to identify trends and relationships in the data. Through our analysis, we have identified some key insights, and we propose to invest in targeted marketing campaigns focusing on the top-performing product categories and targeting our highest volume demographic groups. Additionally, we advise implementing cross-promotion strategies based on product pairings that we have identified in order to boost sales across multiple categories.

For the convenience of your viewing, we have hidden all of the code chunks in this report. Please expand the code using the “show” buttons if you wish to view the raw code involved in the construction of this report.

Required Packages

To perform our analysis, the following R packages are required.

  • tidyverse - This collection of R packages designed for data signs will allow us to easily process and visualize data.
  • completejourney - This package contains datasets characterizing house household level transactions over one year from a group of 2,469 households who are frequent shoppers at a grocery store. This will be our source of data for our products, transactions, promotions, and customer demographics for Regork.
  • viridis [optional] - The viridis color scales will allow our visualizations have pretty colors that are easy to read and colorblind friendly.
  • knitr [optional] - The knitr package allows us to have more control of our report outputs.
# Required libraries
suppressWarnings(suppressMessages(library(tidyverse)))       # Data wrangling and visualization
suppressWarnings(suppressMessages(library(completejourney))) # Access the CompleteJourney data
suppressWarnings(suppressMessages(library(viridis)))         # [Optional] For data visualization
suppressWarnings(suppressMessages(library(knitr)))           # [Optional] For knitting utility

knitr::opts_chunk$set(warning = FALSE, message = FALSE)

print("Packages loaded.")
## [1] "Packages loaded."

Data Preparation

We begin by importing the necessary datasets from the Complete Journey package. We will primarily use transaction, customer, and promotions data. These datasets will be joined to create a master dataset for further analysis.

# Load Complete Journey datasets
transactions <- transactions_sample  
promotions <- promotions_sample
demographics <- demographics
products <- products

# View structure of the datasets
print("Transactions dataset")
## [1] "Transactions dataset"
glimpse(transactions)
## Rows: 75,000
## Columns: 11
## $ household_id          <chr> "2261", "2131", "511", "400", "918", "718", "868…
## $ store_id              <chr> "309", "368", "316", "388", "340", "324", "323",…
## $ basket_id             <chr> "31625220889", "32053127496", "32445856036", "31…
## $ product_id            <chr> "940996", "873902", "847901", "13094913", "10856…
## $ quantity              <dbl> 1, 1, 1, 2, 1, 1, 1, 1, 2, 1, 1, 1, 1, 1, 1, 1, …
## $ sales_value           <dbl> 3.86, 1.59, 1.00, 11.87, 1.29, 2.50, 3.49, 2.00,…
## $ retail_disc           <dbl> 0.43, 0.90, 0.69, 2.90, 0.00, 0.49, 0.00, 1.79, …
## $ coupon_disc           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ coupon_match_disc     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ week                  <int> 5, 10, 13, 8, 10, 15, 10, 33, 2, 16, 47, 18, 18,…
## $ transaction_timestamp <dttm> 2017-01-28 14:06:53, 2017-02-28 22:31:57, 2017-…
print("Promotions dataset")
## [1] "Promotions dataset"
glimpse(promotions)
## Rows: 360,535
## Columns: 5
## $ product_id       <chr> "1000050", "1000092", "1000214", "1000235", "1000235"…
## $ store_id         <chr> "337", "317", "317", "317", "337", "317", "317", "337…
## $ display_location <fct> 3, 0, 6, 0, 0, 9, 0, 0, 0, 5, 9, 0, 0, 9, 0, 9, 0, 6,…
## $ mailer_location  <fct> 0, A, 0, A, A, 0, A, A, A, A, 0, A, A, A, A, 0, A, 0,…
## $ week             <int> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,…
print("Demographics dataset")
## [1] "Demographics dataset"
glimpse(demographics)
## Rows: 801
## Columns: 8
## $ household_id   <chr> "1", "1001", "1003", "1004", "101", "1012", "1014", "10…
## $ age            <ord> 65+, 45-54, 35-44, 25-34, 45-54, 35-44, 45-54, 45-54, 4…
## $ income         <ord> 35-49K, 50-74K, 25-34K, 15-24K, Under 15K, 35-49K, 15-2…
## $ home_ownership <ord> Homeowner, Homeowner, NA, NA, Homeowner, NA, NA, Homeow…
## $ marital_status <ord> Married, Unmarried, Unmarried, Unmarried, Married, Marr…
## $ household_size <ord> 2, 1, 1, 1, 4, 5+, 4, 1, 5+, 2, 5+, 4, 2, 1, 5+, 1, 1, …
## $ household_comp <ord> 2 Adults No Kids, 1 Adult No Kids, 1 Adult No Kids, 1 A…
## $ kids_count     <ord> 0, 0, 0, 0, 2, 3+, 2, 0, 3+, 0, 3+, 2, 0, 0, 3+, 0, 0, …
print("Products dataset")
## [1] "Products dataset"
glimpse(products)
## Rows: 92,331
## Columns: 7
## $ product_id       <chr> "25671", "26081", "26093", "26190", "26355", "26426",…
## $ manufacturer_id  <chr> "2", "2", "69", "69", "69", "69", "69", "69", "69", "…
## $ department       <chr> "GROCERY", "MISCELLANEOUS", "PASTRY", "GROCERY", "GRO…
## $ brand            <fct> National, National, Private, Private, Private, Privat…
## $ product_category <chr> "FRZN ICE", NA, "BREAD", "FRUIT - SHELF STABLE", "COO…
## $ product_type     <chr> "ICE - CRUSHED/CUBED", NA, "BREAD:ITALIAN/FRENCH", "A…
## $ package_size     <chr> "22 LB", NA, NA, "50 OZ", "14 OZ", "2.5 OZ", "16 OZ",…

To investigate the demographics with the most potential to increase revenues, we will need to join the transactions and promotions datasets to identify how promotions impact product purchases, and we will also join transactions with demographics to explore purchasing behavior across different customer groups. Finally, we will need the product information from the products dataset.

# Join transactions, promotions, demographics, and products
complete_data <- transactions %>%
  left_join(promotions, by = "product_id") %>%
  left_join(demographics, by = "household_id") %>%
  left_join(products, by = "product_id") %>%
  drop_na()

# View the resulting dataset
print("The complete dataset, after joining all of the data above")
## [1] "The complete dataset, after joining all of the data above"
glimpse(complete_data)
## Rows: 281,165
## Columns: 28
## $ household_id          <chr> "868", "868", "868", "868", "868", "868", "868",…
## $ store_id.x            <chr> "323", "323", "323", "323", "323", "323", "323",…
## $ basket_id             <chr> "32074722463", "32074722463", "32074722463", "32…
## $ product_id            <chr> "9884484", "9884484", "9884484", "9884484", "988…
## $ quantity              <dbl> 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, …
## $ sales_value           <dbl> 3.49, 3.49, 3.49, 3.49, 3.49, 3.49, 3.49, 3.49, …
## $ retail_disc           <dbl> 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, 0.00, …
## $ coupon_disc           <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ coupon_match_disc     <dbl> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ week.x                <int> 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, 10, …
## $ transaction_timestamp <dttm> 2017-03-02 17:45:37, 2017-03-02 17:45:37, 2017-…
## $ store_id.y            <chr> "317", "337", "317", "337", "317", "337", "317",…
## $ display_location      <fct> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ mailer_location       <fct> A, A, A, A, A, A, A, A, A, A, D, D, A, A, A, A, …
## $ week.y                <int> 2, 2, 5, 5, 21, 21, 25, 25, 33, 33, 41, 41, 3, 3…
## $ age                   <ord> 65+, 65+, 65+, 65+, 65+, 65+, 65+, 65+, 65+, 65+…
## $ income                <ord> 35-49K, 35-49K, 35-49K, 35-49K, 35-49K, 35-49K, …
## $ home_ownership        <ord> Homeowner, Homeowner, Homeowner, Homeowner, Home…
## $ marital_status        <ord> Married, Married, Married, Married, Married, Mar…
## $ household_size        <ord> 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 1, 1, 1, 1, …
## $ household_comp        <ord> 2 Adults No Kids, 2 Adults No Kids, 2 Adults No …
## $ kids_count            <ord> 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, …
## $ manufacturer_id       <chr> "1102", "1102", "1102", "1102", "1102", "1102", …
## $ department            <chr> "GROCERY", "GROCERY", "GROCERY", "GROCERY", "GRO…
## $ brand                 <fct> National, National, National, National, National…
## $ product_category      <chr> "BAKED SWEET GOODS", "BAKED SWEET GOODS", "BAKED…
## $ product_type          <chr> "SNACK CAKE - MULTI PACK", "SNACK CAKE - MULTI P…
## $ package_size          <chr> "8 OZ", "8 OZ", "8 OZ", "8 OZ", "8 OZ", "8 OZ", …

Data Visualization

In this section, we visualize the data to answer the business question. We will focus on identifying potential areas of growth by exploring product purchasing behavior, demographic trends, and the impact of promotions.

1. Revenue by Demographics

We start by analyzing which customer demographics contribute the most to revenue and identify areas with growth potential. In order to do this, we will graph the total sales value by age and income brackets.

# Group by demographic variables and summarize revenue
revenue_by_demo <- complete_data %>%
  group_by(income, age, household_size) %>%
  summarize(total_revenue = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_revenue))

# Graph the results
ggplot(revenue_by_demo, aes(x = age, y = total_revenue, fill = income)) +
  geom_bar(stat = "identity", position = "dodge") +
  labs(title = "Total Revenue by Age and Income Bracket",
       x = "Age Bracket", y = "Total Revenue") + 
  scale_fill_viridis(discrete = TRUE) + 
  theme_bw()

2. Impact of Promotions on Product Sales

Next, we will examine how promotions impact sales and whether there are opportunities to increase the effectiveness of promotional campaigns. We decide to graph the top 30 selling product types, and stack the discounts applied on top of the sales values to determine the impact of the discounts. First, we graph the graph normally to highlight the sales values. Then, we construct a supplementary graph using the log10 of the sales value for the y-axis to better display each discount type.

# Summarize total sales and discount impact by product category
discount_impact <- complete_data %>%
  group_by(product_category) %>%
  summarize(
    total_sales = sum(sales_value, na.rm = TRUE),
    total_retail_disc = sum(retail_disc, na.rm = TRUE),
    total_coupon_disc = sum(coupon_disc, na.rm = TRUE),
    total_coupon_match_disc = sum(coupon_match_disc, na.rm = TRUE),
    net_sales = total_sales + (total_retail_disc + total_coupon_disc + total_coupon_match_disc)
  ) %>%
  arrange(desc(net_sales)) %>%
  top_n(30, net_sales) # Select the top 30 product categories

# Reshape the data to create the stacked bar plot
discount_impact_long <- discount_impact %>%
  select(product_category, net_sales, total_retail_disc, total_coupon_disc, total_coupon_match_disc, total_sales) %>%
  gather(key = "discount_type", value = "amount", total_retail_disc:total_sales)

# Create stacked bar plot (no log10)
ggplot(discount_impact_long, aes(x = reorder(product_category, -amount), y = amount, fill = discount_type)) +
  geom_bar(stat = "identity") +
  labs(title = "Impact of Discounts on Product Sales (Top 30 Categories)",
       x = "Product Category", y = "Sales Volume ($)",
       fill = "Sales/Discount Type") +
  theme(axis.text.x = element_text(angle = 60, hjust = 1)) +
  scale_fill_manual(values = c( 
    "total_retail_disc" = "lightblue", 
    "total_coupon_disc" = "lightgreen", 
    "total_coupon_match_disc" = "lightcoral",
    "total_sales" = "dodgerblue"))

# Create the stacked bar plot. I decided to use log10 for the y-axis so that the impact of the discounts can be seen more clearly. 
ggplot(discount_impact_long, aes(x = reorder(product_category, -amount), y = amount, fill = discount_type)) +
  geom_bar(stat = "identity") +
  labs(title = "Impact of Discounts on Product Sales (Top 30 Categories) (log10)",
       x = "Product Category", y = "Sales Volume ($) (log10)",
       fill = "Sales/Discount Type") +
  theme(axis.text.x = element_text(angle = 60, hjust = 1)) +
  scale_fill_manual(values = c( 
    "total_retail_disc" = "lightblue", 
    "total_coupon_disc" = "lightgreen", 
    "total_coupon_match_disc" = "lightcoral",
    "total_sales" = "dodgerblue")) +
  scale_y_log10()

3. Product Pairing Analysis

Do certain products tend to be purchased together? If so, this could provide opportunities for product bundling or cross-promotions. This heatmap can reveal the frequency of customers buying certain pairs of product types together. For this demonstration, we again applied the top 30 product types based on sales value.

# Summarize total sales per product category and filter for the top 30
top_30_categories <- complete_data %>%
  group_by(product_category) %>%
  summarize(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  arrange(desc(total_sales)) %>%
  top_n(30, total_sales)

# Filter the original data to only include top 30 categories
filtered_data <- complete_data %>%
  filter(product_category %in% top_30_categories$product_category)

# Pivot the data so that each row represents a household and each column represents a category
product_pairing <- filtered_data %>%
  group_by(household_id, product_category) %>%
  summarize(total_sales = sum(sales_value, na.rm = FALSE)) %>%
  pivot_wider(names_from = product_category, values_from = total_sales, values_fill = 0)

# Remove first column so that only the numeric values stay in preparation for correlation matrix
numeric_product_pairing <- product_pairing[-1] 

# Calculate the correlation matrix
product_correlation <- cor(numeric_product_pairing)

# Visualize the correlation matrix as a heatmap. I used bigger margins so that it displays the axes correctly. Also, I wanted to add a color gradient legend, but I couldn't figure out how to do it after searching online and exploring the heatmap() function.
heatmap(product_correlation, 
        main = "Product Pairing Heatmap (Top 30 Product Categories)",
        Colv = NA,
        Rowv = NA,
        symm = TRUE,
        col = viridis::inferno(nrow(numeric_product_pairing)), 
        margins = c(12, 12))

Summary and Recommendations

Key Insights

  1. Demographic Revenue Contribution: Customers in the 45-54 age bracket and mid-income levels, particularly the 35-99k income brackets, contribute to the highest revenue. Targeted marketing to these groups could yield further growth. It may be helpful to acquire data on the demographics of shoppers in general and compare that to our results for further investigation.

  2. Promotion Effectiveness: Promotions significantly boosts sales in most of the popular product categories. The exceptions to this pattern are mostly meat products, such as the miscellaneous meats, lunchmeat, and bacon categories, where only retail discounts are applied. Another notable high selling category which does not employ coupons or discounts is the beers and ales category. As we only viewed the promotion values of the top 30 product categories, we can consider expanding this analysis to a greater number of product categories. Additionally, the impact of promotion effectiveness can be further demonstrated by collecting sales data from periods of time including and not including promotions and comparing.

  3. Product Pairing Opportunities: Commonly purchased product pairs, such as soft drinks and heat/serve products, provide opportunities for cross-promotions or bundling. Keep in mind that we only examined the top 30 products. Product categories with fewer sales can also be examined for product pairing to potentially increase their sales. However, conducting this analysis on less popular items may not result in conclusive data due to the lower volume of sales. While individual households may have unique purchasing patterns, this product pairing information give us insights on product types which are more likely to be purchased together.

Recommendations

  1. Targeted Marketing Campaigns: We suggest developing campaigns focused on the 45-54 age group, particularly targeting mid-income households. These are our most common customers, and focusing on sales directed towards these groups will boost profits from the most typical consumers. We can also consider advertising or deploying outreach tactics to the groups which are not as represented in our customer demographics to broaden our potential clientele.

  2. Enhanced Promotion Strategies: We advise boosting promotions in categories that show lower promotional impact to capitalize on potential growth. Particularly, we predict that introducing more promotion events for meat products and beers/ales has the potential to drive up sales for those popular items. We should continue running promotions for our popular items. Furthermore, we can examine whether running promotions on less popular products impacts their sales by creating and comparing this graph before and after the promotion.

  3. Product Bundling: We recommend introducing product bundles or promotions for pairs of products. For example, Heat/Serve products could be paired with Breakfast sausage/sandwich items or soft drinks, and cheeses could be paired with fluid milk products, eggs, or shelf stable meats. The product pairing heatmap gives valuable insight on what products are often purchased together and can be leveraged to create bundles to encourage larger basket sizes and increase average transaction values.