Project Data Analysis for B2B Retail: Customer Analytics Report [SQL DQLab Project]

Mentor : Trisna Yulia Junita (Data Scientist PT Buma)

Source : https://blog.aspiresys.com/wp-content/uploads/2016/08/Customer-Analytics-01.png

Pada kesempatan kali ini akan di bahas salah satu kelas Project SQL yang ada di DQLab Academy yaitu Customer Analytics Report. Project ini dimentori oleh Kakak Trisna Yulia Junita yang sekarang bekerja sebagai Data Scientist di PT Buma.

Customer analytic adalah salah satu penerapan dari Data Science yang sangat penting dilakukan dalam Dunia retail. Seorang data scientist dapat mengolah database Retail yang bisasanya berupa SQL dan terdiri dari jutaan row menjadi beberapa informasi penting tentang perilaku dan kebiasaan customer yang nantinya akan berguna bagi perusahaan untuk pengambilan keputusan bisnis yang tepat.

Mari langsung masuk ke project .

Latar Belakang

xyz.com adalah perusahaan rintisan B2B yang menjual berbagai produk tidak langsung kepada end user tetapi ke bisnis/perusahaan lainnya. Sebagai data-driven company, maka setiap pengambilan keputusan di xyz.com selalu berdasarkan data. Setiap quarter xyz.com akan mengadakan townhall dimana seluruh atau perwakilan divisi akan berkumpul untuk me-review performance perusahaan selama quarter terakhir.

Tugas dan Langkah

Sebagai seorang data analyst, kamu dimintai untuk menyediakan data dan analisa mengenai kondisi perusahaan bulan terakhir untuk di presentasi-kan di townhall tersebut. (Asumsikan tahun yang sedang berjalan adalah tahun 2004).

Adapun hal yang akan direview adalah :

1. Bagaimana pertumbuhan penjualan saat ini?
2. Apakah jumlah customers xyz.com semakin bertambah ?
3. Dan seberapa banyak customers tersebut yang sudah melakukan transaksi?
4. Category produk apa saja yang paling banyak dibeli oleh customers?
5. Seberapa banyak customers yang tetap aktif bertransaksi?

Langkah yang akan dilakukan :

1. Menggunakan klausa “Select … From …” untuk mengambil data di database
2. Menggunakan klausa Where dan Operator untuk menfilter data
3. Menggunakan “group by”dan fungsi aggregat untuk aggregasi penjualan dan revenue
4. Menggunakan “order by” untuk mengurutkan data
5. Menggunakan “union” untuk menggabungkan tabel data penjualan
6. Menggunakan “date and time function” dan fungsi text untuk data manipulation
7. Menggunakan subquery untuk menyimpan hasil sementara untuk digunakan kembali dalam query.

Tabel yang Digunakan

Tabel yang akan digunakan pada project kali ini adalah sebagai berikut.

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

Memahami table

Sebelum membuat memulai menyusun query SQL dan membuat Analisa dari hasil query, hal yang perlu dilakukan adalah pertama-tama menjadi familiar dengan tabel yang akan digunakan. Hal ini akan sangat berguna dalam menentukan kolom mana sekiranya berkaitan dengan problem yang akan dianalisa, dan proses manipulasi data apa yang sekiranya perlu dilakukan untuk kolom — kolom tersebut, karena tidak semua kolom pada tabel perlu untuk digunakan.

Answer :

SELECT * FROM orders_1 limit 5;
SELECT * FROM orders_2 limit 5;
SELECT * FROM customer limit 5;

Insight :

1. Tabel order_1 dan order_2 berisi 9 kolom yang terdiri dari nomor order(kode unik), tanggal order, tanggal pengiriman, status pengiriman, custumer id, kode produk, jumlah, dan harga per produk
2. Tabel customer berisi kolom customer id, nama customer, nama depan dan belakang, kota asal, negara asal dan tanggal terbentuknya customer id.

Total Penjualan dan Revenue pada Quarter-1 (Jan, Feb, Mar) dan Quarter-2 (Apr,Mei,Jun)

1. Dari tabel orders_1 lakukan penjumlahan pada kolom quantity dengan fungsi aggregate sum() dan beri nama “total_penjualan”, kalikan kolom quantity dengan kolom priceEach kemudian jumlahkan hasil perkalian kedua kolom tersebut dan beri nama “revenue”
2. Perusahaan hanya ingin menghitung penjualan dari produk yang terkirim saja, jadi kita perlu mem-filter kolom ‘status’ sehingga hanya menampilkan order dengan status “Shipped”.
3. Lakukan Langkah 1 & 2, untuk tabel orders_2.

Notes: Jangan lupa untuk mengakhiri setiap statement dengan titik koma sehingga kedua statement dapat dijalankan bersamaan.(;)

Hasilnya sebagai berikut :

Answer :

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

Output :

Menghitung persentasi keseluruhan penjualan

Kedua tabel orders_1 dan orders_2 masih terpisah, untuk menghitung persentasi keseluruhan penjualan dari kedua tabel tersebut perlu digabungkan :

1. Pilihlah kolom “orderNumber”, “status”, “quantity”, “priceEach” pada tabel orders_1, dan tambahkan kolom baru dengan nama “quarter” dan isi dengan value “1”. Lakukan yang sama dengan tabel orders_2, dan isi dengan value “2”, kemudian gabungkan kedua tabel tersebut.
2. Gunakan statement dari Langkah 1 sebagai subquery dan beri alias “tabel_a”.
3. Dari “tabel_a”, lakukan penjumlahan pada kolom “quantity” dengan fungsi aggregate sum() dan beri nama “total_penjualan”, dan kalikan kolom quantity dengan kolom priceEach kemudian jumlahkan hasil perkalian kedua kolom tersebut dan beri nama “revenue”
4. Filter kolom ‘status’ sehingga hanya menampilkan order dengan status “Shipped”.
5. Kelompokkan total_penjualan berdasarkan kolom “quarter”, dan jangan lupa menambahkan kolom ini pada bagian select.

Hasil kolom sebagai berikut :

Answer :

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

Output :

Perhitungan Growth Penjualan dan Revenue

Untuk project ini, perhitungan pertumbuhan penjualan akan dilakukan secara manual menggunakan formula yang disediakan diatas. Adapun perhitungan pertumbuhan penjualan dengan SQL dapat dilakukan menggunakan “window function” yang akan dibahas di materi DQLab berikutnya.

%Growth Penjualan = (6717–8694)/8694 = -22%

%Growth Revenue = (607548320–799579310)/ 799579310 = -24%

Insight :

1. Total penjualan mengalami penurunan di quarter 2 sebesar 22%
2. Total pendapatan juga ikut menurun di quarter 2 sebesar 24%

Apakah jumlah customers xyz.com semakin bertambah?

Penambahan jumlah customers dapat diukur dengan membandingkan total jumlah customers yang registrasi di periode saat ini dengan total jumlah customers yang registrasi diakhir periode sebelumnya.

1. Dari tabel customer, pilihlah kolom customerID, createDate dan tambahkan kolom baru dengan menggunakan fungsi QUARTER(…) untuk mengekstrak nilai quarter dari CreateDate dan beri nama “quarter”
2. Filter kolom “createDate” sehingga hanya menampilkan baris dengan createDate antara 1 Januari 2004 dan 30Juni 2004
3. Gunakan statement Langkah 1 & 2 sebagai subquery dengan alias tabel_b
4. Hitunglah jumlah unik customers à tidak ada duplikasi customers dan beri nama “total_customers”
5. Kelompokkan total_customer berdasarkan kolom “quarter”, dan jangan lupa menambahkan kolom ini pada bagian select.

Answer :

SELECT 
    1 AS quarter, 
    COUNT(customerID) AS total_customers 
FROM customer 
WHERE createDate BETWEEN "2004-01-01" AND "2004-03-31" 
UNION 
SELECT 
    2 AS quarter, 
    COUNT(customerID) AS total_customers 
FROM customer 
WHERE createDate BETWEEN "2004-04-01" AND "2004-06-30";

Output :

Insight :

1. Selain jumlah penjualan dan pendapatan, ternyata total konsumen juga ikut menurun di quarter 2 yaitu dari 43 ke 35 atau sekitar 18%.

Seberapa banyak customers tersebut yang sudah melakukan transaksi?

Problem ini merupakan kelanjutan dari problem sebelumnya yaitu dari sejumlah customer yang registrasi di periode quarter-1 dan quarter-2, berapa banyak yang sudah melakukan transaksi

1. Dari tabel customer, pilihlah kolom customerID, createDate dan tambahkan kolom baru dengan menggunakan fungsi QUARTER(…) untuk mengekstrak nilai quarter dari CreateDate dan beri nama “quarter”
2. Filter kolom “createDate” sehingga hanya menampilkan baris dengan createDate antara 1 Januari 2004 dan 30 Juni 2004
3. Gunakan statement Langkah A&B sebagai subquery dengan alias tabel_b
4. Dari tabel orders_1 dan orders_2, pilihlah kolom customerID, gunakan DISTINCT untuk menghilangkan duplikasi, kemudian gabungkan dengan kedua tabel tersebut dengan UNION.
5. Filter tabel_b menggunakan operator IN() dan ‘Select statement Langkah d’, sehingga hanya customerID yang pernah bertransaksi (customerID tercatat di tabel orders) yang diperhitungkan.
6. Hitunglah jumlah unik customers (tidak ada duplikasi customers) di statement SELECT dan beri nama “total_customers”
7. Kelompokkan total_customer berdasarkan kolom “quarter”, dan jangan lupa menambahkan kolom ini pada bagian select.

Answer :

SELECT 
    1 AS quarter, 
    COUNT(DISTINCT customerID) AS total_customers 
FROM orders_1 
WHERE status =”Shipped” 
UNION 
SELECT 
    2 AS quarter, 
    COUNT(DISTINCT customerID) AS total_customers 
FROM orders_2 
WHERE customerID IN (SELECT customerID FROM customer WHERE createDate BETWEEN “2004–04–01” AND “2004–06–30”);

Output :

Insight :

1. Selain jumlah penjualan, jumlah pendapatan dan jumlah pelanggan yang menurun di quarter 2, ternyata jumlah pelanggan yang melakukan transaksi juga mengalami penurunan dari 25 ke 19 atau sebesar 31,5%. sepertinya perusahaan xyz.com harus berbenah nih, hehe

Category produk apa saja yang paling banyak di-order oleh customers di Quarter-2?

Untuk mengetahui kategori produk yang paling banyak dibeli, maka dapat dilakukan dengan menghitung total order dan jumlah penjualan dari setiap kategori produk.

1. Dari kolom orders_2, pilih productCode, orderNumber, quantity, status
2. Tambahkan kolom baru dengan mengekstrak 3 karakter awal dari productCode yang merupakan ID untuk kategori produk; dan beri nama categoryID
3. Filter kolom “status” sehingga hanya produk dengan status “Shipped” yang diperhitungkan
4. Gunakan statement Langkah 1, 2, dan 3 sebagai subquery dengan alias tabel_c
5. Hitunglah total order dari kolom “orderNumber” dan beri nama “total_order”, dan jumlah penjualan dari kolom “quantity” dan beri nama “total_penjualan”
6. Kelompokkan berdasarkan categoryID, dan jangan lupa menambahkan kolom ini pada bagian select.
7. Urutkan berdasarkan “total_order” dari terbesar ke terkecil

Hasilnya sebagai berikut :

Answer :

SELECT 
    (SELECT SUBSTR(productCode,1,3)) AS categoryid, 
    COUNT(distinct orderNumber) AS total_order,
    SUM(quantity) AS total_penjualan 
FROM orders_2 
WHERE status=’Shipped’ 
GROUP BY categoryid 
ORDER BY total_order DESC;

Output :

Insight :

1. Produk dengan category id S18 paling banyak terjual di quarter 2 dengan total penjualan 25 unit dan pendapatan 2264, disusul oleh S24 dengan total penjualan 21 dan pendapatan 1826.
2. Produk dengan category id S72 paling sedikit terjual di quarter 2 dengan total penjualan 2 unit dan pendapatan 61.

Seberapa banyak customers yang tetap aktif bertransaksi setelah transaksi pertamanya?

Mengetahui seberapa banyak customers yang tetap aktif menunjukkan apakah xyz.com tetap digemari oleh customers untuk memesan kebutuhan bisnis mereka. Hal ini juga dapat menjadi dasar bagi tim product dan business untuk pengembangan product dan business kedepannya. Adapun metrik yang digunakan disebut retention cohort. Untuk project ini, kita akan menghitung retention dengan query SQL sederhana, sedangkan cara lain yaitu JOIN dan SELF JOIN akan dibahas dimateri selanjutnya :

Oleh karena baru terdapat 2 periode yang Quarter 1 dan Quarter 2, maka retention yang dapat dihitung adalah retention dari customers yang berbelanja di Quarter 1 dan kembali berbelanja di Quarter 2, sedangkan untuk customers yang berbelanja di Quarter 2 baru bisa dihitung retentionnya di Quarter 3.

1. Dari tabel orders_1, tambahkan kolom baru dengan value “1” dan beri nama “quarter”
2. Dari tabel orders_2, pilihlah kolom customerID, gunakan distinct untuk menghilangkan duplikasi
3. Filter orders_1 menggunakan operator IN() dan ‘Select statement Langkah 2)’, sehingga hanya customerID yang pernah bertransaksi di quarter 2 (customerID tercatat di tabel orders_2) yang diperhitungkan.
4. Hitunglah jumlah unik customers (tidak ada duplikasi customers) dibagi dengan total_ customers dalam percentage, pada Select statement dan beri nama “Q2”

Answer :

SELECT 
    COUNT(DISTINCT customerID) AS total_customers 
FROM orders_1;
SELECT 
    1 AS quarter,
    (SELECT 
         COUNT(distinct customerID) 
     FROM orders_1 
     WHERE customerID IN (SELECT 
                               customerID 
                         FROM orders_2
                          )
     )*100/count(DISTINCT customerID) AS q2 
FROM orders_1  
GROUP BY quarter;

Output :

Insight :

1. Banyak customer yang sudah bertransaksi di quarter-1 tidak kembali melakukan order di quarter ke-2. dengan penurunan 24%.

Kesimpulan

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

1. Performance xyz.com menurun signifikan di quarter ke-2, terlihat dari nilai penjualan dan revenue yang drop hingga 20% dan 24%,
2. perolehan customer baru juga tidak terlalu baik, dan sedikit menurun dibandingkan quarter sebelumnya.
3. Ketertarikan customer baru untuk berbelanja di xyz.com 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 xyz.com sebaiknya fokus untuk pengembangan category S18 dan S24.
5. Retention rate customer xyz.com 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. com 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 di xyz.com.

Certificate

referensi : dqlab.id