Procuring an ecommerce store with the goods that are in demand is among the most important challenges ecommerce owners and managers face.
Failing to do so translates into two of the biggest nightmares you can think of: lost revenue, and poor customer experiences.
Sure, an efficient procurement system is easier said than done, as the related processes can grow incredibly complex in little time.
Procurement can be affected by internal and external factors like sudden spikes in demand, crowded (and often messy) stock and inventory systems and supplier-related issues, to count a few.
What’s most infuriating, however, is that the basic concept behind a functional procurement system is pretty straightforward: It has to let you know when the stock levels of any of your products reach certain levels.
Executing this concept isn’t always that easy.
Think about stores that deal with thousands of products, or whose suppliers are streaky. Even expectable eventualities need to be considered here, such as an inventory manager getting sick at the wrong time.
These are exactly the reasons why procurement automation is relevant, and also why we are releasing this four-piece tutorial on how to automate procurement.
Before we roll up our sleeves, let’s take a quick look at what our automated procurement system does, and see if it fits your current needs.
A simple procurement automation system for Shopify vendors
We will show you how to build an automated procurement system that will do the following for you:
- Keep track of Shopify inventory levels by adding products to Airtable
- Check when stock levels for every product reach a certain point
- Adding products and suppliers to QuickBooks
- Create a purchase order when predefined stock levels are reached
- Send the purchase order to the corresponding supplier
- Receive the invoice from the supplier and send it to the finance department for payment
This system will require four different Integromat scenarios, and rely on a series of widely-used apps, including Shopify, Airtable, QuickBooks, and email.
There are two reasons why we are breaking down the system in four parts. First, because brevity. Sharing all this information in one or two posts is just too much to bear, as each scenario that composes the system requires multiple configuration steps.
Second, and most importantly, is that the four scenarios that compose the system can be used independently, or altogether. Whether you are after purchase order automation or stock level tracking, we’ll get you covered.
With this in mind, it’s time to get started.
Below, you will find a step-by-step tutorial on how to build the first scenario, which will automatically add inventory from Shopify to Airtable to achieve our first goal: Track Shopify inventory levels.
To create it, you will need:
- A Shopify account
- An Airtable account
- An Integromat account
Ready? Let’s get going!
Step 1: Creating the Integromat scenario
After you log in to your Integromat account, you will find yourself in the main dashboard.
Here, click the “Create a new scenario” button on the right corner of your screen.
You will land on the scenario builder, which is where we will create the automation.
Step 2: Adding the Shopify module
Once there, click on the plus sign at the center of the builder, search for the Shopify app in the pop up menu.
Once you find it, click it. The list of available Shopify modules (endpoints) will display in front of you.
Here, select the “Search for products” module. After you do that, the configuration box for that module will appear on screen.
To access your Shopify inventory, you will have to connect your Integromat account to Shopify.
To do this, click the “Add” button in the configuration box.
You can name your connection as you wish. To establish the connection, you will need to enter the subdomain of your Shopify store to continue.
After you establish the connection, all you need to do is increase the number in the “Limit” field.
This field represents the number of products you want Integromat to retrieve everytime the scenario executes.
Here, we recommend entering a high number to ensure all your inventory is included.
And that’s all that we need to configure this module. Once you are done, click the “OK” button, and let’s keep moving forward.
Step 3: Adding the iterator
More often than not, your Shopify store products will have multiple variants, and all should be included when retrieving the main products from Shopify.
There's a particular way to ensure that all the variants are retrieved correctly.
The Shopify module outputs a Variants array element containing a collection of the variant’s attributes, such as title, price, and so on.
As it stands, only the first variant will be processed, which isn’t what we are looking for.
Therefore you need to split the variant data so that each variant can be processed separately.
To do this, we will add an “Iterator” module to our scenario. You will find it in the “Tools” menu at the bottom of the scenario builder.
After you add the Iterator to the Shopify module, simply map the Variants like you see below.
And that’s all we need to move on to the next step.
Step 4: Adding the Airtable app
The Airtable Base and Table in this use case is shown below. Click here and copy the base to your Airtable space to make sure this automation works (you can tweak it afterwards, don’t worry).
When instructing Integromat to add products and product variants to Airtable, it’s important that these don’t get duplicated on the table each time the scenario executes.
To prevent any duplicates from being added on the table, the scenario needs to search the table to check if these products are already there. If it’s not on the table, it will be added. If on the other hand it’s already on the table, only the inventory amount of the product/variant will be updated.
Now, let’s see how to add the Airtable app to your scenario.
You will have to click on the little “plus” sign next to the iterator module, and then search for the Airtable app. Once you find it, select the “Search Records” Airtable module, and click it.
An Airtable API Token is needed to connect your Airtable account to Integromat. Click the “?” in the module configuration box for information about how to get it.
Once you have the token, give your connection a name, paste the token in the field below and hit “Continue”.
Now, all you need to do is select the Shopify Inventory base and Inventory table that we provided to you at the beginning of this step.
Instead of searching for names of the products, it’s more accurate to search for Inventory Item IDs.
You’ll need a simple formula to perform this search, which you can copy from the image below.
The “Inventory Item ID” element is mapped from the Iterator module; select it from the list of elements that will appear when introducing the formula in the corresponding field.
There are two possible outcomes from the “Search Records” module:
- The Inventory Item ID is not found on the table (Product needs to be added)
- The Inventory Item ID is found on the table (Product inventory total needs to be updated)
Keep in mind that when there’s a record that contains the Inventory Item ID, the “Search Records” module will also output the Record ID.
If there’s no record, the Record ID will be empty.
For the scenario to include both outcomes, a native Integromat tool called “Router” needs to be used.
This tool will split the scenario into multiple routes to cater for these outcomes.
You will find the router in the tools section at the bottom of the visual builder. Once you attach it to your scenario, click on it to add two new routes, which are the ones we will need.
Step 5: Adding the modules and filters on the routes
Now that you have a router, let’s use the top route to add new products to the table.
You need to ensure that only if the product is not found on the table this route will be taken.
To do this, we will use a Filter which is another native Integromat feature.
Remember the Record ID mentioned above?
The filter will verify if the Record ID exists or not. If there is no record ID, the data will pass through this route, and the product will be added to the table.
Adding a filter requires you to click on the little wrench sign on the top route.
Doing this will open up the filter configuration box.
We will start by naming our filter (in our case the name is “Add a product”).
Next, map the Record ID outputted by the “Search Records” Airtable module to the top field.
Then, select the “Does not exist” basic operator from the dropdown, and click the “OK” button to finish configuring the filter.
The last thing to do with the top route is adding the “Airtable > Create a Record” module.
After you select the same Base and Table that you did in the “Search Records” Airtable module, we need to map some data to the fields in the configuration box.
First, map the “Title” data element from the “Shopify” module to the “Product Name” field.
Next, map the data from the iterator to the respective fields as shown below.
Please note the following:
For "Maximum Quantity" in the "Item" field, you can map the SKU element if you have entered the max amount for the variant in that field on Shopify, or use the "Inventory quantity" element, since this would usually contain the max amount when you add a new product to your store.
For the "Unit price" field: Only the selling price and not the cost price per item is outputted by the module. As a quick workaround, enter the cost price in the "Compare at price" field on Shopify (located next to the price field).
Don't worry, this will not be shown to your customers. You can then map the Compare at price element to the Unit price field. You can always remove this in Shopify once the item is on the table.
And that’s it for the top route!. Now let’s go to the bottom route.
Set up the filter on this route as well, and name it “Update Inventory Total”.
The only difference here is that you need to select the “Exists” basic operator instead of the “Does not exist” one.
Remember that on this route the inventory totals are updated, so select the “Airtable > Update a Record” module and add it to the bottom route.
Again, select the Base and Table like you did before and map the ID from the “Search Records” Airtable module to the “Record ID” field, which indicates which record must be updated.
It’s normal that a product or variant name changes, and you always want the updated name on the table.
To make sure this happens, map the “Title” element from the “Shopify > Search Records” module to the “Product Name” field and the “Title” element from the iterator to the “Variant Name” field.
See the image below for reference:
And for the most important part of this configuration, map the “Inventory quantity” element from the iterator to the “Quantity” field as you see here:
Now, let’s execute the scenario and see the automation at work.
Step 6: Executing the scenario
You probably noticed a big blue “Play” button at the bottom of the scenario builder. It’s time to click it and check how the scenario works.
Once you click it, the execution should look like this:
If the execution adds the products and variants from Shopify to the Airtable table, then your scenario is correctly configured.
Don’t forget to save the scenario by hitting the “Save” button found under the controls menu!
For the final touch, it’s time to decide how often you want this scenario to execute. In other words, you need to define how often Integromat will check your Shopify store for products, variants, and quantities, and send that information to Airtable.
Whether it’s once a day, twice a week, or once a month, you can set this up in the scheduling setting.
To do this, switch the scenario on by toggling the scheduling switch.
This will open the schedule setting menu, where you can decide the frequency of scenario runs.
Once you do this, hit the “Activate” button, and you’ll be all set.
You made it through! This automation will set you on course to enjoy the benefits of procurement automation, including:
- Less time spent on inventory management
- Protection from eventualities
- A better understanding of your store’s demand profile
Make sure you bookmark this post, as we will be adding the links to the next steps in this article as well.
Happy automating, and see you on our next procurement automation post!