Keeping data clean
One campaign, several names, is the most common failure
The most frequent way marketing data breaks isn’t a tracking outage. It’s the same campaign appearing under three different names because three different people tagged it differently. A reporting tool treatsFacebook, facebook, and FB as three separate sources, and a channel report that should show one row quietly splits into several, none of which shows the true total.
A few habits prevent most of this:
- Lowercase everything. Case differences fragment a source silently, with no error and no warning.
- Pick one separator and use it everywhere. Underscores or hyphens, never both, never a space.
- Use an approved list of values, not whatever feels right at launch. New values get added on purpose, not invented in the moment a campaign goes live.
- Be specific but short.
fbis too vague to be useful. A tag with the objective, audience, region, and date crammed into it is too long for anyone to type correctly twice.
A rule written down is a suggestion. A rule enforced at creation is a fact.
Writing naming conventions in a document changes nothing on its own; it only changes behavior once someone has to follow it to create a link at all. In order of how well each approach actually works: a link builder with locked dropdown values is the only method that scales past a handful of people, because a wrong value simply can’t be entered. Automated validation before a link goes live catches most of what a link builder doesn’t. Mapping known variants to their correct form during processing recovers some of what gets through anyway. A periodic manual audit catches the rest, but by then some of the damage, the data that was never tagged correctly in the first place, cannot be recovered at all. Tagging errors are not like a typo in a report. Once the data was collected wrong, no amount of later cleanup gets the correct version back.Unclassified traffic is usually a tagging problem, not a brand-awareness one
When direct or unassigned traffic rises sharply, the instinctive read is often “brand awareness must be improving.” The far more common explanation is that a tag broke somewhere. Tracking this as a share of total traffic, with an established normal range, turns a vague worry into a specific check: a rising share above that range is a tagging problem to go find, not a metric to celebrate.Why the flat file stores raw numbers, not calculated ones
Store the inputs, calculate everything else, every time
This is the single rule that makes the rest of the flat file work: store what a source system actually reports, cost, clicks, orders, revenue, and calculate everything derivable, cost per click, return on spend, conversion rate, fresh from those inputs whenever it’s needed. The reason is simple once you’ve seen it go wrong. If you store both ad spend and cost per click as separate numbers, and only one of them gets corrected during a routine reconciliation, the two now disagree and nothing in the file tells you which one is right. Storing only the inputs makes that kind of silent disagreement structurally impossible: there’s only ever one version of the underlying number, and every ratio calculated from it stays in sync automatically because it’s recalculated, not remembered.One row per metric, not a table shaped like a whiteboard
Most people picture their reporting data the way it looks on a whiteboard: metrics down the side, weeks across the top, one table per channel. That’s a reasonable way to look at data. It’s a poor way to store it, because adding a fifth channel or a new metric to that shape means restructuring the whole table, and nothing about the structure prevents someone from summing across a boundary that shouldn’t be summed. The alternative is to store one row per combination of date, channel, and metric: this metric, this channel, this day, this value, and nothing else on the row. Nothing about that structure changes when a channel or a metric gets added. You just add rows. The wide table people actually want to look at is something you generate from this format in a few clicks with a spreadsheet’s pivot table, or that an agent builds as plain arithmetic, not something you maintain by hand.How a rollup actually works
The core rule: sum the raw numbers, then recalculate every ratio
A monthly report is not a new report written from scratch. It’s what you get when you take the raw inputs from every day in that month, add them up, and recalculate every ratio from those totals. A weekly report does the same thing with its seven days, a quarterly report with its three months, and an annual report with its four quarters. Any date range works the same way, October 2 to November 1 included. The chain only works because every level is built from the same raw daily inputs. What doesn’t work, and what quietly produces wrong numbers even though it feels like the obvious shortcut, is averaging the four weekly ratios directly. A worked example shows why:Two habits keep the rollup safe
Two kinds of double-counting can creep into a rollup, and the flat file’s structure closes off both. Store one period per row, never a running total. Spend during one day and total spend since the account started are both legitimate numbers, but summing a cumulative figure across rows counts every earlier day’s spend several times over. The flat file stores only the first kind: every row covers one day, named in itsdate column, and any to-date figure is calculated by summing the dated rows rather than stored as its own number.
Keep every row at the same time grain. The flat file’s base grain is a day. A file that also held weekly or monthly rows would double-count the moment a week’s row was summed on top of the daily rows that made it up. The one exception is the legacy weekly fallback in the flat file template: if older history still lives at the weekly level, filter to a single level before adding anything up.
The same problem exists sideways, and it is the one people miss
Granularity is usually discussed as a question about time, but the identical trap opens along the entity axis. A file that stores spend per channel and spend per campaign holds the same money twice: once as one row, once as the forty rows that make it up. Nothing about the two looks different at a glance, and a pivot table that doesn’t distinguish them will happily report double the spend that actually occurred. The fix is the same shape as the fix for time: name the level on every row and filter to one level before summing. In the flat file that column isgrain, and it takes one of channel, campaign, ad_group, or ad.
This is also what makes a naming convention pay off. The reason campaign, ad set, and ad names are built from closed lists is that each name is a join key, and a join key only works if it arrives in the reporting file as its own column rather than as a string somebody reads. A convention with no column to land in produces tidy names and no new answers.
The rollup also carries the estimate flag forward
A number calculated before its inputs have fully settled, spend before a platform’s final reconciliation, cost of goods before a finance close, is legitimately useful for an early read, but it should be marked as an estimate everywhere it appears. When a monthly figure rolls up from daily rows and some of them were still estimates at the time, the monthly figure inherits that same uncertainty until the underlying daily rows are restated and theis_estimate flag comes off.
Pacing against a plan
Pace: the one formula
Pace compares what has happened so far against where the plan says you should be by now:Checks before every report
A short set of checks, run the same way every time, catches most of what goes wrong before it reaches a reader:- Spend for the period matches what the ad platforms themselves report.
- Revenue matches the approved source of truth, within the normal gap already established for that comparison.
- Volumes (sessions, conversions) sit inside their established normal range.
- No channel value looks unclassified or missing in a way that wasn’t there last period.
- The date range, time zone, and attribution setting are the ones the dictionary specifies.
When something breaks mid-period
Tracking sometimes fails partway through a reporting period, a campaign gets mistagged, or a source goes down entirely. The instinct to quietly patch over the gap with an estimate presented as a real number is the one to resist. Report the metric with the gap stated plainly, log what happened and when, and never silently drop the affected period from a trend without a note explaining why it’s missing. A gap with a note reads as honest. A gap with no note reads as a performance change that never actually happened.Related resources
- Flat file template The schema this page’s reasoning is built for.
- Reporting framework concepts How the metrics stored in the flat file get defined.
- Report writing concepts How the rolled-up numbers become an actual report.
MCP wiring
MCP wiring
Type: standard
Domains: reporting, tracking
Provides:
Uses:
Domains: reporting, tracking
Provides:
concept:rollup, concept:rollup, concept:rollupUses:
budget-plan, data-dictionary, flat-file, kpi-definitions, naming-convention, utm-rulesGet 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.