Skip to main content
This page explains the schema and how to build it as a plain spreadsheet. Examples use Doughnut Labs, a SaaS company that sells disruptive Doughnut Technology, modelled here as its direct-to-consumer arm so the numbers have orders and revenue behind them.
Copy the blank template linked below into your own spreadsheet and start filling in rows each week. Nothing about the structure changes when you add a channel or a metric; you just add rows. See data quality and rollup concepts for why this shape, one row per metric, works better than the wide table most people picture first.

​
Why one row per metric

Most people picture a metrics table like this, metrics down the side, dates across the top: That’s a fine way to look at data. It’s a poor way to store it, because adding a second channel means either a second table, a second header row, or metric names that awkwardly embed the channel (Ad spend (Meta), Ad spend (Google)). Every one of those breaks the moment you add a third channel. The fix is to store one row per date, channel, and metric instead: Nothing about this structure changes when a channel or a metric gets added. You add rows, never new columns. The wide table at the top of this page is one pivot table away from this format; it’s a view you generate, not the thing you store.

​
The schema

Sixteen columns, in two blocks. The core nine describe the measurement. The dimension seven say what was being measured, and they are what every naming convention in this knowledge base exists to fill.
The core nine are the same for every business. Some of the dimension columns only earn their place in some businesses: product matters once you sell more than one thing, and attributed_week matters once results arrive later than the spend that caused them. Leave a column out until you need it, and add it from the week you do. See which columns your business needs.

​
Core columns

Always filled in, on every row.

​
Dimension columns

Filled down to the row’s grain, blank below it. Leave them genuinely blank, not n/a or -, so a filter for “blank” works. Each of these has exactly one source, and none of them is typed by hand at report time:
Never sum across grains. A week has an ad_spend row at channel grain and several more at campaign grain, and they describe the same money. Adding them together double-counts it. Filter to a single grain value before adding anything up, every time, exactly as you already filter to a single period.

​
Lagging outcomes and the week they belong to

Some results arrive long after the spend that caused them. A deal closing in June was often generated by leads, and budget, from March. Two pages in this framework tell you to report those against the period that generated them rather than the period they landed in, and this is the column that makes that possible. On an ordinary row the two are the same thing, so attributed_week stays blank. On a lagging outcome they differ, and both are needed: the close date is a fact about the deal, the attributed week is what lets the cost per result be calculated against the spend that produced it. A closed deal, closing in June, generated by March’s leads: Cost per closed-won for the March cohort is then March’s ad_spend divided by every closed_won row whose attributed_week falls in March, whenever those deals actually closed. Filtering on week_start instead gives you June’s spend over June’s closes, which is the number all three pages warn against.
A cohort read is never final. Deals from a recent cohort are still open, so its cost per closed-won will only ever fall as more of them close. Read a cohort against cohorts of the same age, not against last month, and mark rows from an incomplete cohort with is_estimate set to true.

​
Fractional credit

The rows above carry 0.6 and 0.4 against a metric whose unit is count, which looks like it breaks the whole-number rule in the checks below. It is a deliberate exception, and it is the only one. Where an attribution model splits one outcome across several channels, the credit is fractional by definition. One deal influenced by two channels is not two deals. The alternative, giving one channel the whole deal, is a single-touch model, which is a legitimate choice and a different one.
Recording the model once per metric rather than once per row is what keeps this honest. A file where some rows were split one way and some another, with nothing saying which, produces a total that is correct only by accident.

​
Splitting the product out of the campaign name

product duplicates something already inside campaign, which looks like it breaks the rule two sections down about never storing a value you can derive. It doesn’t, and the difference is worth being precise about. That rule protects against a derived metric going stale: store cost_per_click and then correct clicks, and the two disagree with nothing to say which is right. Dimensions have no such failure mode, because a name is never changed after launch. The string the product was split out of cannot move underneath it. What you gain is that the split happens once, at import, under one rule, instead of every report re-parsing a compound string and each one drawing the boundary slightly differently. A report that has to run SPLIT(campaign, "-") before it can group by product is a report that will eventually group by the wrong segment.

​
Which columns your business needs

This file has a fixed core and a set of extensions. Which extensions apply depends on how your business converts, not on how thorough you are. Ecommerce and sales-led businesses need different extensions, and this is the main place the difference shows up: Adding a column later costs nothing structural. You start filling it from that week forward, and older rows keep whatever they were collected with.

​
Only store the grains you report on

Every extra grain multiplies the row count: a week with 40 campaigns, 120 ad groups, and 400 ads carries roughly 560 rows per metric instead of one. Adding a grain later costs nothing structural. You start writing the extra rows from that week forward, and history stays at whatever grain it was collected at.

​
Why weeks, not days

This framework reports on a weekly base grain, since that’s the shortest cadence most brands actually act on, and everything longer (monthly, quarterly, annual) is a rollup built from weeks rather than a separately-collected number. If your team genuinely makes same-day decisions from data, add a daily grain alongside the weekly one; most brands don’t need to.

​
blended is a real channel value, not a summary row

Business-level metrics, total revenue, total marketing spend, contribution margin, aren’t attributable to one channel, so they get their own blended rows rather than living implicitly as a sum of the channel rows. This is what stops someone accidentally summing a per-channel ratio (like ROAS) into a meaningless blended number; ratios like that have to be recalculated from blended inputs, never summed from channel-level ratios. A blended row sits at channel grain with channel set to blended, and every dimension column blank. There is no campaign behind total revenue.

​
Store the inputs, never the calculated numbers

Store ad_spend and clicks. Don’t store cost_per_click. Calculate it fresh, every time you need it, from the two numbers you actually stored. If a derived number gets stored and one of its inputs is later corrected, the two quietly disagree and nothing in the file tells you which one is right. See the formula blocks in the data dictionary for how each derived number gets calculated from its inputs, and the ecommerce and sales-led starter packs for a ready-made list of common inputs and the metrics they produce.
One exception, and it is worth naming because the rule above reads as absolute. A few metrics are aggregates of a grain this file does not hold: pipeline_value is summed from individual CRM deal records, and sales_cycle_days is averaged across deals. Neither can be recalculated from anything in the flat file, because the deal-level rows live in the CRM. Store them as inputs, and record in the data dictionary which system computed them and over what set, so the number can be reproduced where it was made.

​
A worked week

One week at two grains, showing how the same money appears once per grain. Dimension columns are trimmed to the four that carry values here; a real file keeps all six. Channel grain. What weekly and monthly reporting reads. Campaign grain. The same Google spend, split across the campaigns that make it up. The three campaign-grain ad_spend rows add to 28,900, the channel-grain figure. That is why they are never summed together. A pivot that doesn’t filter on grain reports 57,800 of Google spend in a week that had 28,900. With the product column populated, “what did we spend on Analytics Pro this week?” is a filter on one column: grain = campaign, product = DAPRO. Without it, that question needs someone to read campaign names and add up by hand. Note what’s absent: no cost_per_click, roas, or contribution_margin rows. Those get calculated when a report needs them, not stored.

​
Rolling weeks up into months, quarters, and years

A monthly total is every matching row, filtered to the weeks that fall in that month, added together, with every ratio then recalculated from the summed totals rather than averaged from the weekly ratios. A spreadsheet pivot table does this in a few clicks:
  1. Select the whole flat file range.
  2. Insert a pivot table.
  3. Filter grain to a single value. Do this first, before anything else. A pivot spanning two grains double-counts every number it touches.
  4. Set rows to metric, columns to week_start (or filter to a channel first if you want one channel’s view).
  5. Set values to sum of value.
  6. Filter channel to the one you want, or leave it as a row field to compare channels side by side.
That gives you the wide, human-readable table. To report per product, per campaign, or on a single test, keep step 3 and change step 4: See data quality and rollup concepts for a worked example of why summing first and recalculating after is the only version of this that produces a correct number.

​
Checks before you trust a week’s rows

Run through this list before using a week’s data in a report. None of it requires code, just a scan of the new rows. Structural
  • Every row has all nine core columns filled in.
  • week_end is six days after week_start.
  • grain is one of channel, campaign, ad_group, ad.
  • Dimension columns are filled down to the row’s grain and blank below it. A campaign-grain row with an ad value is a row at the wrong grain.
  • channel matches your approved list in the data dictionary’s source of truth section.
  • metric matches a metric defined in the data dictionary’s formula blocks.
  • No duplicate rows for the same week, grain, channel, metric, and dimension set.
  • value is a bare number: no $, no %, no commas.
Dimensions
  • Every campaign value matches a live campaign name character for character, and matches its utm_campaign. A near-match is a broken join, not a typo.
  • Every product value exists in the product catalog. A value that doesn’t is a product somebody invented at build time.
  • Every experiment_id matches a registered ID in the experiment log. An unregistered ID belongs to a test nobody briefed.
  • The dimension columns were split at import under one rule, not typed in per report.
Logical
  • Percent values look like percentages, not decimals. A 3.44% conversion rate is stored as 3.44, not 0.0344.
  • Counts are whole numbers, unless the metric carries fractional attribution credit. See fractional credit for which metrics may, and what the fractions have to sum to.
  • clicks is less than or equal to impressions.
  • purchases is less than or equal to any funnel stage above it (checkouts, add-to-carts).
  • No gaps: every week has a full set of rows for every channel you track.
Cross-checks
  • Spend matches what the ad platforms themselves report for the week.
  • total_revenue matches your approved source of truth.
  • Sum of channel-level conversion_value sits within the normal gap you recorded against total_revenue in the data dictionary; it should be lower, never higher by an unusual margin.
  • For any grain you store below channel, its rows sum to the channel-grain figure for the same week and metric. A gap means a campaign is missing from the file or one is being counted twice.
If a check fails, fix it before building anything on top of that week’s data. An error caught here costs a few minutes. The same error found after it’s already shaped a report costs a great deal more.
Last modified on August 12, 2026