Automate metrics reporting with Workflows

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.

Considerations

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

• PostgreSQL
• Salesforce
• Slack
• SMTP

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.

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. 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:

revenueSinceMonthStart

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

totalSalesRevenue

SELECT SUM(Annualized_Amount__c) from Business_Unit__c where BU_Status__C = 'Active'  AND Has_credits_remaining__c = FALSE

currentBusinessCasePipeline

SELECT SUM(Amount) from Opportunity where StageName = '(5) Validating Business Case/Negotiating'

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.

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>"
);

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.

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>

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.

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.

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.