Data Analysis for B2B Retail: Customer Analytics Report
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 SQLite. Klik di sini untuk melihat Full SQL script.
Customer Analytics Report adalah sebuah laporan yang menyediakan informasi terkait perilaku customer suatu perusahaan/produk dalam periode tertentu.
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.
Berikut adalah beberapa pertanyaan yang harus dijawab pada customer analytics report ini:
- Bagaimana pertumbuhan penjualan saat ini?
- Apakah jumlah customers xyz.com semakin bertambah ?
- Berapa banyak customers tersebut yang sudah melakukan transaksi?
- Kategori produk apa saja yang paling banyak dibeli oleh customers di quarter ke-2?
- Berapa banyak customers yang tetap aktif bertransaksi?
Diasumsikan tahun yang sedang berjalan adalah tahun 2004.
Dataset Brief
Tabel orders_1
Berisi data terkait transaksi penjualan periode quarter-1 (Januari — Maret 2004).
SELECT * FROM orders_1 LIMIT 5;
Tabel orders_2
Berisi data terkait transaksi penjualan periode quarter-2 (April — Juni 2004).
SELECT * FROM orders_2 LIMIT 5;
Tabel customer
Berisi data profil customer yang mendaftar menjadi customer xyz.com.
SELECT * FROM customer LIMIT 5;
1. Bagaimana pertumbuhan penjualan saat ini?
1A. Total Penjualan dan Revenue pada Quarter-1 dan Quarter-2
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';
1B. Menghitung persentasi keseluruhan penjualan
Untuk mendapatkan % growth penjualan dan % growth revenue, saya menggunakan window function, tepatnya fungsi lag(). Fungsi lag digunakan untuk mengambil previous row. Selain itu, saya juga menggunakan fungsi CAST() untuk mengubah tipe data dari float menjadi text agar dapat ditambahkan karakter ‘%’.
SELECT
quarter,
sum(quantity) AS total_penjualan,
sum(quantity * priceeach) AS revenue,
CAST(round((sum(quantity) - lag(sum(quantity)) OVER(ORDER BY quarter)) * 100 / round(lag(sum(quantity)) OVER(ORDER BY quarter),2),2) AS TEXT) || '%' AS growth_penjualan,
CAST(round((sum(quantity * priceeach) - lag(sum(quantity * priceeach)) OVER(ORDER BY quarter)) * 100 / round(lag(sum(quantity * priceeach)) OVER(ORDER BY quarter),2),2) AS TEXT) || '%' AS growth_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
1;
% Growth Penjualan = (6717 – 8694) / 8694 = -22.74%
% Growth Revenue = (607548320 – 799579310) / 799579310 = -24.02%
Berdasarkan perhitungan di atas, growth penjualan pengalami penurunan yaitu sebesar -22.74%. Begitu juga dengan growth revenue, dengan penurunan sebesar -24.02%.
2. 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.
SELECT
quarter,
count(DISTINCT customerID) AS total_customers
FROM
(
SELECT
customerID,
createDate,
CASE
WHEN CAST(strftime('%m', createDate) AS INTEGER) BETWEEN 1 AND 3 THEN 1
ELSE 2
END AS quarter
FROM
customer
WHERE
createDate BETWEEN '2004-01-01' AND '2004-06-30'
) AS tabel_b
GROUP BY
1;
Berdasarkan data di atas, terjadi penurunan jumlah customer baru pada quarter ke-2.
3. Seberapa banyak customers tersebut yang sudah melakukan transaksi?
SELECT
quarter,
count(DISTINCT customerID) AS total_customers
FROM
(
SELECT
customerID,
createDate,
CASE
WHEN CAST(strftime('%m', createDate) AS INTEGER) BETWEEN 1 AND 3 THEN 1
ELSE 2
END AS quarter
FROM
customer
WHERE
(createDate BETWEEN '2004-01-01' AND '2004-06-30')
AND
customerID IN
(
SELECT
DISTINCT customerID
FROM
orders_1
UNION
SELECT
DISTINCT customerID
FROM
orders_2
)
) AS tabel_b
GROUP BY
1;
% Customer yang telah melakukan transaksi = (Jumlah Customer yang bertransaksi di q1 & q1) * 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%.
4. Kategori produk apa saja yang paling banyak dibeli oleh customers?
Untuk mengetahui kategori produk yang paling banyak dibeli, maka dapat dilakukan dengan menghitung total order dan jumlah penjualan dari setiap kategori produk.
SELECT * FROM
(
SELECT
substr(productCode, 1, 3) AS categoryID,
count(DISTINCT orderNumber) AS total_order,
sum(quantity) AS total_penjualan
FROM
(
SELECT
productCode,
orderNumber,
quantity,
status
FROM
orders_2
WHERE
status = 'Shipped'
) AS tabel_c
GROUP BY
1
) AS c
ORDER BY
2 DESC;
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?
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, saya akan menghitung retention dengan query SQL sederhana.
--Menghitung total unik customers yang transaksi di quarter_1
SELECT count(DISTINCT customerID) AS total_customers FROM orders_1;
--output = 25SELECT
'1' AS quarter,
CAST(round(count(DISTINCT customerID) * 100 / round(25,2),2) AS TEXT) || '%' AS q2
FROM
orders_1
WHERE
customerID IN
(
SELECT
DISTINCT customerID
FROM
orders_2
);
Berdasarkan data di atas, dari 25 customers yang melakukan transaksi di quarter ke-1, 24% tetap aktif melakukan transaksi di quarter ke-2.
Kesimpulan
- Performance xyz.com menurun signifikan di quarter ke-2, terlihat dari nilai penjualan dan revenue yang drop hingga 20% dan 24%.
- Perolehan customer baru juga tidak terlalu baik, dan sedikit menurun dibandingkan quarter sebelumnya.
- Ketertarikan customer baru untuk berbelanja di xyz.com masih kurang, hanya sekitar 56% saja yang sudah bertransaksi. Disarankan tim Produk untuk perlu mempelajari perilaku customer dan melakukan product improvement, sehingga conversion rate (register to transaction) dapat meningkat.
- 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.
- 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).
- 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.
