Proposal summary
This project proposal covers multiple requests that will help to send the notification for the customers. Based on the different conditions for sales orders and item fulfillment.
We have planned to a written a user event script and map/reduce script for sending emails for customers.
Requirement
- For sending emails to customers Notifying Old Open SO. to let them know they have to pick up their order asap. Below are the automated rules I would like to set up. The rules are:
a. ALL SO with NO BACKORDERS that are Older than 7 Days:
If sales order’s “email sent” checkbox checked date comparing with the current date (the “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this)
The date difference in days is greater than 7 days and less than or equal 14 days also check the item fulfillment status is picked/pack. (created from the specific SO) And sales order have backorder items or not.
The above conditions are satisfied we will send an email to the customer. With the following template:
Template sample:
Subject: Your Back Order item(s) is now available for pickup!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which had pending back order item(s) that have been in-stock for over 7 days now. Please advise within 48 hours as to when you are planning on picking up this/these item(s). Corp Design’s policy requires dealers to pick up their order(s) within 14 days of the order being placed or when a Back Order item is back in stock. Your help and attention to this matter are highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
b. ALL SO with NO BACKORDERS that are Older than 14 Days:
If sales order’s “email sent” checkbox checked date comparing with the current date (the “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this)
The date difference in days is greater than 14 days and less than or equal 21 days also check the item fulfillment status is picked/pack. (created from the specific SO) And sales orders have backorder items or not.
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: Your Back Order item(s) has been available for pickup for over 2 weeks now!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which had pending back order item(s) that have been in-stock for over 2 weeks now. Please advise within 48 hours as to when you are planning on picking up this/these item(s). Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed or when a Back Order item is back in stock. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
c. ALL SO with NO BACKORDERS that are Older than 21 Days:
If sales order’s “email sent” checkbox checked date comparing with the current date (the “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this)
The date difference in days is greater than 21 days and less than or equal 30 days also check the item fulfillment status is picked/pack. (created from the specific SO) And sales orders have backorder items or not.
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: Your Back Order item(s) has been available for pickup for over 3 weeks now!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which had pending back order item(s) that are now in-stock for over 3 weeks now. Please advise within 48 hours as to when you are planning on picking up this/these item(s). Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed or when a Back Order item is back in stock. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
d. All SO with NO BACKORDERS that are Older than 30 days:
If sales order’s “email sent” checkbox checked date comparing with the current date (the “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this)
The date difference in days is greater than 30 days and less than or equal 60 days also check the item fulfillment status is picked/pack. (created from the specific SO) And sales orders have backorder items or not.
The above conditions are satisfied we will send an email to the customer. With the following template:
Dear valuable customer,
Subject: This email is our last attempt to contact you pertaining order______ – your order may automatically be cancelled – Please contact us immediately!
We have been trying to reach you several times concerning the attached Open Sale Order which had pending back order item(s) that are now in-stock that you have yet to pick up. Please advise within 48 hours if you are planning on picking up this/these item(s), otherwise we will have to cancel it as it has been open for over a month now. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date immediately – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
2. ALL OPEN ORDERS without Back Orders With No Shipping Dates AND/OR which Shipping date has past – SHIPPING METHOD: PICKUP
a. ALL OPEN ORDERS With No Shipping Dates that are Older than 7 days:
The sales orders with no shipping date and older than 7 days from all items come back-in-stock date. In such cases, we have sent emails to the customer for notifying the customer.
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: Your order_____ is available for pickup!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which has been available for pickup for over 7 days now. Please advise within 48 hours as to when you are planning on picking up this order. Corp Design’s policy requires dealers to pick up their order(s) within 14 days of the order being placed. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date.
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
b. ALL OPEN ORDERS With No Shipping Dates AND/OR Shipping date has past that is older than 14 days:
The sales orders with no shipping date and older than 14 days from all items come back-in-stock date. In such cases, we have sent emails to the customer for notifying the customer.
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: Your order_____ is available for pickup!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which has been available for pickup for over 2 weeks now. Please advise within 48 hours as to when you are planning on picking up this order. Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
c. ALL OPEN ORDERS With No Shipping Dates AND/OR Shipping date has past that are Older than 30 days:
The sales orders with no shipping date and older than 30 days from all items come back-in-stock date. In such cases, we have sent emails to the customer for notifying the customer.
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: This email is our last attempt to contact you pertaining order______ – your order may automatically be cancelled – Please contact us immediately!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which has been available for pickupfor over 1 month now. Please advise within 48 hours as to when you are planning on picking up this order or your order may be cancelled automatically. Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed, this order has been at our warehouse for much longer than our policy allows. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to arrange your pickup.
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
3. All IF which are marked as pick/pack– Only orders with shipping method: pickup
a. IF READY
On the creation of item fulfillment, we have checked the status of IF. If the status is pick/pack and created from the sales order’s shipping method is pickup then send an email to the customer.
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: Your order _________ is ready for pickup!
Dear customer,
SO#_____ is now ready for pickup at our warehouse.
Effective October 1st, 2020 – Orders already PICKED and READY for pickup can’t be changed without a replacement PO an RMA, please contact your customer service specialist if you have any question.
Have a great day!
CorpDesign
info@corpdesign.com
b. IF READY FOR OVER 7 DAYS
If the IF status is pick/pack and created from the sales order’s shipping method is a pickup. Also considering the current date is more than 7 days from IF Trandate. Then we email to customers with the following template
The above conditions are satisfied we will send an email to the customer. With the following template:
Subject: Your order_____ has been sitting on our docks for over a week now.
We would appreciate if you could please come pickup your order_____ within the next 24 hours, otherwise we might have to cancel your order and restock the items. In the future, if you could mark your orders with a PICKUP date it would help us greatly.
Effective October 1st, 2020 – Orders already PICKED and READY for pickup can’t be changed without a replacement PO an RMA, please contact your customer service specialist if you have any question.
Have a great day!
CorpDesign
info@corpdesign.com
C. All IF Which are marked as shipped– ALL SHIPPING METHOD
We need to send an email to when IF status is changed to shipped from another status. The condition is IF created from the sales orders.
Template sample
Subject: Your Order ____ has shipped!
Your order #_____ has shipped – thank you very much for trusting CorpDesign with you office furniture needs!
Have a great day!
CorpDesign
info@corpdesign.com
Our Solution
- For sending emails to customers Notifying Old Open SO. to let them know they have to pick up their order asap. Below are the automated rules I would like to set up. The rules:
a. ALL SO with NO BACKORDERS that are Older than 7 Days:
Solution
This requirement will be achieved by map/reduce script.
We have take each sales order’s “email sent” checkbox checked date for comparing with the current date. This “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this.
The date difference in days is greater than 7 days and less than or equal to 14 days also check the item fulfillment status is picked/pack (created from the specific SO) And sales orders have no backorder items.
The above conditions are satisfied with an email sent to the customer for acknowledgment. And we will flag the sales order with a new check box the 7 days open order email sent to the customer. This will helps to avoid duplicate emails sent to customers.following is the sales order template used in the notification email.
Subject: Your Back Order item(s) is now available for pickup!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which had pending back order item(s) that have been in stock for over 7 days now. Please advise within 48 hours as to when you are planning on picking up this/these item(s). Corp Design’s policy requires dealers to pick up their order(s) within 14 days of the order being placed or when a Back Order item is back in stock. Your help and attention to this matter are highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
Question:
- Where we can add the sales orders document number in the email body?
- If a customer has multiple sales orders within a date difference of 7 days and 14 days. Can we send an individual email to the customer with specific sales orders document number? Or sending a single email including all eligible sales order’s document number to the specific customer.
b. ALL SO with NO BACKORDERS that are Older than 14 Days:
This requirement will be achieved by map/reduce script.
We have to take each sales order’s “email sent” checkbox checked date for comparing with the current date. This “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this.
The date difference in days is greater than 14 days and less than or equal to 21 days also check the item fulfillment status is picked/pack (created from the specific SO) And sales orders have no backorder items.
The day difference greater than 14days and less than or equal to 21days we have to send an email to the customer for acknowledgment also flag the sales order with a new check box the 14 days open order email sent to the customer. This will helps to avoid duplicate emails sent to customers. Following is the sales order template used in the notification email.
Subject: Your Back Order item(s) has been available for pickup for over 2 weeks now!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which had pending back order item(s) that have been in-stock for over 2 weeks now. Please advise within 48 hours as to when you are planning on picking up this/these item(s). Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed or when a Back Order item is back in stock. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
Question:
- Where we can add the sales orders document number in the email body?
- If a customer has multiple sales orders within a date difference of 14 days and 21 days. Can we send an individual email to the customer with specific sales orders document number? Or sending a single email including all eligible sales order’s document number to the specific customer.
c. ALL SO with NO BACKORDERS that are Older than 21 Days:
This requirement will be achieved by map/reduce script.
We have take each sales order’s “email sent” checkbox checked date for comparing with the current date.
This “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this.
The date difference in days is greater than 21 days and less than or equal to 30 days also check the item fulfillment status is picked/pack (created from the specific SO) And sales orders have no backorder items.
This day difference greater than 21days and less than or equal to 30days we have to send an email to the customer for acknowledgment also flag the sales order with a new check box the 21 days open order email sent to the customer. This will helps to avoid duplicate emails sent to customers. Following is the sales order template used in the notification email.
Subject: Your Back Order item(s) has been available for pickup for over 3 weeks now!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which had pending back order item(s) that are now in-stock for over 3 weeks now. Please advise within 48 hours as to when you are planning on picking up this/these item(s). Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed or when a Back Order item is back in stock. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
Question:
- Where we can add the sales orders document number in the email body?
- If a customer has multiple sales orders within a date difference of 21 days and 30 days. Can we send an individual email to the customer with specific sales orders document number? Or sending a single email including all eligible sales order’s document number to the specific customer.
d. All SO with NO BACKORDERS that are Older than 30 days:
This requirement will be achieved by map/reduce script.
We have to take each sales order’s “email sent” checkbox checked date for comparing it with the current date.
This “email sent” checkbox checked date represents the date of all items in sales order back in stock, we have already done a scheduled script for identifying this.
This day difference is greater than 30days and less than or equal to 60days we have to send an email to the customer for acknowledgment also flag the sales order with a new check box the 30 days open order email sent to the customer. This will helps to avoid duplicate emails sent to customers. Following is the sales order template used in the notification email.
Dear valuable customer,
Subject: This email is our last attempt to contact you pertaining order______ – your order may automatically be cancelled – Please contact us immediately!
We have been trying to reach you several times concerning the attached Open Sale Order which had pending back order item(s) that are now in-stock that you have yet to pick up. Please advise within 48 hours if you are planning on picking up this/these item(s), otherwise we will have to cancel it as it has been open for over a month now. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date immediately – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
Question:
- Where we can add the sales orders document number in the email body?
- If a customer has multiple sales orders within a date difference of 21 days and 30 days. Can we send an individual email to the customer with specific sales orders document number? Or sending a single email including all eligible sales order’s document number to the specific customer.
2. ALL OPEN ORDERS without Back Orders With No Shipping Dates AND/OR which Shipping date has past – SHIPPING METHOD: PICKUP
a. ALL OPEN ORDERS With No Shipping Dates that are Older than 7 days:
We have taken all the sales orders with no backorders items and shipping method pickup And the “email sent” check box is checked.
If sales order with no shipping date and that orders are older than 7 days on or before 21 days from all items back in the stock date.
In such cases, we have sent emails to the customer for notifying the customer.
Solution
We have taken all the sales orders with no backorders items and shipping method pickup And the “email sent” check box is checked and with no shipping date.
We have found out the difference between sales order created date and all items back in stock date difference.
This day difference is greater than 7days and less than or equal to 14days and all the above conditions are satisfied we have sent emails to customers. Also, we have flagged the sales order with checkbox shipping date “No shipping date above 7 days”
With the email body :
Subject: Your order_____ is available for pickup!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which has been available for pickup for over 7 days now. Please advise within 48 hours as to when you are planning on picking up this order. Corp Design’s policy requires dealers to pick up their order(s) within 14 days of the order being placed. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date.
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
b. ALL OPEN ORDERS With No Shipping Dates AND/OR Shipping date has past that is Older than 14 days:
We have taken all the sales orders with no backorders items and shipping method pickup And the “email sent” check box is checked.
If sales order with no shipping date and that orders are older than 14 days on or before 30 days from all items back in the stock date.
In such cases, we have sent emails to the customer for notifying the customer.
Solution :
We have taken all the sales orders with no backorders items and shipping method pickup And the “email sent” check box is checked and with no shipping date.
We have found out the difference between sales order created date and all item back in stock date difference.
This day difference is greater than 14days and less than or equal to 30days and all the above conditions are satisfied we have sent emails to customers. Also, we have flagged the sales order with the checkbox shipping date “No shipping date above 14 days”.
With the email body :
Subject: Your order_____ is available for pickup!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which has been available for pickup for over 2 weeks now. Please advise within 48 hours as to when you are planning on picking up this order. Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to secure a pickup date – Thank you!
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
c. ALL OPEN ORDERS With No Shipping Dates AND/OR Shipping date has past that are Older than 30 days:
We have taken all the sales orders with no backorders items and shipping method pickup And the “email sent” check box is checked and with no shipping date.
We have found out the difference between sales order all item back in stock date and current date difference.
This day difference is greater than 30days and less than or equal to 60days and all the above conditions are satisfied we have sent emails to customers. Also, we have flagged the sales order with the checkbox shipping date “No shipping date above 30 days”.
With the email body :
Subject: This email is our last attempt to contact you pertaining order______ – your order may automatically be cancelled – Please contact us immediately!
Dear valuable customer,
As a reminder, we are reaching out to you concerning the attached Open Sale Order which has been available for pickup for over 1 month now. Please advise within 48 hours as to when you are planning on picking up this order or your order may be cancelled automatically. Corp Design’s policy require dealers to pick up their order(s) within 14 days of the order being placed, this order has been at our warehouse for much longer than our policy allows. Your help and attention for this matter is highly appreciated, please contact your customer service representative immediately to arrange your pickup.
For a better and faster experience, please notify us 48 hours before your arrival for pickup so we that can prepare this order in advance – Thank you!
Have a great day!
CorpDesign
info@corpdesign.com
3. ALL IF WHICH ARE MARKED AS PICK/PACK – ONLY ORDERS WITH SHIPPING METHOD: PICKUP
a. IF READY
On the creation of item fulfillment, we have checked the status of IF. If the status is pick/pack and created from the sales order’s shipping method is pickup then send an email to the customer.
We will send emails at the time of IF creation.
Solution :
Create a user event script for achieving this functionality.
The item fulfillment creation the script will check if status and created from the sales order shipping method is the pickup.
If the conditions are satisfied we will send an email to the customer with an email template:
Subject: Your order _________ is ready for pickup!
Dear customer,
SO#_____ is now ready for pickup at our warehouse.
Effective October 1st, 2020 – Orders already PICKED and READY for pickup can’t be changed without a replacement PO an RMA, please contact your customer service specialist if you have any question.
Have a great day!
CorpDesign
b. IF READY FOR OVER 7 DAYS
If the item fulfillment status is picked/pack and the created from the sales order’s shipping method is pickup then we find out the created date and current date difference are greater than 7 days. Then we will send an email to the customer to acknowledge.
Solution:
Create a Map/reduce script for achieving this functionality. Using the script we have take all the IF ( status PICK/PACK and created from sales order shipping method is pickup and current date created from date difference is greater than 7 days). In such a case, we will send an email to the customer. This script will work once a day. Once the email is sent we will make a checkbox and make it true. This will help to resend the same email multiple times for the same customer.
Email template sample:
Subject: Your order_____ has been sitting on our docks for over a week now.
We would appreciate if you could please come pickup your order_____ within the next 24 hours, otherwise, we might have to cancel your order and restock the items. In the future, if you could mark your orders with a PICKUP date it would help us greatly.
Effective October 1st, 2020 – Orders already PICKED and READY for pickup can’t be changed without a replacement PO an RMA, please contact your customer service specialist if you have any question.
Have a great day!
CorpDesign
c. ALL IF WHICH ARE MARKED AS SHIPPED – ALL SHIPPING METHOD
When item fulfillment is shipped, we will send an email to the customer without checking any other condition for acknowledging for orders are shipped.
Solution :
This can be achieved by a user event script. On the mark as a shipped button click, we will send an email to the customer.
Also the status change we will determine the old status and new status using the old record and the new record comparison. If the old record value not shipped and the new record value is shipped then we will send an email to the customer.
Using the email template:
Subject: Your Order ____ has shipped!
Your order #_____ has shipped – thank you very much for trusting CorpDesign with you office furniture needs!
Have a great day!
CorpDesign
info@corpdesign.com
Assumptions
- We have sent one individual email for each customer to notify each sales order. Older than 7, 14,21,30 days.so the same customer get more than one emails if a customer has multiple sales order with satisfying sales orders.
- Our script will send only one email for each condition for a sales order (Older than 7, 14,21,30 days)
- Older than 30 cases which is the specific date range. We will take older than 30 and less than or equal to 60.
- We are calculating the date difference is based on all item back-in-stock date. We have already done a script for sending email to the customer. All the items are back in so. The email send date we have used to identify the date difference
- Is there any attachment necessary for notifying customers with sales order details?
Risks
- A single customer gets multiple emails. For the same sales order. Vary from the conditions.
- There is no sandbox account for testing. The testing can not be done more effectively. For different cases.
- We cant check the real scenario of the email sent.