SaaS revenue analytics and MRR tracking

How to test this use case

Measure SaaS revenue analytics with SQL and check the results

To measure saas revenue analytics with sql, choose one workflow and its owner. Define the events, test them with known inputs, and write a query that answers a specific question.

1
Choose when to log
Document the source of truth for subscriptions, invoices, entitlements, and account identity.
2
Capture the outcome
Begin with subscription_revenue_moved, invoice_payment_completed, invoice_payment_failed and document the grain of each event.
3
Check the stored rows
Join reviewed revenue movements to product outcomes only after validating account identity and cohort windows.
4
Make the decision
How much MRR came from new, expansion, contraction, churn, and reactivation movements?

Related use cases

Check which events this guide covers

Use this guide for subscription revenue changes and revenue retention.
Use usage-based billing analytics for meter, quota, entitlement, and invoice-input correctness.
Use product analytics and retention to compare usage and return visits across cohorts.

Agent prompt

Paste this into your coding agent

Replace YOUR_API_KEY after signup, then ask the agent to run the product flow and verify the first events.

agent prompt

SaaS revenue analytics with SQL setup prompt

text

Instrument SaaS revenue analytics with Telemetry.

Use /skill.md and this Telemetry API key: YOUR_API_KEY

Create immutable subscription_revenue_moved events for new, expansion, contraction, churn, and reactivation movements. Include account_id, subscription_id, movement_type, prior_mrr_minor, new_mrr_minor, movement_mrr_minor, currency, effective_at, plan, billing_interval, reason_category, source_event_id, and schema_version.

Add invoice payment and entitlement outcome events with stable provider identifiers so a closed period can be reconciled without counting webhook retries twice. Connect product outcomes using the same approved account_id.

Create SQL for MRR movement, gross and net revenue retention, payment-failure recovery, expansion by activation cohort, and revenue at risk after reliability incidents.

Keep money in integer minor units, preserve currency, document the reporting timezone and cohort window, and do not log card data, invoice PDFs, customer names, email addresses, or unrestricted provider payloads.

Setup steps

1Document the source of truth for subscriptions, invoices, entitlements, and account identity.
2Emit immutable revenue-movement events with currency, effective time, prior value, new value, and reason category.
3Reconcile a closed billing period against the provider export before publishing MRR or retention metrics.
4Join reviewed revenue movements to product outcomes only after validating account identity and cohort windows.

Events to capture

subscription_revenue_movedinvoice_payment_completedinvoice_payment_failedentitlement_changedaccount_product_outcome_completed

Questions you can answer

How much MRR came from new, expansion, contraction, churn, and reactivation movements?
Which activation and adoption patterns precede retained or expanded revenue?
Which failed payments, reliability incidents, or entitlement changes put recurring revenue at risk?

Example event schemas

Check what each event records, when to send it, and which field types it needs. Review the example payload and privacy checklist before using it in production.

Product lifecycle7 fields