Project Data Analysis for B2B Retail - Customer Analytics Report

Halo pembaca tulisan ini, saya ucapkan terima kasih sudah membaca tulisan ini. Kali ini saya akan mendokumentasikan hasil belajar saya saat saya belajar di pelatihan yang diadakan Kementerian KOMINFO Republik Indonesia yaitu ada Program Digital Talent Scholarship. Proyek ini berjudul Project Data Analysis for B2B Retail yaitu membahas tentang Customer Analytics Report. Customer Analytics Report adalah sebuah laporan yang menyediakan informasi terkait perilaku customer suatu perusahaan/produk dalam periode tertentu. Pada proyek ini terdapat Data Mentor yang mengarahkan saya dalam penyelesaian yaitu kak Trisna Yulia Junita beliau adalah seorang Data Scientist di PT. BUMA. Setelah menyelesaikan proyek ini saya mendapat ilmu analisis yang mudah mudah bisa saya jadikan referesi ketika saya menemukan permasalahan yang sama atau hampir mirip-mirip. Oke langsung saja kita bahas dokumentasi ini.

Latar Belakang

xyz.com adalah perusahan 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 Proyek

Sebagai seorang data analyst, kamu dimintai untuk menyediakan data dan analisa mengenai kondisi perusahaan bulan terakhir untuk dipresentasikan 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?

Dataset

Tabel yang akan digunakan pada proyek 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

1. Pahami tabel-tabel yang ada.

   Sebelum memulai menyusun query SQL dan membuat Analisa dari hasil query, hal pertama yang perlu dilakukan adalah 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.

   • Mengecek tabel orders_1 :SELECT * FROM orders_1 limit 5;

   • Mengecek tabel orders_2 :SELECT * FROM orders_2 limit 5;

   • Mengecek tabel customer :SELECT * FROM customer limit 5;

Baik jika sudah mengetahui seperti apa tabel tabel yang ada maka lanjut pada proses selanjutnya.

1. Bagaimana pertumbuhan penjualan saat ini?

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

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

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

Menghitung persentasi keseluruhan penjualan

Lakukan langkah berikut :

• Menggunakan perintah UNION untuk menggabungkan 2 tabel

• Menggunakan perintah SUM untuk menghitung total_penjualan dan revenue

• Menggunakan GROUP BY untuk mengelompokkan data berdasarkan quarter

• Order yang ditampilkan hanya orderan yang terkirim atau ‘Shipped’ dengan memfilter kolom status pada perintah WHERE

• Menggunakan Subquery untuk menambah perhitungan diluar tabel ‘tabel_a’

Seperti script di bawah ini :

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
	) AS tabel_a
WHERE 
	status='Shipped'
GROUP BY 
	quarter

Berdasarkan tabel diatas, terjadi penurunan quarter 1 dan 2 sebesar -22% penjualan dan -24% untuk revenue

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

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

2.Apakah jumlah customers xyz.com semakin bertambah?

Lakukan langkah berikut :

• Menggunakan perintah QUARTER untuk mengekstrak nilai quarter berdasarkan CreateDate

• Menggunakan perintah COUNT untuk menghitung total_customers dari setiap quarter, dengan menggunakan DISTINCT agar tidak terjadi duplikasi

• Menggunakan GROUP BY untuk mengelompokkan data berdasarkan quarter

• Order yang ditampilkan berdasarkan jangka waktu quarter ‘2004–01–01’ hingga ‘2004–06–30’ dengan memfilter kolom status pada perintah WHERE dan BETWEEN

• Menggunakan Subquery pada tabel baru

Seperti script di bawah ini :

SELECT
	quarter,
	count(distinct customerid) as total_customers
FROM
	(SELECT
		customerID,
		createDate,
		quarter(createDate) as quarter	
FROM
		customer 
	WHERE 
		 createdate between '2004-01-01' and '2004-06-30'
	) as tabel_b
GROUP BY quarter

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

Berdasarkan tabel diatas, terjadi penurunan Customer pada quarter 2. Quarter 1 (Januari-Maret) dan Quarter 2 (April-Juni)

3.Seberapa banyak customers tersebut yang sudah melakukan transaksi?

Lakukan Langkah berikut :

• Menggunakan perintah COUNT untuk menghitung total_customers dari setiap quarter, dengan menggunakan DISTINCT agar tidak terjadi duplikasi data

• Menggunakan UNION untuk menggabungkan 2 tabel dari perintah WHERE IN

• Menggunakan GROUP BY untuk mengelompokkan data total_customers berdasarkan quarter

• Order yang ditampilkan berdasarkan jangka waktu quarter ‘2004–01–01’ hingga ‘2004–06–30’ dengan memfilter kolom status pada perintah WHERE dan BETWEEN

• Menggunakan Subquery pada tabel baru

• Menggunakan operator IN dan DISTINCT untuk memfilter CustomerID yang melakukan transaksi tanpa duplikasi.

Seperti script di bawah ini

SELECT
	quarter, count(distinct customerid) as total_customers
FROM
	(SELECT
		customerid, createdate, quarter(createdate) as quarter
FROM
		customer
WHERE
		createdate between '2004-01-01' and '2004-06-30') as tabel_b
WHERE
customerid IN( SELECT distinct customerid
FROM
			orders_1
UNION
SELECT
			distinct customerid
FROM
			orders_2)
GROUP BY quarter

Berdasarkan tabel diatas, terjadi penurunan transaksi Customer pada quarter 2

Quarter 1 (Januari-Maret) Quarter 2 (April-Juni)

% Customer yang telah melakukan transaksi = (Jumlah Customer yang bertransaksi di q1 & q2) * 100 / (Jumlah Customer yang mendaftar di q1 & q2)

% Customer yang telah melakukan transaksi = (25 + 19) * 100 / (43 + 35) = 56.41%

Berdasarkan perhitungan di atas, diperoleh sebesar 56.41%. Yang artinya sebanyak customers di q1 & q2 yang sudah melakukan transaksi ada 56.41%.

4.Category Produk apa saja yang sering dipesan pada Quarter 2?

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

Lakukan Langkah berikut :

• Menggunakan perintah COUNT untuk menghitung total_order pada orderNumber untuk categoryID, dengan menggunakan DISTINCT agar tidak terjadi duplikasi data

• Menggunakan SUM untuk menjumlahkan total_penjualan dan Menggunakan GROUP BY untuk mengelompokkan data CategoryID

• Order yang ditampilkan hanya orderan yang terkirim atau ‘Shipped’ dengan memfilter kolom status pada perintah WHERE

• Menggunakan fungsi LEFT untuk menggambil 3 karakter awal pada CategoryID

• Mengurutkan total_order dengan ORDER BY secara Descending

Seperti script di bawah ini.

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 ) a 
ORDER BY total_order DESC

Berdasarkan tabel di atas, Top 3 kategori produk dengan total order paling banyak yaitu S18,S24 dan S32 dengan total penjualan > 600 sedangkan kategori produk lainnya memperoleh total penjualan sebesar < 600.

Kemudian dari data di atas, produk dengan categoryID S18 dan S24 berkontribusi sebesar 50% dari total order keseluruhan untuk quarter ke-2. Dari segi kuantitas penjualan, penjualan terbanyak di quarter ke-2 juga ditempati produk S18 dan S24 yaitu sebesar 60.89% dari total penjualan.

5.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 proyek ini, saya akan menghitung retention dengan query SQL sederhana.

Lakukan Langkah berikut :

• Menggunakan perintah COUNT untuk menghitung total_customers, dengan menggunakan DISTINCT agar tidak terjadi duplikasi data

• Menggunakan COUNT untuk menghitung CustomerID pada Quarter 2, dengan menggabungkan Customer Quarter 1, dengan menggunakan DISTINCT agar tidak terjadi duplikasi data pada orders 1

• Order yang ditampilkan hanya orderan pada CustomerID dari orders 2 atau Quarter 2, maka digunakan fungsi WHERE dan DISTINCT agar tidak terjadi duplikasi data

seperti script di bawh ini.

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)

Berdasarkan tabel di atas, Dari 25 customers yang melakukan transaksi di quarter ke-1, 24% tetap aktif melakukan transaksi di quarter ke-2.

Hanya 24% Customer yang melakukan Repeat Order sehingga anjloknya penjualan pada Quarter 2.

100% — 24% = 76%

Sebanyak 76% Customer dari Quarter 1 tidak melakukan Repeat Order, sehingga terjadi penurunan penjualan pada Quarter 2, maka dari itu dibutuhkan evaluasi lebih lanjut mengenai strategi marketing dan produk-produknya pada perusahaan XYZ.COM.

Kesimpulan

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. xyz.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.

Terima kasih telah membaca tulisan ini.