Data Analysis for E-Commerce
Project ini merupakan salah satu modul project-based yang telah saya selesaikan di DQLab Academy. Analisis pada project ini akan dilakukan menggunakan SQL dengan RDBMS MySQL. Klik di sini untuk melihat Full SQL script. Please feel free to leave any comments, constructive feedback is happily welcomed!
Dataset Brief
Dataset yang digunakan berisi transaksi dari Januari 2019 hingga Mei 2020. Dengan jumlah row tabel users sebanyak 18,128, tabel orders sebanyak 74,874, tabel order_details sebanyak 187,452, dan tabel products sebanyak 1,145.
Dataset yang digunakan merupakan data dari DQLab Store yang merupakan e-commerce dimana pembeli dan penjual saling bertemu. Pengguna bisa membeli barang dari pengguna lain yang berjualan. Setiap pengguna bisa menjadi pembeli sekaligus penjual. Jika ingin mengunduh dataset, silahkan klik di sini.
Jumlah Transaksi per Bulan
Untuk mendapatkan jumlah transaksi per bulan, dilakukan grouping berdasarkan tahun-bulan transaksi dilakukan. Agar dapat mengambil tahun dan bulan saja, gunakan fungsi DATE_FORMAT(field, ‘%Y-%m’).
SELECT
DATE_FORMAT(created_at, '%Y-%m') AS Bulan,
count(1) AS jumlah_transaksi
FROM
orders
GROUP BY
1
ORDER BY
1;
Status Transaksi
- Jumlah transaksi yang tidak dibayar:
SELECT count(1) AS transaksi_tidak_dibayar FROM orders WHERE paid_at = 'NA';
- Jumlah transaksi yang sudah dibayar tapi tidak dikirim:
SELECT count(1) AS transaksi_dibayar_tidak_dikirim FROM orders WHERE paid_at != 'NA' AND delivery_at = 'NA';
- Jumlah transaksi yang tidak dikirim, baik yang sudah dibayar maupun belum:
SELECT count(1) AS transaksi_tidak_dikirim FROM orders WHERE delivery_at = 'NA' AND (paid_at != 'NA' OR paid_at = 'NA');
- Jumlah transaksi yang dikirim pada hari yang sama dengan tanggal dibayar:
SELECT count(1) AS jumlah_transaksi FROM orders WHERE paid_at = delivery_at;
Pengguna Bertransaksi
- Total seluruh pengguna:
SELECT count(DISTINCT user_id) AS jumlah_seluruh_pengguna FROM users;
- Total pengguna yang pernah bertransaksi sebagai pembeli:
SELECT count(DISTINCT buyer_id) AS jumlah_buyer FROM orders;
- Total pengguna yang pernah bertransaksi sebagai penjual:
SELECT count(DISTINCT seller_id) AS jumlah_seller FROM orders;
- Total pengguna yang pernah bertransaksi sebagai pembeli dan pernah sebagai penjual:
SELECT count(DISTINCT seller_id) AS buyer_and_seller FROM orders WHERE seller_id IN (SELECT buyer_id FROM orders);
- Total pengguna yang tidak pernah bertransaksi sebagai pembeli maupun penjual:
SELECT count(DISTINCT user_id) AS pengguna_tidak_pernah_trx FROM users
WHERE
user_id
NOT IN
(
SELECT buyer_id FROM orders UNION SELECT seller_id FROM orders
);
Atau:
SELECT count(DISTINCT user_id) AS pengguna_tidak_pernah_trx FROM users
WHERE
user_id
NOT IN
(
SELECT buyer_id FROM orders
)
AND
user_id
NOT IN
(
SELECT seller_id FROM orders
);
Top Buyer All Time
Berikut adalah query untuk mendapatkan siapa pembeli yang merupakan 5 pembeli dengan dengan total pembelian terbesar (berdasarkan total harga barang setelah diskon).
SELECT
buyer_id,
nama_user,
sum(total) AS total_transaksi
FROM
orders o
JOIN
users u
ON o.buyer_id = u.user_id
GROUP BY
1,2
ORDER BY
3 DESC
LIMIT
5;
Frequent Buyer
Berikut adalah query untuk mendapatkan siapa pembeli yang tidak pernah menggunakan diskon ketika membeli barang dan merupakan 5 pembeli dengan transaksi terbanyak.
SELECT
buyer_id,
nama_user,
count(order_id) AS jumlah_transaksi
FROM
orders o
JOIN
users u
ON o.buyer_id = u.user_id
WHERE
discount = 0
GROUP BY
1,2
ORDER BY
3 DESC, 2
LIMIT
5;
Big Frequent Buyer 2020
Berikut adalah query untuk mendapatkan siapa pembeli yang bertransaksi setidaknya 1 kali setiap bulan di tahun 2020 dengan rata-rata total amount per transaksi lebih dari 1 Juta. Untuk mendapatkan output tersebut, saya melakukan beberapa subquery. Berikut adalah fungsi dari masing-masing subquery:
- Subquery trx: untuk mendapatkan pembeli dengan rata-rata total amount per transaksi lebih dari 1 Juta.
- Subquery months: untuk mendapatkan pembeli yang bertransaksi setidaknya 1 kali setiap bulan di tahun 2020. Karena data yang ada mencatat transaksi hingga bulan ke-5, maka maksimal month countnya adalah 5.
- Subquery bfq: untuk mengambil hasil inner join dari subquery trx dan months yang nantinya akan di-join dengan tabel user untuk mendapatkan emailnya.
SELECT
buyer_id,
email,
rata_rata,
month_count
FROM
(
SELECT
trx.buyer_id,
rata_rata,
jumlah_order,
month_count
FROM
(
SELECT
buyer_id,
round(avg(total),2) AS rata_rata
FROM
orders
WHERE
DATE_FORMAT(created_at, '%Y') = '2020'
GROUP BY
1
HAVING
rata_rata > 1000000
ORDER BY
1
) AS trx
JOIN
(
SELECT
buyer_id,
count(order_id) AS jumlah_order,
count(DISTINCT DATE_FORMAT(created_at, '%m')) AS month_count
FROM
orders
WHERE
DATE_FORMAT(created_at, '%Y') = '2020'
GROUP BY
1
HAVING
month_count >= 5
AND
jumlah_order >= month_count
ORDER BY
1
) AS months
ON trx.buyer_id = months.buyer_id
) AS bfq
JOIN
users
ON buyer_id = user_id;
Domain Email dari Penjual
Untuk mendapatkan domain email apa saja yang dimiliki penjual di DQLab store, saya menggunakan fungsi instr(field, ‘karakter yang dicari’) dan substr(field, start position).
SELECT
DISTINCT substr(email, instr(email, '@') + 1) AS domain_email,
count(user_id) AS jumlah_pengguna_seller
FROM
users
WHERE
user_id IN
(
SELECT seller_id FROM orders
)
GROUP BY
1
ORDER BY
2 DESC;
Top 5 Product Desember 2019
Berikut ini adalah query yang digunakan untuk mencari top 5 produk yang dibeli di bulan desember 2019 berdasarkan total quantity.
SELECT
sum(quantity) AS total_quantity,
desc_product
FROM
order_details od
JOIN
products p
ON od.product_id = p.product_id
JOIN
orders o
ON od.order_id = o.order_id
WHERE
created_at BETWEEN '2019-12-01' AND '2019-12-31'
GROUP BY
2
ORDER BY
1 DESC
LIMIT
5;
Transaksi per Bulan di Tahun 2020
Berikut query untuk menampilkan summary transaksi per bulan di tahun 2020. Karena data yang ada memiliki transaksi hingga bulan ke-5, maka filter cukup dilakukan dengan menggunakan tanggal start saja.
SELECT
EXTRACT(YEAR_MONTH FROM created_at) AS tahun_bulan,
count(1) AS jumlah_transaksi,
sum(total) AS total_nilai_transaksi
FROM
orders
WHERE
created_at >= '2020-01-01'
GROUP BY
1
ORDER BY
1;
Pengguna dengan Rata — Rata Transaksi Terbesar di Januari 2020
Berikut query untuk menampilkan 10 pembeli dengan rata-rata nilai transaksi terbesar yang bertransaksi minimal 2 kali di Januari 2020.
SELECT
buyer_id,
count(1) AS jumlah_transaksi,
avg(total) AS avg_nilai_transaksi
FROM
orders
WHERE
created_at >= '2020-01-01' AND created_at < '2020-02-01'
GROUP BY
1
HAVING
count(1) >= 2
ORDER BY
3 DESC
LIMIT
10;
Transaksi Besar di Desember 2019
Berikut query untuk menampilkan transaksi dengan semua nilai transaksi minimal 20,000,000 di bulan Desember 2019.
SELECT
nama_user AS nama_pembeli,
total AS nilai_transaksi,
created_at AS tanggal_transaksi
FROM
orders
INNER JOIN
users
ON buyer_id = user_id
WHERE
created_at >= '2019-12-01' AND created_at < '2020-01-01' AND total >= 20000000
ORDER BY
1;
Kategori Produk Terlaris di 2020
Berikut query untuk menampilkan 5 kategori dengan total quantity terbanyak di tahun 2020 (hanya untuk transaksi yang sudah terkirim ke pembeli).
SELECT
category,
sum(quantity) AS total_quantity,
sum(price) AS total_price
FROM
orders
INNER JOIN
order_details
USING(order_id)
INNER JOIN
products
USING(product_id)
WHERE
created_at >= '2020-01-01' AND delivery_at IS NOT NULL
GROUP BY
1
ORDER BY
2 DESC
LIMIT
5;
Mencari Pembeli High Value
Berikut query untuk mencari pembeli yang sudah bertransaksi lebih dari 5 kali, dan setiap transaksi lebih dari 2,000,000.
SELECT
nama_user AS nama_pembeli,
count(1) AS jumlah_transaksi,
sum(total) AS total_nilai_transaksi,
min(total) AS min_nilai_transaksi
FROM
orders
INNER JOIN
users
ON buyer_id = user_id
GROUP BY
user_id,
nama_user
HAVING
count(1) > 5 AND min(total) > 2000000
ORDER BY
3 DESC;
Dari hasil di atas, didapat 6 pembeli yang masuk ke kategori high value buyer.
Mencari Dropshipper
Dropshipper adalah pembeli yang membeli barang, tetapi barang tersebut dikirim ke orang lain. Ciri-cirinya yakni transaksinya banyak, dengan alamat yang berbeda-beda.
Berikut query untuk mencari pembeli dengan 10 kali transaksi atau lebih yang alamat pengiriman transaksi selalu berbeda setiap transaksi.
SELECT
nama_user AS nama_pembeli,
count(1) AS jumlah_transaksi,
count(DISTINCT orders.kodepos) AS distinct_kodepos,
sum(total) AS total_nilai_transaksi,
avg(total) AS avg_nilai_transaksi
FROM
orders
INNER JOIN
users
ON buyer_id = user_id
GROUP BY
user_id,
nama_user
HAVING
count(1) >= 10 AND count(1) = count(DISTINCT orders.kodepos)
ORDER BY
2 DESC;
Dari hasil di atas, ada 2 orang penjual yang diperkirakan merupakan dropshipper.
Mencari Reseller Offline
Selanjutnya, akan dicari tahu jenis pengguna yang menjadi reseller offline atau punya toko offline, yakni pembeli yang sering sekali membeli barang dan seringnya dikirimkan ke alamat yang sama. Pembelian juga dengan quantity produk yang banyak. Sehingga kemungkinan barang ini akan dijual lagi.
Berikut query untuk mencari pembeli yang punya 8 atau lebih transaksi yang alamat pengiriman transaksi sama dengan alamat pengiriman utama, dan rata-rata total quantity per transaksi lebih dari 10.
SELECT
nama_user AS nama_pembeli,
count(1) AS jumlah_transaksi,
sum(total) AS total_nilai_transaksi,
avg(total) AS avg_nilai_transaksi,
avg(total_quantity) AS avg_quantity_per_transaksi
FROM
orders
INNER JOIN
users
ON buyer_id = user_id
INNER JOIN
(
SELECT order_id, sum(quantity) AS total_quantity
FROM order_details
GROUP BY 1
) AS summary_order
USING(order_id)
WHERE
orders.kodepos = users.kodepos
GROUP BY
user_id,
nama_user
HAVING
count(1) >= 8 AND avg(total_quantity) > 10
ORDER BY
3 DESC;
Dari hasil di atas, ada 25 orang penjual yang diperkirakan merupakan reseller offline.
Pembeli Sekaligus Penjual
Berikut query untuk mencari penjual yang juga pernah bertransaksi sebagai pembeli minimal 7 kali.
SELECT
nama_user AS nama_pengguna,
jumlah_transaksi_beli,
jumlah_transaksi_jual
FROM
users
INNER JOIN
(
SELECT buyer_id, count(1) AS jumlah_transaksi_beli
FROM orders
GROUP BY 1
) AS buyer
ON buyer_id = user_id
INNER JOIN
(
SELECT seller_id, count(1) AS jumlah_transaksi_jual
FROM orders
GROUP BY 1
) AS seller
ON seller_id = user_id
WHERE
jumlah_transaksi_beli >= 7
ORDER BY
1;
Lama Transaksi Dibayar
DQLab store ingin mengetahui bagaimana trend lama waktu transaksi dibayar sejak transaksi tersebut dibuat.
Berikut query untuk menghitung rata-rata lama waktu dari transaksi dibuat sampai dibayar, dikelompokkan per bulan.
SELECT
EXTRACT(YEAR_MONTH FROM created_at) AS tahun_bulan,
count(1) AS jumlah_transaksi,
avg(datediff(paid_at, created_at)) AS avg_lama_dibayar,
min(datediff(paid_at, created_at)) AS min_lama_dibayar,
max(datediff(paid_at, created_at)) AS max_lama_dibayar
FROM
orders
WHERE
paid_at IS NOT NULL
GROUP BY
1
ORDER BY
1;
Kesimpulan
- Pada tahun 2019, jumlah transaksi terbanyak terjadi pada bulan december 2019. Sedangkan tahun 2020, jumlah transaksi terbanyak terjadi pada buan mei 2020.
- Dari seluruh transaksi (jumlah transaksi = 74,874) pada DQLab store ada sebanyak 5,046 transaksi tidak dibayar, 4,744 transaksi sudah dibayar tapi tidak dikirim, 9,790 transaksi yang tidak dikirim (baik yang sudah dibayar maupun belum), dan 9,634 dikirim pada hari yang sama dengan tanggal dibayar.
- Total seluruh pengguna pada DQLab store ada sebanyak 17,936, dengan rincian: 17,877 pengguna yang pernah bertransaksi sebagai pembeli, 69 pengguna yang pernah bertransaksi sebagai penjual, 69 pengguna yang pernah bertransaksi sebagai pembeli dan pernah sebagai penjual, 59 pengguna yang tidak pernah bertransaksi sebagai pembeli maupun penjual. Jadi dari seluruh pengguna DQLab store, sebanyak 99.67% sudah pernah melakukan transaksi.
- Rata-rata nilai transaksi terbesar yang bertransaksi minimal 2 kali di Januari 2020 adalah sebesar 11,719,500.
- Kategori dengan total kuantitas terbanyak di tahun 2020 (hanya untuk transaksi yang sudah terkirim ke pembeli) adalah kategori ‘Kebersihan Diri’ dengan total kuantitas 944,018.
- Dari sejumlah pembeli di DQLab store, ada 6 pembeli yang masuk ke kategori high value buyer dengan transaksi lebih dari 5 kali, dan setiap transaksinya memiliki nilai lebih dari 2,000,000.
- Dari sejumlah penjual, ada 2 orang penjual yang diperkirakan merupakan dropshipper, dan 25 orang penjual diperkirakan merupakan reseller offline.
- Rata-rata lama waktu dari transaksi dibuat sampai dibayar setiap bulannya adalah 7 hari. Dari analisis didapat waktu terlama pembayaran oleh pembeli adalah 14 hari.