AI agents need a marketing data model and a semantic layer

Section 01

A core metric takes five steps from raw data to leaders

People load, shape, and check a core metric before it reaches senior leaders, and an AI tool that takes over part of that work has to be as careful.

Pipelines, tested tables, and an analyst stand behind each number

Take return on ad spend (ROAS) by channel last month at an example brand, made up for illustration, that sells online and buys ads on several platforms. Like every core metric that reaches leaders, it goes through five steps.

• Step 1: The source systems record. Each ad platform records spend and the sales it credits to its own ads. The store records each order and refund. Site analytics records each visit and its source.

• Step 2: Pipelines copy the data into the warehouse. A pipeline is any set of rules that moves data from one place to another; data teams call this step the ingest layer. Each pipeline pulls a system's data through its API, the interface the system offers for getting data out, into raw tables in the data warehouse, the database that holds it all for analysis.

• Step 3: The transformation layer builds the data model. The data team reshapes raw data into governed tables, which it tests, documents, and owns. Each has a documented layout, called a schema. Together they are the data model. The work:

  • filters out rows that do not belong, such as test orders
  • removes duplicates and unused columns
  • joins tables on a shared field such as a customer ID
  • adds totals at more than one level, such as per order or per campaign per day
  • credits each order to a channel by the brand's attribution rule
  • records lineage, each number's path from its source through every table to the dashboard

• Step 4: The dashboard divides revenue by spend. The data model carries spend and revenue by channel. ROAS, a calculation on top, sits in a business intelligence (BI) dashboard, a spreadsheet, and an analyst's saved query. A semantic layer would hold it once: a set of metric definitions, written in code, that sits between the data model and every tool that shows a number.

• Step 5: The analyst runs quality assurance (QA). If ROAS doubles overnight, the analyst looks for a duplicated order, a missing refund, or a campaign credited to the wrong channel before the numbers go out.

Data teams have called this chain of cloud tools, which pass data to one another through SQL (the standard language for querying a database), the modern data stack. The joins live in code and the formula in the dashboard, but only the analyst knows which revenue the CMO means.

An AI agent on raw tables works without tests or definitions

An AI agent is a language model, the kind of AI behind ChatGPT and Claude, given tools such as one that runs a database query. Connected straight to the warehouse, it reads the table and column names, then writes and runs its own query.

The governed path has more steps, but the data team sets them up once.

In a semantic layer, each metric's definition has a named owner who agreed it. The data team tests it against the agreed numbers, the figures the business has signed off on. My view is that this check by people is the reason to build a semantic layer at all.

Section 02

A marketing data model defines each row and how tables join

A data model rests on four parts, and the ROAS example shows how one wrong join repeats spend and returns a wrong number with no error.

The transformation layer builds the data model from these parts:

• Tables. Each holds one kind of record, such as ad spend or orders.
• Keys. A key is the column that identifies each row, such as an order ID.
• Grain. The grain is the table's level of detail, what exactly one row stands for: one order, or one campaign on one day.
• Joins. A join matches two tables on a key they share, such as orders and order lines (one row per product on an order) on the order ID.

Grain decides whether a count of rows is a count of orders

As of October 22, 2026, the commerce platform Shopify documents one event per row in its sales data, so one order can produce several rows when refunds or adjustments follow¹. An agent that counts those rows counts a refund as another order.

Joining orders to their order lines repeats each order's total

In an example in Google's documentation for Looker, its BI tool, joining orders to their order lines on the order ID makes the sum of order totals 223.44 instead of the correct 124.84, because each order's total repeats once per line².

For ROAS, spend and orders sit at different grains. Add each up by channel and date first and line up only the totals.

The Kimball Group calls ratios non-additive, meaning they cannot be added up across rows, and says a good approach, where possible, is to store their parts. Add up the parts first and divide last. ROAS by channel is total revenue over total spend, never an average of each campaign's ROAS.

Data tests catch rows that break the data model's rules

dbt, the transformation tool dbt Labs sells, defines a data test as a query that looks for rows breaking a rule. The test passes when it finds none.

Section 03

An AI agent needs marketing, customer, product, order, and calendar tables

An agent that spots patterns and predicts needs five kinds of tables, kept at a finer grain than your reporting has ever used.

A dashboard can run on totals, such as spend and revenue by channel by week. An agent needs the detail under the totals: each order, each ad, each customer. I'd build the data model at that grain from the start, then expand it and the semantic layer as people ask for more.

Take one question: which channel brought in the customers who later bought from a second product category. Answering it takes the channel credited with each first order, every later order, and each product's category.

Orders carry the channel credit that links ads to customers

• Marketing. Spend, clicks, and platform conversions by campaign, ad set, ad, and day, paid and organic, plus each campaign's channel.
• Customers. One row per customer, built from orders or the CRM (customer relationship management system), with a key that never changes and the first order date.
• Products. Each product with its category and product line.
• Orders and order lines. One row per order, with its customer, time, totals, and the channel the attribution rule credits. Under it, one row per order line, with the product.

Context tables explain why the numbers move

Numbers on their own are just numbers. Context data sits beside the performance data, so when a number jumps or an agent spots an anomaly, it can see what was happening at the time.

A calendar table maps each date to its fiscal period and holidays, so in the example brand, last month means the fiscal month. A promotion calendar records each offer and its dates, so a jump in ROAS can be read against the discount behind it. Seasons and promotions are the start; a brand adds context from other sources as its questions need it.

Finer grain lets an agent find trends, segments, and forecasts

With that grain in the data model, an agent can:

• find trends by channel, ad set, ad, or audience
• segment customers by what they buy, how often, and where they live
• forecast, by calling a forecasting tool on that history
• flag anomalies and act when a number crosses a trigger

The first defined metrics give finance an early result

Finance's best case against this work: an agent sold for data questions can be bought today, and a data project with no visible output is the first budget line cut. The bought agent needs the same definitions: Databricks, which makes an AI agent called Genie, tells teams to define metrics and filters for Genie in SQL, with written instructions only as a last resort³. The first defined metrics are also a result finance can see early.

Section 04

Revenue is a different number in every system that reports it

When several systems report revenue, an agent left to choose has no way to know which one you mean.

As of October 22, 2026, the example brand's store, ad platforms, and site analytics each calculate revenue differently, and some see only part of the sales⁴. Ask an agent for revenue with no definitions in place and it picks one, and you won't know which.

Each version still has a job. Attributed revenue from the ad platforms or site analytics compares channels and campaigns. The store's or the finance system's number is what the business actually booked.

Five settings decide which revenue a system shows

• Gross or net. Whether discounts, refunds, shipping, and tax are in the number.
• Attribution window. How long after a click or a view a sale still counts for the ad.
• Attribution model. The rule for which ad gets the credit when a person saw several.
• Counting date. The day of the click, or the day of the purchase.
• Tracking. A tool counts only the sales its tracking sends it.

Google's Ads Help expects discrepancies, often up to 20%, from different attribution models, with Google Analytics against Google Ads as its example⁶.

The semantic layer names each version of revenue

The semantic layer gives each version a name and a plain description of its source and its five settings. The agent picks a version by reading those descriptions.

For a brand that sells through its own store, my default is the store's net sales. A question that names no version of revenue gets the store's net sales, and the answer names the version it used. The data team writes that default into the store net sales description and into the agent's standing instructions, the written rules it reads before every question.

A question about one platform, such as Meta's ROAS, is different. The agent first asks whether the person means Meta's own revenue figure or the store's sales credited to Meta.

I recommend the same for ROAS: one named metric per version, as the first section's figure shows.

Any ROAS built on attribution can count sales the ads did not cause. An incrementality test holds some people back from the ads to count only the sales the ads caused. If your team runs those tests, add the ROAS they measure as its own named version. When the team moves budget between channels, it relies on those tests or a media mix model, a statistical analysis of each channel's effect on sales.

Section 05

An AI agent writing SQL guesses which revenue you meant

A language model predicts its way to a query, and on a real warehouse it predicts wrong too often for numbers people act on.

A language model is a prediction machine. Asked for ROAS by channel, it reads the table and column names it can see, guesses how the tables relate, and writes the query its best guess supports. Every guess returns a plausible number. That is the problem.

With a small set of tables, clear column names, documentation, and fairly clean data, those guesses get good. Spider 2.0 is a benchmark of enterprise SQL tasks⁷. As of October 22, 2026, the top-ranked system solved 96.70% of tasks on its Snowflake version, which supplies well-prepared table and column descriptions plus documentation⁸.

Real warehouses have sprawl the benchmarks leave out

Real warehouses hold staging and production views, copies, aggregated versions, and tables named orders, orders_v2, and orders_final. On EntSQL, the 2026 benchmark from researchers including a team at Alibaba, the best system averaged 6.8% accuracy with the question and tables alone and 21.4% with notes written by experts⁹.

For numbers people act on, even ninety percent accurate is not good enough. In five studies, the researcher Berkeley Dietvorst and two colleagues found that people lose confidence in an algorithm faster than in a human forecaster after seeing both make the same mistake, even when the algorithm did better¹⁰. Their tasks were forecasts, not dashboard numbers. My view is that a wrong number in a meeting costs an agent trust the same way. So my answer to the case that language models now write strong SQL: the agent still has to be told exactly where the answers are. That is the data model's and the semantic layer's job.

Section 06

A semantic layer writes each metric's query from one written definition

The ROAS example shows how a semantic layer turns a request by name into one query, and where its help stops.

Semantic layers name their tables and joins before any metric

dbt's semantic layer, for example, describes each data model table it reads and names the type of every join key. Then the layer defines metrics and dimensions. Dimensions are the ways a metric can be split, such as by channel or by month.

A named request becomes the same SQL every time

A dashboard or an agent asks for a metric by name, with dimensions, filters, and dates. The layer finds the path between the tables from the declared keys, writes the SQL, and returns the number with the definition used.

dbt Labs says the same request produces the same SQL each time¹¹. The joins and the formula are no longer the agent's job. It still picks the metric, filters, and dates, and that choice can vary between asks.

A ROAS definition is a short YAML file in dbt's format

Here is the example brand's ROAS definition, an illustration written as a YAML file, the plain-text format dbt's semantic layer uses for metric definitions. The file sits in version control, a record of every change, so each edit carries a date and a reason.

metrics:
  # the name every tool asks for
  - name: roas_store_net_sales
    label: "ROAS on store net sales"
    # what the agent reads
    description: >-
      Store net sales credited to a channel by the brand's
      attribution rule, over ad spend. Test orders excluded.
      Fiscal months, store time zone.
    # one total over another
    type: ratio
    type_params:
      numerator: store_net_sales
      denominator: ad_spend
    filter: "is_test_order = false"
    config:
      meta:
        synonyms: ["return on ad spend", "ROAS"]
        time_zone: "store time zone"
        counting_date: "order date for sales, spend date for spend"
        attribution: "last click, the brand's chosen window"
        # the team that owns it
        owner: "acquisition team lead"
        # the data team runs the layer
        maintained_by: "@analytics_engineering"

A semantic layer cannot fix bad data or undefined metrics

A semantic layer built on raw tables repeats their errors in every answer, and it serves only the metrics someone wrote down. A metric read the same way everywhere can still overstate what the ads caused.

Section 07

Marketers' AI agents should query only the semantic layer's metrics

The tools a connector offers, as of October 22, 2026, and the login an agent runs under decide whether its answers stay inside the defined metrics.

The agent lists the defined metrics, then asks for one by name

A connector is a service that offers an agent a named list of tools, such as list metrics or query a metric. The ones here use the Model Context Protocol (MCP), a specification for connecting AI agents to outside tools, and are called MCP servers. The instructions dbt ships with its connector set the order:

• They tell the agent to first ask for the metrics and dimensions it can use.
• The agent then queries the metric by name, such as total_sales grouped by month for total sales last month.
• When no defined metric fits, the instructions tell the agent not to query, and to say what is available and suggest a close question it can answer.

A SQL tool on the same connector goes around the definitions

Snowflake's connector can offer a tool that runs SQL queries the agent writes, read-only by default. Snowflake warns that putting that tool on the same connector lets the agent bypass the semantic views¹², Snowflake's name for its semantic layer definitions. The person asking can't tell which path an answer took.

My rule is stricter. Marketers' agents get the defined metrics and no raw SQL connection. The data team sets up their connector with no tool that lets the agent write and run its own SQL against the tables. The agents get no warehouse login of their own.

My advice has one exception. Some useful data is semi-structured or not governed yet, and sometimes you want an agent to reason over it. Then the answer has to say so: mark which parts came from that data, list every source the agent pulled from, and make the person asking own the result, knowing it may be wrong. Anything that touches reporting, financial planning, forecasting, or accounting stays inside the semantic layer, with no exceptions.

The agent sees only what the asking person may see

Microsoft, the maker of Power BI and Copilot, its AI tool, says Fabric IQ serves Power BI data to Copilot and custom agents. It answers plain-language questions within Power BI's row-level security, the rules that limit which rows each person sees.

The numbers still leave your systems. Microsoft notes that query results go to the AI tool the agent runs in, which might pass them to its language model provider¹³. Legal should check that tool's terms on whether the provider keeps or trains on the results.

Section 08

BI tools can hold the semantic layer if agents query them

If an AI agent outside your BI tool can ask it for a metric by name, and see only what the asking person may see, the BI tool is your semantic layer.

If your dashboards run on Looker or Power BI, some of your metric definitions already live there. The question is whether to keep them there.

Ask your data team one question: can an AI agent outside the BI tool ask it for ROAS by name, under the asking person's permissions?

• Yes: leave the definitions where they are. Moving them means rewriting every one and keeping two copies in step.
• No: move them to a layer agents can reach. dbt Labs argues that moving definitions out of the BI layer lets data teams feel confident that business units work from the same definitions "regardless of their tool of choice"¹⁴.

Wherever the layer lives, every reader goes through it. Every dashboard, spreadsheet, and AI agent asks the same layer through its API or connector. A reader that goes around it, to a raw table or a second set of definitions, is where key numbers start to disagree.

Here is how each home for the definitions answers that question.

If your metrics live only in spreadsheets today, the choice is open. Pick a home your dashboards and your agents can both reach, and keep one set of definitions in it. If you change BI tools later, plan on converting the definitions yourself.

Section 09

Knowledge graphs and ontologies build on the semantic layer's definitions

The three get confused: a semantic layer defines the numbers, an ontology defines the business's things and how they relate, and a knowledge graph holds the actual records.

A question about connections still ends in a number, so it still needs that number defined. Define the metrics first, and add an ontology when your team's questions start to follow connections.

Section 10

Start the data model and semantic layer small, then grow them

The first build covers only the metrics people already ask about, and the agent reaches nothing else.

Databricks gives the same advice for its own agent: start as small as possible, with a limited set of questions, and aim for five or fewer tables³.

The first build follows the questions people already ask

The data team works in this order. A brand without a data team gives the same steps to whoever builds its dashboards today, in-house or at an agency.

• List the questions. Collect what people email the analysts and ask in the weekly meeting. Alex Merced, who works at the semantic layer maker Dremio, calls the SQL behind a team's top 30 dashboard tiles a ready-made list of questions with reviewed answers¹⁵.

• Trace each answer to where its logic lives today: a dashboard, a spreadsheet, a saved query, or someone's memory.

• Build the tables those metrics need first, from data already in the warehouse, working toward the minimum of marketing, customer, product, and order tables. If marketing data is not in a warehouse yet, loading it comes before this step.

• Write each definition with the parts the ROAS definition shows, its owner included.

• Connect an agent to those metrics alone, and test it before marketers use it.

• Add the next domain only once the agent's answers match each owner's number on every test question.

The metrics teams confuse most come first

Prioritize the metrics where two teams quote two different numbers. Revenue is the most common. Attributed customer counts come next, such as new customers by channel and the customer acquisition cost (CAC) built on them.

Attributed counts rest on an attribution rule that can be wrong. That is fine once the definition names the rule and the window, so every tool applies the same credit.

Then define the rest of what marketing already tracks, from spend and orders to conversion rate, total customers, and platform metrics such as impressions.

Define churn and LTV the way your business measures them

Your business decides what counts as a churned, reactivated, or newly acquired customer, not a platform's default. The tools already disagree on churn and lifetime value (LTV). Name each version, and pick the one that counts:

• LTV from past orders. Email marketing company Klaviyo's historic customer lifetime value is the total value of a customer's previous orders, accounting for refunds and cancellations.
• LTV over a fixed period. In Amplitude's product analytics, N-day LTV is a group's revenue in the first N days or other time units after their first activity, divided by the number in the group.
• Churn by time since the last order. Commerce customers typically have no cancel event, so the guide for Adobe's commerce analytics product counts a customer as churned after a set time without an order.

Each metric has an owner, usually the team closest to it

My view is that the data team runs the semantic layer, and each metric's definition has an owner who knows what the number should count. Ownership usually follows the domain:

• Lifecycle or retention team: customers, LTV, and churn.
• Acquisition team: CAC, and often ROAS.
• Finance: the transactional side, such as orders, customer totals, and return rates.

In a team too small for these groups, one person can own several metrics. Each definition still names one owner.

The data team asks each group how it calculates and uses a metric, compares the answers, and records the owner in the definition. When marketing and finance disagree on revenue and one version must win, the data team brings both sides to a decision and writes it into the definition.

Section 11

Data is AI-ready when the agent matches your agreed numbers

Accuracy is the point: the data team checks the agent against agreed answers before marketers rely on it, and again on every change after.

Once people see a wrong number, they stop trusting the agent, and then there is little point to it. I'd rather start with a smaller set of data that is right every time than a larger one with errors in it.

Marketers get the agent once it passes the test questions

The data team runs the check in order:

• Write test questions. I'd start with maybe twenty of the questions people ask most, and keep the set. Include one question no defined metric covers. It passes only if the agent replies that no defined metric covers it.

• Write each agreed answer. The data team computes it from the settled definition, and the metric's owner confirms it. Where finance has its own number, such as revenue as booked, add it as a named version with finance's figure as its agreed answer.

• Run the agent on them, more than once. Databricks lets a team attach the correct SQL to each test question and compares the agent's result with it. It also suggests two to four wordings per question.

• Sort each mismatch by its cause, and fix the description, the data, or the definition it points to.

• Open the agent to marketers once its answers match the agreed numbers on the test questions.

Data tests run each time the pipelines bring in new data. When a test has failed, the agent should say the number is on hold, which it can do only if its connector shows it the test results.

Rerun the tests on every change, so nothing that worked breaks

Each addition to the data model can break a metric already defined or a dashboard that reads from it. My advice is to run the test questions and the data tests automatically on every proposed change to the data model's code, such as a pull request, so a change that breaks a number never ships.

Snowflake's guide to evaluating its agents recommends running the set on every configuration change and on a schedule. Scheduled runs catch problems from language model updates, data changes, or tool settings that drift. The guide also says to pin a specific language model for the agent rather than take the automatic setting.

A brand can test whether its data is AI-ready

The phrase AI-ready data works best as a test. A brand's data passes for an agent when:

• each first metric has one written definition and an owner
• the tables under each metric pass their data tests, with lineage back to the source system
• tables and columns carry plain descriptions
• on the test questions, the agent gives the agreed numbers

Section 12

Grow the semantic layer from the questions people keep asking

After launch, a log of what marketers ask the agent shows which questions to turn into defined metrics, and what each addition costs to keep fresh.

Flagged answers become fixes, and repeated questions become metrics

The data team keeps a question log: each question marketers ask the agent, the answer, the metric used, and any flag. The AI tool's records and the connector's record supply it. A marketer's flag goes to the metric's owner. Don't expect the agent to learn from the flag: Databricks says its agent's behavior does not change based on user feedback alone. The owner's fix is a new definition, a clearer description, or a new test question.

The data team groups the questions the agent could not answer by the metric and detail each needs. It adds the ones that keep coming back and answers a rare one by hand. In the example brand, requests for platform ROAS by ad set and audience keep arriving, so the data team adds ad set and audience as ways to split platform ROAS.

Precompute the answers people ask for often

Once a question comes up often, especially at a fine grain, store its answer ahead of time in the transformation step or the BI tool. A precomputed answer returns faster. I recommend refreshing it on a schedule to keep it fresh and easy for downstream tools to pull. Databricks gives the same advice for its metric views: precompute the combinations queried often, knowing each refresh incurs pipeline charges.