First-party email analytics: the minimum schema, and the discipline that keeps it honest

Owning your analytics outright means the events land in tables you own, written by code you control, queried with SQL you wrote. No vendor script in the page, no data crossing a boundary, no sampling, no retention window somebody else set. When a number looks odd you can read the raw rows that produced it — which is the whole argument, and it is a strong one.

The cost is that you own the failure modes too. A hosted analytics tool at least has a setup wizard that complains when the tag is missing. Your own pipeline has nothing of the kind. A column with no writer still returns rows. A dashboard built on it still renders. The chart is confident, the axis is clean, and the number is wrong. Every failure below is of that shape: nothing threw, nothing 500'd, nothing was flagged.

Three things need to exist before any of this is worth doing. Not three tables necessarily — the shape matters more than the table count.

Visits. One row per page view worth counting: timestamp, the page or slug, an anonymous visitor id, and a traffic-type flag (more on that below). This is the denominator for everything else.

Conversions. One row per action that has a value attached — a download, an email signup, a CTA click, a trial start. Same visitor id, same timestamp discipline. Separate rows, not a boolean on the visit, because one visitor produces many and you want to count them independently.

Source. Four columns — referrer, utm_source, utm_medium, utm_campaign — stamped onto every event row, visits and conversions alike. Not held in a sessions table and joined at read time. Denormalise it. Session-join attribution looks tidier in the schema diagram and it is where first-party analytics goes to die: the join key drifts, sessions expire differently to what the query assumes, and now you are debugging attribution and session logic at once.

Where that source lives is a data-model question, not an analytics one. The same reasoning that governs custom attribute design in your ESP applies here — decide what each column means, who writes it, and what null means, before the first insert. The CRM Data Model Design skill covers the identity and taxonomy layer underneath.

Two browser facts decide the whole capture design, and the second one catches most teams out.

One. document.referrer is set once at document load and does not change across client-side navigations. Someone who arrived from Google keeps a google.com referrer for the whole session. For first-touch attribution that is exactly the behaviour you want.

Two. UTM parameters do not behave that way. They drop off the URL the moment the user navigates client-side — which, in any modern router, is on the first internal link they click. Read them per-event and you attribute the landing page to the campaign and every page after it to nothing.

So: snapshot the referrer and the UTMs on the first event of a session, store the snapshot in sessionStorage, and replay it on every later event. That is the entire mechanism. It is about fifty lines.

The email consequence is direct. Your send lands a reader on a page carrying utm_source=email. They read it, click through two internal links, and download the thing on the third page. Without the snapshot, the campaign gets a page view and the download gets recorded as direct. Email loses credit not to another channel but to nothing at all — which is a harder argument to win in a budget meeting than losing it to paid, because there is no counterparty to check the maths against. The attribution models guide covers what to do with the credit once you can actually see it.

Here is the first bug. Three of our tables — downloads, app visits, guide visits — each carried those four source columns. The acquisition summary read all four. And the three tracking functions that insert the rows wrote the traffic-type flag and nothing else. Eighteen columns, one reader, zero writers.

Every row was null. The server-side referrer normaliser maps null to "direct". So the acquisition card reported 100% direct traffic, and had done for months. Not a measurement subtlety about referrer stripping or privacy browsers or dark social — the explanations we would have reached for first. A missing INSERT.

Nothing about it looked wrong from the outside. Migrations ran, columns existed, the dashboard rendered. Odd but not impossible — new site, lots of direct traffic, sure. It survived because nobody ran the read query against real rows and looked at the null rate.

The working version of this is a write-then-read pass: after adding a column, run the query that reads it against production data and check what share came back null. It takes a minute. Both of the bugs in this guide would have died there. The Lifecycle Reporting Framework skill builds the same check into the metric-definition step, which is the right place for it — a metric definition that does not name its source column is not finished.

The second bug ran in the opposite direction. We put a QA batch of questions through the site's own chat to check the knowledge base, and did not tag the traffic. Those rows landed in the same table as real visitor questions.

The metric that measures how often the knowledge base has no answer went from around 17% to 80.5%. Read as customer research, that says most people asking us things are leaving empty-handed, and it would have started a content project. Read correctly, it says a QA batch deliberately probed the edges of the corpus, which is what a QA batch is for. Nobody caught it from the chart. Someone re-read the raw rows and recognised the questions.

Test traffic is not a tidiness problem. It is the largest single source of measurement error in a first-party setup, because your team generates far more events per person than any real visitor does, and it clusters — one QA session, one afternoon, one metric.

The mechanism is small. A known query parameter on the test URL sets a flag in sessionStorage; every beacon includes it in its POST body; the server writes traffic_type='test'; every dashboard query filters it out. Three details make it hold up:

Filter with IS DISTINCT FROM 'test', not IS NULL. Nulls count as real, and so does any future category you add — bot, internal, staging. Filter on null and every new tag silently disappears from every number the day it is introduced.

Ship the tag before the first QA run, not after. You cannot retroactively tag rows you have no way of identifying. Our recovery was a human recognising the questions, which does not scale and does not work at all once the batch looks like ordinary traffic.

Tag at the session, not the request. One flag set at entry, replayed by every beacon for the rest of the session — the same snapshot-and-replay pattern the attribution capture uses. Two mechanisms doing this differently will eventually disagree about which session a hit belonged to.

This is the same failure family as a contaminated test group, and it has the same fix: decide who is excluded before you collect, never after you see the result. False-positive prevention covers the discipline on the experiment side, and incrementality test design covers what it takes to keep a holdout clean.

The recommendation: first-party is the source of truth for any number that goes into a board deck, a budget case, or a decision to kill something. A third-party tool sits alongside it as a second opinion on traffic shape — and only if someone can name the question it answers that your SQL cannot.

Three honest reasons to keep GA4 or a lightweight alternative running. Someone needs a login and should not have database access. You want channel groupings and bot filtering you would rather not maintain yourself. Or you want an independent read on traffic volume, precisely because two systems that disagree is information — a divergence tells you one of them broke, and a single number never will.

The dishonest use is reaching for the third-party number to explain away one your own tables disagree with. When they diverge, reconcile: find the population each one counts and the events each one drops. Expect the third-party number to be lower on traffic, because blockers and consent refusals suppress a vendor script but do not touch a same-origin POST to your own endpoint. That gap is a known bias, not a mystery, and it is a reason to trust your tables more, not less.

One boundary worth naming: first-party analytics starts at the click. What happens inside the mailbox — opens, and how much of an open number is real — belongs to your ESP and its own well-documented distortions. Deliverability is the other half of that picture, and the lifecycle metrics dashboard guide covers stitching ESP-side and site-side numbers into one view without double-counting.

If you take one thing from this into Monday: open the query that feeds your most-quoted number, find the column it reads, and check who writes it. That single pass would have caught both of the bugs above, and it costs a minute.