Forem: Odumuyiwa Teslim

11 Essential Considerations When Implementing an Ecommerce Cart

Odumuyiwa Teslim — Tue, 07 Mar 2023 13:10:49 +0000

Ecommerce cart functionality is essential for the success of an online store, as it is a way for customers to interact with the store and make their purchases. A properly designed cart can make the shopping experience simple, intuitive, and enjoyable, while a poorly designed cart can frustrate customers and lead to abandoned purchases.

The ecommerce cart plays several important roles in the online shopping process such as allowing customers to manage the products in their carts, display all total information, and more.

This article outlines the crucial aspects to consider for a successful ecommerce cart that satisfies both user needs and technical requirements.

What is Medusa? Medusa is an open source set of ecommerce building blocks that aims to provide a flexible developer experience. Developers and business have full freedom in how they shape their ecommerce application, including how they can implement a cart. Learn how to get started with Medusa in the documentation.

Cart User Experience Considerations

Are you aware that the average shopping cart abandonment rate is 69.82%? The journey doesn't end there despite a customer clicking “add to cart ” on your product page. Several new challenges come into play once a customer places a product into their shopping cart.

Remember that getting potential customers to add an item to their cart is just the starting point. It's an opportunity to convert that into a sale. One key factor in achieving this is the user experience of your ecommerce platform.

Allow Users to Add, Remove and View Items in Their Cart

Customers should feel in control, so provide them with the option to delete items from their cart easily. However, you can also include a confirmation pop-up to ensure the action was intentional.

Make it simple for customers to edit items in their cart as well. For example, provide options to adjust the quantity with "+" or "-" buttons, a drop-down menu, or a box for manual entry. As demonstrated in the example below from the Medusa Next.js storefront:

Ensure that any modifications made to the cart are reflected in the product image and information. The goal is to keep the customer on the cart page and make the buying process seamless. Avoid redirecting customers back to the product page for any changes. The buying journey should be a progressive one.

Be Upfront About Costs

To avoid losing customers, make sure the prices on your cart pages are clear and easy to understand. Hidden fees or unexpected costs can make customers feel tricked and discourage them from completing their purchase.

According to a study by the Baymard Institute, 55% of customers cited extra costs as the main reason for leaving their cart. To prevent this, clearly display taxes and shipping costs on their own line, along with the subtotal and total cost of the cart.

If these costs vary by location, consider adding a tax, shipping calculator, and conversion rate to your page, so customers can see the costs before they checkout. A perfect example is the Medusa storefront sample used below, which shows the subtotal, shipping, and tax fee with the total cost.

Don’t Force Buyers to Create Accounts

Many customers abandon their shopping carts because creating an account on the website can take time and effort. In fact, 34% of people who took a survey by the Baymard Institute cited this as a reason to abandon their shopping carts. To prevent this, offer a "guest checkout" option that allows customers to purchase without creating an account. This way, customers can easily complete a purchase, even if they only plan on buying something once and without any fuss.

Offer Different Ways to Pay

Giving customers many options to pay will make it more likely for them to complete a purchase. If you don't offer a customer's preferred payment on your website, it may discourage them from finishing their purchase. Offering a limited number of payment options can limit customer choice and decrease the likelihood of a sale. You can start with the most popular payment methods, such as credit and debit cards, PayPal, and Google Pay, then you can expand from there.

Source: Nike Checkout

Simplify the Checkout Process

Consolidating everything on a single page can improve customer experience and increase sales by eliminating multiple clicks and guesswork for shipping costs or account creation. Customers can see all necessary information on a single page, including payment processing, shipping details, and order information.

Make Buying on Mobile Easy

Many people shop online using their phones. You could lose sales if your checkout process is not easy to use on the phone. Make sure your checkout page is set up for mobile users so they can easily complete their purchases.

Abandoned Cart Recovery

There are several ways to recover abandoned carts, including sending reminder emails, offering incentives, and displaying abandoned items on the customer's next visit to the website. These methods can remind customers of the items left in their cart and encourage them to complete their purchases.

Offering special discounts or promotions for completing the purchase can also be effective. Another strategy is to track customer behavior, such as which items are frequently abandoned, and address any issues that may be causing customers to abandon their carts.

From a technical perspective, you could implement a cron job that checks for newly created carts, if a cart is older than a specific amount of time, it would send an email to remind the customer of their cart and encourage them to complete their purchase.

Overall, abandoned cart recovery is an important aspect of ecommerce and can lead to significant increases in sales and revenue.

Cart Technical Considerations

Important things to consider when you create and manage an online store include keeping customer data secure, data storage, website performance, and real-time updates.

Security

Ensuring the security of customer information is paramount in an ecommerce site. When choosing a shopping cart solution, you must look for certifications and compliance measures to protect your customers from identity and information theft.

Investing in a shopping cart solution with robust security credentials is crucial to maintaining the trust of your customers. Keeping your customer's information secure and following laws protecting their data is another important part of it. For instance, businesses operating in the EU must comply with the General Data Protection Regulation (GDPR). In the US, the Health Insurance Portability and Accountability Act (HIPAA) are mandatory regulation.

These regulations mandate strict requirements for handling and protecting sensitive customer information.

Sessions, Cookies, and Local Storage

Storing items in a cart between visits is crucial for an ecommerce website. One way to accomplish this is by using sessions, cookies, and local storage. Both are used to store information about the customer's cart on their browser.

Sessions are saved on the website's computer and only last as long as the customer’s tab is opened on the browser. Another session is created if the customer opens another tab for the same site. Once the customer closes the tab, the session is destroyed. Cookies are saved on the customer's computer and can last even if the customer closes the tab or browser. Local storage is a type of web storage that allows web applications to store and access data on the client side (i.e., in the user's web browser) with no expiration.

This lets customers easily pick up where they left off and complete their purchase. Utilizing one of these options ensures customers a smooth and convenient shopping experience.

An example of using local storage is storing cart ID in local storage. The CartID is retrieved from the server stored in the browser's local storage.

// Generate a unique cart ID
var cartId = "<value>";

// Store the cart ID in local storage
localStorage.setItem("cartId", cartId);

This code retrieves the cart information by retrieving the cartID stored in the local storage and then sends the cartID to make a request to the server, which fetches the cart details.

const cartId = localStorage.getItem("cartId")
if (cartId){
    fetch(`/cart?cartId=${cartId}`, {
      method: 'GET',
      headers: {
        'Content-Type': 'application/json'
      }
    })
    .then((response) => response.json())
    .then(({ cart }) => {
        //display cart details
    })
}

This implementation above shows how cookies and local storage could be used together to manage a shopping cart. The cart ID is stored locally to allow the client to retrieve the cart information from the server.

Real-Time Price Update

Updating the shopping cart and total price in real-time as the user makes changes is possible using JavaScript event listeners. For example, when a user clicks on the "add to cart" button, the JavaScript code will update the cart object and re-render the cart UI to reflect the change.

Similarly, when a user changes the quantity of an item or removes an item from the cart, the JavaScript code will update the cart object and recalculate the total price. You can do this by assigning event listeners to the "add to cart", "remove from cart", and "quantity change" buttons.

Additionally, the total price displayed on the page should be updated to reflect the changes. This is just a basic example, you might need to follow extra steps to handle situations like trying to add an item that's unavailable yet or adding the same item more than once.

The site's performance must be considered, as recalculating the total price on every change may negatively affect the performance. Here is an example of how you might implement real-time updating of the cart and total price using JavaScript:

Note: This implementation focuses solely on client-side storage of the cart information. In a real-world scenario, communication with the server would be necessary to ensure the cart information is updated and accurate.

// Assign event listeners to the add to cart, remove from cart, and quantity change buttons
document.getElementById("add-to-cart-button").addEventListener("click", addToCart);
document.getElementById("remove-from-cart-button").addEventListener("click", removeFromCart);
document.getElementById("quantity-change-button").addEventListener("change", updateQuantity);

// Cart object to store the items in the cart
let cart = {};

// Function to add an item to the cart
function addToCart() {
    // Get item details from the page
    let item = {
        id: document.getElementById("item-id").value,
        name: document.getElementById("item-name").value,
        price: document.getElementById("item-price").value
    }
        // Call the calculate total price function
        calculateTotalPrice()

    // Add the item to the cart object
    cart[item.id] = item;

    // Re-render the cart UI to reflect the change
    renderCart();
}

// Function to remove an item from the cart
function removeFromCart() {
    // Get the id of the item to be removed
    let itemId = document.getElementById("item-id-to-remove").value;

    // Remove the item from the cart object
    delete cart[itemId];

    // Re-render the cart UI to reflect the change
    renderCart();

    // Call the calculate total price function
        calculateTotalPrice()

}

// Function to update the quantity of an item in the cart
function updateQuantity() {
    // Get the id of the item and the new quantity
    let itemId = document.getElementById("item-id-to-update").value;
    let newQuantity = document.getElementById("new-quantity").value;

    // Update the quantity in the cart object
    cart[itemId].quantity = newQuantity;

    // Re-calculate the total price
    let totalPrice = calculateTotalPrice();

    // Update the total price displayed on the page
    document.getElementById("total-price").innerHTML = totalPrice;
}

// Function to calculate the total price of the items in the cart
function calculateTotalPrice() {
    let totalPrice = 0;
    for (let itemId in cart) {
        totalPrice += cart[itemId].price * cart[itemId].quantity;
    }
    return totalPrice;
}

// Function to render the cart UI
function renderCart() {
    // Clear the existing cart UI
    document.getElementById("cart").innerHTML = "";

    // Iterate through the items in the cart and render them
    for (let itemId in cart) {
        let item = cart[itemId];
        let itemElement = document.createElement("div");
        itemElement.innerHTML = `<p>${item.name} - $${item.price} x ${item.quantity}</p>`;
        document.getElementById("cart").appendChild(itemElement);
    }
}

Handling Out-of-Stock Items and Inventory Updates

When running an online store, it's essential to have a plan for handling things like items that are out of stock or inventory changes. A good way to handle items that are out of stock is to offer related or replacement items, for example, “Sorry, Nike Air Force 1 is out of stock, but we have the Nike Air Force 2 available.”

Another option is to show a message saying when the item will be back in stock and offer an option for customers to sign up for a mail notification to notify them when the item is available.

You can also let customers backorder items and ship them when they become available. Use inventory software to keep track of stock and update the website in real time. Also, let customers know when an item they're interested in is back in stock via email or a notification.

Limit the number of items a customer can purchase to the available stock and clearly communicate the item's status to customers so they can make informed decisions.

Conclusion

In this article, we cover essential functionalities of an ecommerce cart from the user and technical standpoint, including responsiveness on mobile devices, access to various payment methods, offering guest accounts to new customers, handling out-of-stock items, real-time price updates, and saving the cart information in cookies.

Learn more about how to make a great online store with the Medusa quickstart guide here.

The documentation also provides two important guides related to carts:

For any questions or support, you can reach the Medusa team through Discord.

Predicting Environment Impact of Food Production caused by C02 Emission

Odumuyiwa Teslim — Mon, 31 Oct 2022 22:56:08 +0000

Introduction

In this tutorial, we will be predicting the C02 emissions and their impact on food production with MindsDB. MindsDB is an open-source machine-learning tool that brings automated machine learning to your database. MindsDB offers predictive capabilities in your database.

To complete this tutorial, you are required to have a working MindsDB connection, either locally or via cloud.mindsdb.com. You can use this guide to connect to the MindsDB cloud.

Data Setup

Connecting the data as a file

Follow the steps below to upload a file to MindsDB Cloud.

Log in to your MindsDB Cloud account to open the MindsDB Editor.
Navigate to Add data the section by clicking the Add data button located in the top right corner.

Choose the File tab
Choose the Import File option.
Upload a file Food_Production.csv, name a table used to store the file data (here it is Food_Production), and click the Save and Continue button.
Once you are done uploading, you can query the data directly with the;

SELECT * FROM files.Food_Production LIMIT 10;

Understanding the Dataset

Context

As the world’s population has expanded and gotten richer, the demand for food, energy, and water has seen a rapid increase. Not only has demand for all three increased, but they are also strongly interlinked: food production requires water and energy; traditional energy production demands water resources; agriculture provides a potential energy source. This article focuses on the environmental impacts of food. Ensuring everyone in the world has access to a nutritious diet in a sustainable way is one of the greatest challenges we face.

Content

This dataset contains most 43 most common foods grown across the globe and 23 columns as their respective land, water usage, and carbon footprints.

Columns

Land use change - Kg CO2 - equivalents per kg product
Animal Feed - Kg CO2 - equivalents per kg product
Farm - Kg CO2 - equivalents per kg product
Processing - Kg CO2 - equivalents per kg product
Transport - Kg CO2 - equivalents per kg product
Packaging - Kg CO2 - equivalents per kg product
Retail - Kg CO2 - equivalents per kg product

These represent greenhouse gas emissions per kg of food product(Kg CO2 - equivalents per kg product) across different stages in the lifecycle of food production.

Eutrophication – the pollution of water bodies and ecosystems with excess nutrients – is a major environmental problem. The runoff of nitrogen and other nutrients from agricultural production systems is a leading contributor.

Acknowledgments and Credits

https://ourworldindata.org

Credit: Vivek

Original Source: *Environment Impact of Food Production*

Creating the Predictor

To being, let’s create a predictor that uses the columns to predict CO2 emission. You can learn more about creating a predictor by checking here. You can predict a classification series model using the following syntax

CREATE PREDICTOR mindsdb.[predictor_name]
FROM [integration_name]
    (SELECT [sequential_column], [partition_column], [other_column], [target_column]
     FROM [table_name])
PREDICT [target_column]

CREATE PREDICTOR: Creates a predictor with the name predictor_name in the mindsdb table.
FROM files: Points to the table containing the data.
PREDICT: Dictates the column to predict.

CREATE PREDICTOR mindsdb.CO2_emission
FROM files (SELECT * FROM files.Food_Produuction)
PREDICT Total_emissions

On execution we get:

Query successfully completed

Status of a Predictor

A predictor may take a couple of minutes for the training to complete. You can monitor the status of the predictor by using this SQL command:

SELECT status
FROM mindsdb.predictors
WHERE name='CO2_emission';

Your output should be:

+------------+
| status     |
+------------+
| complete   |
+------------+

Making Predictions

Now that we have our Prediction Model, we can simply execute some simple SQL query statements to predict the target value based on the feature parameters:

Making a Single Prediction

You can make predictions by querying the predictor as if it were a table. The SELECT statement lets you make predictions for the CO2_emission on the chosen feature parameter.

SELECT Total_emissions, Total_emissions_explain
FROM mindsdb.CO2_emission
WHERE Food_Product = 'Rice'
AND Land_use_charge = 0
AND Animal_Feed = 0
AND Farm = 1.4
AND Processing = 0.1
AND Transport = 0.1
AND Packaging = 0.1
AND Retail = 0.3

Making a Bulk Prediction

Now let’s make bulk predictions or multiple predictions to predict the CO2_emission by joining our table with the model.

SELECT * FROM mindsdb.CO2_emission
JOIN files.Food_Production
LIMIT 100;

Expected output should be:

What’s Next?

Have fun while trying it out yourself!

Star the MindsDB repository on GitHub.
Sign up for a free MindsDB account
Engage with the MindsDB community on Slack or GitHub to ask questions and share your ideas and thoughts.

Give a like or a comment if this tutorial was helpful

Predicting & Visualizing Gas Prices with MindsDB and Tableau

Odumuyiwa Teslim — Mon, 31 Oct 2022 22:54:40 +0000

Introduction

MindsDB is an open-source machine-learning tool that brings automated machine learning to your database. MindsDB offers predictive capabilities in your database. Tableau lets you visualize your data easily and intuitively. In this tutorial, we will be using MindsDB to predict the hourly electricity demand in the United States and visualize results in Tableau. To complete this tutorial, you are required to have a working MindsDB connection, either locally or via cloud.mindsdb.com. You can use this guide to connect to the MindsDB cloud.

Data Setup

Connecting the data as a file

Follow the steps below to upload a file to MindsDB Cloud.

Log in to your MindsDB Cloud account to open the MindsDB Editor.
Navigate to Add data the section by clicking the Add data button located in the top right corner.

Choose the Files tab.
Choose the Import File option.
Upload a file (2004-2021.tsv), name a table used to store the file data (here it is gas_prices ), and click the Save and Continue button.

Once you are done uploading, you can query the data directly with the;

SELECT * FROM files.gas_prices LIMIT 10;

The output would be:

Understanding the Dataset

The National Agency of Petroleum, Natural Gas, and Biofuels (ANP in Portuguese) releases weekly reports of gas, diesel, and other fuel prices used in transportation across the country. These datasets bring the mean value per liter, the number of gas stations analyzed and other information grouped by regions and states across the country.

Note: The original dataset was written in Spanish and translated by me into English.

Context

INITIAL DATE
FINAL DATE
REGION
STATE
PRODUCT
Visted_Gas_Station
Measure_Unit
Resell_mean_price
Standard_deviation
Min_Resell_Price
Max_Resell_Price
Mean_Resell_Margin
Resell_Variant_COEF
Mean_Price_Distribution
Standard_Deviation_Distribution
Min_Price_Distribution
Max_Price_Distribution
Max_Price_Distribution

What can be done with this dataset?

How different regions of Brazil saw their gas prices change?
Within a region, which states increased more their prices?
Which states are the cheapest (or most expensive) for different types of fuels?

In this tutorial, we will be predicting the mean_price_distribution.

Creating the Predictor

Let’s create and train the machine learning model. For that, we will use the CREATE PREDICTOR statement and specify the input columns used to train FROM(features) and what we want to PREDICT(labels).

Note: This is a timestamp series model

CREATE PREDICTOR mindsdb.[predictor_name]
FROM [integration_name]
    (SELECT [sequential_column], [partition_column], [other_column], [target_column]
     FROM [table_name])
PREDICT [target_column]

ORDER BY [sequential_column]
GROUP BY [partition_column]

WINDOW [int]
HORIZON [int];

CREATE PREDICTOR: Creates a predictor with the name predictor_name in the mindsdb table.
FROM files: Points to the table containing the data.
PREDICT Close: Dictates the column to predict.
ORDER BY: Shows the column to arrange the data during training.
GROUP BY: It is optional. The column by which rows that make a partition are grouped. For example, if you want to forecast the inventory for all items in the store, you can partition the data by product_id, so each distinct product_idhas its own time series.
WINDOW: Decides how many rows to "look back" into when creating a prediction.
HORIZON: Specifies the number of future predictions. The default is 1.

Let’s proceed to make predictions on our dataset:

CREATE PREDICTOR mindsdb.gas_prices_brazil
FROM files
  (SELECT * FROM gas_prices)
PREDICT Mean_Price_Distribution
ORDER BY FINAL_DATE
GROUP BY REGION, STATE
-- the target column to be predicted stores one row per quarter
WINDOW 300     
HORIZON 100;    -- making forecasts for the next year (next 100 rows)

Status of a Predictor

A predictor may take a couple of minutes for the training to complete. You can monitor the status of the predictor by using this SQL command:

SELECT status
FROM mindsdb.predictors
WHERE name='hourly_electricity_demand';

If we run it right immediately after creating a predictor, we get this output:

+------------+
| status     |
+------------+
| generating |
+------------+

After a while you will get:

+------------+
| status     |
+------------+
| training   |
+------------+

And finally, this should be your output:

+------------+
| status     |
+------------+
| complete   |
+------------+

Making Predictions

Now that we have our Prediction Model, we can simply execute some simple SQL query statements to predict the target value based on the feature parameters:

SELECT Final_Date, Mean_Price_Distrubution
FROM mindsdb.gas_prices_brazil
JOIN files.gas_prices
LIMIT 5;

Expected output would be:

Connecting MindsDB to Tableau

Tableau lets you visualize your data easily and intuitively. This tutorial will use Tableau to create visualizations of our predictions.

How to Connect

First, create a new workbook in Tableau and open the Connectors tab in the Connect to Data window.
Click on MySQL

Input "cloud.mindsdb.com" for Server, "3306" for Port, "mindsdb" for Database "your mindsdb cloud email" for Username, "your mindsdb cloud password" for Password, and Sign in. Now you are connected and your page should look like this:

Visualizing our Data

Before you can visualize predictions in Tableau, you must first choose a data source. And because the predictions in this article are generated using a SQL statement, you will need to create a custom SQL query in Tableau to generate the data source. To do this:

First, select the New Custom SQLon the left side of the window and use the query below to generate the mean price distributions for each date. You can preview the results or directly load the data into Tableau.

SELECT Final_Date, Mean_Price_Distrubution
FROM mindsdb.gas_prices_brazil
JOIN files.gas_prices

Create an extract of the data under the connection heading at the top right of the window. You do this to facilitate data conversion to the appropriate data type. The extraction should take some time.
Move to the Sheet tab on the bottom left and right-click the Mean_Price_Distribution and Date to convert their data types to Number(Decimal) and Date, respectively. Additionally, when right-clicking on the Mean_Price_Distribution and Final_Date, choose the option to convert it to a continuous measure.

Drag the Mean_Price_Distribution measure to the row shelf and the Final_Date dimension to the column shelf.
You can also switch the visualization also to the line, area and etc.

Predicting & Visualizing Petroleum Production with MindsDB and Tableau

Odumuyiwa Teslim — Mon, 31 Oct 2022 20:52:05 +0000

Introduction

Data Setup

Connecting the data as a file

Follow the steps below to upload a file to MindsDB Cloud.

Log in to your MindsDB Cloud account to open the MindsDB Editor.
Navigate to Add data the section by clicking the Add data button located in the top right corner.

Choose the Files tab.

Choose the Import File option.
Upload a file ([national_nameplate_capacity.csv](https://www.kaggle.com/datasets/maurosrgiofigueira/fuel-output-capacity-brazil), name a table used to store the file data (here it ispetroleum_production ), and click the Save and Continue` button.

Once you are done uploading, you can query the data directly with;

sql SELECT * FROM files.petroleum_production LIMIT 10;

The output would be:

Understanding the Dataset

This dataset presents Brazilian petroleum products ouput capacity evolution, from 2011 to 2020 in each Brazilian refinery. Data is displayed in barrels. From the Brazilian National Petroleum Agency.

Context

Refinery - They are 19 refinery in this dataset.
Year - The year of this dataset ranges from 2011 to 2020.
Nameplate Capacity - Nameplate capacity, also known as the rated capacity, nominal capacity , installed capacity, or maximum effect, is the intended full-load sustained output of a facility such as a power station, fuel plant and etc. (Source: Wikipedia)

Creating the Predictor

To being, let’s create a predictor that uses the date to predict the Petroleum Production for refineries. You can learn more about creating a predictor by checking here. You can predict a classification series model using the following syntax

sql CREATE PREDICTOR mindsdb.[predictor_name] FROM [integration_name] (SELECT [sequential_column], [partition_column], [other_column], [target_column] FROM [table_name]) PREDICT [target_column]

CREATE PREDICTOR: Creates a predictor with the name predictor_name in the mindsdb table.
FROM files: Points to the table containing the data.
PREDICT: Dictates the column to predict.

`sql
CREATE PREDICTOR mindsdb.petroleum_output
FROM files (SELECT refinery, Year, Nameplate_Capacity FROM petroleum_production)
PREDICT Nameplate_Capacity

On execution we get:

Query successfully completed

Status of a Predictor

A predictor may take a couple of minutes for the training to complete. You can monitor the status of the predictor by using this SQL command:

sql SELECT status FROM mindsdb.predictors WHERE name='petroleum_output';

Your output should be:

sql +------------+ | status | +------------+ | complete | +------------+

Making Predictions

Now that we have our Prediction Model, we can simply execute some simple SQL query statements to predict the target value based on the feature parameters:

sql SELECT Year, Nameplate_Capacity FROM mindsdb.petroleum_output JOIN files.petroleum_production LIMIT 100;

Connecting MindsDB to Tableau

Tableau lets you visualize your data easily and intuitively. In this tutorial will use Tableau to create visualizations of our predictions.

How to Connect

First, create a new workbook in Tableau and open the Connectors tab in the Connect to Data window.
Click on MySQL

• Input “cloud.mindsdb.com” for Server, “3306” for Port, “mindsdb” for Database “your mindsdb cloud email” for Username, “your mindsdb cloud password” for Password, and Sign in.

Now you are connected and your page should look like this:

Visualizing our Data

First, select the New Custom SQLon the left side of the window and use the query below to generate the petroleum production for each year. You can preview the results or directly load the data into Tableau.

sql SELECT Year, Nameplate_Capacity FROM mindsdb.petroleum_output JOIN files.petroleum_production

Create an extract of the data under the connection heading at the top right of the window. You do this to facilitate data conversion to the appropriate data type. The extraction should take some time.
Move to the Sheet tab on the bottom left and right-click on the Nameplate_Capacity and Year to convert their data types to Number(whole) and Date, respectively. Additionally, when right-clicking on the Nameplate_Capacity and Date, choose the option to convert it to a continuous measure.

Drag the Nameplate_Capacity measure to the row shelf and the Year dimension to the column shelf.
You can also switch the visualization also to line, area and etc.

What’s Next?

Have fun while trying it out yourself!

Star the MindsDB repository on GitHub.
Sign up for a free MindsDB account
Engage with the MindsDB community on Slack or GitHub to ask questions and share your ideas and thoughts.

Give a like or a comment if this tutorial was helpful

Predict Diamond prices with SQL Alchemy and MindsDB

Odumuyiwa Teslim — Sat, 29 Oct 2022 19:40:11 +0000

Introduction

In this tutorial, we will learn how to create, train and query a machine-learning model using a Python library called SQLAlchemy. SQLAlchemy is the most popular open-source library for working with relational databases from Python. It is one of the ORM libraries that provides an interface for using object-oriented programming to interact with a database. MindsDB is an open-source machine learning tool that brings predictive capabilities to your database. For this tutorial, we will be making use of the Kaggle dataset diamond.csv to predict the price of the diamonds.

Prerequisite

The following are the requirements for this tutorial:

Python
SQLAlchemy
MindsDB install via pip
pymysql
Any IDE of your choice preferably VS code.

Connecting our Database

Firstly, we need to upload our dataset into the MindsDB Cloud Interface follow this guide to learn how to upload to the interface here, and then you can download the diamonds.csv dataset on kaggle here

Now, navigate into a working directory , then create a main.py file. To create a database connection, simply follow this guide on how to do that using Sqlachemy and pymysql.

When you are done, you should have something like this:

from sqlalchemy import create_engine

user = "teslimodumuyiwa@gmail.com"
password = "MindsDB Cloud Password"
host = "cloud.mindsdb.com"
port = 3306
database = ""

def establish_connection():
        engine =  create_engine(url=f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}")
        return engine
try:
        engine = establish_connection()
        engine.connect()
        print("Connection to the database is established")
except Exception as e:
        print("Couldn't connect to the database:\n",e)

You can also check that your connection is successful by running the python file from your terminal using python main.py

To run further checks on your connection, you might want to run queries on the database to see if it returns some data.

from sqlalchemy import create_engine

user = "teslimodumuyiwa@gmail.com"
password = "Your MindsDB Cloud Password"
host = "cloud.mindsdb.com"
port = 3306
database = ""

def establish_connection():
        engine =  create_engine(url=f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}")
        return engine
try:
        engine = establish_connection()
        with engine.connect() as eng:
                query = eng.execute("SELECT * FROM files.diamonds LIMIT 5;")
                for row in query:
                        print(row)
except Exception as e:
        print("Couldn't connect to the database:\n",e)

Expected output would be:

Training a Predictor With `CREATE PREDICTOR`

With that done, we can now train our machine-learning predictor. For that, we are going to use the CREATE PREDICTOR syntax where we would specify what query we will train FROM and what to PREDICT.

By implementing the following code to predict the price of our diamond:

from sqlalchemy import create_engine

user = "teslimodumuyiwa@gmail.com"
password = "Your MindsDB Cloud Password"
host = "cloud.mindsdb.com"
port = 3306
database = ""

def establish_connection():
        engine =  create_engine(url=f"mysql+pymysql://{user}:{password}@{host}:{port}/{database}")
        return engine
try:
        engine = establish_connection()
        with engine.connect() as eng:
                query = eng.execute("CREATE PREDICTOR mindsdb.diamond_price FROM files (SELECT * FROM diamonds) PREDICT price;")

except Exception as e:
        print("Couldn't connect to the database:\n",e)

Checking the Status of our Predictor

We can check the status of the model with the syntax below. If the query returns Complete, then the model is ready to be used, or else wait if it returns Training.

query = eng.execute("SELECT status FROM mindsdb.predictors WHERE name='diamond_price';")
                for i in query:
                        print(i)

If the training is not complete, you should get this printed in your terminal:

('training',)

If the training is complete, you should get this printed in your terminal:

('complete',)

Making Predictions

Now that we have our Prediction Model, we can simply execute some simple SQL query statements to predict the target value based on the feature parameters.

Making a Single Prediction

You can make predictions by querying the predictor as if it were a table. The [SELECT](https://docs.mindsdb.com/sql/api/select/) statement lets you make predictions for the diamonds on the chosen feature parameter.

// main.py
query = eng.execute("SELECT price, price_explain FROM mindsdb.diamond_price WHERE carat = 0.23 AND depth = 56.9;")
                for i in query:
                        print(i)

Expected output should be:

Make Batch Predictions with `JOIN`

Now let’s make bulk predictions or multiple predictions to predict the price by joining our table with our model.

bulk = text("SELECT t.price AS real_price, m.price_explain AS explained_price, t.carat,  t.cut, t.color, t.depth, t.table FROM files.diamonds AS t JOIN mindsdb.diamond_price AS m LIMIT 10;")
                query = eng.execute(bulk)
                for i in query:
                        print(i)

On execution, you should get this printed into your terminal

What’s Next?

Have fun while trying it out yourself!

Star the MindsDB repository on GitHub.
Sign up for a free MindsDB account
Engage with the MindsDB community on Slack or GitHub to ask questions and share your ideas and thoughts.

Give a like or a comment if this tutorial was helpful

Forem: Odumuyiwa Teslim

11 Essential Considerations When Implementing an Ecommerce Cart

Cart User Experience Considerations

Allow Users to Add, Remove and View Items in Their Cart

Be Upfront About Costs

Don’t Force Buyers to Create Accounts

Offer Different Ways to Pay

Simplify the Checkout Process

Make Buying on Mobile Easy

Abandoned Cart Recovery

Cart Technical Considerations

Security

Sessions, Cookies, and Local Storage

Real-Time Price Update

Handling Out-of-Stock Items and Inventory Updates

Conclusion

Predicting Environment Impact of Food Production caused by C02 Emission

Introduction

Data Setup

Connecting the data as a file

Understanding the Dataset

Context

Content

Acknowledgments and Credits

Creating the Predictor

Status of a Predictor

Making Predictions

Making a Single Prediction

Making a Bulk Prediction

What’s Next?

Predicting & Visualizing Gas Prices with MindsDB and Tableau

Introduction

Data Setup

Connecting the data as a file

Understanding the Dataset

Context

What can be done with this dataset?

Creating the Predictor

Status of a Predictor

Making Predictions

Connecting MindsDB to Tableau

How to Connect

Visualizing our Data

Predicting & Visualizing Petroleum Production with MindsDB and Tableau

Introduction

Data Setup

Connecting the data as a file

Understanding the Dataset

Context

Creating the Predictor

Status of a Predictor

Making Predictions

Connecting MindsDB to Tableau

How to Connect

Visualizing our Data

What’s Next?

Predict Diamond prices with SQL Alchemy and MindsDB

Introduction

Prerequisite

Connecting our Database

Training a Predictor With CREATE PREDICTOR

Checking the Status of our Predictor

Making Predictions

Making a Single Prediction

Make Batch Predictions with JOIN

What’s Next?

Training a Predictor With `CREATE PREDICTOR`

Make Batch Predictions with `JOIN`