Exploratory Data Analysis - Super Store Retail

As a business manager, we will try to find out the weak areas where we can work to make more profit. what all business problems we can derive by exploring the data?

We will create a Power BI Dashboard using the dataset Superstore.

This project will be split into 2 parts:

data preprocessing: Using Power Query and M-language where we will split our one large 10000 records and 13 wide columns into star schema for better analysis and performance.
Power BI Dashboard: showing the analysis of the data and the insights we can derive from it.

Part 1: Data Preprocessing:

After Exploring the data and modeling the Data here is the star schema used for developing the dataset

There were no Date/Time Data, so we would be limited to analyzing 3 facts [Sales, Profit, Quantity].
- based on different dimensions [Shipment Mode, Customer Segment, Geo-location, Product Category]
- Analyze the effect of Discount on Sales and profit

Part 2: Data Exploration and Analysis Dashboard

firstly we provided General insights:
The Store has delivered $38K product across 9994 orders with an average 3.79 Product per order.
Accomplished total Profit of 288K from Sales of $2.3M, with an average $230 per order and $29 per product Price.
secondly we presented Category Insights:
- With products span across 3 categories, the Office Supplies is most sold products with 22,981 sold products which represent 60.5% of total sales, followed by Technology and Furniture with ~8,000 sales each.
- The most sold products are the Binders with 6,010 sold products, followed by Paper with 5,192 sold products, both belongs to office supplies.
- While the Office Supplies is the most sold category in quantity, it is not the most profitable, this place is occupied with Technology with the highest sales around $839k and highest profit with $146K.
- Another interesting fact is that the Furniture category is the least profitable category with only $19k profit from 720k sales, in fact we can see that both Tables ($207k sales and $17 loss in profit) and Bookcases ($115k sales and $4k loss in profit) are the most losing products.
- On the other hand we can see that the most profitable products belongs to technology category, with the Copiers as the most profitable product with $150k sales and $56k profit, followed by Phones with $331k sales and $45k profit, and Accessories with $167k sales and $42k profit.
also we produced Products and Geo-graphical insights:
- The most profitable products is Copiers and Phones with $56k and $45k profit respectively and the most losing products are Tables and Bookcases with $18k and $4k loss respectively.
- The most profitable state is California with $76k profit, followed by New York with $71k profit, and the most losing state is Texas with $25k loss, followed by Ohio with $18k loss.
- The most profitable city is New York City with $65k profit, followed by Los Angeles with $34k profit, and the most losing city is Philadelphia with $15k loss, followed by Houston with $14k loss.
Finally we examined the effect of remaining dimensions on the sales facts:
- Shipment Mode:
  - The most used shipment mode is Standard Class with 59% of total orders, followed by Second Class with 19% of total orders, and the most profitable shipment mode is First Class with $31k profit, followed by Second Class with $29k profit.
  - shipping mode has no effect on the sales and profit.
- Customer Segment:
  - The most profitable customer segment is Home Office with $60k profit, followed by Corporate with $60k profit, and the most losing customer segment is Consumer with $11k loss.
  - the customer segment has no effect on the sales and profit.
- Discounts:
  - As we can see that all of discounts above 30% are losing money, and the most losing discount is 80% with $2k loss, followed by 70% with $1.5k loss, and the most profitable discount is 10% with $7k profit, followed by 20% with $6k profit.
  - We can suggest limiting the discount to 20% to maximize the profit, avoid discount above 30% to avoid losing money.
  - We can also suggest following another marketing strategies instead of discounting, such as offering free shipping, or offering free gift with the order.
  - Higher discounts have no effect on the sales or quantity while effecting the branding image, showing the product as low quality.