Data Analysis for E-Commerce

UN Store is e-commerce where business processes occur online. Every user can buy goods from the seller.

Data Used

There are 3 tables available:

1. User , contains user details. Consist of:

a. User_id,

b. Name,

c. Postal Code,

d. Email

2. Product, contains detailed data of the products sold. Consist of:

a. Product_id,

b. Product_name,

c. Price

3. Order_detail, contains purchase transaction details. Consist of:

a. Order_id,

b. User_id,

c. Product_id,

d. Sub_total,

e. Quantity,

f. Total,

g. Order_date,

h. Country

1. 10 biggest transactions user 1000

The code below displays 10 transactions from purchases from users with user_id 1000 and sorted from the largest transaction value.

Output

2. Transactions per month

order_date field has a date format, we can get the year month part of the date format by using the EXTRACT(YEAR_MONTH) function. To generate the number of transactions (number_of_order) we use the count function and the sum function to calculate the total value (sales).

Output

3. User with largest transaction average in June 2011

To calculate the number of transactions (number_of_order) we use the count function and use the average function to find the average total (average) in June 2011

Output

The results above show 2 buyers with the largest average transaction value who transacted at least 2 times in June 2011.

4. Big deal in December 2010

Display name, total and order_date using the where function in December and the total is above 1000.

Output

The results above show all transaction values ​​of at least 1000 in December 2010.

5. Best Selling Products in 2011

Use the sum function to add quantity (total_quantity) and price (total_price) values.

Output

The results above show the 5 categories with the highest total quantity in 2011.

6. Customers Who Have Never Shopped

Use the not-in function to find customers who have never shopped in the order_detail field.

Output

7. Shopping Transactions with Shopping Lists of more than 2

Use the count function to count the number of quantities (number_of_detail) and the result is more than 2.

Output

Summary

1. In a few months, user =1000 transact with big results.
2. The largest number of transactions is in May and the smallest is in July.
3. There are 2 users with the largest average transaction in June.
4. In December there are 5 users with transactions above 1000.
5. 5 best selling products in 2011 with the highest total quantity reaching 10266.
6. There are about 25 users who never shop.
7. There are 10 users with a shopping list of more than 2.

That’s the portfolio I made, if there are criticisms and suggestions just leave them in the comments column or visit my email dwipuspita1240@gmail.com.

Dataset

Full Code