/
Profiling Suppressed Customer OLD

Profiling Suppressed Customer OLD

Owned by Rohit Kumar
Oct 05, 2023
5 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 Cash On Delivery (COD) orders but subsequently refuse to accept the delivery when the courier partners attempt to fulfill the order. This category also encompasses customers who exhibit a recurring pattern of either order rejection or have a history of placing COD orders with multiple instances of returned orders. 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 Cash On Delivery (COD) orders and subsequently rejected the delivery or have multiple instances of returns within the past year.

  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.

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 the CRON will have two part

    1. First part will save and update following columns

      1. account_id

      2. email

      3. phone

      4. rejected_shipment_count

    2. Second part will update only one column

      1. total_shipment_count

  3. Flow Diagram of CRON

  4. To address 2.a, we do followings

    1. Get all distinct shipment Ids, which have been marked as status = 'failed_delivery' in last 24 hours and rejected by customer, using given query

      SELECT DISTINCT( shipment_status_logs.shipment_id ) FROM shipment_status_logs WHERE shipment_status_logs.created_at >= '2023-07-21 13:57:38' AND shipment_status_logs.status = 'failed_delivery' AND shipment_status_logs.track_payload LIKE "%Rejected by Customer%"

    2. After getting Unique shipment IDs we will get order details and shipment counts which is rejected by customer using given query

      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 (18112821,18284166,18285580,18063611,18262913,18302955,18267651,18287566,18289326,18216024,18289896,18315476,18268406,18287945,18285633,18283533,18309952,18280471,18310882,18291001,18292488,18298245,18298666,18231777,18181140,18109156,18288868,18260763,18220364,18288715,18296649,18265027,18297430,18306168,18300659,18270557,18289964,18279112,18319741,18285122,18257501,18317313,18310065) AND orders.thirdPartyShipping = '0' GROUP BY orders.shipping_contactPhone, orders.shipping_email, channels.account_id;

    3. We will fetch details from above query into a List of DTO, our DTO swill contains table structure of suppressed_customers table

    4. After getting List we will pass in batch to save and update these details

  5. To address 2.b, we will do following

    1. We will get all the data from suppressed_customers table using given query, into a DTO

      SELECT id, account, email, phone, total_shipment_count FROM suppressed_customers

    2. after we get list of DTOs we will perform following steps

      1. map each DTO with key (account_id##phone##email)

      2. get list of account_id, phone and email from list of DTOs

    3. we will pass above lists in query given below to fetch shipments count

      SELECT orders.id, channels.account_id, orders.shipping_contactPhone, orders.shipping_email COUNT(*) shipmentCounts 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 shipments.order_id;

    4. We will fetch shipments count details in list and map these result with key (account_id##phone##email)

    5. Using above key set we will get keys and find DTO and shipment count details and set the total_shipment_count in DTO

    6. After getting shipments count we will update total_shipment_count in suppressed_customers

      QUERY YET TO BE DECIDE

 

Use case of “suppressed_customers” table

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

  2. OPEN POINT: what percentage is considered as significant to mark any order as on hold?

    1. percentage above 30 should be considered as high risk order (suggestion)

NOTE:

  • email, phone and accountId should be unique composite key of the the table

EDGE CASES:

  • If single shipment get rejected by multiple time

  • If shipment journey have both the status first rejected and then accept

Discussion Point:

  • Handling for historic sync, provided initial date and end date

    • using cyclic taskqueue and passing time slot of fixed length

  • to update and insert rejected counts, get rejected count details and based on these rejected count details fetch details from suppressed_customers and use upsert mechanism

  • to updated total count fetch all shipment counts within 24hours and find out how many of them are present in suppressed_customer and update only those shipment counts in the suppressed_customers table using below query

    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;

BIG QUERY FLOW FOR REGULAR SYNC:

  1. In this Step we will get all rejected count of last 24 hours using below Query:

    SELECT workspace, shipping_email_id, shipping_contact_number, COUNT(shipment_id) AS count FROM `analytics-340903.bq_export_sync.bq_sale_order_report_v2` WHERE order_journey_update_time IS NOT NULL AND order_processing_on BETWEEN DATETIME("2023-09-28") AND DATETIME_ADD("2023-09-28", INTERVAL 1 DAY) AND esb_shipping = '1' AND reason_for_failed_delivery = 'Rejected by customer' GROUP BY workspace, shipping_email_id, shipping_contact_number

  2. After getting result from above query we filter out data which need to be updated and which need to be inserted

  3. We will update and insert rejected shipment count in suppressed_customers customer table

  4. Using below query we will fetch all shipments count within 24 hours and fetch data suppressed_customers table to verify which entry need to be updated

    SELECT workspace, shipping_email_id, shipping_contact_number, COUNT(shipment_id) AS count FROM `analytics-340903.bq_export_sync.bq_sale_order_report_v2` WHERE order_journey_update_time IS NOT NULL AND order_processing_on BETWEEN DATETIME("2023-09-28") AND DATETIME_ADD("2023-09-28", INTERVAL 1 DAY) AND esb_shipping = '1' GROUP BY workspace, shipping_email_id, shipping_contact_number

  5. After updating we will then get all shipment count of data which is has been inserted first time in step 3 above using query given below

    SELECT workspace, shipping_email_id AS email, shipping_contact_number AS phone, CONCAT( workspace, '##', shipping_email_id, '##', shipping_contact_number ) AS compositeKey, COUNT(DISTINCT shipment_id) AS count FROM `analytics-340903.bq_export_sync.bq_sale_order_report_v2` WHERE order_journey_update_time IS NOT NULL GROUP BY workspace, shipping_email_id, shipping_contact_number HAVING compositeKey IN ( 'farmely##shubhmkhajuriya786@gmail.com##8966055092' );

  6. after getting total shipment count for newly rejected shipments we will update the total count in suppressed_customers table

  7. In above case we only checking for latest failed_delivery reason, following problem can occur

    1. If there are two failed delivery events with given failed_delivery reasons

      1. “Rejected by customer

      2. “Payment issue”

    2. Then we will get reason_for_failed_delivery as “Payment issue” due to which we will miss this count

    3. To tackle this problem we need to add an additional column in bq sync which will have the information where the shipment got rejected in any point of time or not

DIAGRAM CODE:

FIGMA: Suppressed Customer Figma

Add label

Related content