Revenue and subscription analytics

Revenue dashboards are valuable when they help a team understand product performance. They are dangerous when a convenient analytics number is mistaken for an accounting ledger. Keep the billing provider or application database authoritative, and send well-defined billing facts to GraphJSON for analysis.

Choose the source#

Use one of two patterns:

• Connect the Stripe integration when its event stream and properties match the questions you need.
• Emit normalized events from your billing service when you need provider-independent names, joined product context, or a narrower privacy surface.

Do not send the same billing fact through both paths into one metric unless they have distinct event_id namespaces and a deliberate deduplication rule.

Define immutable billing facts#

Payment success:

{
  "event": "payment_succeeded",
  "event_id": "stripe:evt_123",
  "account_id": "acct_7",
  "invoice_id": "in_91",
  "subscription_id": "sub_44",
  "amount": 4900,
  "currency": "usd",
  "plan": "pro",
  "billing_interval": "month",
  "schema_version": 1
}

Refund:

{
  "event": "refund_succeeded",
  "event_id": "stripe:evt_124",
  "account_id": "acct_7",
  "payment_id": "pay_61",
  "amount": 1200,
  "currency": "usd",
  "reason": "requested_by_customer",
  "schema_version": 1
}

Subscription state change:

{
  "event": "subscription_status_changed",
  "event_id": "stripe:evt_125",
  "account_id": "acct_7",
  "subscription_id": "sub_44",
  "previous_status": "trialing",
  "status": "active",
  "plan": "pro",
  "mrr": 4900,
  "currency": "usd",
  "effective_at": "2026-07-26T18:00:00Z",
  "schema_version": 1
}

Store amounts in the currency’s minor units and include the lowercase currency code. Do not assume every currency has two decimal places.

Avoid names, emails, card details, addresses, invoice PDFs, or full provider payloads. Keep provider object IDs only when they support reconciliation or investigation.

Make webhook handling idempotent#

Billing providers retry webhooks. Record the provider event ID in your application database before enqueueing analytics, and make the business-state handler idempotent.

Use the same provider event ID as the GraphJSON event_id so uncertain analytics retries can be deduplicated:

receive and verify webhook
      ↓
transaction: apply state + record provider event + write outbox
      ↓
acknowledge provider
      ↓
worker sends normalized event to GraphJSON

Never apply billing state from GraphJSON. The flow is one way from the billing authority to analytics.

Query gross and net collected revenue#

WITH billing AS (
  SELECT
    JSONExtractString(json, 'event') AS event,
    JSONExtractString(json, 'currency') AS currency,
    JSONExtractInt(json, 'amount') AS amount
  FROM billing_events
  WHERE
    JSONExtractString(json, 'event') IN (
      'payment_succeeded',
      'refund_succeeded'
    )
    AND toDateTime(timestamp) >= subtractMonths(now(), 3)
)
SELECT
  currency,
  sumIf(amount, event = 'payment_succeeded') AS gross_minor_units,
  sumIf(amount, event = 'refund_succeeded') AS refunds_minor_units,
  gross_minor_units - refunds_minor_units AS net_minor_units
FROM billing
GROUP BY currency

This is an operating view of collected payments and refunds. It is not automatically GAAP revenue, accrued revenue, tax reporting, or cash settlement.

Query current recurring revenue#

For one current subscription record per account:

WITH latest AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    argMax(JSONExtractString(json, 'status'), timestamp) AS status,
    argMax(JSONExtractString(json, 'currency'), timestamp) AS currency,
    argMax(JSONExtractInt(json, 'mrr'), timestamp) AS mrr
  FROM billing_events
  WHERE
    JSONExtractString(json, 'event') = 'subscription_status_changed'
    AND JSONExtractString(json, 'account_id') != ''
  GROUP BY account_id
)
SELECT
  currency,
  sumIf(mrr, status = 'active') AS active_mrr_minor_units,
  countIf(status = 'active') AS active_accounts
FROM latest
GROUP BY currency

This event model assumes one relevant subscription state per account. If accounts can have several concurrent subscriptions, group by subscription_id first.

MRR requires a normalization policy for annual, usage-based, discounted, paused, and multi-currency subscriptions. Put that policy in the billing service and include the normalized mrr value and currency; do not hide it inside one dashboard query.

Build a decision-oriented dashboard#

Include:

• gross payments and refunds by currency
• net collected amount over time
• new active subscriptions
• upgrades, downgrades, cancellations, and reactivations
• current MRR by plan and currency
• payment failures and recoveries
• product activation by paid plan

Keep payment-flow metrics separate from subscription-state metrics. A failed payment does not always mean immediate churn, and a canceled subscription may remain active through its paid period.

Reconcile every critical metric#

For a settled daily interval, compare GraphJSON with:

• provider event export
• application webhook processing table
• invoices and refunds in the billing database
• finance-approved recurring revenue model

Check event IDs, counts, currency totals, minimum and maximum timestamps, and missing account mappings. Investigate duplicates separately from missing events.

Document expected timing differences. Webhooks can arrive late or out of order, and finance reports may use settlement, invoice, or service-period dates instead of event arrival time.

Launch checklist#

• The billing provider or database remains the authority.
• Webhooks are verified and applied idempotently.
• An outbox separates business state from analytics delivery.
• Provider event IDs become stable analytics event IDs.
• Money uses minor units plus explicit currency.
• Refunds and subscription changes are separate facts.
• MRR policy covers annual, discount, pause, and multi-subscription cases.
• GraphJSON metrics reconcile with provider and finance sources.
• Dashboards state whether values are cash, invoices, or recurring run rate.