Data Analysis for B2B Retail: Customer Analytics Report

Kali ini, saya akan memberikan kesimpulan singkat salah satu mini project dari DQLab Project yang telah saya selesaikan. Latar belakang yang diberikan yaitu terdapat perusahaan rintisan B2B yang akan meninjau performance perusahaan selama quarter terakhir. Peserta diminta untuk memberikan ulasan seputar pertumbuhan penjualan saat ini.

Tabel yang disediakan yaitu :

dqlab.id

1. Tabel orders_1 : Berisi data transaksi penjualan periode quarter 1 (Jan — Mar 2004)
2. Tabel Orders_2 : Berisi data transaksi penjualan periode quarter 2 (Apr — Jun 2004)
3. Tabel Customer : Berisi data profil customer yang mendaftar menjadi customer

Data Overview

Sebelum menyusun query SQL dan membuat analisa dari hasil query, pertama-tama cek seluruh kolom dalam tiap tabel agar familiar dengan tabel yang akan digunakan dan agar dapat menentukan kolom mana yang sekiranya berkaitan dengan masalah yang akan dianalisa.

SELECT * FROM orders_1 LIMIT 5;

SELECT * FROM orders_2 LIMIT 5;

SELECT * FROM customer LIMIT 5;

Total Penjualan dan Revenue

Kita akan melihat potal penjualan dan revenue pada quarter 1 (Jan, Feb, Mar) dan quarter 2 (Apr, Mei, Jun), berikut query yang dibutuhkan dan output yang dihasilkan :

total penjualan dan revenue quarter 1

SELECT SUM(quantity) as total_penjualan, SUM(quantity*priceeach) as revenue
FROM orders_1;

total penjualan & revenue quarter 1 yang berstatus “Terkirim”

SELECT SUM(quantity) as total_penjualan, SUM(quantity*priceeach) as revenue
FROM orders_1
WHERE status = "Shipped";

total penjualan dan revenue quarter 2

SELECT SUM(quantity) as total_penjualan, SUM(quantity*priceeach) as revenue
FROM orders_2;

total penjualan & revenue quarter 2 berstatus “Terkirim”

SELECT SUM(quantity) as total_penjualan, SUM(quantity*priceeach) as revenue
FROM orders_2
WHERE status = "Shipped";

Dari 4 query diatas, didapat insight bahwa pada quarter 1, seluruh penjualan berhasil terkirim (Shipped) tanpa ada yang dibatalkan, dilihat dari hasil query yang sama antara total penjualan seluruhnya dengan total penjualan dengan status “Shipped”. Namun, pada quarter 2, menunjukkan hasil query yang berbeda jumlah. Sehingga diketahui bahwa terdapat 1796 barang yang dibatalkan (berstatus “Cancelled”) sehingga perusahaan kehilangan revenue sebesar 171.723.490

Presentasi Keseluruhan Penjualan

SELECT quarter, SUM(quantity) as total_penjualan, SUM(quantity*priceEach) as revenue
FROM 
    (SELECT orderNumber, status, quantity, priceEach, '1' as quarter
     FROM orders_1
     UNION
     SELECT orderNumber, status, quantity, priceEach, '2' as quarter
     FROM orders_2
    ) tabel_a
WHERE status = "Shipped"
GROUP BY quarter;

total penjualan dan total revenue tiap quarter

Dari query diatas, terlihat bahwa :

• total penjualan dari quarter 1 ke quarter 2 menurun, sehingga growth penjualan - 22,74%
  perhitungan : (8694 - 6717) / 8694 x 100% = 22,74%
• Total revenue dari quarter 1 ke quarter 2 menurun, sehingga growth revenue - 24,02%
  perhitungan : (799.579.310 - 607.548.320) / 799.579.310 x 100% = 24,02%

Maka, penjualan menurun sebesar 22,74% dan revenue (pendapatan) menurun sebesar 24,02%.

Apakah jumlah customer semakin bertambah?

SELECT quarter, COUNT(customerID) as total_customers
FROM (SELECT customerID, createDate, QUARTER(createDate) as quarter
      FROM customer
      WHERE createDate BETWEEN '2004-01-01' AND '2004-06-30'
      ) tabel_b
GROUP BY quarter;

jumlah customer yang terdaftar tiap quarter

Jumlah pelanggan yang terdaftar menurun. Jumlah pelanggan dalam quarter 2 berkurang 8 orang dari jumlah pelanggan quarter 1.

Seberapa banyak customer yang sudah melakukan transaksi?

SELECT quarter, COUNT(customerID) as total_customers
FROM (SELECT customerID, createDate, QUARTER(createDate) as quarter
      FROM customer
      WHERE createDate BETWEEN '2004-01-01' AND '2004-06-30'
     ) tabel_b
WHERE customerID IN (SELECT DISTINCT(customerID) as total_customers
                     FROM orders_1
                     UNION
                     SELECT DISTINCT(customerID) as total_customers
                     FROM orders_2)
GROUP BY quarter;

jumlah customer yang bertransaksi tiap quarter

Jumlah pelanggan yang melakukan transaksi pun menurun, dalam quarter 2 berkurang 6 orang dari jumlah pelanggan quarter 1. Ketertarikan customer untuk melakukan transaksi dalam 2 quarter hanya sebesar 56%.
Perhitungan : (25 + 19) / (43 + 35) x 100% = 56,4%

Kategori produk apa saja yang paling banyak di-order customer di quarter 2?

SELECT *
FROM (SELECT categoryID, COUNT(DISTINCT orderNumber) as total_order,
      SUM(quantity) as total_penjualan
      FROM (SELECT productCode, orderNumber, quantity, status,
            LEFT(productCode, 3) as categoryID
            FROM orders_2
            WHERE status = "Shipped"
           ) tabel_c
      GROUP BY categoryID) sub
ORDER BY total_order DESC;

Total order dan total keseluruhan penjualan masing masing kategori produk

Produk yang terbanyak diorder adalah produk dengan categoryID S18 diorder sebanyak 25 kali dengan total penjualan 2264 item dan produk dengan categoryID S24 diorder sebanyak 21 kali dengan total penjualan 1826 item.

Seberapa banyak customers yang tetap aktif bertransaksi setelah transaksi pertamanya?

#Menghitung total unik customers yang transaksi di quarter_1
SELECT COUNT(DISTINCT customerID) as total_customers 
FROM orders_1;

SELECT '1' as quarter, COUNT(DISTINCT customerID)/25*100 as q2
FROM orders_1
WHERE customerID IN (SELECT DISTINCT customerID 
                     FROM orders_2);

Dalam bentuk presentase, customer yang tetap aktif bertransaksi (dalam quarter 2) setelah transaksi pertamanya pada quarter 1 yaitu sebanyak 24%

Kesimpulan

Berdasarkan data yang telah kita peroleh melalui query SQL, Kita dapat menarik kesimpulan bahwa :

1. Performance perusahaan menurun di quarter ke-2, terlihat dari nilai penjualan dan revenue yang menurun hingga 20% dan 24%.
2. Perolehan customer baru juga tidak terlalu baik, dan sedikit menurun dibandingkan quarter sebelumnya.
3. Ketertarikan customer baru untuk berbelanja masih kurang, hanya sekitar 56% saja yang sudah bertransaksi. Disarankan tim Produk untuk perlu mempelajari behaviour customer dan melakukan product improvement, sehingga conversion rate (register to transaction) dapat meningkat.
4. Produk kategori S18 dan S24 berkontribusi sekitar 50% dari total order dan 60% dari total penjualan, sehingga perusahaan sebaiknya fokus untuk pengembangan category S18 dan S24.
5. Retention rate customer juga sangat rendah yaitu hanya 24%, artinya banyak customer yang sudah bertransaksi di quarter-1 tidak kembali melakukan order di quarter ke-2 (no repeat order).
6. Perusahaan mengalami pertumbuhan negatif di quarter ke-2 dan perlu melakukan banyak improvement baik itu di sisi produk dan bisnis marketing, jika ingin mencapai target dan positif growth di quarter ke-3. Rendahnya retention rate dan conversion rate bisa menjadi diagnosa awal bahwa customer tidak tertarik/kurang puas/kecewa berbelanja dalam perusahaan ini.