TL;DR
Biaya kirim yang bocor paling gampang ketahuan kalau kamu hitung ongkir bersih per pesanan, yaitu ongkir yang kamu bayar ke kurir dikurangi ongkir yang dibayar pembeli. Susun datanya per pesanan, tambahin kolom kota, berat, dan kurir, lalu pakai SUMIFS dan COUNTIFS buat lihat kota mana yang paling banyak nyedot subsidi.
Biaya kirim yang bocor paling gampang ketahuan dari satu kolom: ongkir bersih per pesanan, yaitu ongkir yang kamu bayar ke kurir dikurangi ongkir yang dibayar pembeli.
Angka ini nggak ada di laporan penjualan manapun. Harus kamu hitung sendiri, dan biasanya hasilnya bikin kaget.
Aku pakai data 3.412 pesanan dari seller perlengkapan dapur yang jualan di dua marketplace sepanjang 2025. Semua hitungannya di spreadsheet, tanpa alat tambahan.
Satu baris sama dengan satu pesanan. Sepuluh kolom ini yang bikin analisisnya jalan:
| Kolom | Isi | Contoh |
|---|---|---|
| no_pesanan | ID unik | INV-2025-08841 |
| tanggal | Tanggal pesanan | 2025-08-14 |
| kota_tujuan | Kota, distandarkan | SURABAYA |
| zona | Zona tarif kurir | Jawa Timur |
| kurir | Nama kurir | JNE REG |
| berat_kg | Berat tertagih | 2,4 |
| nilai_barang | Harga barang tanpa ongkir | 187000 |
| ongkir_dibayar_pembeli | Yang masuk ke kamu | 18000 |
| ongkir_ke_kurir | Yang kamu keluarkan | 26000 |
| hpp | Modal barang | 121000 |
Kolom yang paling sering nggak ada itu ongkir_ke_kurir. Banyak seller cuma nyatet ongkir yang ditagih ke pembeli, jadi selisihnya nggak pernah kelihatan.
Kolom kota_tujuan wajib distandarkan. Di data mentah tadi, Surabaya ditulis 7 versi: Surabaya, SURABAYA, Sby, Surabaya Kota, dan tiga varian lain dengan spasi ekstra. Rapikan pakai fungsi TEXT atau UPPER plus TRIM sebelum ngitung apapun.
Tambahin tiga kolom hitungan di sebelah kanan:
K2: =I2-H2
ongkir_bersih = ongkir_ke_kurir - ongkir_dibayar_pembeli
L2: =G2-J2-K2
margin_bersih = nilai_barang - hpp - ongkir_bersih
M2: =IFERROR(L2/G2, 0)
persen_margin = margin_bersih / nilai_barangKolom ongkir_bersih yang jadi bintangnya. Kalau isinya positif, kamu nombok. Kalau nol, ongkir ditanggung penuh pembeli.
IFERROR di kolom M dipakai buat jaga-jaga kalau ada pesanan yang nilai barangnya nol karena hadiah atau salah input.
Bikin tabel ringkasan di sheet baru. Kolom pertama isinya daftar kota unik, lalu tiga rumus ini:
Jumlah pesanan:
=COUNTIF(data!$C:$C, $A2)
Total subsidi ongkir:
=SUMIF(data!$C:$C, $A2, data!$K:$K)
Total margin bersih:
=SUMIF(data!$C:$C, $A2, data!$L:$L)Kalau kamu mau motong per periode juga, naik ke SUMIFS:
=SUMIFS(data!$K:$K,
data!$C:$C, $A2,
data!$B:$B, ">="&DATE(2025,7,1),
data!$B:$B, "<="&DATE(2025,12,31))Buat rata-rata subsidi per pesanan, bagi total subsidi sama jumlah pesanan. Angka ini yang paling berguna, soalnya kota besar otomatis punya total subsidi besar cuma karena pesanannya banyak.
Setelah semua dihitung, ini yang keluar dari datanya.
Total subsidi ongkir setahun: Rp41.870.000. Dari total omzet Rp1.284.000.000, artinya 3,26 persen omzet habis buat nombokin ongkir.
Yang bikin kaget bukan totalnya, tapi sebarannya:
| Zona | Pesanan | Subsidi per pesanan | Margin bersih rata-rata |
|---|---|---|---|
| Jabodetabek | 1.647 | Rp4.100 | 18,4% |
| Jawa non-Jabodetabek | 1.129 | Rp9.800 | 14,1% |
| Sumatera | 412 | Rp21.600 | 6,8% |
| Kalimantan dan timur | 224 | Rp38.400 | -2,3% |
Baris terakhir itu masalahnya. Pesanan ke Kalimantan dan Indonesia timur rata-rata rugi Rp4.200 per pesanan setelah semua biaya. Jumlahnya cuma 6,6 persen dari total pesanan, tapi nyedot Rp8.600.000 subsidi setahun.
Kenapa bisa gitu? Karena flat rate ongkir yang diset di marketplace disamain buat semua zona, sementara tarif kurirnya beda tiga kali lipat.
Temuan kedua yang lebih halus. Aku pisahin pesanan berdasarkan berat tertagih, dan pesanan di rentang 1,1 sampai 1,5 kg punya subsidi rata-rata 62 persen lebih tinggi dari pesanan 0,6 sampai 1,0 kg.
Penyebabnya kemasan. Barang yang beratnya 1,05 kg dibulatkan kurir jadi 2 kg. Setelah kemasan diganti jadi lebih ringan 90 gram, 213 pesanan per bulan turun satu tingkat tarif. Hemat sekitar Rp2.130.000 per bulan dari satu perubahan kardus.
Untuk cek sebaran subsidi yang lebih jujur, pakai MEDIAN juga, bukan cuma rata-rata. Beberapa pesanan besar bisa narik rata-rata jauh dari kenyataan sehari-hari.
Lima angka ini cukup buat satu halaman ringkasan bulanan. Kalau kamu mau nyusunnya jadi dashboard, tentuin dulu mana yang jadi metrik utama biar nggak semua ditampilin sekaligus.
1. Nyampur ongkir ke dalam omzet. Kalau ongkir dari pembeli dihitung sebagai penjualan, margin kamu kelihatan lebih besar dari kenyataannya. Pisahin dari awal.
2. Pakai berat asli, bukan berat tertagih. Kurir nagih berdasarkan yang lebih besar antara berat asli dan berat volume. Selisih ini yang bikin hitungan meleset.
3. Nggak standarin nama kota. COUNTIF bakal ngitung Surabaya dan SURABAYA sebagai dua kota berbeda kalau ada spasi ekstra atau singkatan. Rapikan dulu, cek soal kualitas data.
4. Ngukur pakai rata-rata doang. Satu pesanan grosir 40 kg bisa naikin rata-rata subsidi seluruh kota. Pakai median buat pembanding.
5. Lupa biaya balik dan retur. Paket yang dikembalikan kena ongkir dua arah. Kalau ini nggak masuk hitungan, kota dengan tingkat retur tinggi kelihatan lebih untung dari aslinya.
6. Nge-set flat rate ongkir satu angka buat semua zona. Ini penyebab paling sering margin negatif di luar Jawa. Bikin minimal tiga tingkat tarif.
7. Nggak nyimpen data tarif per periode. Waktu kurir naikin tarif di tengah tahun, kamu perlu tau sebelum dan sesudahnya biar bisa bandingin.
Dari laporan pesanan yang bisa kamu unduh di dashboard seller. Biasanya formatnya CSV atau Excel dan udah ada kolom nomor pesanan, kota tujuan, berat, dan biaya kirim. Yang sering nggak ada di situ adalah ongkir yang kamu bayar ke kurir kalau kirimnya di luar sistem marketplace, jadi catat manual di kolom terpisah.
Berat volume adalah berat hitungan berdasarkan ukuran paket, bukan berat aslinya. Kurir pakai yang lebih besar antara berat asli dan berat volume. Barang ringan tapi besar kayak tisu atau bantal sering kena tarif jauh di atas berat timbangannya. Kalau margin kamu tipis, kolom dimensi paket wajib ada di spreadsheet.
Tergantung kenaikan pesanannya nutup subsidinya atau nggak. Cara ngeceknya: hitung margin bersih per pesanan sebelum dan sesudah program, lalu kali jumlah pesanannya. Kalau margin per pesanan turun Rp3.000 tapi jumlah pesanan naik 40 persen, biasanya masih untung. Kalau pesanan cuma naik 5 persen, itu rugi yang dibungkus grafik naik.
Bikin tabel tarif per kurir per zona, lalu pakai INDEX MATCH buat narik tarif termurah per pesanan. Setelah itu bandingin sama ongkir yang kamu bayar sekarang. Selisihnya itu potensi hemat. Jangan lupa masukin faktor lama kirim dan tingkat paket rusak, karena kurir termurah nggak selalu paling menguntungkan.
Sebulan sekali cukup buat kebanyakan seller, dan wajib diulang tiap kali kurir naikin tarif atau kamu ganti kemasan. Ukuran kemasan yang berubah sedikit bisa naikin berat volume dan bikin banyak pesanan naik satu tingkat tarif. Perubahan kayak gitu baru kelihatan di angka setelah 2 sampai 3 minggu.
Tiga hal yang bisa kamu kerjain minggu ini:
Buat rujukan cara ngitung berat volume, ketentuan resminya bisa dicek di halaman layanan pengiriman JNE. Tiap kurir punya rumus pembagi yang beda, jadi cek yang kamu pakai.
Kalau kamu mau lanjut ke sisi grafiknya, aku bahas cara nampilin tren bulanan yang jujur di panduan visualisasi time series.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Punya ratusan angka dan mau tau sebarannya masuk ke rentang mana aja? FREQUENCY ngitung distribusi frekuensi sekali jalan, tanpa nulis COUNTIFS berkali-kali.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.