Flow Automation of Receiving Summary Notification Mail for Orders Not Shipped within a Certain Time

[StoreProduct]

Our orders have a delivery time. We would like to receive an e-mail at the end of the day for orders that are not sent within the shipping period. There is a sample theme for the template we want in the Flow library, but this theme "Send email summary with all unfulfilled orders older than 2 days" is insufficient for us. The example we want is written in detail below;

For example: All new orders received during the day will be shipped until 17:00. New orders placed after 17:01 must be shipped by 17:00 the next day. When the delivery time of the order is delayed, we want to receive a summary notification of the orders that are not shipped at the end of the day.

[paul_n]

You would need to change the query in "Get order data" to find orders that were created on those time windows, something like:

created_at:<='{{ "now" | date: "%Y-%m-%d" }}T17:00:00'

Two things to be aware of:
Timezones may make mean you need to adjust the time part.

This would get all orders prior to 17:00 today. I think you want all orders and not just the new ones today that didn't ship, but can't tell from your description. If you want just today, you would need to add a second "created_at" to the query (but > the cutoff from yesterday).

[StoreProduct]

Hello, Thank you for your response and interest. Could you please explain step by step how to apply the code and information you have provided in the shopify panel?

I am explaining the Sample Scenario in detail:
Send a reminder email at 19:30 if all orders received before 17:00 and 17:00 have not been shipped by 19:00 on the same day.

[paul_n · Accepted solution]

In the "Get order data" step, you need to change query to "Advanced" and enter this:

created_at:>'{{ scheduledAt | date_minus: "1 days" | date: "%Y-%m-%d" }}T17:00:00' AND fulfillment_status:unfulfilled AND NOT status:cancelled AND created_at:<='{{ scheduledAt | date: "%Y-%m-%d" }}T17:00:00'

(I switched it to scheduleAt instead of now because if it was ever delayed you wouldn't want the current date/time). 

I would test that this matches what you want in the notification. 

In the email body, you would click "add a variable" and choose "Get Order Data" and whatever info you want in the message. It will insert a loop for you to get started. 

[StoreProduct]

Hello, we need another example scenario. Can you help me?

For example: Send a reminder e-mail for all orders received between 17:00 yesterday and 14:00 today that have not been shipped.

In addition:
- If there is the word "abc cargo" in the cargo name, it should be excluded,
- Exclude if the payment method name contains the word "xxx" and the payment status is "payment pending",

[paul_n]

The queryable fields are listed here: https://shopify.dev/docs/api/admin-graphql/2023-07/objects/order#query-orders

You can usually do a similar search in the Admin on the orders list and look at the URL to see the values you need to use for these fields. 

I'm not sure what "cargo name" is supposed to be in Shopify, so can't help there. The payment method probably only allows searching by ID, so you might not be able to do that in the query.