Forensic Analytics dengan Pandas - The Largest Subset Test

The Largest Subset dapat digunakan untuk mendeteksi kesalahan dan fraud, yaitu dengan menampilkan kelompok data dengan nilai tertinggi. Tidak semua data dengan nilai tertinggi merupakan hasil dari kesalahan atau fraud. Namun dalam kasus fraud, pelaku fraud cenderung untuk menjadi tamak dan tidak tahu kapan waktunya berhenti sehingga ia akan sampai pada titik dimana hasil “kreativitasnya” menjadi cukup besar untuk muncul sebagai large subset.

Akan Dibahas hide

1 The Largest Subset Test

2.1.1 Load library dan data

2.1.2 Kelompokkan berdasarkan CustomerID

2.1.3 Tambahkan Kolom Jumlah Transaksi

2.1.4 Kolom Nama Customer

2.2 Selanjutnya

3 Referensi

Kode Lima Detik

import pandas as pd

sales = pd.read_csv('AW/Sales.SalesOrderHeader.csv', sep='\t')
customer = pd.read_csv('AW/Sales.Customer.csv', sep='\t')
person = pd.read_csv('AW/Person.Person.csv', sep='\t')

salesgroup2 = sales.groupby('CustomerID').\
    agg({'SalesOrderID': 'count', 'SubTotal': 'sum'}).\
    reset_index().\
    rename(columns={'SalesOrderID': 'Count'})

salesgroup2 = salesgroup2.merge(customer[['CustomerID', 'PersonID']], on='CustomerID')
salesgroup2 = salesgroup2.merge(person[['BusinessEntityID', 'PersonType', 'FirstName', 'MiddleName', 'LastName']], left_on='PersonID', right_on='BusinessEntityID')

salesgroup2['CustomerName'] = salesgroup2['FirstName'] + ' ' + salesgroup2['LastName']

salesgroup2.sort_values(by='SubTotal', ascending=False).head(20)[['CustomerName', 'Count', 'SubTotal']]

The Largest Subset Test

Metode ini menggunakan setidaknya dua kolom yaitu kolom kelompok/subset dan kolom nilai. Subset sendiri berarti himpunan bagian dari himpunan lain. Kolom kelompok ini dapat berupa penjual, tenaga pemasar dan lainnya yang digabungkan. Kolom nilai berupa jumlah nilai transaksi per masing-masing kelompok. Jika dibutuhkan dapat pula ditambahkan kolom ketiga berupa banyaknya transaksi per masing-masing kelompok.

Pada tataran praktek, metode ini menggunakan groupby, count (opsional) dan sum pada SQL maupun pandas.

Tujuan

Berbeda dengan tulisan-tulisan sebelumnya, kali ini kita akan menggunakan data AdventureWorks. Penulis telah mengekspor database tersebut menjadi berkas csv karenanya saat me-load data, penulis akan menggunakan read_csv. Jika pembaca ingin meload data langsung dari MSSQL dapat mengikuti langkah di sini.

Informasi akan ditampilkan seperti ini.

Urgensi

Dari Forensic Analytics-nya Pak Nigrini di halaman 193, beberapa temuan menarik yang didapatkan setelah menggunakan The largest-subsets test adalah.

Pengujian yang dilakukan pada pembayaran lembur perusahaan menunjukkan terdapat 1.300 jam lembur dalam satu tahun yang terjadi karena pegawai memanipulasi sistem. Bahkan jika durasi tersebut bukan disebabkan kegiatan yang melanggar hukum, temuan tersebut tetap menarik karena tingginya jam lembur dapat menjadi bahan evaluasi bagi manajemen kepegawaian.
Perusahaan memproses 100.000 transaksi pengiriman/bulan. Lebih lanjut diketahui bahwa biaya transaksi tersebut sebenarnya dibebankan kepada pelanggan. Transaksi tersebut diproses secara otomatis oleh sistem bukan diinput manual oleh pegawai. Meski tidak merugikan secara langsung namun tambahan 100.000 data/bulan membuat database menjadi lebih besar sehingga pemrosesan membutuhkan sumberdaya komputasi yang lebih besar.
Pengujian dilakukan pada cek yang dikeluarkan untuk penyedia jasa menghasilkan temuan cek yang ditulis untuk 2 vendor tiap bulan. Masing-masing cek bernilai USD100 sehingga tiap tahun terdapat 24 cek sejumlah total USD2.400 yang diproses satu per satu. Dalam konteks kekiniyan seperti memproses tagihan telepon selular masing-masing pegawai satu per satu.
Pengujian pada data reimburse pegawai menemukan satu pegawai mendapat nilai sebesar USD620.000 dalam satu tahun. Pemeriksaan lebih lanjut diketahui bahwa terdapat 1 cek senilai USD608.000 yang salah ditujukan pada pegawai tersebut. Baiknya adalah cek tersebut belum diberikan kepada pegawai bersangkutan saat kesalahan diketahui. Investigator menyarankan agar sistem dilengkapi dengan mekanisme untuk menandai nilai berpotensi keliru sebelum dicetak.
Analisa dilakukan pada biaya perawatan kendaraan perusahaan menunjukkan penyedia aki mobil muncul di posisi atas. Investigator kemudian menghitung berapa banyak aki yang digunakan pada tiap mobil, ternyata masing-masing mobil menggunakan dua aki per tahun.

Langkah Kerja

Data yang digunakan adalah dari tabel Sales.SalesOrderHeader. Tabel ini memiliki 31.465 baris dan 27 kolom. Kita hanya akan menggunakan kolom CustomerID yang menunjukkan pembeli dan SubTotal yang berisi nilai total penjualan sebelum pajak dan ongkir.

Data dalam bentuk csv dapat diakses di sini.

Kode

Load library dan data

import pandas as pd

sales = pd.read_csv('AW/Sales.SalesOrderHeader.csv', sep='\t')

Karena kolom yang banyak, sebagian kolom tidak ditampilkan. Untuk melihat kolom apa saja yang ada, dapat menggunakan fungsi tranpose (disimbolkan dengan huruf T besar) untuk membalik kolom menjadi baris.

sales.head(1).T

Kelompokkan berdasarkan CustomerID

salesgroup = sales[['CustomerID', 'SubTotal']].groupby('CustomerID', as_index=False).sum()

Urutkan berdasarkan SubTotal kemudian tampilkan 20 tertinggi.

salesgroup.sort_values(by='SubTotal', ascending=False).head(20)

Tambahkan Kolom Jumlah Transaksi

Agar lebih informatif kita dapat menambahkan kolom jumlah transaksi (count) selain total nilai transaksi (sum).

salesgroup2 = sales.groupby('CustomerID').\
    agg({'SalesOrderID': 'count', 'SubTotal': 'sum'}).\
    reset_index().\
    rename(columns={'SalesOrderID': 'Count'})

Kolom Nama Customer

CustomerID sepertinya tidak berarti apa-apa untuk kita karena hanya terdiri dari angka. Agar lebih informatif, kita akan menambahkan kolom nama pelanggan.

Skema database Adventureworks dapat dilihat pada gambar ini. Berdasarkan jawaban ini untuk mendapatkan nama pelanggan, kita perlu menghubungkan Sales.Customer (kolom PersonID) dan tabel Person.Person (kolom BusinessEntityID)

Langkah pertama adalah me-load data customer dan menggabungkannya dengan data salesgroup2.

customer = pd.read_csv('AW/Sales.Customer.csv', sep='\t')
salesgroup2 = salesgroup2.merge(customer[['CustomerID', 'PersonID']], on='CustomerID')

Selanjutnya me-load data person dan menggabungkan dengan variabel salesgroup2.

person = pd.read_csv('AW/Person.Person.csv', sep='\t')
salesgroup2 = salesgroup2.merge(person[['BusinessEntityID', 'PersonType', 'FirstName', 'MiddleName', 'LastName']], left_on='PersonID', right_on='BusinessEntityID')

Jika diperhatikan pada kode di atas, pada data customer hanya diambil dua kolom yaitu CustomerID dan PersonID. Sedang pada data person diambil lima kolom yaitu BusinessEntityID, PersonType, FirstName, MiddleName dan LastName. Hal tersebut semata hanya agar tidak terlalu banyak kolom pada hasil akhir karenanya pilihan kolom tersebut dapat disesuaikan dengan kebutuhan atau kondisi yang ditemui.

Selanjutnya dibuat sebuah kolom bernama CustomerName yang berisi gabungan nama depan dan nama belakang pelanggan.

salesgroup2['CustomerName'] = salesgroup2['FirstName'] + ' ' + salesgroup2['LastName']

Kode terakhir adalah untuk mengurutkan data berdasar kolom SubTotal, menampilkan 20 data tertinggi dan menampilkan hanya kolom informasi utama.

salesgroup2.sort_values(by='SubTotal', ascending=False).head(20)[['CustomerName', 'Count', 'SubTotal']]

Selanjutnya

Kedua puluh pelanggan dengan nilai transaksi tertinggi di atas dapat diuji lebih lanjut, misal dengan melihat alamat apakah terdapat pelanggan dengan alamat yang sama.

Jika dua puluh pelanggan tersebut merupakan pelanggan dengan transaksi yang valid maka sampel dapat ditambah, misal menjadi 30 pelanggan teratas

Referensi

Cover Image by 3974931 from Pixabay

Forensic Analytics dengan Pandas – The Largest Subset Test