The problem shows up the first time two reports disagree
You pull a churn number one week and a slightly different one the next, and nothing in the business actually changed. Or you build a cohort chart and the retention curve looks off compared to what you remember happening. Nine times out of ten, when I’ve chased this down in my own dashboards, the root cause wasn’t the calculation. It was the date field feeding it.
Most small businesses end up with more date fields than they realize. A subscription tracks signup date, trial start date, first payment date, and subscription start date. An order tracks order placed, payment confirmed, fulfilled, and delivered. None of these are wrong. They’re all real events that really happened. The trouble is that they happened on different days, sometimes hours apart, sometimes weeks apart, and if you don’t decide on purpose which one drives a given report, your reports will decide for you, inconsistently, one query at a time.
This isn’t a technical nitpick. It’s a data modelling decision, and it changes the answers you get from churn, MRR, cohort, and attribution analysis.
Why you end up with four dates in the first place
Every system records a timestamp when something happens in its own workflow, and those workflows don’t run at the same moment.
Take a typical subscription flow. Someone creates an account (signup date). They start a free trial (trial start date). The trial converts and their card gets charged (first payment date). Your billing system then opens a subscription record with its own start date, which might match the payment date exactly, or might not, if there was a proration adjustment, a delayed webhook, or a manual invoice fix.
Or take an order-based business. A customer places an order (order date). Your payment processor confirms the charge (payment date), which can lag the order by seconds or, if a card gets declined and retried, by days. You pack and ship it (fulfillment date). It arrives (delivery date), which you may only know if the carrier reports it back to you.
None of these systems is lying to you. They’re each recording the truth about their own step. The problem is that “when did this customer become a customer” isn’t a single fact, it’s a sequence of facts, and your reporting query has to pick one.
What each date actually tells you, and what it doesn’t
It helps to think of each date field as answering a narrow question, not a general one.
Order or signup date tells you when someone expressed intent. It’s the earliest point you can attach a customer to a marketing channel or a campaign, which makes it the natural choice for attribution. It does not tell you whether they ever paid you.
Payment or first charge date tells you when money actually moved. It’s the honest choice for revenue recognition and for MRR, because MRR is fundamentally a statement about recurring cash, not intent. It does not tell you when the relationship started from the customer’s point of view, and it can be delayed by retries, failed cards, or manual invoicing that has nothing to do with the customer’s actual behavior.
Subscription start or fulfillment date tells you when the service or product actually began. For a SaaS product this is often when access was granted. For a physical goods business it’s when the item shipped. It’s useful for operational reporting (capacity, support load) but it’s a step removed from both intent and payment, so using it for churn or CLV can quietly shift numbers by however long your fulfillment process takes.
Delivery, activation, or “last successful charge” date tells you the most recent confirmed touchpoint. This is usually the right field for churn, because churn is a statement about the last point you know a customer was actively engaged, not the point they first showed up.
None of these fields is a substitute for the others. A date field only answers the specific question it was designed to record.
Match the field to the question, not the other way around
The mistake I see most often, including in my own early dashboards, is picking whichever date field is easiest to query and using it for everything. That’s backwards. The question should choose the field.
If you’re building a cohort analysis to see how retention changes by acquisition month, you want the date that marks the start of the relationship as the customer would describe it, which for most businesses is signup date or order date, not payment date. If a chunk of your trials take two weeks to convert, cohorting by first payment date instead of signup date will bury that lag inside the cohort and make your retention curve look artificially steep in month one.
If you’re calculating MRR, you want the billing period start date from your subscription or invoicing system, because MRR is a statement about recognized recurring revenue on a given date, and that has to tie back to what you actually billed, not when someone first showed interest.
If you’re calculating churn, you want the last date you can point to and say “this customer was still active as of here.” For a subscription business that’s usually the last successful charge date, not the signup date and not the cancellation request date, since a cancellation request and the actual end of paid service are frequently different days.
If you’re doing attribution, working out which channel or campaign a customer came from, you want the earliest date available, because that’s the point closest to the marketing touch you’re trying to credit. Using a later date, like first payment, risks crediting the wrong campaign if the customer’s channel exposure changed between signup and payment.
The pattern across all four: ask what real-world event the metric is supposed to represent, then use the date field that actually recorded that event, not the one that happens to sit in the same table as everything else.
A concrete way this goes wrong
Say you run a small membership site with a seven-day free trial. You build a cohort table by signup date and see 40% of a cohort converting to paid by day 10. Someone on your team rebuilds the same report using first payment date as the cohort anchor instead, because it’s the field that’s already indexed in the billing tool. Now every converted customer in that cohort appears to join on their payment date, which is up to seven days later than their actual signup date. The two reports will show different cohort sizes for the same calendar month, and neither number is “wrong” exactly. They’re answering different questions while looking like the same chart. This is the exact kind of quiet discrepancy that erodes trust in your own numbers if you don’t catch it.
Pick a system of record and write it down
The fix isn’t a clever formula. It’s a governance decision: for each metric you report on regularly, pick one date field as the standard, and document why. This doesn’t need to be a formal process. A single page or even a comment block in your query file that says “churn uses last_successful_charge_date, not cancellation_requested_at, because service continues until the paid period ends” is enough to stop the same argument from happening every quarter.
A few questions worth asking about any date field before you standardize on it: Does every record actually have this field populated, or is it null for a chunk of your data because of how a certain signup path works? Does it ever get backfilled or edited after the fact, which would make historical reports change retroactively? Does it match the real-world event closely enough for the question you’re asking, or is it three steps removed?
None of this replaces judgment. A date field is a proxy for a real event, and proxies can drift from what they’re meant to represent, especially as your systems change over time. Revisit the choice when you add a new signup flow, change payment processors, or notice a metric moving for no operational reason. That’s usually a date field problem before it’s anything else.
If you want more on how the fields underneath a metric shape what it can and can’t tell you, take a look at what else is on the site.
Data Research Analysis Collection
Get new guides and videos first — join the Telegram channel.