Automate trial expiration offers with Workflows

A common use case for Retool Workflows is to automate ETL (extract, transform, and load) tasks that transform and process data. Retool apps can directly interact with data from a variety of sources but it's much more efficient to automate ETL tasks than perform them in a frontend application.

This tutorial explains how to build a daily ETL and notification workflow that:

• Retrieves a list customers from a REST API.
• Filters results to exclude customers with personal email addresses and whose trial does not expire within 24 hours.
• Looks up data on company size using filtered email addresses with Clearbit.
• Sends a special offer email using SMTP to customers at companies with fewer than 50 employees.
• Notifies the Sales team in Slack of expiring trials for customers at companies with more than 50 employees.

Considerations

This tutorial uses the following resources to demonstrate a real-world use case of Retool Workflows:

• REST API for customer data and Clearbit interactions
• Slack to notify the Sales team
• SMTP to contact customers with expiring trials

Much of what you'll learn can also apply to other resources and data.

You can generate your own test API and sample data to try out Retool Workflows without using any data sources of your own.

1. Create a new workflow

Sign in to Retool, select the Workflows tab in the navigation bar, then click Create new. Set the name to Trials Expiring Automation.

2. Configure Start block

The workflow initially contains two blocks: Start and Resource query. This workflow should run every day at 9:00 AM.

To configure a daily schedule:

1. Click on the Start block to expand its settings.
2. Set the Trigger to Schedule.
3. Set the Schedule type to Interval.
4. Update the schedule to run every day at 9:00 AM.

The connecting lines between blocks in a workflow represent the control flow. Workflow blocks connect together to perform specific actions in sequential order, beginning with the Start block. Once a block has completed its action, it triggers the next block in the chain, and so on.

3. Look up customers with trials

The first query in this workflow performs a GET request to a REST API that retrieves a list of customers who are currently signed up to a trial. The RESTQuery resource allows you to query an API without first configuring it as a resource.

When run, this query retrieves a list of customer email addresses with trials expiration dates. Click ▸ to save changes and run the query. Finally, rename the query to getUsers.

3. Filter trial expiration and customer email address data

The data must be filtered to exclude trials that do not expire today and to exclude any personal email addresses. This is done using a Filter block.

Click and drag ⦿ to create a connected block, and select Filter. You can also click + in the workflow editor toolbar to add a Filter block, then click and drag ⦿ from one block to another.

Filter blocks allow you to filter data using JavaScript expressions. You can also chain together expressions to create advanced filtering logic. The block loops through each item and returns only data that evaluates as true.

With this block, you can exclude some common email provider domains and also check if a customer's trial is due to expire. The following JavaScript excludes email addresses from gmail.com, hotmail.com, and yahoo.com, and also checks if trial_expiry_date is within the next 24 hours.

!value.email.includes("gmail.com") &&
  !value.email.includes("hotmail.com") &&
  !value.email.includes("yahoo.com") &&
  moment(value.trial_expiry_date).isBetween(
    moment.now(),
    moment().add(1, "day"),
  );

Click ▸ to run the query. Finally, rename the query to filterUsers.

4. Look up company size

Now that you have a list of work email addresses, the workflow can perform an API request with each one to get the company size from Clearbit. First, click and drag ⦿ to create a Query block, then select Loop.

A Loop block automatically iterates through each record in an array of data. As the block loops, it outputs values for value and index. This loop needs to process each email address and make a GET request to Clearbit’s Person API.

First, select Clearbit from the list of resources. Update the query to include a URL parameter of email with a value of {{ value.email }}. As the Loop block iterates, value.email corresponds to a different email address from the array.

Click ▸ to save changes and run the query. Once complete, the Loop block outputs all results from Clearbit, each with person and company objects. Finally, set the name to companyLookup.

5. Send emails or Slack notifications

The workflow should only send a special offer email to companies with fewer than 50 employees. For companies larger than this, the Sales team should be notified in the #new-leads Slack channel instead. To achieve this, you use a Loop block to trigger Functions.

Function blocks are reusable queries that operate outside of the workflow control flow. Any block in a workflow can call a function and pass data as parameters. You can provide test parameter values that allow you to test functions without first passing data from elsewhere.

Configure a Loop block

Add a Loop block named filterCompanySize that's connected to the companyLookup block, them select Run JS Code as the resource.

You will return to the Loop block to add the necessary JavaScript once you add the necessary functions.

Add a function to send emails

To add a Function block, click + in the Functions section of the left panel and select an SMTP resource. This function needs the customer's email address and first name. Set the function's name to emailCustomer then configure the following parameters and test values:

Parameter	Description	Test value
email	The customer's email address.	jenny@example.com
first_name	The customer's first name.	Jenny

Finally, update the template fields using these parameters to compose the email that's sent to customers. Set the To email value to {{email}} and use {{first_name}} in the message body to personalize for each customer.

Add a function to send a Slack notification

Add a Function block for a Slack resource and set its name to pingSales. This function only requires a single parameter, company, that will correspond to name of the company returned by Clearbit. Set this parameter's test value to Test Company and write the message to send:

Trial subscription expiring for {{ company }}!

Update Loop block to conditionally call functions

The data returned by Clearbit is available to the Loop block using value.

Value	Description
value.company.metrics.employees	Employee count.
value.person.email	The customer's email address.
value.person.first_name	The customer's first name.

The workflow should send an email if there are fewer than 50 employees at the company, otherwise it should send a Slack notification. Update the Loop block with a conditional statement to call each function based on the number of employees. Functions are called using await.

if (value.company.metrics.employees < 50) {
  await emailCustomer(value.person.email, value.person.first_name);
} else {
  await pingSales(value.company.name);
}

6. Test and enable the workflow

Now that the workflow is complete, manually run the workflow by clicking Run on the right of the Workflow editor toolbar.

Workflows are not triggered automatically by default. After verifying that the workflow runs as expected, toggle Enable. This activates the Start block's trigger so that it runs on its configured schedule automatically.

Wrap up

Using Retool Workflows, you have now fully automated a complex ETL task that interacts with multiple database tables, transforms data, and aggregates the results.

By applying the lessons learned here and following the same patterns, you can extend the workflow's functionality further, such as sending conditional notifications.