Problem Statement: Building an SQL Project with E-Commerce Dataset from Kaggle
The goal of this project is to analyze an E-Commerce dataset from Kaggle using SQL. The focus of the analysis is to evaluate sales performance, identify popular and underperforming products, track market trends, and discover high-value customers. Through this analysis, you will compare actual sales with sales targets to uncover key insights, optimize product strategies, and enhance decision-making for better business outcomes.
For this project, I obtained the dataset from Kaggle containing sales data from an Indian E-commerce website spanning from April 2018 to March 2019.
- The marketing department is running a sales campaign and they target the customer with different sales materials. They categorized customers into groups based on the RFM model. Show the number and percentage for each customer segment as the final result. Order the results by the percentage of customers.
- Almost 50% of the customers were loyal customers (spend well and often) and champions (spend well and often, as well as make a recent purchase). The rest of the customers falls within the categories of potential loyalists, hibernating, customers needing attention, at risk, and about to sleep.
- There are 500 orders and 332 customers from 24 different cities and 19 states from April 2018 to March 2019.
- The most profitable cities are Pune, followed by Indore, Allahabad, and Delhi. This may be because these areas are more developed (e.g. having a better internet connection and better logistics).
- Delhi is the last state where this Indian e-commerce website established its footprint. However, the profit generated from Delhi state is much higher than that from Gujarat state (the first state with business). Hence, it is concluded that the customers from Delhi truly have higher purchasing power.
- The highest sales happened on Sunday. However, the number of orders is the highest on Monday. This may happen because the customers selected the items they want to order on Sunday, and placed their orders on Monday.
- Losses occurred from April 2018 to September 2018. Luckily, there was a high profit from October 2018 onwards, followed along with an increase in the quantity sold (although it fluctuates). The total profit was able to cover all the losses it suffered previously. Besides, it also indicates that consumers started to shift toward online shopping.
- According to the result, the salespeople mostly failed to achieve the target for furniture and clothing target. It is needed to review the target to determine if it is really achievable. Otherwise, more training would be required for the salespeople who are involved in promoting the furniture and clothing.
- Losses occurred from April 2018 to September 2018. Luckily, there was a high profit from October 2018 onwards, followed along with an increase in the quantity sold (although it fluctuates). The total profit was able to cover all the losses it suffered previously. Besides, it also indicates that consumers started to shift toward online shopping.
- A high number of products sold does not guarantee a high profit because the highest loss occurred in June 2018, but the quantity sold was the lowest in July 2018.
- All types of clothing made a profit. The top 3 best-selling sub-categories are under the category of clothing, and they are saree, handkerchief, and stole. The sellers can provide products that are complementary to these top 3 best-selling products to boost sales as there is a high chance that customers buy clothing products in pairs. For example, leggings and insoles.
- The sellers should avoid selling electronic games and focus more on selling printers and accessories because electronic games led to losses although the quantity of electronic games is higher than that of printers and accessories.