Analyzing eCommerce Business Performance with SQL

This repository represents a set of query and my analysis of eCommerce database in pdf file. I've stored all queries in one file, there are end-to-end query processes of this project. The purpose of this project is analyzing eCommerce business performance which the output is just calculation of various important KPIs/metrics and visualization also the interpretation of each calculation.

Data

The database has 8 datasets which contain information of orders, order_items, order_payments, order_reviews, customers, product, seller and geolocation. However, I didn't use all of it's datasets; depends on metrics that I look for.

Data Source

If any of you curious about the database or wanna to try by yourself, feel free to access the database from here.

Tools

I've used various tools on this project. Since the objective of this project was analyzing with SQL so I've used postgreSQL as my RDBMS platform. Then for visualization I've used Jupyter Notebook with python programming language.

Contents

Data Preparation

Including the processes of generate tables, import it's data/attributes/values, define primary and foreign key and generate ERD (Entity Relationship Diagram).

Annual Customer Activity Growth Analysis

Including the calculation processes of MAU (Monthly Active User), new customers and repeat order customers.

Annual Product Category Quality Analysis

Including the calculation processes of top product category, top product revenue, most canceled product, most canceled product's order numbers, and total canceled customers. All of revenue's currency is set as '$'.

Analysis of Annual Payment Type Usage

Including the calculation processes of customer's payment type favorite.

NOTE

I presented the result of each progress in Bahasa Indonesia.