We’re delighted to introduce the general availability of the new Metrics Report API. A GraphQL API that makes it easy for developers to build sophisticated reports into their web and mobile apps based on aggregated metrics. Metric Report allows you to return tabular data containing metrics grouped by one or more dimensions. That’s a mouthful, so let’s break it down with a simple example.

Our TacoSoft order events table

Let’s say we’ve got a set of data flowing into Propel that shows order events for 🌮 TacoSoft, our incredible online taco ordering system.

Creating our Metrics

We can create a <span class="code-exp">TOTAL_SALES</span> Metric in Propel, which in plain language we can describe as “the sum of all orders’ prices that are not canceled.” If we showed that as a time series, we’d see $77.49 as our total for 2023-03-29. We don’t want to include orders that are canceled, so when setting up the Metric we’ll filter them out:

‍

Then we can create a metric for <span class="code-exp">TOTAL_ORDERS</span>, which we can describe as “the sum of all tacos ordered that aren’t canceled.” Notice how <span class="code-exp">TOTAL_SALES</span> is a currency amount and <span class="code-exp">TOTAL_ORDERS</span> is a quantity. If we showed that as a time series, we’d see 15 for 2022-02-29.

So far, so good! We can have two-time series visualizations showing <span class="code-exp">TOTAL_SALES</span> and <span class="code-exp">TOTAL_ORDERS</span>, and we can break that down by day:

We could use <span class="code-exp">store_location</span> as a dimension to filter the results so we could see each visualization for a particular location. But let’s say we want to break it out by location and show it as a table.

What we want is for a given time period to show a table like this:

This is a Metric Report. We’re using the metrics we’ve already created and then telling Propel what dimension to group by. In this case, the store_location is the dimension we want to group by.

<aside>💡 For using metrics with a Metric Report, we need to ensure that we’ve added the dimension we intend to group by to at least one of the metrics.</aside>

Making our Metric Report Query

We already have the Total Sales and Total Orders Metrics set up, so let’s look at how to query a Metric Report using Propel’s GraphQL API.

To learn more about the basics of the Propel API in general, click here. Here’s the GraphQL query we are going to make:

query {
  metricReport(input: {
    timeRange: {
      start: "2023-01-01T00:00:00Z"
      stop:  "2023-02-01T00:00:00Z"
    }
    dimensions: [{
      displayName: "Location"
      columnName:  "STORE_LOCATION"
    }]
    metrics: [{
      displayName: "Sales"
      id: "MET11111111111111111111111111"
      sort: ASC
    }, {
      displayName: "Total Orders"
      id: "MET22222222222222222222222222"
    }]
    orderByColumn: 3
    first: 10
  }) {
    headers
    rows
    query {
      durationInMilliseconds
      recordsProcessed
      bytesProcessed
    }
    pageInfo {
      startCursor
      endCursor
      hasNextPage
      hasPreviousPage
    }
  }
}

This will return data like this:

{
  "data": {
    "metricReport": {
      "headers": [
        "Location",  "Sales",  "Total Orders"
      ],
      "rows": [
        ["NEW YORK", "158.54", "34"],
        ["ATLANTA",  "135.55", "16"]
      ],
      "query": {
        "durationInMilliseconds": 212,
        "recordsProcessed": "396",
        "bytesProcessed": "28956"
      },
      "pageInfo": {
        "startCursor": "eyJvZmZzZXQiOjB9",
        "endCursor": "eyJvZmZzZXQiOjR9",
        "hasNextPage": true,
        "hasPreviousPage": false
      }
    }
  }
}

With the data returned by the API, we can build our table up:

‍

In terms of ordering, note the <span class="code-exp">orderByColumn</span> parameter. By giving it the value <span class="code-exp">3</span>, we’re telling Propel to sort the returned data by the 3rd column. If we wanted to sort by Location, we’d pass <span class="code-exp">orderByColumn: 1</span>. In our front end, we can set this value and pass it to each request for new data via the GraphQL API, allowing users to sort their table.

Filters inside Metrics

Like with querying metrics, we can also supply filters when specifying our metrics. So, for example, if we only want to see only total orders greater than 20, we can filter:

{
  id: "MET11111111111111111111111111",
  displayName: "Sales"
  sort: ASC
  filters: [{
    column: "tacos_ordered"
    operator: GREATER_THAN
    value: 20
  }]
}

Handling pagination

In our query example above, we passed a <span class="code-exp">first</span> variable set to <span class="code-exp">100</span>. This means we’ll retrieve the first 100 records. But what about when there are thousands of rows of data, and we need to paginate? To handle that, we can use the <span class="code-exp">pageInfo</span> data passed back to us in the query. It shows whether there are more pages of data to render and a <span class="code-exp">cursor</span> that we can pass to subsequent queries to retrieve more results. In our query for a second page of results, we will pass <span class="code-exp">cursor: eyJvZmZzZXQiOjR9</span>, telling the system to retrieve records from that point forwards. To learn more about pagination, check out our documentation page about it.

Conclusion

Once we have our results, we can render it in a table and display it on our front ends. Currently, Metric Report is only available via the API. We’re looking to add it as an experience in the Console, so look out for that soon. To learn more about Metric Report and get started, visit our docs or browse the GraphQL API.

Stay tuned for part 2, where we go through the various options for rendering such a table in React and other front-end libraries.

To get started with Propel, you can sign up here. If you’d like to learn more about Propel, we’d love to speak with you; click here to book a demo today!

‍