Send an Abandoned Cart Reminder Email using Scheduled Triggers

Introduction

Scheduled Triggers allow you to schedule business logic to occur at specific times or intervals.

In this guide, we'll show how to use Scheduled Triggers to send an email reminder to a customer who has added products to their cart but hasn't checked out within one day, otherwise known as an abandoned cart.

The trigger will be executed every hour to check if a user has a cart that hasn't been updated for 24 hours and hasn't already received a reminder. If true, we'll send them an email.

DOCS E-COMMERCE SAMPLE APP

This quickstart/recipe is dependent upon the docs e-commerce sample app. If you haven't already deployed the sample app, you can do so with one click below. If you've already deployed the sample app, simply use your existing project.

Prerequisites

Before getting started, ensure that you have the following in place:

• The docs e-commerce sample app deployed to Hasura Cloud.
• A working SMTP server or email-sending service that you can integrate with to send emails.

Tunneling your webhook endpoint from your local machine

If you plan on using a webhook endpoint hosted on your own machine, ensure that you have a tunneling service such as ngrok set up so that your Cloud Project can communicate with your local machine.

Our Model

When sending transactional emails like this, consider three essential components:

• Your data source: Which table in your database contains the value that you will use to determine whether to send the email?
• Your querying logic: How will your webhook query your database to decide whether to send the email? How will it return information so that you have the correct data to include in the email?
• Your email templating: How will you generate and send the email containing the information you want to send?

Our sample app's database contains, among others, three tables: cart, cart_items, products, and users.

One shopping cart can have multiple cart_items and there is a one-to-many relationship enabled between these tables. Each cart belongs to one user via the user_id foreign key, and we can use the cart_items updated_at field to determine when last a user interacted with their cart.

The products table contains the details of all products, including the product name and description.

The users table contains the details of all users, including the user's email address.

Step 1: Create the Scheduled Event

Go to your Hasura Console and click the "Events" tab. From there, click on the Cron Triggers item in the sidebar. Then, click Create:

Step 2: Configure the Scheduled Event

First, provide a name for your trigger, for example, send_abandoned_cart_email. Enter a webhook URL that will be called when the scheduled event is triggered. This URL should point to the logic you've implemented to:

• Query the database for users who have abandoned their carts
• Generate the reminder email
• Send the email to the correct users

Enter the URL of the webhook to allow Hasura to communicate with it:

https://<your-webhook-url>/abandoned-cart

Tunneling your webhook endpoint to your local machine

You'll need to use a tunneling service such as ngrok to expose a webhook endpoint running on your local machine to the internet and Hasura Cloud. This will give you a public URL that will forward requests to your local machine and the server which we'll configure below.

You'll need to modify your webhook URL to use the public URL provided by ngrok.

After installing ngrok and authenticating, you can do this by running:

ngrok http 4000

Then, copy the Forwarding value for use in our webhook 🎉

In the Cron Schedule field, set the cron expression to 0 * * * *, which means the trigger will be activated every hour.

Our trigger must also have a payload. This payload will be sent to the webhook endpoint when the event is triggered. We don't have to include any data in the payload, but we can if we want to. In this example, we'll simply send a trigger_type property categorizing the event as a check_abandoned_carts. In the Payload section, enter the following:

{
  "trigger_type": "check_abandoned_carts"
}

Under Advanced Settings, we can configure the headers that will be sent with the request. We'll add an authentication header to prevent abuse of the endpoint and ensure that only Hasura can trigger the event. Set the Key as secret-authorization-string and the Value as super_secret_string_123.

Finally, click the "Add Cron Trigger" button to create the Scheduled Event.

Step 3: Implement the Webhook

Your webhook can be a simple HTTP server that performs the desired tasks. It could be written in any programming language or framework you prefer. The webhook needs to do three main things when triggered:

1. Query the database to find users who have items in their cart that were added over 24 hours ago and haven't completed the checkout process.
2. For each user found, generate a reminder email containing the product details.
3. Send the email.

Below, we've written an example of webhook. As we established earlier, this runs on port 4000. If you're attempting to run this locally, follow the instructions below. If you're running this in a hosted environment, use this code as a guide to write your own webhook.

JavaScript

Init a new project with npm init and install the following dependencies:

npm install express nodemailer

Then, create a new file called index.js and add the following code:

const express = require('express');
const nodemailer = require('nodemailer');

const app = express();

// Create a Nodemailer transporter using Ethereal email service
// Ideally, this configuration would be stored somewhere else
nodemailer.createTestAccount((err, account) => {
  if (err) {
    console.error('Failed to create a testing account. ' + err.message);
    return process.exit(1);
  }

  // If all goes as planned, here's the console telling us we're 👍
  console.log('Credentials obtained, listening on the webhook...');

  // Create a transporter object for nodemailer
  const transporter = nodemailer.createTransport({
    host: 'smtp.ethereal.email',
    port: 587,
    secure: false,
    auth: {
      user: account.user,
      pass: account.pass,
    },
  });

  // Our route for the webhook
  app.post('/abandoned-cart', async (req, res) => {
    // confirm the auth header is correct — ideally, you'd keep the secret in an environment variable
    const authHeader = req.headers['secret-authorization-string'];
    if (authHeader !== 'super_secret_string_123') {
      return res.status(401).json({
        message: 'Unauthorized',
      });
    }

    // get our date ready for the query
    const today = new Date();
    const aDayAgo = new Date(today.getTime() - 24 * 60 * 60 * 1000);
    const formattedDate = aDayAgo.toISOString();

    // Fetch the data from our Hasura instance
    async function getRecentNoReminderCarts() {
      const response = await fetch('<YOUR_CLOUD_PROJECT_ENDPOINT>', {
        method: 'POST',
        headers: {
          'Content-Type': 'application/json',
          'x-hasura-admin-secret': '<YOUR_ADMIN_SECRET>',
        },
        body: JSON.stringify({
          query: `
            query EmailsOfUsersWithAbandonedCarts($updated_date: timestamptz!) {
              carts(where: {_and: {is_reminder_sent: {_eq: false}, cart_items: {updated_at: {_lt: $updated_date}}}}) {
                id
                created_at
                user_id
                is_reminder_sent
                user {
                  id
                  email
                  name
                }
              }
            }
          `,
          variables: {
            updated_date: formattedDate,
          },
        }),
      });
      const { data } = await response.json();
      const { carts } = data;
      return carts || [];
    }

    let carts = await getRecentNoReminderCarts();

    console.log('carts: ', JSON.stringify(carts, null, 2));

    // map over the data and send an email to each customer
    async function sendAbandonedCartReminders(carts) {
      let outcomes = [];
      for (let i = 0; i < carts.length; i++) {
        const cart = carts[i];
        // Create a message object
        const message = {
          from: 'SuperStore.com <sender@SuperStore.com>',
          to: `${cart.user.name} <${cart.user.email}>`,
          subject: `You've left items in your cart, ${cart.user.name.split(' ')[0]}!`,
          text: `Hi ${
            cart.user.name.split(' ')[0]
          }, it seems you've left some items in your cart. Don't forget to check out!`,
        };

        // Send the message using the Nodemailer transporter
        const info = await transporter.sendMail(message);

        // Log the message info
        console.log(`\nMessage sent to ${cart.user.name}: ${nodemailer.getTestMessageUrl(info)}`);

        // add the info to the outcomes array
        outcomes.push({
          messageId: info.messageId,
          previewUrl: nodemailer.getTestMessageUrl(info),
        });
      }
      return outcomes;
    }

    await sendAbandonedCartReminders(carts);

    // Return a JSON response to the client
    res.json({
      message: 'Abandoned cart reminders sent!',
    });

    // Here we would run a query to update the database to mark the carts as having received a reminder but we will
    // leave this out for ease of demonstration.
  });

  // Start the server
  app.listen(4000, () => {
    console.log('Server started on port 4000');
  });
});

This script connects to a Postgres database, finds the users who have abandoned their carts, generates a reminder email for each user, and sends it. The script uses the Nodemailer package to send emails.

Python

Make sure you have the necessary dependencies installed. You can use pip to install them:

pip install Flask requests secure-smtplib

Then, create a new file called index.py and add the following code:

from flask import Flask, request, jsonify
import smtplib
import requests
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart
from datetime import datetime, timedelta

app = Flask(__name__)

# function to send emails
def send_email(user_email, user_name):
    # configure your SMTP server
    smtp_server = 'smtp.ethereal.email'
    smtp_port = 587
    smtp_user = '<YOUR_SMTP_USERNAME>'
    smtp_pass = '<YOUR_SMTP_PASSWORD>'

    # create an SMTP connection
    with smtplib.SMTP(smtp_server, smtp_port) as server:
          try:
              server.starttls()
              server.login(smtp_user, smtp_pass)
          except Exception as e:
              print(f"An error occurred while creating the SMTP server: {e}\nWe'll print the message to the terminal\n")
    
          # compose the email
          subject = f"You've left items in your cart, {user_name.split(' ')[0]}!"
          body = f"Hi {user_name.split(' ')[0]}, it seems you've left some items in your cart. Don't forget to check out!"
          sender = 'sender@SuperStore.com'
          recipient = user_email
    
          msg = MIMEMultipart()
          msg['From'] = sender
          msg['To'] = recipient
          msg['Subject'] = subject
          msg.attach(MIMEText(body, 'plain'))
    
          # send the email
          try:
              server.sendmail(sender, recipient, msg.as_string())
          except Exception as e:
              print(f"From: {msg['From']}\nTo: {msg['To']}\nSubject: {msg['Subject']}\n{body}")

# function to fetch data from our Hasura instance
def get_abandoned_carts():
    cloud_project_endpoint = '<YOUR_CLOUD_PROJECT_ENDPOINT>'
    admin_secret = '<YOUR_ADMIN_SECRET>'

    headers = {
        'Content-Type': 'application/json',
        'x-hasura-admin-secret': admin_secret
    }

    query = '''
    query EmailsOfUsersWithAbandonedCarts($updated_date: timestamptz!) {
        carts(where: {
            _and: {
                is_reminder_sent: {_eq: false},
                cart_items: {updated_at: {_lt: $updated_date}}
            }
        }) {
            id
            created_at
            user_id
            is_reminder_sent
            user {
                id
                email
                name
            }
        }
    }
    '''

    # setup formatted time and date for the query
    aDayAgo = datetime.now() - timedelta(days = 1)
    formattedDate = aDayAgo.isoformat()
    variables = {'updated_date': formattedDate}

    # make the query and process the results
    response = requests.post(cloud_project_endpoint, json={'query': query, 'variables': variables}, headers=headers)
    data = response.json()
    return data.get('data', {}).get('carts', [])

# route for the webhook
@app.route('/abandoned-cart', methods=['POST'])
def abandoned_cart():
    # confirm the auth header is correct — ideally, you'd keep the secret in an environment variable
    auth_header = request.headers.get('secret-authorization-string')
    if auth_header != 'super_secret_string_123':
        return jsonify({'message': 'Unauthorized'}), 401

    # fetch and process abandoned carts
    carts = get_abandoned_carts()

    # send email for individual abandoned cart
    for cart in carts:
        user_email = cart['user']['email']
        user_name = cart['user']['name']
        send_email(user_email, user_name)

    return jsonify({'message': 'Abandoned cart reminders sent!'})
    # Here we would run a query to update the database to mark the carts as having received a reminder but we will
    # leave this out for ease of demonstration.

if __name__ == '__main__':
    app.run(port=4000)

This script connects to a Postgres database, finds the users who have abandoned their carts, generates a reminder email for each user, and sends it. The script uses the Flask, Requests and SMTP packages to send emails.

If you see the message Webhook server is running on port 4000, you're good to go!

Step 4: Test your Setup

Now that your Scheduled Trigger and webhook are set up, you can test it by simulating an abandoned cart situation.

With your server running, Hasura should start hitting our endpoint. As we set our cron expression to 0 * * * *, the webhook will be triggered every hour. We don't want to wait that long to test it. For now, update the expression to * * * * * to trigger the webhook every minute. Then, check out your invocation logs in the Hasura Console to verify that the webhook was triggered successfully and your terminal to see the outputted information and a handy link to a the mock email 🎉

Click here to see an example of the console log of the webhook.

carts:  [
  {
    "id": "e2f27008-673d-11ed-8a24-7224baf239e5",
    "created_at": "2023-08-20T16:22:21.68884+00:00",
    "user_id": "7cf0a66c-65b7-11ed-b904-fb49f034fbbb",
    "is_reminder_sent": false,
    "user": {
      "id": "7cf0a66c-65b7-11ed-b904-fb49f034fbbb",
      "email": "seandemo@hasura.io",
      "name": "Sean"
    }
  },
  {
    "id": "e6e0edc0-673d-11ed-8a25-7224baf239e5",
    "created_at": "2023-08-20T12:22:21.68884+00:00",
    "user_id": "82001336-65b7-11ed-b905-7fa26a16d198",
    "is_reminder_sent": false,
    "user": {
      "id": "82001336-65b7-11ed-b905-7fa26a16d198",
      "email": "robdemo@hasura.io",
      "name": "Rob"
    }
  },
  {
    "id": "ea226f5e-673d-11ed-8a26-7224baf239e5",
    "created_at": "2023-08-20T02:22:21.68884+00:00",
    "user_id": "86d5fba0-65b7-11ed-b906-afb985970e2e",
    "is_reminder_sent": false,
    "user": {
      "id": "86d5fba0-65b7-11ed-b906-afb985970e2e",
      "email": "mariondemo@hasura.io",
      "name": "Marion"
    }
  },
  {
    "id": "ee2c0948-673d-11ed-8a27-7224baf239e5",
    "created_at": "2023-08-20T20:22:21.68884+00:00",
    "user_id": "8dea1160-65b7-11ed-b907-e3c5123cb650",
    "is_reminder_sent": false,
    "user": {
      "id": "8dea1160-65b7-11ed-b907-e3c5123cb650",
      "email": "sandeepdemo@hasura.io",
      "name": "Sandeep"
    }
  },
  {
    "id": "f11e43aa-673d-11ed-8a28-7224baf239e5",
    "created_at": "2023-08-20T20:22:21.68884+00:00",
    "user_id": "9bd9d300-65b7-11ed-b908-571fef22d2ba",
    "is_reminder_sent": false,
    "user": {
      "id": "9bd9d300-65b7-11ed-b908-571fef22d2ba",
      "email": "abbydemo@hasura.io",
      "name": "Abby"
    }
  }
]

Message sent to Sean: https://ethereal.email/message/ZOOv8vpfkoUaaaERZOOwe8.vdLF4IYAAAAAAC9ZopFVSeISEbido2clhdIY

Message sent to Rob: https://ethereal.email/message/ZOOv8vpfkoUaaaERZOOwfbf8ObJ559KGAAAADNWlylfrS2nLoNm08jGBs8U

Message sent to Marion: https://ethereal.email/message/ZOOv8vpfkoUaaaERZOOwf8.vdLF4IYADAAAADeBzopphA4UxwgtGz41In74

Message sent to Sandeep: https://ethereal.email/message/ZOOv8vpfkoUaaaERZOOwgfNxowjZihTyAAAADnGmSjpMKPsxQbwhwmVjlcE

Message sent to Abby: https://ethereal.email/message/ZOOv8vpfkoUaaaERZOOwg.NxowjZihT1AAAAD-Lx3fc0Cofq6XFMj6bn69E

Conclusion

In this guide, we've shown how to use Hasura Scheduled Triggers to automate sending reminder emails for abandoned shopping carts. This can be a powerful tool to drive conversion and revenue in e-commerce applications.