Build a business dashboard with Retool and the Commerce Layer Metrics API.

Let’s start building!

Requirements

Before we dive into the tutorial, let's make sure we have all the necessary requirements in place:

• A Commerce Layer Account: Create one for free simply by clicking the sign up button on this page.
• A Retool Account: It's also free at https://www.retool.com.

And that’s everything you will need today to start building your Metrics API dashboards.

Step-by-step tutorial

Step 1: Setting up Commerce Layer environment

After you've created your Commerce Layer account, follow the Onboarding Tutorial that explains how to create an organization and seed it with test data. If you already have an account, you can use an existing organization or create a new one. It's up to you.

Assuming everything goes smoothly, at the end of this step, you will have:

• A Commerce Layer account
• An organization with some data
• The Commerce Layer CLI installed on your computer

It’s now time to create a new integration application in Commerce Layer that will be used to access Metrics API data. Inside the Dashboard, click on Applications:

Click Add new as per below:

Please note that you might want to use a read-only role for this application as it will only read data from the Metrics API. You can also specify an admin role, but as long as this app will be used only to get Metrics API data, we suggest read only.

Now press Get Credentials and you will be presented with the following page:

The Client ID, Client secret, and Base endpoint will be used in the next step to configure access to the Metrics API. And that’s all for Step 1. You are done here. Celebrate momentarily, then let's get back to work.

Step 2: Creating a new Retool Dashboard

We are going to setup three things in Retool:

1. A new REST API resource to connect to Commerce Layer’s Metrics API
2. Three queries that will be used to get data from the REST API resource that we set up
3. A web application that will be our visual dashboard with charts and controls

Let’s begin.

Setting up a REST API resource in Retool

From the Retool main page click on “Connect a resource” under the Resources section.

Select REST API from the list of available resource templates:

Setting up queries to fetch data from the Metrics API

We are setting up three queries, one for each widget we’ll be showing in the dashboard:

• orders by currency
• best selling products by market
• orders by month

A full explanation of how the Metrics API works can be found in our documentation.

Orders by currency

This query uses the orders/breakdown endpoint. Our documentation describes the orders breakown as:

"aggregations that summarize your data as metrics based on specific operators or statistics, computed on field values. When performing a breakdown query on the Metrics API endpoint you get in the response the value of the computation (based on the selected operator) on the selected field, aggregated by another field."

To create the query, let’s open the Query Library of Retool and click +New

This query will retrieve all orders in the date_from / date_to time window. For each order, we want the total amount.

You might have noticed that if you try to run these queries, you will get an error. That’s expected. The query needs input from the application to fill the variables defining the time window. If you just run the query and no value is provided, the result is an API error.

Creating the web application

Now that we have all the data sources in place, it’s time to build the actual dashboard. Retool visual editor will help us in this task.

Retool is a very powerful tool, and of course we can’t cover the basics in this article. To learn more about Retool and their dashboard components, please refer to the product documentation. For specific info on the available components please refer to the components library. You can download the package of this blog here…

The application layout is structured as a component tree where you can easily drag and drop components. Our application has a single page and contains:

• A header containing the title
• A container for the dates selector
• A container for the widget displaying data.

A new panel will appear, pictured in the screenshot below. Starting in the General Tab:

• Select Import from Query Library as the Resource. We are going to select one of the queries previously created.

• Just below, select Run query automatically when inputs change from the dropdown.

**Every time we change our time window using the previously created date selectors, a new query will be performed and the dashboard will update accordingly.

• Select the query Orders by Currency as created in the previous section. As you’ll recall, the query needs two variables to run: date_from and date_to. The variables will show in the panel, which allows us to “wire” the input coming from our date selector.

• Set date_from as {{date_from.value.substring(0, date_from.value.length-4)}}Z

• Set date_to as {{date_to.value.substring(0, date_to.value.length-4)}}Z

You might be wondering why we truncate the last four digits of the value coming from the Date Time component. We trim the milliseconds because the format is not compatible with the date format expected by Metrics API. (e.g. %Y-%m-%dT%H:%M:%SZ or %Y-%m-%dT%H:%M:%S+/-HH:MM)

• Finally, in the Transform results section, replace the default value with return data.data.

The Metrics API returns the actual data wrapped in a data object, ( documentation), while the first data is Retool’s wrapper for the overall response.

Now let’s go back to our Table component and attach the data source to it. From the Data source drop down, select the code snippet we created before. I used the name ordersbycurrency.

In the Columns section,create the first column named Currency code, and change the following:

• Set Source as order.currency_code
• Set Label as “Currency code”
• Set Format as String
• Set Mapped value as {{item.label}}

In Retool, an item corresponds to the current item of the array that is received as the data source. label is the attribute of the original data we want to be displayed as the column value according to the response of the API we showed before.

Now, create a second column named # of orders and change the following:

• Set Source as order.currency_code
• Set Label as “# of orders”
• Set Format as Number
• Set Mapped value as {{item.value}}

Finally, set the Currency code as the Primary key column. Leave the remaining settings with default values. And that’s it. You should now have a nice table that looks like the example we showed at the beginning of this section.

The Best Selling Product By Market widget

Since this widget is based on the Table component, follow the same steps as in the previous section. However, since we are interested in a different metric, you will create another code snippet that uses the “Best-selling products by market” query we created earlier. Here is the configuration:

Similar to what we did before, we can now create a new table and attach the datasource we just created.

From the Data source drop down, select the code snippet we created before (bestsellingproductbymarket if you named it as I did)

In the Columns section, create a first column named Market name and update the following:

• Set Source as market.name
• Set Label as “Market name”
• Set Format as String
• Set Mapped value as {{item.label}}

Create a second column named Best Seller, and update these:

• Set Source as order.currency_code
• Set Label as “Best Seller”
• Set Format as String
• Set Mapped value as {{(item['line_items.name'][0]).label}} We use the “get 0 pattern” because the query is set up to return data in descending order as seen from the output example above.

Finally, set the Primary key to the Market name column. Leave the remaining settings with default values.

The result will be something like this:

For each month, the Metrics API will return stats about the orders, namely order count, min value, max value, average, and total value. Now that we have set up the data source, we can create our chart. We are going to use Retool's Chart component.

Start by adding a new chart to the dashboard layout tree. Then, in the Content section, update the following:

• Select UI Form (default)
• Set Data source as {{orders_by_month.data}}
• Set Chart Type as Line Chart
• Set X-axis values as {{formatDataAsObject(orders_by_month.data)['date']}} so we extract all the months from the response query.

Take note of the formatDataAsObject. It is a Retool helper method used to transform data structures in different forms, an object in this case. More information can be found .

In the Datasets section we are creating two datasets. Our chart will display both order count and order total value per month. Create the first data set:

• Set the Dataset name as “orders per month”
• Set the Dataset values with this command {{formatDataAsObject(orders_by_month.data).value.map((value)=>value.count)}} .

What we are doing is extracting the count attribute in the value object and creating an array of all the values for the different months. Now, create the second data set:

• Set the Dataset name as “revenue per month”
• Set the Dataset values with this command {{formatDataAsObject(orders_by_month.data).value.map((value)=>value.sum)}} .

Similar to just above, we extract the count attribute in the value object and create an array of all the values for the different months.

Finally, in the Interaction section:

• Set X-axis title as "Month"
• Set X-axis type as "Date"

The result should be something like the below: