Data Analysis for E-Commerce

Photo by Roberto Cortese on Unsplash

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.

Entity Relationship Diagram database yang digunakan.

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;
Jumlah transaksi per bulan.

Status Transaksi

  • Jumlah transaksi yang tidak dibayar:
SELECT count(1) AS transaksi_tidak_dibayar FROM orders WHERE paid_at = 'NA';
Jumlah transaksi yang tidak dibayar.
  • 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 sudah dibayar tapi tidak dikirim.
  • 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 tidak dikirim, baik yang sudah dibayar maupun belum.
  • Jumlah transaksi yang dikirim pada hari yang sama dengan tanggal dibayar:
SELECT count(1) AS jumlah_transaksi FROM orders WHERE paid_at = delivery_at;
Jumlah transaksi yang dikirim pada hari yang sama dengan tanggal dibayar.

Pengguna Bertransaksi

  • Total seluruh pengguna:
SELECT count(DISTINCT user_id) AS jumlah_seluruh_pengguna FROM users;
Total seluruh pengguna.
  • Total pengguna yang pernah bertransaksi sebagai pembeli:
SELECT count(DISTINCT buyer_id) AS jumlah_buyer FROM orders;
Total pengguna yang pernah bertransaksi sebagai pembeli.
  • Total pengguna yang pernah bertransaksi sebagai penjual:
SELECT count(DISTINCT seller_id) AS jumlah_seller FROM orders;
Total pengguna yang pernah bertransaksi sebagai penjual.
  • 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 pernah bertransaksi sebagai pembeli dan pernah sebagai penjual.
  • 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
);
Total pengguna yang tidak pernah bertransaksi sebagai pembeli maupun penjual.

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;
5 pembeli dengan dengan total pembelian terbesar (berdasarkan total harga barang setelah diskon).

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;
Pembeli yang tidak pernah menggunakan diskon ketika membeli barang dan merupakan 5 pembeli dengan transaksi terbanyak.

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;
Pembeli yang bertransaksi setidaknya 1 kali setiap bulan di tahun 2020 dengan rata-rata total amount per transaksi lebih dari 1 Juta.

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;
Domain email dari penjual.

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;
Top 5 product desember 2019.

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;
Transaksi per bulan di tahun 2020.

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;
Pengguna dengan rata — rata transaksi terbesar di januari 2020.

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;
Transaksi besar di desember 2019.

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;
5 kategori dengan total quantity terbanyak di tahun 2020 (hanya untuk transaksi yang sudah terkirim ke pembeli).

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;
Mencari pembeli high value.

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;
Mencari dropshipper.

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;
Mencari reseller offline.

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;
Pembeli Sekaligus Penjual.

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;
Lama Transaksi Dibayar.

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.

--

--

Data Scientist @EdTech | NLP Entusiast | fadilah.webflow.io

Love podcasts or audiobooks? Learn on the go with our new app.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store