How to track MRR in Google Sheets for a small SaaS

by

TL;DR

You can build a fully functional MRR tracker in Google Sheets using SUMIFS formulas, a raw subscription log, and a monthly summary tab. The initial setup takes about two hours, and keeping it updated after that takes five minutes a week. You need a Google account, your billing export from Stripe or your payment processor, and a clear definition of what counts as an active subscription.

What You Need Before You Start

  • A Google account with access to Google Sheets (free tier is fine)
  • A billing export or CSV from your payment processor, subscription management tool, or CRM
  • A consistent definition of “active”: decide upfront whether trials, paused accounts, and annual plans billed monthly count toward MRR
  • Your pricing tiers clearly named and documented (e.g., Starter $49/mo, Pro $99/mo, Scale $299/mo)
  • Optional: Zapier free plan for automating data pulls from Stripe, or a Google Apps Script if you prefer to stay inside Sheets
  • Optional: a paid tool like ChartMogul or Baremetrics as your eventual migration target once you outgrow Sheets
  • Basic familiarity with Google Sheets formulas (SUMIFS, ARRAYFORMULA, IFERROR)

If you want to understand the difference between MRR and ARR before you start building, read the MRR vs ARR explained guide first.

Step 1: Create Your Raw Subscription Data Tab

Open a new Google Sheets file and rename the first tab raw_subscriptions. This tab is your single source of truth. Every row represents one subscription.

Add these exact column headers in row 1:

A B C D E F G H
customer_id customer_name plan_name mrr_amount currency status start_date end_date

Fill in each column for every customer you have right now. For annual plans, divide the annual contract value by 12 and enter that as mrr_amount. Status values should be exactly one of: active, paused, churned, or trial. Keep currency as a three-letter ISO code (USD, EUR, GBP).

You should now see a clean table with one row per customer, no merged cells, no blank header rows, and consistent status values throughout.

Step 2: Add a Month Column and Normalize Dates

In column I, add the header mrr_month. Then in I2, enter:

=IF(H2="",TEXT(G2,"YYYY-MM"),IF(A2="","",TEXT(G2,"YYYY-MM")))

This is a placeholder for now. What you actually want is a lookup that tells you which months a given subscription was active. For a simpler approach that works for most early-stage SaaS companies, add a second tab called month_map.

In month_map, create a column of monthly dates going back 24 months and forward 12 months. In A1, enter your earliest customer start date rounded to the first of the month. Then in A2:

=EDATE(A1,1)

Drag that formula down to row 37 to get 36 months of data. Format column A as YYYY-MM using Format > Number > Custom date and time.

You should now see a clean list of 36 months, one per row, formatted consistently with your raw data dates.

Step 3: Write the Core MRR Formula with SUMIFS

Create a third tab called mrr_summary. This is where your headline numbers live.

In column A, paste or reference your 36 months from month_map. In column B, add the header total_mrr. In B2, enter this formula:

=SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"active",
  raw_subscriptions!$G:$G,"<="&DATE(YEAR(DATEVALUE(A2&"-01")),MONTH(DATEVALUE(A2&"-01"))+1,0),
  raw_subscriptions!$H:$H,">="&DATEVALUE(A2&"-01")
)

This pulls the sum of mrr_amount for all rows where the subscription was active and the date range overlaps the target month. Drag it down for all 36 rows.

For customers still active (no end date), you need to handle the blank end_date. Wrap the end_date part like this:

=SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"active",
  raw_subscriptions!$G:$G,"<="&EOMONTH(DATEVALUE(A2&"-01"),0),
  raw_subscriptions!$H:$H,">="&DATEVALUE(A2&"-01")
) + SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"active",
  raw_subscriptions!$G:$G,"<="&EOMONTH(DATEVALUE(A2&"-01"),0),
  raw_subscriptions!$H:$H,""
)

You should now see a monthly MRR figure for each month in your list that actually reflects your subscription data.

Step 4: Track MRR Movements (New, Expansion, Contraction, Churn)

Total MRR alone does not tell you enough. You need to see where growth is coming from and where it is leaking. Add four more columns to mrr_summary:

  • new_mrr: revenue from brand-new customers this month
  • expansion_mrr: revenue from existing customers who upgraded
  • contraction_mrr: revenue lost from downgrades (show as negative)
  • churned_mrr: revenue lost from cancellations (show as negative)

For new_mrr, in C2:

=SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"active",
  raw_subscriptions!$G:$G,">="&DATEVALUE(A2&"-01"),
  raw_subscriptions!$G:$G,"<="&EOMONTH(DATEVALUE(A2&"-01"),0)
)

For churned_mrr, you need to track customers whose end_date falls in the target month. In E2:

=-SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"churned",
  raw_subscriptions!$H:$H,">="&DATEVALUE(A2&"-01"),
  raw_subscriptions!$H:$H,"<="&EOMONTH(DATEVALUE(A2&"-01"),0)
)

Expansion and contraction require a previous_mrr column in your raw data for each customer, which you add manually or via script whenever a plan change happens. This is the part where the manual spreadsheet approach starts to show its limits.

You should now see a breakdown of MRR movements per month, which lets you diagnose growth problems much faster than looking at total MRR alone.

Step 5: Calculate Net MRR Growth and Churn Rate

Add two more columns to mrr_summary.

net_mrr_growth in F2:

=B2-B1+C2+D2+E2

This is: ending MRR minus starting MRR, which should equal new plus expansion minus contraction minus churn. If your numbers do not reconcile, you have data gaps.

monthly_churn_rate in G2:

=IFERROR(ABS(E2)/B1,"")

This gives you revenue churn rate, not customer churn rate. For most SaaS businesses, revenue churn is the more important number because it weights high-value customers appropriately.

Add a helper column H labeled net_revenue_retention for any month where you have enough history:

=IFERROR((B1+D2+E2)/B1,"")

NRR above 100% means your existing customer base is growing on its own, which is a very healthy sign.

You should now see a full MRR waterfall in tabular form across all 36 months with growth rate and churn rate calculated automatically.

Step 6: Build a Plan-Level Breakdown Tab

Create a fourth tab called by_plan. Here you repeat the SUMIFS logic from Step 3, but filter by plan_name as well. This tells you which tier is growing and which is churning fastest.

In by_plan, set up columns for each plan name across the top (B, C, D) and months down the left (A). In B2:

=SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"active",
  raw_subscriptions!$E:$E,"Starter",
  raw_subscriptions!$G:$G,"<="&EOMONTH(DATEVALUE(A2&"-01"),0),
  raw_subscriptions!$H:$H,">="&DATEVALUE(A2&"-01")
)+SUMIFS(
  raw_subscriptions!$D:$D,
  raw_subscriptions!$F:$F,"active",
  raw_subscriptions!$E:$E,"Starter",
  raw_subscriptions!$G:$G,"<="&EOMONTH(DATEVALUE(A2&"-01"),0),
  raw_subscriptions!$H:$H,""
)

Repeat for each plan. You will start to see clearly if one plan segment is disproportionately churning, or if one tier is where all expansion is happening.

You should now see a grid of MRR per plan per month that makes it obvious which parts of your pricing are working.

Step 7: Add a Visual Dashboard Tab

Create a fifth tab called dashboard. Select the monthly total_mrr column from mrr_summary, then Insert > Chart. Choose a line chart and set the month column as the X-axis.

Add a second chart for your MRR movement waterfall. Highlight the new_mrr, expansion_mrr, contraction_mrr, and churned_mrr columns, then choose a stacked bar chart type.

Pin four summary cells at the top of the dashboard using simple references:

=mrr_summary!B37         ← current month MRR
=mrr_summary!G37         ← current churn rate
=mrr_summary!H37         ← NRR
=COUNTIFS(raw_subscriptions!F:F,"active")  ← active customer count

Format these with large fonts and conditional formatting (red if churn rate is above 5%, green if NRR is above 100%).

You should now see a one-page view that gives you an MRR snapshot the moment you open the file.

Step 8: Automate the Data Import

Manual data entry breaks down fast. Set up an automation to push new subscription events into your raw_subscriptions tab without you touching it.

If you use Stripe, create a Zapier zap: Trigger on “Customer Subscription Updated” in Stripe, then action “Create Spreadsheet Row” in Google Sheets. Map the Stripe fields to your column structure from Step 1.

If you prefer a code-based approach, add this Google Apps Script under Extensions > Apps Script:

function importStripeSubscriptions() {
  const apiKey = PropertiesService.getScriptProperties().getProperty('STRIPE_KEY');
  const url = 'https://api.stripe.com/v1/subscriptions?limit=100&status=all';
  const options = {
    headers: { Authorization: 'Basic ' + Utilities.base64Encode(apiKey + ':') }
  };
  const response = UrlFetchApp.fetch(url, options);
  const data = JSON.parse(response.getContentText());
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('raw_subscriptions');
  data.data.forEach(sub => {
    sheet.appendRow([
      sub.customer,
      sub.metadata.name || '',
      sub.items.data[0].price.nickname,
      sub.items.data[0].price.unit_amount / 100,
      sub.currency.toUpperCase(),
      sub.status,
      new Date(sub.start_date * 1000),
      sub.canceled_at ? new Date(sub.canceled_at * 1000) : ''
    ]);
  });
}

Store your Stripe secret key under Project Settings > Script Properties. Add a daily trigger under Triggers to run this function automatically every morning.

You should now see your raw_subscriptions tab updating automatically, which means your MRR dashboard refreshes without any manual intervention.

Common Mistakes To Avoid

  • Counting annual plans at full contract value. Divide by 12. MRR means monthly recurring revenue, not invoice amount.
  • Leaving status values inconsistent. “Cancelled”, “canceled”, and “churned” are three different strings to SUMIFS. Standardize to a controlled vocabulary from day one.
  • Ignoring paused subscriptions. If a customer pauses for two months and you still count them as active, your churn rate looks better than it is and your MRR is overstated.
  • Not reconciling your Sheets MRR against your payment processor. Run a spot check monthly. If your Sheets number is more than 2% off from what Stripe reports, you have a data gap.
  • Mixing currencies without conversion. If you have USD and EUR customers, pick a base currency and apply a conversion rate. Mixing raw amounts produces nonsense totals.
  • Counting trial users as active. Trials are pipeline, not revenue. Keep them in the data but filter them out of all MRR calculations.

When To Level Up

The Sheets setup described here works well up to roughly 200 active subscriptions and 3-4 pricing tiers. Past that point, three problems appear.

First, manual deduplication becomes painful. When a customer upgrades, downgrades, pauses, and re-activates in the same month, tracking that in a flat spreadsheet requires multiple rows and careful flag logic that breaks when you miss one event.

Second, historical accuracy degrades. Sheets does not have an immutable event log. If someone edits a past row, your historical MRR silently changes and you will not notice until a chart looks wrong.

Third, investor and board reporting requires standardized metrics with audit trails. A spreadsheet you built yourself does not give auditors or investors confidence in the numbers.

At that point, move to a dedicated MRR tool. ChartMogul, Baremetrics, and ProfitWell all connect directly to Stripe or Paddle and handle the movement calculations automatically. The data analysis tools category on this site covers all three in depth, including which one makes sense at different ARR stages. You can also look at the spreadsheet templates for SaaS metrics guide if you want to extend your Sheets setup before making that jump.

Frequently Asked Questions

What is MRR and how is it different from revenue?
MRR is the predictable, recurring portion of your revenue normalized to a monthly figure. If a customer pays $1,200 per year, that is $100 MRR, not $1,200 in revenue. Cash revenue is what hits your bank account; MRR is what your business can count on every month going forward.

How do I handle annual plans in my MRR calculation?
Divide the annual contract value by 12 and enter that as the monthly MRR amount. Do not count the full annual payment as MRR in the month it was received. The goal is to represent recurring run rate, not cash collection timing.

Can I use Google Sheets for MRR if I have multiple currencies?
Yes, but you need to add a conversion step. Create a small exchange rate table in a separate tab, then multiply each customer’s MRR by the appropriate conversion factor before summing. Update the rates at least monthly, or pull live rates via a Google Apps Script call to an FX API.

How often should I update the spreadsheet?
Weekly at minimum, daily if your churn is high or you are in a growth sprint and need fast feedback. The Zapier or Apps Script automation in Step 8 makes daily updates essentially free once it is set up.

What is a healthy monthly churn rate for a SaaS?
For SMB-focused SaaS, monthly revenue churn below 2% is strong. For mid-market or enterprise, anything above 1% is worth investigating. The more important number is net revenue retention: above 100% means expansion is outpacing churn, which is the best position to be in.

Bottom Line

Building an MRR tracker in Google Sheets is straightforward if you get the raw data structure right from the start. One clean subscription log, a month-map reference tab, a SUMIFS-powered summary, and a simple dashboard give you everything you need to monitor growth, spot churn early, and have a credible number ready for any investor conversation. The automation step is the piece most founders skip, and it is also the piece that determines whether the spreadsheet stays useful three months from now or quietly gets ignored. Set up the Zapier or Apps Script import in the same session you build the tracker. For a deeper look at the analytics tools that complement or eventually replace this setup, browse the full data analysis tools category.