Common event contract
Fields that keep these queries reusable
- timestamp_utc, account_id, event_name, plan, and currency
- amount_usd, previous_mrr_usd, new_mrr_usd, and invoice_id
- payment_attempt, failure_type, acquisition_source, and cohort
Definitions before SQL
Decisions the query cannot make for you
- 1Normalize money to one reporting currency without discarding the source amount.
- 2Define upgrades, downgrades, churn, and reactivation as mutually exclusive movements.
- 3Deduplicate provider events before aggregating revenue.
Recommended sequence
Build detection first, then diagnosis
Analysis patterns
Make the result explain a decision
Reconcile movements
Classify new, expansion, contraction, churn, and reactivation movements so beginning MRR plus movements equals ending MRR.
Keep cohorts observable
Compare acquisition, plan, and signup cohorts only after each cohort has had the same opportunity to convert or renew.
Preserve source money fields
Store the original amount and currency beside the normalized reporting value so financial totals can be traced.
Complete recipes
Copy the query, then validate the assumptions
Calculate Trial-to-Paid Conversion
Measure how many trial accounts become paid customers within a fixed conversion window.
Which trial cohorts and acquisition sources convert to paid accounts?
See SQL and resultMeasure Payment-Failure Recovery
Calculate how often failed invoices are recovered by a later successful payment attempt.
Which payment failures recover, and how much revenue remains at risk?
See SQL and resultCalculate Monthly Recurring Revenue Movement
Separate new, expansion, contraction, churn, and reactivation MRR from billing lifecycle events.
What caused recurring revenue to grow or shrink each month?
See SQL and resultCalculate Net and Gross Revenue Retention
Calculate NRR and GRR from account-level monthly recurring-revenue snapshots while keeping expansion out of gross retention.
How much starting recurring revenue was retained before and after expansion?
See SQL and resultAdapt the event contract before the threshold
Keep the analysis pattern, but validate table names, field types, business definitions, time windows, and minimum-volume rules against your own events. Every published query is also planned and executed against an empty typed table with the pinned engine.