SaaS data model: a free subscription analytics template

A SaaS data model is the set of tables – accounts, subscriptions, invoices, usage – and the keys that join them into something you can answer revenue questions from. Get the subscription part right and net revenue retention, the MRR waterfall, and "which usage predicts churn" are one query away. Get it wrong and every board deck becomes a reconciliation exercise.

This page gives you a free, ready-made SaaS data model you can open in your browser, edit like a diagram, and export to OKF – Google's open, portable format. No sign-up. It's one of nine in our data model template gallery; this one is built for subscription analytics.

‍

What a SaaS data model is, and the three things people mean

Search "SaaS data model" and you get three different answers, because the phrase hides three jobs.

The first is the multi-tenant app schema – how you store tenants, orgs, users, and permissions so the product runs. The second is a GTM metrics framework – the conceptual volume/conversion/time metrics behind a go-to-market motion. The third, and the one analysts actually build, is the subscription-analytics model: the warehouse tables you compute MRR, churn, and retention from.

This template is that third thing. It's a dimensional model – facts surrounded by dimensions – with one design decision at its heart: subscription change is captured as events, not monthly snapshots. If the vocabulary is new, start with what data modeling is, and for the product-team case, see why every SaaS product team needs a data model.

The SaaS template, mart by mart

The template is six data marts – two dimensions and four facts – wired around subscription revenue. Here's the whole thing.

‍

Entity relationship diagram of a SaaS data model: Account and User dimensions joined to Subscription Events, Invoices, Usage, and Support Tickets fact tables, centered on the MRR-movement event table. ‍

Open the SaaS model in the canvas →

•  Account (dimension) – one row per customer account: plan, segment, region, signup date.

•  User (dimension) – one row per seat/user, joined to Account, for activation and engagement.

•  Subscription Events (fact) – the centerpiece: one row per MRR movement (new, expansion, contraction, churn, reactivation).

•  Invoices (fact) – one row per invoice/line: billed amount, period, status – the cash view next to the recognized-MRR view.

•  Usage (fact) – one row per account-day (or user-day) of product usage, the leading indicator for churn and expansion.

•  Support Tickets (fact) – one row per ticket, joined to Account, for the CSAT-vs-renewal question.

The joins make it a star: User → Account, Subscription Events → Account, Invoices → Account, Usage → Account and Usage → User, Support Tickets → Account. Every table is a reporting-ready data mart in the sense we describe in our approach to data marts.

The MRR-movement table (the centerpiece)

If you take one idea from this template, take this: model subscription change as a stream of movements, not a monthly balance.

A row in Subscription Events is a single change to recurring revenue – a new subscription, an expansion, a contraction, a churn, a reactivation – each with an account, a date, a type, and a signed MRR delta. From that one table you can reconstruct the MRR waterfall for any period (new + expansion − contraction − churn), and compute net and gross revenue retention by summing deltas per cohort.

A monthly-snapshot table can't do this cleanly – it tells you the balance changed but not why. The event grain keeps the reason, which is the whole point. This is a classic transaction-grain fact table; for the grain choices behind it, see the three types of fact tables and understanding star schema.

What this model answers

Because the events carry the reason and the dimensions carry the context, the hard SaaS questions become joins, not projects:

•  Net & gross revenue retention – sum signed MRR deltas per cohort over time.

•  The MRR waterfall – new / expansion / contraction / churn for any month or segment.

•  What usage predicts churn – Usage joined to the churn events that follow it.

•  CSAT vs. renewal – Support Tickets joined to Account against subsequent churn/expansion.

•  Quick ratio & expansion rate – straight from the movement types.

None of these need a new table. They're different paths across the same six data marts.

Analytics model vs. the multi-tenant app schema

A common first question: "where are the tenants, orgs, and permissions?" Those belong to the multi-tenant app schema that runs the product – a separate, normalized model. The analytics model sits downstream and starts at the account and the subscription event, because that's the grain reporting needs.

How to open and customize the template

Opening it and shaping it to your business takes about two minutes, then as long as you want to refine.

(1)  Open it. Use the link under the diagram above – loads in your browser, no sign-up.

(2)  Reshape it. Add fields (plan tier, billing interval, CAC), split usage by feature, or add a Cohorts dimension; redraw joins on the canvas.

(3)  Set grain and keys. Confirm Subscription Events is one row per movement and the keys that tie each fact to Account.

(4)  Export it. Use Export → OKF for a portable model, or grab a diagram image. Keep the OKF in git or push it into OWOX Data Marts to make it live in your warehouse.

Comparing tools while you're here? Our roundup of free database diagram design tools puts the canvas in context.

Export to OKF: a portable SaaS model

The reason this beats a static ER picture is what happens after the diagram. A drawing can't be diffed or fed to a warehouse.

This template exports to OKF (Open Knowledge Format), Google's open, markdown-based standard. Because it's plain text, you can keep the model in git, review it in a pull request, and hand it off without lock-in. New to it? See our explainer on what OKF is, then open the SaaS model and export your own.

‍