Analyzing-eCommerce-Business-Performance-with-SQL

Data Preparation

• ERD (Entitiy Relationship Diagram)

  

Annual Customer Activity Growth Analysis

• The master table of the average Monthly Active User (MAU), total new customers, the number of customers who make repeat orders, and the average order frequency for each year can be seen as follows.

  

• Most customers only make one purchase in 3 years as shown by the pie chart on the left (the average order frequency is the same, which is around number 1).

  

• Total new customers always increase every year, followed by an average monthly active user (MAU) which also increases every year.

  

• The growth in total new customers which is increasing every year cannot make customers make repeat orders which also increase every year. It can be seen that there was a decrease in repeat orders in 2018.

  

Annual Product Category Quality Analysis

• The master table of total revenue and total cancellations per year, the top product categories per year along with their revenue, and the categories that have the most cancellations each year can be seen as follows.

  

• Visualization chart

  

  From the several charts on the side, there are several insights obtained:

• Total revenue from 2016-2018 always increases.

• Total orders that have been canceled also always increase every year.

• Health Beauty products are the products that have the highest revenue in 2018 as well as the products with the highest cancellations in that year.

• Total revenue from each top product each year has also increased.

• Total cancel orders also increase every year.

• Total cancel orders in 2017 and 2018 only have a difference of 2 products.

Analysis of Annual Payment Type Usage

• The table regarding information on the total usage of each type of payment for each year can be seen as follows.

  

• Visualization chart

  

  From the chart on the side, some information is obtained:

• Every year from 2016-2018 the Credit Card payment type is the type of payment most often used by customers.

• Voucher payment types fluctuate in use. In 2016 to 2017 it has increased, but in 2017 to 2018 there has been a decrease in its use.

• The type of debit card payment has always increased more than 2 times compared to the previous year.

• There are payment types that are not clearly defined in 2018.

mini-project file link is Here