/
Requirements for risk score

Tables to be made:

  1. Pincode rto score

    1. This table will save the daily packed shipments count and RTO shipment counts of a particular drop pincode

    2. Table name : pincode_rto_score

    3. Columns - 

      1. id

      2. drop_pincode

      3. total_packed_shipments

      4. total_rto

      5. total_cir

      6. created_at

      7. updated_at

  2. ODA table

    1. This table will contains ODA drop pincodes.

    2. Table name : oda_pincode_mapping

    3. Columns(TBD)

  3. Customer Fraudelent History(Open Point)

    1. This table will contains details of those customers who

      1. had earlier rejected the shipments and had cancelled via OTP/IVR verification multiple times

      2. Customer had seized the shipment earlier, wanted open delivery.

      3. Customer had high returns % (Returns/Delivered)

APIs to be made

  1. POST RISK_SCORE API

  • API Path: _ah/api/esb/v1/risk/order-create-listener

  • Http method POST

  • Query Params:

1{ 2 "resource": "order", 3 "eventType": "POST", 4 "eventSubType": "created", 5 "accountSlug": "tab", 6 "accountId": null, 7 "actor": "SYSTEM", 8 "version": "v1", 9 "request_data": [], 10 "response_data": { 11 "id": 44176830, 12 "externalChannelID": "CH5865", 13 "channelLabel": "Bigbasket new", 14 "integrationType": "4", 15 "customerOrderNumber": "Series73", 16 "vendorOrderNumber": "Series73", 17 "shipMethod": "STD", 18 "orderDate": "2023-03-29 08:07:13", 19 "expectedShipDate": null, 20 "dispatchAfterDate": null, 21 "promiseDeliveryDate": null, 22 "isCOD": "0", 23 "paymentType": "prepaid", 24 "orderSiteID": null, 25 "taxAmount": 0, 26 "shipChargeAmount": 25, 27 "subtotal": 950, 28 "orderTotal": 975, 29 "balanceDue": 0, 30 "thirdPartyShipping": false, 31 "onHold": false, 32 "created_at": "2023-03-29 13:37:15", 33 "updated_at": "2023-03-29 13:37:15", 34 "shippingAddress": { 35 "customerName": "anup", 36 "addressLine1": "Hsidc Maruti Indl Area", 37 "addressLine2": null, 38 "city": "south west delhi", 39 "state": "DELHI", 40 "postalCode": "110022", 41 "countryCode": "IN", 42 "countryName": "IN", 43 "contactPhone": "9650000000", 44 "email": "johndoe@test.com" 45 }, 46 "billingAddress": { 47 "customerName": "anup", 48 "addressLine1": "Hsidc Maruti Indl Area", 49 "addressLine2": null, 50 "city": "Delhi", 51 "state": "Delhi", 52 "postalCode": "110761", 53 "countryCode": "IN", 54 "countryName": "IN", 55 "contactPhone": "9650000000", 56 "email": "johndoe@test.com" 57 }, 58 "items": [ 59 { 60 "lineItemSequenceNumber": 1, 61 "orderItemID": "Series73-23924311", 62 "itemID": "ImportTest1", 63 "sku": "T1U973FNPT3", 64 "productName": "Multi color jersey tee", 65 "quantity": 1, 66 "customerPrice": 1000, 67 "discount": 50, 68 "lineItemTotal": 950, 69 "taxRate": 0, 70 "taxAmount": 0, 71 "giftMessage": "Happy Birthday.", 72 "giftLabelContent": "", 73 "cancellationReason": "", 74 "cancellationTime": "0000-00-00 00:00:00", 75 "original_order_item_id": null 76 } 77 ], 78 "expectedDeliveryDate": null 79 }, 80 "previous_data": [], 81 "resource_type": "order.create", 82 "account_slug": "tab", 83 "custom": [] 84}

  1. POST RISK_SCORE API

  • API Path: _ah/api/esb/v1/risk/order-verification

  • Http method POST

  • Query Params:

    1{ 2 "externalChannelID": "CH1299", 3 "customerOrderNumber": "4571602911385", 4 "externalShipmentId": "4571602911385-1299-0547", 5 "shippingAddress": { 6 "city": "Churcha colliery", 7 "countryCode": "IN", 8 "postalCode": "497339", 9 "addressLine1": "Shreya poultry farm, Ward number 06", 10 "addressLine2": "East nepal gate, Near balaji beauty parlour", 11 "countryName": "INDIA", 12 "state": "Chandigarh", 13 "contactPhone": "9131786078", 14 "customerName": "Anamika Dad", 15 "email": "nomailprovided@gmail.com" 16 }, 17 "integrationType": "7", 18 "items": [ 19 { 20 "itemID": "44372319699097", 21 "sku": "24QGK3H1ZV5", 22 "externalWarehouseID": "Gurgaon_FC", 23 "quantity": 1, 24 "outOfStock": "false" 25 } 26 ] 27}

  1. RTO score cron

  • API path : _ah/api/esb/v1/risk/rto-score

  • Http method GET

Working of RTO score cron

  1. Cron will hit at time 't' where t is every day at 11:59 PM

  2. With the current time - 24hrs fetch total packed shipment of that day grouped by drop pincode

    1SELECT orders.shipping_postalCode, COUNT(DISTINCT shipments.id) FROM orders 2LEFT JOIN shipments ON orders.id = shipments.order_id 3LEFT JOIN shipment_status_logs ON shipments.id = shipment_status_logs.shipment_id 4WHERE shipment_status_logs.status = 'packed' AND shipment_status_logs.created_at >='2022-11-21 15:42:00' 5GROUP BY orders.shipping_postalCode

  3. Query to fetch total rto shipment

    1SELECT orders.shipping_postalCode, COUNT(DISTINCT shipments.id) FROM orders 2LEFT JOIN shipments ON orders.id = shipments.order_id 3LEFT JOIN shipment_status_logs ON shipments.id = shipment_status_logs.shipment_id 4WHERE shipment_status_logs.status = 'rto' AND shipment_status_logs.created_at >='2022-11-21 15:42:00' 5GROUP BY orders.shipping_postalCode

  4. Update total packed shipments, total rto shipments by adding previous values of pincode_rto_score to the total count of present day

  5. 1UPDATE pincode_rto_score SET total_packed_shipments = total_packed_shipments+(today's count) 2, total_rto = total_rto +(today's rto count) WHERE drop_pincode = '110005';