Copy the blank template (spreadsheet or CSV) into your own spreadsheet and start filling in rows each day through the scheduled daily fill, or each week by hand. 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
Fifteen columns, in two blocks. The core eight 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. Each row covers one day, so a report over any date range, such as October 2 to November 1, is a filter, never a restructure.The core eight 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_date matters once results arrive later than the spend that caused them. Leave a column out until you need it, and add it from the day 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, notn/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:
Lagging outcomes and the date 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_date stays blank. On a lagging outcome they differ, and both are needed: the close date is a fact about the deal, the attributed date 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_date falls in March, whenever those deals actually closed. Filtering on date instead gives you June’s spend over June’s closes, which is the number all three pages warn against.
Fractional credit
The rows above carry0.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 day 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 day 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 day forward, and history stays at whatever grain it was collected at.
The base grain is a day
Each row covers one day, and every report is a date-range filter over the rows: a week, a calendar month, a quarter, or any range you need, such as October 2 to November 1. Nothing ties a report to period boundaries the file holds, because the file holds none. Daily rows were the expensive option when a person loaded the file by hand, so teams loaded weekly and accepted the rough edges: month-to-date reads, weeks straddling a month boundary. An agent filling the file each morning removes the loading cost, and the rough edges with it. Reporting cadence is a separate choice: the weekly report stays the shortest written report, because day-over-day movement is mostly noise. If you do load by hand, weekly loading still works; write the week’s totals as one row dated the Monday of that week, and keep the choice consistent.Daily fill and restatement
When an agent loads the file, it writes yesterday’s rows each morning: one row per metric, per channel, per grain you store. Conversion metrics settle over several days, so recent rows carryis_estimate set to true and get restated in place once the platform’s figures settle; spend and impressions settle fast and rarely need the flag. Record the restatement window in the data dictionary, and treat a restatement as an update to the row, never a second row.
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
Storead_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 day
One day at two grains, showing how the same money appears once per grain. Dimension columns are trimmed to the ones that carry values here; a real file keeps all seven. Channel grain. What every report reads, over whatever date range it covers.
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 on a day that had 28,900.
With the product column populated, “what did we spend on Analytics Pro this period?” 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 days up into weeks, months, quarters, and years
A period total is every matching row, filtered to the dates that fall in that period, added together, with every ratio then recalculated from the summed totals rather than averaged from smaller-period ratios. Any date range works the same way: a Monday-to-Sunday week, a calendar month, a quarter, or October 2 to November 1. A spreadsheet pivot table does this in a few clicks:- Select the whole flat file range.
- Insert a pivot table.
- Filter
grainto a single value. Do this first, before anything else. A pivot spanning two grains double-counts every number it touches. - Set rows to
metric, columns todate, grouped into weeks or months in the pivot (or filter to a channel first if you want one channel’s view). - Set values to sum of
value. - Filter
channelto the one you want, or leave it as a row field to compare channels side by side.
For any rollup, filter the pivot’s date range to the period’s dates and let the sum do the adding up. Then recalculate every ratio (ROAS, conversion rate, contribution margin) from the summed totals shown in the pivot, exactly as the monthly report template walks through.
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 new rows
Run through this list before using new rows in a report. The checks are testable assertions: a person can scan for them, and the agent that loaded the rows can run them on every fill. Structural- Every row has all eight core columns filled in.
dateis a valid calendar day inYYYY-MM-DDform.grainis one ofchannel,campaign,ad_group,ad.- Dimension columns are filled down to the row’s grain and blank below it. A campaign-grain row with an
advalue is a row at the wrong grain. channelmatches your approved list in the data dictionary’s source of truth section.metricmatches a metric defined in the data dictionary’s formula blocks.- No duplicate rows for the same date, grain, channel, metric, and dimension set. A restatement updates the existing row.
valueis a bare number: no$, no%, no commas.
- Every
campaignvalue matches a live campaign name character for character, and matches itsutm_campaign. A near-match is a broken join, not a typo. - Every
productvalue exists in your product pages. A value that doesn’t is a product somebody invented at build time. - Every
experiment_idmatches 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.
- Percent values look like percentages, not decimals. A 3.44% conversion rate is stored as
3.44, not0.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.
clicksis less than or equal toimpressions.purchasesis less than or equal to any funnel stage above it (checkouts, add-to-carts).- No gaps: every day has a full set of rows for every channel you track.
- Spend matches what the ad platforms themselves report for the same dates.
total_revenuematches your approved source of truth.- Sum of channel-level
conversion_valuesits within the normal gap you recorded againsttotal_revenuein 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 date and metric. A gap means a campaign is missing from the file or one is being counted twice.
Related resources
- Data quality and rollup concepts
- Data dictionary
- Ecommerce metrics starter pack
- Sales-led metrics starter pack
- Weekly report template
- Campaign naming conventions The source of the
campaigncolumn. - Product pages The source of the
productcolumn.
MCP wiring
MCP wiring
Type: spec
Domains: reporting, tracking, naming
Provides:
Uses:
Domains: reporting, tracking, naming
Provides:
flat-fileUses:
data-dictionary, experiment-log, kpi-definitions, naming-convention, utm-rulesOutput: A long-format spreadsheet schema and validated daily rowsGet the complete Marketing OS
Subscribe to Snow Doughnut, our free weekly newsletter, and get the Notion workspace where you do the work: tasks, a marketing calendar, name generators, briefs, and more.
Have us run it for you
We implement this system, use it to help you grow and scale, then hand it all off to you when you’re ready.