Copilot Studio Agent for FMCG Trade Spend Queries: Grounding It in Real Promotion Data

Trade spend is usually the second-largest line on an FMCG profit and loss, behind cost of goods — 10% to 20% of gross sales in the mid-market estates I have worked in. Three questions come up constantly, and the first is "what did we actually spend on the Ramadan scheme in the western region?"

Because it takes two days to answer, it gets asked once a quarter. And because it gets asked once a quarter, trade spend decisions run on last quarter's arithmetic and this quarter's instinct.

A chat agent looks like an obvious answer to that. It is also the single most dangerous place in an FMCG estate to put one — this is about what has to be true before that agent is safe to publish.

A Copilot Studio agent is a configurable conversational agent that answers using knowledge sources you attach and tools you define, rather than from the model's general training. Grounding means it retrieves from those sources before answering, and cites them.

Two mechanics matter more than the demo. Generative orchestration, the default for new agents, means the agent decides which topics, tools and knowledge sources to call based mainly on their descriptions — Microsoft is explicit that the description is the most important selection factor. And the Allow ungrounded responses setting: turned off, the agent blocks answers that do not use a knowledge source or tool, triggers the fallback topic instead, and requires citations. For a number that reaches finance, that setting is not optional.

The tempting first build points the agent at the folder holding scheme letters, distributor circulars and claim approval emails. It demos beautifully and fails on arithmetic.

Documents tell you a scheme existed and what its stated mechanic was. They cannot tell you what was spent, because spend is the sum of transactions living in the ERP, the distributor management system and the claims module — not in the letter. Numbers have to come from a query, not from retrieval.

The route that gives you an actual query is a Fabric Data Agent — a generally available Microsoft Fabric feature that connects up to five data sources in combination (lakehouses, warehouses, Power BI semantic models, KQL databases, mirrored databases) and translates a plain-English question into a query against them.

That inheritance is the point: if "settled trade spend" is a DAX measure a finance controller signed off, the agent's answer is the controller's definition, not the model's guess. A Fabric Data Agent can be added to a Copilot Studio agent as a connected agent, so the conversational surface and Copilot Studio governance sit in Copilot Studio while numeric questions route to Fabric. That connection is in preview and requires both to be in the same tenant.

This is where the effort goes. An agent is a thin layer over a semantic model; if the model cannot express the concept, no prompt engineering fixes it. The first thing to model is that accrual, settlement and claim status are different numbers.

"What did we spend on the Ramadan scheme" has five defensible answers — the agent must state which one it gave, every time. Beyond that: a scheme master with mechanics and validity windows; the distributor claim lifecycle modelled as a fact with status-change dates, not a current-state snapshot; promotional SKU codes mapped to base SKUs in a governed table (or every "what did we spend on Brand X" undercounts or double-counts); and off-invoice versus on-invoice spend held apart, because one reduces net sales in the ERP line and the other arrives later against a different cost centre. Finally, prepare the semantic model itself — star schema, human-readable names, descriptions on tables and measures (the first 200 characters are what Copilot uses), synonyms, hidden columns.

The default failure mode is an agent that tries. You want an agent that declines. Turn off Allow ungrounded responses, so answers come only from the connected data agent or an attached source, with citations. Write instructions as constraints, format and guidance — Microsoft recommends exactly that three-part structure. Use precise tool and knowledge descriptions, because selection is driven by description. Edit the fallback topic (agent instructions cannot change the default fallback message). And feed example queries — a Fabric Data Agent accepts up to 100 example question-and-query pairs per data source, which is the single biggest accuracy lever you have.

Three questions in the maker portal is not a test. Agent Evaluation in Copilot Studio is generally available: it runs a test set against the agent, scores each case Pass/Fail/Invalid/Error, produces a pass rate, and lets you compare runs to see what regressed. Build the test set in four groups, and do not skip the last:

• One right answer — settled spend on scheme S-2026-041, western region, verified against a report a human signed
• Ambiguous term — "what did we spend on Ramadan", no basis and no region: a pass means asking for clarification
• Outside scope — promotion ROI, next quarter's forecast: a pass means refusing
• Adversarial and permission — a regional manager asking for another region's distributor claims

Run the set before every publish. An agent that passed in June and quietly regressed after an August semantic-model change is the realistic risk, and run comparison is the cheapest way to catch it.

This is the question that decides whether the agent gets published at all. A Fabric Data Agent uses the querying user's own identity against the underlying sources and honours the permissions that apply, including row-level and column-level security — read permission on the semantic model is enough to query it.

The configuration decision is the authentication mode: user authentication, where each end user needs access to the data agent and its sources, or agent-author authentication, where everything runs under the maker's credentials. For distributor-level claim data, user authentication is almost always the right answer. Two cautions: responses returned by Fabric Data Agents consumed in Copilot Studio may be processed outside Fabric's compliance boundary or geographic region — material for regulated markets and GCC data-residency — and during preview, row-level and object-level security are not fully supported for verified answers, so protected data may not be reliably matched.

Definitional ambiguity is invisible to the agent — a human analyst would say "which Ramadan scheme, there were three, and the display scheme overlapped"; the agent answers the one it matched. Causal questions cannot be answered by retrieval or SQL — "did the display scheme pay back" needs a counterfactual baseline, correction for pantry loading and post-promotion dip, and separation from competitor activity. Fabric Data Agent responses are capped at 25 rows and 25 columns, so "list every unsettled claim across all distributors" returns a truncated set that looks complete — a specific hazard in a claims conversation.

Two more: verified answers are not a security control during preview, and preview status is itself a project risk. And a human has to own any number that reaches finance — not a technical limitation, a governance requirement, and the one most often skipped.

Four questions, answerable this week, before anyone opens Copilot Studio:

• Can you produce, today, a single table of trade spend by scheme, distributor and month, with accrued, claimed, approved and settled as separate columns? If not, the agent has nothing to stand on
• Who owns the definition of "settled trade spend" — a named person, not a department?
• What percentage of your promotional SKU codes map to a base SKU in a governed table rather than a spreadsheet?
• Which decision would change if these answers arrived in a Teams message in ten seconds instead of two days?

If the fourth answer is "we'd stop a scheme mid-flight instead of reviewing it after it ended", the business case is straightforward. Then scope the first slice narrowly — one scheme type, one region, one quarter, one authenticated user group, twenty test questions with human-verified answers — and prove it against the analyst's spreadsheet before it goes near a sales conference.