Codelab: previsão de fraudes e análise geoespacial para conjunto de dados de bancos de varejo

1. Introdução

Neste laboratório, você vai criar um modelo de regressão linear para prever se transações recentes são fraudulentas usando o SQL no BigQuery. Você cria um conjunto de dados de treinamento que contém cada transação de cartão e alguns dos atributos que decidimos que melhor indicam fraude, como distância da casa do cliente, hora do dia e valor da transação.

Depois você vai usar o BQML para criar um modelo de regressão logística e prever se uma transação é fraudulenta com base nos nossos dados de treinamento. Um dos bons recursos do BQ ML é que ele cuida do overfitting para que nossos dados de treinamento não afetem o desempenho do modelo com os dados novos. Por fim, você vai criar três transações de amostra com características diferentes e prever se são fraudulentas ou não usando o modelo.

O que você vai aprender

Neste laboratório, você aprenderá a fazer o seguinte:

  • Carregue o conjunto de dados de um bucket do Google Cloud Storage
  • Criar dados de treinamento
  • Criar e treinar um modelo de regressão logística
  • Usar o modelo para prever se as transações de amostra são fraudulentas ou não
  • Identifique transações fraudulentas por CEP usando a análise geoespacial

2. Carregar o conjunto de dados de um bucket do GCS

Nesta tarefa, você vai criar um conjunto de dados chamado bq_demo e carregá-lo com dados bancários de varejo de um bucket do GCS. Isso exclui todos os dados que já estão nas tabelas.

Abra o Cloud Shell

  1. No console do Cloud, na barra de ferramentas superior direita, clique no botão "Ativar o Cloud Shell".

3c0c949fe3626208.png

  1. Depois que o Cloud Shell for carregado, digite:
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. Quando terminar, clique no X para fechar o terminal do Cloud Shell. Você carregou um conjunto de dados de um bucket do Google Cloud Storage.

3. Criar dados de treinamento

Consultar transações fraudulentas por tipo de cartão

Antes de criarmos dados de treinamento, vamos analisar como as transações fraudulentas são distribuídas entre os tipos de cartão. Nosso banco de dados de bancos de varejo contém uma sinalização que indica quando um cliente denuncia uma transação fraudulenta em sua conta. Essa consulta mostra o número de transações fraudulentas por tipo de cartão.

[Ponto de discussão sobre a concorrência: ao contrário de alguns concorrentes, o BigQuery não exige que você exporte os dados do seu data warehouse para um bucket de armazenamento, execute algoritmos de machine learning e copie os resultados de volta para o banco de dados. Tudo isso pode ser feito no local, o que preserva a segurança dos dados e não leva à "expansão de dados".]

  1. Abra o console do BigQuery:

No console do Google Cloud, selecione o menu de navegação > no BigQuery.

dfdbd753a900e349.png

  1. A caixa de mensagem "Olá! Este é o BigQuery no console do Cloud" vai aparecer. Ela tem um link para o guia de início rápido e as notas de lançamento.

Clique em "Concluído".

O console do BigQuery é aberto.

2982953ee582e477.png

  1. Execute a consulta no Editor de consultas:
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

Mas e se pudéssemos usar esses dados para prever transações fraudulentas antes mesmo do cliente perceber? ML não é apenas para especialistas. Com o BigQuery, os analistas podem executar modelos de ML de alto nível diretamente em dados de data warehouse via SQL.

Criar dados de treinamento

Crie um conjunto de dados de treinamento que contenha cada transação de cartão e alguns dos atributos que decidimos que melhor indicam fraude, como distância da casa do cliente, hora do dia e valor da transação.

Execute a consulta no Editor de consultas:

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 );

Em "Resultados", clique em "Ir para a tabela" e este será o resultado:

c03442924d2dc7f3.png

4. Criar e treinar o modelo

Use o BQML para criar um modelo de regressão logística e prever se uma transação é fraudulenta com base nos dados de treinamento criados na etapa anterior. Um dos bons recursos do BQML é que ele cuida do overfitting para que nossos dados de treinamento não afetem o desempenho do modelo com os novos dados.

Execute a consulta no Editor de consultas:

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));

Mais detalhes do modelo

Em "Resultados", clique em "Ir para o modelo".

9417986e0fa45427.png

As guias "Esquema", "Treinamento" e "Avaliação" vão aparecer.

Na guia "Treinamento", você verá o seguinte:

cb6b11a01816ba74.png

Na guia "Avaliação", você verá o seguinte:

ff47d42700a79544.png

Na guia "Esquema", você verá o seguinte:

edfd13025ed64705.png

5. Use o modelo para prever fraudes

Agora vamos usar nosso modelo para prever se uma transação é potencialmente fraudulenta. Criaremos três transações de amostra com características diferentes.

Execute a seguinte consulta no editor de consultas:

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)
);

O resultado será assim:

c3c25d3652e45c9c.png

Como você pode ver, a primeira transação é quase certamente fraudulenta com base no nosso modelo, enquanto a segunda e a terceira transações não são fraudulentas. Definimos o limite para 55%, em vez do padrão de 50%. Podemos treinar nosso modelo com dados extras ou adicionar outros atributos para melhorar a acurácia.

6. Identifique transações fraudulentas por CEP usando a análise geoespacial

O BigQuery tem um suporte avançado para dados geoespaciais. Veja um exemplo que usa a função GIS ST_WITHIN para determinar o CEP conforme a latitude e a longitude das transações do comerciante usando um conjunto de dados públicos de limites de CEP.

Execute o seguinte código no Editor de consultas:

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;

Você vai ver os seguintes resultados:

6f0dfb6d93c0be11.png

Como você pode ver, a taxa de fraude na maioria dos códigos postais é relativamente pequena (menos de 2%), mas a taxa de fraude em 69.345 é de 11% surpreendente. Provavelmente, queremos investigar isso.