One analytics table, several sites, and journeys that never happened
If one table holds visits for more than one site, every query that forgets to group by site produces a plausible, confident lie.
How it happens
Sharing one table across sites is a reasonable decision. One schema, one dashboard, one place to look. The problem is not the design, it is that the filter is optional.
Any query that omits it silently blends properties. You get an entry page from one business, an exit page from another, and a funnel that describes nobody. It looks fine, because the numbers are real numbers.
Why it survives
Nothing errors. Nothing looks obviously wrong. The totals are higher than expected, which reads as good news rather than a warning. And it is usually caught only when someone notices a journey that could not physically exist.
What to do
Record the site on every row from day one. Backfilling it later is guesswork, because the evidence you would use to infer it is the same evidence that is now ambiguous.
Then remove the chance to forget:
- Query through per-site views rather than the base table, so the filter is structural rather than remembered
- Keep shared helper scripts that always filter, and use those rather than writing ad hoc queries
- If you must query the base table, make the site filter the first line you write, before the thing you are actually asking
The general principle
Any shared table where the discriminating column is optional in queries will eventually produce a wrong answer that nobody catches. The fix is not discipline, because discipline fails at exactly the moment you are in a hurry. The fix is making the correct query the easy one.
The same shape appears with environments in one database, tenants in one schema, and test data mixed with live. If a filter is load bearing, it should not be optional.
Need help with any of this?
These notes are free and always will be. If you would rather someone just set it up, or you are stuck on something similar, get in touch at hello@opsira.io.