In the past, a company's reach was limited by the number of customers who could physically enter via a store's front doors. E-commerce now allows us to access customers all over the world. The growth of social media and the rise of internet usage have made it easier for e-commerce business owners to reach a wide new mix of customers.
To support the performance of an e-commerce business, past evaluation and analysis are needed to improve the performance and scope of a business.
This project will analyze an e-commerce business performance using SQL and data visualization by PowerBI to improve business performance by giving business recommendations at the end.
- Annual Customer Activity Growth
- Annual Product Category Quality
- Annual Payment Type Usage
The first step to analyze is ensuring the data is ready to be used. A local database was created by PostgreSQL to load the 8 datasets and make a data relationship diagram to ensure integrity within the database. Our datasets in a nutshell can be seen from the diagram below.
Description
- Average MAU: The average Monthly Active Users (MAU) per year
- Total New Cust: Total of new customers each year
- Total Cust Repeat Oder: Total customers who repeat orders in a year
From the graph of Average MAU, we can see that active users per year have increased rapidly from 2016 to 2017 and grew constantly until 2018. The same thing goes with total new customers which grew tremendously from under 1k to 52k. We can conclude that new customers have a big impact on active user growth.
Now from the order analysis, on average we can say each customer ordered just once.
Meanwhile, the total number of customers who repeated orders increased from 2016 (only 3 customers) to 2017 (1256 customers), but then decreased to 1167 in 2018. If we combine the customer and order analysis where 2017 is the top year for customer growth, it seems that some of our products last more than year such as furniture or houseware, so they don’t buy the same thing again. This decrease doesn't seem bad.
Description
- Total Revenue: Total revenue per year
- Top Product: Top product category name with the most transactions of the year
- Revenue of Top Product: Revenue of top product
- Total Canceled: Total canceled transactions
- Most Canceled Product: Product category name with the most cancellations of the year
- Total Product Canceled: Number of product categories with the most cancellations of the year
Due to many new customers especially since 2017, the revenue grew rapidly as well. If we look closer at the product category which gave high revenue, we got Health-Beauty as the top product in 2018, Bed-Bath-Table in 2017, and Furniture-Decor in 2016. Just as delivered orders are increasing, canceled orders are also increasing. As the graph shows, there is an increasing amount of canceled orders behind the rising revenue. Then, if we look at the product categories, the graph also shows the total product categories with the most canceled orders.
Here are the top and most canceled products for each year. Every year has a different dynamic of product interests. However, the interesting thing here is in 2018, the health and beauty category was the top product that gave the highest revenue and the most canceled product at the same time. This might happen because in 2018 the product category that dominated all transactions was health & beauty. Further analysis can be carried out to confirm this.
Description
Payment Type: Type of payment
Year 2016: Payment type usage in 2016
Year 2017: Payment type usage in 2017
Year 2018: Payment type usage in 2018
Total Usage: Total payment type usage in 2016-2018
Just the same important as customer and order analysis, payment type needs to be analyzed as well to support the transaction flow. From this pie chart, credit card was the top payment type in 2016-2018. Meanwhile, debit card is the least. The not-defined type of payment is only 3 in 3 years in total, not a big deal.
From the comparison chart above, the increase in payment type is linear to customer and order increases. Credit card was the most used payment type for three years in a row. However, the gap between credit cards and other types of payment is very large. This could indicate that our customers are consumer people or that they think the easiest payment is by using a credit card.
For this reason, for business recommendation, we can:
- Collaborate with banks to provide promotions and discounts to customers who use their credit cards
- Adding more other payment types to make it easier for customers to make transactions