Analitik Logistik dan Ongkir untuk Seller Online di Spreadsheet

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.

Kolom apa aja yang harus ada di spreadsheet?

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.

Gimana cara ngitung ongkir bersih per pesanan?

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_barang

Kolom 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.

Gimana cara lihat kota mana yang paling banyak nyedot subsidi?

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.

Contoh kasus: 3.412 pesanan sepanjang 2025

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.

Metrik apa yang perlu dipantau tiap bulan?

1. Rasio subsidi ongkir terhadap omzet. Total subsidi dibagi total omzet. Kalau lewat 4 persen, cek tarif dan kemasan.
2. Subsidi per pesanan per zona. Ini yang nunjukin zona mana yang harus disesuaikan tarifnya.
3. Persentase pesanan dengan margin negatif. Di data tadi angkanya 8,4 persen. Ini yang paling sering kelewat karena tertutup rata-rata.
4. Rata-rata berat tertagih dibanding berat asli. Kalau selisihnya di atas 25 persen, masalahnya di kemasan.
5. Biaya kirim per kurir untuk zona yang sama. Ini dasar buat negosiasi atau pindah kurir.

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.

Kesalahan umum waktu ngitung analitik ongkir

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.

FAQ

Data ongkir harus diambil dari mana kalau jualan di marketplace?

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 itu apa dan kenapa penting?

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.

Gratis ongkir bikin rugi atau untung?

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.

Gimana cara milih kurir kalau tarifnya beda-beda per kota?

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.

Berapa sering analisis ongkir ini perlu diulang?

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.

Penutup

Tiga hal yang bisa kamu kerjain minggu ini:

• Tambahin satu kolom ongkir bersih di spreadsheet pesanan kamu. Ini satu kolom yang paling banyak ngasih jawaban.
• Ringkas per zona pakai SUMIFS, lalu cek zona mana yang margin bersihnya di bawah 5 persen.
• Timbang ulang kemasan kamu. Selisih 90 gram bisa berarti satu tingkat tarif untuk ratusan pesanan.

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.