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, a second Architect checks and approves it, and when the assistant uses it, the figure is calculated exactly as written.

Note

Metric definitions are managed and used through an AI assistant connected over MCP, or through the REST API. There is no screen for them in PlaidCloud yet, and PlaidCloud’s built-in AI assistant and Microsoft 365 Copilot don’t use 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
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, and again when it is approved. 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, approve 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 or approve a metric, so keep metrics in a project with its own access list.
• Anyone who can read the project can see and run the approved definitions. Drafts are visible to Architects only.
• An approval needs two people, so a project needs at least two Architects. Whoever saved a version cannot approve it. If you edit someone else’s draft, the version is now yours: they can approve it and you can’t.
• A locked project refuses every change. Defining, editing, approving 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 as a draft, and tells you if a table or column it names doesn’t exist. A draft can be edited as often as you like. Every edit is a new version of the definition, written by whoever made it. The latest saved version, approved or not, is the metric’s working 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.

Approve a Metric

A draft becomes usable when a second Architect approves it. To find the drafts waiting, ask the assistant to list the project’s metrics: each shows its state, draft, approved or retired.

1. Read the definition as it is stored now. Ask the assistant to show you the metric, or fetch it with GET /rest/v1/analyze/metric/definition. What comes back includes a content_digest: a fingerprint of the name, synonyms, description and definition you are looking at. Through the assistant, you never need to handle it yourself.

2. Check it. Is the table right, the measure right, the filters complete, the breakdown columns what people will ask for?

3. Approve that exact content. Ask the assistant to approve the metric, or call POST /rest/v1/analyze/metric/approve, passing the content_digest you read as digest. If anyone has changed the definition since you read it, the approval is refused and you are asked to read it again, so you can approve only the exact content you read.

Approval runs two checks of its own before it goes through:

• Every table and column still exists in the project. The schema may have moved since the draft was saved.
• 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 approval 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.

The lookup check runs once, at approval

The one-row-per-key check looks at the lookup table’s data on the day the metric is approved. If that table later gains a second row for a key, the approved metric double-counts the source rows that key matches, and nothing warns you. An approved version cannot be approved again, so to run the check again an Architect saves the metric as a new version (re-saving its description is enough) and a different Architect approves that version. Better, keep the lookup’s key unique in the workflow that writes it.

Each approval is recorded with who approved it, when, and which version. Editing an approved metric creates a new draft version; for readers, the approved version keeps answering questions until the new one is approved in turn.

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, approved, 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 an approved derived metric means one of its inputs is no longer usable: retired, or missing.

Approval checks the inputs. A derived metric can be approved only when each input has an approved version, since that is the version readers get. Readers always get the approved version of each input and of the derived metric itself; Architects get the working versions, drafts included. An input is not pinned to a version: when a later version of an input is approved, the derived metric’s figures change with it, and the approval’s result names the derived metrics it affects (used_by).

An input stays in service while an approved derived metric uses it. It can’t be retired, and a new version of it can’t be approved as a derived metric; the refusal names the metric that uses it. Editing that metric isn’t enough, since its approved version still uses the input: retire it, or approve a version of it that doesn’t.

Ask About a Metric

Anyone who can read the project can ask 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, takes the breakdown and the filters from your question, and runs the approved 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 approved: the refusal names the metric and its version, and an Architect must fix the definition.

The result the assistant works from names the metric, its version and its state, and says whether row access was applied. If an answer doesn’t say which metric it used, ask. 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.

Architects asking about a metric get its working version, draft included, so they can preview a change before it is approved; everyone else always gets the approved version. 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 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 an approved derived metric is built from can’t be retired until that metric is retired, or a version of it that no longer uses this input is approved. 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	metric_find	GET /rest/v1/analyze/metric/definitions?project_id=
Read one metric		GET /rest/v1/analyze/metric/definition?project_id=&metric_id=
Define or edit a draft	metric_upsert	POST / PUT /rest/v1/analyze/metric/definition
Approve	metric_review with action: approve and the content_digest as digest	POST /rest/v1/analyze/metric/approve with digest
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, scenario}, with 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