DATASET
Data - An ecommerce company's sales data with 113271 rows over two years, 2021 and 2022. Data Analysis - SQL Server Data Visualization - Microsoft Power BI
The following questions were answered using SQL:
Total revenue
Total Profit
Revenue by year
Yearly revenue percent change
Profit by year
Profit yearly percent change
Average order value
Average order quantity
Average profit per order 10 Top 5 bestselling products
5 worst selling products
Bestselling category
Bestselling region
Top 5 bestselling states
Top 5 worst selling states 16 Top 5 most profitable products
5 least profitable products
profit by category 19 highest spending customers
Highest grossing months
Relationship between order and delivery
INSIGHTS AND RECCOMMENDATIONS
The business generated a total of $23M is sales in 2 years.
Although profits increased by 4.5%, sales went down almost 1% in 2022. The company could easily have made more if it had matched 2021 sales. The reasons for the drop in sales should be investigated
The bestselling category by far proved to be Office supplies (60%). It is also the most profitable (60%). The company should consider going all in on office supplies.
Worst performing products should be dropped.
Average order value (AOV) can be increased by incentivizing the purchase process in order to make customers spend more.
Customers who spent the most should be recognized and made long-term loyal customers to boost revenue.
The company's marketing activities should be increased in the 5 states which generated the most revenue: California, New York, Texas, Pennsylvania, and Washington.
54.8% (62051) of orders were delivered late. That could easily have resulted in lost revenue for the company. Clearly, this needs to be worked on. The reasons for the late deliveries should be investigated and addressed.
Canceled orders accounted for 4.3% of all orders. It should be as low as possible.
