Subscription Monitoring

View active subscription counts and plan distribution for basic financial oversight.

Overview

Subscription Monitoring provides finance teams with real-time visibility into active subscription counts and plan distribution. This feature queries the OLTP database for simple subscription counts, while detailed revenue analytics are available in Stripe Dashboard.

[!IMPORTANT] Stripe-First Approach: This feature provides basic counts only. For MRR, ARR, churn rates, and revenue analytics, use Stripe Dashboard directly.

Key Capabilities

Active Subscription Count: Real-time count of active subscriptions
Plan Distribution: Breakdown of subscriptions by plan tier (Starter, Pro, Enterprise)
Stripe Dashboard Link: Quick access to detailed revenue analytics in Stripe
Simple Queries: Fast, read-only queries against OLTP database

User Story

Finance Admin

“As a finance admin, I need to check our active subscription count and access Stripe for revenue details.”

Workflow

Access Finance Dashboard
- Navigate to /dashboard/finance
- View subscription summary panel
Review Basic Metrics
- Total Active Subscriptions: e.g., 1,247 active
- Plan Distribution:
  - Starter: 823 subscriptions
  - Pro: 389 subscriptions
  - Enterprise: 35 subscriptions
Access Detailed Analytics
- Click “View MRR & Revenue in Stripe” button
- Opens Stripe Dashboard in new tab
- Access detailed revenue reports, MRR trends, churn analysis
Export Data
- In Stripe: Export revenue data (CSV, PDF)
- Import into board presentation or accounting system

Subscription Summary Display

See Analytics UI Patterns for visual design.

Metrics Displayed

Metric	Description	Data Source
Active Subscriptions	Total count of active subscriptions	OLTP `subscriptions` table (`status = 'active'`)
Plan Distribution	Subscriptions grouped by plan tier	GROUP BY `plan_id`
Last Updated	Timestamp of last database query	Real-time

Not Displayed (Available in Stripe)

The following metrics require complex calculations and should be accessed via Stripe Dashboard:

MRR (Monthly Recurring Revenue): Real-time MRR calculations with proration
ARR (Annual Recurring Revenue): Annualized revenue projections
Churn Rate: Customer and revenue churn percentages
LTV (Lifetime Value): Customer lifetime value by cohort
Revenue Growth: Month-over-month and year-over-year trends
Cohort Analysis: Subscription retention by signup month

Technical Implementation

Query Logic

Simple Count Query:

SELECT 
  COUNT(*) as active_count
FROM subscriptions
WHERE status = 'active';

Plan Distribution Query:

SELECT 
  plans.name,
  COUNT(*) as subscription_count
FROM subscriptions
JOIN plans ON subscriptions.plan_id = plans.id
WHERE subscriptions.status = 'active'
GROUP BY plans.id, plans.name
ORDER BY subscription_count DESC;

Performance Characteristics

Query Time: <50ms (simple COUNT with indexed status column)
Dashboard Load: <200ms total (includes all finance dashboard metrics)
Caching: Results cached for 5 minutes to reduce database load
Real-Time: Reflects subscription changes within 5 minutes

Database Schema

Tables Used:

subscriptions: Current subscription state (OLTP)
- id, tenant_id, plan_id, status, stripe_subscription_id
plans: Plan definitions
- id, name, stripe_product_id

Status Values:

active: Subscription is current and paid
past_due: Payment failed, grace period active
canceled: Subscription canceled by customer
trialing: In trial period

Stripe Dashboard Integration

Quick Links Provided

From the subscription monitoring panel, finance admins can access:

View MRR Analytics: Direct link to Stripe Revenue section
View Customer List: Stripe Customers page filtered by active subscriptions
Export Data: Stripe Data Export tools

What’s Available in Stripe

Revenue Analytics:

Real-time MRR and ARR dashboards
Revenue growth charts (daily, weekly, monthly)
Breakdown by plan, geography, and customer segment

Customer Insights:

Customer cohort analysis
Retention rates by signup month
Average revenue per customer (ARPU)

Export Capabilities:

CSV exports with custom date ranges
Automated reports via email
SQL access via Stripe Sigma (paid add-on)

Best Practices

When to Use Subscription Monitoring

Quick Checks: “How many active subscriptions do we have?”
Plan Distribution: “Which plan tier is most popular?”
Board Reporting: “Total customer count for board deck”

When to Use Stripe Dashboard

Revenue Analysis: “What’s our MRR growth rate this quarter?”
Churn Analysis: “What’s our monthly churn rate?”
Financial Forecasting: “Projected ARR for next quarter”
Accounting Reconciliation: “Download all invoices for Q4”

Data Accuracy

Subscription Counts: Real-time from OLTP database
Stripe as Source of Truth: Payment and revenue data always authoritative in Stripe
Webhook Sync: Subscription status updated via Stripe webhooks
Reconciliation: If discrepancies exist, trust Stripe data

Common Scenarios

Scenario: Monthly Board Report

Goal: Prepare subscription metrics for monthly board presentation

Workflow:

Access finance dashboard
Note total active subscriptions: 1,247
Note plan distribution: Starter (66%), Pro (31%), Enterprise (3%)
Click “View MRR in Stripe”
Export MRR trend chart from Stripe
Include both subscription count and MRR trend in board deck

Scenario: Plan Performance Analysis

Goal: Determine which plan tier has best retention