You have customers paying you recurring fees through Stripe, but getting a clear picture of your MRR requires piecing together subscription data. Stripe doesn't surface this out of the box—you need to query the Subscriptions API, use Sigma for SQL-based reporting, or build a webhook handler to track changes in real time.
Extract MRR from the Subscriptions API
The simplest approach is to pull all active subscriptions and sum their recurring amounts.
List all active subscriptions
Use the Subscriptions endpoint to fetch all active subscriptions. Filter by status to get only the ones currently generating revenue.
const stripe = require('stripe')('sk_test_...');
const subscriptions = await stripe.subscriptions.list({
status: 'active',
limit: 100,
});Sum recurring amounts across all subscriptions
Iterate through each subscription and add up the amounts for recurring items only. Divide by 100 because Stripe stores amounts in cents.
let mrr = 0;
subscriptions.data.forEach(subscription => {
subscription.items.data.forEach(item => {
if (item.price.recurring) {
const monthlyAmount = item.price.unit_amount / 100;
mrr += monthlyAmount;
}
});
});
console.log(`MRR: $${mrr.toFixed(2)}`);Handle pagination for large customer bases
If you have more than 100 subscriptions, use the starting_after parameter to paginate through results.
let allSubscriptions = [];
let hasMore = true;
let startingAfter = null;
while (hasMore) {
const page = await stripe.subscriptions.list({
status: 'active',
limit: 100,
starting_after: startingAfter,
});
allSubscriptions = allSubscriptions.concat(page.data);
hasMore = page.has_more;
if (hasMore) startingAfter = page.data[page.data.length - 1].id;
}Use Stripe Sigma for automated MRR queries
Stripe Sigma lets you write SQL against your actual Stripe data without exporting anything. It's the best way to get historical MRR trends and filter by plan, customer, or creation date.
Navigate to Sigma in the Stripe Dashboard
In your Stripe Dashboard, go to Reports > Sigma. Click Create a new report to open the SQL editor.
Write a query to calculate monthly MRR
Query the subscriptions table and sum the amount for all active subscriptions grouped by month.
SELECT
DATE_TRUNC('month', created_at) as month,
SUM(amount) / 100.0 as mrr
FROM subscriptions
WHERE status = 'active'
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;Segment MRR by plan to identify revenue drivers
Join with the prices table to break down MRR by billing plan. This shows which plans contribute most to your recurring revenue.
SELECT
p.nickname as plan_name,
COUNT(s.id) as subscriber_count,
SUM(s.amount) / 100.0 as plan_mrr
FROM subscriptions s
JOIN prices p ON s.items_data[0].price_id = p.id
WHERE s.status = 'active'
GROUP BY p.nickname
ORDER BY plan_mrr DESC;Track MRR changes with webhooks
For real-time MRR updates, listen to subscription events and recalculate whenever a customer upgrades, downgrades, or cancels.
Set up a webhook endpoint
Go to Developers > Webhooks in your Stripe Dashboard and add an endpoint URL. Enable the events: customer.subscription.created, customer.subscription.updated, and customer.subscription.deleted.
Listen for subscription changes and update MRR
In your webhook handler, catch subscription events and recalculate your MRR total. Store it in your database or cache.
const express = require('express');
const app = express();
app.post('/webhook', express.json(), (req, res) => {
const event = req.body;
if (event.type === 'customer.subscription.updated' ||
event.type === 'customer.subscription.created' ||
event.type === 'customer.subscription.deleted') {
const subscription = event.data.object;
// Recalculate MRR and update database
updateMRRInDatabase(subscription);
}
res.json({received: true});
});Verify webhook signatures
Always verify that the webhook came from Stripe by checking the signature in the Stripe-Signature header. Use stripe.webhooks.constructEvent().
const endpointSecret = 'whsec_...';
const sig = req.headers['stripe-signature'];
let event;
try {
event = stripe.webhooks.constructEvent(req.body, sig, endpointSecret);
} catch (err) {
console.log(`Webhook signature verification failed: ${err.message}`);
return res.sendStatus(400);
}Common Pitfalls
- Including one-time charges in MRR—only count items where
price.recurringis true; one-time products haverecurring: null - Not filtering by subscription status—canceled, paused, or incomplete subscriptions still appear in API results; explicitly filter for
status: 'active' - Using invoice amounts instead of subscription amounts—invoice totals include one-time fees, prorations, and adjustments; sum subscription items for pure recurring revenue
- Forgetting about billing cycles—some customers may be on annual plans; decide whether to annualize those or count only active monthly billing as MRR
Wrapping Up
You now have three ways to surface MRR from Stripe: query the API for a snapshot, use Sigma for flexible SQL reports, or track real-time changes with webhooks. Start with Sigma if you want quick historical reporting, or webhooks if you need live updates. If you want to track this automatically across tools—Stripe, PayPal, Zuora, and more—Product Analyst can help.