Why the same metric differs by tool
Ask three tools for last month's conversions and three numbers come back. None is wrong by its own rules; the rules differ. Google Ads counts conversions it attributed to its own clicks, within its conversion window, on the date of the click. GA4 counts key events on the date of the event and credits them through its own attribution model. The CRM counts records that a human marked as qualified, whenever that happened. Three counting units, three dates, three attribution rules, one word.
The sources of drift are finite and worth naming, because a definition has to settle each one:
| Source of drift | What differs | Example of the effect |
|---|---|---|
| Counting unit | Clicks, sessions, users, events, records, orders | A session-based rate and a user-based rate from the same data |
| Attribution rule | Which touch gets credit and across what window | Platform-attributed conversions exceed analytics-attributed ones |
| Date assignment | Click date, event date, order date, close date, report date | A conversion counted in different months by different tools |
| Time zone | Account, property, warehouse and viewer zones | Daily totals that never match at the edges |
| Filters | Internal traffic, test orders, refunds, brand terms, test campaigns | A revenue figure before and after refunds |
| Currency and fees | Billing currency, exchange date, agency and platform fees | A cost figure with and without fees |
| Formula | Numerator and denominator choices | Return on spend on gross against net revenue |
| Freshness | When each tool last loaded and whether it backfills | Yesterday's number changing for a week |
The platform versus analytics discrepancies guide explains the mechanics of each; the point here is that none of them is resolved by choosing a better tool. They are resolved by choosing, writing the choice down, and making the tools follow the writing.
What a metrics layer is
A metrics layer, sometimes called a semantic layer, is the place between the warehouse and the dashboards where metric definitions live as code or configuration rather than inside each dashboard. A dashboard asks the layer for "cost per lead by channel by month"; the layer knows which tables to read, which rows to filter, how to aggregate to the grain and which formula to apply, and returns the result. Two dashboards asking the same question get the same answer because the same code produced it.
The layer can be a dedicated product, a feature of the BI tool, a set of modelled tables in the warehouse with the metrics precomputed, or, at the smallest scale, a document that every calculated field is built from by hand. The form matters less than the properties: one definition per metric, readable by the people who use the metric, applied by the tools rather than retyped into them, and changed through a process rather than an edit. A team on Looker Studio usually starts with modelled tables and a dictionary; the calculated fields tutorial shows how to keep the dashboard-side formulas pointing at the modelled fields rather than reinventing them.
The modelled tables underneath, facts at their natural grain joined to shared date, channel and campaign dimensions, are laid out in the guide to designing a marketing data model.
What the layer is not: a dashboard, a report or a list of KPIA metric tied directly to an objective. names. It holds definitions, not opinions about which metrics matter; that choice belongs to the KPI framework and the KPI tree that decomposes an outcome into drivers. The layer makes sure that when the tree says "conversion rate", every branch computes it the same way. When a new dashboard is requested, its specification should list every metric by its key in the layer, so a metric without a definition is caught before the build rather than argued about after it.
Writing a metric definition
A definition is complete when a new analyst could rebuild the metric from it without asking anyone. The template below is the minimum; most teams add a worked example and a link to the query.
| Field | What to write | Example for cost per lead |
|---|---|---|
| Name and key | The display name and the stable identifier used in code | Cost per lead; cost_per_lead |
| Plain-language meaning | One sentence a manager understands | Paid media spend divided by the leads that spend produced |
| Formula | Numerator and denominator as named metrics, not prose | paid_spend divided by attributed_leads |
| Grain | The smallest unit the metric is valid at | Day by channel; valid when rolled up to week, month, channel group |
| Source tables | Which modelled tables, which columns | fct_spend_daily, fct_leads |
| Filters baked in | Rows always excluded or included | Excludes internal test leads and spam-flagged records |
| Attribution rule | How the denominator is credited | Last paid click within the documented window, from the warehouse model |
| Time rule | Which date the row is counted on, in which zone | Lead creation date, in the business's reporting zone |
| Null and zero rule | What happens when the denominator is zero or data is missing | Returns null; dashboards show a dash, not zero |
| Owner | A named person who approves changes | Head of performance marketing |
| Version and status | Current version, date and whether it is certified | Certified; last changed when fees were added to spend |
| Known differences | How and why it differs from platform figures | Lower than platform conversions because of the attribution rule |
Where the source is a CRM, the columns behind each metric are only as reliable as the fields they come from, and the tutorial on auditing and documenting CRM fields builds the field dictionary those definitions can point to.
Two fields cause most of the arguments and deserve the most care. The formula must reference other defined metrics rather than raw columns wherever it can, so that "spend" means the same thing in cost per lead, cost per acquisition (CPACost of each conversion.) and customer acquisition cost (CACCost to acquire one new customer.); when fees are added to spend, every metric that uses it moves together. And the known-differences field is the one managers read first, because the first question about any new number is why it does not match the platform. A definition only helps if people read it, and building data literacy in a marketing team covers putting definitions within reach of every report and teaching the team to check them.
Grain, filters and time
Grain is the unit a single row represents: a day and a channel, a session, an order, a customer. Every metric has a grain at which it is computed and a set of grains it can be rolled up to. Additive measures like spend and revenue roll up freely. Ratios do not: a conversion rate at the daily grain cannot be averaged to get the monthly rate; the monthly rate is recomputed from the monthly sums. Distinct counts do not roll up either; users per day summed across a month overcounts anyone who returned. The definition states the grain and the roll-up rule, and the layer refuses, or at least flags, requests that break it.
Filters come in two kinds, and confusing them is a classic source of drift. Filters baked into the definition apply always: internal trafficVisits from the business's own offices, staff, agencies and testers, labelled by an address rule and filtered so they do not count as customers. excluded, test orders excluded, refunded orders removed from revenue. Filters applied by the viewer are choices: a date range, a channel, a market. A definition that says "revenue" must say whether refunds are already out; a dashboard that lets a viewer toggle refunds is offering a different metric and should name it differently. Where the same base metric is legitimately needed with different baked-in filters, define two metrics with two names, not one with a footnote.
Time has three decisions. Which date a row is counted on: the event, the click, the order, the close, the refund. Which zone: the business picks one reporting zone and converts everything to it on the way into the warehouse, because converting at query time is where daily totals go wrong. And how late-arriving data is handled: platforms restate recent days as conversions arrive within their windows, so yesterday's number is not final for some time; the definition states the restatement window and the dashboard marks recent days as provisional.
Versioning and change
Definitions change, and the damage comes from changing them silently. A fee added to spend, a window widened, a filter tightened: each moves every historical number, and anyone comparing this quarter's dashboard to last quarter's deck sees a difference they cannot explain.
The rules that prevent it are the ones software teams use for interfaces. Every definition carries a version and a change log with the date, the reason and the person. A change that alters the meaning gets a new version; the old one is marked deprecated with the date it stops being served, rather than overwritten. Historical values are restated under the new version where the raw rows allow it, and the restatement is announced with a before-and-after for the key periods. Dashboards show which version they read. Where the change is large enough that the old and new numbers will both be quoted for a while, the new metric gets a new name instead, and the old one is retired through the dashboard governance process. A figure that was simply calculated wrongly is not a new version but an error, and the tutorial on correcting a number after a report has gone out restates it the same open way. When the metric in question is the north star a growth team steers by, the guide to changing a north star metric covers deciding whether a new version is enough and what else has to move with a replacement, from key results to running tests. Before any new version takes effect, the metric definition change checklist gathers the sign-off, the restatement and everything that reads the metric into one list to tick.
Adding agency fees to cost per lead at a legal services firm
A legal services firm defines cost per lead as platform spend divided by qualified leads, and reports £96
for the second quarter. Finance asks for the agency's £4,500 monthly retainer and the £620 monthly
call-tracking subscription to be included, because that is the cost the firm actually pays. The metric
owner issues version two of cost_per_lead with fees in the numerator, restates the previous four quarters,
and publishes a note: the second quarter moves from £96 to £131, and the trend is unchanged because fees were
flat. Version one stays available for ninety days under the name "cost per lead (media only)" so that the
board deck, which quoted £96, can be footnoted rather than reissued. Both numbers appear in the layer with
their version, and the certified dashboard switches to version two on the first day of the next quarter with
a banner explaining the step change.
Enforcing definitions
A definition that lives in a document and is applied by goodwill will drift within a quarter. Enforcement is what makes the layer real, and it has four parts.
Single path. Dashboards read metrics from the layer or from the modelled tables that implement it, never from raw tables or platform connectors. Where the BI tool needs a calculated field, the field references the modelled column and nothing else. A dashboard found computing its own version of a defined metric is treated as a defect. The same applies to queries an AI tool writes: pointing the model at the layer lets it inherit the definitions, and the checking AI-written SQL tutorial covers proving its answer before use.
Ownership. Each metric has a named owner who approves changes and answers questions. Without an owner, the definition is whatever the person who last edited the dashboard decided.
Tests. The modelled tables carry automated checks: unique keys at the stated grain, no nulls where nulls are not allowed, totals that reconcile to the source within a stated tolerance, and a comparison of each certified metric against its previous run so that a sudden move is flagged before a viewer sees it. A failing test blocks the refresh rather than publishing a wrong number.
Review. Changes to definitions and models go through the same review as code: a second person reads the change, the change log is written, and the version moves. The SQL for marketers guide covers the query craft behind the models; the SEO KPIs and reporting guide shows the same discipline applied to a single channel's metrics, where a dictionary alone is often enough.
Enforcement also means saying no. When a stakeholder wants return on spend (ROASRevenue generated per unit of ad spend.) computed on gross revenue because it looks better, the answer is a second, named metric with its own definition, or a refusal; it is never a quiet edit to the certified one. The BI hub places the layer among the other parts of the stack that depend on it holding.
Common mistakes
- Writing a dictionary and stopping. Definitions nobody applies drift as fast as none at all.
- Defining metrics on raw columns. "Spend" should be one defined metric that cost per lead, cost per acquisition and acquisition cost all inherit.
- Averaging ratios across the grain. Monthly rates are recomputed from monthly sums, never averaged from daily ones.
- Mixing baked-in and viewer filters. Refunds either are or are not in revenue; a toggle is a second metric.
- Converting time zones at query time. Convert once, on the way in.
- Changing a definition without a version. Every silent change becomes a reconciliation meeting.
- Leaving the known-differences field empty. It is the first thing anyone asks about.
- No owner. The definition then belongs to whoever edited the dashboard last.
