h1{border: 2px solid black;font-weight: 900;padding: 20px}
h2{color: #265073;}
h3{color: #265073;}
h4{color: #2D9596;}
th{border: 1px solid black;font-weight: bolder;font-size: 17px}
td{border: 1px solid black}
.r{border: 2px solid black;background-color: black;color: white}
pre{border: 3px solid black}

datasets_from: https://codebasics.io/challenge/codebasics-resume-project-challenge

Problem Statement

AtliQ Mart is a retail giant with over 50 supermarkets in the southern region of India. All their 50 stores ran a massive promotion during the Diwali 2023 and Sankranti 2024 (festive time in India) on their AtliQ branded products. Now the sales director wants to understand which promotions did well and which did not so that they can make informed decisions for their next promotional period.

Installing Packages

  install.packages("tidyverse")
  library(tidyverse)
  library(knitr)

uploading files

fact_events <- read_csv("datasets/fact_events.csv")
stores <- read_csv("datasets/dim_stores.csv")

Renaming Columns

fact_events <- fact_events %>% 
  rename("quantity_sold_before_promo" = "quantity_sold(before_promo)") %>% 
  rename("quantity_sold_after_promo"="quantity_sold(after_promo)")

Adding Columns which states revenue before and after promotions.

fact_events <- fact_events %>% 
  mutate(revenue_before_promo = (base_price * quantity_sold_before_promo))

aft_promo <- fact_events %>% 
  mutate(revenue_after_promo = ifelse(promo_type=="50% OFF" | promo_type=="BOGOF",
                                      (base_price*quantity_sold_after_promo)/2,
                                      ifelse(promo_type=="25% OFF",
                                             as.numeric(0.75*base_price*quantity_sold_after_promo),
                                             ifelse(promo_type == "33% OFF",
                                                    (0.67*base_price*quantity_sold_after_promo),
                                                      ifelse(promo_type=="500 Cashback",
                                                             (base_price*quantity_sold_after_promo)-500,"no")))))
after_table <- aft_promo %>% 
  select(event_id,store_id,revenue_before_promo,revenue_after_promo)

kable(head(after_table))
event_id store_id revenue_before_promo revenue_after_promo
7f650b STCBE-2 6460 4940
a21f91 STBLR-8 61308 37674
78bc80 STVJD-0 6600 12750
a1503f STCBE-1 987000 2999500
1091cf STBLR-6 5940 3836.25
8.02E+96 STBLR-4 17290 11020

Adding Incremental Revenue (IR) column

aft_promo <-aft_promo %>% 
  mutate(incremental_revenue=(as.numeric(revenue_after_promo)-revenue_before_promo),
         incremental_sold_units=(quantity_sold_after_promo-quantity_sold_before_promo))
kable(head(select(aft_promo,event_id,store_id,revenue_before_promo,revenue_after_promo,incremental_revenue,incremental_sold_units)))
event_id store_id revenue_before_promo revenue_after_promo incremental_revenue incremental_sold_units
7f650b STCBE-2 6460 4940 -1520.00 18
a21f91 STBLR-8 61308 37674 -23634.00 -71
78bc80 STVJD-0 6600 12750 6150.00 63
a1503f STCBE-1 987000 2999500 2012500.00 671
1091cf STBLR-6 5940 3836.25 -2103.75 -15
8.02E+96 STBLR-4 17290 11020 -6270.00 25

Merging fact_events and dim_store.csv

full_data <- merge(x=aft_promo,y=stores,by="store_id")
kable((head(select(full_data,event_id,store_id,revenue_before_promo,revenue_after_promo,incremental_revenue,city))))
event_id store_id revenue_before_promo revenue_after_promo incremental_revenue city
5bbf38 STBLR-0 8580 5720 -2860.0 Bengaluru
8e6e77 STBLR-0 44030 92820 48790.0 Bengaluru
b1a501 STBLR-0 15200 10260 -4940.0 Bengaluru
0f750a STBLR-0 387860 353210.6 -34649.4 Bengaluru
75c3a0 STBLR-0 25550 49350 23800.0 Bengaluru
3b4a4f STBLR-0 97730 64380 -33350.0 Bengaluru

Which are the top 10 stores in terms of Incremental Revenue (IR) generated from the promotions?

  top_10_hotels <- full_data %>% 
  select(store_id,incremental_revenue) %>% 
  group_by(store_id) %>% 
  summarise(Incremental_revenue_IR = sum(incremental_revenue)) %>% 
  arrange(-Incremental_revenue_IR) %>% 
  top_n(10)
kable(top_10_hotels)
store_id Incremental_revenue_IR
STMYS-1 4574104
STCHE-4 4468630
STBLR-0 4449984
STMYS-3 4319367
STBLR-7 4263407
STCHE-7 4191161
STBLR-6 4142705
STCHE-3 3989340
STMDU-0 3610217
STBLR-3 3586073

Which are the bottom 10 stores when it comes to Incremental Sold Units (ISU) during the promotional period?

bottom_10_hotels <- full_data %>%
  group_by(store_id) %>% 
  summarise(incremental_sold_units=sum(incremental_sold_units)) %>% 
  arrange(incremental_sold_units) %>% 
  top_n(10)

kable(bottom_10_hotels)
store_id incremental_sold_units
STHYD-0 5705
STHYD-2 5773
STBLR-3 5777
STCHE-3 6091
STCHE-4 6453
STBLR-0 6487
STCHE-7 6560
STBLR-6 6573
STBLR-7 6859
STMYS-1 6862

Performance according to the city

city_data <- full_data %>% 
  group_by(city) %>% 
  summarise(rev_by_city = sum(incremental_revenue)) %>% 
  arrange(-rev_by_city)
kable(city_data)
city rev_by_city
Bengaluru 34358160
Chennai 27197444
Hyderabad 19898072
Mysuru 13128870
Coimbatore 11990708
Madurai 11593876
Visakhapatnam 9515722
Mangalore 4432909
Vijayawada 3679798
Trivandrum 3027786
ggplot(city_data)+geom_col(mapping = aes(x=city,y=rev_by_city,fill=city,options(scipen = 5)))+
  theme(axis.text.x = element_text(angle = 45))+labs(title = "Performance By City",subtitle=
                                                       "Incremental Revenue By each city after promotion",
                                                     x="Name of the City",
                                                     y="Revenue Generated",
                                                     caption = "dataset by : CODEBASICS")
## Warning in geom_col(mapping = aes(x = city, y = rev_by_city, fill = city, :
## Ignoring unknown aesthetics:

Insight1 -> The top 3 performing cities are capital cities of their respective states.

What are the top 2 promotion types that resulted in the highest Incremental Revenue?

promo_types <- full_data %>% 
  group_by(promo_type) %>% 
  summarise(incremental_revenue = sum(incremental_revenue)) %>%
  arrange(-incremental_revenue) %>% 
  top_n(2)

kable(promo_types)
promo_type incremental_revenue
500 Cashback 122593000
BOGOF 21694880

What are the bottom 2 promotion types in terms of their impact on Incremental Sold Units?

bottom_2 <- full_data %>% 
  group_by(promo_type) %>% 
  summarise(incremental_revenue = sum(incremental_revenue)) %>%
  arrange(incremental_revenue) %>% 
  head(2)
  
kable(bottom_2)
promo_type incremental_revenue
25% OFF -3174515
33% OFF -1563356

Performance based on promo type (Discount based VS BOGOF VS Cashback)

promo_differences <- full_data %>% 
  mutate(promo_method = (ifelse(promo_type=="50% OFF"|promo_type=="25% OFF"
                                |promo_type=="33% OFF","Discount",ifelse(
                                  promo_type=="500 Cashback","cashback",
                                  ifelse(promo_type=="BOGOF","BOGOF","no")))))
promo_type_performance <- promo_differences %>% 
  group_by(promo_method) %>% 
  summarise(revenue_generated = sum(incremental_revenue))

ggplot(promo_type_performance) + geom_col(mapping = aes(x=promo_method,y=revenue_generated,fill=promo_method
                                                        ,options(scipen = 5))) + 
                                                        labs(title = "Performance By Promo Type",
                                                             subtitle = 
                                                             "Revenue generated by DISCOUNT VS CASHBACK VS BOGOF",
                                                             x="Promotion Type",Y="Revenue Generated",
                                                             caption= "DATASET FROM: CODEBASICS")

Insight -> 2 :

In summary, “Cashback” promotions are the most successful in generating revenue, followed by “BOGOF,” while “Discount” promotions appear to perform poorly or even result in losses. Businesses may consider focusing more on cashback or BOGOF promotions based on this performance analysis.

Which promotions strike the best balance between Incremental Sold Units and maintaining healthy margins?

ggplot(data = promo_differences,aes(x = incremental_sold_units, y = incremental_revenue,color=promo_method))+geom_line()+
  geom_jitter(width = 10, height = 10) + labs(title = "Balance Between ISU and IR",
                                                             x="ISU",y="IR",
                                                             caption= "DATASET FROM: CODEBASICS")

Insight 3 ->

In summary, while Cashback promotions drive revenue growth with increasing sales volume, Discount promotions may lead to revenue loss, and BOGOF promotions show relatively stable revenue performance. These insights can inform promotional strategies and help optimize revenue generation in marketing campaigns.

Merging sales_data and product_category rata (fact_events + dim_products.csv)

product_category <- read_csv("datasets/dim_products.csv")
final_dataset <- merge(promo_differences,product_category,by="product_code")

Which product categories saw the most significant lift in sales from the promotions?

categorical_analysis <- final_dataset %>% 
  group_by(category) %>% 
  summarise(quantity_sold_before_promo = sum(quantity_sold_before_promo),
            quantity_sold_after_promo = sum(quantity_sold_after_promo),
            percentage_growth = (((quantity_sold_after_promo-quantity_sold_before_promo)/quantity_sold_before_promo)*100))

ggplot(data = categorical_analysis) + geom_col(mapping = aes(x=category,y=percentage_growth,fill=category,width=0.3))+
  theme(axis.text.x = element_text(angle = 30)) + labs(title = "Performance By Product Category",
                                                             subtitle = 
                                                             "Percentage growth in quantities sold by different categories",
                                                             x="Product Category",y="Percentage growth",
                                                             caption= "DATASET FROM: CODEBASICS")

Insight 4 ->

-> The “Home Appliances” category exhibits the highest growth rate of 264%.

      Promotional activities in this category appear to have effectively stimulated consumer interest and led           to increased sales. The promotions may have included discounts, special offers, or marketing campaigns            tailored to the target audience's preferences.

-> The “Personal Care” category shows the lowest growth rate of 24%.

      The relatively lower growth rate suggests that promotional activities in the personal care category may           not have been as effective in driving sales compared to other categories.While the growth rate is lower,           it presents opportunities for businesses to refine their promotional strategies, enhance product                  offerings, or address consumer needs more effectively.
write.csv(final_dataset, file = "final_dataset.csv", row.names = FALSE)
cat_wise <- final_dataset %>% 
  group_by(category) %>% 
  summarise(incr = sum(incremental_revenue))
kable(cat_wise)
category incr
Combo1 122593000.0
Grocery & Staples 5094587.8
Home Appliances 7833640.0
Home Care 4147616.2
Personal Care -845498.5

TASK 1 (ad-hoc Analysis):

Provide a list of products with a base price greater than 500 and that are featured in promo type of 'BOGOF'     (Buy One Get One Free). This information will help us identify high-value products that are currently        being  heavily discounted, which can be useful for evaluating our pricing and promotion strategies.
products_bp_over_500 <- final_dataset[final_dataset$promo_type=="BOGOF" & final_dataset$base_price>500,]

products_bp_over_500 <- products_bp_over_500 %>% 
  select(product_code,product_name,base_price,promo_type)

products_bp_over_500 <- unique(products_bp_over_500)
kable(products_bp_over_500)
product_code product_name base_price promo_type
701 P08 Atliq_Double_Bedsheet_set 1190 BOGOF
1301 P14 Atliq_waterproof_Immersion_Rod 1020 BOGOF

TASK-2 (ad-hoc analysis)

Generate a report that provides an overview of the number of stores in each city. The results will be sorted      in descending order of store counts, allowing us to identify the cities with the highest store presence. The      report includes two essential fields: city and store count, which will assist in optimizing our retail            operations.
city_store_count <- final_dataset %>% 
  group_by(city) %>% 
  summarise(store_count = length(unique(store_id))) %>% 
  arrange(-store_count)
kable(city_store_count)
city store_count
Bengaluru 10
Chennai 8
Hyderabad 7
Coimbatore 5
Visakhapatnam 5
Madurai 4
Mysuru 4
Mangalore 3
Trivandrum 2
Vijayawada 2

TASK-3 (Ad-hoc Analysis)

Generate a report that displays each campaign along with the total revenue generated before and after the campaign? The report includes three key fields: campaign _name total revenue(before_promotion), total revenue(after_promotion).

campaign_revenue <-final_dataset %>% 
  group_by(campaign_id) %>% 
  summarise(revenue_before_promo = sum(revenue_before_promo),revenue_after_promo=sum(as.numeric(revenue_after_promo)),
            differnce = (revenue_after_promo-revenue_before_promo))

kable(campaign_revenue)
campaign_id revenue_before_promo revenue_after_promo differnce
CAMP_DIW_01 82573759 185648222 103074463
CAMP_SAN_01 58127429 93876312 35748883

TASK 4 (Ad-Hoc Analysis)

Produce a report that calculates the Incremental Sold Quantity (ISU%) for each category during the Diwali         campaign. Additionally, provide rankings for the categories based on their ISU%. The report will include three     key fields: category, isu%, and rank order. This information will assist in assessing the category-wise           success and impact of the Diwali campaign on incremental sales.
category_wise_rank <- final_dataset %>% 
  group_by(category) %>% 
  summarise(quantity_sold_before_promo = sum(quantity_sold_before_promo),
            quantity_sold_after_promo=sum(quantity_sold_after_promo),
            ISU_percentage=(((quantity_sold_after_promo-quantity_sold_before_promo)/
                              quantity_sold_before_promo)*100)) %>% 
  arrange(-ISU_percentage) 

category_wise_rank <- mutate(category_wise_rank,rank = row_number())

category_wise_rank <- category_wise_rank %>%
                      select(rank, category, ISU_percentage)

kable(category_wise_rank)
rank category ISU_percentage
1 Home Appliances 264.39203
2 Combo1 183.33109
3 Home Care 106.59785
4 Grocery & Staples 94.01748
5 Personal Care 24.50205

TASK 5 (Ad-Hoc Analysis)

Create a report featuring the Top 5 products, ranked by Incremental Revenue Percentage (IR%), across all          campaigns. The report will provide essential information including product name, category, and ir%. 
top_5_products <- final_dataset %>%
  group_by(product_name,category) %>% 
  summarise(ir_percent = mean((incremental_revenue/revenue_before_promo)*100)) %>% 
  arrange(-ir_percent) %>% 
  head(5)

kable(top_5_products)
product_name category ir_percent
Atliq_Home_Essential_8_Product_Combo Combo1 162.06271
Atliq_waterproof_Immersion_Rod Home Appliances 79.30972
Atliq_Double_Bedsheet_set Home Care 79.18456
Atliq_High_Glo_15W_LED_Bulb Home Appliances 78.75562
Atliq_Curtains Home Care 78.57854