🖖🏼 🏂🏼 🐽 从零开始在出租车中创建反欺诈系统 👩‍🚀 🙌🏽 🛎️

下午好。我叫Nikita Bashun，我是Lucky集团公司的数据分析师。我的故事将是关于我们如何由三个人组成的团队从头开始为旅行预订服务创建一个反欺诈系统。

介绍

曾经知道如何欺骗的人会欺骗很多次。

洛佩·德·维加

在本案中，欺诈是驾驶员欺骗公司的情况。骗取钱财。

, , 25, Delphi. . , . , . …

— MVP, .

, :

. ( «-» ), , . ;
. — , ( , ). -, 200 .
, — « »:
- , (, 0% );
- , -.

— , , , ;
- ( ) — , , ;
— , , , .

False Positive, False Negative . :
- , ( , !);
- .
. ;
. , — . , , , . - , … .

SQL- DWH, . . , , «»:

WHERE susp = 1 --   
  AND finished_orders >= 3 --        
  AND cancelled >= 3 --    ,        
  AND dist_fin_drivers <= 2 --      
  AND ok <= 2 --   2-

, .

. « » . , ? -. , .

python. pandas, postgres, Google ( ). , , Apache Airflow.

API .

:

credentials = ServiceAccountCredentials.from_json_keyfile_dict(
    config.crd,
    ['https://www.googleapis.com/auth/spreadsheets',
     'https://www.googleapis.com/auth/drive'])

httpAuth = credentials.authorize(httplib2.Http())
service = googleapiclient.discovery.build('sheets', 'v4', http=httpAuth)
sheet = service.spreadsheets()

base_range = f'{city_name}!A{ss_row + 1}:Z{ss_row + reserved_rows}'
sheet.values().append(spreadsheetId=spreadsheetid,
                                 range=base_range,
                                 body={"values": df_pos.values.tolist()},
                                 valueInputOption='RAW').execute()

range_from_ss = f'{city_name}!A{ss_row}:S{ss_row + reserved_rows}'

data_from_ss = service.spreadsheets().values().get(
            spreadsheetId=spreadsheetid,
            range=range_from_ss).execute().get('values', [])

data_from_ss = pd.DataFrame(data_from_ss)
data_from_ss_cols = ['id', '', '']
data_from_ss = data_from_ss.loc[1:, data_from_ss_cols]

PG:

vls_ss = ','.join([f"""({', '.join([f(d[c]) for c in data_from_ss_cols])}
                    )""" for d in data_from_ss.to_dict('rows')])

sql_update = f"""
    WITH updated as (
        UPDATE fraud_billing
        SET resolution = tb.resolution,
            comment=tb.comment,
            dt = NOW()
        FROM (VALUES {vls_ss}) AS tb(fraud_billing_id, resolution, comment)
        WHERE fraud_billing.fraud_billing_id = CAST(tb.fraud_billing_id AS INTEGER)
            AND ((fraud_billing.resolution IS NULL AND tb.resolution IS NOT NULL)
                OR (fraud_billing.comment IS NULL AND tb.comment IS NOT NULL)
                OR (fraud_billing.comment IS NOT NULL AND tb.comment IS NOT NULL
                   AND fraud_billing.comment <> tb.comment)
                OR (fraud_billing.resolution IS NOT NULL AND tb.resolution IS NOT NULL
                    AND fraud_billing.resolution <> tb.resolution)
               )
        RETURNING {alias_cols_text_with_id}
        )
    INSERT INTO fraud_billing_history ({cols_text_with_id})
    SELECT {cols_text_with_id}
    FROM updated;
"""

crs_postgres.execute(sql_update)
con_postgres.commit()