R Mart - campaign analysis

---

R Mart is a retail giant with over 50 supermarkets in the southern region of India. All their 50 stores ran a massive promotion on their branded products, during Diwali Festival - 2023 and Sankranti Festival - 2024 (Big festivals in India). 

The sales director wants to understand which promotions did well and which did not, so that they can make decisions and get information for their next promotional period.  Also, the overview of stores, highest number of stores across cities and the top performers. With respect to products and their performance in terms of different Promo types, in comparison with percentage of Incremental revenue and sold quantity.

• dim_campaigns–Comprehensive information about the campaigns.
• dim_products–Information regarding products.
• dim_stores- Details about stores and their respected locations.
• fact_events-Complete dataset of sales and revenue during the campaign season.

SQL Code:

select * from dim_campaigns;

select * from dim_products;

select * from dim_stores;

select * from fact_events;

-- Base price greater than 500 and "BOGOF"

select product_code,promo_type,base_price

from fact_events 

where base_price >=500 and promo_type="BOGOF" 

group by product_code

order by product_code;

-- Store distribution by city

select s.city,count(f.store_id) as store_count from fact_events f 

right join dim_stores s 

on f.store_id=s.store_id

group by s.city;

-- total revenue by diffrent campaigns

select * from fact_events;

select campaign_id,

sum(revenue_beforepromo)/1000000 beforepromo_in_millions,

sum(revenue_after_promo)/1000000 as afterprom_in_millions

from fact_events

group by campaign_id

order by 2,3 desc ;

-- ISU% for each category

select * from fact_events;

with ISU_cte as (

select pr.category,sum(f.ISU)/sum(f.quantity_sold_beforepromo)*10 as percent

from fact_events f

join dim_products pr

on f.product_code=pr.product_code

where f.campaign_id='CAMP_DIW_01'

group by pr.category

) 

select category,round(percent,2),RANK() OVER (ORDER BY percent DESC) AS categoryrank

from ISU_cte

order by categoryrank ;

-- top 5 products on incremental revenue

with IR_cte as (

select pr.product_name,pr.category,sum(f.IR)/sum(f.revenue_beforepromo)*10 as percent

from fact_events f

join dim_products pr

on f.product_code=pr.product_code

group by pr.product_name,pr.category

)

select * from(

select product_name,category

,percent,RANK() OVER (ORDER BY percent DESC) AS categoryrank

from IR_cte

group by product_name,category

order by categoryrank 

)as ranked_data

where categoryrank<=5

• The analysis of stores, focusing on the top-performing stores in terms of revenue, sales, IR%, ISU%. This analysis will provide valuable insights into the best-performing stores, enabling informed decision-making and strategic planning for the future.
• This analysis helps businesses determine evaluate the effectiveness of promotional campaigns, and identify opportunities for revenue growth.
• This analysis will provide valuable insights into customer segmentation across various promotion’s, shedding light on the effectiveness of these promotions and the opportunities