Prerequisites: SQL skills
With the volume of data increasing exponentially, it’s critical for businesses focused on e-commerce to leverage that data as quickly and efficiently as possible. Machine learning represents a disruption to increase predictive capabilities and augment human decision-making for use cases like price, assortment and supply chain optimization, inventory management, delivery management, and customer support. In this ‘how-to’ guide, we’ll provide step-by-step instructions showing you how to simply make predictions using Shopify order data and MindsDB, an open-source in-database ML framework. It doesn’t require significant data-science skills (“low code”) and at the same time has unique advantages for time-series predictions.
This guide consists of three parts:
- Exporting Shopify orders to a CSV file and uploading it to MindsDB.
- Create a Predictor for forecasting purchase orders (a machine learning model that learns from Shopify data).
- Make Predictions with simple data queries
STEP 1 Exporting Shopify orders to a CSV file
Shopify users are able to extract their orders’ data into a CSV file from their Shopify accounts. To do so, follow this Shopify tutorial to export orders along with their transaction histories or you can export only the transaction histories. This will be the data source to train the machine learning model.
Exploring ML predictions with Shopify Data using MindsDB
MindsDB makes it convenient to create and train machine learning predictive models with their GUI’s SQL Editor using simple SQL syntax. MindsDB’s GUI can be accessed either on Cloud or local via the URL 127.0.0.1:47334/. Make sure you have signed up for a free Cloud account or installed MindsDB locally.
For this tutorial, we will make use of the MindsDB cloud to:
- Upload Shopify order data to MindsDB.
- Create and train a Machine Learning Predictive Model.
- Make a Prediction.
Upload Shopify orders data to MindsDB
Once the Shopify orders data file is exported from the Shopify store, we will access MindsDB GUI via Cloud to upload the data:
- On the GUI’s landing page, select the button Add Data or the plug icon on the left side of the bar.
- The screen will navigate to the page Select your data source. Select the Files option and choose the Shopify button as your data source.
- The screen will navigate to the Shopify Upload Data page for you to import your data file. Under Upload a file, click the tab to browse files on your local device and select your Shopify order data file.
- Once the file is 100% uploaded, provide a name for the file you have uploaded that will be saved as the table name. For this tutorial, it will be named shopify_orders.
You will be automatically directed to the SQL Editor where you can query the data file you have uploaded.
SELECT *
FROM files.shopify_orders
LIMIT 10;
Now that the data has been successfully uploaded, we can move on to create and train a machine learning model.
STEP 2 Create a machine learning predictor
Now, let’s specify that we want to forecast the Total column, which is a moving average of the historical price for sales. However, looking at the data you can see several entries for the same date, depending on several factors: the vendor, the line item, and the shipping country. We want to generate forecasts to predict the behavior of Total (price) by vendor, line-item, and country for the 7 days. MindsDB makes it simple so that we don’t need to repeat the predictor creation process for every group there is. Instead, we can just group for both columns and the predictor will learn from all series and enable all forecasts!
In the SQL Editor, we will make use of the CREATE PREDICTOR statement to create and train a model.
The basic syntax for training a time-series predictor is:
--specify predictor name, target and the source of data for training:
CREATE PREDICTOR mindsdb.[predictor_name]
FROM files
(SELECT [sequential_column], [partition_column], [other_column], [target_column] FROM [file_name])
PREDICT [target_column]
--specify sorting and grouping for time-series predictor:
ORDER BY [sequantial_column]
GROUP BY [partition_column]
--specify time-series interval of how many rows to look back and how many future predictions to make:
WINDOW [int]
HORIZON [int];
Select the Run button or Shift+Enter to execute the query. If successful, the message Query successfully completed will appear in the console.
You can check the status of the model:
SELECT * FROM mindsdb.predictors
WHERE name='shopify_test_model'
This step prompted the MindsDB AutoML engine to select a suitable model based on performance; you can use DESCRIBE command to see how the model was built when training is complete. On the other hand, the USINGsyntax will give you full control over how to build your next model.
STEP 3 Make Predictions
Once the model’s status is complete, you can query it as a table to get forecasts for a given period of time. Usually, you’ll want to know what happens right after the latest training data point that was fed, for which we have a special bit of syntax, the “LATEST” keyword:
SELECT m.'Created at' as date, m.Total as forecast
FROM mindsdb.shopify_test_model as m
JOIN files.shopify_orders as t
WHERE t.'Created at' > LATEST AND t.'LineItem name' = 'basketball top' AND
t.'Shipping Country'='Spain' AND t.'Vendor'='store1'
LIMIT 7;
Now, try changing LineItem Name to ‘winter coat’, Vendor to ‘store2’ or Shipping Country to the ‘Netherlands’, and see how the forecast varies. This is because MindsDB recognizes each grouping as being its own different time series.
Note: for the convenience of analysis it is better to use MindsDB with your database or analytical solution. It speaks the SQL Wire Protocol and thus works with the majority of business intelligence tools.
CONCLUSION
We have successfully created a machine learning predictive model using a Shopify order data file and made predictions. MindsDB is an amazingly helpful tool that puts the power in your hands to create and analyze predictions in order to make better business decisions for your Shopify store!
