# Analisis Tabel Database `z_sales_salesman_cache` & Formula Laporan Pivot Sales

Dokumen ini berisi hasil analisis mendalam terhadap tabel database `z_sales_salesman_cache` pada database `san_13mar`. Analisis ini dirancang sebagai panduan teknis untuk membangun **Laporan Pivot Sales** (Sales Pivot Reports) yang akurat dan berkinerja tinggi.

---

## 1. Karakteristik & Struktur Tabel

Tabel `z_sales_salesman_cache` bertindak sebagai **jurnal pencatatan transaksi penjualan pada tingkat dokumen (header-level)**. Tabel ini mencakup data dari tanggal **2021-01-04** hingga **2026-03-12** dengan total **18.525 baris data** dan **8.111 transaksi unik** (`transaksi_id`).

### Kolom Penting untuk Laporan Pivot
Berikut adalah kolom-kolom kunci yang digunakan dalam visualisasi dan penghitungan pivot:

| Nama Kolom | Tipe Data | Deskripsi / Peran dalam Pivot |
| :--- | :--- | :--- |
| **`transaksi_id`** | `int(11)` | ID unik dokumen transaksi penjualan. Digunakan untuk identifikasi dan deduplikasi. |
| **`rekening`** / **`jenis`** | `varchar` | Kode tipe/tahap transaksi (contoh: `582so` = Sales Order, `582spd` = Packing List/Delivery). |
| **`seller_nama`** | `varchar` | Nama salesman yang menangani transaksi (dimensi baris/kolom pivot). |
| **`customer_nama`** | `varchar` | Nama pelanggan/customer (dimensi baris/kolom pivot). |
| **`thn`** / **`bln`** / **`tgl`** | `varchar` | Dimensi waktu transaksi untuk pengelompokan tahunan/bulanan/harian. |
| **`fulldate`** | `date` | Tanggal lengkap transaksi (format `YYYY-MM-DD`). |
| **`harga_bruto`** | `decimal` | Nilai penjualan kotor (sebelum diskon & PPN). |
| **`diskon_nilai`** | `decimal` | Total potongan harga/diskon yang diberikan pada transaksi. |
| **`ppn_nilai`** | `decimal` | Nilai PPN (Pajak Pertambahan Nilai) transaksi. |
| **`harga_netto`** | `decimal` | Nilai penjualan bersih (dasar utama omset/sales). Diperoleh dari `harga_bruto - diskon_nilai`. |
| **`ongkir_nilai`** | `decimal` | Nilai ongkos kirim. |
| **`harga_nppn`** | `decimal` | Total tagihan akhir kepada customer (`harga_netto + ppn_nilai`). |

---

## 1.5. Laporan Detail Item & Per Produk (`z_sales_pembantu_cache`)

Untuk membuat laporan pivot **Per Produk** (`produk_part` / `produk_kode` / `produk_jenis`), kita **tidak bisa** menggunakan tabel `z_sales_salesman_cache` karena tabel tersebut berada di level header (dokumen).

Sebagai gantinya, database menyediakan tabel **`z_sales_pembantu_cache`** (Subsidiary Ledger/Detail Item) yang berisi **69.015 baris data**. Tabel pembantu ini merekam baris nota secara detail dan memiliki kolom produk tambahan:

*   **`produk_kode`** & **`produk_part`**: Kode dan nama model produk (contoh: `FC-H2`).
*   **`produk_satuan`** & **`produk_jenis`**: Satuan dan jenis pengelompokan produk.
*   **`kategori_nama`**: Kategori produk.

Dengan tabel `z_sales_pembantu_cache`, Anda bisa membuat pivot:
1.  **Top Products per Salesman** (Produk terlaris yang dijual masing-masing salesman).
2.  **Top Products per Customer** (Produk yang paling sering dibeli oleh masing-masing pelanggan).
3.  **Kategori Produk Terlaris** per kuartal/tahun.

---


## 2. Temuan Penting & Aturan Bisnis (Gotchas)

> [!WARNING]
> **Adanya Literal Duplicate Rows**
> Tabel ini memiliki data duplikat mutlak (baris dengan kolom, waktu, status, dan nilai yang sama persis). Melakukan `SUM(harga_netto)` secara langsung di seluruh tabel akan menghasilkan angka yang terinflasi (jauh lebih besar dari aslinya).
> * **Solusi**: Gunakan query deduplikasi berbasis `GROUP BY transaksi_id, jenis, rekening` dengan fungsi `MAX()` untuk mengambil nilai sebenarnya per dokumen/status sebelum melakukan agregasi pivot.

> [!NOTE]
> **Lifecycle Transaksi Penjualan**
> Satu nomor transaksi (`transaksi_id`) dapat muncul beberapa kali dengan kode `rekening` yang berbeda karena merepresentasikan status alur dokumen:
> * **Pre-Order (SPO)**: Diwakili oleh `582spo` (Lokal), `382spo` (Internasional), dan `588spo` (Proyek).
> * **Confirmed Order (SO)**: Diwakili oleh `582so` (Lokal), `382so` (Internasional), dan `588so` (Proyek).
> * **Delivery/Shipped (SPD)**: Diwakili oleh `582spd` (Lokal) dan `382spd` (Internasional).
> * **Project Receipt**: Diwakili oleh `7499`.
>
> Untuk membuat laporan realisasi sales (penjualan terkonfirmasi/omset), Anda harus memfilter `rekening` pada tahap **Confirmed Order (SO)** saja untuk menghindari *double counting* lintas status.

---

## 3. Rancangan Query SQL untuk Laporan Pivot Sales

Berikut adalah query-query SQL yang telah dioptimalkan untuk memproduksi data siap-pakai di antarmuka laporan pivot:

### A. Pivot Penjualan Terkonfirmasi (SO) per Salesman per Tahun
Query ini menampilkan total penjualan bersih (`harga_netto`) masing-masing salesman yang dikelompokkan secara horizontal dari tahun 2021 hingga 2026.

```sql
SELECT 
    seller_nama AS Salesman,
    FORMAT(SUM(CASE WHEN thn = '2021' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sales 2021`,
    FORMAT(SUM(CASE WHEN thn = '2022' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sales 2022`,
    FORMAT(SUM(CASE WHEN thn = '2023' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sales 2023`,
    FORMAT(SUM(CASE WHEN thn = '2024' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sales 2024`,
    FORMAT(SUM(CASE WHEN thn = '2025' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sales 2025`,
    FORMAT(SUM(CASE WHEN thn = '2026' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sales 2026`,
    FORMAT(SUM(net_sales), 2, 'id_ID') AS `Total Sales`
FROM (
    SELECT 
        transaksi_id, 
        thn, 
        seller_nama, 
        MAX(harga_netto) AS net_sales 
    FROM z_sales_salesman_cache 
    WHERE rekening IN ('582so', '382so', '588so') 
    GROUP BY transaksi_id, thn, seller_nama
) t 
GROUP BY seller_nama 
ORDER BY SUM(net_sales) DESC;
```

### B. Analisis Pipeline Penjualan (SPO vs SO vs SPD) per Salesman
Query ini menunjukkan tahapan siklus penjualan salesman saat ini (Pre-Order vs Confirmed Order vs Shipped/Delivered). Sangat berguna untuk menganalisis konversi sales dan memantau pesanan yang belum terkirim.

```sql
SELECT 
    seller_nama AS Salesman,
    FORMAT(SUM(CASE WHEN stage = 'Pre-Order (SPO)' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Pre-Order (SPO)`,
    FORMAT(SUM(CASE WHEN stage = 'Confirmed Order (SO)' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Confirmed Order (SO)`,
    FORMAT(SUM(CASE WHEN stage = 'Shipped/Delivered (SPD)' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Shipped/Delivered (SPD)`,
    FORMAT(SUM(CASE WHEN stage = 'Project Receipt' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Project Receipt`
FROM (
    SELECT 
        transaksi_id,
        seller_nama,
        CASE 
            WHEN rekening IN ('582spo', '382spo', '588spo') THEN 'Pre-Order (SPO)'
            WHEN rekening IN ('582so', '382so', '588so') THEN 'Confirmed Order (SO)'
            WHEN rekening IN ('582spd', '382spd') THEN 'Shipped/Delivered (SPD)'
            WHEN rekening = '7499' THEN 'Project Receipt'
            ELSE 'Other'
        END AS stage,
        MAX(harga_netto) AS net_sales
    FROM z_sales_salesman_cache
    GROUP BY transaksi_id, seller_nama, stage
) t
GROUP BY seller_nama
ORDER BY SUM(CASE WHEN stage = 'Confirmed Order (SO)' THEN net_sales ELSE 0 END) DESC;
```

### C. Analisis Konsentrasi Pelanggan (Top Customers) per Salesman
Query ini menyajikan relasi pivot antara top customer dengan salesman yang berkontribusi terbesar terhadap penjualan tersebut.

```sql
SELECT 
    customer_nama AS Customer,
    FORMAT(SUM(CASE WHEN seller_nama = 'Adiwirya Hadisaputra ' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Adiwirya Hadisaputra`,
    FORMAT(SUM(CASE WHEN seller_nama = 'Yanty ' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Yanty`,
    FORMAT(SUM(CASE WHEN seller_nama = 'Suyanto' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Suyanto`,
    FORMAT(SUM(CASE WHEN seller_nama NOT IN ('Adiwirya Hadisaputra ', 'Yanty ', 'Suyanto') THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Salesman Lainnya`,
    FORMAT(SUM(net_sales), 2, 'id_ID') AS `Total Sales`
FROM (
    SELECT 
        transaksi_id, 
        customer_nama, 
        seller_nama, 
        MAX(harga_netto) AS net_sales 
    FROM z_sales_salesman_cache 
    WHERE rekening IN ('582so', '382so', '588so') 
    GROUP BY transaksi_id, customer_nama, seller_nama
) t 
GROUP BY customer_nama 
ORDER BY SUM(net_sales) DESC 
LIMIT 15;
```

### D. Pivot Penjualan Bulanan (Bulanan Trend)
Query ini digunakan untuk memantau performa penjualan musiman (seasonal) sepanjang tahun berjalan.

```sql
SELECT 
    thn AS Tahun,
    FORMAT(SUM(CASE WHEN bln = '01' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Jan`,
    FORMAT(SUM(CASE WHEN bln = '02' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Feb`,
    FORMAT(SUM(CASE WHEN bln = '03' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Mar`,
    FORMAT(SUM(CASE WHEN bln = '04' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Apr`,
    FORMAT(SUM(CASE WHEN bln = '05' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Mei`,
    FORMAT(SUM(CASE WHEN bln = '06' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Jun`,
    FORMAT(SUM(CASE WHEN bln = '07' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Jul`,
    FORMAT(SUM(CASE WHEN bln = '08' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Agu`,
    FORMAT(SUM(CASE WHEN bln = '09' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Sep`,
    FORMAT(SUM(CASE WHEN bln = '10' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Okt`,
    FORMAT(SUM(CASE WHEN bln = '11' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Nov`,
    FORMAT(SUM(CASE WHEN bln = '12' THEN net_sales ELSE 0 END), 2, 'id_ID') AS `Des`,
    FORMAT(SUM(net_sales), 2, 'id_ID') AS `Total`
FROM (
    SELECT 
        transaksi_id, 
        thn, 
        bln, 
        MAX(harga_netto) AS net_sales 
    FROM z_sales_salesman_cache 
    WHERE rekening IN ('582so', '382so', '588so') 
    GROUP BY transaksi_id, thn, bln
) t 
GROUP BY thn 
ORDER BY thn DESC;
```

---

## 4. Cara Membuat Pivot Dinamis (Menghindari Hardcode Tahun)

Jika tabel terus bertambah dengan data tahun 2027, 2028, dan seterusnya, query SQL yang di-hardcode di atas akan membutuhkan pemeliharaan kode berkelanjutan. Untuk menghindari hal ini, terdapat dua pendekatan utama untuk membuat laporan pivot dinamis:

### Pendekatan A: Flat SQL + Pengolahan di PHP (Sangat Direkomendasikan)
Metode ini paling efisien karena membagi beban kerja: database hanya menyajikan data relasional sederhana (flat), dan pembentukan kolom horizontal dilakukan secara dinamis oleh PHP.

#### 1. SQL Query untuk mengambil data flat:
```sql
SELECT 
    seller_nama AS Salesman,
    thn AS Tahun,
    SUM(net_sales) AS total_sales
FROM (
    SELECT 
        transaksi_id, 
        thn, 
        seller_nama, 
        MAX(harga_netto) AS net_sales 
    FROM z_sales_salesman_cache 
    WHERE rekening IN ('582so', '382so', '588so') 
    GROUP BY transaksi_id, thn, seller_nama
) t 
GROUP BY seller_nama, thn
ORDER BY seller_nama ASC, thn ASC;
```

#### 2. Logika PHP (Controller & View) untuk merender tabel:
```php
// Di Controller:
$raw_data = $this->db->query($query)->result_array();

$pivot = array();
$years = array();

foreach ($raw_data as $row) {
    $salesman = $row['Salesman'];
    $year = $row['Tahun'];
    $amount = (float)$row['total_sales'];
    
    // Kumpulkan daftar tahun unik secara dinamis
    if (!in_array($year, $years)) {
        $years[] = $year;
    }
    
    // Kelompokkan data berdasarkan salesman dan tahun
    if (!isset($pivot[$salesman])) {
        $pivot[$salesman] = array();
    }
    $pivot[$salesman][$year] = $amount;
}
sort($years); // Urutkan tahun dari terkecil ke terbesar

// Di View (HTML):
echo '<table class="table">';
echo '<thead><tr><th>Salesman</th>';
foreach ($years as $yr) {
    echo '<th>Sales ' . $yr . '</th>';
}
echo '<th>Total</th></tr></thead><tbody>';

foreach ($pivot as $salesman => $sales_data) {
    echo '<tr>';
    echo '<td>' . htmlspecialchars($salesman) . '</td>';
    $row_total = 0;
    foreach ($years as $yr) {
        $amount = isset($sales_data[$yr]) ? $sales_data[$yr] : 0;
        $row_total += $amount;
        echo '<td>' . number_format($amount, 2, ',', '.') . '</td>';
    }
    echo '<td><strong>' . number_format($row_total, 2, ',', '.') . '</strong></td>';
    echo '</tr>';
}
echo '</tbody></table>';
```

---

### Pendekatan B: Menggunakan Dynamic SQL (Prepared Statements)
Jika visualisasi pivot harus diproses murni di tingkat database (misal untuk diekspor langsung oleh engine DB tanpa perantara script), kita dapat menggunakan SQL dinamis dengan perintah `PREPARE` dan `EXECUTE`:

```sql
-- 1. Kumpulkan daftar kolom tahun secara dinamis ke dalam variable
SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'FORMAT(SUM(CASE WHEN thn = ''',
      thn,
      ''' THEN net_sales ELSE 0 END), 2, ''id_ID'') AS `Sales ',
      thn,
      '`'
    )
  ) INTO @sql
FROM z_sales_salesman_cache;

-- 2. Gabungkan potongan kolom dinamis ke query utama
SET @query = CONCAT('
    SELECT 
        seller_nama AS Salesman, ', @sql, ',
        FORMAT(SUM(net_sales), 2, \'id_ID\') AS `Total Sales`
    FROM (
        SELECT 
            transaksi_id, 
            thn, 
            seller_nama, 
            MAX(harga_netto) AS net_sales 
        FROM z_sales_salesman_cache 
        WHERE rekening IN (''582so'', ''382so'', ''588so'') 
        GROUP BY transaksi_id, thn, seller_nama
    ) t 
    GROUP BY seller_nama 
    ORDER BY SUM(net_sales) DESC;
');

-- 3. Eksekusi query dinamis
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
```

---

## 5. Analisis Lanjutan & Kreatif Menggunakan Data `z_sales_*`

Selain 10 laporan visualisasi standar di blueprint, kekayaan data di tabel `z_sales_salesman_cache` (header) dan `z_sales_pembantu_cache` (detail item) memungkinkan Anda membangun analisis bisnis strategis berikut:

### 1. RFM (Recency, Frequency, Monetary) Customer Segmentation
Menganalisis perilaku belanja pelanggan untuk memetakan loyalitas mereka. 
*   **Recency (Kapan terakhir belanja):** Selisih hari antara tanggal hari ini dengan `MAX(fulldate)`.
*   **Frequency (Seberapa sering belanja):** Total transaksi unik (`COUNT(DISTINCT transaksi_id)`).
*   **Monetary (Total belanja):** Total nilai omset (`SUM(harga_netto)`).

**Query SQL RFM:**
```sql
SELECT 
    customer_nama AS Customer,
    DATEDIFF(CURRENT_DATE(), MAX(fulldate)) AS Recency_Days,
    COUNT(DISTINCT transaksi_id) AS Purchase_Frequency,
    SUM(net_sales) AS Monetary_Value
FROM (
    SELECT transaksi_id, customer_nama, fulldate, MAX(harga_netto) AS net_sales
    FROM z_sales_salesman_cache
    WHERE rekening IN ('582so', '382so', '588so')
    GROUP BY transaksi_id, customer_nama, fulldate
) t
GROUP BY customer_nama
ORDER BY Monetary_Value DESC;
```
*   *Tindakan Bisnis:* Menentukan program promo khusus untuk pelanggan ber-Monetary tinggi tapi Recency-nya sudah lama (pelanggan pasif/churn risk).

---

### 2. Analisis Konversi Pipeline & Siklus Pengiriman (SLA / Lead Time)
Mengukur efisiensi kerja salesman dalam menutup kesepakatan dan efisiensi logistik dalam mengirimkan barang menggunakan kolom waktu.
*   **Waktu Konversi Order:** Selisih `dtime_order` ke `dtime_kirim` (menghitung rata-rata hari konversi).
*   **Rasio Konversi Salesman:** Berapa banyak dari Pre-Order (SPO) yang berhasil diubah menjadi Sales Order (SO).

**Query SQL Siklus Pengiriman:**
```sql
SELECT 
    seller_nama AS Salesman,
    AVG(DATEDIFF(dtime_kirim, dtime_order)) AS Avg_Days_Order_to_Ship,
    AVG(DATEDIFF(dtime_terima, dtime_kirim)) AS Avg_Days_Ship_to_Received
FROM z_sales_salesman_cache
WHERE dtime_order IS NOT NULL AND dtime_kirim IS NOT NULL
GROUP BY seller_nama;
```

---

### 3. Market Basket Analysis / Product Association (Analisis Bundling)
Dengan tabel detail `z_sales_pembantu_cache`, Anda dapat mendeteksi produk apa saja yang sering dibeli secara bersamaan dalam satu nota (keranjang belanja) oleh customer.

**Query SQL Pencarian Asosiasi Produk:**
```sql
SELECT 
    p1.produk_part AS Produk_A,
    p2.produk_part AS Produk_B,
    COUNT(*) AS Frekuensi_Dibeli_Bersama
FROM z_sales_pembantu_cache p1
JOIN z_sales_pembantu_cache p2 
    ON p1.transaksi_id = p2.transaksi_id AND p1.produk_part < p2.produk_part
WHERE p1.rekening IN ('582so', '382so', '588so')
  AND p2.rekening IN ('582so', '382so', '588so')
GROUP BY Produk_A, Produk_B
ORDER BY Frekuensi_Dibeli_Bersama DESC
LIMIT 10;
```
*   *Tindakan Bisnis:* Membuat paket bundling produk (misal jika beli Brankas tipe X, tawarkan kunci pengaman tipe Y dengan diskon paket).

---

### 4. Analisis Pareto Penjualan (Hukum 80/20)
Menentukan siapa saja 20% entitas (salesman atau customer) yang berkontribusi terhadap 80% dari total omset perusahaan. Ini membantu manajemen memprioritaskan akun-akun penting (*Key Account Management*).

---

### 5. Analisis Customer Churn (Pelanggan yang Hilang)
Mendeteksi pelanggan yang sebelumnya rutin membeli dalam 6 bulan terakhir namun sekarang tidak ada transaksi sama sekali, sehingga tim sales dapat melakukan tindak lanjut sebelum mereka benar-benar hilang.