Introduction

Business Problem

The problem I am trying to solve is where can we invest money to increase revenue and market share from the Groceries Department and consequentially, the overall business.

How I Approached The Problem

To produce the desired results, I decided to join the tables that could have potential useful information, and filter the grocery department from that join. This gave me a base to work with. After that, I decided to take a closer look on the impact of product category within the Grocery department, which opened a lack of paths that I could look to. From there, I created visualizations to help me and the executive who will be reading this report to understand how sales changed different demographic categories and across the year.

How Is The Report Useful

This report explores the impact of demographics and seasonal trends of the department that generated the most revenue. It will allow the reader to visualize and understand the data trends, which will allow the higher-ups to have better decisions on the allocation of resources to increase revenue.

Required Packages

knitr

Is an R package for dynamic report generation, enabling users to integrate R code with text and produce reproducible documents with embedded analyses and visualizations.

#Load Packages
library(tidyverse)
library(completejourney)
library(lubridate)
library(knitr)
#Load The Following Data Sets
transactions <- get_transactions()
demographics <- demographics
products <- products
#Join The Data Sets
prod_demo_tran <- transactions %>%
  inner_join(products, by = "product_id") %>%
  inner_join(demographics, by = "household_id")
dim(prod_demo_tran)
## [1] 825622     24

Understanding The Demographics

Lets take a quick look on the demographics of this data set, considering that for this analysis, the amount of people you have to feed is directly related by how much you spend on groceries:

#Plot the Age Group by Household Size
demographics %>%
  ggplot(aes(x = age)) +
  geom_bar() +
  facet_wrap( ~ household_size) +
  ggtitle("Household Size per Age Group") +
  xlab("Age Group") +
  ylab("Count") +
  theme(axis.text.x = element_text(angle = 45, vjust = 0.8))

By just looking at the graph, we can see that the amount of households with 1 and 2 people is considerably greater than the others, and that people between 45-54 were the majority of household owners in this data-set. We need this information to not have any biases with the data in the future.

Income by Department

Which departments produce the most sales?

#Plot the Top 10 Departments per Total Sales
prod_demo_tran %>%
  group_by(department) %>%
  summarize(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  top_n(10, total_sales) %>%
  ggplot(aes(x = department, y = total_sales)) + 
  geom_col() +
  scale_y_continuous("Total Sales", labels = scales::dollar) +
  scale_x_discrete("Department") +
  ggtitle("Top 10 Departments by Total Sales") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

## [1] 49.92163

The Grocery Department produced more than 1.25 million on sales, which represents almost 50% of all sales. Assuming that we want to increase more our market share in the sales from our top sales department, the Grocery Department, we need to dig in the the data to understand/find what areas could receive more attention to increase sales. With that in mind, I will be focusing our data set only in the Grocery department.

#Focus only in the Grocery Department
prod_demo_tran_GROCERY <- prod_demo_tran %>%
  filter(department == "GROCERY")

Product Categories With Sales Higher Than $25000

To get only the most impactful data, I decided to select product categories that sum up to at least $25000 in sales.

#Plot the Product Types
prod_demo_tran_GROCERY %>%
  group_by(product_category) %>%
  summarize(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  filter(total_sales >= 25000) %>%
  #top_n(10, total_sales) %>%
  ggplot(aes(x = product_category, y = total_sales)) + 
  geom_col() +
  scale_y_continuous("Total Sales", labels = scales::dollar) +
  scale_x_discrete("Product Category") +  
  ggtitle("Product Categories With Sales Higher Than $25000") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1))

With this filter in place, we can understand that these 11 product categories is what actually drive most of our revenue. Knowing that, now we can look on possibilities to increase the market share within these leading products categories, driving our clients satisfaction up.

The product categories in order of revenue are as follows:

  1. SOFT DRINKS
  2. FLUID MILK PRODUCTS
  3. CHEESE
  4. FRZN MEAT/MEAT DINNERS
  5. BAG SNACKS
  6. BAKED BREAD/BUNS/ROLLS
  7. BEERS/ALES
  8. FROZEN PIZZA
  9. COLD CEREAL
  10. SOUP
  11. ICE CREAM/MILK/SHERBTS
#Focus only in the 11 Categories above
prod_demo_tran_category <- prod_demo_tran_GROCERY %>%
  filter(product_category %in% c('BAG SNACKS','BAKED BREAD/BUNS/ROLLS','BEERS/ALES','CHEESE','COLD CEREAL','FLUID MILK PRODUCTS', 'FROZEN PIZZA', 'FRZN MEAT/MEAT DINNERS', 'ICE CREAM/MILK/SHERBTS', 'SOFT DRINKS', 'SOUP'))

Total Sales By Week

#Plot the Sales Over Time
prod_demo_tran_category %>%
  group_by(week, age) %>%
  summarize(total_sales = sum(sales_value, na.rm = TRUE)) %>%
  ggplot(aes(x = week, y = total_sales, fill = age)) + 
  geom_area() +
  scale_y_continuous("Total Sales", labels = scales::dollar) +
  scale_x_continuous(breaks = seq(min(prod_demo_tran_category$week), max(prod_demo_tran_category$week), by = 4)) +  
  ggtitle("Total Sales Trends Over Time for Each Age Group") +
  labs(fill = "Age")

This graph shows the trend of sales within these 11 product categories that we filtered previously. The graph shows all weeks of the 2017 year, and we can get some useful information with this area chart, including that:

Meaning that there is margin for a further analysis more focused in the trend within holidays and overall weeks in the year. Lets take a different approach now.

Average Sales Per Age Group

#Calculate the Total Sales per age group divided by Unique households
result <- prod_demo_tran_category %>%
  group_by(age) %>%
  summarize(total_sales = sum(sales_value, na.rm = TRUE),
            unique_households = n_distinct(household_id)) %>%
  mutate(avg_sales_per_household = total_sales / unique_households)
kable(result, format = "markdown", col.names = c("Age", "Total Sales", "Unique Households", "Avg Sales per Household"))
Age Total Sales Unique Households Avg Sales per Household
19-24 32799.23 46 713.0267
25-34 98950.51 142 696.8346
35-44 149937.31 194 772.8727
45-54 201009.68 288 697.9503
55-64 32371.42 59 548.6681
65+ 33476.75 72 464.9549

In this data frame, I created a calculated column that allows us to clearly quantify how each age group impacts sales from the leading product categories in the Grocery Department. In the “Avg Sales per Household” column, we can identify that even though the age group 19-24 is the one with the least Households, it has the second highest average expenditure per household, meaning that they buy more than the average. The opposite is also visible when looking to 65+ age group and their small average expenditure with the top 11 product categories in the Grocery Department.

Combo Chart With Average Sales and Total Sales

Lets place the data frame above in a combo chart to facilitate the visualization of the trend:

#Plot the Previous Data
result %>%
ggplot(aes(x = age)) +
  geom_bar(aes(y = total_sales, fill = "Total Sales"), stat = "identity", alpha = 0.7) +
  geom_point(aes(y = avg_sales_per_household * 100, color = "Average Sales"), size = 3) +
  scale_y_continuous(
    name = "Total Sales",
    sec.axis = sec_axis(~ . / 110, name = "Average Sales per Household", breaks = seq(0, 2000, by = 400))
  ) +
  scale_color_manual(values = c("Average Sales" = "blue")) +
  scale_fill_manual(values = c("Total Sales" = "orange")) +
  labs(title = "Total Sales and Average Sales per Household for Each Age Group",
       x = "Age Group") +
  theme_minimal() +
  guides(color = guide_legend(title = NULL), fill = guide_legend(title = NULL))

Again, we can see the average sales per household being the lowest at 65+ age group and the highest at the 35-44 age group.

Summary

With this analysis, I intended to understand where the company can invest money to increase revenue and market share from the Groceries Department and consequentially, the overall business.

How The Business Problem Was Answered

To answer the problem, I first presented the demographics that participated in this data collection to help us understand the data. Following that, I decided to look into the revenue by department, which showed that the Grocery Department is the one that provided the most revenue. Then I filtered the product categories from the Grocery department to understand what areas were most impactful to the overall business, where I found 11 that generated more than $25000 in 2017. With that in mind, I created an area chart to show how the sales from those 11 groups trended over the weeks and which portion of that weekly revenue came from each age group. That graph gave me some insight towards the worst performing age groups, so I created a calculation to see how much each age group was spending proportionally to the total of households, and not the total of transactions. To assist in the visualization of such data, I decided to put all that in a combo chart. And with all that, I was able to provide the recommendation below.

My Recommendation

I recommend increasing the investment in propaganda/advertisements for the age groups 55-64 and 65+. The data shows that even though they are the minority of households, there is a huge gap in comparative expenditure to the other age groups, suggesting that they are buying their essential groceries from a different retailer. These is a large portion of clients that are spending with your secondary business lines, and turning them to also spend on the grocery side is just a case of incentive.

Limitations

The analysis has some limitations, which derives mostly of the fact that I don’t have access to the information of the area or state at which the data came from, limiting links with real world scenarios and forcing lots of assumptions to be made. Another major limitation is that the data provided is from 2017. Nowadays, with the COVID Pandemic drastically changing consumer behavior and expenditure mode, my analysis would not be up to date.