Creation of an anti-fraud system in a taxi from scratch

Good afternoon. My name is Nikita Bashun, I work as a data analyst in the Lucky group of companies. My story will be about how we, with a team of three people, created from scratch an anti-fraud system for the travel booking service.

Introduction

Whoever knows how to deceive will deceive many times.

Lope de Vega

Fraud in our case is a situation when a driver deceives a company. Fraud to get money.

, , 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()

postgres :

• ;
• .

:

( , ).

, :

— .

: , , .

.

, . , , — . «» .

FP- , .

— , , . .

« ». , , :

• ;
• ;
• ;
• — .

.

, . , , , , . - :

, , — .

Google Spreadsheets. . , :

• , «» ;
• API — ;
• ;
• , .

. , — :

• . - , - , - . , . ;
• . , , :
  
  
  • - , ;
  • - ;
  • - , ;
  • - .

, , . , «».

— , . — , . !

, ?

• . . , «» , . ;
• . , . , .

( ) 35%. — 25%. , — — , . : , , , . .

:

• 15 ;
• 6800 ;
• 500 .

But the most important success is the gradual reduction of the cases of suspicion in many cities. After all, ideally, we do not want to catch more , we want nothing to catch .

Conclusion

I tried to describe the main functionality and principles of the anti-fraud system, as well as the difficulties we encountered. The plans include: using ML to optimize search, creating a sanctions monitoring system (now it is at the initial stage), improving the interface for managers, creating dynamic reporting, developing new patterns and much more.

After all, we are only at the beginning of the journey.