The bug that isn’t a bug
A few years into running a subscription business, I went to pull a cohort report. Customers who signed up in a given month, broken out by the plan they started on. Simple enough. Except the numbers didn’t make sense. People who I remembered signing up on the cheapest plan were showing up in the “Pro” cohort.
Nothing was broken. My database was doing exactly what I told it to do. When a customer upgraded, the row for that customer got updated. The plan field changed from “Starter” to “Pro.” That’s what an UPDATE statement does, and it’s the right thing for the app to do, because the app only cares what plan the customer is on right now, so it can bill them and give them the right features.
The problem is that my reporting query was reading that same field and assuming it reflected the past. It didn’t. It reflected today, applied backward. The customer’s whole history had been silently rewritten the moment they upgraded.
This is the core issue behind “tracking changes over time” in customer records: most systems are built to answer “what is true right now,” not “what was true then.” Those are different questions, and a lot of business metrics depend on the second one.
Why overwriting is the default
Databases overwrite by default because it’s cheaper and simpler. One row per customer, one current value per field. Fast to query, easy to reason about, small on disk. For running the day-to-day business, an operational system, this is correct. You don’t want the checkout page joining across ten years of address history to figure out where to ship an order.
The trouble starts when you point analysis at that same table. A metric like churn rate needs to know when someone’s status changed, not just what it is now. Monthly recurring revenue by plan tier needs to know what plan someone was on in March, even if they’ve upgraded twice since. Customer lifetime value segmented by acquisition channel needs the channel they came in on, not whatever channel touched them last if that field gets overwritten on every visit.
If your only record of “channel” is a single field that gets updated every time a returning customer clicks a new ad, you’ve lost the original attribution the moment that happens. There’s no way to get it back later. That’s the honest limit here: change history has to be captured going forward. You can’t reconstruct what you didn’t record.
Two ways to keep history
There are a couple of well-worn patterns for this, and neither requires a data warehouse or a data engineering team. I’ve used simple versions of both in small businesses running on a plain relational database or even a well-structured spreadsheet.
An event log. Instead of updating a field in place, you append a row every time something changes. A table like customer_changes with columns for the customer, which field changed, the old value, the new value, and when it happened. The customer table still holds the current value for operational use, but the log holds the full trail. Every plan upgrade, every status change from active to canceled, every address update becomes a row that never gets touched again.
This is close to what accountants mean by a ledger. You don’t erase a transaction and write a new balance, you add an entry. The current balance is a sum of the ledger, not a stored fact you edit directly.
Versioned records, sometimes called slowly changing dimensions. Instead of a separate log table, you add columns to the customer record itself: a start date and end date for each version, and a flag for whether it’s the current one. When a customer upgrades, you don’t edit the existing row, you close it out by setting its end date and insert a new row with the new plan and a fresh start date. Now a query asking “what plan was this customer on during March” can filter for the row where March falls between the start and end date, instead of trusting a single mutable field.
Data warehousing people call the overwrite-only version “type 1,” this open-and-close-a-new-row version “type 2,” and there’s a “type 3” that just keeps one extra column for the previous value, which works if you only ever need to look back one step. You don’t need the vocabulary to use the idea. The question that matters is: when this changes, do I need to know it changed, and when? If yes, don’t overwrite, append.
What this actually costs you
Keeping history isn’t free, and it’s worth being honest about that instead of pretending it’s a pure upgrade.
It takes more storage, though for most small businesses this is a non-issue, a few thousand customers with a handful of tracked fields is a rounding error on disk space. The real cost is in queries and discipline. Every report that used to be “read the current value” now has to be “read the value as of a specific date,” which is a slightly harder query to write and a slightly easier one to get wrong. And someone has to decide, field by field, whether a change is worth recording. Fixing a typo in someone’s name isn’t the same kind of event as them canceling a subscription, even though both are technically an UPDATE.
I’d rather track fewer things well than everything badly. Before I add a field to a change log, I ask whether a metric I actually look at depends on knowing its history. Plan tier, subscription status, and acquisition source almost always do, because churn, MRR, and CLV calculations touch them directly. A customer’s phone number usually doesn’t need versioning unless you’re doing something specific with it. Tracking everything “just in case” mostly produces a log nobody reads and a maintenance burden nobody wants.
Getting started without overbuilding it
If you’re running things solo, you don’t need a proper data warehouse to get most of the benefit. A few practical starting points:
Check what your existing tools already give you for free. Stripe keeps a full event history of subscription changes. Most CRMs and tools like Airtable have built-in revision history or an activity feed you can query. HubSpot logs property change history automatically for a lot of fields. Before building your own logging, see whether the system of record already has it and you just haven’t been looking.
If you need to build it yourself, a simple trigger or a line in your application code that writes to a changes table whenever a tracked field is updated gets you most of the way there. You don’t need this running for every field on day one. Start with the two or three fields that actually feed a metric you check regularly, plan, status, and the field you use for cohort or attribution grouping, and expand from there once you trust the pattern.
And if you’re already missing history for something you now wish you’d tracked, resist the urge to backfill it with guesses. If you don’t know when a customer actually upgraded, don’t invent a date so the chart looks complete. Either accept the gap, mark those rows as unknown, or start clean from today. A metric that’s honestly incomplete is more useful than one that’s quietly wrong, because at least you know not to trust it.
The takeaway
A record that only shows the current state is answering a different question than most of your metrics are asking. Churn, MRR by segment, cohort retention, and CLV by channel all need to know what was true at a point in the past, not what’s true today applied backward. You don’t have to instrument everything. You have to decide, deliberately, which changing fields your numbers actually depend on, and stop letting those get silently overwritten.
If you want more of this kind of plain walkthrough on the metrics and methods that actually run a small business, from churn and cohorts to data quality practices like this one, you can find the rest of what we’ve covered on the home page.
Get new guides and videos first — join the Telegram channel.