Bagian 6: Menyiapkan Tabel Analitik

Fact table pada grain item pesanan adalah gudang serba bisa, tetapi dashboard tidak boleh men-agregasi 33 baris setiap kali kartu KPI dibuka — apalagi ketika datanya sudah jutaan baris. Pada bagian ini kita menyiapkan lapisan serving: empat tabel mkt_* yang sudah teragregasi sesuai pertanyaan bisnis Bagian 1, lalu mengekspornya sebagai CSV pada data/gold/ untuk konsumen yang tidak membaca DuckDB. Pola ini persis pembahasan chapter Serving Data untuk Analytics dan Data Marts: antarmuka konsumsi dipisahkan dari tabel inti agar query dashboard tetap ringkas dan stabil.

Tiga Mart dari Berkas Model #

Dua mart pertama menjawab pertanyaan tren dan kontribusi. models/mkt_gmv_harian.sql mengagregasi per tanggal:

sql
-- Mart GMV harian: satu baris per tanggal, hanya pesanan selesai.
-- Grain agregat ini dipilih karena dashboard menampilkan garis tren harian;
-- detail per item tetap tersedia pada fct_pesanan untuk metrik baru.
SELECT
    tanggal,
    SUM(total) AS gmv,
    COUNT(DISTINCT pesanan_id) AS jumlah_pesanan
FROM fct_pesanan
WHERE is_selesai
GROUP BY tanggal
ORDER BY tanggal

models/mkt_kategori.sql menambahkan kontribusi persen lewat window function di atas agregat:

sql
-- Mart kategori: kontribusi tiap kategori produk terhadap GMV.
-- kontribusi_persen dihitung dengan window function di atas agregat
-- (SUM(SUM(total)) OVER ()) sehingga total GMV tidak perlu di-hardcode.
SELECT
    kategori,
    SUM(kuantitas) AS jumlah_item,
    SUM(total) AS gmv,
    COUNT(DISTINCT pesanan_id) AS jumlah_pesanan,
    ROUND(SUM(total) * 100.0 / SUM(SUM(total)) OVER (), 1) AS kontribusi_persen
FROM fct_pesanan
WHERE is_selesai
GROUP BY kategori
ORDER BY gmv DESC

Perhatikan dua hal pada model kategori tersebut. Pertama, WHERE is_selesai diterapkan sebelum agregasi sehingga kontribusi dihitung dari GMV bersih, bukan dari seluruh transaksi. Kedua, SUM(SUM(total)) OVER () menjumlahkan hasil agregasi lintas grup — total GMV tidak ditulis manual sebagai angka tetap, sehingga mart tetap benar berapa pun data yang mengalir.

Mart ketiga, models/mkt_funnel.sql, menghitung perjalanan belanja dari event web. Funnel dihitung per sesi (COUNT(DISTINCT sesi_id)), bukan per event, agar satu sesi yang melihat lima produk tidak menggelembungkan tahap lihat. Urutan tahap dipetakan lewat CASE menjadi kolom urutan, dan persentase dihitung terhadap jumlah sesi tahap pertama.

Satu Mart dari Query Terprogram #

Mart keempat, mkt_pelanggan, ditulis sebagai query pada src/marts.py — bukan berkas model — untuk memperlihatkan kedua pendekatan berdampingan:

python
SQL_MKT_PELANGGAN = """
SELECT
    pelanggan_id,
    COUNT(DISTINCT pesanan_id) AS jumlah_pesanan,
    SUM(total) AS total_belanja,
    MAX(tanggal) AS terakhir_belanja,
    ROW_NUMBER() OVER (ORDER BY SUM(total) DESC, pelanggan_id) AS peringkat
FROM fct_pesanan
WHERE is_selesai
GROUP BY pelanggan_id
ORDER BY peringkat
"""

Kolom peringkat memakai ROW_NUMBER() dengan tie-breaker pelanggan_id sehingga peringkat deterministik: dua pelanggan dengan belanja sama tidak pernah bertukar posisi antar eksekusi. Definisi pelanggan aktif dari Bagian 1 — minimal satu pesanan selesai — otomatis terpenuhi karena query hanya mengagregasi baris is_selesai.

Lapisan Gold: Ekspor CSV #

Fungsi ekspor_gold pada src/marts.py menyalin keempat mart menjadi CSV:

python
def ekspor_gold(con, direktori_gold):
    """Ekspor setiap tabel mart sebagai CSV pada direktori gold.

    Lapisan gold membuat hasil pipeline dapat dikonsumsi alat yang tidak
    membaca DuckDB: spreadsheet, skrip ad-hoc, hingga alat BI ringan.
    """
    direktori_gold = Path(direktori_gold)
    direktori_gold.mkdir(parents=True, exist_ok=True)
    berkas = []
    for tabel in TABEL_GOLD:
        tujuan = direktori_gold / f"{tabel}.csv"
        con.execute(
            f"COPY (SELECT * FROM {tabel}) TO '{tujuan}' (HEADER, DELIMITER ',')"
        )
        berkas.append(tujuan)
    return berkas

Perintah COPY ... TO milik DuckDB menuliskan hasil query langsung ke CSV tanpa melewati Python, sehingga ekspor secepat eksekusi query itu sendiri. Isi data/gold/mkt_gmv_harian.csv hasil pipeline:

csv
tanggal,gmv,jumlah_pesanan
2025-01-02,347000,2
2025-01-03,280000,2
2025-01-04,205000,1
2025-01-05,175000,1
2025-01-06,246000,2
2025-01-07,160000,1
2025-01-08,160000,1
2025-01-09,440000,2
2025-01-10,410000,2
2025-01-11,180000,1
2025-01-12,306000,2
2025-01-13,365000,2
2025-01-14,175000,1
2025-01-15,477000,2

Hasil Materialisasi #

Skrip berikut membaca seluruh mart hasil pipeline dari data/analytics.duckdb:

python
import duckdb

con = duckdb.connect("data/analytics.duckdb", read_only=True)

print("mkt_funnel:")
for baris in con.execute(
    "SELECT tahap, jumlah_event, jumlah_sesi, persen_dari_view FROM mkt_funnel ORDER BY urutan"
).fetchall():
    print(f"  {baris[0]:15s} event {baris[1]:>3d}  sesi {baris[2]:>3d}  {baris[3]:>5.1f}%")

print("mkt_pelanggan (5 teratas):")
for baris in con.execute(
    "SELECT peringkat, pelanggan_id, jumlah_pesanan, total_belanja, terakhir_belanja "
    "FROM mkt_pelanggan ORDER BY peringkat LIMIT 5"
).fetchall():
    print(f"  #{baris[0]} {baris[1]}  {baris[2]} pesanan  {baris[3]:>9d}  terakhir {baris[4]}")
con.close()

Output yang Dihasilkan:

text
mkt_funnel:
  view_product    event  42  sesi  42  100.0%
  add_to_cart     event  32  sesi  32   76.2%
  begin_checkout  event  20  sesi  20   47.6%
  purchase        event  18  sesi  18   42.9%
mkt_pelanggan (5 teratas):
  #1 PLG-009  3 pesanan     496000  terakhir 2025-01-10
  #2 PLG-001  3 pesanan     462000  terakhir 2025-01-05
  #3 PLG-002  2 pesanan     455000  terakhir 2025-01-09
  #4 PLG-005  2 pesanan     425000  terakhir 2025-01-15
  #5 PLG-011  2 pesanan     392000  terakhir 2025-01-15

Angka-angka tersebut menjawab pertanyaan Bagian 1 secara langsung: dari 42 sesi yang melihat produk, 18 berujung pembelian (42,9%), dan pelanggan paling bernilai adalah PLG-009 dengan total belanja Rp496.000 dari tiga pesanan. Lapisan gold terisi lima berkas: empat CSV mart plus validation_report.json dari Bagian 7.

Keempat mart kini berisi angka final, tetapi siapa yang memastikan angka tersebut benar? Bagian 7 menjawab dengan delapan pemeriksaan validasi yang menghitung ulang semuanya lewat jalur independen.