Step 3: Define billable metrics

The visual query builder provides a guided interface to define your billable metrics by selecting data tables and applying aggregation logic.

1. Log in to your Zenskar dashboard.
2. Navigate to Usage > Billable Metrics.
3. Click ADD NEW BILLABLE METRIC and choose the VISUAL BUILDER option.
4. Use the Visual Builder to create billable metrics:

First metric: For CPU usage

• Tab 1: Select tables and filters
  • In the Select data table section, use the Table Name dropdown. Select compute_events (the usage event you defined in step 2.1 for CPU usage).
  • (Optional: Use + Add Filter if you need to filter events before aggregation, e.g., by region).
• Tab 2: Map customer
  • Click NEXT to proceed to the Map customer screen.
  • Under Your customer column, select customer_id. This is the field from your compute_events that identifies the customer in your system.
  • Under Zenskar customer column, select external_id. This maps ACME Inc.'s Id in your system to the Zenskar customer's unique external Id.
• Tab 3: Set date field
  • Click NEXT to proceed to the "set date field" screen.
  • Under "column", select timestamp. This maps the timestamp from your compute_events as the primary date field for aggregation.
• Tab 4: Set billable metric over
  • Click NEXT to proceed to the "set billable metric over" screen.
  • Under Select calculation type, choose SUM (as opposed to COUNT) to sum up the values.
  • Under Select column to calculate over, choose data.cpu_hours_consumed.
  • Click CREATE BILLABLE METRIC. Give your metric a name (e.g., Total CPU Hours).

Second metric: For storage usage

Repeat the process by clicking "add billable metric" and selecting "visual builder" again.

• Tab 1: Select tables and filters
  • In the "select data table" section, choose storage_events (the usage event you defined in step 2.1 for storage usage).
  • Ensure the "usage event" checkbox is marked.
• Tab 2: Map customer
  • Map the customer_id field from your storage_events to the external_id in Zenskar.
• Tab 3: Set date field
  • Map the timestamp field from your storage_events.
• Tab 4: Set billable metric over
  • Select calculation type: Choose SUM.
  • Select column to calculate over: Choose data.storage_gb_month.
  • Click CREATE BILLABLE METRIC. Give your metric a name (e.g., Total Storage GB-Months).

The SQL query builder provides a flexible way to define your billable metrics using SQL queries. You have direct control over how your usage data is aggregated.

1. Log in to your Zenskar dashboard.
2. Navigate to Usage > Billable Metrics.
3. Click ADD NEW BILLABLE METRIC and choose the SQL BUILDER option.
4. Use the SQL Builder to create billable metrics:

First metric: For CPU usage

In the SQL editor, enter the following query:

SELECT
    SUM(data.cpu_hours_consumed) AS quantity
FROM
    compute_events
WHERE
    DATE(timestamp) >= DATE({{start_date}}) AND
    DATE(timestamp) <= DATE({{end_date}}) AND
    customer_id = CAST({{customer.external_id}} AS String)

• SUM(data.cpu_hours_consumed) AS quantity: This calculates the total CPU hours. quantity is a common alias for the aggregated value that Zenskar expects for the metric.
• FROM compute_events: This specifies that the data comes from your compute_events usage event.
• WHEREclause: This filters the data to ensure it's within the current billing period and for the specific customer.
  • {{start_date}} and {{enddate}} are Zenskar variables that dynamically provide the start and end dates of the billing period.
  • {{customer.external_id}} is a Zenskar variable that dynamically provides the customer's external ID.

Click CREATE BILLABLE METRIC. Give your metric a name (e.g., Total CPU Hours).

Second metric: For storage usage

Repeat the process by clicking ADD NEW BILLABLE METRIC and selecting "SQL BUILDER" again.

In the SQL editor, enter the following query:

SELECT
    SUM(data.storage_gb_month) AS quantity
FROM
    storage_events
WHERE
    DATE(timestamp) >= DATE({{start_date}}) AND
    DATE(timestamp) <= DATE({{end_date}}) AND
    customer_id = CAST({{customer.external_id}} AS String)

• SUM(data.storage_gb_month) AS quantity: This calculates the total storage in GB-Months.
• FROM storage_events: This specifies the data source.
• The WHEREclause uses the same Zenskar variables for filtering by billing period and customer.

Click CREATE BILLABLE METRIC. Give your metric a name (e.g., Total Storage GB-Months).