How to Build a Real Time SaaS MRR Dashboard with Google Sheets and BigQuery

If your vital SaaS metrics are trapped in a soul-crushing spreadsheet, it’s time to break free. Learn how to automate your reporting to get accurate data and reclaim the time you need to actually grow your business.

Introduction: The End of Manual MRR Reporting

If you’re running a SaaS business, you live and die by your metrics. Monthly Recurring Revenue (MRR), Churn Rate, Customer Lifetime Value (LTV), and Customer Acquisition Cost (CAC) aren’t just vanity numbers; they are the vital signs of your company. But how are you tracking them? For too many founders and early-stage teams, the answer is a complex, fragile, and soul-crushing labyrinth of spreadsheets.

This section is about acknowledging the pain of that manual process and introducing a more robust, automated, and scalable solution. We’re going to lay the groundwork for a system that frees you from the tyranny of the VLOOKUP and gives you back the time you need to actually grow your business.

The Pain of Manual SaaS Metric Tracking in Spreadsheets

Let’s be honest. The “master spreadsheet” for your SaaS metrics probably started innocently enough. A simple export from Stripe, a few formulas, and voilà—you had an MRR chart. But as your business grew, so did the complexity. Soon, you were dealing with upgrades, downgrades, failed payments, refunds, and different subscription plans.

The manual spreadsheet approach quickly becomes a house of cards built on digital duct tape.

• **It’s Incredibly Time-Consuming: You or someone on your team spends hours, if not days, at the end of each month exporting CSVs, cleaning data, wrestling with pivot tables, and manually updating charts just to get a snapshot of the last month.

• **It’s Fragile and Error-Prone: One broken formula, one copy-paste error, one incorrectly formatted cell, and your entire financial model is thrown into question. The constant fear of “Is this number really right?” undermines your confidence in making critical decisions.

• It’s a Static Snapshot: The moment you finish the report, it’s already out of date. You have no real-time visibility into how a new marketing campaign is performing today or how a pricing change is affecting new sign-ups this week. You’re always looking in the rearview mirror.

• It Doesn’t Scale: What works for 50 customers breaks for 500 and is a complete disaster for 5,000. Spreadsheets buckle under the weight of large datasets, becoming slow, unresponsive, and ultimately, unusable as your single source of truth.

This manual grind isn’t just an inconvenience; it’s a strategic liability. It keeps you reactive when you need to be proactive and buries you in low-value tasks when you should be focused on high-impact strategy.

Why a Real-Time Dashboard is a Game-Changer for Founders

Imagine a world where your key SaaS metrics are always up-to-date, always accurate, and always available at a glance. That’s not a pipe dream; it’s the reality an automated, real-time dashboard provides. Moving from a static spreadsheet to a live dashboard is a fundamental upgrade for your business operations.

Here’s why it’s a game-changer:

• A Single Source of Truth: No more debating which spreadsheet is the “correct” one. An automated system that pulls directly from your payment processor (like Stripe) and warehouses the data centrally ensures everyone—from marketing and sales to product and leadership—is looking at the same numbers.

• Accelerated Decision-Making: See the immediate impact of your actions. Did that new feature release reduce churn? Is the new ad campaign driving high-LTV customers? A real-time dashboard gives you answers in hours, not weeks, allowing you to double down on what works and kill what doesn’t with confidence.

• From Reactive to Proactive: Instead of discovering at the end of the month that your churn rate spiked, a live dashboard helps you spot negative trends as they emerge. This early warning system allows you to intervene and fix problems before they snowball into crises.

• Empowers the Entire Team: When key metrics are easily accessible and understandable, it fosters a culture of ownership and data-informed thinking across the company. Your team can self-serve insights without needing to bother you for a custom report.

Ultimately, [Automated Job Creation in Real Time Jobber and Google Sheets Integration from Gmail](https://votuduc.com/Automated-Job-Creation-in-Jobber-from-Gmail-p115606) gives you leverage. It automates the reporting so you can focus on the analysis and action that actually drive growth.

An Overview of Our Automated Solution: Sheets, BigQuery, and Apps Script

So, how do we build this magical dashboard? We’re going to architect a robust and surprisingly low-cost solution using three powerful tools from the Google ecosystem. Each component plays a distinct and crucial role.

Here’s a high-level look at our tech stack and the data flow:

1. Google BigQuery (The Brains): This is our scalable, serverless data warehouse. Instead of dumping raw data into a fragile spreadsheet, we’ll send it to BigQuery. Its job is to be the permanent, reliable “single source of truth.” It will store all our historical subscription and customer data and perform the heavy-duty calculations (like cohort analysis and MRR movements) that would instantly crash a Google Sheet.

2. [AI Powered Cover Letter Automated Quote Generation and Delivery System for Jobber Engine](https://votuduc.com/AI-Powered-Cover-Letter-Automated Work Order Processing for UPS-Engine-p111092) (The Engine): This is the automation glue that holds everything together. We’ll write simple JavaScript-based scripts that run on a schedule. These scripts will act as our data pipeline:

• Fetch: They will call the Stripe API to pull the latest subscription and customer data.

• Load: They will load this raw data into our BigQuery tables.

• Query: They will run pre-written SQL queries against BigQuery to calculate our final, aggregated metrics (e.g., total MRR, net new MRR, churn rate).

3. **[Automated Web Scraping with [Multilingual Text-to-Speech Tool with SocialSheet Streamline Your Social Media Posting 123](https://votuduc.com/Multilingual-Text-to-Speech-Tool-with-Google-Workspace-p809282)](https://votuduc.com/Automated-Web-Scraping-with-Google-Sheets-p292968) (The Face): This is our final presentation layer. It’s the familiar, user-friendly dashboard interface. An Apps Script function will pull the final, summarized metrics from BigQuery and neatly place them into specific cells in our Google Sheet. From there, we can build beautiful, interactive charts and share them with the team.

The flow is simple but powerful: Stripe API → Apps Script → BigQuery (for storage & processing) → Apps Script → Google Sheets (for visualization).

This architecture gives us the best of all worlds: the immense power and scalability of a real data warehouse, the flexibility of custom automation, and the accessibility of a familiar spreadsheet interface. Let’s get started.

The Architectural Blueprint: From Raw Data to Actionable Insight

Before we write a single line of code, let’s architect our solution. A robust system isn’t about choosing the most complex tools; it’s about choosing the right tools and making them work in concert. Our goal is to create a pipeline that is scalable, automated, and accessible. This philosophy leads us to a powerful three-tier architecture built entirely within the Google Cloud and Workspace ecosystem: BigQuery as the data warehouse, Genesis Engine AI Powered Content to Video Production Pipeline as the automation engine, and Google Sheets as the user-facing front-end.

This isn’t just a random collection of services. It’s a deliberate design pattern that separates concerns: data storage, business logic, and presentation. Let’s break down the role each component plays.

Why BigQuery is Your Scalable Data Warehouse for Subscription Data

It’s tempting to dump all your data directly into a Google Sheet. For a handful of subscriptions, that might work. But as your SaaS business grows, a spreadsheet quickly becomes a liability. You’ll face performance degradation, row limits, and a high risk of data corruption from a single misplaced formula. You need a proper source of truth.

Enter BigQuery. It’s not just a database; it’s a fully-managed, petabyte-scale, serverless data warehouse. Here’s why it’s the perfect foundation for our MRR dashboard:

• Infinite Scalability: As your company grows, so does your subscription event data—new signups, upgrades, downgrades, cancellations, payments. BigQuery is built to handle this volume from day one without you ever needing to provision or manage servers. It scales transparently, ensuring your queries are fast whether you have 100 subscriptions or 10 million.

• Immutable Source of Truth: We will treat BigQuery as an append-only ledger for subscription events (e.g., raw webhooks from Stripe like invoice.paid or customer.subscription.deleted). This creates an immutable, auditable history of everything that has ever happened. Unlike a spreadsheet where rows can be accidentally deleted or edited, your BigQuery table becomes the unchangeable historical record, guaranteeing data integrity.

• Blazing-Fast Analytics: BigQuery is a columnar store database, optimized for analytical queries (OLAP). When you ask, “What was our Net New MRR in Q2?”, it only scans the specific columns needed to answer that question, making complex calculations across millions of rows incredibly fast. This is the engine that will perform the heavy lifting of calculating your SaaS metrics.

• Cost-Effective: The pricing model is built for lean operations. You pay for the data you store (which is very cheap) and the data you process in queries. For most early and growth-stage SaaS companies, the generous free tier is more than sufficient to run a comprehensive MRR dashboard without incurring any cost.

In our architecture, BigQuery is the vault. It holds the raw, untarnished truth of your business, providing a stable foundation upon which all insights are built.

The Role of [Architecting Multi Tenant AI Workflows in Building Modular Agentic Apps Script with Gemini Function Calling](https://votuduc.com/architecting-multi-tenant-ai-workflows-in-google-apps-script-p-20260321290501) as the Automation Engine

If BigQuery is the vault, Google Apps Script is the trusted courier that retrieves the gold and delivers it to the showroom. It’s the serverless “glue” that connects our powerful data warehouse to our accessible front-end.

Google Apps Script is a cloud-based scripting platform based on JavaScript that lets you extend and automate Automatically create new folders in Google Drive, generate templates in new folders, fill out text automatically in new files, and save info in Google Sheets applications. For our project, it serves two critical functions:

1. Query Execution: Apps Script will act as our scheduled orchestrator. We will write a script that connects directly to the BigQuery API. This script will hold the master SQL queries needed to calculate our key metrics—MRR, Churn Rate, Active Subscriptions, LTV, etc. It’s the “business logic layer” of our system.

2. Data Hydration: Once the script executes a query against BigQuery, it receives the aggregated results. Its second job is to “hydrate” our Google Sheet. It will programmatically clear old data and write the new, fresh results into designated cells on the dashboard.

Using Apps Script is a strategic choice because of its native integration. It lives inside your Google account and has built-in, first-class services for communicating with BigQuery (BigQuery service) and Google Sheets (SpreadsheetApp service). This eliminates complex authentication hurdles and boilerplate code. With time-driven triggers, you can set your script to run automatically—every hour, every night—ensuring your dashboard is always up-to-date without any manual intervention. It’s a free, serverless, and powerful automation engine hiding in plain sight.

Leveraging Google Sheets as the Universal Front-End for Your Team

With data securely stored in BigQuery and logic automated by Apps Script, we need a place to visualize the results. While sophisticated BI tools like Looker or Tableau have their place, for a core metric like MRR, their complexity can be a barrier. Google Sheets, however, is the perfect “last-mile” solution.

Here’s why it shines as the front-end for our dashboard:

• Universal Accessibility: Your entire team already knows how to use a spreadsheet. The CEO, the Head of Sales, the Product Manager, and the Marketing Analyst can all open the document and immediately understand what they’re looking at. There is zero learning curve and no need to purchase additional software licenses. This democratizes access to critical business data.

• Rich Visualization & Flexibility: Don’t underestimate the power of modern Google Sheets. You can build compelling line charts for MRR growth, waterfall charts for MRR movements, and pivot tables for deep dives. The key is that the Sheet is only a presentation layer. The underlying data is not calculated in the Sheet; it’s simply displayed there. This frees up users to copy the data, run their own ad-hoc analyses, and build custom charts without any risk of corrupting the source of truth in BigQuery.

• Unmatched Collaboration: This is where Sheets truly excels. Your finance team can leave comments on specific data points. Your product team can filter a view to see MRR by a specific plan. The leadership team can get a real-time pulse on the business during a meeting. It’s a living, collaborative document built on a foundation of rock-solid, automated data.

By using Google Sheets as the front-end, you create a powerful dashboard that is not only accurate and real-time but also deeply integrated into the existing workflows of your entire organization.

Step 1: Consolidating Your Billing Data in BigQuery

Before we can build a dashboard, we need a reliable, scalable, and queryable source of truth for our financial data. This is where Google BigQuery shines. It’s a serverless data warehouse designed to handle massive datasets with lightning-fast SQL queries. Our first step is to centralize all the raw data from our billing platform into a BigQuery dataset. This consolidation turns fragmented operational data into a powerful analytical asset, forming the bedrock of our entire MRR dashboard.

Connecting Your Billing Platform (e.g., Stripe) to BigQuery

Getting data from a third-party service like Stripe into your own data warehouse is the first critical hurdle. You have a few paths, ranging from a simple click-to-configure setup to a full-blown custom engineering project.

Option 1: Third-Party ELT/ETL Tools (The Recommended Path)

For most businesses, this is the most efficient and reliable option. Tools like Fivetran, Stitch Data, or Airbyte specialize in one thing: moving data from Point A to Point B.

• How it works: You provide API keys for your Stripe account and credentials for your BigQuery project. The service handles the rest. It extracts the data, normalizes it into a predefined schema, and loads it into BigQuery tables (customers, subscriptions, invoices, etc.). Crucially, it manages ongoing synchronization, so your BigQuery data stays up-to-date.

• Pros:

• Massive Time Saver: Saves hundreds of hours of engineering and maintenance.

• Reliability: These services are built to handle API changes, rate limits, and error recovery.

• Production-Ready: They just work, letting you focus on analysis, not data plumbing.

• Cons:

• Cost: These services are not free, though the cost is often a fraction of the engineering salary required to build and maintain a custom solution.

Option 2: Official Connectors (The “Best of Both Worlds” Path)

Some platforms offer their own native data pipelines. Stripe, for example, has Stripe Data Pipeline, which can be configured to send your data directly to BigQuery (or other warehouses).

• How it works: You configure it directly within the Stripe Dashboard. It’s a first-party solution, so the integration is seamless.

• Pros:

• Official Support: Maintained by Stripe, ensuring compatibility.

• Simple Setup: Often just a few clicks to get started.

• Cons:

• Less Flexibility: You get the data schema Stripe provides, with limited transformation options.

• Not Universally Available: Your specific billing platform may not offer this.

Option 3: The DIY Script (The “Last Resort” Path)

You can always write your own code to pull data from your billing provider’s API and push it to BigQuery.

• How it works: You’d use a language like JSON-to-Video Automated Rendering Engine or Node.js, leverage the Stripe API client library, and use the BigQuery client library to load the data. You would need to schedule this script to run periodically (e.g., using Cloud Functions and Cloud Scheduler).

• Pros:

• Total Control: You can customize the entire process.

• No Software Cost: You only pay for the cloud resources you consume.

• Cons:

• Brittle and High Maintenance: You are responsible for handling API rate limits, pagination, schema changes, error logging, and retries. This becomes a significant engineering burden.

Our Recommendation: Start with a third-party ELT tool or an official connector if available. The time you save will allow you to deliver value (the actual dashboard) much faster. For the rest of this guide, we’ll assume you have your Stripe data in a BigQuery dataset, with tables like subscription, price, and customer.

Structuring Your Data: The Essential MRR Data Model

Once your data lands in BigQuery, it will likely be in a raw, normalized format that mirrors the source API. This is great for completeness but not ideal for direct MRR calculation. We need to establish a clear mental model of how the key pieces fit together.

For a SaaS business using a platform like Stripe, the core entities for calculating MRR are:

• subscriptions: This is the heart of the model. Each row represents a customer’s recurring commitment to a product. Key columns include id, customer_id, price_id, status (active, canceled, trialing), current_period_start, current_period_end, and cancel_at_period_end.

• prices (or plans in older APIs): This table tells us the value and terms of a subscription. It contains the critical information: unit_amount (the cost in the smallest currency unit, e.g., cents) and recurring_interval (month, year, etc.).

• customers: This table holds information about who is subscribing, but for MRR calculations, it’s primarily used to link subscriptions back to a single entity.

The relationship is straightforward: A customer has one or more subscriptions, and each subscription is tied to a specific price. To calculate MRR, we need to join these tables to connect a subscription’s status with its monetary value and billing frequency.

Our goal is to write a query that can look at this data and produce a single, definitive number: our current Monthly Recurring Revenue.

Writing the Core SQL Query to Calculate Monthly Recurring Revenue

This is where the magic happens. We’ll craft a SQL query that transforms our raw tables into a clear MRR figure. The logic involves three key steps:

1. Identify all currently active subscriptions.

2. Normalize the value of each subscription to a monthly amount. (e.g., a $1200/year plan is $100 MRR).

3. Sum the normalized monthly amounts.

Let’s build the query. We’ll use a Common Table Expression (CTE) to keep the logic clean and readable. This query assumes your tables are named subscription and price within a stripe_dataset.

-- This query calculates the current, snapshot MRR.

-- It assumes data is synced from Stripe into a BigQuery dataset named `stripe_dataset`.

WITH subscriptions_with_mrr AS (

SELECT

s.id AS subscription_id,

-- Step 1: Normalize all plan amounts to a true monthly value.

-- Stripe amounts are in cents, so we divide by 100 to get dollars.

CASE

WHEN p.recurring_interval = 'year' THEN (p.unit_amount / 100) / 12

WHEN p.recurring_interval = 'month' THEN (p.unit_amount / 100)

-- Add other intervals if you use them, e.g., 'week' would be (p.unit_amount / 100) * 4.33

ELSE 0

END AS mrr_amount

FROM

`your-project-id.stripe_dataset.subscription` AS s

JOIN

-- We use an INNER JOIN because a subscription without a price is invalid for MRR calculation.

`your-project-id.stripe_dataset.price` AS p

ON s.price_id = p.id -- Adjust this join key based on your specific schema (it might be s.plan_id)

WHERE

-- Step 2: Define what an "active" subscription is.

-- This is the core of the MRR definition. We only include subscriptions

-- that are currently generating revenue.

-- 'past_due' is included because the customer is still subscribed and expected to pay.

-- 'trialing' is typically excluded from strict MRR, as no payment has been made.

s.status IN ('active', 'past_due')

)

-- Step 3: Sum the monthly values of all active subscriptions.

SELECT

-- Use ROUND to present a clean, two-decimal number.

ROUND(SUM(mrr_amount), 2) AS total_mrr

FROM

subscriptions_with_mrr;

Breaking Down the Query:

• WITH subscriptions_with_mrr AS (...): We define a CTE to first prepare our data. This makes the final query much simpler. Inside this CTE, we join subscription and price tables to bring the status and value information together for each subscription.

• CASE WHEN p.recurring_interval = ...: This is the crucial normalization logic. It checks if a plan is billed yearly or monthly and calculates its equivalent monthly value. If you have weekly or quarterly plans, you can easily extend this CASE statement.

• WHERE s.status IN ('active', 'past_due'): This is our filter. It defines what contributes to MRR right now. A subscription that is canceled or incomplete is excluded. We include past_due because the subscription is still technically active and there’s an expectation of payment.

• SELECT ROUND(SUM(mrr_amount), 2): Finally, we sum the mrr_amount from our CTE to get the total MRR figure.

Running this query in the BigQuery console will give you a single, powerful number: your current MRR. This snapshot is the foundation upon which we’ll build our historical analysis and real-time dashboard.

Step 2: Automating the Data Sync with Google Apps Script

With our MRR data neatly structured in BigQuery, the next challenge is to bridge the gap between our powerful data warehouse and our accessible Google Sheet. Manually exporting CSVs is a non-starter for a “real-time” dashboard. This is where Google Apps Script comes in. It’s the serverless, JavaScript-based glue that lives inside your Google Sheet, allowing us to programmatically fetch data from BigQuery and write it directly into our cells.

Let’s roll up our sleeves and build the automation engine for our dashboard.

Setting Up Your Apps Script Project and API Permissions

Before we write a single line of code, we need to set up our environment. This involves creating an Apps Script project linked to our sheet and granting it the necessary permissions to communicate with BigQuery.

1. Open the Apps Script Editor: From your Google Sheet, navigate to Extensions > Apps Script. This will open a new tab with a cloud-based code editor. Give your project a descriptive name, like “BigQuery MRR Sync”.

2. Enable the BigQuery API Service: Apps Script doesn’t have access to all Google services by default. We need to explicitly enable the BigQuery API.

In the editor, find the* Services** section in the left-hand sidebar and click the + icon.

A dialog will appear. Find* BigQuery API in the list, select it, and click Add**.

Step 3: Building the Executive MRR Dashboard in Google Sheets

With our clean, aggregated data pipeline flowing from BigQuery, we’ve reached the final and most rewarding stage: transforming raw numbers into actionable insights. This is where the magic happens. We’ll move from a table of data to a strategic command center in Google Sheets, a tool that’s universally accessible to your team. Our goal isn’t just to display data, but to tell a clear and compelling story about the health and trajectory of your business.

Designing an Effective and Clean Dashboard Layout

Before you write a single formula, take a moment to think like a designer. A great dashboard is opinionated; it guides the viewer’s attention to what matters most. Clutter is the enemy of clarity.

Core Principles for a High-Impact Layout:

1. The F-Pattern Rule: Most people scan screens in an “F” shape. Place your most critical, high-level KPIs (Key Performance Indicators) in the top-left corner. This is where you’ll put your “at-a-glance” numbers like current MRR and month-over-month growth.

2. Embrace Whitespace: Don’t cram every inch of the sheet with charts and numbers. Use empty rows and columns to create visual separation between different sections. This reduces cognitive load and makes the dashboard feel less intimidating and more professional.

3. Establish a Logical Flow: Group related metrics. For instance, have a section dedicated to MRR movement (New, Expansion, Churn) and another for customer-level metrics (LTV, ARPA). This creates a narrative that users can follow.

4. Use Color with Intention: Resist the urge to create a rainbow. Develop a simple color palette. Use one primary color for your brand, green for positive trends (e.g., new MRR), red for negative ones (e.g., churn), and shades of grey for everything else. Consistency is key.

Practical Sheet Structure:

To keep things organized and prevent accidental data corruption, we’ll use a multi-tab setup:

• Dashboard: This is the final, polished view for stakeholders. It should contain only charts and key numbers. No raw data or complex formulas live here. For a pro look, go to View > Show and uncheck “Gridlines”.

• Data (from BQ): This sheet houses the raw data connected from BigQuery. Lock this sheet down (Protect sheet) and hide it. Nobody should ever touch this directly.

• Calculations: This is your workshop. All your pivot tables, QUERY functions, and intermediate calculations will live here. The Dashboard tab will simply reference the cells and ranges in this sheet. This separation is crucial for maintainability and performance.

Scaling and Beyond: Next-Level SaaS Analytics

Congratulations! You’ve moved your core MRR reporting from the fragile confines of a spreadsheet into a robust, automated BigQuery pipeline. This is a massive win. Your dashboard is faster, more reliable, and less prone to manual error. But this isn’t the finish line; it’s the starting line for a much more powerful analytics engine.

Your current setup is excellent for monitoring the health of your business. The next step is to evolve it into a system that helps you strategize and drive growth. Let’s explore how to layer on more sophisticated metrics, upgrade your visualization tools, and ensure your architecture can handle whatever you throw at it.

Incorporating Other Metrics like LTV and CAC

MRR tells you what you’re earning, but it doesn’t tell you if your growth is profitable or sustainable. To get that picture, you need to introduce the two most critical SaaS metrics: Customer Lifetime Value (LTV) and Customer Acquisition Cost (CAC). The beauty of your new BigQuery setup is that you’re already halfway there.

Customer Lifetime Value (LTV)

LTV estimates the total revenue a business can reasonably expect from a single customer account. It helps you understand what a customer is worth, which informs how much you can spend to acquire one.

A simple way to calculate LTV is:

LTV = Average Revenue Per Account (ARPA) / Customer Churn Rate

You can derive both of these components from the MRR data you already have in BigQuery.

• ARPA: SUM(mrr) / COUNT(DISTINCT active_customer_id) for a given period.

• Churn Rate: (COUNT(churned_customers) / COUNT(start_of_period_customers)) * 100 for a given period.

You can create a new BigQuery view that calculates these components on a monthly basis, allowing you to track LTV over time. The real power comes when you start segmenting LTV by pricing plan, marketing channel, or customer cohort.

Customer Acquisition Cost (CAC)

CAC is the total cost of sales and marketing required to acquire a new customer. This metric is crucial because if your CAC is higher than your LTV, your business model is fundamentally broken.

Calculating CAC requires bringing new data into your warehouse. Your acquisition costs live in other systems:

• Google Ads

• Facebook Ads

• LinkedIn Campaigns

• Content marketing and SEO tool subscriptions

• Sales team salaries and commissions (from your CRM or HR system)

This is where BigQuery truly shines as a central data warehouse. You can use tools like Fivetran, Stitch, or even BigQuery’s own Data Transfer Service to pipe cost data from these platforms into separate tables in the same dataset as your MRR data.

Once the data is in BigQuery, you can write a query to join your marketing spend with the new customer data from your subscription table.

-- Conceptual Query for LTV:CAC Ratio

WITH monthly_costs AS (

SELECT

'2023-11-01' AS month,

SUM(spend) AS total_spend

FROM google_ads_spend

WHERE date BETWEEN '2023-11-01' AND '2023-11-30'

-- UNION ALL with other ad platforms

),

new_customers_monthly AS (

SELECT

DATE_TRUNC(subscription_start_date, MONTH) AS month,

COUNT(DISTINCT customer_id) AS new_customers

FROM your_mrr_table

WHERE event_type = 'new_subscription'

GROUP BY 1

)

SELECT

c.month,

c.total_spend / n.new_customers AS cac,

-- Join with your LTV calculation view here

ltv.ltv_value,

ltv.ltv_value / (c.total_spend / n.new_customers) AS ltv_to_cac_ratio

FROM monthly_costs c

JOIN new_customers_monthly n ON c.month = n.month

LEFT JOIN your_ltv_view ltv ON c.month = ltv.month;

The goal is to track your LTV:CAC ratio. A healthy SaaS business typically aims for a ratio of 3:1 or higher, meaning a customer is worth at least three times what it cost to acquire them.

Advanced Visualization with Looker Studio

Google Sheets was the perfect tool to get started, but as your data volume and complexity grow, you’ll start to feel its limitations. Dashboards become slow, collaboration gets messy, and creating interactive filters is cumbersome.

Enter Looker Studio (formerly Google Data Studio). It’s a free, enterprise-grade BI tool that is the natural next step in the Google Cloud ecosystem. Migrating your dashboard from Sheets to Looker Studio provides several immediate advantages:

1. Direct BigQuery Connector: Looker Studio connects directly to your BigQuery tables and views. There’s no more need for an Apps Script to periodically copy data. Your dashboard can query BigQuery in real-time (or use BigQuery’s BI Engine for lightning-fast cached results), ensuring your data is always fresh.

2. Interactive and Dynamic: Empower your team to self-serve. With Looker Studio, you can build one dashboard with powerful controls that allow users to filter by date range, customer segment, marketing channel, or any other dimension. They can drill down from a high-level MRR number to the specific customers that make it up.

3. Data Blending: You can visualize your LTV:CAC ratio by creating a “blended data source” directly within Looker Studio. It can combine your BigQuery MRR data with data from a Google Sheet containing sales quotas or a Google Analytics source with website conversion data, all in a single chart.

4. Governance and Sharing: Easily manage who can view or edit reports. Schedule automated PDF exports to be emailed to your executive team every Monday morning. Embed charts directly into internal wikis or presentations.

Making the switch is straightforward. You simply create a new Data Source in Looker Studio, select the BigQuery connector, and point it to the tables or views you’ve already built. From there, you can recreate—and dramatically improve upon—your Google Sheets dashboard.

Considerations for Maintaining and Scaling Your Architecture

Your current architecture is lean and effective, but as your company grows, your data volume and analytical needs will become more demanding. Here are three key areas to consider for long-term health and scalability.

1. From Scripts to a Robust Data Pipeline

The initial Google Apps Script is a brilliant MVP, but it can become a bottleneck. It might hit Google’s execution time limits or struggle with high-volume webhook traffic. To build a more resilient ingestion pipeline, consider these upgrades:

• Cloud Functions: For a serverless, event-driven approach. Instead of a script that runs on a schedule, you can deploy a Cloud Function with an HTTP trigger that acts as a webhook endpoint. Stripe (or your payment provider) can call this function directly every time an event happens (e.g., invoice.paid), providing true real-time updates in a highly scalable way.

• Managed ELT/ETL Tools: Services like Fivetran, Airbyte, or Stitch offer pre-built, production-hardened connectors for hundreds of data sources, including Stripe, Salesforce, and all major ad platforms. This shifts the burden of building and maintaining data pipelines from your team to a specialized vendor, freeing you up to focus on analysis, not ingestion.

2. Data Modeling with dbt (Data Build Tool)

As you add more metrics and data sources, your SQL logic can become a tangled mess of complex, repetitive queries. This is where a transformation tool like dbt becomes invaluable.

dbt allows you to apply software engineering best practices to your analytics code. You can:

• Modularize: Break down long, complicated queries into smaller, reusable SQL models that build on each other (e.g., a stg_stripe_events model feeds into a fct_mrr_movements model).

• Version Control: Store all your logic in Git, enabling code reviews, collaboration, and a full history of changes.

• Test: Write data quality tests to ensure your numbers are always accurate (e.g., assert mrr is never negative).

By implementing dbt, you create a clean, reliable transformation layer. Your Looker Studio dashboard no longer queries raw, messy data; it connects to beautifully modeled, well-documented, and tested tables in BigQuery. This makes your analytics faster, more trustworthy, and infinitely easier to maintain.

3. BigQuery Cost and Performance Optimization

BigQuery is incredibly powerful, but you pay for the data your queries process. As your dashboards get more users and your tables grow to billions of rows, it’s wise to optimize for both cost and speed.

• Partitioning and Clustering: This is the single most important optimization. Partition your main events table by date (e.g., event_timestamp). When you query a specific date range, BigQuery will only scan the data in those partitions, dramatically reducing cost and improving speed. Cluster the table by frequently filtered columns like customer_id to further accelerate lookups.

• Materialized Views: For very complex and frequently accessed aggregations (like a daily MRR summary table), you can create a materialized view. BigQuery will automatically pre-calculate the results and keep them updated, making dashboard loads instant and cheap, as the complex query only runs when the underlying data changes.

• Monitor Your Usage: Use the BigQuery query history and GCP’s cost management dashboards to identify expensive or inefficient queries. Often, a small tweak to a query powering a Looker Studio chart can lead to significant cost savings.

Conclusion: Your Automated SaaS Command Center

You’ve made it. You’ve navigated the APIs, wrangled the SQL queries, and connected the dots between a powerhouse data warehouse and a familiar spreadsheet interface. What you’ve built is far more than just a report; it’s an automated, scalable, and near real-time command center for your SaaS business. This system is the foundation for a truly data-informed culture, moving you from reactive reporting to proactive strategy.

Recap: The Power of an Automated Real-Time Dashboard

Let’s take a moment to appreciate the architecture you’ve assembled. By piping your billing data into BigQuery and using Google Sheets as the visualization layer, you have fundamentally upgraded your operational intelligence.

• Automation at its Core: You’ve replaced the soul-crushing, error-prone cycle of manual CSV exports and data wrangling with a robust, scheduled pipeline. Your metrics—MRR, churn, LTV, ARR—now update themselves, freeing up invaluable time for analysis instead of administration.

• A Scalable Foundation: While Google Sheets is your user-friendly front end, BigQuery is the powerhouse engine. As your company grows from hundreds to hundreds of thousands of subscriptions, this system won’t break a sweat. Your queries will remain fast, and your ability to store and analyze historical data is virtually limitless.

• A Single Source of Truth: By centralizing your raw data in BigQuery and defining your core business logic in SQL, you’ve eliminated the “dueling spreadsheets” problem. Every chart and every KPI in your dashboard traces back to a single, verifiable source, fostering trust and alignment across your entire team.

From Manual Reporting to Strategic Data-Driven Decisions

The true value of this dashboard isn’t just in the numbers it displays, but in the conversations and decisions it enables. You’ve officially graduated from data janitor to data strategist.

Gone are the days of making critical decisions based on last month’s stale data. The lag between an event happening and you knowing about it has been compressed from weeks to hours, or even minutes. This new velocity allows you to operate with a clarity that was previously impossible.

Instead of asking, “Can someone pull the latest churn numbers?”, your team can now ask the questions that truly matter:

• “We just launched a new pricing tier. How is it impacting our Expansion MRR in real-time?”

• “Is the spike in new sign-ups from our latest marketing campaign translating into a higher Net New MRR?”

• “We’re seeing a slight increase in logo churn. Can we drill down in BigQuery to see if it’s concentrated in a specific customer segment or plan?”

This dashboard is your launchpad. It’s a living system you can expand upon—integrating product analytics from Segment, marketing data from Hubspot, or support metrics from Zendesk. You’ve built the nerve center; now, you can start connecting the entire nervous system of your business to it. Welcome to the driver’s seat.