Skip to content
All case studies
B2B SaaS Revenue / ARR Data modelling

The ARR model the board runs on

ARR was pieced together from billing exports and disagreed with itself across teams. Built a from-scratch, net-portfolio ARR model finance and the board now run on.

1

ARR source of truth, agreed definitions

Net-portfolio

new / expansion / contraction / churn, by month

GRR + NRR

from the same model, same definitions

2-hourly

current-year refresh, history frozen yearly

The client

A B2B SaaS company on a recurring-revenue model, with multiple product lines and pricing tiers, selling across several business units and regions.

The problem

ARR was assembled by hand from billing exports and a patchwork of spreadsheets. Different teams quoted different ARR, net retention couldn't be computed consistently, and a pricing-model change had split revenue across two systems. The board needed one ARR number with definitions everyone trusted - and the movement behind it: new, expansion, contraction, churn.

How it ran

A layered model with one set of definitions, fresh enough to trust and cheap enough to keep.

Step 1

Layered model

A normalised base, a product / business-unit / month taxonomy, and a reporting layer the dashboards read.

Step 2

Net-portfolio logic

Every month's ARR change decomposed into new, expansion, contraction and churn - the movement, explainable.

Step 3

Two models, one ARR

Reconciled the legacy and new pricing models into one consistent ARR, with a clean reclassification for migrated accounts.

Step 4

Fresh + frozen

A current-year pipeline refreshed every two hours; last year's numbers frozen once per year so history stays stable.

Step 5

Forward previews

Signed billing proposals fed an ARR preview, so the trend shows where ARR is heading as well as where it has been.

Step 6

Cheap & self-serve

Materialised the heaviest model and gave finance a one-click refresh, so it stays fast without warehouse access.

What I did

  • 01.Designed a layered ARR model: a normalised base, a product / business-unit / month taxonomy, and a reporting layer the dashboards read.
  • 02.Built net-portfolio logic that decomposes every month's ARR change into new, expansion, contraction and churn, so the movement behind the total is explainable.
  • 03.Reconciled the legacy and new pricing models into one consistent ARR, with a clean reclassification for accounts migrated between them.
  • 04.Split the pipeline: a current-year model refreshed every two hours for freshness, and a yearly snapshot that freezes prior years so history stays stable.
  • 05.Added forward-looking ARR previews from signed billing proposals, so the trend shows where ARR is heading as well as where it has been.
  • 06.Derived GRR and NRR straight from the net-portfolio deltas, so retention metrics share one definition with the ARR they come from.
  • 07.Converted the heaviest model from a live view to a scheduled-query-materialised table, cutting dashboard cost and load time.
  • 08.Gave finance a one-click refresh (a sheet-button trigger) to rebuild the table on demand without warehouse permissions.

InteractiveThe net-portfolio bridge

Move the deltas - watch ARR, GRR and NRR

Sample numbers, illustrative only

End ARR

GRR

NRR

Result

What finance and the board got out of it.

One ARR number

Finance, sales and the board read the same ARR from the same model with the same definitions. The recurring "whose number is right?" debate ended.

Movement, explained

Every shift in ARR breaks into new, expansion, contraction and churn. GRR and NRR fall straight out of the same deltas, with one shared definition.

Fresh and stable at once

The current year refreshes every two hours; prior years are frozen. Plus a forward preview from signed proposals, so the trend looks ahead.

Cheap to keep

Materialising the heaviest model cut dashboard cost and load time, and finance can rebuild it themselves with one click. It stays maintained.

What the team could run afterwards

At the start, the in-house analyst could read the SQL but not own the model. By the end of the engagement they were running the monthly refresh, extending the net-portfolio logic when pricing changed, and answering the board's ARR questions without me in the room. The model was built to be handed over, and it was: the documentation and the one-click finance refresh mean it keeps running in the team's own hands.

Tools used

BigQuery SQL Looker Scheduled queries Apps Script
"Bernadett is brilliant at what she does, showcasing exceptional skills in SQL, Looker modelling, and dashboarding. Her communication is top-notch, and she maintains full transparency in all her work, making her a true asset to any team. With Bernadett on your side, you can expect outstanding results and a seamless collaboration experience."

Laura Simons

Head of Finance, Silverfin

A similar problem in your stack?

Send me the rough shape of it. I'll figure out scope on a 30-min call.

Book a call