Metric Definitions

A metric definition is a business number whose calculation has been agreed and written down: Net Revenue, Headcount (FTE), Units Shipped. It says which table the number comes from, how the rows are added up, which rows count, and which columns it can be broken down by.

Without one, an AI assistant asked “what was net revenue in EMEA last quarter?” decides for itself which table, column and filters to use, and two people asking the same question can get two different numbers. A metric definition takes that decision away from the assistant. A project Architect writes the definition once, and from the moment it is saved the assistant calculates the figure exactly as written.

Note

Metric definitions are managed through an AI assistant connected over MCP, or through the REST API; there is no screen for them in PlaidCloud yet. Metrics are available to PlaidCloud’s built-in AI assistant and to any assistant connected over MCP. The built-in assistant can find and calculate metrics, but cannot create, edit or retire them.

Architects: start at Define a Metric. Analysts: start at Ask About a Metric.

A metric definition is a recipe, not a stored result. It is recalculated every time it is asked for, under the row access of the person asking: someone who can see only EMEA gets EMEA figures. Two people can get different totals from the same definition because they see different rows; the calculation is the same.

What a Definition Holds

Part	What it says	Example
Name	What people call the number. Up to 200 characters.	Net Revenue
Synonyms	Other words people use for it, so the assistant recognizes the question. Up to 25.	revenue, sales
Description (memo in the API)	What the number means and when to use it, for the assistant and for people. Up to 1,000 characters.	Revenue after returns and discounts. Use for all revenue questions.
Source table	The table the rows come from.	sales_fact
Joins	Lookup tables joined to the source on matching columns. Each lookup table must have only one row for each key (one row per region code, say), or the rows it matches are counted twice.	region_lookup, matching region_code in the source to code in the lookup
Measure	How the rows are added up: sum, count, count_distinct, min, max or avg of a column, or weighted_avg of a column weighted by a second column (weight): the sum of value times weight over the rows where both are set, divided by the sum of those weights; empty if the weights add to zero.	sum of net_amount; weighted_avg of unit_price by units
Filters	Which rows always count. Each filter is a column, an operator (eq, ne, in, not_in, lt, le, gt, ge, between, is_null, not_null) and its values.	status eq Posted
Breakdown columns (grain in the API)	The columns a person may group and filter by when they ask. Filters are fixed; breakdown columns are the asker’s choice.	region_lookup.region, product_line, fiscal_year
Scenario (optional)	The column that separates actual rows from budget rows, and the value of each. The metric is its actual rows; the budget rows are read only for a variance.	scenario, actual Actual, budget Budget
Dimensions (optional, dimensions in the API)	A breakdown column bound to a dimension, so the words in a question are matched against that dimension’s members and aliases and turned into the column’s values: see Ask About a Metric. The dimension must exist in the project.	region_lookup.region bound to the Region dimension
Time columns (optional, time in the API)	A date column, or a fiscal year column and a period column, so a question can ask for one fiscal period and compare it over time.	fiscal_year and fiscal_period

A column from a lookup table is written as <table>.<column>; the assistant writes these for you. Every table and column in a definition must exist in the project; a definition that names one that doesn’t is refused when it is saved. A metric with a scenario can’t filter on the scenario column, or offer it as a breakdown column: the scenario decides which rows are read, so a filter such as scenario eq Actual belongs in the scenario, not in the filters.

A metric can also be built from other metrics, as a ratio, a sum, a difference or a multiple: see Derive a Metric From Others.

Nothing stops two metrics in a project from sharing a name or a synonym. Keep them distinct, or the assistant has to guess which one you mean, or ask.

Who Can Do What

• Project Architects write, edit and retire definitions. A public project gives every member the Architect role in name only, and that doesn’t count: in a public project nobody can define a metric, so keep metrics in a project with its own access list.
• Anyone who can read the project can see and run the definitions.
• A locked project refuses every change. Defining, editing and retiring all wait until the project is unlocked.

Define a Metric

If you are a project Architect, ask your connected AI assistant in plain language:

“In the Sales project, define a metric named Net Revenue, with synonyms revenue and sales: the sum of net_amount in sales_fact, joined to region_lookup on region_code, that can be broken down by the lookup’s region, by product_line and by fiscal_year; its actual rows have scenario Actual and its budget rows scenario Budget.”

The assistant saves it, and tells you if a table or column it names doesn’t exist. The metric is live as soon as it is saved. Every edit is a new version of the definition, written by whoever made it, and readers get the new version immediately. A save that changes nothing is refused. The latest saved version is the metric’s current version.

For developers, the same definition through the REST API is:

POST /rest/v1/analyze/metric/definition
{
  "project_id": "<project>",
  "name": "Net Revenue",
  "synonyms": ["revenue", "sales"],
  "memo": "Revenue after returns and discounts. Use for all revenue questions.",
  "definition": {
    "source": "sales_fact",
    "joins": [{"table_id": "region_lookup", "on": [["region_code", "code"]]}],
    "measure": {"agg": "sum", "column": "net_amount"},
    "filters": [],
    "grain": ["region_lookup.region", "product_line", "fiscal_year"],
    "scenario": {"column": "scenario", "actual": "Actual", "budget": "Budget"},
    "time": {"year_column": "fiscal_year", "period_column": "fiscal_period"}
  }
}

Each pair in on is [source column, lookup column]. time is either {"year_column", "period_column"} with an optional periods_per_year, or {"column"} for a date column with an optional fiscal_year_start and fiscal_year_label; see Compare Over Time.

What Is Checked When You Save

Every save runs these checks, and a definition that fails one is refused with a message that names the problem:

• Every table and column exists in the project.
• Every lookup table has one row per key, in the data as it is at that moment. A lookup table with two rows for the same key would double the source rows it matches, so the save is refused and names the table. A lookup row with no key value is ignored by this check, since it never matches a source row.
• A derived metric’s inputs exist and are neither retired nor derived themselves.

The lookup check runs at each save

The one-row-per-key check looks at the lookup table’s data on the day you save. If that table later gains a second row for a key, the metric double-counts the source rows that key matches, and nothing warns you. To run the check again, save the metric again (a change to its description is enough). Better, keep the lookup’s key unique in the workflow that writes it.

Each version records who saved it and when. Editing a metric saves a new version that readers get immediately.

Derive a Metric From Others

A derived metric is arithmetic over metrics that already exist: Net Revenue per FTE is Net Revenue divided by Headcount (FTE). It is defined, asked about and retired like any other metric. What differs is its definition, which names the metrics it is built from instead of a table.

Operation	What it calculates	Inputs
ratio	the first input divided by the second	two metrics
add	the first input plus the second	two metrics
subtract	the first input minus the second	two metrics
scale	the input multiplied by a fixed factor	one metric and a number

The inputs are base metrics of the same project, the kind defined from a table above. A derived metric can’t be built from another derived metric, and can’t use the same input twice.

Ask the assistant:

“In the Sales project, define a metric named Net Revenue per FTE as the ratio of Net Revenue to Headcount (FTE).”

Through the REST API, the definition names the inputs by their ids, which metric_find or the definitions list gives you. The order matters: the first id is the numerator of a ratio, and the metric subtracted from in a subtract.

{
  "kind": "derived",
  "op": "ratio",
  "inputs": ["<Net Revenue metric_id>", "<Headcount (FTE) metric_id>"]
}

scale takes one input and a factor: {"kind": "derived", "op": "scale", "inputs": ["<Monthly Run Rate metric_id>"], "factor": 12} annualizes a monthly figure.

How it is calculated. Each input is calculated on its own, at the breakdown you asked for and under your row access, and the results are then combined for each breakdown value. Totaling each input before combining them is what keeps a ratio of two tables honest: joining the rows first would count a row once for every row it met on the other side. Where one input has no rows for a breakdown value, the derived value for it is empty, and so is a ratio whose denominator is zero; neither is reported as zero.

Breakdown columns are computed, not declared. A derived metric can be broken down only by the columns that every input allows, and that list is shown beside its definition. If you want to ask for the derived metric by region, give each input region_lookup.region, spelled the same way, as a breakdown column. Inputs that share no breakdown column give a derived metric that answers only as a single total. A question that groups or filters by anything else is refused, as for any metric. An empty list beside a derived metric means one of its inputs is no longer usable: retired, or missing.

Saving checks the inputs. A derived metric can be saved only when each input exists, is not retired and is not itself derived. Readers and Architects alike always get the current version of each input and of the derived metric. An input is not pinned to a version: when a later version of an input is saved, the derived metric’s figures change with it.

An input stays in service while a derived metric uses it. It can’t be retired, and it can’t be changed into a derived metric; the refusal names the metric that uses it. To retire an input, retire the derived metric first, or save a version of it that doesn’t use the input.

Ask About a Metric

Anyone who can read the project can ask PlaidCloud’s built-in AI assistant, or an assistant connected over MCP:

“What was net revenue by region in FY26?”

The assistant recognizes the metric by its name or one of its synonyms (the lookup ignores case and matches part of a name, so revenue finds Net Revenue), takes the breakdown and the filters from your question, and runs the definition. A question can only group or filter by one of the metric’s breakdown columns; one that asks for anything else is refused rather than answered with a guess. So is a metric whose table has lost a column since it was saved: the refusal names the metric and its version, and an Architect must fix the definition.

Your words become the column’s values through a dimension. When an Architect has bound a breakdown column to a dimension, the assistant looks the words in your question up against that dimension’s member names and aliases, ignoring case. A word that names one member becomes that member’s values: a leaf is its own value, and a parent such as EMEA expands to every leaf beneath it, so “net revenue in EMEA” filters on all of EMEA’s regions. A word that names no member, or more than one (Seaboard may be an alias on two members) and none of them in exactly the spelling you used, comes back as a list of candidates for the assistant to ask you about; it never picks one for you. Looking words up in a dimension needs permission to read dimensions, as reading a dimension anywhere else in PlaidCloud does.

The result the assistant works from names the metric and its version, and says whether row access was applied. If an answer doesn’t say which metric it used, ask. In PlaidCloud’s built-in AI assistant, the figures appear in their own card with a ✓; see Figures From a Metric. For how PlaidCloud grades and caveats AI answers more generally, see Answers You Can Trust.

Row access is applied to every table the metric reads, the source and each lookup alike. Where a lookup row is hidden from you but the source row it labels is not, the source row still counts. Say you can’t see the lookup row for region APAC but can see its sales: those sales are in your total, appear under a blank region in a breakdown by region, and are usually left out if you filter on region, or if the metric itself filters on a lookup column.

Row access shapes the lookup too: where the breakdown column is governed, you are offered only the values your access allows. A member with nothing beneath it that you may see is never offered, as a value or as a candidate, and a parent expands only to the leaves you may see. An Architect, who reads every row, sees every member.

Architects read every row, as they do everywhere else in the project.

Compare, Rank and Share

A question about a metric can also ask for one comparison, one ranking, and a share of the total. Each is a fixed calculation over the metric’s own figures, under the asker’s row access; none of them is worked out by the assistant.

Ask for	What comes back	In the API
Variance against budget	For each breakdown value: the actual figure, the budget figure, the difference (value), and the difference as a percentage of the budget’s size (pct), which keeps the sign of the difference when the budget is negative.	compare: "variance"
Share of the total	Each row’s figure divided by the total over every row of the breakdown (share), or over the rows that share the within columns you name.	share: {"within": ["region"]}; {} for the grand total
Top or bottom N	The N highest figures, or the N lowest with order: "asc", with their rank; per group when you name within columns. Rows that tie share a rank and are all kept, so there can be more than N. For a cost metric furthest over budget is the top N of the variance; for a revenue metric it is the bottom N.	top: {"n": 10, "within": [], "order": "desc"}

“Which five cost centers are furthest over budget in the Finance project?”

“What share of total spend is each region, and which region is the largest?”

A variance needs a metric with a scenario; a metric without one, or a derived metric, refuses it. Each side is totaled on its own before the two are compared: a breakdown value with no budget row, or no actual row, shows an empty difference rather than a zero (the exception is a count metric asked for as a single total, where a side with no rows counts zero), and so does a percentage over a zero budget.

The ranking and the share are over the metric’s figure, or over the difference when a variance or a comparison over time is asked for, so “which cost centers account for most of the overspend” is a variance with a share and a ranking. pct is a percentage, so 12 means 12%, where share is a fraction. The share is worked out over every row of the breakdown before a top-N keeps some of them, so the shares of the rows you get back need not add up to one. An empty figure ranks last whichever order you ask for, and has an empty share; so does a share over a zero total.

A share is of what you can see. Someone whose row access limits them to EMEA gets EMEA’s cost centers as shares of EMEA’s total, and the result says row access was applied. The within columns of a share or a ranking must be among the breakdown columns you asked for, and a question that groups by a column named like one of the result’s own columns (value, and actual, budget, current, prior, pct, share, rank, year, period or cumulative when the operator that produces it is asked for) is refused. A question with two comparisons, or two rankings, is refused.

Compare Over Time

A metric whose definition has time columns (time in the API) can be asked for one fiscal period, compared with the period before it, and totaled up period by period. The time columns say where each row sits in the fiscal calendar, in one of two ways:

• A date column, with fiscal_year_start, the calendar month the fiscal year starts in (1 for January, the default). Its periods are the fiscal year’s months, numbered from the start month: with a year starting in October, period 1 is October and period 7 is April. A quarter is three of them. A fiscal year that doesn’t start in January is named for the calendar year it ends in by default (fiscal_year_label is ending); set fiscal_year_label to starting to name it for the year it starts in. With an October start, the months from October 2025 to September 2026 are FY2026 by default, and FY2025 with starting. Rows are read by date range, which keeps the question fast on a large table.
• A fiscal year column and a period column, both integers holding the fiscal year (2026, not 26) and the period within it, numbered from 1, with periods_per_year: 12 by default, 13 for a 13-period calendar, 52 for weeks, 4 for quarters. Quarters exist only when the periods divide into four. A calendar with 53-week years can set 53, at the cost of its quarters. A save is refused if either column holds a value that isn’t a whole number, such as 2024-01 or FY26, and the refusal names the column and the value. Digits stored as text, such as 07, are accepted, and so is a period outside the year, such as a ledger’s period 0 or 13: a question about a period leaves those rows out.

A question then names the period it is about: a fiscal year, or one quarter or one period of it. To date stretches that back to the start of the year, so April year to date is the year’s first months up to and including April.

“What were sales in FY2026 Q1 by region, and how does that compare with the quarter before?”

“Sales year to date through April against the same months last year.”

“Show me FY2026 sales by month as a running total.”

Ask for	What comes back	In the API
One period	The metric over that period alone, with the usual breakdowns, shares and rankings; a variance against budget reads both scenarios over it.	period: {"year": 2026}; {"year": 2026, "quarter": 1}; {"year": 2026, "period": 7}
Year over year	For each breakdown value: the figure for the period (current), the same period one year earlier (prior), the difference (value) and pct. The answer names the prior period.	compare: "yoy" with a period
Quarter over quarter	The same, against the quarter before. The first quarter is compared with the fourth of the year before.	compare: "qoq"; the period must name a quarter
Period over period	The same, against the period before. The first period is compared with the last of the year before. On a 12-period calendar this is month over month, which is where the name comes from.	compare: "mom"; the period must name a period
Year to date	The year from its start to the end of the named quarter or period, on its own or, with yoy, against the same months of the year before. Refused with qoq or mom.	"to_date": true in the period
Running total	One row for each fiscal year and period in the period asked for, with the period’s own figure (value) and the total so far (cumulative). Without a period, it runs from the first row to the last across the years. It takes no comparison, share or ranking.	cumulative: true

pct is the difference as a percentage of the prior figure’s size, keeping the sign of the difference when the prior figure is negative, as for a variance. Each of the two periods is totaled on its own, and the two totals are matched up by breakdown value; nothing is read off a neighboring row. So a month with no rows last year compares as empty, not as the nearest month that had some, and a breakdown value with rows in only one of the two periods shows the other, the difference and pct as empty rather than zero.

A running total is for sum and count metrics. An average, a distinct count, a minimum or a maximum doesn’t add up over time and is refused. A period with no rows has no row in a running total, and the total carries on past it.

The time columns can’t be filtered or grouped by in a question that names a period, compares over time or asks for a running total: those decide which rows are read and how they are placed. A question that does none of these can still filter and group by them as by any breakdown column. Also refused: a period past the end of the year (period 13 on a 12-period calendar), a quarter on a calendar without quarters, a time comparison or a running total on a derived metric, and a question that doesn’t name the kind of period its comparison needs. A derived metric can be asked for one period when every one of its inputs has time columns on the same fiscal calendar, and then none of those can be filtered or grouped by either. A time comparison is the question’s one comparison, so it can’t be combined with a variance against budget, though a share and a ranking still apply to the difference.

Retire a Metric

Retiring a metric takes it out of service for good: people who read the project no longer see it, Architects see it marked retired with its history, and it can no longer be edited or run. An Architect retires one by asking the assistant, or with POST /rest/v1/analyze/metric/retire. A metric that a derived metric is built from can’t be retired until that derived metric is retired, or a version of it that no longer uses this input is saved. To replace a retired metric, define a new one.

Through MCP and the REST API

Action	MCP tool	REST
List a project’s metrics, each with its full definition; q keeps those whose name or a synonym contains it	metric_find	GET /rest/v1/analyze/metric/definitions?project_id=&q=
Resolve the words in a question to a breakdown column’s values	metric_values with column and words	GET /rest/v1/analyze/metric/values?project_id=&metric_id=&column=&words=
Read one metric		GET /rest/v1/analyze/metric/definition?project_id=&metric_id=
Define or edit	metric_upsert	POST / PUT /rest/v1/analyze/metric/definition
Retire	metric_review with action: retire	POST /rest/v1/analyze/metric/retire
Run	metric_run with group_by, filters, and the optional period, compare, share, top and cumulative	POST /rest/v1/analyze/metric/run with the same fields
Earlier versions (Architects only)		GET /rest/v1/analyze/metric/flashback-versions

metric_upsert takes a spec of {name, memo, synonyms, definition} and accepts dry_run to check a definition without saving it. A base metric’s definition is {source, joins, measure, filters, grain, dimensions, scenario, time}, with dimensions as {<breakdown column>: <dimension id>}, measure as {agg, column} or {agg: "weighted_avg", column, weight}, scenario as {column, actual, budget} and time as {column, fiscal_year_start, fiscal_year_label} or {year_column, period_column, periods_per_year}. A derived metric’s definition is {kind, op, inputs, factor}; its computed breakdown columns come back as grain beside the definition.

Related

• Analysis Paths — name the tables people ask about, and give assistants standing filters and terminology for each
• Answers You Can Trust — how PlaidCloud grades and caveats every AI answer
• Control Data Access — the row access a metric runs under