Created by- Saddam Ansari @Aspiring Data Analyst Linkeldin Profile
Live Dashboard at Novypro Live_link_Novypro
Power BI Live Dashboard Link
Prsentation Link
Business Reqest Solutions with query and result Link
Masterdata where you can see which types of changes i have made link
- Challenge Objective
- Promotion Overview
- About dataset
- Master Data
- Business Request
- KPI
- Recommendation Insight
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.
"AtliQ Mart conducted two major campaign promotions: the Diwali Campaign 2023 and the Sankranti Campaign 2024. During both campaigns, the following promotions were implemented:
- 25% off
- 33% off
- 50% off
- 500 cashback
- BOGOF (Buy One Get One Free)
For this project, I was provided with separate tables of data, namely dim_campaign, dim_product, dim_store, and fact_events.
I combined all of these tables into one, named 'masterdata,' for ease of use-
Eg;
| event_id | store_id | campaign_id | product_code | base_price | promo_type | quantity_sold_before_promo | total_revenue_generated_before_promo | quantity_sold_after_promo | ISU_Incremental_Sold_Quantity_Percentage | FinaL_quantity_sold_after_promo | based_on_new_quantity_ISU_percentage_change | how_many_quntity_goes_free | after_promo_revenue_without_promo | after_promo_price_affected | how_much_disconted_ammount | incremental_revenue_IR_change | incremental_revenue_IR_change_without_promo | product_name | category | city | campaign_name | start_date | end_date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 7f650b | STCBE-2 | CAMP_SAN_01 | P11 | 190 | 50% OFF | 34 | 6460 | 52 | 52.94% | 52 | 52.94% | 0 | 9880 | 4940 | 4940 | -23.53% | 52.94% | Atliq_Doodh_Kesar_Body_Lotion (200ML) | Personal Care | Coimbatore | Sankranti | 10-01-2024 | 16-01-2024 |
| a21f91 | STBLR-8 | CAMP_DIW_01 | P03 | 156 | 25% OFF | 393 | 61308 | 322 | -18.07% | 322 | -18.07% | 0 | 50232 | 37674 | 12558 | -38.55% | -18.07% | Atliq_Suflower_Oil (1L) | Grocery & Staples | Bengaluru | Diwali | 12-11-2023 | 18-11-2023 |
| 78bc80 | STVJD-0 | CAMP_SAN_01 | P07 | 300 | BOGOF | 22 | 6600 | 85 | 286.36% | 170 | 672.73% | 85 | 51000 | 25500 | 25500 | 286.36% | 672.73% | Atliq_Curtains | Home Care | Vijayawada | Sankranti | 10-01-2024 | 16-01-2024 |
| a1503f | STCBE-1 | CAMP_DIW_01 | P15 | 3000 | 500 Cashback | 329 | 987000 | 1000 | 203.95% | 1000 | 203.95% | 0 | 3000000 | 2500000 | 500000 | 153.29% | 203.95% | Atliq_Home_Essential_8_Product_Combo | Combo1 | Coimbatore | Diwali | 12-11-2023 | 18-11-2023 |
| 1091cf | STBLR-6 | CAMP_DIW_01 | P05 | 55 | 25% OFF | 108 | 5940 | 93 | -13.89% | 93 | -13.89% | 0 | 5115 | 3836 | 1279 | -35.42% | -13.89% | Atliq_Scrub_Sponge_For_Dishwash | Home Care | Bengaluru Diwali | 12-11-2023 | 18-11-2023 | |
| 8.02E+96 | STBLR-4 | CAMP_DIW_01 | P11 | 190 | 50% OFF | 91 | 17290 | 116 | 27.47% | 116 | 27.47% | 0 | 22040 | 11020 | 11020 | -36.26% | 27.47% | Atliq_Doodh_Kesar_Body_Lotion (200ML) | Personal Care | Bengaluru | Diwali | 12-11-2023 | 18-11-2023 |
| 73d86f | STVSK-2 | CAMP_SAN_01 | P10 | 50 | 25% OFF | 31 | 1550 | 26 | -16.13% | 26 | -16.13% | 0 | 1300 | 975 | 325 | -37.10% | -16.13% | Atliq_Cream_Beauty_Bathing_Soap (125GM) | Personal Care | Visakhapatnam | Sankranti | 10-01-2024 | 16-01-2024 |
| fe3560 | STHYD-4 | CAMP_DIW_01 | P13 | 350 | BOGOF | 73 | 25550 | 245 | 235.62% | 490 | 571.23% | 245 | 171500 | 85750 | 85750 | 235.62% | 571.23% | Atliq_High_Glo_15W_LED_Bulb | Home Appliances | Hyderabad | Diwali | 12-11-2023 | 18-11-2023 |
Start by importing the 'retail_events_db' database into MySQL Workbench. Craft SQL queries to address the specified business questions. Save these queries in an SQL file and upload it to GitHub. Share the GitHub link and include query outputs in your presentation.
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.
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.
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). This report should help in evaluating the financial impact of our promotional campaigns. (Display the values in millions).
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.
Note: ISU% (Incremental Sold Quantity Percentage) is calculated as the percentage increase/decrease in quantity sold (after promo) compared to quantity sold (before promo)
-
First, to calculate the quantity sold after the promo, a new column was created. In this column, if the quantity after the promo corresponds to a BOGOF (Buy One Get One Free) offer, it is multiplied by 2; otherwise, it remains the same.
-
Next, to compute the ISU (Incremental Quantity Sold %), another new column was created. A DAX formula was applied to calculate the percentage change in quantity sold after subtracting the quantity sold before and then dividing by the quantity sold before. This resulted in the percentage change value, which was then converted to a percentage format.
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%. This analysis helps identify the most successful products in terms of incremental revenue across our campaigns, assisting in product optimization.
-
For the analysis of the top 5 products based on revenue before and after the promotion, along with the calculation of the Incremental Revenue Percentage (IR%), I have initially created a column called "Revenue After Promo." This column calculates the revenue after the promotion by subtracting the discounted amount based on the promo percentage (e.g., 20%, 33%, 55%, etc.) from the base value. Then, this adjusted value is multiplied by the quantity to derive the revenue after the promotion.
-
Subsequently, I computed the Incremental Revenue (IR) similar to the ISU formula, which involves calculating the difference between the revenue after the promotion and the revenue before the promotion.
Which are the top 10 stores in terms of Incremental Revenue (IR) generated from the promotions?
Which are the bottom 10 stores in terms of Incremental Revenue (IR) generated from the promotions?
Which are the bottom 10 stores when it comes to Incremental Sold Units (ISU) during the promotional period?
Which are the Top 10 stores when it comes to Incremental Sold Units (ISU) during the promotional period?
How does the performance of stores vary by city? Are there any common characteristics among the top-performing stores that could be leveraged across other stores?
-
The performance of stores is indeed influenced by the city in which they are located. For instance, the top-performing store, STCHE-7, which boasts high Incremental Revenue Percentage (IR%) and Incremental Sales Uplift Percentage (ISU%), is situated in Chennai. Similarly, the second top performer, STBLR-7, with remarkable IR% and ISU%, is located in Bengaluru. Additionally, the third top performer is situated in Mysore.
-
This indicates that the top cities have the top-performing stores exhibit superior sales and revenue performance.
-
Yes, there are indeed common characteristics among the top-performing stores that could be leveraged across other stores. For instance, the top-performing stores have witnessed the highest sales and revenue generation through the "Buy One Get One" promotion and the 500 cashback offer.
-
Additionally, these stores have observed significant sales of certain common products, which were among the top-selling items across both stores. Furthermore, it was found that the highest sales in the home appliance category occurred in the top two stores.
Check out this insights to understand top 2 store have whats kinds of things common
What are the top 2 promotion types that resulted in the highest Incremental Revenue?
What are the bottom promotion types that resulted in the lowest Incremental Revenue?
What are the bottom 2 promotion types in terms of their impact on Incremental Sold Units?
What are the top promotion types in terms of their impact on Incremental Sold Units?
Is there a significant difference in the performance of discount-based promotions versus BOGOF (Buy One Get One Free) or cashback promotions?
-
Based on my analysis, there is a significant difference in the performance between discount-based promotions and BOGOF (Buy One Get One Free) or cashback promotions. This is evident when considering incremental revenue, as BOGOF and the 500 cashback promotions are the only ones where revenue increased after the promotion. In contrast, for other discounted promotions, revenue either remained stagnant or decreased post-promotion.
-
Similarly, when examining the Incremental Sales Uplift Percentage (ISU%), BOGOF and the 500 cashback promotions exhibit the highest ISU, indicating that these promotions resulted in the highest increase in quantity sold post-promotion.
-
On the other hand, discounted offers show minimal growth, and in some cases, even a decrease in quantity sold, highlighting the stark difference in performance between discount-based promotions and BOGOF or cashback promotions.
Which promotions strike the best balance between Incremental Sold Units and maintaining healthy margins?
To determine which promotion strikes the best balance between Incremental Sold Units (ISU) and maintaining healthy margins, we can calculate the revenue per unit sold for each promotion. A higher revenue per unit sold indicates a better balance between ISU and maintaining healthy margins.
Let's calculate the revenue per unit sold for each promotion:
- 500 Cashback : Revenue per unit sold = 158 million / 63 thousand = 2500
- BOGOF : Revenue per unit sold = 95 million / 431 thousand ≈ 220.41
- 25% OFF: Revenue per unit sold = 6 million / 38 thousand ≈ 157.89
- 50% Off: Revenue per unit sold = 1 million / 28 thousand ≈ 35.71
- 33% Off: Revenue per unit sold = 35 million / 91 thousand ≈ 384.62
Based on the calculations, Promotion 500 Cashback has the highest revenue per unit sold, followed by Promotion 33% Off. These promotions may be considered as striking the best balance between Incremental Sold Units and maintaining healthy margins.
Promotion BOGOF also shows a relatively high revenue per unit sold. However, Promotion 25% Off and 50% Off have lower revenue per unit sold, indicating potentially lower margins despite higher quantities sold.
Therefore, Promotions 500 Cashback, BOGOF, and 33% Off could be considered as the best balance between Incremental Sold Units and maintaining healthy margins.
Which product categories saw the most significant lift in sales from the promotions?
Which product categories saw the most significant lift in revenue from the promotions?
Are there specific products that respond exceptionally well or poorly to promotions?
Are there specific products that respond exceptionally well or poorly to promotions? based on QTY-
What is the correlation between product category and promotion type effectiveness?
Based on the previous analysis, it is recommended that AtliQ Mart should consider opening its new store in the top-performing city. This decision is supported by our observation that the top city generates higher revenue.
For promotional activities, it is suggested to focus on seasonal products. Our analysis indicates that offering promotions on seasonal items tends to result in higher sales.
When deciding on which product category to promote, it is advisable to target products that are in high demand during specific seasons. For example, during summer, promotions can be offered on products such as sunglasses, glucose, refrigerators, fans, etc.
To increase revenue in bottom-performing stores, it is essential to understand the local area's culture and preferences. By catering to local preferences and offering tailored promotions, it is possible to boost sales. Additionally, running special offers can attract more customers and drive revenue growth.
Based on our analysis, promotions such as 500 cashback, BOGOF (Buy One Get One Free), and 33% discounts tend to perform well. These promotions maintain a good balance between post-promotion revenue and profit margin.
We can do some such promotional activities at our supermarket in summer which will attract customers and improve sales in the summer season. Here are some proposed promotional strategies which can be effective in summer season:
Focus on cooling products in the summer season, such as air conditioners, fans, refrigerators, air coolers, etc. Customers can be attracted by providing discounts or special offers on these products.
Organizing promotions on summer wear, like cotton clothes, beachwear, sunglasses, caps, etc. Sales can be improved by highlighting seasonal clothing lines.
In the summer season, providing discounts or combo offers on hydration products, like water bottles, energy drinks, fruit juices, etc. Customers will be helped in providing essential products for hydr
In the summer season, providing discounts or combo offers on hydration products, like water bottles, energy drinks, fruit juices, etc. Customers will be helped in providing essential products for hydr
This challenge was a great opportunity to learn and apply advanced data analysis techniques. I also learned how to create visually appealing and informative dashboards that can be used to make data-driven decisions.
also This project has been truly rewarding, allowing me to showcase my work and further my journey as an aspiring data analyst. It not only adds value to my portfolio but also demonstrates my capabilities in handling complex datasets.
I'm grateful to Codebasics for providing a platform for aspiring data analysts to work on real-time datasets and enhance their skills.
Check out my submission and let me know what you think!
Your feedback is highly plays a crucial role in my growth, so please feel free to share any suggestions.
Apart from this project, I have also completed over 20+ Power BI projects, which you can explore on NovyPro at NovyPro Portfolio
Thank you for taking the time to view my project. I hope you enjoyed it.
Note;- I am Open to internship or entry level of data analytics or power bi developer role , so you have any oppening which is based on my profile so please connect with me on Linkeldin and disccuse your requirement.
Created and Analyzed by:
Saddam Ansari @Aspiring Data Analyst LinkedIn
Location: India