Introduction

Any business must understand its business performance and develop business improvement ideas and activities. In this project, I’m going to analyze the business performance of an E-commerce company and try to find insights into its customer growth, product sales, and payment methods.

It has information on 100k orders from 2016 to 2018 made at multiple marketplaces in Brazil. Its features allow viewing orders from various dimensions: from order status, price, payment, and freight performance to customer location, product attributes, and reviews written by customers. I will be performing the analysis using Microsoft SQL Server and Power BI.

Objectives

 With the data that is provided, we would like to know about:

i) Overall customer activity growth from 2016 to 2018 by seeing average active users, new customers, customers with repeat orders, and average orders by customers.

ii ) Overall product category quality from 2016 to 2018 by seeing total revenue, total canceled orders, best-selling product category, and most canceled product category.

iii ) Overall payment type usage from 2016 to 2018 by seeing the favorite payment type all time, and the amount of usage for each type of payment by year.

About the Dataset

There are 8 datasets included in this project for the analysis namely:

1. customers

2. geolocation

3. order_items

4. order_payments

5. order_reviews

6. orders

7. products

8. sellers

Link to the data source 👉 https://bit.ly/3JXei0r

Methodology

1. Data cleaning and preprocessing: The raw data was first loaded into a SQL database and cleaned to ensure data quality and consistency. This involved handling duplicate, null, inconsistencies, and format errors using SQL queries.

2. Data modeling: Once the data was cleaned and preprocessed, it was modeled into tables, and relationships were established between them. This involved the use of SQL techniques such as creating views, stored procedures, and user-defined functions. The data model created served as the foundation for the analysis.

3. Data analysis: With the data model in place, exploratory data analysis was performed using SQL queries to understand the distribution and trends in the data. This step helped in identifying key features that would later be used in the statistical analysis.

4. Data visualization and reporting: The results obtained from the statistical analysis were then visualized and reported to the stakeholders using Power BI. This involved the creation of dashboards, reports, and visualizations that effectively communicated the findings of the analysis. The data visualization and reporting steps helped in providing insights and recommendations to the stakeholders.

Analysis

I) Overall customer activity growth from 2016 to 2018

1. Average active users per year

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

2. New customers per year 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

3. No.of customers with repeat orders 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

4. Average no.of orders by customers 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Overview:

• From the above diagram, we can see that there is a significant increase in the number of Avg Active Users as well as New Customers from 2016 to 2018.

• These numbers are comparatively smaller in 2016. This might be because the datasets may not contain enough information for 2016.

• 2017 seems to be the year with the most no.of repeat orders.

• There was a significant increase in the no.of repeat orders from 2016 to 2017 but the numbers seem to be lower in 2018 than that in 2017.

• There is only a slight difference in the numbers in 2017 and 2018 because the average order per customer is only 1.

======================================================================================================================

II) Product category quality analysis 2016–2018 

1. Total revenue per year 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

2. Total canceled orders per year 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

3. Best selling product category by year 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

4. Most canceled category 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Overview:

• From 2016 to 2018 there is a significant increase in revenue.

• 2018 is the year with the highest revenue.

• Best selling categories based on revenue in 2016, 2017, and 2018 are “Furniture Decor”, “Bed Bath Table”, and “Health & Beauty” respectively.

• Also, the “Health & Beauty” category is the best-selling as well as the category with the most cancellations in 2018.

======================================================================================================================

III) Payment Type Usage Analysis 

1. Favorite payment type 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

2. Top favorite payment type by year 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

3. Payment usage per year 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

Overview:

• Credit card is the most preferred method of payment throughout the years.

• Credit cards accounted for 74.92% of the transactions in 2018.

• Debit cards also saw a significant increase in usage from 2017 to 2018.

• Vouchers have a slight decline in usage in 2018 compared to 2017.

Conclusions

From the analysis, we can conclude that:

• In terms of Annual Customer Growth, there is an increase in the number of Average Active Users and New Customers from 2016 to 2018.

• On the other hand, 2018 saw a slight decline in the number of customers who purchased more than one product compared to 2017.

• 2018 is the year with the highest revenue(54.81%) followed by 2017(44.89%) and 2016(0.30%). The revenue has been increasing since 2016.

• “Health & Beauty” is the best-selling category in 2018 with a revenue of $ 8,85,191.12 followed by “Bed Bath Table” in 2017 ($ 5,90,280.44) and “Furniture Decor” in 2016 ($ 7,188.51).

• “Health & Beauty” is also the category with the most cancellations in 2018 followed by “Sports Leisure” in 2017 and “Toys” in 2016.

• Credit cards dominate the method of payment throughout the years: 74.92% in 2018, 72.74% in 2017, and 74.57% in 2016.

Suggestions

Based on the conclusions derived the following suggestions can be provided:

• Focus on retaining existing customers who purchase more than one product: As the number of customers who purchased more than one product declined in 2018, it is important to focus on retaining these customers by providing them with personalized offers and discounts.

• Explore other payment methods: Although credit cards dominate the method of payment, it would be worthwhile to explore other payment methods such as digital wallets and mobile payments to provide customers with more options.

• Invest in the "Health & Beauty" category: As it is the best-selling category in 2018, it would be a good idea to invest more in this category by introducing new products and promotions to drive sales further.

• Analyze the reasons for cancellations: As "Health & Beauty" is the category with the most cancellations, it would be important to analyze the reasons for cancellations and take corrective actions to improve customer satisfaction and reduce cancellations.

• Continue to focus on revenue growth: As the revenue has been increasing since 2016, it is important to continue focusing on revenue growth by introducing new products, optimizing pricing strategies, and expanding the customer base.