Introduction

On average, higher private label sales lead to greater pre-tax profits for grocery retailers. This widely accepted industry principle is supported by “Retained Value per Customer Spend”, which shows that the highest margins are primarily driven by private-label sales. In addition to the financial benefits, successful private-labels offer intangible value. For example, when marketed under the same banner, the quality and reputation of private-label products enhance the overall reputation of the retailer.

The objective of this analysis is to identify potential drivers of consumer preference for private-label products and assess the effectiveness of current strategies in fostering sustained preference. Key questions include:

  1. Identify the top areas for marketing investment to achieve this goal.
  2. Review historical coupon usage and its impact on sales.
  3. Analyze households that showed a significant change in spending due to coupon usage.
  4. Explore the product preferences of those households.

Packages Required

Package Purpose
completejourney Provides access to data sets characterizing household level transactions at a grocery store.
tidyverse Data import, tidying, manipulation, visualisation, and programming.
dplyr Make it easier to manipulate dataframes; Primarily utilized for joins, muatations and filtering.
ggplot2 The Grammar of Graphics.
stringr Simplify String manipulation; Primarily utilized to detect substrings.
RColorBrewer Encode graphics with colors in an aesthetic and inclusive way.
scales Format as Dollar.
zoo Calculate a three month moving average.
purrr Replace for loops with easy to understand function.
ggrepel Display plot labels with clarity and no overlap.

EDA

Private Brand Value

value_opportunity <- transactions %>%
  mutate(
    loyalty_price = (sales_value - (retail_disc + coupon_match_disc)) / quantity, 
    non_loyalty_price = (sales_value - (coupon_match_disc)) / quantity,
    value_per_USD_loyalty = sales_value/loyalty_price,
    value_per_USD_non = sales_value/non_loyalty_price
  )  %>%
  inner_join(
    select(products, product_id, brand),
    by = "product_id"
  )  %>%
  filter(value_per_USD_loyalty > 0, loyalty_price != 0, non_loyalty_price !=0)

breaks_values <- quantile(value_opportunity$value_per_USD_loyalty, probs = c(0.5, 0.75, 1), na.rm = TRUE)
ggplot(value_opportunity, aes(x = brand, y = value_per_USD_loyalty)) +
  geom_jitter(alpha = 0.1, width = 0.2) +
  ggtitle("Strip plot") +
  scale_y_log10(
     breaks = breaks_values,
     labels = scales::dollar_format()
   ) +
  labs(
    title = "Retained Value Per Customer Dollar Spent by Brand",
    subtitle = "Private brand offers additional opportunities to capture value.",
    x = "Brand",
    y = "Value per Dollar Spend (Log Scale)"
  ) +
  theme(
    axis.text.y = element_text(size = 8),
    plot.subtitle = element_text(face = "italic")
  )

By introducing randomization, we can see that there is a large concentration of high-margin sales above and beyond the maximum national brand value.

Top Private Brand Growth Opportunities

brand_sales <- products %>% 
  inner_join(transactions, by = "product_id") %>%
  group_by(product_category, brand) %>% 
  summarise(total_quantity = sum(quantity, na.rm = TRUE))

## Investigate product categories with greatest opportunity for Private Brand Growth,
## using quantity as indicator of consumer choice.
top_diff <- brand_sales %>%
  ## Include NA so that only products with confirmed alternative are compared.
  pivot_wider(names_from = brand, values_from = total_quantity)   %>% 
   mutate(difference = National - Private) %>%
   arrange(desc(difference)) %>%
   head(25) %>%
   pivot_longer(cols = Private:National, names_to = "brand", values_to = "total_quantity")

private <- top_diff %>%
  filter(str_detect(brand, "Private"))
national <- top_diff %>%
  filter(str_detect(brand, "National"))

diff_helper <- private %>%
  mutate(x_pos = total_quantity + (difference/2))

diff_helper$product_category <- factor(
  diff_helper$product_category, 
  levels = diff_helper$product_category[order(diff_helper$difference, decreasing = FALSE)]
  )

ggplot(top_diff) +
  geom_segment(data = private,
              aes(x = total_quantity, y = product_category,
                  yend = national$product_category, xend = national$total_quantity), 
              color = "#aeb6bf",
              size = 4.5, #Note that I sized the segment to fit the points
              alpha = .5) +
  geom_point(aes(x = total_quantity, y = product_category, color = brand), size = 4, show.legend = TRUE) +
  labs(
    title = "Difference between Private and National Brand Quantities",
    subtitle = "Scoped by Top 20 Private Label Opportunities",
    x = "Total Quantity of Purchases",
    y = "Product Category",
    color = "Brand"
  ) + 
  theme(
    plot.subtitle = element_text(face = "italic")
  ) +
  scale_color_brewer(palette = "Accent") + 
  geom_text(data = diff_helper,
            aes(label = diff_helper$difference, x = x_pos, y = product_category),
            color = "#5A2D81",
            size = 2)

A strong preference for national brands over direct private-label alternatives presents a valuable opportunity for Regork to increase its market share in these categories. This insight highlights current market dynamics, with soft drinks, meat dinners, and bagged snacks identified as key growth opportunities for Regork.

Private Exclusive Coupon Driver

coupon_by_brand <- 
  coupons %>%
  left_join(products, by = "product_id")  %>%
  group_by(coupon_upc, brand) %>%
  summarise(products = n_distinct(product_id))  %>%
  pivot_wider(names_from = brand, values_from = products)  %>%
  filter(!is.na(Private), is.na(National)) %>% ## Redeemable for Private brand only
  arrange(desc(Private)) 

brand_sales <- products %>% 
  inner_join(transactions, by = "product_id") %>%
  group_by(product_category, product_type, brand) %>% 
  summarise(total_quantity = sum(quantity, na.rm = TRUE)) %>%
  pivot_wider(names_from = brand, values_from = total_quantity) 

private_coupons <- coupon_by_brand %>% 
  semi_join(coupons, ., by = "coupon_upc") %>% ## filter coupon detail for Private-exclusive
  left_join(campaign_descriptions, by = "campaign_id") %>% ## Add active dates
  left_join(products, by = "product_id") ## Add product detail
  
## All transactions for a product with private exclusive coupon eligibility.
private_sales_detail <- private_coupons %>% 
  left_join(transactions, by = "product_id") %>% 
  mutate( 
    ## Flag whether the transactions were before or after the campaign.
    timebox = case_when(
      transaction_timestamp < start_date ~ "Before",
      transaction_timestamp >= start_date & transaction_timestamp <= end_date ~ "During",
      transaction_timestamp > end_date ~ "After"
    )
  ) 

## Note that interesting coupons was determined by first visually inspecting all coupons.
interesting_coupons <-  c("51111074130", "10000085380", "10000089091", "10000089259",
                          "51111073935", "10000089113", "51111079140", "51111070135", 
                          "51111010133", "10000089053", "10000089051", "10000089051",
                          "51111019450")

weekly_private_sales <- private_sales_detail %>% 
  group_by(week, product_category, coupon_upc, timebox)  %>% 
  summarise(sales = sum(sales_value))  %>%
  filter(coupon_upc %in% interesting_coupons) 

baseline_avg <- weekly_private_sales %>%
  filter(str_detect(timebox, "Before")) %>%
  group_by(coupon_upc) %>%
  summarize(mean = mean(sales))
weekly_private_sales %>%  
  ggplot(aes(x = week, y = sales, fill = timebox)) +
  geom_bar(stat = "identity") +
  facet_wrap( ~ coupon_upc, ncol = 3, scales = "free_y") + 
  labs(
    title = "Coupon Impact to Private Brand Sales",
    subtitle = "Private-Exclusive Coupons Case Studies",
    x = "Week of 2017",
    y = "Sales (USD)", 
    fill = "Campaign Period"
  ) + 
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    plot.subtitle = element_text(face = "italic")
  ) +
  scale_fill_brewer(palette = "Accent") + 
  geom_hline(
    # Add dotted line for average 'Before' sales
    data = baseline_avg, 
    aes(yintercept = mean), 
    linetype = "dotted", 
    color = "black"
  )  +
  scale_y_continuous(labels = dollar_format())

To simplify the interpretation of this analysis, I focused on coupons exclusively applicable to private-label products. Notably, the data set indicates that private-label coupons are only present when there is no national brand alternative (see Next Steps). By plotting the sales of these eligible products, we can evaluate the effectiveness of coupons in driving overall sales. Initially, I created visualizations for all private-exclusive coupons and then refined the selection based on notable trends, especially those aligned with the timing of the coupon campaigns. It is useful to know that even the most successful campaigns based on “During” spend, usually do not result in a sustained increase in sales “After” the campaign.

Relative Private Label Sales

## Household transactions for those that utilized an exclusive coupon.
private_coupon_households <- coupon_by_brand %>%
  semi_join(coupon_redemptions, ., by = "coupon_upc") %>%
  left_join(transactions, by = "household_id") %>%
  left_join(
    select(products, product_id, brand),
    by = "product_id"
  )  %>%
  filter(!is.na(brand))


## Households that utilize coupons and customers of both private and national label products.
brand_choices <- private_coupon_households %>%
  group_by(household_id, brand) %>%
  summarise(total_quantity = sum(quantity, na.rm = TRUE), .groups = "drop") %>%
  pivot_wider(names_from = brand, values_from = total_quantity)  %>% 
  filter(!is.na(Private), !is.na(National)) %>%
  ## Calculate households who are nearly as likely to choose between private and national.
  mutate(ratio = Private/National, ratio_ref = abs(1-ratio)) %>% 
  arrange(ratio_ref) %>% ## 138 households worth looking into
  head(12)



brand_sales <- private_coupon_households %>% 
  filter(household_id %in% brand_choices$household_id) %>%
  group_by(week, household_id, brand) %>%
  summarise(total_quantity = sum(quantity, na.rm = TRUE), .groups = "drop") 
  
private_sales_only <- brand_sales %>%
  filter(str_detect(brand, "Private")) %>%
  ungroup() %>%
  complete(week = seq(min(week), max(week), by = 1), household_id, fill = list(total_quantity = 0, brand = "Private")) %>%
  group_by(household_id) %>%  # Group by household to calculate rolling average within each household
  arrange(week) %>%
  mutate(rolling_avg = rollmean(total_quantity, k = 12, fill = NA, align = "right"))
ggplot() +  # Specify ggplot with no data
  geom_bar(data = brand_sales, aes(x = week, y = total_quantity, fill = brand), stat = "identity") +
  geom_line(data = private_sales_only, aes(x = week, y = rolling_avg), size = 1, color = "#5A2D81") + 
  facet_wrap(~ household_id, ncol = 3, scales = "free_y") +  
  labs(
    title = "Weekly Sales Quantity by Brand",
    subtitle = "Househould spending habits for those that utilized a private-excusive coupon",
    x = "Week of 2017",
    y = "Total Quantity of Sales",
    fill = "Brand",
    color = "Three Month Rolling Avg"
  ) + 
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    plot.subtitle = element_text(face = "italic")
  ) +
  scale_fill_brewer(palette = "Accent")

To understand our business question, we must also examine private brand as an alternative to national brands. The “Weekly Sales Quantity by Brand” chart highlights household spending habits for customers influenced by private-exclusive coupons. Households were selected based on a roughly equal propensity for both private-label and national brands, indicating they could be swayed by the right incentives. Household 2123 stands out, as their overall purchases increased, with a notable rise in private-label purchases over time.

Basket Analysis Case Study

## Count the number of baskets that the prdduct combination appear in together
product_combos <- transactions %>%
  filter(household_id %in% brand_choices$household_id) %>%
  select(basket_id, product_id) %>%
  mutate(in_basket = 1)  %>%
  pivot_wider(names_from = product_id, values_from = in_basket) %>%
  mutate_all(~replace_na(.,0), ) %>%
  select(-basket_id) %>%
  as.matrix() %>%
  crossprod()

## Create a vector that flags column products with an interesting value (>30)
c_scope <- map_lgl(asplit(product_combos, 2), ~ any(.x > 30))
## ...flags row products...
r_scope <- map_lgl(asplit(product_combos, 1), ~ any(.x > 30))

## Reduce the matrix to include only the products above threshold,
## either in rows or columns.
product_combos <- product_combos[,r_scope | c_scope] 
product_combos <- product_combos[r_scope | c_scope,] 
## Must pivot data back for plot

products_helper <- products %>%
  select(product_id, product_category)


product_combos_df <- as.data.frame(as.table(product_combos)) %>%
  filter(Var1 != Var2)  %>% ## Remove matrix diagonals - Always matched to self
  ## Pull in product category to reveal relationships 
  left_join(products_helper, by = c("Var1" = "product_id")) %>%
  rename(category1 = product_category) %>%
  left_join(products_helper, by = c("Var2" = "product_id")) %>%
  rename(category2 = product_category)


product_combos_df <- product_combos_df %>%
  mutate(
    ## Level product by product category to reveal trends
    Var1 = factor(Var1, levels = unique(products$product_id[order(products$product_category)])),
    Var2 = factor(Var2, levels = unique(products$product_id[order(products$product_category)])),
    ## Sort categories and create a combination string, so that order doesn't matter.
    category_combination = apply(cbind(category1, category2), 1, function(x) {
      paste(sort(x), collapse = " & ")
    })
  )

distinct_labels <- product_combos_df %>%
  filter(Freq >= 10, Freq < 40) %>%  # Filter for combinations with Freq > 50
  group_by(category_combination) %>%  # Group by category combination
  slice(1)

ggplot(product_combos_df, aes(x = Var1, y = Var2, size = Freq, color = category_combination)) +
  geom_point(alpha = 0.7) +  # Bubble color and transparency
  geom_label_repel(data = distinct_labels, 
          aes(label = str_wrap(category_combination, width = 15)), 
          vjust = 1.5, 
          size = 3, 
          color = "black") + 
  scale_size(range = c(1, 30), name = "Count Shared Baskets") +  # Size scale for bubbles
  # scale_color_brewer(palette = "Set3", guide = "none") +
  labs(title = "Product Combinations by Category",
       subtitle = "Scoped on Household and Product Category Insights",
       caption = "Product category combinations in <30 but >10 baskets were considered notable, 
        but novel, and labeled in the matrix.",
       x = "Product",
       y = "Product") +
  theme(
        axis.text.x = element_text(angle = 45, hjust = 1, size = 14),  # Increase axis text size
        axis.text.y = element_text(size = 14),  # Increase y-axis text size
        axis.title.x = element_text(size = 16),  # Increase x-axis label size
        axis.title.y = element_text(size = 16),  # Increase y-axis label size
        plot.title = element_text(size = 24, face = "bold"),  # Increase title size
        plot.subtitle = element_text(size = 16, face = "italic"),  # Increase subtitle size
        plot.caption = element_text(size = 12),  # Increase caption size
        legend.text = element_text(size = 12),  # Increase legend text size
        legend.title = element_text(size = 14) 
  ) + 
  scale_color_viridis_d(guide = FALSE)   # Increase space on x-axis

Using the previously identified “independent” households as a case study, I drilled down into their specific product choices to evaluate potential paired product insights. This visual calls out product category combinations that occur frequently enough to suggest potential sales significance, but hides the most common combinations, which are more likely to be obvious (eg deli meat and sandwich bread.) When cross referencing this visual with Private and National Label Quantities, we are able to identify avenues to driving increased market share in those areas. For example, a paired product promotion for private brand seafood and soft drinks could increase the likelihood that customers will choose private brand soft drinks.

Summary

Overview of Insights

Higher Margins for Private Labels: Private-label products provide a greater retained value per customer dollar, offering a significant margin advantage compared to national brands. The analysis shows that certain categories have untapped potential where private-label products could drive higher profitability. Growth Opportunities: Product categories like soft drinks, meat dinners, and bagged snacks exhibit a strong preference for national brands. These categories represent prime opportunities for Regork to capture more market share with private-label alternatives. Coupon Effectiveness: Private-label exclusive coupons show a clear impact during campaigns, but post-campaign sales often do not sustain the boost, indicating that the effects are short-lived. Household Analysis: Households that use private-label exclusive coupons show varying responses, but some, like household 2123, display an increased preference for private-label products over time. Product Pairing Insights: Certain product combinations (e.g., private brand seafood paired with soft drinks) offer promising potential for paired product promotions, which could increase the overall private-label share.

Reccomendations

Immediate Gains

My recommendation is that Regork launch campaign targeting households that have an equal propensity for national and private brand. The specific terms of the campaign should be a pairing of a private label products that consumers are likely to choose, like seafood, and a private label product that consumers are less likely to select, like soft drinks.

Long-Term Loyalty

Additionally, I recommend that Regork review the quality of private label products which experience a boost from coupon sales, but no sustained preference for private label. Consumers benefit from private-label products as they are often priced lower than national brands; Intuitively, if private brand products were similar or better quality than national brand, then customers would continue to choose private label after the initial reconciling of perceived and true quality.

Limitations and Next Steps

This analysis should be considered alongside year-over-year trends. Private label preference is highly sensitive to economic conditions. Our data is from 2017, a year marked by strong GDP growth and low unemployment. Regork should not assume that these findings would hold in different economic climates. Sales trends should be adjusted for seasonal factors that may distort the results. Seasonal influences could be confounding variables in the analysis.

While private label growth has advantages, Regork should also weigh the potential loss of national brand sales. High-profile national brands may drive more foot traffic, which could impact overall store performance.

Our data does not distinguish between in-store and online purchases. This distinction is critical, as research suggests the perceived quality gap between private labels and national brands is narrower in online shopping environments. This segmentation would offer more valuable insights for decision-making.