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

# Libraries
library(completejourney)
library(tidyverse)

# Data setup
transactions <- get_transactions()
promotions <- get_promotions()

# Frozen products
frozen_products <- products %>%
  mutate(product_category = if_else(product_category=="FROZEN",
                               product_type,
                               product_category)) %>%
  mutate(product_category = str_replace(
    product_category,
    pattern = "FRZN",
    replacement = "FROZEN"
  )) %>%
  filter(str_detect(product_category, "FROZEN")) %>%
  mutate(product_category = fct_collapse(
    product_category,
    "FROZEN MEAT" = c("FROZEN CHICKEN",
                      "FROZEN MEAT",
                      "FROZEN MEAT/MEAT DINNERS",
                      "FROZEN PACKAGE MEAT"),
    "FROZEN SEAFOOD" = c("FROZEN SEAFOOD",
                         "SEAFOOD - FROZEN"),
    "FROZEN BREAKFAST" = c("FROZEN BREAKFAST",
                           "FROZEN BREAKFAST FOODS"),
    "FROZEN BREAD" = c("FROZEN BREAD",
                       "FROZEN BREAD/DOUGH"),
    "FROZEN VEGETABLES" = c("FROZEN VEGETABLES",
                            "FROZEN VEGETABLE/VEG DSH"),
    "FROZEN MEAT ALTERNATIVES" = c("FROZEN MEAT ALTERNATIVES",
                                   "FROZEN MEAT (VEGETARIAN)"),
    "FROZEN FRUIT" = c("FROZEN FRUIT",
                       "FROZEN FRUITS")
  ))

shrimp_products <- frozen_products %>%
  filter(str_detect(product_type, "SHRIMP|SHLFSH"))

frozen_shrimp_transactions <- shrimp_products %>%
  inner_join(transactions, by="product_id")

Regork: Frozen Sales Analysis

Introduction

Problem Introduction

Frozen seafood represents the most expensive purchases in the frozen food category. Despite average product costs that line up with other frozen categories, the higher end of several frozen seafood product types represent significant outliers.

Shrimp and shellfish sales represent some of the largest costs to, however, they make up a very small portion of the overall frozen food sales.

In addition, there is a discrepancy between the brand offerings compared to sales. Despite National providing a wider range of frozen shellfish offerings, Private makes up significantly larger portion of sales.

Problem Impact

Shrimp and shellfish are high cost, low shelf-life products. Currently there are six different items labeled as frozen shrimp or shellfish. While there are some slight difference between the products, it does not justify the logistics, overhead or shelf space dedicated to such product holdings.

Methodology

Packages and Libraries used

CompleteJourney
Dataset being analyzed
Tidyverse
Data wrangling tools, visualization tools, and assorted functions

Data Tidying

  • Combined some frozen product categories
  • Created consistency in category langauge (EG “FRZN” and “FROZEN” combined)

Problem Analysis

Frozen Food Sales

Frozen Product Costs

transactions %>%
  inner_join(frozen_products, by="product_id") %>%
  mutate(product_cost = (sales_value/quantity)) %>%
  group_by(brand,product_type) %>%
  summarize(max_product_cost = max(product_cost),
            avg_product_cost = mean(product_cost, na.rm = TRUE)) %>%
  arrange(desc(max_product_cost)) %>%
  head(n=10) %>% 
  knitr::kable(digits=2, col.names = c("Brand", "Product", "Max Cost", "Avg Cost"))
Brand Product Max Cost Avg Cost
National SEAFOOD-FRE-LIVE SHLFSH-OTHER 27.01 5.34
National SEAFOOD-FRZ-IQF RAW SHRIMP RW 22.42 7.52
Private SEAFOOD-FRZ-IQF COOKED SHRIMP 22.10 7.50
National SEAFOOD-FRZ-MISC 14.99 5.59
Private FRZN BURGERS/BBQ/MEATBALL 14.79 5.36
National PORK-FULLY COOKED 11.99 5.40
National OTHER - FULLY COOKED 10.99 9.77
National SEAFOOD-FRZ-RAW FILLETS 10.99 5.38
Private SEAFOOD-FRZ-RAW SHLFSH-OTHER 10.99 5.35
National BREADED - CHICKEN 10.69 9.78

Frozen Product Sales

transactions %>%
  inner_join(frozen_products, by="product_id") %>%
  mutate(is_shrimpper = str_detect(product_type, "SHRIMP|SHLFSH"),
         month = month(transaction_timestamp, label=TRUE, abbr=TRUE)) %>%
  # group_by(month, is_shrimpper) %>%
  ggplot(aes(x=month, y=sales_value, fill=is_shrimpper)) +
    geom_bar(stat="identity") +
    scale_y_continuous(labels = scales::dollar) +
    labs(
      title="Shrimp Sales as Portion of Frozen Sales",
      x="Month",
      y="Sales Value"
    )

Current Shellfish Offerings

frozen_shrimp_transactions %>%
  count(product_type) %>%
  mutate(percent_transactions = n/sum(n)) %>%
  ggplot(aes(percent_transactions, reorder(product_type, percent_transactions))) +
    geom_point() +
    geom_segment(
      aes(x=0, xend=percent_transactions, y=product_type, yend=product_type),
      size = 0.15
    ) +
    labs(
      title="Shellfish as % of Transactions",
      x="Percent",
      y="Product Type"
    )

Demographics

frozen_demographics <- frozen_shrimp_transactions %>%
  inner_join(demographics, by="household_id")

By Home Ownership

frozen_demographics %>%
  group_by(brand,home_ownership) %>%
  summarize(total_sales = sum(sales_value)) %>%
  ggplot(aes(x = home_ownership, y = total_sales, fill=brand)) +
    geom_bar(stat = "identity") +
    labs(
      title="Sales by Home Ownership",
      x="Ownership Type",
      y="Transactions"
    )

By Income

frozen_demographics %>%
  group_by(brand,income) %>%
  summarize(total_sales = sum(sales_value)) %>%
  ggplot(aes(x = income, y = total_sales, fill=brand)) +
    geom_bar(stat = "identity") +
    labs(
      title="Sales by Income Level",
      x="Income Level",
      y="Transactions"
    )

Brand Loyalty

brand_loyalty <- frozen_demographics %>%
  group_by(household_id) %>%
  summarise(unique_brands = n_distinct(brand)) %>%
  mutate(loyalty_category = 
           ifelse(unique_brands == 1, "Single Brand", "Multiple Brands"))

brand_loyalty %>%
  group_by(loyalty_category) %>%
  summarise(count = n()) %>%
  ggplot(aes(x=loyalty_category,
             y = count,
             fill = loyalty_category)) +
    geom_bar(stat = "identity") +
    labs(
      title="Brand Purchases by Household",
      x="Brand Purchase Habbits",
      y="Housholds"
    )

Promotions

Discounts Summary

shrimp_products %>%
  inner_join(transactions, by="product_id") %>%
  filter(coupon_disc > 0) %>%
  mutate(discount_percent = coupon_disc/sales_value) %>%
  ggplot(aes(discount_percent)) +
    geom_boxplot() +
    scale_x_continuous(labels = scales::percent) +
    labs(
      title="Coupon Discounts for Frozen Shellfish",
      x="Discount Percent"
    )

Discounts Redeemed

shrimp_products %>%
  inner_join(transactions, by="product_id") %>%
  filter(coupon_disc > 0) %>%
  mutate(discount_percent = coupon_disc/sales_value) %>%
  ggplot(aes(x=discount_percent, y=sales_value)) +
    geom_point() +
    scale_x_continuous(labels = scales::percent) +
    labs(
      title="Corrolation of Discount % to Sales Value",
      x="Discount Percent",
      y="Sales Value"
    )

Proposed Solution

Option A (Stabilize Discount Rates)

Based on findings in the Frozen Product Costs and Frozen Product Sales figures, frozen shellfish represents a very high cost product, but does not represent a large portion of frozen sales. Lowering the costs of the higher end products such as the FRZ-IQF shrimp offerings might help boost sales and increase turnover rate.

The data suggests that this might not be an easy price target to hit. The Discounts Summary graph shows that discounts on frozen shellfish are inconsistent. The mean discount hovers around 20% with massive jumps. The Discounts Redeemed figure shows no correlation between discount percent and transaction total sales value. This would suggest that discounts are ineffective for the frozen shellfish category, but this could also be a result of the inconsistent nature of discount rates. Customers are hesitant to use a regular 20% discount when they have historically seen 50%+.

Suggestion A would be to tighten the discounts surrounding frozen shellfish. The 20% mark has proven to be ineffective, but there is limited data between the 25% - 50% range. Regular and reliable discounts hovering around 30% are recommended as a starting point for reevaluation.

Option B (Reduce Options)

Of the six current shellfish options, three account for nearly 90% of sales (FRZ-IQF RAW SHRIMP, FRZ-BRD SHRIMP, and FRZ IQF COOKED SHRIMP). Of those, the FRZ IQF RAW SHRIMP commands nearly 50% of all frozen shellfish sales. Culling lower performing options could allow the core products to perform better, and would help turnover rate. Shrimp outperforms other shellfish, so the suggestion would be to focus on the top 3 selling types. Namely:

  • SEAFOOD-FRZ-IQF RAW SHRIMP
  • SEAFOOD-FRZ-BRD SHRIMP
  • SEAFOOD-FRZ-IQF COOKED SHRIMP

Option C (Reduce Brands)

There is a gap in the performance of the National and Private brands in the frozen shellfish segment. As shown in the Home Ownership and Income Demographics charts, the Private brand out performs the national brand rather heavily. The Brand Loyalty chart shows that households have a strong brand loyalty, with very few making purchases from multiple brands.

Removing the National brand frozen shrimp offerings would allow resources and display space to be refocused into the products offered by Private. Increasing sales of the products that are already preferred by customers.

Executive Summary

Problem statement

Frozen shellfish has redundant offerings, with inconsistent pricing and promotions that hamper sales.

Data and Methodology

  • Utilizing CompleteJourney dataset
  • Focused on product categories with the word “FROZEN”
  • Further narrowed focus to product item types with the words “SHRIMP” or “SHLFSH”

Insigts and Analysis

  • Frozen shellfish has unusually high price/product outliers within it’s product type
  • Low sales do not justify the product diversity offered
  • Discounts as currently structured are ineffective

Consumer implications

Of the three options proposed. Option A is the only one that attempts to increase sales by attracting customers. Options B and C instead focus on reducing investment into a loss product. Customers will interpret these options as a reduction of offerings.

Limitations and Improvements

  • Limited promotional data within key range
  • Deeper dive into how promotions impact demographics groupings
  • Comparisons of success rates of other “niche” products
    • Is it valuble to stock low-selling items in order to maintain image of broad availability, or is Regork focusing on essentials?