SaaS metrics

This tutorial calculates SaaS metrics that depend on time and then turns several operational signals into a reusable customer-health feature.

You will answer three questions: What was monthly recurring revenue on a chosen date? How was it split by segment? Which customers were at risk, and why?

This tutorial builds on the entity, binding, and RELATED() model from E-commerce . Run Data and Model before the metrics. If a notebook restarts, rerun those sections first.

Meet the SaaS data

The dataset follows five customers across enterprise and SMB segments. Each subscription row describes a plan period:

active_from is the first day the period contributes MRR.
inactive_on is the first day it no longer contributes.

Invoices and support tickets provide the payment and service signals used later.

FeatureQL

CREATE SCHEMA IF NOT EXISTS tutorial_saas;
--
DROP TABLE IF EXISTS tutorial_saas.support_tickets;
--
DROP TABLE IF EXISTS tutorial_saas.invoices;
--
DROP TABLE IF EXISTS tutorial_saas.subscriptions;
--
DROP TABLE IF EXISTS tutorial_saas.customers;
--
CREATE TABLE tutorial_saas.customers (
  id BIGINT,
  name VARCHAR,
  segment VARCHAR,
  created_at DATE
);
--
INSERT INTO tutorial_saas.customers VALUES
  (1, 'Acme', 'enterprise', DATE '2024-01-15'),
  (2, 'Beta', 'smb', DATE '2024-01-20'),
  (3, 'Gamma', 'enterprise', DATE '2024-02-10'),
  (4, 'Delta', 'smb', DATE '2024-03-01'),
  (5, 'Epsilon', 'smb', DATE '2024-03-15');
--
CREATE TABLE tutorial_saas.subscriptions (
  id BIGINT,
  customer_id BIGINT,
  plan VARCHAR,
  mrr BIGINT,
  started_at DATE,
  ended_at DATE,
  inactive_on DATE
);
--
INSERT INTO tutorial_saas.subscriptions VALUES
  (1, 1, 'pro', 500, DATE '2024-01-15', NULL, DATE '9999-12-31'),
  (2, 2, 'starter', 100, DATE '2024-01-20', DATE '2024-04-15', DATE '2024-04-15'),
  (3, 3, 'pro', 500, DATE '2024-02-10', NULL, DATE '9999-12-31'),
  (4, 4, 'starter', 100, DATE '2024-03-01', DATE '2024-05-20', DATE '2024-05-20'),
  (5, 5, 'starter', 100, DATE '2024-03-15', NULL, DATE '2024-06-01'),
  (6, 2, 'pro', 500, DATE '2024-06-01', NULL, DATE '9999-12-31'),
  (7, 5, 'pro', 300, DATE '2024-06-01', NULL, DATE '9999-12-31');
--
CREATE TABLE tutorial_saas.invoices (
  id BIGINT,
  subscription_id BIGINT,
  customer_id BIGINT,
  amount BIGINT,
  issued_at DATE,
  paid_at DATE
);
--
INSERT INTO tutorial_saas.invoices VALUES
  (1, 1, 1, 500, DATE '2024-02-01', DATE '2024-02-05'),
  (2, 2, 2, 100, DATE '2024-02-01', DATE '2024-02-10'),
  (3, 1, 1, 500, DATE '2024-03-01', DATE '2024-03-03'),
  (4, 2, 2, 100, DATE '2024-03-01', DATE '2024-03-15'),
  (5, 3, 3, 500, DATE '2024-03-01', DATE '2024-03-05'),
  (6, 1, 1, 500, DATE '2024-04-01', DATE '2024-04-02'),
  (7, 4, 4, 100, DATE '2024-04-01', DATE '2024-04-20'),
  (8, 3, 3, 500, DATE '2024-04-01', DATE '2024-04-03'),
  (9, 5, 5, 100, DATE '2024-04-01', DATE '2024-04-10'),
  (10, 1, 1, 500, DATE '2024-05-01', DATE '2024-05-04'),
  (11, 3, 3, 500, DATE '2024-05-01', DATE '2024-05-02'),
  (12, 4, 4, 100, DATE '2024-05-01', NULL);
--
CREATE TABLE tutorial_saas.support_tickets (
  id BIGINT,
  customer_id BIGINT,
  priority VARCHAR,
  opened_at DATE,
  resolved_at DATE
);
--
INSERT INTO tutorial_saas.support_tickets VALUES
  (1, 1, 'low', DATE '2024-02-10', DATE '2024-02-11'),
  (2, 2, 'high', DATE '2024-03-20', DATE '2024-03-25'),
  (3, 2, 'high', DATE '2024-04-01', NULL),
  (4, 4, 'critical', DATE '2024-04-15', DATE '2024-04-18'),
  (5, 5, 'low', DATE '2024-05-01', DATE '2024-05-02'),
  (6, 3, 'medium', DATE '2024-05-10', DATE '2024-05-12');
--
SELECT CAST(COUNT() AS INTEGER) AS cnt FROM tutorial_saas.customers;

Result

Count BIGINT
5

Nulls carry useful meaning here: an open ticket has no resolved_at, and an unpaid invoice has no paid_at. Keep those cases in mind when we build the health score.

Build the feature model

Declare customers, subscriptions, invoices, and tickets as entities with distinct typed keys:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SAAS AS
PERSIST
    customers := ENTITY(),
    subscriptions := ENTITY(),
    invoices := ENTITY(),
    tickets := ENTITY(),
    customer_id := INPUT(BIGINT#customers),
    subscription_id := INPUT(BIGINT#subscriptions),
    invoice_id := INPUT(BIGINT#invoices),
    ticket_id := INPUT(BIGINT#tickets)
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SAAS.CUSTOMERS	CREATED	Feature created as not exists
FM.SAAS.SUBSCRIPTIONS	CREATED	Feature created as not exists
FM.SAAS.INVOICES	CREATED	Feature created as not exists
FM.SAAS.TICKETS	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_ID	CREATED	Feature created as not exists
FM.SAAS.SUBSCRIPTION_ID	CREATED	Feature created as not exists
FM.SAAS.INVOICE_ID	CREATED	Feature created as not exists
FM.SAAS.TICKET_ID	CREATED	Feature created as not exists

Then map the source columns and annotate the customer foreign keys on subscriptions, invoices, and tickets:

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SAAS AS
PERSIST
    tables.customers := EXTERNAL_COLUMNS(
        id BIGINT#customers BIND TO customer_id,
        name VARCHAR,
        segment VARCHAR,
        created_at DATE
        FROM TABLE(tutorial_saas.customers)
    ),
    tables.subs := EXTERNAL_COLUMNS(
        id BIGINT#subscriptions BIND TO subscription_id,
        customer_id BIGINT#customers,
        plan VARCHAR,
        mrr BIGINT,
        started_at DATE,
        ended_at DATE,
        inactive_on DATE
        FROM TABLE(tutorial_saas.subscriptions)
    ),
    tables.invoices := EXTERNAL_COLUMNS(
        id BIGINT#invoices BIND TO invoice_id,
        subscription_id BIGINT#subscriptions,
        customer_id BIGINT#customers,
        amount BIGINT,
        issued_at DATE,
        paid_at DATE
        FROM TABLE(tutorial_saas.invoices)
    ),
    tables.tickets := EXTERNAL_COLUMNS(
        id BIGINT#tickets BIND TO ticket_id,
        customer_id BIGINT#customers,
        priority VARCHAR,
        opened_at DATE,
        resolved_at DATE
        FROM TABLE(tutorial_saas.support_tickets)
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SAAS.TABLES.CUSTOMERS	CREATED	Feature created as not exists
FM.SAAS.TABLES.SUBS	CREATED	Feature created as not exists
FM.SAAS.TABLES.INVOICES	CREATED	Feature created as not exists
FM.SAAS.TABLES.TICKETS	CREATED	Feature created as not exists

Those annotations give FeatureQL a safe path from a customer to each child population. The metrics can now compose on named features rather than repeat table joins.

Take an MRR snapshot

MRR is a snapshot, not a number you can add freely across dates. A subscription period counts when it has started by the as-of date and has not yet reached inactive_on.

Bind the snapshot date to the end of March 2024 and sum the qualifying plan amounts:

FeatureQL

DEFINE
    as_of := DATE '2024-03-31',
    started := tables.subs[started_at],
    inactive_on := tables.subs[inactive_on],
    mrr := tables.subs[mrr],
    contrib := IF(started <= as_of AND inactive_on > as_of, mrr, 0)
RETURN
    total_mrr := SUM(contrib)
FROM FM.SAAS
FOR
    subscription_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.subscriptions ORDER BY id)
    )
;

Result

TOTAL_MRR BIGINT
1300

The total is 1300:

Acme 500 + Beta 100 + Gamma 500 + Delta 100 + Epsilon 100

The as-of date is query-local in this example. The activity condition remains the same when the reporting period changes.

Break the snapshot out by segment

Move the snapshot to the end of April and bring each subscription's customer segment into the aggregation with RELATED():

FeatureQL

DEFINE
    as_of := DATE '2024-04-30',
    started := tables.subs[started_at],
    inactive_on := tables.subs[inactive_on],
    mrr := tables.subs[mrr],
    segment := RELATED(tables.customers[segment] VIA tables.subs[customer_id]),
    contrib := IF(started <= as_of AND inactive_on > as_of, mrr, 0)
RETURN
    segment,
    segment_mrr := SUM(contrib) GROUP BY (segment)
FROM FM.SAAS
FOR
    subscription_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.subscriptions ORDER BY id)
    )
ORDER BY segment
;

Result

SEGMENT VARCHAR	SEGMENT_MRR BIGINT
enterprise	1000
smb	200

The result is enterprise 1000 and SMB 200. Beta's subscription became inactive on April 15, so its 100 no longer contributes at month-end.

This is the key point-in-time pattern: changing the bound date reevaluates the same activity rule instead of creating a new metric for every month.

Turn operational signals into customer health

Now combine two different child populations:

support tickets tell us whether a customer has an unresolved issue;
invoices tell us their average time to pay.

Define at risk as an unresolved ticket or an average payment time above the 10-day SLA. Persist the factors and label together so other queries can reuse the same decision.

FeatureQL

CREATE OR REPLACE FEATURES IN FM.SAAS AS
PERSIST
    payment_sla_days := 10.00,
    customer_avg_days_paid_invoices := customer_id.RELATED(
        AVG(
            IF(
                tables.invoices[paid_at] IS NOT NULL,
                DATE_SUBTRACT(
                    tables.invoices[paid_at]::TIMESTAMP,
                    tables.invoices[issued_at]::TIMESTAMP,
                    'day'
                ),
                NULL::BIGINT
            )
        )
        GROUP BY (tables.invoices[customer_id])
    ),
    customer_open_ticket_count := customer_id.RELATED(
        SUM(IF(tables.tickets[resolved_at] IS NULL, 1, 0))
        GROUP BY (tables.tickets[customer_id])
    ),
    customer_avg_exceeds_payment_sla := customer_avg_days_paid_invoices::DECIMAL(10,2)
    > payment_sla_days::DECIMAL(10,2),
    customer_has_open_ticket := customer_open_ticket_count > 0,
    customer_at_risk := customer_has_open_ticket
    OR customer_avg_exceeds_payment_sla,
    customer_health_status := CASE
        WHEN customer_at_risk THEN 'at risk'
        ELSE 'healthy'
    END,
    customer_open_unresolved_ticket_id := customer_id.RELATED(
        MAX(
            IF(
                tables.tickets[resolved_at] IS NULL,
                KEY_TO_SCALAR(tables.tickets[id]),
                NULL::BIGINT
            )
        )
        GROUP BY (tables.tickets[customer_id])
    ),
    customer_open_unresolved_ticket_opened_at := customer_id.RELATED(
        MAX(
            IF(
                tables.tickets[resolved_at] IS NULL,
                tables.tickets[opened_at],
                NULL::DATE
            )
        )
        GROUP BY (tables.tickets[customer_id])
    )
;

Result

feature_name VARCHAR	status VARCHAR	message VARCHAR
FM.SAAS.PAYMENT_SLA_DAYS	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_AVG_DAYS_PAID_INVOICES	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_OPEN_TICKET_COUNT	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_AVG_EXCEEDS_PAYMENT_SLA	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_HAS_OPEN_TICKET	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_AT_RISK	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_HEALTH_STATUS	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_OPEN_UNRESOLVED_TICKET_ID	CREATED	Feature created as not exists
FM.SAAS.CUSTOMER_OPEN_UNRESOLVED_TICKET_OPENED_AT	CREATED	Feature created as not exists

Query the resulting label for every customer:

FeatureQL

DEFINE
    status := customer_health_status
RETURN
    customer_id,
    status
FROM FM.SAAS
FOR
    customer_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.customers ORDER BY id)
    ),
    NESTED invoice_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.invoices ORDER BY id)
    ),
    NESTED ticket_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.support_tickets ORDER BY id)
    )
ORDER BY customer_id
;

Result

FM.SAAS.CUSTOMER_ID BIGINT#FM.SAAS.CUSTOMERS	STATUS VARCHAR
1	healthy
2	at risk
3	healthy
4	at risk
5	healthy

Beta is at risk because of an open ticket. Delta is at risk because of slow payment. Acme, Gamma, and Epsilon are healthy.

The label is useful for filtering, but a human also needs to understand it. Select the contributing factors beside the decision:

FeatureQL

DEFINE
    factor := CASE
        WHEN customer_open_ticket_count > 0 THEN 'open_ticket'
        WHEN customer_avg_exceeds_payment_sla THEN 'slow_payment'
        ELSE NULL::VARCHAR
    END,
    detail_value := CASE
        WHEN customer_open_ticket_count > 0 THEN customer_open_unresolved_ticket_id::VARCHAR
        WHEN customer_avg_exceeds_payment_sla THEN customer_avg_days_paid_invoices::BIGINT::VARCHAR
        ELSE NULL::VARCHAR
    END,
    explanation := CASE
        WHEN customer_open_ticket_count > 0 THEN 'unresolved support ticket (id '
        || customer_open_unresolved_ticket_id::VARCHAR
        || ', opened '
        || customer_open_unresolved_ticket_opened_at::VARCHAR
        || ')'
        WHEN customer_avg_exceeds_payment_sla THEN 'average days to pay ('
        || customer_avg_days_paid_invoices::BIGINT::VARCHAR
        || ') exceeds payment SLA ('
        || payment_sla_days::BIGINT::VARCHAR
        || ')'
        ELSE NULL::VARCHAR
    END
RETURN
    customer_id,
    factor,
    detail_value,
    explanation
FROM FM.SAAS
FOR
    customer_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.customers WHERE id IN (2, 4) ORDER BY id)
    ),
    NESTED invoice_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.invoices ORDER BY id)
    ),
    NESTED ticket_id := BIND_COLUMNS(
        id
        FROM SQL(SELECT id FROM tutorial_saas.support_tickets ORDER BY id)
    )
ORDER BY customer_id
;

Result

FM.SAAS.CUSTOMER_ID BIGINT#FM.SAAS.CUSTOMERS	FACTOR VARCHAR	DETAIL_VALUE VARCHAR	EXPLANATION VARCHAR
2	open_ticket	3	unresolved support ticket (id 3, opened 2024-04-01)
4	slow_payment	19	average days to pay (19) exceeds payment SLA (10)

The explanation points to ticket 3 for Beta and 19 average days to pay for Delta. Because the factors are persisted features, the status and its explanation cannot drift into separate implementations.

What's next

You have built a point-in-time measure, grouped it through an entity relationship, and composed several operational signals into an explainable score.

Continue with Supply inventory to reconstruct a snapshot from signed events, or return to the analytics overview to choose another technique.

Last update at: 2026/10/13 08:32:21

On this page

Meet the SaaS data Build the feature model Take an MRR snapshot Break the snapshot out by segment Turn operational signals into customer health What's next