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. POST RISK_SCORE API

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

• Http method POST

• Query Params:

  { "externalChannelID": "CH1299", "customerOrderNumber": "4571602911385", "externalShipmentId": "4571602911385-1299-0547", "shippingAddress": { "city": "Churcha colliery", "countryCode": "IN", "postalCode": "497339", "addressLine1": "Shreya poultry farm, Ward number 06", "addressLine2": "East nepal gate, Near balaji beauty parlour", "countryName": "INDIA", "state": "Chandigarh", "contactPhone": "9131786078", "customerName": "Anamika Dad", "email": "nomailprovided@gmail.com" }, "integrationType": "7", "items": [ { "itemID": "44372319699097", "sku": "24QGK3H1ZV5", "externalWarehouseID": "Gurgaon_FC", "quantity": 1, "outOfStock": "false" } ] }

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

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

3. Query to fetch total rto shipment

   SELECT orders.shipping_postalCode, COUNT(DISTINCT shipments.id) FROM orders LEFT JOIN shipments ON orders.id = shipments.order_id LEFT JOIN shipment_status_logs ON shipments.id = shipment_status_logs.shipment_id WHERE shipment_status_logs.status = 'rto' AND shipment_status_logs.created_at >='2022-11-21 15:42:00' GROUP 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. UPDATE pincode_rto_score SET total_packed_shipments = total_packed_shipments+(today's count) , total_rto = total_rto +(today's rto count) WHERE drop_pincode = '110005';