E-Commerce Store Analysis Using SQL

PROBLEM STATEMENT

As the owner of an e-commerce store, SAM wants to gain insights into the performance of his online business and make data-driven decisions to improve profitability, customer satisfaction, and overall business growth. To achieve this, we need to conduct a comprehensive analysis of your e-commerce data.

INSIGHTS SOUGHT BY E-COMMERCE OWNER

1. How many orders were processed?
2. What is the total revenue?
3. The average profit, discount, and shipping cost per sale?
4. Which product category generates the highest sales revenue?
5. What is the profitability for each product category?  (Profit Margin by Product Category)
6. What are the top 10 and worst 10 selling products? How does their performance compare to other products?
7. How does the average order value vary across different customer segments?
8. What are the monthly trends in sales and Which months or seasons experience a significant increase or decrease in sales?
9. How does the sales performance vary across different regions or countries?
10. What is the average time to process an order (order date to ship date) for each ship mode? 
11. Is there a ship mode that consistently performs better regarding order processing time?

RESOURCES

SQL QUERIES

Q1: How many orders were processed?

SELECT COUNT(DISTINCT([Order ID])) AS total_orders FROM orders

I used the COUNT() to count the [Order ID] but there were duplicates so I also used DISTINCT().

A total of 5129 orders were processed

Q2: What is the total revenue?

SELECT 
	SUM(sales) AS total_Revenue 
FROM products

Here is used SUM() in order to ass the sales as total_revenue

A total of $8,024,275 USD is generated from 5129 orders

Q3: The average profit and shipping cost per sale?

In order to find the average profit and shipping cost I used the AVG() function. But it returned values up to 4 decimal places so in order to get 2 decimal places I used the ROUND() function and then I placed it in average_values CTE.

WITH average_values  AS (
	SELECT 
		ROUND(AVG(Profit),2) AS avg_profit, 
		ROUND(AVG([Shipping Cost]),2) AS avg_shipping 
	FROM products
 )

SELECT * FROM average_values

Q4: Which product category generates the highest sales revenue?

SELECT 
	[Product Category], 
	SUM(Sales) AS total_revenue 
FROM products
GROUP BY [Product Category]
ORDER BY total_revenue DESC

Q5: What is the profitability for each product category?

In order to find the profit margin we can use the profit margin formula [profit/revenue * 100]. 

SELECT 
	[Product Category], 
	SUM(sales) AS total_revenue, 
	SUM(Profit) AS total_profit, 
	(SUM(profit)/SUM(sales))*100 AS profit_margin 
FROM products
GROUP BY [Product Category]
ORDER BY total_profit DESC

Q6: What are the top 10 selling products?

SELECT 
	TOP 10 Product, 
	SUM(Quantity) AS total_unit_sold 
FROM products
GROUP BY Product
ORDER BY total_unit_sold DESC

In order to find the worst 10-selling products I just changed the order from DESC to ASC

SELECT 
	TOP 10 Product, 
	SUM(Quantity) AS total_unit_sold 
FROM products
GROUP BY Product
ORDER BY total_unit_sold ASC

Q7: How does the average order value vary across different customer segments?

SELECT 
	c.Segment, 
	ROUND(avg(p.sales),2) AS avg_order_value 
FROM customers c
FULL JOIN products p
ON c.[Order ID] = p.[Order ID]
WHERE c.Segment IS NOT NULL
GROUP BY c.Segment
ORDER BY avg_order_value DESC

Q8: What are the monthly trends in sales and Which months or seasons experience a significant increase or decrease in sales?

SELECT 
	DATENAME(month, o.[Order Date]) AS month_name, 
	SUM(p.Quantity) AS total_orders, 
	SUM(p.Sales) AS total_revenue

FROM orders o
FULL JOIN products p
ON o.[Order ID] = p.[Order ID]

WHERE o.Months IS NOT NULL
GROUP BY DATENAME(month, o.[Order Date])
ORDER BY MIN(o.[Order Date])

Top 3 Months based on revenue

SELECT 
	TOP 3 DATENAME(month, o.[Order Date]) AS month_name, 
	SUM(p.Quantity) AS total_orders, 
	SUM(p.Sales) AS total_revenue 

FROM orders o
FULL JOIN products p
ON o.[Order ID] = p.[Order ID]

WHERE o.Months IS NOT NULL
GROUP BY DATENAME(month, o.[Order Date])
ORDER BY total_revenue DESC

Worst 3 months based on revenue

SELECT 
	TOP 3 DATENAME(month, o.[Order Date]) AS month_name, 
	SUM(p.Quantity) AS total_orders, 
	SUM(p.Sales) AS total_revenue 

FROM orders o
FULL JOIN products p
ON o.[Order ID] = p.[Order ID]

WHERE o.Months IS NOT NULL
GROUP BY DATENAME(month, o.[Order Date])
ORDER BY total_revenue 

Q9: How does the sales performance vary across different regions or countries?

SELECT 
	r.Country,
	SUM(p.Quantity) AS total_orders, 
	SUM(Sales) AS total_revenue, 
	SUM(Profit) AS total_profit 

FROM products p
FULL JOIN regions r
ON p.[Order ID] = r.[Order ID]
GROUP BY r.Country 

Q10: What is the average time to process an order for each ship mode?

WITH shipping_data AS (

SELECT 
	[Ship Mode],
	order_date, 
	ship_date, 
	DATEDIFF(day, order_date, ship_date) AS ship_days

FROM 
(	SELECT 
			CONVERT (DATE, [Order Date]) AS order_date, 
			CONVERT (DATE, [Ship Date]) AS ship_date, 
			[Ship Mode] 
	FROM orders ) AS shipping_date 
)

SELECT 
	[Ship Mode], 
	AVG(ship_days) AS avg_ship_days 

FROM shipping_data
GROUP BY [Ship Mode]