Case study

One set of subscription metrics for finance, product and customer teams

Internet and IT

An internet company whose finance, product and customer teams each reported from their own spreadsheets. All 3 now read one governed Power BI model with a shared subscription and revenue KPI layer.

Read the engagement brief(opens in a new tab)
Period
19 months
Company
50–250 people
Focus
Subscription revenue
A schematic: the separate reports this engagement started from, the one consolidated reporting layer that replaced them, and the technologies underneath.

3 sets of spreadsheets, replaced by one model

Finance, product and customer teams each kept their own spreadsheets. We built the client’s first governed Power BI environment on one model of billing, CRM and product usage.

source systems in one model
3
teams reading it
3
subscription KPI families
4
months of engagement
19

No outcome figures are published for this engagement: nothing exists that would let you check them, so what was built is described in full instead. Anonymised by sector and size, because the confidentiality that protects this client would protect you too. Sector and outcomes are real; identifying detail is not.

Before and after

Before and after the engagement

3 sets of spreadsheets

  • Finance spreadsheetsTheir own reading of revenue
  • Product spreadsheetsBuilt separately, prepared by hand
  • Customer spreadsheetsA third count of the same accounts

One model

  • One semantic modelBilling, CRM and product usage combined
  • Power BI for all 3 teamsThe same definitions in every report
From 3 teams reporting from their own spreadsheets to one governed model they all read.

The engagement in full

Client
An internet company of 50 to 250 people with subscription revenue.
Situation
Each team had its own reading of revenue, prepared by hand, so recurring revenue meant one thing in finance and another in product, and no conversation could start from an agreed number.
Mandate
Replace separate spreadsheet reporting with one governed Power BI environment and one definition of each subscription metric.

The subscription KPI layer

Recurring revenue, cohort retention, expansion and contraction are each defined once in the model, with standardised time intelligence, so finance, product and customer teams read the same figure for the same period. Cohort retention reads as one row per starting group and one column per period since.

Refresh and delivery, automated

Preparation that used to precede every report now runs on its own.

  1. SourcesBilling, CRM, usage
  2. DataflowsScheduled refresh
  3. SQLShaped for the model
  4. ReportsPrepared automatically

The architecture

Sources: Billing · CRM · Product usage. Preparation: Dataflows · SQL transformations. Semantic model: Subscription KPI layer · Time intelligence. Teams: Finance · Product · Customer
Billing, CRM and product usage are prepared automatically and combined in one semantic model, which all 3 teams read.

Architecture decisions

DecisionRejected alternativeWhy
One model across billing, CRM and usageA model per teamAll 3 teams read the same customers and revenue.
Definitions in a KPI layerFormulas in each spreadsheetRecurring revenue means one thing wherever it appears.
Workspace, naming and role-based access set up firstAccess granted case by caseThe environment stays governed as it grows.

What the client has today

  • A first governed Power BI environment, with role-based access, naming rules and user training.
  • One semantic model of billing, CRM and product usage, with reusable definitions.
Where it left them
3 teams answer subscription questions from one model, with one definition of each metric.
Stack
  • Power BI
  • DAX
  • SQL
  • Dataflows
  • Azure Data Factory
  • Semantic modelling
All case studies

The other engagements

  • Internet and IT

    Fragmented customer data, moved to one warehouse that watches churn

    An internet company of 250 to 500 people with several business units, each holding its own copy of the customer data.

    • Snowflake
    • Databricks
    • Azure Data Factory
    • Power BI
    • SQL

  • Retail

    Foundation then Activation then Acceleration alongside Stewardship

    Store revenue, executive questions and forecasting on one set of definitions

    A retailer with Microsoft Fabric already in production, semantic models over Direct Lake and store revenue streaming in, who wanted forecasting, Copilot questions and anomaly detection on top of it.

    • Microsoft Fabric
    • Power BI semantic models
    • Direct Lake
    • Copilot in Power BI
    • Fabric AI Functions
    • Activator

  • Industrial manufacturing

    Foundation then Activation then Acceleration

    Governed reporting across a production network

    An industrial manufacturer reporting across a production network of sites, each with its own view of what was running.

    • Microsoft Fabric
    • OneLake
    • OneLake Security
    • Direct Lake
    • Spark
    • MES
    • OPC UA
    • Power BI
    • DAX
    • Azure Key Vault
    • Purview
    • Azure DevOps

  • Hospitality and real estate

    Foundation then Activation

    A CFO cockpit over a group that closed its books separately

    A holding group across hospitality and real estate, with entities that each closed their own books and a CFO who had to answer for all of them.

    • Microsoft Fabric
    • OneLake
    • Direct Lake
    • Databricks Mirrored Catalog
    • Fabric Data Factory
    • Power BI
    • DAX
    • Calculation groups
    • Row-level security
    • Azure DevOps
    • Azure Key Vault
    • Purview
All case studies

Free consultation

Know where your data stands in an hour.

A free 60-minute consultation. No pitch, no invoice. You’ll leave knowing whether your data is ready for AI, and whether we’re a good fit.

Book your free consultation(opens in a new tab)