Introduction

Potential Sales Growth

The business problem I am trying to solve is how can we increase our sales in the Cosmetic Department by doing a promotion? The Cosmetic department falls right in the middle of total department sales, so this gives us the option to experiment with a solid clientele base without a too big of a risk being taken if the promotions don’t work.

How a Solution Was Reached

By filtering the products, transactions, and demographics data within the CompleteJourney data set, we are able to see the different types of products that are bought with cosmetic items, allowing us to run a potential coupon sale. We then filter to look for the cosmetic sales, along for specific demographic sales for each month to see when in the year we should run this promotion, and as well for specific cosmetic items that may be relevant to advertise towards.

Proposal

Due to the nature of the sales positioning of the cosmetic items, running promotions in this group could give us potential insight as to whether or not this is something we want to look towards for other potential departments as well. I am proposing that we run a two different coupon sales, on targeting the 19-24 age group and another targeting the whole age demographic.

These coupons will contain a sale on another item that is frequently bought with cosmetic items, which will be touched on below. The promotion for the 19-24 year old group should be ran in March, with the one targeting the market as a whole to be done in December. Visual images and text referencing specific cosmetic categories should be heavily targeted towards the Makeup and Treatment items.

Packages Required

CompleteJourney: This is the data set that we will be using.
Dplyr: Package that allows for easier filtering.
GGplot2: Provides graphs and other syntax related to them.
Stringr: Allows us to detect specific character strings while filtering.
Lubridate: Helps with transforming dates within our data.
GGpubr: Gives more graph options.
DT: Provides easy-to-read and see data tables.

suppressWarnings(suppressMessages(library(completejourney)))
suppressWarnings(suppressMessages(library(dplyr)))
suppressWarnings(suppressMessages(library(ggplot2)))
suppressWarnings(suppressMessages(library(stringr)))
suppressWarnings(suppressMessages(library(lubridate)))
suppressWarnings(suppressMessages(library(ggpubr)))
suppressWarnings(suppressMessages(library(DT)))

#load in the whole transactions data set
transactions <- readRDS("C:/Users/dknmw/Downloads/transactions (3).rds")

Department Sales

As previously mentioned, the Cosmetic Department total sales falls near the middle of our total sales. More specifically, it ranks at 13th out of 28 departments, giving us the ability to not take too much of a risk but still see real results.

table1 = products %>% inner_join(transactions, by = "product_id") %>% 
  inner_join(demographics, by = "household_id") %>% group_by(department) %>% 
  mutate(total_sales = sum(sales_value)) %>% tally(total_sales) %>% 
  arrange(desc(n)) %>% mutate(Value_in_Dollars = round(n, 0)) %>% select(department, Value_in_Dollars)

datatable(
  head(table1, 28),
  options = list(pageLength = 28, scrollY = "175px", dom = 't', container = list(height = '175px'))
)

Filtering by Age

The first thing I am going to do is filter our data so that we can see what proportion of our sales is done by each age group.

products %>% filter(str_detect(department, "COSMETICS")) %>% 
  inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% filter(str_detect(department, "COSMETICS")) %>% group_by(age) %>% summarize(total_count = sum(sales_value)) %>% 
  arrange(desc(total_count)) %>% ggplot(aes(x = "", y = total_count, fill = age)) +
  geom_col(color = "black") + theme_void() +
  coord_polar(theta = "y") + ggtitle("Proportion of Cosmetic Sales by Age Group")

We see that the age group of 35-54 dominate the sales for Cosmetics, with surprisingly, the 19-24 group lacking sales. This doesn’t make much sense with the presence of social media, and with how people in college have a very active role with makeup, along with other factors.

19-24 year olds only have 4.4% of sales for cosmetics

However, this isn’t as unreasonable as it sounds, due to the percentage of ages in the total data set.

sum_of_sales = products %>% inner_join(transactions, by = "product_id") %>% inner_join(demographics, by = "household_id") %>% group_by(age) %>% tally() 
ggplot(sum_of_sales,(aes(n,age))) + geom_col()+ ggtitle("Sales vs Age Group") + ylab("Age") + xlab("Sales in $")

sum_of_sales$n[1]/sum(sum_of_sales$n)

## [1] 0.0530933

This data shows the total sales by age group, for all departments.

The 19-24 age group only takes up 5.3% of our total sales.

While 5.3% compared to 4.4% that drastic of a difference, it is still lower than the percentage for all departments. This could be as a result of insufficient marketing and/or promotions, which is why I am proposing that we run a sale geared towards this age group.

Evaluating Ways to Expand our Reach to 19-24 year olds

One way to expand our market share in cosmetics for this specific demographic is to look at frequently bought items by this age group, and providing coupons towards these items when bought with Cosmetics.

Due to the fact that there was such low purchases of other items with cosmetics within the 19-24 age group, it makes more sense to look at the total department market and provide coupons with these items instead of what is frequently bought with cosmetic items. This make sense considering how low the percentage of Cosmetic transactions are from this demographic to begin with. After all, we are trying to expand our market share instead of catering to only the 19-24 age group who are already buying from us.

table2 = products %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% filter(age == "19-24") %>% 
  group_by(product_type) %>% count(product_category) %>% arrange(desc(n)) %>% head(10)

datatable(head(table2, 10), options = list(pageLength = 10, scrollY = "175px", dom = 't', container = list(height = '175px')))

However, we need to make sure that this doesn’t hinder at total sales, due to crossovers between this age group and the total demographic market. Lets look at the total market product sales.

Total Market Product Sales

table3 = products %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% 
  group_by(product_type) %>% count(product_category) %>% arrange(desc(n)) %>% head(15) 

datatable(head(table3, 15), options = list(pageLength = 15,scrollY = "175px", dom = 't', container = list(height = '175px')))

While there are lots of similarities, there are some items that we could integrate as coupons in order to increase awareness and sales to cosmetics by the 19-24 year old category such as KIDS CEREAL and RTS SOUP: CHUNKY/HOMESTYLE ET.

Alternative/Comparative Coupon

While it is important to market to new segments to try and gain new customers, we also need to make sure that we are retaining the ones we have currently. We can do this by a similar process, but instead of looking at sales from certain age groups, we can see what is being purchased with cosmetic items by all demographics to provide coupons that give incentive for our current clientele to buy with us, and buy more.

This also gives us the opportunity to compare the two different coupons against each other, which provides insight towards which of these we should continue to do in the future for potential other departments as well.

basketsall = products %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% filter(str_detect(department, "COSMETICS")) %>% 
  distinct(basket_id)

table4 = products %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% semi_join(basketsall, by= "basket_id") %>% 
  select(basket_id, department, product_type) %>%
  filter(!str_detect(department, "COSMETICS")) %>% 
  count(product_type) %>% arrange(desc(n)) %>% head(15)

datatable(head(table4, 15), options = list(pageLength = 15,scrollY = "175px", dom = 't', container = list(height = '175px')))

Comparing this with the total market product sales data like we did before will give us good insight onto what coupons to run, because it is important to make sure that these are targeting the cosmetic department and not the clientele base as a whole. Some of these items include: SHAMPOO and FRZN BAGGED VEGETABLES - PLAIN.

When Should these Promotions be Ran?

Another question that is critical to understand is when should this promotion be done? As we look to expand our market, it is important to see when customers like to shop at our store so we can try and capitalize on reaching the most people.

Lets look at the sales for each month

sales = products %>% filter(str_detect(department, "COSMETICS")) %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% 
  mutate(month = month(transaction_timestamp, label = TRUE)) %>% group_by(month) %>% 
  mutate(total_sales= sum(sales_value)) %>% tally(total_sales) %>% 
  select(n)

integer_sales = as.numeric(sales$n) 
baseline_sales = mean(integer_sales)

products %>% filter(str_detect(department, "COSMETICS")) %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% 
  mutate(month = month(transaction_timestamp, label = TRUE)) %>% group_by(month) %>%
  mutate(total_sales= sum(sales_value)) %>% tally(total_sales) %>% mutate(rel_sales_months = n-baseline_sales) %>%
  ggdotchart(x ="month",y = "rel_sales_months", color ="#FC4E07",
             sorting = "descending", add = "segments", add.params = list(color = "#00AFBB", size = 2), 
             dot.size = 6,
             font.label = list(color = "white", size = 9, 
                               vjust = 0.5), ggtheme = theme_pubr(),
             ) +
  geom_hline(yintercept = 0, linetype = 2, color = "lightgray") + ggtitle(
    "Monthly Cosmetic Sales Compared to the Average"
  ) + ylab('Sales Relative to Avg') + xlab('Month') + theme(axis.text.x = element_text(angle = 45, hjust = 0.5, size = 10))

This chart takes the average from the total sales for the year and compares it relative to each month. Months that have more sales than the mean appear to have lines that move upward while those months that are less than the mean point downwards.

This shows that December and January have the two highest sales for Cosmetics as a whole, with December having by far the most. This makes sense due to Christmas, along with a common theme around New Years being self improvement, which is typically enhanced upon by using cosmetic items. This gives us insight to when we should market our promotions for our total market coupon.

Sales Comparison for the 19-24 Age Group

Due to the fact that we are trying to expand our market share for the 19-24 age group, meaning they don’t already shop in the department, I decided to make a graph with the same idea behind it, but instead for all departments instead of just cosmetics.

sales_nineteen = products %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% 
  mutate(month = month(transaction_timestamp, label = TRUE)) %>% group_by(month) %>% 
  filter(age == "19-24") %>% 
  mutate(total_sales= sum(sales_value)) %>% tally(total_sales) %>% 
  select(n)

integer = as.numeric(sales_nineteen$n) 
baseline = mean(integer)
products %>% inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% 
  mutate(month = month(transaction_timestamp, label = TRUE)) %>% group_by(month) %>% filter(age == "19-24") %>%
  mutate(total_sales= sum(sales_value)) %>% tally(total_sales) %>% mutate(rel_sales = n-baseline) %>%
  ggdotchart(x ="month",y = "rel_sales", color ="#FC4E07",
             sorting = "descending", add = "segments", add.params = list(color = "#00AFBB", size = 2), 
             dot.size = 6,
             font.label = list(color = "white", size = 9, 
                               vjust = 0.5), ggtheme = theme_pubr(),
             ) +
  geom_hline(yintercept = 0, linetype = 2, color = "lightgray") + ggtitle(
    "Monthly Total Sales Compared to the Average for Ages 19-24"
  ) + ylab('Sales Relative to Avg') + xlab('Month') + theme(axis.text.x = element_text(angle = 45, hjust = 0.5, size = 10))

Interestingly enough, March has by far the most sales for the 19-24 year old age group. This means if we were to do multiple promotions, one targeting the whole cosmetics customers base should be done during the months of December and potentially into January, while our 19-24 year old promotion would be done during the month of March.

What Specific Products do we Promote Heavily?

While trying to run a coupon sale, it is important to make sure that the visuals on the coupon and the advertising catch the eye of the customer, otherwise they will only glance at it and not read more into it. We can do this by seeing if there is a specific product that has the most sales and putting that product on the primary visual front.

products %>% filter(str_detect(department, "COSMETICS")) %>% 
  inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>%
  mutate(month = month(transaction_timestamp, label = TRUE)) %>% 
  group_by(product_category) %>% mutate(total_sales= sum(sales_value)) %>% 
  mutate(product_category = recode(product_category, "MAKEUP AND TREATMENT" = "M&T", "FRAGRANCES" = "FRAG", 
                                   "COSMETIC ACCESSORIES" = "COS_ACC", "COUPON/MISC ITEMS" = "MISC")) %>% 
  ggplot(aes(product_category, total_sales, fill = product_category)) + geom_col() + facet_wrap(~month) + theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 6)) +  scale_fill_manual(values = c("M&T" = "navy", "FRAG" = "green", "COS_ACC" = "orange", "BATH" = "red", "MISC"="brown")) + theme(legend.text = element_text(size = 8),legend.title = element_text(size = 10)) + ggtitle("Individual Product Sales per Month") + ylab("Sales in Dollars") + xlab("Product Category")

It is clear that the Makeup and Treatment category is the most popular within the whole market, regardless of month as well, so if we want to really reach the audience we can home in on marketing to that category through visuals and things such as text within these promotions.

Lets check this for the 19-24 year old category, as well.

products %>% filter(str_detect(department, "COSMETICS")) %>% 
  inner_join(transactions, by= "product_id") %>% 
  inner_join(demographics, by= "household_id") %>% filter(age == "19-24") %>%
  mutate(month = month(transaction_timestamp, label = TRUE)) %>% 
  group_by(product_category) %>% mutate(total_sales= sum(sales_value)) %>% 
  mutate(product_category = recode(product_category, "MAKEUP AND TREATMENT" = "M&T", "FRAGRANCES" = "FRAG", 
                                   "COSMETIC ACCESSORIES" = "COS_ACC", "COUPON/MISC ITEMS" = "MISC")) %>% 
  ggplot(aes(product_category, total_sales, fill = product_category)) + geom_col() + facet_wrap(~month) + theme(axis.text.x = element_text(angle = 45, hjust = 1, size = 6)) +  scale_fill_manual(values = c("M&T" = "navy", "FRAG" = "green", "COS_ACC" = "orange", "BATH" = "red", "MISC"="brown")) + theme(legend.text = element_text(size = 8),legend.title = element_text(size = 10)) + ggtitle("Individual Product Sales per Month for Ages 19-24") + ylab("Sales in Dollars") + xlab("Product Category")

This remains true, so for both of our promotions we can have an emphasis on marketing this coupon to those who like Makeup and Treatment as they have the highest total sales.

Summary

Addressing the Business Problem

Throughout this analysis, I assessed potential ways that we could increase our overall sales to the Cosmetic Department.

How Analysis was Done

By joining together the transactions, demographics, and products data in the CompleteJourney set, I was able to filter through the data to find insights to what potential promotions we should run. I first started by identifying a target demographic we could go after, and then found items that are popular in this demographic that we could do a coupon for, by combining said items with the purchase of a cosmetic item. I then did the same thing, but this time with the whole demographic market and only finding frequently bought items with a cosmetic item so that we could have two coupons to work with. Next, I filtered sales by month to figure out during which months should these sales be ran, and finally I identified which specific cosmetic products provide the most sales so we can focus advertising efforts on these items to bring in the most customers.

Interesting Insights

I found it interesting how much the Makeup and Treatment category dominated total sales. It is to the point where you can barely see sales for some of the other product categories within the department. This makes me wonder in the future if the Makeup and Treatment category will one day become its own department, considering how far and away from the other categories it is in sales.

Reccomendation

My strategic recommendation is to provide coupons to people who purchase a cosmetic item an item such as KIDS CEREAL or RTS SOUP: CHUNKY/HOMESTYLE ET, and another one with the options of SHAMPOO or FRZN BAGGED VEGETABLES - PLAIN. This provides customers who are in the 19-24 demographic to have an incentive to join the Cosmetic market, and also gives an incentive to the current Cosmetic market to continue to shop with us, with a coupon tailored to them. The first listed coupon should be campaigned in March, as that is when the 19-24 age group shops with us the most, and the other coupon should be campaigned in December into January, since that is when our current cosmetic market shops with us the most. Visuals and advertisements should be heavily marketed for the Makeup and Treatment category, however all categories should be shown. It is important to note the specifics of the items within the coupons, because they are highly sold within the market we are trying to capitalize on, but also don’t get the overall consumer market too involved.

Limitations of Analysis

The data I worked with was only for a one year span, so it is not certain if all the trends found are consistent throughout the years, or if it was just for this specific time frame. Filtering through more specifc traits such as household size and income could be done to further identify and find more accurate trends between cosmetic purchasers and their frequent items bought. This is why I included two different coupons to use, because it is uncertain whether or not the promotions will be effective or not.

Midterm Project

Matthew Wiley

2024-02-25