Project Data Analysis for B2B Retail: Customer Analytics Report

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

2. Problem Statement

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

Tabel orders_1 : Berisi data terkait transaksi penjualan periode quarter 1(Jan — Mar 2004)

Tabel Orders_2: Berisi data terkait transaksi penjualan periode quarter 2(Apr — Jun 2004)

Tabel Customer : Berisi data profil customer yang mendaftar menjadi customer xyz.com

Sebelum mulai menganalisa kita harus cek isi kolom dan value dari tabel tabel yang ada

SELECT * FROM orders_1 limit 5;

orders_1

SELECT * FROM orders_2 limit 5;

order_2

SELECT * FROM customer limit 5;

Customer

3. PROBLEM STATEMENT 1

Bagaimana pertumbuhan penjualan saat ini?

Step 1 Define metriks yang dibutuhkan:

• Total Penjualan = Quantity
• Revenue = Quantity * Price (GMV)

Step 2 Kondisi yang harus terpenuhi:

• Terhitung apabila statusnya sudah dikirimkan (Shipped)
• Only for Quarter 1 and Quarter 2

Step 3 susun query berdasarkan yang sudah kita define di Step 1 dan 2

SELECT
 sum(quantity) AS total_sales,
 quantity (amount * price each) AS revenue
FROM
 order_1
WHERE
 status = 'Sent';

Total Sales and Revenue Quarter-1

SELECT
 sum(quantity) AS total_sales,
 quantity (amount * price each) AS revenue
FROM
 order_2
WHERE
 status = 'Sent';

Total Sales and Revenue Quarter-2

4. PROBLEM STATEMENT 2

Apakah jumlah customers xyz.com semakin bertambah ?

Menghitung persentasi keseluruhan penjualan

Menghitung persentasi perbandingan (% delta):

Spike -> naik, declines -> turun

(Before/After — 1) * 100%

(After-Before)/Before * 100%

SELECT quarters,
       sum(quantity) AS total_sales,
       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 table_a
WHERE
 status = 'Shipped'
GROUP BY 1;

Total Sales and Revenue Quarter-1 and Quarter 2

5. PROBLEM STATEMENT 3

Apakah jumlah customers xyz.com semakin bertambah?

SSELECT quarters,
       count(DISTINCT customerID) AS total_customers
FROM (
     SELECT customerID, createDate,
QUARTER(createDate) AS quarter
     FROM customers
     WHERE createDate BETWEEN '2004-01-01' AND '2004-06-30'
) AS table_b
GROUP BY 1;

Total customer by quarter

6. PROBLEM STATEMENT 4

Seberapa banyak customers tersebut yang sudah melakukan transaksi?

Dari customer yang sudah registrasi kita akan lihat yang sudah melakukan transaksi

SELECT quarters,
       count(DISTINCT customerID) AS total_customers
FROM (
      SELECT customerID, createDate,
  QUARTER(createDate) as quarter
 FROM customers
      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 table_b
GROUP BY 1;

Total customer did transactions by quarter

% Customer Did Transactions = Sum(customer did transaction by quarter) / sum(customer by quarter) * 100 (%)% Customer Did Transactions = Sum(customer did transaction by quarter) / sum(customer by quarter) * 100 (%)

% Customer Did Transactions = (25 + 19) * 100 / (43 + 35) = 56,41%

Berdasarkan perhitungan di atas, persentase nasabah yang melakukan transaksi sebesar 56,41% atau lebih dari separuh Customer yang melakukan transaksi.

7. PROBLEM STATEMENT 5

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

SELECT * FROM (
       SELECT categoryID,
       count(DISTINCT orderNumber) AS total_order,
       sum(quantity) AS total_sales
       FROM (
            SELECT substr(productCode, 1, 3) AS categoryID, orderNumber, quantity, status
            FROM orders_2
            WHERE status = 'Shipped'
       ) AS table_c
GROUP BY 1
) US c
ORDER BY 2 DESC;

most product bought by customer

8. PROBLEM STATEMENT 6

Seberapa banyak customers yang tetap aktif bertransaksi?

SELECT COUNT(DISTINCT customerID) as total_customers FROM orders_1;

total customer first quarter

dan sekarang kita hitung berapa pelanggan yang tetap aktif bertransaksi setelah transaksi pertama

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

persentase pelanggan yang masih aktif membeli setelah transaksi pertama

Berdasarkan data, dari 25 nasabah yang melakukan transaksi di kuartal pertama, 24% tetap aktif di kuartal kedua.

9. Summary

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

• 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 behaviour 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-2 tidak kembali melakukan order di quarter ke-2 (no repeat order).
• xyz.com mengalami pertumbuhan negatif di quarter ke-1 dan perlu melakukan banyak improvement baik itu di sisi produk dan bisnis marketing, jika ingin mencapai target dan positif growth di quarter ke-2. Rendahnya retention rate dan conversion rate bisa menjadi diagnosa awal bahwa customer tidak tertarik/kurang puas/kecewa berbelanja di xyz.com.