Home
Posts

BI Analyst Case Study: Optimizing Revenue Logic & Digital Gold Dashboard Strategy

BI Analyst Case Study: Optimizing Revenue Logic & Digital Gold Dashboard Strategy

Solving Data Overlap Issues & Designing Investment Monitoring Tools

Industry: Fintech / Wealth Management Role: BI Analyst

Sebagai Business Intelligence Analyst di sebuah platform Wealth-Tech (Manajemen Aset), saya menangani dua tantangan utama: (1) Pembersihan Data Pendapatan untuk polis yang tumpang tindih, dan (2) Perancangan Matriks untuk Dashboard Strategis untuk peluncuran produk Emas Digital.

Part 1: The Data Logic Challenge

1. Raw Data Structure & The Problem

Perusahaan memiliki produk Asset Protection. Struktur data mentah mencatat setiap pembaruan paket sebagai baris baru. Tantangannya adalah menentukan mana paket yang harus dijumlahkan (karena masih aktif) dan mana yang harus dibuang (karena sudah expired). Rank menunjukan urutan order per user. Start Date adalah tanggal awal polis berlaku, sedangkan End Date adalah tanggal kadaluarsa polis.

Sample Raw Data (CSV Input)

Perhatikan User 9027429831 (Baris 1 & 2):

user_id order_rank date_start date_end premium
9027... 1 01/01/23 31/03/23 50,000
9027... 2 01/02/23 30/04/23 600,000
9027... 3 01/03/23 31/05/23 45,000
The Issue: Handling Expiry Logic

Kasus Overlap (Februari):
Saat Order 2 mulai (1 Feb), Order 1 (berakhir 31 Mar) MASIH AKTIF. Secara bisnis, premi keduanya harus DIJUMLAHKAN (Accumulated Coverage).

Target Output:
50,000 + 600,000 = 650,000
(Hasil disimpan sebagai kolom Active Premium)

Masalah SQL Biasa:
Query SUM() standar tanpa filter tanggal akan menjumlahkan SEMUA riwayat, termasuk paket yang sudah expired (misal order tahun lalu).

Objective: Membuat logika SQL yang cerdas: Hanya menjumlahkan order lama JIKA tanggalnya belum berakhir.

2. The SQL Solution

Saya menggunakan STR_TO_DATE untuk membandingkan date_end order lama dengan date_start order baru.

/* OBJECTIVE: Sum premiums ONLY if the policy is still active (Not Expired) */

SELECT 
    date_start, 
    date_end, 
    user_id, 
    order_rank, 
    premium,
    (
        SELECT SUM(premium)
        FROM dummy_data sub
        WHERE 
            sub.user_id = main.user_id 
            AND sub.order_rank <= main.order_rank
            AND STR_TO_DATE(sub.date_end, '%d/%m/%Y') >= STR_TO_DATE(main.date_start, '%d/%m/%Y')
    ) AS active_premium,
    CASE 
        WHEN (
            SELECT SUM(premium)
            FROM dummy_data sub
            WHERE 
                sub.user_id = main.user_id 
                AND sub.order_rank <= main.order_rank
                AND STR_TO_DATE(sub.date_end, '%d/%m/%Y') >= STR_TO_DATE(main.date_start, '%d/%m/%Y')
        ) >= 300000 THEN 'TRUE' 
        ELSE 'FALSE' 
    END AS flag
FROM 
    dummy_data main 
ORDER BY 
    user_id ASC, 
    order_rank ASC;
Untuk menghitung active_premium, saya menggunakan pendekatan Correlated Subquery. Metode ini menghitung total (sum) untuk setiap baris spesifik dengan cara menelusuri riwayat user. Dalam struktur query tersebut, istilah main dan sub digunakan sebagai nama alias untuk tabel yang sama (dummy_data) agar sistem dapat membedakan peran baris saat melakukan operasi perbandingan data (self-join). Alias main (pada outer query) merepresentasikan baris data 'saat ini' yang sedang diproses atau dihitung, bertindak sebagai titik acuan waktu. Sementara itu, alias sub (pada subquery) merepresentasikan daftar riwayat transaksi yang dipindai untuk mencari data pendukung yang relevan dengan baris main tersebut. Logika filter (WHERE clause) dalam correlated subquery ini kemudian dirancang dengan tiga lapisan validasi untuk memastikan perhitungan Active Premium yang akurat.

Pertama, kondisi sub.user_id = main.user_id berfungsi untuk mengisolasi data, memastikan bahwa agregasi premi hanya dilakukan terhadap riwayat transaksi milik pengguna yang sama (tidak bercampur dengan pengguna lain). Kedua, kondisi sub.order_rank <= main.order_rank menetapkan batasan kronologis (running window), di mana perhitungan hanya boleh memperhitungkan transaksi yang terjadi di masa lalu hingga saat ini, tanpa melihat data masa depan. Terakhir dan yang paling krusial, kondisi sub.date_end >= main.date_start berfungsi sebagai validasi status aktif; logika ini memastikan bahwa polis dari order sebelumnya hanya akan dijumlahkan apabila masa berlakunya masih aktif atau tumpang tindih (overlap) saat order yang baru dimulai, sehingga polis yang sudah kadaluarsa secara otomatis dikecualikan dari total kewajiban perusahaan.

Part 2: Digital Gold Dashboard Strategy

Business Scenario & Task

Scenario: Sebuah platform Wealth-Tech meluncurkan produk inovatif: Digital Gold. Produk ini memberdayakan pengguna untuk berinvestasi emas secara digital (Jual/Beli) mengikuti harga pasar. Bisnis mendapatkan keuntungan dari biaya transaksi (fees) setiap kali pengguna membeli atau menjual emas mereka.

The Task: Sebagai BI Analyst, saya dipercaya oleh tim bisnis untuk mengembangkan Dashboard Komprehensif. Dashboard ini ditujukan sebagai alat strategis untuk memantau dan menganalisis metrik performa produk baru tersebut.

Scope of Work (Deliverables):

  • (a) Menentukan metrik monitoring, definisi, dan kalkulasinya.
  • (b) Menjelaskan urgensi metrik terhadap performa bisnis.
  • (c) Menentukan jenis chart yang tepat dan membuat Mockup Dashboard.
A. Key Metrics Strategy

Berikut adalah metrik yang saya pilih untuk menjawab kebutuhan bisnis (Poin a & b):

  • 1. Net Fee Revenue Profitability
    Def: Total pendapatan bersih dari biaya admin (Jual + Beli).
    Formula: (Total Buy Amount * Buy Fee %) + (Total Sell Amount * Sell Fee %)
    Why: Sumber pendapatan utama. Bisnis perlu tahu apakah produk menguntungkan.
  • 2. GMV (Transaction Value) Liquidity
    Def: Total nilai uang (Rupiah) yang berputar dalam transaksi.
    Formula: SUM(Total Buy Amount) + SUM(Total Sell Amount)
    Why: Menunjukkan skala pasar (*market size*) dan likuiditas produk.
  • 3. Active Transacting Users User Growth
    Def: Jumlah user unik yang melakukan minimal 1 transaksi.
    Formula: COUNT(DISTINCT user_id)
    Why: Indikator adopsi produk. Tanpa pertumbuhan user, GMV tidak akan berkelanjutan.
  • 4. Total Transaction Count Activity
    Def: Frekuensi total transaksi (Jual + Beli).
    Formula: COUNT(Transaction_ID)
    Why: Memonitor beban sistem dan tingkat interaksi user (engagement).
  • 5. Gold AUM (Assets Under Management) Retention
    Def: Total gram emas yang tersimpan di saldo user saat ini.
    Formula: SUM(Total Grams Bought) - SUM(Total Grams Sold)
    Why: Indikator kepercayaan. Menunjukkan user menabung jangka panjang.
  • 6. Buy/Sell Ratio Sentiment
    Def: Rasio volume pembelian dibagi penjualan.
    Formula: Total Buy Volume / Total Sell Volume
    Why: Peringatan dini (*early warning*). Rasio jual tinggi = Panic Selling.
B. Visualization & Mockup

Implementasi chart yang dipilih untuk representasi visual (Poin c):

Dashboard Mockup

Mockup Dashboard Performance Digital Gold

Chart Selection Logic:
  • Scorecards: Untuk melihat performa instan (Revenue, GMV, Active Users).
  • Line Chart (Dual Axis): Memantau tren pertumbuhan Revenue & GMV dari waktu ke waktu.
  • Area Chart: Khusus untuk memantau tren pertumbuhan Gold AUM (Gramasi), untuk menonjolkan akumulasi aset.
  • Stacked Bar Chart: Membandingkan komposisi volume Buy vs Sell per bulan.

Post a Comment