TL;DR
You can build a working Stripe MRR dashboard using Python, a SQLite database, and Looker Studio in about three to four hours. The pipeline pulls live subscription data from the Stripe API, normalises billing intervals to monthly values, and visualises MRR, churn, and expansion in a shareable report. You need a Stripe account with at least a few active subscriptions, Python 3.10+, and a free Google account.
What You Need Before You Start
- a Stripe account with subscriptions (even a handful of test-mode records work for building)
- a Stripe secret API key (find it in Dashboard > Developers > API keys; use a restricted key scoped to read-only subscriptions and customers)
- Python 3.10 or higher installed locally
- the following Python packages:
stripe,pandas,sqlite3(stdlib),schedule - a free Google account for Looker Studio
- optional: Metabase Community Edition if you prefer a self-hosted BI tool
- optional: a PostgreSQL instance if you expect more than 50 000 subscription rows
- fifteen minutes of uninterrupted time to run the first full sync without distractions
Step 1: Create a Restricted Stripe API Key
Log into your Stripe Dashboard and navigate to Developers > API keys > Create restricted key. Name it something like mrr-dashboard-readonly. Under permissions, enable read access for Subscriptions and Customers only. Leave everything else off.
Copy the key into a .env file at your project root:
STRIPE_SECRET_KEY=rk_live_xxxxxxxxxxxxxxxxxx
Never commit this file. Add .env to your .gitignore before your first commit.
Why a restricted key? If this script ever runs on a server or gets shared accidentally, a restricted key limits the blast radius to read operations on those two resources. A full secret key can issue refunds, create charges, and delete customers.
You should now see a key in your Stripe dashboard labelled mrr-dashboard-readonly with a yellow “Restricted” badge and a last-used timestamp of “Never.”
Step 2: Install Dependencies and Test the Connection
Create a virtual environment and install the required packages:
python -m venv venv
source venv/bin/activate # Windows: venv\Scripts\activate
pip install stripe pandas python-dotenv schedule
Then write a quick connection test in test_connection.py:
import stripe, os
from dotenv import load_dotenv
load_dotenv()
stripe.api_key = os.getenv("STRIPE_SECRET_KEY")
account = stripe.Account.retrieve()
print(f"Connected to: {account['business_profile']['name']}")
Run it with python test_connection.py. A successful connection prints your business name. If you see an AuthenticationError, double-check the key value in your .env file and make sure there are no trailing spaces.
You should now see your Stripe account name printed in the terminal with no errors.
Step 3: Pull All Active Subscriptions
Stripe paginates API results at 100 objects per page. You need auto-pagination to get everything. Create fetch_subscriptions.py:
import stripe, os, pandas as pd
from dotenv import load_dotenv
load_dotenv()
stripe.api_key = os.getenv("STRIPE_SECRET_KEY")
def fetch_subscriptions():
rows = []
for sub in stripe.Subscription.list(
status="active", limit=100, expand=["data.customer"]
).auto_paging_iter():
for item in sub["items"]["data"]:
rows.append({
"sub_id": sub["id"],
"customer_id": sub["customer"]["id"],
"customer_email": sub["customer"].get("email", ""),
"plan_id": item["price"]["id"],
"plan_nickname": item["price"].get("nickname", ""),
"interval": item["price"]["recurring"]["interval"],
"interval_count": item["price"]["recurring"]["interval_count"],
"unit_amount": item["price"]["unit_amount"],
"quantity": item["quantity"],
"status": sub["status"],
"created": sub["created"],
"current_period_end": sub["current_period_end"],
})
return pd.DataFrame(rows)
df = fetch_subscriptions()
print(df.shape)
You should now see a tuple like (47, 12) showing your subscription count and column count.
Step 4: Normalise Billing Intervals to Monthly MRR
Stripe stores amounts in the smallest currency unit (cents for USD). A yearly plan at $1 200 per year is stored as 120000. You need to convert every subscription to a monthly equivalent.
Add this function to your script:
def calculate_mrr(row):
amount_dollars = row["unit_amount"] / 100
quantity = row["quantity"]
interval = row["interval"]
count = row["interval_count"]
monthly_map = {
"day": 30 / count,
"week": 4.33 / count,
"month": 1 / count,
"year": 1 / (count * 12),
}
multiplier = monthly_map.get(interval, 1)
return round(amount_dollars * quantity * multiplier, 2)
df["mrr"] = df.apply(calculate_mrr, axis=1)
print(f"Total MRR: ${df['mrr'].sum():,.2f}")
The monthly_map handles daily, weekly, monthly, and annual intervals. If you have custom intervals (every 3 months, every 6 months), the interval_count field covers those automatically.
You should now see a total MRR figure printed in dollars that matches roughly what you see in your Stripe revenue overview.
Step 5: Store the Data in SQLite
Running the fetch script every time you open a dashboard is slow. Store the data locally so your visualisation tool queries a database instead of the live API.
import sqlite3
def save_to_sqlite(df, db_path="mrr_dashboard.db"):
conn = sqlite3.connect(db_path)
df["snapshot_date"] = pd.Timestamp.today().date().isoformat()
df.to_sql("subscriptions", conn, if_exists="replace", index=False)
# also append to history table for trend charts
df.to_sql("subscriptions_history", conn, if_exists="append", index=False)
conn.close()
print(f"Saved {len(df)} rows to {db_path}")
save_to_sqlite(df)
The subscriptions table always holds the current snapshot. The subscriptions_history table accumulates daily rows so you can plot MRR over time. Run this script once per day (step 8 covers automation) and you will have a full MRR trend within a week.
You should now see a mrr_dashboard.db file in your project directory. Open it with any SQLite viewer and confirm rows exist in both tables.
Step 6: Add Churn and Expansion Tracking
Total MRR is one number. The useful numbers are new MRR, expansion MRR, contraction MRR, and churn MRR. Pull cancelled subscriptions from the last 30 days to calculate churn:
import time
thirty_days_ago = int(time.time()) - (30 * 24 * 60 * 60)
churned = []
for sub in stripe.Subscription.list(
status="canceled",
created={"gte": thirty_days_ago},
limit=100,
).auto_paging_iter():
for item in sub["items"]["data"]:
churned.append({
"sub_id": sub["id"],
"canceled_at": sub["canceled_at"],
"unit_amount": item["price"]["unit_amount"],
"interval": item["price"]["recurring"]["interval"],
"quantity": item["quantity"],
})
df_churn = pd.DataFrame(churned)
# apply same calculate_mrr logic to get churned MRR
Save this to a churned_subscriptions table in the same SQLite database. For expansion, compare unit_amount changes on existing subscriptions between snapshots using the history table.
You should now see a churned_subscriptions table in your database with at least some rows if any customers cancelled in the last 30 days.
Step 7: Connect SQLite to Looker Studio
Looker Studio does not connect directly to SQLite. Export your data to CSV first, then use Google Sheets as the bridge.
df["snapshot_date"] = pd.Timestamp.today().date().isoformat()
df.to_csv("mrr_export.csv", index=False)
print("Exported to mrr_export.csv")
Upload mrr_export.csv to a Google Sheet. In Looker Studio, click Add Data > Google Sheets, select your sheet, and enable “Use first row as headers.”
Build three charts:
1. a scorecard showing SUM(mrr) as your headline number
2. a time-series line chart with snapshot_date on the x-axis and SUM(mrr) on the y-axis
3. a bar chart breaking MRR down by plan_nickname
For more control and a self-hosted option, use Metabase Community Edition with a native SQLite connection instead. see our guide at /stripe-analytics-tools-compared/ for a full comparison.
You should now see a Looker Studio report with live data from your Stripe account, including a working MRR trend line.
Step 8: Automate the Daily Sync
Manual exports get skipped. Use the schedule library to run the fetch-and-save pipeline every day at 6am:
import schedule, time
def run_pipeline():
print("Running MRR sync...")
df = fetch_subscriptions()
df["mrr"] = df.apply(calculate_mrr, axis=1)
save_to_sqlite(df)
df.to_csv("mrr_export.csv", index=False)
print("Done.")
schedule.every().day.at("06:00").do(run_pipeline)
while True:
schedule.run_pending()
time.sleep(60)
Run this script in a tmux or screen session on a server, or set it as a cron job: 0 6 * * * /path/to/venv/bin/python /path/to/pipeline.py. For a no-server option, wrap the script in a GitHub Action that triggers on a daily schedule and writes the CSV to a Google Sheet via the Sheets API.
You should now see the subscriptions_history table grow by one day’s worth of rows each morning, and the CSV update automatically.
Step 9: Add a Net Revenue Retention (NRR) Metric
MRR alone does not show whether your business is healthy. Net Revenue Retention tells you how much revenue you keep from existing customers after churn and expansion. Calculate it with SQL:
-- Run in SQLite or paste into a Metabase question
SELECT
strftime('%Y-%m', snapshot_date) AS month,
SUM(CASE WHEN snapshot_date = date('now', 'start of month') THEN mrr ELSE 0 END)
/ SUM(CASE WHEN snapshot_date = date('now', 'start of month', '-1 month') THEN mrr ELSE 0 END)
* 100 AS nrr_pct
FROM subscriptions_history
GROUP BY month;
An NRR above 100% means expansion revenue from existing customers outpaces churn. Add this as a scorecard in Looker Studio. If your NRR sits below 90% for two months in a row, that is the signal to fix retention before you fix acquisition. For a deeper breakdown of SaaS metrics worth tracking, see /saas-metrics-every-founder-should-track/.
You should now see a monthly NRR percentage appear in your dashboard alongside your total MRR.
Common Mistakes To Avoid
- Forgetting
interval_count: A plan billed every three months is not the same as a monthly plan times three. Always divide byinterval_count, not justinterval. Skipping this inflates MRR for quarterly and bi-annual plans. - Double-counting subscription items: One subscription can have multiple items (base plan plus add-ons). Iterating over
sub["items"]["data"]instead of the subscription object itself avoids summing the same subscription twice. - Using
replacefor every history write: If you setif_exists="replace"on the history table by mistake, you delete all previous snapshots on every run. The history table must useif_exists="append". - Pulling only
status="active"for churn: Cancelled subscriptions have a different status. You need a second query withstatus="canceled"and a date filter to capture churn MRR correctly. - Not converting cents to dollars: Stripe returns
unit_amountin cents. Forgetting to divide by 100 makes your MRR look 100x bigger than it is. Fun for five seconds, then embarrassing in a board meeting. - Refreshing the Looker Studio CSV manually: If you forget to re-upload the CSV after the daily sync, your dashboard shows stale data. Automate the Google Sheets update with the Sheets API or use a tool like ChartMogul once volume justifies it.
When To Level Up
This approach works well up to roughly 2 000 active subscriptions and a single Stripe account. Past that point, a few things start to break. Large accounts hit API rate limits during the full subscription sweep. Monthly history tables grow into hundreds of thousands of rows, and SQLite query performance degrades noticeably. Multi-product businesses with different Stripe accounts need separate pipelines that merge into one dataset, which gets messy fast.
The other trigger is team size. Once a second person needs to see the dashboard, sharing a Looker Studio link is fine. Once five people need to slice it differently, ask questions, or set up alerts, you want a proper BI layer with access controls and a real data warehouse behind it.
At that point, move to a purpose-built stack: a warehouse like BigQuery or Supabase, a Stripe data connector (Airbyte has a free Stripe source), and a BI tool like Metabase or Grafana. The /category/data-analysis/ section of this site covers the tools worth evaluating at that stage.
The DIY pipeline built here is not a toy. it is a legitimate starting point that many SaaS founders run in production for months. But know the ceiling so you migrate before the pain forces you to.
Frequently Asked Questions
Does this work with Stripe test mode?
Yes. Set your STRIPE_SECRET_KEY to a test-mode key (starts with sk_test_) and the script pulls test subscriptions. This is the safest way to build and validate the dashboard before pointing it at live data.
What if I use metered billing or usage-based pricing?
Metered billing requires pulling UsageRecord objects and summing them per billing period. The MRR calculation is more complex because the amount varies month to month. Start with a fixed-price MRR baseline and layer in metered estimates using the previous month’s actuals.
How do I handle multiple currencies?
The script above mixes currencies by default, which is wrong. Add a filter for your primary currency in the Stripe API call: currency="usd". For multi-currency businesses, convert to a base currency using daily FX rates before summing MRR.
Can I use this with Stripe Billing without subscriptions?
If you use one-time invoices rather than subscriptions, Stripe does not track recurring revenue natively. You would need to query invoices, identify recurring patterns manually, and estimate MRR from payment frequency. Subscriptions are the right tool if MRR tracking matters to you.
How accurate is this compared to Stripe’s own MRR figure?
Stripe’s dashboard MRR uses their own calculation logic, which may differ slightly in how it handles trials, discounts, and prorations. The Python approach gives you full control over the formula and transparency into every row. For investor reporting, document your methodology so the number is reproducible and auditable.
Bottom Line
Building a Stripe MRR dashboard from scratch takes a few hours, a bit of Python, and a free Google account. the pipeline connects to the Stripe API with a restricted read-only key, normalises billing intervals to monthly values, stores snapshots in SQLite for trend analysis, and visualises the result in Looker Studio. you add churn and NRR on top of that base, then automate with a daily cron job so the data stays fresh without any manual work. the whole stack costs nothing until you outgrow SQLite and need a real warehouse. if you are ready to explore what comes next as your data volume grows, start with the /category/data-analysis/ tools section to compare MRR platforms, BI tools, and data pipeline options suited for SaaS at scale.