Create billable metrics with the SQL Editor

Billable metrics defined using the SQL Editor are formatted as SQL queries that are run against a table called events, which includes all of the raw usage events that are sent in to Metronome. Similar to the Basic Filters editor, billable metric SQL queries should include a set of filters that identify the correct usage events to query over, and an aggregation to turn these events into a value that Metronome will use as the quantity for downstream line items.

Metronome handles filtering down the SQL queries for individual customers over the course of their billing period. You do not need to include this logic in your queries - simply focus on filtering and aggregation!

Supported features and functionality

Here is how you can access elements of your usage event within the query:

• Query the events table
• You can access the event_type field in the SQL editor by using event_type
• You can access the timestamp field in the SQL editor by using timestamp
• You can access any fields in the properties dictionary by using properties.field_name

If your query returns more than one column, Metronome first looks for a column called value to use as the column for the metric quantity. If no column value exists, Metronome uses the first column returned in the query. Any other columns returned can be used as group keys in downstream pricing and packaging.

info

If extra columns are returned but are not used as presentation or pricing group keys, Metronome sums over all of the results to generate a single quantity for the metric.

Supported functions and operators

The SQL Editor supports these functions and operators.

• Aggregations
  • COUNT: Counts the number of rows
  • SUM: Sums numbers
  • MAX: Takes the maximum of numbers
  • MIN: Takes the minimum of numbers
  • AVG: Averages numbers
  • EARLIEST: Returns the earliest value of a column based on its timestamp
  • LATEST: Returns the latest value of a column based on its timestamp
  • COUNT DISTINCT: Counts the distinct values in the expression
• Math
  • +, -, *, /
  • =, !=, >, <, ≥, ≤
  • LEAST: Returns the least of its arguments
  • GREATEST: Returns the maximum of its arguments
  • ROUND: Rounds a number to a specified number of decimal places
  • CEIL: Returns the smallest integer value that is greater or equal to the input
  • FLOOR: Returns the largest integer value that is less than or equal to the input
• Logic
  • AND
  • OR
  • NOT
  • CASE WHEN
  • IS NULL
  • IS NOT NULL
  • IN
  • NOT IN
  • =
  • !=
• Dates
  • DATE_TRUNC: Truncates the timestamp field to the hour or day.
• Casting
  • CAST

Example creation flow

As an example of a more complex scenario, use the SQL Editor to create billable metric that tracks the daily average of storage used over a billing period.

1. Navigate to the Billable Metrics section in the Metronome app.

2. Click + Add new Billable Metric.

3. Choose SQL query

4. Name your metric (for example, Storage latest daily max).

5. Enter your SQL query:

   SELECT SUM(max_daily_storage) / SUM(num_days) as value, user_id, region
   FROM (
       SELECT
           date_trunc('day', timestamp) as date,
           properties.user_id as user_id,
           properties.region as region,
           MAX(properties.storage_used) as max_daily_storage,
           1 as num_days
       FROM events
       WHERE event_type = 'storage_heartbeat'
       GROUP BY date, user_id, region
   )
   GROUP BY user_id, region
   info

   In this example, user_id is returned so that it can be defined as a presentation_group_key when creating a product from this metric. This allows you to display invoices broken out by user_id. region is returned so that it can be defined as a pricing_group_key . This would allow you to add different rates for this billable metric for values of us-east-1, us-west-1, or ap-south-1.

6. Preview your metric against any existing usage data to ensure the results are correct. When complete, save your metric.