Prediksi penipuan dan analisis Geospasial untuk Codelab set data perbankan Retail

1. Pengantar

Di lab ini, Anda akan membuat model regresi linear untuk memprediksi apakah transaksi terbaru merupakan penipuan menggunakan SQL di BigQuery. Anda membuat set data pelatihan yang berisi setiap transaksi kartu dan beberapa atribut yang telah kami putuskan paling baik untuk menunjukkan penipuan, seperti jarak dari rumah pelanggan, waktu, dan jumlah transaksi.

Kemudian, Anda menggunakan BQML untuk membangun model regresi logistik guna memprediksi apakah transaksi merupakan penipuan berdasarkan data pelatihan kami. Salah satu fitur bagus dari BQ ML adalah kemampuan ini menangani overfitting, sehingga data pelatihan kita tidak akan memengaruhi performa model pada data baru. Terakhir, Anda membuat tiga contoh transaksi dengan karakteristik yang berbeda dan memprediksi apakah transaksi tersebut bersifat menipu atau tidak menggunakan model.

Yang akan Anda pelajari

Di lab ini, Anda akan mempelajari cara melakukan tugas berikut:

  • Memuat set data dari bucket Google Cloud Storage
  • Membuat data pelatihan
  • Membuat dan melatih model regresi logistik
  • Gunakan model ini untuk memprediksi apakah transaksi sampel bersifat menipu atau bukan
  • Mengidentifikasi transaksi penipuan berdasarkan kode pos menggunakan analisis geospasial

2. Memuat set data dari bucket GCS

Dalam tugas ini, Anda akan membuat set data bernama bq_demo dan memuatnya dengan data perbankan retail dari bucket GCS. Tindakan ini akan menghapus semua data yang sudah ada di tabel Anda.

Buka Cloud Shell

  1. Di Cloud Console, pada toolbar di kanan atas, klik tombol Activate Cloud Shell.

3c0c949fe3626208.pngS

  1. Setelah Cloud Shell dimuat, ketik:
bq rm -r -f -d bq_demo
bq rm -r -f -d bq_demo_shared
bq mk --dataset bq_demo
bq load --replace --autodetect --source_format=CSV bq_demo.account gs://retail-banking-looker/account
bq load --replace --autodetect --source_format=CSV bq_demo.base_card gs://retail-banking-looker/base_card
bq load --replace --autodetect --source_format=CSV bq_demo.card gs://retail-banking-looker/card
bq load --replace --autodetect --source_format=CSV bq_demo.card_payment_amounts gs://retail-banking-looker/card_payment_amounts
bq load --replace --autodetect --source_format=CSV bq_demo.card_transactions gs://retail-banking-looker/card_transactions
bq load --replace --autodetect --source_format=CSV bq_demo.card_type_facts gs://retail-banking-looker/card_type_facts
bq load --replace --autodetect --source_format=CSV bq_demo.client gs://retail-banking-looker/client
bq load --replace --autodetect --source_format=CSV bq_demo.disp gs://retail-banking-looker/disp
bq load --replace --autodetect --source_format=CSV bq_demo.loan gs://retail-banking-looker/loan
bq load --replace --autodetect --source_format=CSV bq_demo.order gs://retail-banking-looker/order
bq load --replace --autodetect --source_format=CSV bq_demo.trans gs://retail-banking-looker/trans
  1. Setelah selesai, klik X untuk menutup terminal Cloud Shell. Anda berhasil memuat set data dari bucket Google Cloud Storage.

3. Membuat data pelatihan

Kueri untuk transaksi penipuan per jenis kartu

Sebelum membuat data pelatihan, mari kita analisis bagaimana transaksi penipuan didistribusikan di antara jenis kartu. Database perbankan retail kami berisi tanda yang menunjukkan kapan pelanggan melaporkan transaksi penipuan di akunnya. Kueri ini menunjukkan jumlah transaksi penipuan berdasarkan jenis kartu.

[Poin Pembicaraan Kompetitif: Tidak seperti pesaing tertentu, BigQuery tidak mengharuskan Anda mengekspor data di data warehouse ke bucket penyimpanan, menjalankan algoritma machine learning, lalu menyalin hasilnya kembali ke database. Semua ini dapat dilakukan secara langsung, yang menjaga keamanan data dan tidak menyebabkan "kebocoran data".]

  1. Buka Konsol BigQuery:

Di Konsol Google Cloud, pilih Navigation menu > menggunakan BigQuery.

dfdbd753a900e349.png

  1. Kotak pesan Welcome to BigQuery pada Konsol Cloud akan terbuka. Kotak pesan ini menyediakan link ke panduan memulai dan catatan rilis.

Klik Selesai.

Konsol BigQuery terbuka.

2982953ee582e477.pngS

  1. Jalankan kueri di Query Editor:
SELECT c.type, count(trans_id) as fraud_transactions
FROM bq_demo.card_transactions AS t 
JOIN bq_demo.card c ON t.cc_number = c.card_number
WHERE t.is_fraud=1
GROUP BY type

Tetapi bagaimana jika kita dapat menggunakan data ini untuk memprediksi transaksi penipuan bahkan sebelum pelanggan mengetahuinya? ML bukan hanya untuk pakar. Dengan BigQuery, analis dapat menjalankan model ML kelas dunia langsung di data warehouse melalui SQL.

Membuat data pelatihan

Buat set data pelatihan yang berisi setiap transaksi kartu dan beberapa atribut yang telah kami putuskan paling baik untuk menunjukkan penipuan, seperti jarak dari rumah pelanggan, waktu, dan jumlah transaksi.

Jalankan kueri di Query Editor:

CREATE OR REPLACE TABLE bq_demo.training_data as (
  SELECT
    card_transactions.trans_id  AS trans_id,
    card_transactions.is_fraud  AS is_fraud,
    --amount for transaction: higher amounts are more likely to be fraud
    cast(card_transactions.amount as FLOAT64)  AS card_transactions_amount,
    --distance from the customers home: further distances are more likely to be fraud
    ST_DISTANCE((ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),
            (cast(card_transactions.merchant_lat as FLOAT64)))), (ST_GeogPoint((cast(SPLIT(client.address,'|')[OFFSET(4)] as float64)),
            (cast(SPLIT(client.address,'|')[OFFSET(3)] as float64)))))  AS card_transactions_transaction_distance,
    --hour that transaction occured: fraud occurs in middle of night (usually between midnight and 4 am)
    EXTRACT(HOUR FROM TIMESTAMP(CONCAT(card_transactions.trans_date,' ',card_transactions.trans_time)) ) AS card_transactions_transaction_hour_of_day
  FROM bq_demo.card_transactions  AS card_transactions
  LEFT JOIN bq_demo.card  AS card ON card.card_number = card_transactions.cc_number
  LEFT JOIN bq_demo.disp  AS disp ON card.disp_id = disp.disp_id
  LEFT JOIN bq_demo.client  AS client ON disp.client_id = client.client_id );

Di bagian "Hasil" klik "buka tabel" dan Anda akan melihat hasil berikut:

c03442924d2dc7f3.png

4. Membuat dan melatih model

Gunakan BQML untuk membangun model regresi logistik guna memprediksi apakah transaksi merupakan penipuan berdasarkan data pelatihan kami yang dibuat di langkah sebelumnya. Salah satu fitur bagus BQML adalah kemampuan ini menangani overfitting, sehingga data pelatihan tidak akan memengaruhi performa model pada data baru.

Jalankan kueri di Query Editor:

CREATE OR REPLACE MODEL bq_demo.fraud_prediction
    OPTIONS(model_type='logistic_reg', labels=['is_fraud']) AS
    SELECT * EXCEPT(trans_id)
    FROM bq_demo.training_data
    WHERE (is_fraud = 1) OR 
            (is_fraud = 0 AND rand() <= 
                (SELECT SUM(is_fraud)/COUNT(*) FROM bq_demo.training_data));

Lihat detail model

Di bagian "Results", klik "Go to Model".

9417986e0fa45427.pngS

Anda akan melihat tab Schema, Training, dan Evaluation.

Di bagian "Tab pelatihan", Anda akan melihat ini:

cb6b11a01816ba74.png

Di bagian "tab Evaluasi", Anda akan melihat ini:

ff47d42700a79544.png

Di bagian "Tab skema", Anda akan melihat ini:

edfd13025ed64705.png

5. Menggunakan model untuk memprediksi penipuan

Sekarang, mari kita gunakan model untuk memprediksi apakah suatu transaksi berpotensi menipu. Kita akan membuat 3 contoh transaksi dengan karakteristik yang berbeda.

Jalankan kueri berikut di editor kueri:

SELECT * FROM ML.PREDICT(MODEL bq_demo.fraud_prediction, (
  SELECT '001' as trans_id, 500.00 as card_transactions_amount, 600 as card_transactions_transaction_distance, 2 as card_transactions_transaction_hour_of_day
    UNION ALL
  SELECT '002' as trans_id, 5.25 as card_transactions_amount, 2 as card_transactions_transaction_distance, 13 as card_transactions_transaction_hour_of_day
    UNION ALL
  SELECT '003' as trans_id, 175.50 as card_transactions_amount, 45 as card_transactions_transaction_distance, 10 as card_transactions_transaction_hour_of_day
  ), STRUCT(0.55 AS threshold)
);

Anda akan melihat hasil seperti ini:

c3c25d3652e45c9c.png

Seperti yang Anda lihat, transaksi pertama hampir pasti adalah penipuan berdasarkan model kami, sedangkan transaksi ke-2 dan ke-3 diprediksi tidak bersifat menipu. Kami telah menetapkan batas menjadi 55%, naik dari default 50%. Kita dapat melatih model dengan data tambahan atau menambahkan atribut lain untuk meningkatkan akurasi.

6. Identifikasi transaksi penipuan berdasarkan kode pos menggunakan analisis geospasial

BigQuery memiliki dukungan yang lengkap untuk data geospasial. Berikut adalah contoh yang menggunakan fungsi GIS ST_WITHIN untuk menentukan kode pos berdasarkan lintang dan bujur transaksi penjual menggunakan set data publik batas kode pos.

Jalankan kode berikut di Query Editor:

WITH trans_by_zip as (
  SELECT
    card_transactions.trans_id,
    zip_code AS merchant_zip,
    city as merchant_city,
    county as merchant_county,
    state_name as merchant_state
  FROM
    bq_demo.card_transactions AS card_transactions,
    bigquery-public-data.geo_us_boundaries.zip_codes AS zip_codes
  WHERE ST_Within(ST_GEOGPOINT((cast(card_transactions.merchant_lon as FLOAT64)),(cast(card_transactions.merchant_lat as FLOAT64))),zip_codes.zip_code_geom)
)
SELECT merchant_zip, 1.0 * (SUM(is_fraud)) / nullif((COUNT(*)),0) AS percent_transactions_fraud
FROM bq_demo.card_transactions t, trans_by_zip
WHERE t.trans_id = trans_by_zip.trans_id
GROUP BY merchant_zip
ORDER BY percent_transactions_fraud DESC;

Anda akan melihat hasil berikut:

6f0dfb6d93c0be11.pngS

Seperti yang dapat Anda lihat, tingkat penipuan di sebagian besar kode pos relatif kecil (di bawah 2%), tetapi tingkat penipuan di 69345 adalah 11% yang mengejutkan. Mungkin ini hal yang ingin kita selidiki.