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 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)))
| 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))))
| 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 |
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")
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)
| 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)
| 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)
| 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)
| 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)
| 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)
| 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 |