Automate metrics reporting with Retool Workflows

Retrieve sales goal and target data from Salesforce and PostgreSQL, transform the data into a report using JavaScript, and send as an email report.

πŸŽ‰

beta

Retool Workflows is currently in public beta for Retool Cloud organizations. Sign up to get started β†’

Retool Workflows enables you to build complex automations that interact with your data sources.

Use case

A common use case for Retool Workflows is to automatically compile metrics and generate reports by aggregating data from various sources.

This tutorial explains how to build a daily reporting workflow that:

  • Retrieves opportunity, sales, and pipeline data from Salesforce.
  • Retrieves monthly targets from a PostgreSQL database.
  • Transforms and aggregates data using JavaScript.
  • Sends HTML-formatted email reports using SMTP.
  • Sends Slack notifications about any targets not currently met.

Metrics reporting workflowMetrics reporting workflow

Considerations

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

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 Metrics workflow.

Create a new workflowCreate a new workflow

2. Configure Start block

The workflow initially contains two blocks: Start and 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.

Configure a scheduleConfigure a schedule

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. Retrieve Salesforce opportunities data

The first query in this workflow retrieves data for all Salesforce opportunities that closed this year. This includes each opportunity's ID, name, close date, and amount.

Select your Salesforce resource and update the query with the following SOQL statement:

select
  Id,
  Name,
  CloseDate,
  Annualized_Amount__C
from
  Opportunity
where
  StageName = 'Closed Won'
  and CloseDate >= { { moment().format('YYYY-MM-01') } }
  and (
    Business_Unit__r.Subscription_Typ__c = 'Enterprise Annual'
    or Business_Unit__r.Subscription_Typ__c = 'Enterprise Monthly'
  )
  and (Business_Unit__r.BU_Status__C = 'Active')
  and Business_Unit__r.Has_credits_remaining__c = false

Click β–Έ to run the query and rename the block to recentDealsClosed.

4. Retrieve Salesforce revenue data

This workflow needs to retrieve additional data from Salesforce:

  • Month-to-date revenue
  • Total sales revenue
  • Current business pipeline

Rather than create Query blocks that run sequentially as part of the control flow, you can connect multiple blocks in parallel so they run at the same time.

Click and drag β¦Ώ to create a connected Query block, set its name to RevenueSinceMonthStart, and select the Salesforce resource. Repeat this process for each of the required queries and insert the following SOQL statements:

SELECT SUM(Annualized_Amount__C) from Opportunity where StageName = 'Closed Won' and CloseDate >= {{moment().format('YYYY-MM-01')}} AND (Business_Unit__r.Subscription_Typ__c = 'Enterprise Annual' OR Business_Unit__r.Subscription_Typ__c = 'Enterprise Monthly') and (Business_Unit__r.BU_Status__C = 'Active') AND  Business_Unit__r.Has_credits_remaining__c = FALSE AND Script_Created__c = FALSE
SELECT SUM(Annualized_Amount__c) from Business_Unit__c where BU_Status__C = 'Active'  AND Has_credits_remaining__c = FALSE
SELECT SUM(Amount) from Opportunity where StageName = '(5) Validating Business Case/Negotiating'

Salesforce SOQL queriesSalesforce SOQL queries

5. Retrieve goals data

In this use case, a PostgreSQL database contains revenue goals information. This can also be retrieved in parallel. Add another connected Query block, select the PostgreSQL resource, and write a query to retrieve table data:

select * from goals

Change the name of the block to MonthlyGoals.

Query for goalsQuery for goals

6. Format email reports

This workflow sends two email reports:

  • Recently closed deals
  • Overall business metrics

The workflow must first format the reports in HTML before sending them as an email.

Recently closed deals

The workflow iterates through the array of records from the recentDealsClosed block and construct the required HTML markup. To achieve this, you use a Loop block.

A Loop block iterates through an array of items and triggers a Query block for each evaluated item. This is useful for automating repetitive actions or performing complex logic. Click and drag β¦Ώ from the recentDealsClosed block to create a connected Loop block and set its name to formatClosedDeals.

The Query block has access to the value and index variables, which reflect the value of the array item and its index. For this use case, the Query block needs to format number values into currency-formatted amounts and include the required HTML markup for table rows and cells.

return (
  "<tr><td>" +
  value.Name +
  "</td>" +
  "<td>" +
  value.CloseDate.toLocaleString("en-US", {
    style: "currency",
    currency: "USD",
    minimumFractionDigits: 2,
  }) +
  "</td>" +
  "<td>" +
  value.Annualized_Amount__c.toLocaleString("en-US", {
    style: "currency",
    currency: "USD",
    minimumFractionDigits: 2,
  }) +
  "</td></tr>"
);

Format the messageFormat the message

Loop blocks evaluate each array item individually. As a result, the Loop block produces an array of strings, each of which is an HTML-formatted table row. You combine these in a subsequent Query block using join().

Click and drag β¦Ώ from the formatClosedDeals block to create a connected Loop block, set its name to joinClosedDeals, and select the Run JS Code resource. Insert the following JavaScript to join the array items into a single string:

return formatClosedDeals.data.join("");

Overall business metrics

The report for overall business metrics is formatted in much the same way. The difference between these reports is that this one combines data from the totalSalesRevenue and currentBusinessCasePipeline blocks.

Click + in the toolbar, add a Query block named businessMetricsFormat, and select the Run JS Code resource.

Insert the following JavaScript to construct the HTML-formatted markup to send in an email:

const monthly_rev =
  RevenueSinceMonthStart.data["0"].expr0 > CurrentMonthGoal.data
    ? "<td>" +
      RevenueSinceMonthStart.data["0"].expr0.toLocaleString("en-US", {
        style: "currency",
        currency: "USD",
        minimumFractionDigits: 2,
      }) +
      "</td>"
    : "<td> <p style='color:red'>" +
      RevenueSinceMonthStart.data["0"].expr0.toLocaleString("en-US", {
        style: "currency",
        currency: "USD",
        minimumFractionDigits: 2,
      }) +
      "</p> </td>";

const result =
  "<td>" +
  TotalSalesRevenue.data["0"].expr0.toLocaleString("en-US", {
    style: "currency",
    currency: "USD",
    minimumFractionDigits: 2,
  }) +
  "</td>" +
  monthly_rev +
  "<td>" +
  CurrentBusinessCasePipeline.data["0"].expr0.toLocaleString("en-US", {
    style: "currency",
    currency: "USD",
    minimumFractionDigits: 2,
  }) +
  "</td>";

return result;

This block was initially unconnected. When adding references RevenueSinceMonthStart.data, TotalSalesRevenue.data, and CurrentBusinessCasePipeline.data, Retool Workflows automatically creates the block connections.

Business metrics formattingBusiness metrics formatting

7. Send email report

Now that the workflow generates HTML-formatted email reports, add a Query block named sendEmail and select the SMTP resource. Specify the From email, To email(s), and Subject.

Insert additional HTML markup within the Body content to define the email's title and table, then include the output of joinClosedDeals and businessMetricsFormat:

<h1>Overall Business Metrics</h1>
<table border="1" cellpadding="5" cellspacing="5">
  <tr>
    <th>Total Revenue</th>
    <th>Since Month Start</th>
    <th>Pipeline</th>
  </tr>
  <tr>
    {{ BusinessMetricsFormat.data }}
  </tr>
</table>

<h1>Recently Closed Deals</h1>

<table border="1" cellpadding="5" cellspacing="5">
  <tr>
    <th>Name</th>
    <th>Close Date</th>
    <th>Amount</th>
  </tr>
  {{ JoinClosedDeals.data }}
</table>

SMTP query to send the reportSMTP query to send the report

The completed HTML formatting results in an email containing two sections:

# Recently Closed Deals

| Name                               | Close Date | Amount     |
| ---------------------------------- | ---------- | ---------- |
| Transak - Convert to Annual 9/2022 | 2022-09-01 | $31,200.00 |
| Curebase - Renewal 10/2022         | 2022-09-01 | $15,000.00 |
| IDEXX - New Annual 9/2022          | 2022-09-02 | $40,000.00 |

# Overall Business Metrics

| Total Revenue  | Since Month | Start Pipeline |
| -------------- | ----------- | -------------- |
| $42,008,785.59 | $402,746.15 | $8,495,223.09  |

8. Send Slack notifications for missed targets

If current revenue is below the target for the month, the workflow needs to escalate this by sending a Slack notification.

The Branch block enables you to visually construct if...else statements that evaluate data and perform conditional actions. If the condition evaluates as a truthy value, it triggers the block to which it's connected. If not, it triggers another block.

Click + in the toolbar, add a Branch block named checkMonthGoal, and set the If condition to RevenueSinceMonthStart.data['0'].expr0 < CurrentMonthGoal.data. If monthly revenue is below the current target, it evaluates as true.

Branch blockBranch block

Click β–Έ to run the code. As you test your workflow, the condition that evaluates as true highlights in green.

To send a notification, click and drag β¦Ώ from the Branch block's If condition to create a connected Query block. Set its name to updateSlack, select the Slack resource, and enter a message to send.

Send slack messageSend slack message

9. 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 metrics reporting job that interacts with multiple resources, transforms data, and performs conditional actions.

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