NRR & Renewal Rate — Technical Documentation

# NRR & Renewal Rate — Technical Documentation

Table of Contents

• Overview
• Architecture
  • Layers and File Map
  • Data Flow Diagram
• Database Entity
  • InvoiceMonthlyCalculation
  • ItemId Filter
  • Type Column
• Controller: MRRController
  • Index Action
  • GetRenewalRate Action
• Service: GetRenewalRate Deep Dive
  • Period Calculation
  • Data Query
  • Client Classification Algorithm
  • KPI Formulas
• ViewModels
  • RenewalRateViewModel
  • RenewalRateClientViewModel
• Frontend: Index.cshtml Renewal Rate Tab
  • HTML Structure
  • JavaScript: loadRenewalRate
  • Date Range Picker
  • Gauge Charts
  • Category Breakdown
  • Client Grid
• End-to-End Request Trace

---

Overview

The NRR (Net Revenue Retention) module — surfaced as the Renewal Rate tab on the MRR page — analyses month-over-month Monthly Recurring Revenue (MRR) changes per client across two equal-length time periods. It classifies every client into one of six categories and computes churn, renewal, upgrade, downgrade, and new-business metrics.

The module answers the following business questions:

Metric	What it measures
Renewal Rate	Percentage of base MRR retained (excluding churned revenue)
Gross Churn	Percentage of base MRR lost to churn
Customer Churn	Percentage of customer count lost to churn
BASE	Total MRR from the previous period — the baseline
RENEWAL	MRR from clients whose spend stayed flat
UPGRADE	MRR increase from clients who spent more
NEW BIZ	MRR from brand-new clients (not in previous period)
DOWNGRADE	MRR decrease from clients who spent less
CHURN	MRR lost from clients who disappeared

---

Architecture

Layers and File Map

┌─────────────────────────────────────────────────────┐
│  PRESENTATION                                       │
│  Sodastream/Web/Web/Views/MRR/Index.cshtml          │
│  Sodastream/Web/Web/Controllers/MRRController.cs    │
└────────────────────────┬────────────────────────────┘
                         │
┌────────────────────────▼────────────────────────────┐
│  SERVICE LAYER                                      │
│  Services/Services/InvoiceMonthlyCalculationService │
│  Services/Services/Interfaces/                      │
│    IInvoiceMonthlyCalculationService.cs             │
└────────────────────────┬────────────────────────────┘
                         │
┌────────────────────────▼────────────────────────────┐
│  DATA LAYER                                         │
│  SoProEntities/InvoiceMonthlyCalculation/           │
│    InvoiceMonthlyCalculation.cs  (EF entity)        │
│  SoProData/IDbContext  (EF DbContext)               │
└────────────────────────┬────────────────────────────┘
                         │
┌────────────────────────▼────────────────────────────┐
│  SHARED MODELS                                      │
│  SharedModels/MRR/RenewalRateViewModel.cs           │
│  SharedModels/MRR/RenewalRateClientViewModel.cs     │
│  SharedModels/MonthlyCostDTO.cs                     │
└─────────────────────────────────────────────────────┘

Data Flow Diagram

---

Database Entity

InvoiceMonthlyCalculation

The entire NRR module reads from a single materialised table InvoiceMonthlyCalculation. This table is pre-computed (likely via a scheduled process) and stores one row per invoice-item per month, with MRR values already calculated.

File: SoProEntities/SoProEntities/InvoiceMonthlyCalculation/InvoiceMonthlyCalculation.cs

Column	Type	Description
Id	int	Primary key
InvoiceId	int	FK → Invoice
InvoiceItemId	int	FK → InvoiceItem
ClientId	int	FK → Client — the customer
CampaignId	int	FK → Campaign
InvoiceType	string	e.g. "Invoice", "CreditNote"
IssueDate	DateTime	Date the invoice was issued
XeroInvoiceNumber	string	Xero reference
Quantity	double	Line-item quantity
Price	double	Unit price
Discount	double	Discount applied
CurrencyRatePoundToOther	double	FX rate GBP → invoice currency
BillingPeriodFrom	DateTime?	Start of billing period
BillingPeriodTo	DateTime?	End of billing period
CalcucatedAmountGBP	double	Computed column — amount in GBP
CalcucatedAmount	double	Computed column — amount in original currency
PartBulk	bool	Whether this is a bulk-partitioned row
PartDevide	int	Divisor for apportioning across months
ItemId	int	FK → Item (the product/SKU)
Description	string	Line-item description
InvoiceNumberForCreditNote	string	Credit note reference
InvoiceNumberForCreditNoteDate	DateTime?	Credit note date
Year	int?	Calendar year of the MRR month
Month	int?	Calendar month (1–12) of the MRR month
Days	int?	Days in the billing period
DaysDevider	int?	Divisor for day-level proration
Type	int?	Calculation type (see below)

ItemId Filter

All NRR queries filter by a hardcoded list of ItemId values:

var itemids = new List<int>() { 2, 3, 8, 9, 12, 13, 14, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29 };

These represent the product/SKU IDs that count toward MRR (excluding non-recurring items like one-off services).

Type Column

The Type column selects the MRR calculation variant:

Value	Meaning	Used by NRR?
1	Total (Revenue)	No
2	Year/Month Custom Currency Revenue	Yes
3	Year/Month GBP Currency Revenue	No
4	Year/Month Custom Currency MRR	No
5	Year/Month GBP Currency MRR	No

The NRR module always uses Type = 2 (hardcoded as int type = 2; // MRR in GetRenewalRate).

---

Controller: MRRController

File: Sodastream/Web/Web/Controllers/MRRController.cs

Index Action

public async Task<ActionResult> Index(int? ClientId)

1. Reads MRRType from session (defaults to 1 = Revenue).
2. Calls GetMonthlyCost(Type, ClientId) → populates the C3.js line chart with monthly totals.
3. Calls GetClientLastInvoiceDates() → populates the client filter dropdown.
4. If a ClientId is selected, calls GetDetails(Type, ClientId) → populates the Details tab table.
5. Calls GetMissing(ClientId) → populates the "Invoices Missing Billing Date" tab table.
6. Returns the view with List<MonthlyCostDTO> as the model.

The Renewal Rate tab is not populated on page load — it loads via AJAX when the tab is first activated.

GetRenewalRate Action

[HttpGet]
public JsonResult GetRenewalRate(DateTime from, DateTime to)
{
    var model = _invoiceMonthlyCalculationService.GetRenewalRate(from, to);
    return Json(model, JsonRequestBehavior.AllowGet);
}

• Receives from and to as query-string parameters (formatted YYYY-MM-DD by the JavaScript date picker).
• Delegates entirely to the service.
• Returns RenewalRateViewModel serialised as JSON.

---

Service: GetRenewalRate Deep Dive

File: Services/Services/InvoiceMonthlyCalculationService.cs — method GetRenewalRate(DateTime from, DateTime to)

Period Calculation

The user selects a date range. The service constructs two equal-length periods:

User selects:  from = 2025-01-01, to = 2025-06-30
                     ↓
Current period:   2025-01 through 2025-06  (6 months)
Previous period:  2024-07 through 2024-12  (6 months, equal length)

int periodMonths = (currentEndYear - currentStartYear) * 12
                 + (currentEndMonth - currentStartMonth) + 1;

var prevTo = from.AddDays(-1);                              // day before current start
var prevFrom = prevTo.AddMonths(-(periodMonths - 1));       // equal-length window
prevFrom = new DateTime(prevFrom.Year, prevFrom.Month, 1);  // snap to 1st of month

Data Query

A single LINQ query fetches all rows for both periods at once:

var allData = _dataContext.InvoiceMonthlyCalculation
    .Where(x => itemids.Contains(x.ItemId) && x.Type == type
                && x.Year.HasValue && x.Month.HasValue)
    .Where(x =>
        // Current period OR Previous period
        (x.Year.Value > currentStartYear
            || (x.Year.Value == currentStartYear && x.Month.Value >= currentStartMonth))
        && (x.Year.Value < currentEndYear
            || (x.Year.Value == currentEndYear && x.Month.Value <= currentEndMonth))
        ||
        (x.Year.Value > prevStartYear
            || (x.Year.Value == prevStartYear && x.Month.Value >= prevStartMonth))
        && (x.Year.Value < prevEndYear
            || (x.Year.Value == prevEndYear && x.Month.Value <= prevEndMonth))
    )
    .Select(x => new {
        x.ClientId,
        ClientName = x.Client.ClientName,
        x.Year,
        x.Month,
        MrrPart = x.CalcucatedAmountGBP / x.PartDevide
    })
    .ToList();

Key detail — MrrPart: The MRR contribution of each row is computed as:

$$MrrPart = \frac{CalcucatedAmountGBP}{PartDevide}$$

This apportions a single invoice amount across multiple months (e.g. a £1,200 annual invoice with PartDevide = 12 contributes £100/month).

The result set is then split in-memory into currentPeriodData and previousPeriodData using the same date-range predicates.

Client Classification Algorithm

Step 1: Group all rows by ClientId and sum MrrPart to get total MRR per client per period:

var currentByClient = currentPeriodData
    .GroupBy(x => new { x.ClientId, x.ClientName })
    .ToDictionary(g => g.Key.ClientId,
        g => new { Name = g.Key.ClientName, Mrr = Math.Round(g.Sum(x => x.MrrPart), 2) });

var previousByClient = previousPeriodData
    .GroupBy(x => new { x.ClientId, x.ClientName })
    .ToDictionary(g => g.Key.ClientId,
        g => new { Name = g.Key.ClientName, Mrr = Math.Round(g.Sum(x => x.MrrPart), 2) });

Step 2: Walk every client in the previous period and compare against the current period:

Step 3: Walk every client in the current period — those not in the previous period are NEW BIZ:

The classification uses these delta thresholds:

Category	Condition	Value tracked
Upgrade	delta > 0	upgradeValue += delta
Downgrade	delta < 0	downgradeValue += delta (negative)
Renewal	delta == 0	renewalValue += curr.Mrr
Churn	Was in previous, not in current	churnValue += -prev.Mrr (negative)
New Biz	In current, not in previous	newBizValue += curr.Mrr

Important: downgradeValue and churnValue are stored as negative numbers. The UI uses Math.abs() when displaying them.

KPI Formulas

// Base = the total MRR of all clients in the previous period
double baseValue = previousByClient.Values.Sum(x => x.Mrr);
int baseCount = previousByClient.Count;

// Total portfolio MRR
double totalMrrCurrent = currentByClient.Values.Sum(x => x.Mrr);
double totalMrrPrevious = baseValue;

// === GAUGE METRICS ===

// Renewal Rate = (Base - |Churn|) / Base × 100
// Measures what % of baseline MRR was retained
double renewalRatePercent = baseValue > 0
    ? Math.Round((baseValue - Math.Abs(churnValue)) / baseValue * 100, 0)
    : 0;

// Gross Churn = |ChurnValue| / Base × 100
// Measures what % of baseline MRR was lost
double grossChurnPercent = baseValue > 0
    ? Math.Round(Math.Abs(churnValue) / baseValue * 100, 0)
    : 0;

// Customer Churn = ChurnCount / BaseCount × 100
// Measures what % of customers left entirely
double customerChurnPercent = baseCount > 0
    ? Math.Round((double)churnCount / baseCount * 100, 0)
    : 0;

// === SUMMARY STATS ===

double avgRevenueCurrent = customerCountCurrent > 0
    ? Math.Round(totalMrrCurrent / customerCountCurrent, 2) : 0;
double avgRevenuePrevious = customerCountPrevious > 0
    ? Math.Round(totalMrrPrevious / customerCountPrevious, 2) : 0;

Mathematical relationships:

$$\text{Renewal Rate} = \frac{\text{Base} - |\text{Churn}|}{\text{Base}} \times 100%$$

$$\text{Gross Churn} = \frac{|\text{Churn}|}{\text{Base}} \times 100%$$

$$\text{Customer Churn} = \frac{\text{Churn Count}}{\text{Base Count}} \times 100%$$

$$\text{NRR (implied)} = \frac{\text{Total MRR Current}}{\text{Total MRR Previous}} = \frac{\text{Base} + \text{Upgrade} + \text{NewBiz} + \text{Downgrade} + \text{Churn}}{\text{Base}}$$

---

ViewModels

RenewalRateViewModel

File: SharedModels/MRR/RenewalRateViewModel.cs

Property	Type	Description
RenewalRatePercent	double	0–100 gauge value
GrossChurnPercent	double	0–100 gauge value
CustomerChurnPercent	double	0–100 gauge value
AvgRevenueCurrent	double	Average MRR per customer in current period (£)
AvgRevenuePrevious	double	Average MRR per customer in previous period (£)
AvgRevenueDelta	double	Change in average revenue per customer
CustomerCountCurrent	int	Number of active customers in current period
CustomerCountPrevious	int	Number of customers in previous period
CustomerCountDelta	int	Net change in customer count
TotalMrrCurrent	double	Sum of all MRR in current period
TotalMrrPrevious	double	Sum of all MRR in previous period
TotalMrrDelta	double	Net change in total portfolio MRR
BaseValue	double	Total MRR of all clients in previous period
BaseCount	int	Number of clients in previous period
RenewalValue	double	MRR from flat-renewal clients
RenewalCount	int	Count of flat-renewal clients
UpgradeValue	double	MRR increase from upgrades
UpgradeCount	int	Count of upgrading clients
NewBizValue	double	MRR from entirely new clients
NewBizCount	int	Count of new-business clients
DowngradeValue	double	MRR decrease from downgrades (negative)
DowngradeCount	int	Count of downgrading clients
ChurnValue	double	MRR lost from churned clients (negative)
ChurnCount	int	Count of churned clients
Clients	List<RenewalRateClientViewModel>	Per-client detail rows

RenewalRateClientViewModel

File: SharedModels/MRR/RenewalRateClientViewModel.cs

Property	Type	Description
ClientId	int	Client primary key
ClientName	string	Display name
Category	string	"Renewal", "Upgrade", "Downgrade", "Churn", "New Biz"
PreviousMrr	double	Total MRR in the previous period (£)
CurrentMrr	double	Total MRR in the current period (£)
Delta	double	CurrentMrr - PreviousMrr

---

Frontend: Index.cshtml Renewal Rate Tab

File: Sodastream/Web/Web/Views/MRR/Index.cshtml — lines ~165–265 (HTML) and ~400–590 (JavaScript)

HTML Structure

The Renewal Rate tab (#renewalRate) is the third tab in a Bootstrap nav-tabs component. It contains:

┌─ Date Range Picker ──────────────────────────────────┐
│  [________________________] (default: this month)     │
└──────────────────────────────────────────────────────┘

┌─ Gauge Charts Row ───────────────────────────────────┐
│  ┌──────────┐  ┌──────────┐  ┌──────────────────┐    │
│  │  RENEWAL  │  │  GROSS   │  │    CUSTOMER      │    │
│  │   RATE    │  │  CHURN   │  │     CHURN        │    │
│  │  (gauge)  │  │  (gauge) │  │    (gauge)       │    │
│  └──────────┘  └──────────┘  └──────────────────┘    │
└──────────────────────────────────────────────────────┘

┌─ Summary Stats Row ──────────────────────────────────┐
│  Avg revenue/customer went from X to Y    ▲ +Z       │
│  Number of customers went from X to Y     ▲ +Z       │
│  Total portfolio MRR from X to Y          ▲ +Z       │
└──────────────────────────────────────────────────────┘

┌─ Category Breakdown Row ─────────────────────────────┐
│  BASE (295)  RENEWAL (213)  UPGRADE (11)  ...        │
│  £842.27k    £630.45k       £13.53k                  │
│                                                       │
│  NEW BIZ (27)  DOWNGRADE (20)  CHURN (51)             │
│  £75.04k       -£32.16k        -£104.13k              │
└──────────────────────────────────────────────────────┘

┌─ Client Detail Grid ─────────────────────────────────┐
│  Client Name  | Category    | Prev MRR | Curr MRR | Δ│
│  ─────────────────────────────────────────────────── │
│  Acme Corp    | upgrade     | 500.00   | 750.00   |… │
│  Beta Ltd     | churn       | 300.00   | 0.00     |… │
│  ...                                                │
└──────────────────────────────────────────────────────┘

JavaScript: loadRenewalRate

The core function loadRenewalRate(from, to) performs an AJAX GET to /MRR/GetRenewalRate:

function loadRenewalRate(from, to) {
  $.ajax({
    url: '@Url.Action("GetRenewalRate", "MRR")',
    type: "GET",
    data: { from: from, to: to },
    success: function (data) {
      // 1. Update gauge charts
      // 2. Update summary stats
      // 3. Update category breakdown cards
      // 4. Rebuild client grid DataTable
    },
  });
}

Date Range Picker

Initialised with moment().startOf('month') to moment().endOf('month') as the default range. Preset ranges include:

Preset	Range
Today	today → today
Yesterday	yesterday → yesterday
Last 7 Days	6 days ago → today
Last 30 Days	29 days ago → today
Last 90 Days	89 days ago → today
Last 12 Months	12 months ago (1st) → today (end)
This Month	1st of month → end of month
Next Month	1st of next month → end of next month
Last Month	1st of last month → end of last month
This Quarter	1st of quarter → end of quarter

On date selection, loadRenewalRate(start.format('YYYY-MM-DD'), end.format('YYYY-MM-DD')) is called.

Lazy-loading is implemented via a Bootstrap tab event:

$('a[href="#renewalRate"]').on("shown.bs.tab", function () {
  var picker = $("#renewalDateRange").data("daterangepicker");
  loadRenewalRate(picker.startDate.format("YYYY-MM-DD"), picker.endDate.format("YYYY-MM-DD"));
});

Gauge Charts

Three C3.js gauge charts using colour thresholds:

Gauge	Max	Thresholds	Colours
Renewal Rate	100	[30, 60, 85, 100]	Red → Amber → Green → Blue
Gross Churn	100	[10, 30, 100]	Green → Amber → Red
Customer Churn	100	[10, 30, 100]	Green → Amber → Red

Charts are created once and updated in-place on subsequent calls via gauge.load().

Category Breakdown

Each category card displays the formatted value and count. The formatMoney helper converts raw numbers to display format:

function formatMoney(val) {
  var absVal = Math.abs(val);
  var formatted;
  if (absVal >= 1000) {
    formatted = (absVal / 1000).toFixed(2) + "k";
  } else {
    formatted = absVal.toFixed(2);
  }
  return (val < 0 ? "-" : "") + formatted + "£";
}

Category colours (used in the client grid badges):

Category	Colour	Hex
Renewal	Grey	#999999
Upgrade	Blue	#5b9bd5
New Biz	Green	#70ad47
Downgrade	Amber	#ffc000
Churn	Red	#ff0000

Client Grid

A DataTable (#dt-renewal-clients) displays every classified client with columns:

1. Client Name — plain text
2. Category — coloured badge (<span class="label">)
3. Previous MRR (£) — toFixed(2)
4. Current MRR (£) — toFixed(2)
5. Delta (£) — toFixed(2)

The table is destroyed and recreated on each AJAX response to reflect the new data. Default sort is by Category (column index 1, ascending).

---

End-to-End Request Trace

Below is the complete call chain for a typical Renewal Rate interaction:

1. User navigates to /MRR/Index
   ├── MRRController.Index(ClientId: null)
   │   ├── GetMonthlyCost(Type=1, ClientId=null)
   │   │   └── SELECT Year, Month, SUM(CalcucatedAmountGBP / PartDevide)
   │   │       FROM InvoiceMonthlyCalculation
   │   │       WHERE ItemId IN (2,3,8,9,12,13,14,18,19,20,21,22,23,24,25,26,27,28,29)
   │   │         AND Type = 1
   │   │       GROUP BY Year, Month
   │   │       ORDER BY Year DESC, Month DESC
   │   ├── GetClientLastInvoiceDates() → populates dropdown
   │   ├── GetMissing(null) → populates "Missing" tab
   │   └── Returns View(List<MonthlyCostDTO>)
   └── Browser renders C3.js line chart, client dropdown, Type selector

2. User clicks "Renewal Rate" tab
   └── shown.bs.tab event fires
       └── loadRenewalRate("2025-06-01", "2025-06-30")  [default: this month]

3. AJAX GET /MRR/GetRenewalRate?from=2025-06-01&to=2025-06-30
   ├── MRRController.GetRenewalRate(DateTime from, DateTime to)
   │   └── InvoiceMonthlyCalculationService.GetRenewalRate(from, to)
   │       ├── Calculate periods:
   │       │   Current:  2025-06 (1 month)
   │       │   Previous: 2025-05 (1 month)
   │       ├── Query InvoiceMonthlyCalculation WHERE Type=2
   │       │   AND ItemId IN (2,3,...)
   │       │   AND (Year/Month in current OR previous)
   │       ├── Group by ClientId → dictionaries of {ClientId → Sum(MrrPart)}
   │       ├── Classify each client:
   │       │   ├── In both periods, delta=0   → RENEWAL
   │       │   ├── In both periods, delta>0   → UPGRADE
   │       │   ├── In both periods, delta<0   → DOWNGRADE
   │       │   ├── Only in previous           → CHURN
   │       │   └── Only in current            → NEW BIZ
   │       ├── Compute KPIs (RenewalRate%, GrossChurn%, CustomerChurn%)
   │       └── Return RenewalRateViewModel
   └── JSON response → Browser updates gauges, stats, cards, grid