Portofolio Data Analysis for E-Commerce [Part-1] 🛍

17 min readJun 22, 2024

Pada kesempatan kali ini saya akan membagikan hasil pengerjaan project yang saya ambil melalui Website DQLab. Pada project kali ini saya diberikan pilihan untuk menyelesaikannya menggunakan beberapa tools seperti, excel, R, SQL dan lainnya.

Business Understanding

Dataset yang digunakan merupakan data dari DQLab Store yang merupakan salah satu E-Commerce yang menjual berbagai kebutuhan wanita dan pria, baik dari segi pakaian, aksesoris, makanan, vitamin dan juga minuman. Pada store ini pelanggan akan melakukan pembelian dan penjualan barang. Pada store ini juga pengguna bisa membeli barang dari pengguna lain yang berjualan. Untuk setiap pengguna bisa menjadi pembeli sekaligus penjual.

Dari penjelasan singkat diatas, berikut beberapa pertanyaan dari pihak DQLab Store yang diberikan pada saya, agar membantu mereka untuk melakukan analisa:

Berapa banyak kolom dan baris yang ada pada table Products, serta infokan juga pada kolom category terdapat berapa banyak jenisnya?
Berapa banyak kolom, baris serta variable yang berisi data amount dan juga data tanggal pada table Orders?
Buatkan summary transaksi bulanan yang dilakukan pada bulan September 2019, Januari 2020, Maret 2020, dan Mei 2020?
Buatkan juga summary transaksi yang mana pelanggan hanya membuat transaksi saja tanpa melakukan pembayaran, lalu totalkan juga transaksi yang tidak dikirim baik sudah dilakukan pembayaran ataupun belum?
Berapa banyak pengguna yang melakukan transaksi pada DQLab Store sebagai pembeli, penjual dan juga pernah bertransaksi sebagai keduanya?
Dari daftar user_id dan nama pengguna, mana saja yang merupakan 5 pembeli dengan dengan total pembelian terbesar (berdasarkan total harga barang setelah diskon)?
Dari daftar nama pengguna, mana saja pengguna yang tidak pernah menggunakan diskon ketika membeli barang yang mana merupakan 5 pembeli dengan transaksi terbanyak?
Dari daftar email pengguna, mana saja pengguna yang bertransaksi setidaknya 1 kali setiap bulan di tahun 2020 dengan rata-rata total amount per transaksi lebih dari Rp 1.000.000?
Dari daftar domain pada table users, carikan domain email dari penjual di DQLab Store (domain adalah nama unik setelah tanda @), biasanya menggambarkan nama organisasi dan imbuhan internet standar (seperti .com, .co.id dan lainnya)?
Dari daftar nama produk, manakah yang merupakan top 5 produk yang dibeli di bulan Desember 2019 berdasarkan total quantity?

Data Understanding

Terdapat 4 tabel yang disediakan oleh pihak DQLab Store:

— users, berisi detail kolom data pengguna:

user_id : ID pengguna
nama_user : nama pengguna
kodepos : kodepos alamat utama dari pengguna
email : email dari pengguna

— orders, berisi transaksi pembelian dari pembeli ke penjual.

order_id : ID transaksi
seller_id : ID dari pengguna yang menjual
buyer_id : ID dari pengguna yang membeli
kodepos : kodepos alamat pengirimian transaksi (bisa beda dengan alamat utama)
subtotal : total harga barang sebelum diskon
discount : diskon dari transaksi
total : total harga barang setelah dikurangi diskon, yang dibayarkan pembeli
created_at : tanggal transaksi
paid_at : tanggal dibayar
delivery_at : tanggal pengiriman

— order_details, berisi detail barang yang dibeli saat transaksi.

order_detail_id : ID table ini
order_id : ID dari transaksi
product_id : ID dari masing-masing produk transaksi
price : harga barang masing-masing produk
quantity : jumlah barang yang dibeli dari masing-masing produk

— products, berisi detail kolom data dari produk yang dijual:

product_id : ID produk
desc_product : nama produk
category : kategori produk
base_price : harga asli dari produk

Data Preparation

MySQL
Tools DBeaver (Salah satu tools pengolahan query SQL)

Data Cleansing

Setelah dilakukan pengecekan lebih lanjut mengenai data yang akan diolah, saya menemukan adanya nilai [NA] pada table Orders dengan jumlah 5.046 rows.
Namun pada kali ini saya tidak akan melakukan tahapan cleansing terlalu dalam karena tahapan cleansing biasanya dilakukan menggunakan Python dan bukannya SQL, setau saya… correct me if wrong 👐.

Exploratory Data Analysis (EDA)

Pada tahapan ini saya akan mencoba melakukan analisa dan menjawab business question yang sebelumnya diminta.

Berapa banyak kolom dan baris yang ada pada table Products, serta infokan juga pada kolom category terdapat berapa banyak jenisnya?

Untuk menjawab business question mengenai banyak kolom dan baris serta info banyak jenis dari data category pada table products, saya menggunakan beberapa tahapan yang dijelaskan pada code SQL dibawah ini:

-- Check Columns
SELECT *
FROM products;

-- Check Jumlah Baris
SELECT COUNT(*) jml_baris
FROM products;

-- Check Unik Jumlah Category
SELECT DISTINCT category
FROM products;

-- Check Jumlah NULL setiap columns
WITH banyak_null AS (
    SELECT COUNT(*) AS jml_null
    FROM products
    WHERE product_id IS NULL
      OR desc_product IS NULL
      OR category IS NULL
      OR base_price IS NULL
), banyak_not_null AS (
    SELECT COUNT(*) AS jml_not_null
    FROM products
    WHERE product_id IS NOT NULL
      OR desc_product IS NOT NULL
      OR category IS NOT NULL
      OR base_price IS NOT NULL
)
SELECT banyak_null.jml_null, banyak_not_null.jml_not_null
FROM banyak_null, banyak_not_null;

Berdasarkan 4 langkah yang saya lakukan saat ini, kita dapat mengetahui bahwa pada table products memiliki 4 kolom dengan banyak jumlah rows 1.145, dimana pada setiap rows ini memiliki category yang berbeda-beda dengan jumlah sebanyak 12 category yang terdaftar pada table products.

Pada table products juga tidak memiliki nilai NULL dengan dibuktikan berdasarkan pengecekan pada kolom pertama yaitu “jml_null” dengan hasil angka 0, sedangkan pada kolom kedua yaitu “jml_not_null” ini menjelaskan pada data table products banyaknya nilai yang tidak NULL pada table products.

Berapa banyak kolom, baris serta variable yang berisi data amount dan juga data tanggal pada table Orders?

Untuk menjawab business question selanjutnya mengenai banyak kolom, baris dan juga variable yang berisi data amount serta data tanggal, saat ini saya menggunakan code SQL seperti dibawah ini:

-- Mengitung jumlah kolom
SELECT COUNT(*) AS jml_columns
FROM information_schema.COLUMNS
WHERE table_schema = 'data_store' AND table_name = 'orders';

-- Check Jumlah Baris
SELECT COUNT(*) jml_baris
FROM orders;

-- Check Daftar Nama Columns
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE table_schema = 'data_store' AND table_name = 'orders';

-- Check Nilai NULL/NA
WITH banyak_null AS(
 SELECT COUNT(*) AS jml_null
 FROM orders
 WHERE buyer_id IS NULL
  OR created_at IS NULL
  OR delivery_at IS NULL
  OR discount IS NULL
  OR kodepos IS NULL
  OR order_id IS NULL
  OR paid_at IS NULL
  OR seller_id IS NULL
  OR subtotal IS NULL
  OR total IS NULL
), banyak_na AS(
 SELECT COUNT(*) AS jml_na
 FROM orders
 WHERE paid_at = 'NA'
)
SELECT jml_null, jml_na
FROM banyak_null, banyak_na;

-- Check Data berisi Amount
SELECT *
FROM orders
WHERE discount != 0
LIMIT 5;

Dari hasil 5 langkah diatas, saat ini kita bisa mendapatkan informasi bahwa pada table orders memiliki 10 kolom dengan jumlah row sebanyak 74.874. Pada table orders juga tidak memiliki nilai NULL namun memiliki nilai NA di dalamnya sebanyak 5.046.

Lalu selanjutnya kita dapat mengetahui bahwa terdapat 3 variable yang diisi data amount seperti subtotal, discount, dan total. Saat ini juga kita dapat mengetahui bahwa terdapat 3 variable juga yang menggunakan data tanggal pada table orders seperti created_at, paid_at, dan juga delivery_at.

Buatkan summary transaksi bulanan yang dilakukan pada bulan September 2019, Januari 2020, Maret 2020, dan Mei 2020?

Pada business question berikutnya untuk mengetahui summary dari transaksi bulanan yang dilakukan DQLab Store pada bulan September 2019, Januari 2020, Maret 2020, dan Mei 2020, kita dapat menggunakan query SQL seperti dibawah ini:

-- Check Table
SELECT *
FROM orders;

-- Check Transaksi per Bulan 
WITH september_2019 AS(
 SELECT COUNT(*) AS jml_transaksi_sep19
 FROM orders
 WHERE created_at BETWEEN '2019-09-01' AND '2019-09-30'
), november_2019 AS(
 SELECT COUNT(*) AS jml_transaksi_nov19
 FROM orders
 WHERE created_at BETWEEN '2019-11-01' AND '2019-11-30'
), januari_2020 AS(
 SELECT COUNT(*) AS jml_transaksi_jan20
 FROM orders
 WHERE created_at BETWEEN '2020-01-01' AND '2020-01-31'
), maret_2020 AS(
 SELECT COUNT(*) AS jml_transaksi_mar20
 FROM orders
 WHERE created_at BETWEEN '2020-03-01' AND '2020-03-31'
), mei_2020 AS(
 SELECT COUNT(*) AS jml_transaksi_mei20
 FROM orders
 WHERE created_at BETWEEN '2020-05-01' AND '2020-05-31'
)
SELECT
 jml_transaksi_sep19,
 jml_transaksi_nov19,
 jml_transaksi_jan20,
 jml_transaksi_mar20,
 jml_transaksi_mei20
FROM
 september_2019,
 november_2019,
 januari_2020,
 maret_2020,
 mei_2020;

Dari hasil data yang ditampilkan saat ini dapat kita lihat untuk nominal banyaknya transaksi yang dilakukan berdasarkan tahun dan bulan yang diminta pada business question sebelumnya.

Dimana pada bulan September 2019 transaksi yang terdata pada DQLab Store berjumlah 4.327, untuk bulan November 2019 sebanyak 7.162, pada bulan Januari 2020 sebanyak 5.062, untuk bulan Maret sebanyak 7.323, sedangkan pada bulan Mei 2020 sebanyak 10.026 transaksi.

Berdasarkan hasil pendataan transaksi, saat ini kita dapat mengetahui bahwa tingkat penjualan terbanyak ada pada bulan Mei 2020 di angka 10.026 transaksi dan transaksi terendah ada pada bulan September 2019 di angka 4.327 transaksi.

Buatkan juga summary transaksi yang mana pelanggan hanya membuat transaksi saja tanpa melakukan pembayaran, lalu totalkan juga transaksi yang tidak dikirim baik sudah dilakukan pembayaran ataupun belum?

Untuk menyelesaikan business question ini ada beberapa langkah yang saya coba, seperti mengidentifikasi isi data yang ada pada table orders terlebih dahulu, lalu menggunakan isi data ini sebagai kondisi sesuai dengan permintaan business question saat ini. Code SQL-nya sebagai berikut:

-- Check Transaksi
WITH transactions_not_pay AS(
 SELECT COUNT(*) AS jml_transactions_not_pay
 FROM orders
 WHERE paid_at = 'NA'
), transactions_not_deliver AS(
 SELECT COUNT(*) AS jml_transactions_not_deliver
 FROM orders
 WHERE delivery_at = 'NA'
)
SELECT
 jml_transactions_not_pay,
 jml_transactions_not_deliver
FROM
 transactions_not_pay,
 transactions_not_deliver;

Transaksi Tidak Bayar & Transaksi Bayar/Belum Namun tidak Dikirim

Dari hasil yang ada saat ini menjelaskan bahwa transaksi yang dilakukan tanpa membayar pada DQLab Store sebanyak 5.046 dan untuk transaksi yang tidak dikirim dengan melakukan pembayaran ataupun tidak sebanyak 9.790 transaksi.

Dari data yang dihasilkan ssperti pada gambar, terdapat kemungkinan untuk pelanggan yang melakukan transaksi tanpa membayar yang mana hanya melakukan check out akhir tanpa menyelesaikan tahapan pembayaran.

Lalu yang bisa menjadi evaluasi berikutnya adalah pada pengiriman barang yang sudah dilakukan pembayaran namun tidak dikirim juga, disini terdapat kemungkinan juga bahwa pengiriman tidak dilakukan karena alamat yang tidak dicantumkan ataupun tidak ada yang menerima paket pengiriman dari pihak DQLab Store sehingga barang kembali pada pihak toko.

Berapa banyak pengguna yang melakukan transaksi pada DQLab Store sebagai pembeli, penjual dan juga pernah bertransaksi sebagai keduanya?

Pada business question ini saya akan menyelesaikannya menggunkan CTE yang ada pada SQL, walaupun bisa juga menggunakan subquery namun jika kalian perhatikan kembali beberapa jawaban saya untuk artikel kali ini banyak menggunakan CTE.

Alasan saya banyak menggunakan CTE salah satu garis besarnya adalah mudah untuk dibaca dibandingkan subquery yang cukup membuat saya bingung ketika menulis sudah sampai pada kolom mana 😅 … berikut code SQL yang saya terapkan untuk menyelesaikan question ini:

-- Check Pengguna sebagai Buyer/Seller or Both of Them
WITH user_as_buyer AS(
 SELECT COUNT(DISTINCT buyer_id) AS jml_user_as_buyer
 FROM orders
), user_as_seller AS(
 SELECT COUNT(DISTINCT seller_id) AS jml_user_as_seller
 FROM orders
), user_as_seller_and_buyer AS(
 SELECT COUNT(DISTINCT seller_id) AS jml_user_as_seller_and_buyer
 FROM orders
 WHERE seller_id IN (
  SELECT buyer_id
  FROM orders
 )
)
SELECT
 jml_user_as_buyer,
 jml_user_as_seller,
 jml_user_as_seller_and_buyer
FROM
 user_as_buyer,
 user_as_seller,
 user_as_seller_and_buyer;

Jumlah Transaksi Pelanggan dari Pembeli, Penjual & Keduanya

Berdasarkan hasil data yang ditampilkan pada gambar diatas, menunjukan bahwa terdapat 17.877 pengguna yang pernah melakukan transaksi pada DQLab Store sebagai pembeli. Pada kolom kedua menjelaskan juga terdapat 69 pengguna yang pernah melakukan transaksi sebagai penjual.

Sedangkan pada kolom terakhir menunjukkan bahwa terdapat 69 pengguna yang pernah melakukan transaksi baik sebagai pembeli ataupun sebagai penjual pada DQLab Store.

Dari daftar user_id dan nama pengguna, mana saja yang merupakan 5 pembeli dengan dengan total pembelian terbesar (berdasarkan total harga barang setelah diskon)?

Business question berikutnya adalah mencari 5 pembeli dengan total pembelian terbesar dengan note “berdasarkan total harga barang setelah diskon”, untuk menyelesaikan question ini dapat kita gunakan code SQL dibawah ini:

-- Check TOP Total Penjualan
SELECT
 o.buyer_id,
 u.nama_user,
 SUM(total) AS total_pembelian
FROM orders AS o
JOIN users AS u ON o.buyer_id = u.user_id
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 5;

Dari gambar diatas menunjukkan TOP 5 Transaksi teratas dengan total pembelian terbesar berdasarkan total harga barang setelah diskon. Dimana tempat pertama ditempati oleh Jaga Puspasari dengan total pembelian sebesar Rp 54.102.250, lalu setelahnya ditempat kedua terdapat R.A. Yulia Padmasari, S.I.Kom dengan total pembelian sebesar Rp 52.743.200.

Sedangkan pada tempat ketiga terdapat Septi Melani, S.Ked dengan total pembelian mencapai Rp 49.033.000 dan ditempat terakhir terdapat Kartika Habibi dengan total pembelian mencapai Rp 48.868.000.

Dari daftar nama pengguna, mana saja pengguna yang tidak pernah menggunakan diskon ketika membeli barang yang mana merupakan 5 pembeli dengan frekuensi transaksi terbanyak?

Jika pada business question sebelumnya saya diminta untuk melakukan pengecekan TOP 5 Transaksi teratas berdasarkan harga barang setelah diskon, untuk kali ini saya diminta melakukan pengecekan TOP 5 Frekuensi Transaksi teratas juga.

Namun perbedaannya disini adalah saya diminta untuk mencari frekuensi transaksi teratas dari pelanggan yang tidak pernah menggunakan diskon ketika melakukan pembelian barang pada DQLab Store. Untuk code SQL yang saya gunakan sebagai berikut:

-- Check Frequency
SELECT
 o.buyer_id,
 u.nama_user,
 o.discount,
 COUNT(o.order_id) AS jumlah_transaksi
FROM orders AS o
JOIN users AS u ON o.buyer_id = u.user_id
WHERE o.discount = 0
GROUP BY 1, 2, 3
ORDER BY 4 DESC, 2
LIMIT 5;

Frekuensi Transaksi Pelanggan Tidak Menggunakan Diskon

Dari hasil yang saya dapat saat ini untuk TOP 5 Frekuensi Transaksi yang dilakukan oleh pelanggan tanpa menggunakan diskon pada tempat pertama terdapat Yessi Wibisono dengan jumlah transaksi sebanyak 13, lalu pada tempat kedua terdapat Drs. Pandu Mansur, M.TI. dengan jumlah transaksi sebanyak 12.

Pada tempat ketiga terdapat Umay Latupono dengan jumlah transaksi sebanyak 12 juga, lalu berikutnya ditempat keempat terdapat Bakiono Zulaika dengan jumlah transaksi sebanyak 11 dan pada tempat terakhir ditempati oleh Cakrawangsa Habibi dengan jumlah transaksi sebanyak 11 juga.

Dari daftar email pengguna, mana saja pengguna yang bertransaksi setidaknya 1 kali setiap bulan di tahun 2020 dengan rata-rata total amount per transaksi lebih dari Rp 1.000.000?

Seperti yang diminta pada business question saat ini saya akan melakukan pencarian data mengenai pengguna yang melakukan transaksi setidaknya 1 kali setiap bulan pada tahun 2020 yang mana memiliki rata-rata amount dari masing-masing transaksi lebih dari Rp 1.000.000.

Pada code SQL dibawah ini terdapat 7 langkah yang saya buat untuk memastikan data yang saya dapat sesuai dengan yang diminta oleh pihak DQLab Store:

-- Jumlah User yang melakukan Transaksi
SELECT COUNT(*) jml_user_transaksi
FROM (
 SELECT
  buyer_id,
  MONTH(created_at) AS month,
  COUNT(*) AS jml_transaksi
 FROM orders
 WHERE YEAR(created_at) = 2020
 GROUP BY 1, 2
) AS total_user_transaksi;

Jumlah User Melakukan Transaksi Lebih dari 1 kali

-- Menghitung jumlah transaksi per bulan pada tahun 2020
SELECT
 buyer_id,
 MONTH(created_at) AS month,
 COUNT(*) AS jml_transaksi
FROM orders
WHERE YEAR(created_at) = 2020
GROUP BY 1, 2
ORDER BY 3 DESC;

-- Periksa apakah pengguna bertransaksi setidaknya sekali setiap tahun
-- Sub Step 1 [Periksa 2020 ini dimulai dari bulan apa sampai berakhir pada bulan apa]
SELECT DISTINCT created_at
FROM orders
WHERE YEAR(created_at) = 2020
ORDER BY 1;

Sub Step 1 - Check Tahun 2020 dimulai dari Bulan mana dan berakhir dimana

-- Sub Step 2 [Setelah mengetahui awal bulan dan akhir bulan, selanjutnya melakukan pengecekan berdasarkan banyak bulan yang ada]
SELECT
 buyer_id, 
 COUNT(DISTINCT MONTH(created_at)) AS month_with_transaction
FROM orders
WHERE YEAR(created_at) = 2020
GROUP BY 1
HAVING COUNT(DISTINCT MONTH(created_at)) = 5;

Sub Step 2 - Check Transaksi yang dilakukan berdasarkan Bulan

-- Banyak User dengan AVG Lebih dari Rp 1.000.000
SELECT
 COUNT(*) AS jml_user
FROM (
 SELECT
  buyer_id,
  AVG(total) AS avg_total_amount
 FROM orders
 WHERE YEAR(created_at) = 2020
 GROUP BY 1
 HAVING avg_total_amount > 1000000
) AS banyak_user;

Banyak User dengan Rata-Rata lebih dari Rp 1.000.000

-- Menghitung Rata-rata Total Amount per Transaksi
SELECT
 buyer_id,
 AVG(total) AS avg_total_amount
FROM orders
WHERE YEAR(created_at) = 2020
GROUP BY 1
HAVING avg_total_amount > 1000000
ORDER BY 2 DESC;

# Menggabungkan semua langkah
SELECT
 o.buyer_id,
 u.email,
 ROUND(AVG(o.total), 2) AS avg_total_amount,
 COUNT(DISTINCT MONTH(o.created_at)) AS month_with_transaction
FROM orders AS o
JOIN users AS u ON o.buyer_id = u.user_id
WHERE YEAR(o.created_at) = 2020
GROUP BY 1, 2
HAVING 
 AVG(o.total) > 1000000 AND
 COUNT(DISTINCT MONTH(o.created_at)) = 5
ORDER BY 3 DESC;

-- Filter Pengguna Berdasarkan Email yang Diberikan
SELECT
 o.buyer_id,
 u.email,
 ROUND(AVG(o.total), 2) AS avg_total_amount,
 COUNT(DISTINCT MONTH(o.created_at)) AS month_with_transaction
FROM orders AS o
JOIN users AS u ON o.buyer_id = u.user_id
WHERE 
 YEAR(o.created_at) = 2020 AND
 u.email IN ('ragustina@pd.gov', 'fnainggolan@hotmail.com', 'lputra@pd.gov', 'bzulkarnain@yahoo.com', 'dyulianti@gmail.com')
GROUP BY 1, 2
HAVING 
 AVG(o.total) > 1000000 AND
 COUNT(DISTINCT MONTH(o.created_at)) = 5
ORDER BY 3 DESC;

Dari hasil query yang di dapat saat ini kita dapat mengetahui bahwa untuk pelanggan yang melakukan transaksi lebih dari 1 kali setiap bulan pada tahun 2020 sebanyak 34.315 pelanggan, ini dapat dibuktikan dengan adanya hasil dari jumlah transaksi yang ada pada langkah kedua dimana pada kolom ketiga menunjukkan banyaknya transaksi yang dilakukan oleh setiap pelanggan lebih dari 1 kali.

Sedangkan jika kita perhatikan kembali pada langkah ketiga disini saya membaginya kembali menjadi 2 sub step, yang mana pada langkah pertama saya melakukan pengecekan terlebih dahulu pada data table dimulai dari bulan apa, hingga berakhir pada bulan apa pada tahun 2020. Hal ini dapat membantu saya menyesuaikan proses filtering yang akan dilakukan pada langkah selanjutnya, dan memang hasil yang ditunjukkan pada query menjelaskan bahwa untuk tahun 2020 dimulai dari bulan Januari hingga bulan Mei.

Lalu dilanjut pada langkah berikutnya setelah saya mengetahui awal bulan dan akhir bulan pada tahun 2020 ini, saya melakukan pengecekan kembali berdasarkan banyaknya bulan yang tertera pada sub step pertama sebelumnya dan jika kita lihat hasilnya menunjukkan bahwa terdapat 42 pengguna yang melakukan transaksi pada 5 bulan berbeda tersebut.

Untuk langkah keempat berdasarkan informasi yang ada pada data DQLab Store terdapat 13.248 pelanggan yang memiliki total rata-rata amount lebih dari Rp 1.000.000 untuk transaksinya, dibuktikan pada langkah kelima dimana kita disajikan dengan beberapa informasi dari ID pengguna yang memiliki rata-rata lebih dari Rp 1.000.000, sebagai contoh ada pada ID 12854 dengan total rata-rata amount sebesar Rp 29.143.000 dan pada ID 10028 dengan total rata-rata amount sebesar Rp 27.136.000.

Selanjutnya ada pada langkah keenam dimana saya akan menggabungkan semua informasi yang sebelumnya didapatkan menjadi 1 table dimana memiliki ID, email, total rata-rata dan juga 5 bulan transaksi berbeda pada tahun 2020. Sebagai contoh disini terdapat ID 11195 dengan email fnainggolan@hotmail.com yang memiliki total rata-rata amount sebanyak Rp 5.867.500 transaksi pada 5 bulan berbeda pada tahun 2020.

Sedangkan pada langkah terakhir disini saya melakukan filtering untuk 5 email berbeda yang ingin saya cari jumlah rata-ratanya diatas Rp 1.000.000 dengan transaksi di 5 bulan yang berbeda pada tahun 2020, sehingga hasil yang ditunjukkan pada data saat ini menjelaskan bahwa hanya terdapat 3 email saja yang memenuhi kualifikasi dari aturan yang dibuat sebelumnya. Seperti pada email fnainggolan@hotmail.com dengan jumlah total rata-rata amount sebesar Rp 5.867.500, pada urutan kedua ada pada email bzulkarnain@yahoo.com dengan total rata-rata amount sebesar Rp 5.310.480 dan pada urutan ketiga ditempati oleh email dyulianti@gmail.com dengan total rata-rata amount sebesar Rp 4.900.920.

Dari daftar domain pada table users, carikan domain email dari penjual di DQLab Store, “domain adalah nama unik setelah tanda @”, biasanya menggambarkan nama organisasi dan imbuhan internet standar (seperti .com, .co.id dan lainnya)?

Pada business question kali ini saya akan menyelesaikannya dengan 3 langkah seperti dibawah ini:

-- Langkah 1: Ekstrak domain dari alamat email
-- Langkah 2: Dapatkan domain penjual
SELECT
 SUBSTRING_INDEX(u.email, '@', -1) AS domain,
 COUNT(user_id) AS jml_pengguna_seller
FROM orders AS o
JOIN users AS u ON o.buyer_id = u.user_id
WHERE o.buyer_id IN (
 SELECT
  seller_id
 FROM orders
)
GROUP BY 1
ORDER BY 2 DESC;

-- Langkah 3: Filter domain yang diberikan
SELECT
 SUBSTRING_INDEX(u.email, '@', -1) AS domain,
 COUNT(user_id) AS jml_pengguna_seller
FROM orders AS o
JOIN users AS u ON o.buyer_id = u.user_id
WHERE o.buyer_id IN (
 SELECT
  seller_id
 FROM orders
) AND
SUBSTRING_INDEX(u.email, '@', -1) IN ('cv.web.id', 'cv.com', 'cv.net.id', 'pt.net.id', 'ud.co.id')
GROUP BY 1
ORDER BY 2 DESC;

Berdasarkan data yang diperoleh saat ini menjelaskan bahwa terdapat 27 domain dengan jumlah yang berbeda beda dimana pada tempat pertama ada pada domain gmail.com dengan jumlah pengguna seller sebanyak 64 orang dan ditempat terendah ada pada domain pd.my.id dengan jumlah pengguna seller sebanya 1 orang.

Jika kita melakukan filtering berdasarkan domain yang ditentukan seperti pada langkah ketiga yang mana terdapat 5 domain berbeda pada data DQLab Store guna mencari email dari seller pada toko ini, maka kita saat ini mendapatkan informasi bahwa hanya terdapat 2 domain yang tercantum seperti pt.net.id dengan jumlah pengguna seller sebanyak 13 orang dan domain cv.web.id dengan jumlah pengguna seller sebanyak 9 orang.

Dari daftar nama produk, manakah yang merupakan top 5 produk yang dibeli di bulan Desember 2019 berdasarkan total quantity?

Untuk business question terakhir saat ini saya diminta untuk menginformasikan TOP 5 Products yang dibeli pada bulan Desember 2019 berdasarkan banyaknya total quantity yang ada pada data DQLab Store. Maka untuk menyelesaikan question ini saya menggunakan query sebagai berikut:

-- Langkah 1: Gabungkan tabel `order_details` dan `orders`
-- Langkah 2: Hitung total quantity untuk setiap produk di bulan Desember 2019
-- Langkah 3: Dapatkan top 5 produk berdasarkan total quantity
SELECT
 p.desc_product,
 SUM(od.quantity) AS total_quantity
FROM order_details AS od
JOIN orders AS o ON od.order_id = o.order_id
JOIN products AS p ON od.product_id = p.product_id
WHERE o.created_at BETWEEN '2019-12-01' AND '2019-12-31'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

TOP 5 Products berdasarkan banyak Quantity

Dari hasil yang ditampilkan pada gambar saat ini kita dapat mengetahui bahwa terdapat TOP 5 Products dengan total quantity terbanyak, dimana pada tempat pertama ditempati oleh Product QUEEN CEFA BRACELET LEATHER dengan total item terjual sebanyak 2.550, lalu pada urutan kedua ditempati oleh Product SHEW SKIRTS BREE dengan total item terjual sebanyak 1.423.

Sedangkan pada urutan ketiga ada pada Product ANNA FAITH LEGGING GLOSSY dengan total item terjual sebanyak 1.323, pada tempat keempat ada pada Product Cdr Vitamin C 10’S dimana total item yang dijual sebanyak 1.242, lalu pada urutan terakhir ditempati oleh Product RIDER CELANA DEWASA SPANDEX ANTI BAKTERI R325BW dengan total item terjual sebanyak 1.186.

Kesimpulan Analisa

Berdasarkan tahapan EDA yang sudah dilakukan sebelumnya, saat ini saya mendapatkan beberapa poin kesimpulan seperti dibawah ini:

Dalam analisis data DQLab Store, saya menemukan beberapa hal penting. Tabel products berisi 1.145 produk dari 12 kategori, dan tidak ada nilai NULL di dalamnya, menunjukkan data yang lengkap dan rapi.
Pada tabel orders dengan 74.874 transaksi, tidak ada nilai NULL, tetapi ada 5.046 entri 'NA'. Saya juga menemukan bahwa transaksi terbanyak terjadi pada Mei 2020 dengan 10.026 transaksi, sementara transaksi terendah terjadi pada September 2019 dengan 4.327 transaksi.
Saya mengidentifikasi masalah pada proses transaksi dan pengiriman: 5.046 transaksi tanpa pembayaran dan 9.790 transaksi tidak dikirim. Ini perlu perhatian khusus.
Ada 17.877 pengguna yang berbelanja di DQLab Store, dan 69 di antaranya juga berperan sebagai penjual. Dari segi pembelian, Jaga Puspasari memimpin dengan total pembelian sebesar Rp 54.102.250. Yessi Wibisono memiliki transaksi terbanyak tanpa diskon, yaitu 13 transaksi.
Sebanyak 34.315 pelanggan melakukan lebih dari satu transaksi setiap bulan pada tahun 2020, dan 42 pelanggan bertransaksi pada lima bulan berbeda. Ada 13.248 pelanggan dengan rata-rata total pembelian lebih dari Rp 1.000.000.
Domain email gmail.com paling banyak digunakan oleh penjual, dengan 64 pengguna. Produk terlaris adalah QUEEN CEFA BRACELET LEATHER dengan 2.550 item terjual.

Temuan ini memberikan wawasan penting untuk memperbaiki proses pembayaran dan pengiriman serta meningkatkan strategi bisnis DQLab Store kedepannya.

Penutup

Demikian artikel ini dibuat sebagai bentuk portofolio yang digunakan untuk memperdalam pemahaman dalam bidang Analisa Data yang ada pada industri.

Kritik dan saran sangat saya harapkan sebagai masukan yang akan membantu saya berkembang lebih baik lagi kedepannya. Silakan berikan tepuk tangan meriah apabila kalian suka dan silakan berikan komentar apabila kalian memiliki masukan atau berdiskusi mengenai project pada artikel ini.

Terima kasih telah membaca sampai akhir artikel dan nantikan [Part-2] untuk project ini, sampai jumpa kembali 😁