Metrics

A metric is a number you have defined once. net revenue is gross revenue less discounts, for paid invoices, in Australian dollars. Say that here, and every query, report, cherry answer and AI assistant uses it instead of working out its own version.

A metric is written the way you would write SQL for one table, with bare column names and no schema prefix. It is measured on one table, the one you pick when you define it.

Open the panel from the rail: press metrics on the map. The panel lists every metric with its expression and the table it is measured on.

Define a metric

There are two routes, both in the metrics panel.

Write one

Describe it

If you would rather say what you want, press describe a metric, type a sentence such as "refund rate by month" or "the share of orders that came back", and press draft it. If the map has categories, chips under Group by (up to six) add "by region" and the like to your sentence.

sanda drafts the metric from your own tables and columns, in an editable form headed "sanda's draft · check it". It lists the columns the draft reads and how confident it is. Check the SQL, correct it, then choose:

• save metric puts it live in your metrics.
• save for review parks it in the review queue instead, for a second pair of eyes.

If your data cannot support what you described, sanda says exactly what is missing (a column, a table, a join) instead of inventing something. The draft is an AI call, and it is billed like any other. See Learn from my data.

Write the expression

• One SQL fragment. An aggregate, or arithmetic over aggregates. No semicolon, and no comment (-- or /* */). It can be up to 2,000 characters. A fragment that cannot contain those cannot end the statement it is placed in.
• Columns of one table. Use bare names such as unit_price, never mapping.products.unit_price.
• Constants are safe. Text inside quotes, such as 'PAID', is left alone.

sum(unit_price * quantity)
count(distinct customer_id)
avg(total)
count(*) filter (where status = 'refunded')::numeric / nullif(count(*), 0)

When several tables are in one question, sanda writes each column out in full before it runs the query, so two tables that both have a unit_price never make it ambiguous. If a word in a definition is a column of another table in the same question, sanda stops and says which definition, which word and which two tables, and suggests the fix: write the column with its table name, or measure the metric on the other table.

Build on other metrics

Refer to another metric by putting its name in braces. net revenue can be {gross revenue} - sum(discount_amount). Before anything is run, sanda expands the reference, so net revenue arrives as (sum(total)) - sum(discount_amount). You write gross revenue once and reuse it everywhere.

• Names match without regard to case, and spaces around the name are ignored: { Net Revenue } and {net revenue} are the same reference.
• A metric cannot be built out of itself, directly or through another metric. sanda detects the loop, leaves the reference unexpanded, and tells the agent not to use the metric. sanda also refuses to run a metric whose reference names something that is not defined.
• A referenced metric keeps its own condition. paid share can be {net revenue} / nullif({gross revenue}, 0): the top half counts only paid invoices, as net revenue does, and the bottom half counts them all.
• A metric can be built from metrics on other tables, as long as a relationship joins them. sanda works out each part on its own table and then does the arithmetic: with revenue summed over the order lines and order count counted over the orders, average order value is {revenue} / nullif({order count}, 0), and each order is counted once however many lines it has. Without a relationship between the tables, the workbench and run_query refuse it and say which join to draw.
• In a metric that is worked out table by table, keep every column inside an aggregate. Arithmetic around the references, such as nullif(..., 0) or ::numeric, is fine. A bare column outside sum() or count() belongs to no one table's part, so sanda refuses it and names the metric.

To rename a metric, open it, press edit, change the name and save. sanda repoints every metric that refers to the old name, and tells you which ones before you save. Deleting a metric that others refer to leaves those metrics unresolved.

Filters on a metric

The field "only counting rows where" is that number's own condition. net revenue above counts only paid invoices.

It belongs to that one number. When it is the only number in a question, or every number shares it, the condition becomes the query's where. Beside numbers that do not share it, sanda writes it onto that number alone, as filter (where status = 'PAID'), so net revenue and invoice count side by side give paid revenue and every invoice. Two metrics whose conditions disagree, such as paid and draft revenue, sit side by side the same way.

When several metrics, or many questions, need the same condition, name it once as a filter and use it by name.

Unit, period and display

• Unit is text such as AUD. It shows beside the metric on cards and in what the agent is told.
• Also called lists other words for the number. A word that names exactly one metric finds it. If two metrics both answer to revenue, that word is ambiguous and finds neither.

The list view's metrics tab has three more fields:

• Reported per is the natural period, such as month. The agent is told "per month".
• Shape records how the number is built: sum, average, count, distinct count, minimum, maximum, ratio or something else.
• Shown as records how it should read: plain number, currency, percent, whole number, decimal or duration.

The agent's text uses the definition, the unit and the period. Shape and display are kept with the metric and included in the JSON download of what the agent is told.

On a report, Shown as and Unit decide how the metric's figures read on kpi, bar and line blocks, on the canvas, a shared link, the reports portal and the email alike. See blocks.

What sanda does with a metric

Ask for net revenue by region and sanda writes this, from your definitions, with no AI involved:

select (sum("mapping"."billing__invoices"."total")) - sum("mapping"."billing__invoices"."discount_amount") as "net_revenue",
       "mapping"."billing__customers"."region" as "region"
from "mapping"."billing__invoices"
left join "mapping"."billing__customers"
  on "mapping"."billing__customers"."customer_id" = "mapping"."billing__invoices"."customer_id"
where ("mapping"."billing__invoices"."status" = 'PAID')
group by 2
order by 1 desc
limit 16

The reference was expanded, the condition became a where, the join followed a relationship you signed off, and the number is grouped by region, largest first.

The join matters. Reaching from many invoices to one customer is a lookup, so every invoice keeps exactly one customer and the total is the same total. It is a left join, so an invoice whose customer is missing is kept, in a group with no region, rather than dropped from the total.

The other direction is different, because one customer's columns repeat once per invoice. When a question puts a number measured on customers beside the invoices, sanda counts each customer once by the table's key columns, in a part of the query of its own, and lines the two up by region. When a question would need two tables that each have many rows per customer joined to each other, such as invoices and payments, sanda declines and says why rather than return a number that is too large:

sanda won’t join mapping.billing__payments to mapping.billing__customers here: one mapping.billing__customers row matches many mapping.billing__payments rows, so every total on the one-row side would be counted once per match and come back too large. Build this from the mapping.billing__payments side instead, or ask for the two tables separately.

A refusal is an answer about the shape of the question. See relationships.

More examples

Edit or delete

Press a metric in the panel to open it. edit opens the form on its current values, and save changes replaces the definition: every report and chat answer picks it up on its next run. Reported per, Shape and Shown as, which the panel's form does not show, keep their values, and so do the metrics repointed by a rename. The bin deletes the metric.

cherry can define metrics for you too: "define net revenue as gross revenue less discounts, for paid invoices". In Autonomous mode a metric cherry defines is live at once, and in Ask first mode it waits for you to confirm. See actions and approvals.