Create Delivery Route From Shopify Orders

Create an optimized delivery route once a day with all your deliveries!

Last updated by Red Cap Tom on May 3, 2020 at 13:37 +0300

Introduction

This tutorial came up from this Merchant Needs thread in the Shopify Community, and felt like an easy win for No-Code - it's just mashing two services together, right?

A note

If you don't want to go through the whole process yourself, I can set it up for you on my personal Integromat and Google Cloud accounts. I will make sure it runs at all times, fix any bugs as they come up, and add any additional features as I come up with them. If you'd like me to do that, e-mail me at hey@redcaptom.com and we can discuss the price (it will be monthly, but it won't be high).

The Use Case

Assume you're a Shopify merchant that has a local presence somewhere in the world. You'd like to be able to do delivery to local customers, namely ones that either live by your store or are located in a place you can physically ship to. If you're shipping by yourself - and not using some external service - then it's most likely you need to make a delivery route for your drivers to use when they come to work in the morning.

In an optimal world, the order in which you deliver those orders would not matter - you would just give your driver a list of locations and get on with it. In real life, however, gas and time cost money - and you would like to create the optimal route for your delivery drivers to take in order to deliver as many orders as possible - in as little time as possible. We need to therefore take the list of orders coming in every day, and construct an optimal route out of them.

In addition, it's probably best if we can feed it into some navigation app - like Google Maps - in order to have the route displayed as a sequence of instructions read aloud to the drivers from their mobile phone as they drive.

It feels like a pretty complicated app to build, but I've got a solution that uses a few services (and a tiny bit of code) to make that happen.

Price & Limitations

I have managed to create this scenario, under the limitations listed below, for free. All you need is a Shopify store (you're already paying for one if you're here), an Integromat account (free), and a Google Cloud account (free, requires entering a credit card). Also, your drivers would need Google Maps installed on their phones.

There are a few limitations, though:

  1. After the initial setup, you will receive an email every day with your delivery route for the day's orders. There are, unfortunately, three manual clicks needed at the end of the process - one for going to the route-optimizer website, one for clicking an “Export” button, and one clicking a link to Google Maps. This is not very complicated, but it's worth noting if you're looking for a fully automated solution. I have some ideas on how to 100% automate this, but it's out of scope for this tutorial.
  2. Integromat's pricing structure, at the time of writing, dictates that their free tier contains 1,000 operations and 100 MB of data transfer a month for free. A single operation of this tutorial (i.e. what will be triggered each morning) requires 4 operations and about 2KB of data transfer. This means that you could use it safely 10 times a day and never get a charge from Integromat. This also means that you could choose to run it manually at some given point in the day and not on a scheduled basis (for example, as new orders come in). Email me for more information about that.
  3. Google Cloud's pricing structure, at the time of writing, dictates that their free tier allows for 2 million invocations, 400,000 GB-seconds and 200,000 GHz-seconds of compute time, and 5GB of Internet egress traffic per month. To get to a place where Google charges you for using this tuotrial, you would have to be selling thousands of orders a day, and then some. Do note my warnings about security below, though, as these do pose a billing thread.
  4. There is a limit of 23 orders per route, imposed by Google Maps inability to display routes with more than 25 locations (so 23 drop-off locations with your warehouse as the first and last location in the route). However, if you don't require Google Maps at all and just need a list of directions or a map without actual navigation, there's a way to bypass that. Stay tuned.
  5. If you offer day-to-day deliveries, this process is not for you. It will only grab the orders available at the time of the day you dictated for the process to run, and will not auto-update during the day. As I mentioned before, though, you could choose to run it manually at some given point in the day and not on a scheduled basis - email me for more details.

A Note About Zipcode Validation

If you're on this path, you probably also want to make sure only relevant orders are coming into your store. A great way to do that is to use a zipcode validation app - something like this - that makes sure you're only accepting orders that you can actually deliver.

Setup Integromat - Basic Setup

Let's get started! First, sign up for an account in Integromat if you don't have one. It's best to sign in using your Google account, since you can later use your Gmail account seamlessly from inside integromat. You don't have to if you don't want to, but I'm going to use it in this tutorial. Log into your Integromat account and then:

  1. Create a new scenario:

  1. Add Shopify, JSON, HTTP and Gmail to it:

  1. Name the scenario - I used Create Daily Delivery Route:

  1. Schedule your scenario - I set it to happen once a day at 06:00 o'clock in the morning, but you can set it up whenever you want to:

Setup Integromat - Connect Shopify

We can now go ahead and add the first module in our scenario - the one that queries Shopify for open, paid orders that need to be delivered.

  1. Add a Shopify module:

  1. Make sure to use the Search for Orders capability - we'll later filter out all orders that have already been fulfilled:

  1. Connect it to your store - you need to first add a new connection, by adding your store's subdomain (if https://STORE_NAME.myshopify.com is your store's address, STORE_NAME is your subdomain). If you don't remember your subdomain, log in to your store and look at the address bar - it should show it.

  1. A pop-up should open up, asking you to install an application to your store. Click Install unlisted app to continue (if you get a Token Doesn't Exist error, close the pop-up and then click Continue in the Create a Connection screen again):

  1. Filter for relevant orders only - we'd like to get open orders, that have been paid, but not shipped. Make sure to put a limit of 23 on the order number, to make sure you don't get stuck with Google Maps:

Setup Integromat - Parse Orders as JSON

We want to do a little bit of organizing of the data before we move on to actually using it. Integromat offers a JSON module, which will enable us to take multiple Shopify order bundles (which is just an Integromat term that refers to whatever came through an integration with some service - in this case, Shopify orders) and convert them into a single “thing” that we can later use.

That single “thing” will be a JSON string (no need to get into what that means exactly right now, how it differs from a JSON array or a JSON object or what JSON even is - more info here, if you're interested). So - let's setup the JSON module:

  1. Add a JSON Aggregator module:

  1. We can now connect the two modules:

  1. In order to understand how the information is coming from Shopify into Integromat, we will do a test run (disregard the errors about a transformer should not be the last module in the route and run anyway- we're just testing for now):

  1. Click the white bubble that shows up on top of the Shopify module - you should see something like this if you have orders in your store:

  1. We would now like to have our JSON module understand what it should create. For that, we will use an Integromat Data Structure, which is a way of describing a relation between things (again, no need to go into it - more info about JSON-encoded data structures here). Click the Add button next to the Data Structure field:

Note: You should probably name your data structure something meaningful, like Parse Shopify Orders. It doesn't actually matter, but it's more legible that way for later.

  1. We will now create fields for the relevant information we need to construct a full customer address - Address 1 & Address 2 (Shopify uses a 2-line address format), Zip, Country and City. This is enough information for our route-optimizing service (again, more about this later) to do its thing. For each of the items I listed before, enter their name in the Name field, choose a Type of Text and click Add. An example for Address 1 in the image below:

  1. You can then click Save, and should have something like the following (if your order of fields is a bit different than mine it doesn't matter, just that the names are the same):

  1. You now need to “map” the address of each order into this field. This actually “aggregates” all the orders into one JSON string that we can later use. Click each field, and select the relevant piece of information from the popup that will come up to the right. You should eventually have something like this - and can click OK once you're done:

We can now move on to actually parsing the data.

Setup Google Cloud Functions

We'd like to get our nicely-formatted order information into the form our route-optimizing service wants it to be in. This can be done by using the JSON module Integromat provides (like we did in the last step), along with some clever use of the Flow Control modules. But, since I am a programmer after all, I'm going to save you the trouble of trying to set it up with Integromat (took me a total of 6 nodes, and even then did not work properly) and just use a bit of custom code to achieve the same result.

Combining No-Code Solutions With Code

There is no right and wrong when it comes to programming - there is what works, how reliably it works and how easy it is to construct it. To me personally, the fact that Integromat took the time to create the connection to Shopify and to Gmail and their great connector system is well worth the use of the service. However, using the platform to construct something as simple as data parsing proved difficult, so I wrote a small piece of code to solve it.

Disregard any dogma some people put forward - you're businesspeople. You need to get the job done, and the tricky details of whether this is the “correct” solution or not matters little when orders are not being delivered. Use what works.

Since Integromat does not actually offer any custom function module (come on Integromat - if you're reading this, give us JavaScript!), then we will have to use an external service that knows how to compute those types of functions.

This sounds a bit complicated, but it's actually quite a popular trend - it's called “Serverless”, and consists of products by several of the big cloud providers:

  1. Amazon Web Services - Amazon Lambda
  2. Google Cloud Platform - Google Cloud Functions
  3. Microsoft Azure - Azure Functions

These are mini-programs that run based on something being sent to them (like orders from a Shopify store), and output something else based on how you define then (like a URL for a route-optimizing service). I chose Google Cloud Functions for no other reason than the fact I know it well - you can use any other one pretty much the same way.

For this, though, you will need a Google Cloud account.

A note about billing

Google Cloud requires entering a credit card to use it. In the context of this tutorial, we are not doing anything that would trigger any billing whatsoever. However, as you will see in the following section, there is one place where you can be exposed to extra billing if you don't take care. Just as a reminder, if you choose to use my services by emailing me, this will never be a concern.

Create a Google Cloud Account

If you have a Google account, it's really easy to setup a Google Cloud account (note I omitted some of the pictures here since it's not really in scope for the article):

  1. Go to Google Cloud and click Get Started For Free.
  2. Sign up with your Google account - this should be a 2-step process, where the first one will require choosing the Google account you want to sign up with, and the second one will involve inputting your credit card.
  3. You will be asked to create a new project by going here and choosing a name. Choose a random name by going here, copying the random password in the middle of the page, and using that as your project name. The name of your project is part of what constructs your function's URL, and needs to be hard to guess. Let Google Cloud choose all the defaults for you, as it does not matter at this point.

  1. You might be prompted to allow billing and then to enable billing by a banner on the top of the screen. Approve anything that comes up - as I mentioned above, with some caution you will never incur a single charge.

Create the Google Cloud Function

  1. Go here to create a new Google Cloud function (see the next step for what you need to enter):

  1. As mentioned before, the URL for your function needs to be hard to guess. The name of your function, like the name of your project, is part of the function's URL. Choose a random name by going here, copying the random password in the middle of the page, and using that as your function name.

  2. Check the “Allow unauthenticated invocations” checkbox.

Do Not Share This Function's URL With Anyone

Anybody who has the URL can use it, and then make you incur the charge for its use. We took steps to ensure it's hard to guess it, but that would matter little if someone else has it. Keep it safe. I must note that there is a smarter way of approaching this, that has to do with creating an additional Google Cloud user and providing its username and password as part of the HTTP request in the next stage. If you use my services by emailing me, I will set it up for you on my private account, which means that it is I who's on the hook here for extra billing - and not you.
  1. Copy and paste the following code into the INDEX.JS box - make sure to change YOUR_START_LOCATION to the full address of where your deliveries are starting from (the actual form of the address does not matter - just enter anything you use on a navigation app like Waze or Google Maps):
/**
 * Maps Shopify orders (coming from Integromat) into Optimap URLs
 *
 * @param {!express:Request} req HTTP request context.
 * @param {!express:Response} res HTTP response context.
 */
exports.parseShopifyOrdersIntoOptimapURL = (req, res) => {
  const orders = req.body;
  const startLocation = "YOUR_START_LOCATION";
  const baseOptimapURL = "http://gebweb.net/optimap/index.php";

  // This is a for loop and not a reducer since we need to increment the `loc` with every new location
  let locationString = "";
  // Note that loc0 is actually hard-coded
  for (i = 0; i < orders.length; i++) {
    const address1 =
      orders[i]["Address 1"] == null ? "" : orders[i]["Address 1"] + ",";
    const address2 =
      orders[i]["Address 2"] == null ? "" : orders[i]["Address 2"] + ",";
    const city = orders[i].City == null ? "" : orders[i].City + ",";
    const country = orders[i].Country == null ? "" : orders[i].Country + ",";
    const zip = orders[i]["Zip Code"] == null ? "" : orders[i]["Zip Code"];
    locationString +=
      "loc" + (i + 1) + "=" + address1 + address2 + city + country + zip;
    if (i < orders.length - 1) {
      locationString += "&";
    }
  }

  const finalOptimapURL =
    baseOptimapURL + "?loc0=" + startLocation + "&" + locationString;
  res.status(200).send(finalOptimapURL);
};
  1. In the Function to execute box, enter parseShopifyOrdersIntoOptimapURL.

  2. Click Deploy.

  3. You should now wait for the function to be created and deployed - that could take up to a minute. Once you're done, you will see a “Cloud Functions” screen noting your function:

  1. Click your function, and in the screen that opens click Trigger:

  1. Copy the URL - Remember to keep it safe!:

Security By Obscurity

What we did here - “hiding” the URL by making it hard-to-guess - is a well-known example of bad application security. There are better ways to solve the problem than this one in order to protect yourself from unwanted billing. Contact me for details! If the demand will be large enough I will write up a separate tutorial on security for Google Cloud Functions.

Alright, we now have all we need from Google Cloud functions and can get back to integromat.

Setup Integromat - Make a Request to Google Cloud Functions

In order to actually use the function we just created, we'd need to add the function to Integromat. Since the function is triggered by an HTTP request, we can create one using Integromat HTTP module.

  1. Add an HTTP module to your scenario:

  1. Connect it to the JSON module:

  1. Get your Google Cloud Function URL and insert it into the HTTP module. Make sure to choose the same options as the image below (this parses the response as JSON, adds the orders as a payload to the request, and uses POST instead of GET - all interesting things, but out of scope for now). Don't forget to Click OK in the end:

We can now move on to the final step - trigger an E-Mail to your account with the final link for the route.

Get An Optimized Route & Send An E-Mail With It

So, what have we been actually doing so far?

The steps up until now got all the orders from Shopify that are in need of fulfillment, parsed them into a format our route-optimizing mechanism understands, and will leave us with the fastest roundrip route between our warehouse and all the customers. But how does that route optimization actually works?

The Problem Of Route Optimization

Optimizing a route with many different stops is a known hard computation problem, often referred to as “The Travelling Salesperson” problem. It is very difficult to produce an optimal route for a large amount of stops, but for a small amount - like 25 - it's just kinda slow, but not very hard.

In this tutorial I use a piece of software that knows how to solve this problem pretty fast - Optimap. The reason to go through all the hassle of mashing the orders into a specific format is so Optimap stays happy. Optimap outputs a list of the fastest route between all delivery locations, which should save you a significant amount of time going forward and ensure your drivers are as efficient as possible.

Send An E-Mail

So, why are we even sending an E-Mail? No reason, other than it's the easiest thing to set up with Integromat. The e-mail will contain the link to Optimap, a few lines of instructions for the manual steps that I mentioned in the Price & Limitations section of this tutorial, and the link.

  1. Add a Gmail module with the Send an email attribute:

  1. Add a connection by clicking the Add button next to the Connection field and then following the steps that pop-up to allow Integromat to use your Gmail acount to send e-mails:

  1. Edit the Gmail module in the following way - note that the 43.Data blue field comes from the HTTP module right before the Gmail module, which you can get access to by clicking the pop-up that appears next to the Content field:

The message I put in contents, if you'd like to use it, is:

Instructions:
<br />
1. Click  <a href="{{43.data}}">this link</a>
<br />
2. Wait until the page loads and finishes calculating
<br />
3. Press the Export button at the bottom of the map --> "View In Google Maps"
<br />
4. Share the link with your drivers!

Test The Scenario

  1. Click the Run Once button:

  1. Open your e-mail client, and click the link:

  1. An Optimap screen should open up and start calculating the route - this can take a minute or two, remember that this is still somewhat of a difficult problem:

  1. A map with the optimal route should then appear:

  1. Click Export, and then View in Google Maps:

  1. Observe your optimal route, with driving instructions!

What's left now is just to activate the scenario, and you're done!

Activate The Scenario

  1. Save the scenario:

  1. Activate the scenario:

Final Comments

While this is a bit arduous to set up, there is nothing especially complicated about the whole process aside from that custom bit of code in the middle. I would wager that programming the same solution as a custom Shopify app will take about x5 the time it took to write this, and will not have the benefit of relying on battle-tested platforms (Integromat and Google Cloud Platform) for most of the process.

Do note that you could skip the Google Maps part of the process, and just use Optimap directly. This would allow you to have 48 stops instead of 23, but will not allow you to have voice navigation and Google Maps support. Your pick - just change the limit in the Shopify module to 48 instead of 43, and skip the clickedy-clacking at the end. In fact, Optimap has a directions list beneath each route it creates - just scroll the page down!

Did You Find This Useful?

Then you might enjoy my other Merchant Tutorials. Also, if you have an idea for something that needs automating in your store and there isn't an app for it, email me and I might write a tutorial about it too!

A quick reminder: I can set this (and other similiar services) for you on my personal accounts. Just e-mail me for the details.

Troubleshooting

A random place in my country appears in the Google Maps route

This sometimes happens when you are logged into a Google account and do not allow location sharing on your browser. Google seems to use some location that has some association to you, and insert it into the path. Why it does that in a roundtrip searche is beyond me, though. You could just click the X right next to it on Google Maps to get rid of it.