metrics

name	metrics
description	Calculate business metrics from Razorpay data — MRR, churn rate, ARPU, LTV, revenue trends. Use when the user asks "what's my MRR", "show churn rate", "build a revenue dashboard", "calculate ARPU", or needs to track SaaS business health from payment data.
argument-hint	[mrr|churn|revenue|dashboard]

Razorpay Business Metrics

Calculate key SaaS metrics from your Razorpay subscription and payment data. Includes both database queries (fast, for dashboards) and API-based approaches (accurate, for reconciliation).

MRR (Monthly Recurring Revenue)

Database Approach (Fast)

// lib/metrics/mrr.ts
import { db } from "@/lib/db";

interface MRRResult {
  mrr: number;          // in paise
  mrrDisplay: string;   // formatted in rupees
  activeCount: number;
  breakdown: { planKey: string; count: number; mrr: number }[];
}

export async function calculateMRR(): Promise<MRRResult> {
  // Query active subscriptions with plan details
  const subscriptions = await db.subscription.findMany({
    where: { status: "active" },
    select: {
      planKey: true,
      amount: true,       // amount in paise
      period: true,        // "monthly" | "yearly"
    },
  });

  let totalMRR = 0;
  const planMap = new Map<string, { count: number; mrr: number }>();

  for (const sub of subscriptions) {
    // Convert yearly to monthly contribution
    const monthlyAmount = sub.period === "yearly"
      ? Math.round(sub.amount / 12)
      : sub.amount;

    totalMRR += monthlyAmount;

    const existing = planMap.get(sub.planKey) || { count: 0, mrr: 0 };
    planMap.set(sub.planKey, {
      count: existing.count + 1,
      mrr: existing.mrr + monthlyAmount,
    });
  }

  const breakdown = Array.from(planMap.entries()).map(([planKey, data]) => ({
    planKey,
    ...data,
  }));

  return {
    mrr: totalMRR,
    mrrDisplay: `₹${(totalMRR / 100).toLocaleString("en-IN")}`,
    activeCount: subscriptions.length,
    breakdown,
  };
}

Razorpay API Approach (Accurate)

// lib/metrics/mrr-api.ts
import { razorpay } from "@/lib/razorpay";

export async function calculateMRRFromAPI(): Promise<number> {
  let totalMRR = 0;
  let skip = 0;
  const count = 100; // max per request

  // Fetch all plans first to know their periods
  const plansRes = await razorpay.plans.all({ count: 100 });
  const planPeriods = new Map<string, string>();
  for (const plan of plansRes.items) {
    planPeriods.set(plan.id, plan.period); // "monthly" | "yearly" | "weekly" | "daily"
  }

  // Paginate through all active subscriptions
  while (true) {
    const res = await razorpay.subscriptions.all({ count, skip });
    const active = res.items.filter((s: any) => s.status === "active");

    for (const sub of active) {
      const plan = await razorpay.plans.fetch(sub.plan_id);
      const amount = plan.item.amount * (sub.quantity || 1);

      // Normalize to monthly
      const period = planPeriods.get(sub.plan_id) || plan.period;
      if (period === "yearly") {
        totalMRR += Math.round(amount / 12);
      } else if (period === "weekly") {
        totalMRR += Math.round(amount * 4.33);
      } else if (period === "daily") {
        totalMRR += Math.round(amount * 30);
      } else {
        totalMRR += amount; // monthly
      }
    }

    if (res.items.length < count) break;
    skip += count;
  }

  return totalMRR; // in paise
}

---

Churn Rate

// lib/metrics/churn.ts
import { db } from "@/lib/db";

interface ChurnResult {
  churnRate: number;             // percentage
  cancelledCount: number;
  activeAtStart: number;
  voluntary: number;             // user-initiated cancellation
  involuntary: number;           // payment failed / halted
}

export async function calculateChurnRate(days: number = 30): Promise<ChurnResult> {
  const periodStart = new Date();
  periodStart.setDate(periodStart.getDate() - days);

  // Active subscriptions at start of period
  // (currently active + cancelled during period = what was active at start)
  const activeNow = await db.subscription.count({
    where: { status: "active" },
  });

  const cancelledInPeriod = await db.subscription.findMany({
    where: {
      status: { in: ["cancelled", "halted"] },
      cancelledAt: { gte: periodStart },
    },
    select: {
      status: true,
      cancelReason: true,  // store this when processing cancel/halt webhooks
    },
  });

  const cancelledCount = cancelledInPeriod.length;
  const activeAtStart = activeNow + cancelledCount;

  // Distinguish voluntary vs involuntary
  // "halted" status = payment failure (involuntary)
  // "cancelled" with user action = voluntary
  const involuntary = cancelledInPeriod.filter(
    (s) => s.status === "halted" || s.cancelReason === "payment_failed"
  ).length;
  const voluntary = cancelledCount - involuntary;

  const churnRate = activeAtStart > 0
    ? (cancelledCount / activeAtStart) * 100
    : 0;

  return {
    churnRate: Math.round(churnRate * 100) / 100,
    cancelledCount,
    activeAtStart,
    voluntary,
    involuntary,
  };
}

---

ARPU (Average Revenue Per User)

// lib/metrics/arpu.ts
import { db } from "@/lib/db";

interface ARPUResult {
  arpu: number;            // in paise
  arpuDisplay: string;     // formatted in rupees
  totalRevenue: number;    // in paise
  payingUsers: number;
}

export async function calculateARPU(days: number = 30): Promise<ARPUResult> {
  const since = new Date();
  since.setDate(since.getDate() - days);

  // Sum captured payments, excluding refunded ones
  const payments = await db.payment.aggregate({
    where: {
      status: "captured",
      refundStatus: { not: "full" },  // exclude fully refunded
      createdAt: { gte: since },
    },
    _sum: { amount: true },
  });

  // Count distinct paying users
  const payingUsers = await db.payment.groupBy({
    by: ["userId"],
    where: {
      status: "captured",
      createdAt: { gte: since },
    },
  });

  const totalRevenue = payments._sum.amount || 0;
  const userCount = payingUsers.length;
  const arpu = userCount > 0 ? Math.round(totalRevenue / userCount) : 0;

  return {
    arpu,
    arpuDisplay: `₹${(arpu / 100).toLocaleString("en-IN")}`,
    totalRevenue,
    payingUsers: userCount,
  };
}

ARPU from Razorpay API

// lib/metrics/arpu-api.ts
import { razorpay } from "@/lib/razorpay";

export async function calculateARPUFromAPI(days: number = 30): Promise<number> {
  const since = Math.floor(Date.now() / 1000) - days * 86400;
  let totalRevenue = 0;
  const uniqueEmails = new Set<string>();
  let skip = 0;

  while (true) {
    const res = await razorpay.payments.all({
      count: 100,
      skip,
      from: since,
    });

    for (const p of res.items) {
      // Only count captured, non-refunded payments
      if (p.status === "captured" && p.amount_refunded === 0) {
        totalRevenue += p.amount;
        if (p.email) uniqueEmails.add(p.email);
      }
    }

    if (res.items.length < 100) break;
    skip += 100;
  }

  return uniqueEmails.size > 0
    ? Math.round(totalRevenue / uniqueEmails.size)
    : 0; // in paise
}

---

Revenue Dashboard API Route

Single endpoint returning all key metrics. Use the DB approach for speed in dashboards.

// app/api/billing/metrics/route.ts
import { calculateMRR } from "@/lib/metrics/mrr";
import { calculateChurnRate } from "@/lib/metrics/churn";
import { calculateARPU } from "@/lib/metrics/arpu";
import { db } from "@/lib/db";

export async function GET(request: Request) {
  const user = await getAuthenticatedUser(request);
  if (!user?.isAdmin) {
    return Response.json({ error: "Unauthorized" }, { status: 401 });
  }

  const thirtyDaysAgo = new Date();
  thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);

  // Run all queries in parallel
  const [mrr, churn, arpu, newSubs, cancelledSubs, totalRevenue] = await Promise.all([
    calculateMRR(),
    calculateChurnRate(30),
    calculateARPU(30),

    // New subscribers in last 30 days
    db.subscription.count({
      where: {
        status: "active",
        createdAt: { gte: thirtyDaysAgo },
      },
    }),

    // Cancelled in last 30 days
    db.subscription.count({
      where: {
        status: { in: ["cancelled", "halted"] },
        cancelledAt: { gte: thirtyDaysAgo },
      },
    }),

    // Total captured revenue in last 30 days (paise)
    db.payment.aggregate({
      where: {
        status: "captured",
        refundStatus: { not: "full" },
        createdAt: { gte: thirtyDaysAgo },
      },
      _sum: { amount: true },
    }),
  ]);

  const revenue = totalRevenue._sum.amount || 0;

  return Response.json({
    mrr: {
      amount: mrr.mrr,
      display: mrr.mrrDisplay,
      activeSubscribers: mrr.activeCount,
      breakdown: mrr.breakdown,
    },
    churn: {
      rate: churn.churnRate,
      cancelled: churn.cancelledCount,
      voluntary: churn.voluntary,
      involuntary: churn.involuntary,
    },
    arpu: {
      amount: arpu.arpu,
      display: arpu.arpuDisplay,
      payingUsers: arpu.payingUsers,
    },
    revenue30d: {
      amount: revenue,
      display: `₹${(revenue / 100).toLocaleString("en-IN")}`,
    },
    subscribers: {
      active: mrr.activeCount,
      new30d: newSubs,
      cancelled30d: cancelledSubs,
    },
    // LTV estimate: ARPU / monthly churn rate
    ltv: churn.churnRate > 0
      ? {
          amount: Math.round(arpu.arpu / (churn.churnRate / 100)),
          display: `₹${(Math.round(arpu.arpu / (churn.churnRate / 100)) / 100).toLocaleString("en-IN")}`,
        }
      : { amount: null, display: "N/A (no churn)" },
    generatedAt: new Date().toISOString(),
  });
}

API-Based Dashboard (Accurate, Slower)

For reconciliation or when you do not have a local database:

// app/api/billing/metrics/api-based/route.ts
import { razorpay } from "@/lib/razorpay";

export async function GET(request: Request) {
  const user = await getAuthenticatedUser(request);
  if (!user?.isAdmin) {
    return Response.json({ error: "Unauthorized" }, { status: 401 });
  }

  const thirtyDaysAgo = Math.floor(Date.now() / 1000) - 30 * 86400;

  // Fetch all subscriptions (paginated)
  const allSubs: any[] = [];
  let skip = 0;
  while (true) {
    const res = await razorpay.subscriptions.all({ count: 100, skip });
    allSubs.push(...res.items);
    if (res.items.length < 100) break;
    skip += 100;
  }

  const active = allSubs.filter((s) => s.status === "active");
  const cancelledRecent = allSubs.filter(
    (s) =>
      ["cancelled", "halted"].includes(s.status) &&
      s.ended_at && s.ended_at >= thirtyDaysAgo
  );

  // Fetch plans for MRR calculation
  const planCache = new Map<string, any>();
  for (const sub of active) {
    if (!planCache.has(sub.plan_id)) {
      planCache.set(sub.plan_id, await razorpay.plans.fetch(sub.plan_id));
    }
  }

  // Calculate MRR
  let mrr = 0;
  for (const sub of active) {
    const plan = planCache.get(sub.plan_id);
    const amount = plan.item.amount * (sub.quantity || 1);
    mrr += plan.period === "yearly" ? Math.round(amount / 12) : amount;
  }

  // Fetch recent payments for revenue
  let totalRevenue = 0;
  let payerEmails = new Set<string>();
  skip = 0;
  while (true) {
    const res = await razorpay.payments.all({ count: 100, skip, from: thirtyDaysAgo });
    for (const p of res.items) {
      if (p.status === "captured" && p.amount_refunded === 0) {
        totalRevenue += p.amount;
        if (p.email) payerEmails.add(p.email);
      }
    }
    if (res.items.length < 100) break;
    skip += 100;
  }

  const churnRate = (active.length + cancelledRecent.length) > 0
    ? (cancelledRecent.length / (active.length + cancelledRecent.length)) * 100
    : 0;

  const arpu = payerEmails.size > 0
    ? Math.round(totalRevenue / payerEmails.size)
    : 0;

  return Response.json({
    mrr: { amount: mrr, display: `₹${(mrr / 100).toLocaleString("en-IN")}` },
    activeSubscribers: active.length,
    churnRate: Math.round(churnRate * 100) / 100,
    cancelledLast30d: cancelledRecent.length,
    revenue30d: { amount: totalRevenue, display: `₹${(totalRevenue / 100).toLocaleString("en-IN")}` },
    arpu: { amount: arpu, display: `₹${(arpu / 100).toLocaleString("en-IN")}` },
    payingUsers: payerEmails.size,
    generatedAt: new Date().toISOString(),
    source: "razorpay-api",
  });
}

---

Revenue by Plan Breakdown

// lib/metrics/revenue-by-plan.ts
import { db } from "@/lib/db";

interface PlanRevenue {
  planKey: string;
  activeCount: number;
  mrr: number;             // monthly contribution in paise
  mrrDisplay: string;
  revenueShare: number;    // percentage of total MRR
}

export async function revenueByPlan(): Promise<PlanRevenue[]> {
  const subscriptions = await db.subscription.findMany({
    where: { status: "active" },
    select: {
      planKey: true,
      amount: true,
      period: true,
    },
  });

  const planMap = new Map<string, { count: number; mrr: number }>();

  for (const sub of subscriptions) {
    const monthlyAmount = sub.period === "yearly"
      ? Math.round(sub.amount / 12)
      : sub.amount;

    const existing = planMap.get(sub.planKey) || { count: 0, mrr: 0 };
    planMap.set(sub.planKey, {
      count: existing.count + 1,
      mrr: existing.mrr + monthlyAmount,
    });
  }

  const totalMRR = Array.from(planMap.values()).reduce((sum, p) => sum + p.mrr, 0);

  return Array.from(planMap.entries())
    .map(([planKey, data]) => ({
      planKey,
      activeCount: data.count,
      mrr: data.mrr,
      mrrDisplay: `₹${(data.mrr / 100).toLocaleString("en-IN")}`,
      revenueShare: totalMRR > 0
        ? Math.round((data.mrr / totalMRR) * 10000) / 100
        : 0,
    }))
    .sort((a, b) => b.mrr - a.mrr);
}

---

Curl Commands for Quick Checks

Before running: Ensure RAZORPAY_KEY_ID and RAZORPAY_KEY_SECRET are set in your environment or .env.local.

# Load env vars if using .env.local
export RAZORPAY_KEY_ID=$(grep RAZORPAY_KEY_ID .env.local | head -1 | cut -d'=' -f2)
export RAZORPAY_KEY_SECRET=$(grep RAZORPAY_KEY_SECRET .env.local | head -1 | cut -d'=' -f2)

Count active subscriptions

curl -s -u $RAZORPAY_KEY_ID:$RAZORPAY_KEY_SECRET \
  "https://api.razorpay.com/v1/subscriptions?count=100" \
  | jq '[.items[] | select(.status == "active")] | length'

Sum recent captured payment amounts (last 30 days)

# Calculate timestamp for 30 days ago
SINCE=$(date -v-30d +%s 2>/dev/null || date -d "30 days ago" +%s)

curl -s -u $RAZORPAY_KEY_ID:$RAZORPAY_KEY_SECRET \
  "https://api.razorpay.com/v1/payments?count=100&from=$SINCE" \
  | jq '[.items[] | select(.status == "captured" and .amount_refunded == 0) | .amount] | add // 0 | . / 100 | "₹\(.)"'

List churned subscriptions this month

curl -s -u $RAZORPAY_KEY_ID:$RAZORPAY_KEY_SECRET \
  "https://api.razorpay.com/v1/subscriptions?count=100" \
  | jq --arg since "$SINCE" '.items[] | select((.status == "cancelled" or .status == "halted") and (.ended_at // 0 | tostring > $since)) | {id, status, plan_id, ended_at}'

Quick MRR estimate from API

# Fetch active subs and their plan amounts
curl -s -u $RAZORPAY_KEY_ID:$RAZORPAY_KEY_SECRET \
  "https://api.razorpay.com/v1/subscriptions?count=100" \
  | jq '[.items[] | select(.status == "active")] | length as $count | "Active subscriptions: \($count)"'

Revenue per plan

curl -s -u $RAZORPAY_KEY_ID:$RAZORPAY_KEY_SECRET \
  "https://api.razorpay.com/v1/subscriptions?count=100" \
  | jq '[.items[] | select(.status == "active")] | group_by(.plan_id) | .[] | {plan_id: .[0].plan_id, count: length}'

---

Gotchas

1. Amounts are in paise: Razorpay stores all amounts in paise (1/100 of a rupee). Always divide by 100 for display: amount / 100.
2. Do not count pre-active statuses as revenue: Subscriptions with status authenticated or created have not been charged yet. Only active subscriptions contribute to MRR.
3. Yearly subscriptions and MRR: Divide yearly plan amounts by 12 to get monthly revenue contribution. Do not count the full yearly amount as MRR.
4. Exclude refunded payments: Payments with amount_refunded > 0 should be partially or fully excluded from revenue calculations. Check amount - amount_refunded for net revenue.
5. API pagination: Razorpay returns max 100 items per request. Use skip parameter to paginate: ?count=100&skip=100. Always loop until items.length < count.
6. Halted vs cancelled: halted means payment failed (involuntary churn). cancelled means explicit cancellation (could be voluntary or admin-initiated). Track both but report them separately for actionable insights.
7. Timestamp format: Razorpay API uses Unix timestamps in seconds. Multiply by 1000 for JavaScript Date: new Date(timestamp * 1000).