Data Analysis for E-Commerce

9 min readDec 10, 2020

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;

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 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 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;

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;

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;

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;

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.