Analisis Penjualan, Customer & Bisnis

Studi Kasus: Analisis Bisnis Toko Online

Hari terakhir ini kita menerapkan semua yang sudah dipelajari untuk menjawab pertanyaan bisnis nyata.

Analisis 1: Dashboard Penjualan

-- Ringkasan performa penjualan bulanan
SELECT
    strftime('%Y-%m', tanggal_pesan) AS bulan,
    COUNT(*) AS jumlah_transaksi,
    SUM(total_harga) AS total_pendapatan,
    AVG(total_harga) AS rata_per_transaksi,
    COUNT(DISTINCT pelanggan_id) AS pelanggan_unik
FROM pesanan
WHERE status_pesanan = 'selesai'
GROUP BY strftime('%Y-%m', tanggal_pesan)
ORDER BY bulan;

Analisis 2: Customer Segmentation (RFM)

-- Segmentasi pelanggan berdasarkan frekuensi & nilai belanja
WITH pelanggan_stats AS (
    SELECT
        p.id, p.nama, p.kota,
        COUNT(ps.id) AS frekuensi,
        SUM(ps.total_harga) AS total_belanja,
        MAX(ps.tanggal_pesan) AS terakhir_belanja
    FROM pelanggan p
    LEFT JOIN pesanan ps ON p.id = ps.pelanggan_id AND ps.status_pesanan = 'selesai'
    GROUP BY p.id, p.nama, p.kota
)
SELECT nama, kota, frekuensi, total_belanja,
    CASE
        WHEN total_belanja > 15000000 THEN '🥇 VIP'
        WHEN total_belanja > 5000000 THEN '🥈 Premium'
        WHEN total_belanja > 0 THEN '🥉 Regular'
        ELSE '💤 Belum Pernah Beli'
    END AS segmen
FROM pelanggan_stats
ORDER BY total_belanja DESC;

Analisis 3: Produk Terlaris

-- Top 5 produk berdasarkan revenue
SELECT
    pr.nama_produk,
    k.nama_kategori,
    SUM(dp.jumlah) AS total_unit_terjual,
    SUM(dp.subtotal) AS total_revenue,
    RANK() OVER (ORDER BY SUM(dp.subtotal) DESC) AS rank_revenue
FROM detail_pesanan dp
JOIN produk pr ON dp.produk_id = pr.id
JOIN kategori k ON pr.kategori_id = k.id
JOIN pesanan ps ON dp.pesanan_id = ps.id
WHERE ps.status_pesanan = 'selesai'
GROUP BY pr.id, pr.nama_produk, k.nama_kategori
ORDER BY total_revenue DESC
LIMIT 5;