/
Profiling of suppressed customer

Profiling of suppressed customer

Owned by Rohit Kumar
Last updated: Oct 06, 2023
4 min read

What is the purpose of this article?

This article presents various methods for identifying suppressed customers. In this context, "suppressed customers" refer to those individuals who place orders but subsequently refuse to accept the delivery when the courier partners attempt to deliver the order. This category also encompasses customers who exhibit a recurring pattern of placing orders with multiple instances of returned orders after rejecting delivery. To address this, the suggested approach involves blacklisting these customers, suspending any future orders placed by them, and offering sellers:

  • View on hold orders due to suppressed customer

  • Provision to add suppressed customer from workspace (including activity)

  • Provision to delete suppressed customer from workspace (including activity)

  • Notify the customer and merchant in real time

  • Notify the merchant via summary email

How can the outlined objective be accomplished?

To accomplish the aforementioned task, the following rules are applied to identify customers exhibiting suspicious behavior:

  1. We compile a list of customers who have placed orders and subsequently rejected the delivery which end up as RTO

  2. Once we have obtained this list of customers, we proceed to determine the total number of orders placed by each customer within the last year.

  3. Subsequently, the gathered data is stored in the database, organized by their respective account IDs, under which the orders were placed.

  4. Utilizing the information derived from step 3, we calculate the likelihood of future order rejection or return for customers identified as blacklisted.

  5. Based on the calculated odds, we implement a procedure to place orders on hold for these customers, as needed.

Flow Diagram:

Technical Requirement to Achieve above steps

  1. Need to create new table in database named suppressed_customers, columns listed below

    1. id (INT)

    2. account_id(INT)

    3. email(VARCHAR)

    4. phone(VARCHAR)

    5. rejected_shipment_count(INT)

    6. total_shipment_count(INT)

    7. isBlacklisted(ENUM)

    8. created_at(TIMESTAMP)

    9. updated_at(TIMESTAMP)

  2. We will create CRON Job to populate suppressed_customers table

  3. To update suppressed_customers table we need to handle following cases

    1. Rejection count update (Existing Data)

    2. Total count update (Existing Data)

    3. Rejection count Insertion (New Entry)

    4. Total count insertion (New Entry)

  4. Rejection count update (Existing Data): We will fetch rejected shipment count of last 24 hours using given query below

    1. This query will fetch all the shipments id of courier initiated return of last 24 hours

      SELECT DISTINCT(order_items.shipment_id) FROM order_items LEFT JOIN return_shipments ON order_items.return_shipment_id = return_shipments.id LEFT JOIN orders ON order_items.order_id = orders.id WHERE order_items.returnFlag = '1' AND orders.thirdPartyShipping = '0' AND order_items.created_at >= "2023-09-20 00:00:00" AND ( order_items.return_shipment_id = 0 OR return_shipments.type = 'FDR');

    2. This query will fetch all the shipments id from shipments status logs out of shipments Id obtained from above query provided status is 'failed_delivery' and failed_delivery reason is “Rejected by customer“

      SELECT DISTINCT( shipment_status_logs.shipment_id ) FROM shipment_status_logs WHERE shipment_status_logs.shipment_id IN (ShipmentIdListFromAboveQuery) AND shipment_status_logs.status = 'failed_delivery' AND shipment_status_logs.track_payload LIKE "%Rejected by Customer%";

    3. Based on shipment ids obtained from above query we will fetch rejected shipment count and customers details and account details using query given below

      SELECT orders.shipping_contactPhone, orders.shipping_email, channels.account_id, COUNT(Distinct shipments.id) counts FROM shipments LEFT JOIN orders ON orders.id = shipments.order_id LEFT JOIN channels ON channels.id = orders.channel_id WHERE shipments.id IN (ShipmentIdListOfRejectedAndRTO) AND orders.thirdPartyShipping = '0' GROUP BY orders.shipping_contactPhone, orders.shipping_email, channels.account_id;

  5. After getting customer details and Rejected shipment count from above two query we will prepare list of accountIds, emails and phones and based on these IDs we will fetch existing details of suppressed_customer using query given below:

    SELECT account_id AS accountId, phone AS phone, email AS email, rejectedShipmentCount AS rejectedShipmentCount, totalShipmentCount AS totalShipmentCount, suppressed_customer_historic_sync_id AS suppressedCustomerHistoricSyncId FROM suppressed_customers WHERE account_id IN (: accountIds) AND email IN (: emails) AND phone IN (: phones)

  6. After getting Data from Step 4 and Step 5 will seperate out data which need to be updated and which need to be inserted

  7. We will update rejected shipment count in suppressed_customers

  8. Just after updating rejected shipment count, we will update total shipment count

  9. Total count update (Existing Data): To update existing data of total shipment count we will fetch data of past 24 hours using below query and update it into suppressed_customers table after verifying with existing data

    SELECT orders.id, orders.shipping_contactPhone, orders.shipping_email, channels.account_id, COUNT(DISTINCT shipments.id) counts FROM shipments LEFT JOIN orders ON orders.id = shipments.order_id LEFT JOIN channels ON channels.id = orders.channel_id WHERE shipments.created_at >= '2023-08-31 00:00:00' AND shipments.created_at <= "2023-08-31 23:59:59" AND orders.thirdPartyShipping = '0' GROUP BY orders.shipping_contactPhone, orders.shipping_email, channels.account_id;

  10. Rejection count Insertion (New Entry): After updating rejected and total shipment count we will insert new entries into suppressed_customers table

  11. Total count insertion (New Entry): If there is any “new entry of rejected shipment counts” then for those entry we will prepare list of accountIds, emails and phones and fetch total shipment counts using given query below:

    SELECT orders.shipping_contactPhone, orders.shipping_email, channels.account_id, COUNT(DISTINCT shipments.id) counts FROM orders LEFT JOIN channels ON channels.id = orders.channel_id LEFT JOIN shipments ON shipments.order_id = orders.id WHERE channels.account_id IN (552, 801, 725) AND shipping_contactPhone IN ( '7204634592', '9068070522', '7259034618', '9712919840' ) AND shipping_email IN ( 'haifabanday@yahoo.com', 'rashmitejaswinir@gmail.com', 'deekshabarora@gmail.com', 'g.bhatnagar22@gmail.com' ) GROUP BY orders.shipping_contactPhone, orders.shipping_email, channels.account_id;

  12. We will verify data from above query with IDs list of “new entry of rejected shipment counts” and update total shipment count for new entries.

Use case of “suppressed_customers” table

  1. Based on rejected percentage we will create order is on hold due to high risk scor

DIAGRAM CODE:

FIGMA: Suppressed Customer Figma

Add label

Related content