top of page

SQL end-to-end project

Updated: Feb 21, 2023




Problem Statement

Top n Down is a wholesale shop headquartered in Ranchi, Jharkhand. It has multiple stores in several cities. It has been in the market for 4 years and operates across several cities in India. Despite making a net profit in the current year (2012), the company did not hit its estimated target of Rs 50,00,000 and missed the target by Rs 19,00,000.





Objective

To find the root cause for missing revenue targets for the Financial Year 2012


Dataset

The analysis was made on 5 different datasets related to the business. The datasets are based on Customer Information, Turnover information, Product Information, Order Details, and shipping details.



Star Schema


Profit Comparison: 2011 vs 2012


Profits were more in the year,2012 except in the month of December when the profit was all-time low that year.


December is the only month where there have been net losses for 2 consecutive years.


Sales and profit were more in year 2012 than in 2011 which shows growth but did not meet the expected revenue target.



Possible Root causes

There could be many reasons for not reaching the estimated revenue. These problems could be:




High Expenses


Shipping Costs



Most of the expenses in shipping are spent on delivering by trucks (51%) and then followed by express air (48%).


To further break it down, we can see why the cost of delivery is high with regard to the truck and express air.





Most products are shipped to the south of India ( 10 out of 13), 1 in the west and 2 in the northern parts.


Since, the shop is headquartered in Ranchi, Jharkhand, the shipping costs are extremely high due to the long distance from the store.


Profit and Demand by City


Most customers are from the south of India


Most profitable cities are also from the south. Despite Patna being the closest city, it is one of the least profitable cities.


*Inventory cost data not available


Product Pricing


The most profitable customers are corporate and the least profitable are consumers but are the third highest buyers (3rd highest demand from consumers)


Product category

The most profitable category is a technology and the least profitable is furniture.




Product Sub-Category

There has been demand every year for each sub-category and every category has been sold out and yet profits were not made on several items.





Competitor price analysis

Most competitor's average price is higher than Top n Down




The pricing is low due to the high discounts offered especially during the month of December.


Suggestions and Recommendations

There are two problems at hand:


I. High Shipping Cost

II. Low Pricing


(1) A high shipping cost can be reduced by possibly opening a franchise in the South to reduce delivery costs, a place with a low cost to start. This might cost initially to set up but it's an investment that can reduce costs in the long run. An ideal store can be set up in Bengaluru, which is the closest to most cities where our customers belong to.


By setting up a store in Bengaluru, costs can be reduced significantly and delivery can be made faster. Costs will not be exuberant during on holiday season, especially during the Christmas season which reduces expenses.


Customers by city

(2) Discounts can be less frequent and prices can be charged higher. Since competitors are charging a price above that of Top N Down, it can be inferred that people are ready to pay more. Since a customer base has been built, most trusted customers will be willing to pay a little extra for the same brand.


Moving away from a loss leader


(3) Since most of our customers our from Corporates and small businesses, social media advertising, search engine marketing, Email Marketing, and advertising at conferences & workshops are recommended to acquire consumers.








Notes and references


[1] Data extraction was done using MySQL and Visualizations were created using Power BI


[2] All data are fictional and used for demonstration


[3] Dataset and SQL files are available on Github Repository.



[5] Pricing Strategy Image source: www.cfoperspective.com

B2B Effective Marketing Image source: https://contentmarketinginstitute.com


[6] India map (Shape Map) .JSON file for Power BI available on: https://github.com/deldersveld/topojson/blob/master/countries/india/india-states.json










Comments


bottom of page