High Level Diagram Whatsapp Integration (Meta)

What is expected to be done?

Trigger notification messages whenever FDR (failed delivery reason) events occur, clients would have the option of using an Eshopbox account integrated with Whatsapp to trigger notifications or using their own account for triggering notifications.

How would it be implemented?

Open

ALL GCP PROJECT IDS AND REPOS:

DATA MODEL:

Tables and columns to be introduced to achieve the following are as follows:

1. Table: eshopbox_wms_production.poe_channel_notifications: columns to be added “isWhatsappEnabled” (values 0 or 1), “whatsappTemplateId” (eg. 567787) (can be different for the different account)

2. Table: eshopbox_wms_production.poe_channel_settings: columns to be added “whatsappSenderConnectionId”

3. New tables:

   1. Table wa_outgoing_messages

   2. Table wa_dlr_messages

   3. Table wa_customer_reply_messages

   4. Table wa_templates

Table wa_outgoing_messages: This table will hold the data for all the outgoing messages i.e. from EShopbox to Customer. root_message_id will be null for the first template message as it has no root and from the next outgoing message onwards all the outgoing messages will have the message_id of the initial template as its root_message_id.

Table wa_dlr_messages: This table will hold the data for all the delivery reports i.e. when the message was sent/delivered/read.

Table wa_customer_reply_messages: This table will hold the data for all the customer actions/replies and the entire webhook received from WhatsApp which are coming from the customer to EshopBox. Also this will save the primary key from wa_outgoing_messages in wa_root_outgoing_message_id whenever we receive a customer reply having a context_message_id.

This will include 2 cases,

first, if the context_message_id is not having root_message_id in the wa_outgoing_messages then it will save the primary key (id) in root_message_id.

Second, If root_message_id is present in This will include 2 cases, first, if the context_message_id is not having root_message_id in the wa_outgoing_messages then we will use the primary key which is saved against the root_message_id.

Table wa_templates: This table will hold the data for all the templates and their description along with the variables used in templates.

Logic:

Type of FDR:

APP & INTEGRATION

For the Eshopbox application, it will be by default present and if clients want their own Whatsapp app then they will perform the following else this isn’t required

1. New Whatsapp will be integrated into the platform where the client would be installing the application in the very first place

2. As soon as the app is installed client would be prompted to enter the credentials, credentials would save in the integration database credentials and table reference integration db table name ie_appinstall_connection – App&Integration team

CUSTOMER PORTAL:

– GCP Service for the following steps (poe-setting), repo name Poe-setting-service, GCP Project poe-backend-prod

1. Project Integration Engine service name: default, call API - “/connection/start“ (apps and integration) from which we will fetch the connection ids of apps installed

2. Default settings to be updated for new workspace and existing which is “0” false isWhatsappEnabled it should be by default disabled and if new workspaces is created same needs to be covered

3. Update default settings of eshopbox account id = 5 for every workspace until and unless client has chosen its own WhatsApp account from apps selection

4. We would save the same connection ids in poe_channel_settings.whatsappSenderConnectionId

5. Configuration of application by default eshop app would be installed and templateId would be saved for that default account column to be added in DB.

6. If the client chooses other apps than eshop then we update templateId and set it as the user will input the template id for their account also we will verify each template ID with the following Meta API

   1. “https://graph.facebook.com/v16.0/108687922264083/message_templates”

7. For clients there would be a toggle option to choose and turn on or off the WhatsApp notifications setting if the client chooses to turn the notification off it would be saved in db in table “poe_channel_notification”.

8. By default the setting will be set as “0” because other services Eshopbox provide are free of cost and Whatsapp isn’t so it will be by default in the off state.

9. Clients would be prompted to choose the application if they have installed their own application they would choose their else by default Eshopbox application would be selected from the dropdown.

10. We need to create a new “wa_templates” table in wms prod db where various variables and template body will be saved for triggering message

11. To trigger the FDR message we first receive an event from GCP Project - ESB-platform-prod, service name - notification where we have published the event for shipment the same will be subscribed by our service and FDR events look like this:

    { "resource": "shipment", "eventType": "PUT", "eventSubType": "failed_delivery", "accountSlug": "vitaminplanet", "accountId": null, "actor": "SYSTEM", "version": "v1", "request_data": [], "response_data": { "customerOrderNumber": "4980116095129", "orderSiteID": "", "vendorOrderNumber": "#98079", "externalShipmentID": "4980116095129-90758", "externalWarehouseID": "Gurgaon_FC", "externalChannelID": "CH1299", "externalWmsChannelName": "SCITRON_SHOPIFY_VITAMINPLANET_AGGN_2295", "external_wms_channel_id": 2295, "channelLabel": "Scitron Shopify", "integrationType": "7", "vendorPartyID": null, "partner_as2_id": null, "defaultWarehouseCode": "Gurgaon_FC", "facilityLabel": "Gurgaon (AGGN)", "facilityType": "auperator", "orderDate": "2023-06-05 16:58:58", "portal_id": 27, "paymentType": "Cash on Delivery (COD)", "expectedShipDate": "2023-06-06 12:00:00", "dispatchAfterDate": null, "externalManifestNumber": null, "channelManifestNumber": null, "order_id": 48884718, "channel_id": 1299, "warehouse_id": 650, "channel_account_id": 0, "account_id": 379, "connectionId": 482, "locationId": "67070591129", "region": "Zonal", "isMetro": "0", "isSpecialplace": "0", "shippingConnectionId": 0, "picklistCode": null, "invoiceNumber": "INVVITHZ42616", "boxType": "UNKNOWN", "isPriorityShipment": "0", "isGift": "0", "invoice_url": "https:\/\/storage.googleapis.com\/invoicefiles-prod\/invoice\/4980116095129-90758-1685964612847.pdf", "invoiceDate": "2023-06-05 17:00:09", "label_url": "https:\/\/storage.googleapis.com\/eshopbox_wms_uploads\/myntraLabel\/20230605210903274917906.pdf", "labels": "", "shippingInfo": [], "boxAdditionalRecommendation": [], "dimension_length": "26", "dimension_width": "16", "dimension_height": "20", "weight": "1550", "trackingID": "1704612602165", "trackingDomain": "track.scitron.com", "packageID": "", "barcode": "", "taxAmount": 0, "shipChargeAmount": 0, "courierName": "Delhivery", "cp_id": 4, "created_at": "2023-06-05 16:59:09", "updated_at": "2023-06-06 19:49:23", "status": "failed_delivery", "remarks": "code verified cancellation", "warehousePincode": "122503", "thirdPartyShipping": false, "customerName": "Aman Verma", "customerContactNumber": "7891971009", "email": "Amitv12330@gmail.com", "channelSlug": "vitaminplanet90", "status_updated_at": "2023-06-08 16:06:16", "status_log": { "created": "2023-06-05 16:59:09", "accepted": "2023-06-05 17:00:08", "picked": "2023-06-05 17:30:14", "packed": "2023-06-05 21:09:05", "out_for_pickup": "2023-06-06 16:46:12", "dispatched": "2023-06-06 19:49:23", "intransit": "2023-06-08 10:13:42", "out_for_delivery": "2023-06-08 12:09:06", "failed_delivery": "2023-06-08 16:06:16" }, "status_log_first_occurrence": { "failed_delivery": "2023-06-08 16:06:16", "out_for_delivery": "2023-06-08 12:09:06", "intransit": "2023-06-07 00:16:22", "dispatched": "2023-06-06 19:49:23", "out_for_pickup": "2023-06-06 16:46:12", "packed": "2023-06-05 21:09:05", "picked": "2023-06-05 17:30:14", "accepted": "2023-06-05 17:00:08", "created": "2023-06-05 16:59:09" }, "status_log_count": { "created": 1, "accepted": 1, "picked": 1, "packed": 1, "out_for_pickup": 1, "dispatched": 1, "intransit": 7, "out_for_delivery": 1, "failed_delivery": 1 }, "status_log_id": "124953537", "orderExternalCreatedAt": "2023-06-05 16:59:05", "shippingAddress": { "customerName": "Aman Verma", "addressLine1": "Sadafal kunj", "addressLine2": "Arjun club road ward num 26", "city": "churu", "state": "RAJASTHAN", "postalCode": "331403", "countryCode": "IN", "countryName": "INDIA", "contactPhone": "7891971009", "email": "Amitv12330@gmail.com" }, "warehouseAddress": { "addressLine1": "MJ Logistics Private Limited, Seabird Marine Services Pvt Ltd", "addressLine2": "Village Babra Bakirpur, Pataudi Road Near Adani Logistic Park, ICD Patli, Gurgaon, Haryana(122503)", "city": "Gurgaon", "state": "Haryana", "postalCode": "122503", "gstin": "06AAFCB0498R2Z2" }, "id": 17148471, "isCOD": "1", "track_payload": { "clickPostTrackData": { "location": "Sardarshahr_IndustrialArea_D (Rajasthan)", "additional": { "courier_partner_edd": "2023-06-09", "destination_hub_inscan_ts": null, "order_id": "4980116095129-90758", "latest_status": { "status": "RT In Transit", "clickpost_status_description": "FailedDelivery", "clickpost_status_bucket": 5, "reference_number": "4980116095129-90758", "clickpost_status_bucket_description": "Failed delivery", "remark": "Code verified cancellation", "timestamp": "2023-06-08T14:58:41Z", "location": "Sardarshahr_IndustrialArea_D (Rajasthan)", "clickpost_city": null, "clickpost_status_code": 9 }, "is_rvp": false, "ndr_status_code": 14, "ndr_status_description": "OTP-based cancellation", "account_code": "ESB_Delhivery_Forward_500g_Express", "npr_status_code": null, "npr_status_description": null }, "status": "RT In Transit", "clickpost_city": null, "clickpost_status_code": 9, "clickpost_status_description": "FailedDelivery", "cp_id": 4, "remark": "Code verified cancellation", "account_code": "ESB_Delhivery_Forward_500g_Express", "waybill": "1704612602165", "timestamp": "2023-06-08T14:58:41Z" } }, "account_slug": "vitaminplanet", "packed_date": "2023-06-05 21:09:05", "items": [ { "order_item_id": 31200595, "lineItemSequenceNumber": 12566731260057, "orderItemID": "4980116095129-31200595", "itemID": "45162317217945", "sku": "06REP3HBCN3", "asin": "", "productName": "Advance Whey Protein with 20 Vitamins & Minerals", "quantity": 1, "orderItemCreatedAt": "2023-06-05 16:59:05", "customerPrice": 2099, "lineItemTotal": 1889.0999999999999, "invoiceTotal": 1889.0999999999999, "cashOnDeliveryCharges": 0, "discount": 209.90000000000001, "taxRate": 0, "taxAmount": 0, "inventoryItemCode": "G-IB1735411", "giftMessage": "", "isGift": "0", "giftLabelContent": "", "lineItemOrderStatus": "", "orderItemIDs": [ "4980116095129-31200595" ], "productImageUrl": null, "productAdditionalInfo": { "size": "Kulfi \/ 1KG \/ Gloves" }, "expectedDeliveryDate": "2023-06-09", "shippingCharges": 0, "productUrl": null, "originalOrderItemId": null, "isVirtualKit": "1", "component": [ { "inventoryItemCode": "G-IB1665164", "productAdditionalInfo": "", "whOrderItemID": "4980116095129-31200595\/1", "esin": "63MYP3HA5I0" }, { "inventoryItemCode": "G-IB1735411", "productAdditionalInfo": "", "whOrderItemID": "4980116095129-31200595\/2", "esin": "75V0P3HA6O1" } ], "onhold": "0", "cancellationAdditionalReason": "", "cancellationReason": "", "customerOrderItemID": "", "recallBlockedInventoryUsed": "" } ] }, "previous_data": [], "resource_type": "shipment.update", "account_slug": "vitaminplanet", "custom": [] }

     

12. From the above event, we fetch and store the required details in WhatsappDataDto on our system

13. After confirmation of the above (as we received the FDR event) we verify is the event valid

14. We verify the above we check if poe_setting.isWhatsappEnabled flag is enabled or not on the basis of user account_id (we implement caching here to store the user setting)

15. If the above details are verified then we trigger the message using WhatsApp (meta API)

    1. POST: https://graph.facebook.com/v16.0/108687922264083/messages

       Test registered Template: { "messaging_product": "whatsapp", "recipient_type": "individual", "to": "919650186697", "type": "template", "template": { "name": "customer_unreacheable_ndr", "language": { "code": "en_US" }, "components": [ { "type": "body", "parameters": [ { "type": "text", "text": "teg" }, { "type": "text", "text": "96501874894" }, { "type": "text", "text": "Pablo" }, { "type": "text", "text": "566701" }, { "type": "text", "text": "Pablo" }, { "type": "text", "text": "566701" } ] } ] } }

    2. We need to append the request body (variables as defined) and refer to tables for variable

    3. Parameters are where we enter variables

16. As soon as we trigger a message we receive various DLR, Incoming message responses on our webhook registered which are documented taking the example of “Select new delivery date” here:

    1. Whatsapp API Response:

17. Here we are not using any bot as the customer reply on the first message is acted upon on the basis of selection for example if the customer chose “Select new delivery date” we trigger a new message using:

    1. POST: https://graph.facebook.com/v16.0/108687922264083/messages

       { "messaging_product": "whatsapp", "recipient_type": "individual", "to": "919650186697", "type": "interactive", "interactive": { "type": "button", "body": { "text": "Please help us by selecting your preferred delivery date" }, "action": { "buttons": [ { "type": "reply", "reply": { "id": "date-1", "title": "23 June 2023" } }, { "type": "reply", "reply": { "id": "date-2", "title": "24 June 2023" } }, { "type": "reply", "reply": { "id": "date-3", "title": "25 June 2023" } } ] } } }

    2. We append the action(s) button according to the next three delivery dates in our message

    3. We can append "id": "date-2" in our response body accordingly to fetch details from which particular message it is from and to maintain a relationship in b/w conversation

    4. We need to save incoming message response and outgoing message responses in the following new tables:”wa_incoming_messages” and “wa_outgoing_messages” respectively

18. POST: https://graph.facebook.com/v16.0/108687922264083/messages

    1. The final message is triggered by the above with the body as:

       { "messaging_product": "whatsapp", "preview_url": false, "recipient_type": "individual", "to": "919650186697", "type": "text", "text": { "body": "Thank you for your response. As requested by you we will cancel your order. Hope to see you again!" } }

    2. This is the flow for select new delivery dates similar fashion we would handle other cases as well

19. Reference what would be the cases are as follows FDR whats app notifications

20. To handle various error codes refer to the following Handling Error Codes

21. As soon as the message is triggered we also need to log the activity and reflect the same on the workspace, would be receiving a response from Meta (WhatsApp) API which would tell the state of the sent message on WhatsApp to the customer

22. For triggering activity events we have received DLR which is mentioned in 13. c point above we need to create an event for the same which would be subscribed to by the notification service

23. Cases to be handled when a message is triggered for FDR reason can vary and for “new delivery date” the customer would be prompted to enter the new delivery date as soon as the customer selects the same we will call API: “_ah/ESB/v1/ndrNotification” this API updates the delivery date in the system

24. For address updates, we will redirect the customer to the customer portal and the same goes for adding an alternate number

25. For the URL to be identical to WhatsApp (customer portal), we need to configure a new static URL in Poe-backend-notification when the message is triggered for the FDR case and the same URL needs to be redirected to API which is mapped on the server “/track shipment/WhatsApp/{shipment_id}” this will create separate URL for WhatsApp help identify where the customer came from

HLD Diagram:

Open