DATA ANALYSIS PROJECTS

OVERVIEW

A SQL project I'm working on with a case study analyzing sales data belonging to an E-Commerce company. This project is part of a project training session provided by DQLab to improve relational database disclosure capabilities. I am working on this project using MySQL as a Relational Database and Data Preprocessing tool. For better visualization, I used IBM Cognos Analytics combined with SQL to get better analysis results.

The relationship between tables can be described in the following diagram:

SUMMARY

• There are approximately 1145 unique products divided into 12 categories. The highest number of unique products available by category are products that are in the ‘personal hygiene’ category with a number of unique products of approximately 434 products.
• There are 6.34% of paid transactions that are not sent to buyers. E-Commerce needs to consider evaluating the transaction system in an effort to increase the security of buyers and sellers
• E-Commerce’s performance has increased in the last 1 year based on the number of orders made by users. An increase in the number of orders means an increase in buying and selling activities carried out by users via this E-Commerce application. In fact, compared to 2019, the total orders placed by buyers per month in 2020 increased sharply. December is the month with the highest total orders. Companies must prepare various forms of promotion, for example through discounts, etc., in December of the following year.
• However, if we analyzed based on the transaction value, it increased dramatically in 2020 compared to 2019 and it continues to increase from month to month. Except in December until the beginning of 2020, in January the transaction value decreased but continued to increase from month to month.
• The Best Selling Product Category in 2019 and 2020 are the products from the ‘personal hygiene category. The second are the products from the ‘fresh food’ category. Compared to 2019, both categories of featured products have increased, both in sales volume and total price, in 2020.

RECOMMENDATION

E-Commerce store performance has been very good in the past year and needs to be maintained. There is no need to take policy changes too quickly. Various innovations can still be done in an effort to improve the performance of E-Commerce in the future. But the thing that should be a concern is the level of consumer security in dealing with transactions. There are still 6% of total customers whose orders have not been sent but have made payments. Even though the percentage of it is still small, companies still need to optimize consumer security so that they can reduce indications of fraudulent transactions

‍