Data Analysis for E-Commerce Challenge



Project Summary

Improving the Understanding of SQL function to help access, read, manipulate, and analyze the data stored in the database and generate useful insights to drive an informed decision-making process, and this 3 main things that I did in this project :

  1. Created SQL query based using aggregate functions, date format, joins, CTE, and window function.
  2. Creating EDA, Analyze Monthly Sales Growth and creating Customer Analytics
  3. Created the insights for the project

Project Background & Dataset

Project Background

Provide data and analysis regarding the condition of the company, including sales growth, customer growth,transaction growth, actual active customer, Explore and get the hands on Structured Query Language (SQL). As a data analyst, we are expected to use SQL to formating and cleaning the data, joining the data, using CTE and Window function also generating useful insights from the data.

Dataset :

The dataset used is data from the DQLab Store which is an e-commerce where buyers and sellers meet each other. Users can buy goods from other users who sell. Each user can be both a buyer and a seller. There are 4 tables used in this project:

The tables that will be used in this project are:

  1. orders table: Contains data related to sales transactions from Jan 2019 — Mare 2020
  2. order_details table: Contains data related to detail product and price each product (Apr — Jun 2004)
  3. users table: Contains profile data of consumers
  4. Products table: Contains profile data of detail product, category and price

1.The 10 highest transactions of users 12476



The highest transaction of user 12476 is transaction on 23 dec with 12Mio Value

2.Transactions per month in 2020



Mei 2020 is the month with higest total transaction.

3. Buyer with the highest average transaction in January 2020



buyer_id 11140 is the highest value transaction in January 2020

4. Big transaction in December 2019

Show all transactions with a minimum value of 20,000,000 in December 2019. Show table of nama_pembeli, nilai transaksi and tanggal_transaksi, sort alphabetically by buyer.



5. Best seller product category in 2020

Show 5 categories with the highest total quantity in 2020, only for transactions that have been sent to buyers. Show table of category, total_quantity, and total_price.



6. Buyer with high value

Show buyers who have transacted more than 5 times and each transaction more than 2,000,000. Show table of nama_pembeli, jumlah_transaksi, total_nilai_transaksi, and min_nilai_transaksi, sort by the highest total_nilai_transaksi.



7. Dropshipper

Show buyers with 10 or more transactions whose shipping address is different for each transaction. Show table of nama_pembeli, jumlah_transaksi, distinct_kodepos, total_nilai_transaksi, and avg_nilai_transaksi, sort by the highest number of transactions.



8.Offline reseller

Show buyers with 8 or more transactions whose shipping address is the same as the main shipping address and the average total quantity per transaction is more than 10. Show table of nama_pembeli, jumlah_transaksi, total_nilai_transaksi, avg_nilai_transaksi, and avg_quantity_per_transaksi, sort by the highest total_nilai_transaksi.



9. Buyer and seller at once

Show the seller who has transacted as a buyer at least 7 times. Show table of nama_pengguna, jumlah_transaksi_beli, and jumlah_transaksi_jual, sort alphabetically by nama_pengguna



10. Transaction time

Show the average time from transaction made to payment. Show table of tahun_bulan, jumlah_transaksi, avg_lama_dibayar, min_lama_dibayar, and max_lama_dibayar, group by month and sort by tahun_bulan.



Conclusion

Based on the data that we have obtained through SQL queries, we can conclude that:

  1. xyz.com's performance decreased significantly in the 2nd quarter, as seen from the value of sales and revenue which dropped by 20% and 24%,
  2. the acquisition of new customers is also not very good, and slightly decreased compared to the previous quarter.
  3. The interest of new customers to shop at xyz.com is still lacking, only about 56% have made transactions. It is recommended that the Product team need to study customer behavior and make product improvements, so that the conversion rate (register to transaction) can increase.
  4. Product categories S18 and S24 contribute about 50% of total orders and 60% of total sales, so xyz.com should focus on developing categories S18 and S24.
  5. xyz.com's customer retention rate is also very low at only 24%, meaning that many customers who have transacted in the 1st quarter do not return to order in the 2nd quarter (no repeat orders).
  6. xyz.com experienced negative growth in the 2nd quarter and needs to make a lot of improvements both in terms of products and marketing business, if you want to achieve the target and have positive growth in the 3rd quarter. The low retention rate and conversion rate can be an early diagnosis that customers are not interested/unsatisfied/disappointed in shopping at xyz.com.