CRM Billing Model

A complete reference for how billing works in the Dynamics CRM, how it maps to the JSON exported via BigQuery, and how we use it for revenue recognition and forecasting in the dashboard.

1. Entity Overview

There are 6 key entities involved in billing. We primarily work with the first 3.

Entity	DB Table	Purpose
Billing Group	`med_billinggroup`	Groups order lines that share payment terms. One BG per unique combination of payment term, method, invoice period, start date, and installment count.
Recurring Invoice	`med_recurringinvoice`	Controls when and how much to invoice. Despite the name, it handles BOTH recurring (subscription) AND non-recurring (setup/fixed-term) billing.
Recurring Invoice Detail	`med_recurringinvoicedetail`	Links an RI to the specific order line products it covers. Amounts on the RI are the sum of these details.
Invoice Claim	`med_invoiceclaim`	The actual invoice record created when an RI is processed. Contains the final amounts, dates, and VAT.
Invoice Payment	`med_invoicepayment`	Payment records against invoice claims.
Document Queue	`med_documentqueue`	PDF generation queue for invoices.

2. Entity Relationships

Account
  └── Orders (salesorder)
        └── Order Lines (salesorderdetail)
              └── BillingGroup (many order lines → one BG)
                    └── RecurringInvoices (one BG → one or more RIs)
                          └── RecurringInvoiceDetails (one RI → one or more product lines)

Order Line → Billing Group (Many-to-One)

Multiple order lines can point to the same Billing Group. This happens when:

The lines share the same payment term, payment method, invoice period, start date, and installment count.
Example: An order with a Website (€35/mo) and SEA (€400/mo) on the same monthly DD — both lines point to the same BG.

Critical implication: In the JSON, the same BG object (with the same Id) appears on every order line that references it. When processing, we must deduplicate by BG.Id and collect all associated lines, then split the RI amount proportionally across them.

Billing Group → Recurring Invoice (One-to-Many)

Initially, a BG has either:

1 RI — standard case (subscription or non-subscription without deposit)
2 RIs — when payment term involves deposit (“Deposit and Installments” or “Deposit and Invoice”). One RI for the deposit part (IsDeposit = true), one for the remainder.

Over time, additional RIs can be created:

Partial cancellation — when some (not all) order lines in a BG are cancelled, the existing RI is set to Inactive and a new RI is created with the revised amount from the remaining active lines.
Billing Group replacement — a new BG is created (with PreviousBillingGroup pointing to the old one), new RIs are created, and old RIs are set to Inactive.

RI Selection Rule: When multiple non-deposit RIs exist for a BG, pick the most recently created Active one. Sort by CreatedOn DESC, prefer StateCodeName === "Active", take the first match.

3. The Four Billing Categories

The naming “Recurring vs Non-Recurring” is misleading. The real distinction is:

Category 1: Subscription (Recurring)

Identified by: RI.InvoicePeriod > 0
Values: 1 = Monthly, 3 = Quarterly, 6 = Semi-Annual, 12 = Annual
Behaviour: RI is processed repeatedly. After each processing, NextInvoiceOn is advanced by InvoicePeriod months. RI stays Active indefinitely until all associated order lines are cancelled.
Monthly value: RI.TotalLineItemAmount / RI.InvoicePeriod
Sub-variants:
- No split (InstallmentCount = 1): one invoice per period
- Multi-shot (InstallmentCount > 1, period 3/6/12): the period amount is divided into installments

Category 2: Non-Subscription (Setup / Fixed-Term)

Identified by: RI.InvoicePeriod is null or 0
Behaviour: RI is processed once. After processing, it’s set to Inactive/Expired. But it may generate multiple Invoice Claims if InstallmentCount > 1.
Per-invoice amount: RI.TotalLineItemAmount / BG.InstallmentCount
Recognition: Spread across installment months starting from FirstInvoiceOn

Category 3: Deposit

Identified by: RI.IsDeposit = true
Behaviour: Always processed immediately on order load. One invoice, then set to Inactive.
Amount: The deposit portion of the BG (typically a percentage of the total).
Always one-time, regardless of whether the parent BG is subscription or non-subscription.

Category 4: Setup Detection (within Non-Subscription)

We further classify non-subscription lines as “Setup” vs “One-Time” based on:
- Product.IsSetup = true
- Product name contains “setup” or “set up”
- Product number is “site st” or “sitepro multi”
- Product is in the “Website” group with no InvoicePeriod and positive amount

4. Revenue Recognition — How We Forecast

Revenue is recognized based on when invoices are issued, not when contracts are signed.

Subscription Lines

Calculate monthlyValue = RI.TotalLineItemAmount / RI.InvoicePeriod
Determine the billing window:
- Start: RI.FirstInvoiceOn (first month billed)
- End: depends on state:
  - Active + no SuspendTill → ongoing (no end)
  - Active + SuspendTill → RI.LastInvoiceOn or RI.SuspendTill (whichever is set)
  - Inactive → RI.LastInvoiceOn or RI.ExpireOn
For target year: count months where billing window overlaps → monthlyValue × activeMonths

Example: Website at €35/mo, FirstInvoiceOn 2024-03-01, Active with no SuspendTill:

2025 recognition: €35 × 12 = €420
2026 recognition: €35 × 12 = €420

Example: SEA at €400/mo, FirstInvoiceOn 2024-06-01, Suspended since 2025-08-01 (LastInvoiceOn 2025-08-01):

2025 recognition: €400 × 8 = €3,200 (Jan–Aug)
2026 recognition: €0 (suspended)

Non-Subscription Lines (Setup / One-Time)

Calculate perInstallment = RI.TotalLineItemAmount / BG.InstallmentCount
Generate months from RI.FirstInvoiceOn, one per installment
Count how many fall in target year → perInstallment × monthsInCY

Example: Website setup €300, 3 installments, FirstInvoiceOn 2025-11-01:

Installment months: Nov-25, Dec-25, Jan-26
2025 recognition: €100 × 2 = €200
2026 recognition: €100 × 1 = €100

Deposit Lines

Single recognition in the month of RI.FirstInvoiceOn
Always immediate, so typically the month the order was loaded

BG Amount Splitting Across Product Lines

When a BG covers multiple order lines (e.g., SEA + Website), the RI amount must be split:

Sum BaseAmount across all lines in the BG
Each line’s share = RI.TotalLineItemAmount × (line.BaseAmount / sumBaseAmount)
If all BaseAmounts are 0, split equally
Each line becomes its own revenue item — this is essential for:
- Accurate product-level portfolio counts (e.g., “how many active websites?”)
- Per-product revenue reporting
- Avoiding double-counting when the same BG appears on multiple lines

5. Suspension

How Suspension Works

A field SuspendTill is set on the Recurring Invoice (not the BG)
The nightly billing job checks this: if SuspendTill is set and in the future, the RI is skipped
Typical value: 2100-12-31 meaning “indefinitely suspended”
The RI remains Active (StateCode = 0) but no invoices are generated

Detecting Suspension in the API

Currently suspended: RI.StateCodeName === "Active" AND RI.SuspendTill is set
Historically suspended: RI.StateCodeName === "Inactive" AND RI.SuspendTill is set (was suspended, then either expired or replaced)

The Unsuspension Problem

When an account is unsuspended, SuspendTill is cleared (set to null). This means:

Live data loses suspension history — you cannot tell from the current API that an account was ever suspended
For accurate historical tracking, we need weekly BQ snapshots of the RI data (the suspension-snapshot n8n workflow captures this)

Calculating Lost Revenue

For a suspended RI in target year:

monthlyValue = RI.TotalLineItemAmount / RI.InvoicePeriod
wouldBillMonths = months in CY where billing would have occurred (from FirstInvoiceOn, no end)
actualBillMonths = months in CY where billing actually occurred (from FirstInvoiceOn to LastInvoiceOn/SuspendTill)
lostMonths = wouldBillMonths - actualBillMonths
lostCY = monthlyValue × lostMonths

Full vs Partial Suspension

Full suspension: All RIs on all BGs for an account are suspended. The suspended.xlsx shows this as “Full Suspension”.
Partial suspension: Some products are suspended, others are active. Example: SEA suspended but Website still billing. Shown as “Part Suspension”.

6. Payment Types

Derived from BillingGroup fields:

PaymentTermName	PaymentMethodName	Classification
Installment / Deposit and installments	Direct debit by bank	DD Bank
Installment / Deposit and installments	Direct debit by credit card / Credit Card - Build Only	DD Credit Card
Full Prepayment	Credit card	Credit Card
Invoice	(any)	Invoice
Full Prepayment	Cash, Postal_order, Bankdraf	Bank Transfer
Deposit and Invoice	(any)	Invoice (for non-deposit part)

Invoice Timing by Payment Type (Non-Deposit)

Scenario	First Invoice Issued
DD by bank, first run	StartDate minus 1 month (invoice issued 1 month before collection)
DD by bank, subsequent	LastUsedStartDate + InvoicePeriod - 1 month
DD by card, first run	StartDate (invoice and collection same day)
DD by card, subsequent	LastUsedStartDate + InvoicePeriod
ActivateInvoiceDate set, first run	ActivateInvoiceDate
ActivateInvoiceDate set, subsequent	LastUsedStartDate + InvoicePeriod
On Invoice (no activate date)	Immediately (NextInvoiceOn)
Full Prepayment	Immediately (NextInvoiceOn)

Deposit Parts

Always invoiced immediately regardless of payment type
If deposit method is “Credit card”, collection is also immediate
Deposit RI uses NextInvoiceOn directly (no override from StartDate/ActivateInvoiceDate)

7. VAT Handling

RI stores both TotalLineItemAmount (net) and TotalAmount (gross including VAT)
VatRate field shows the rate at time of RI creation
Historical 21% VAT: Some older RIs were created at 21%. For current forecasting, re-gross at 23%:
- adjustedGross = (TotalAmount / 1.21) * 1.23
- When InstallmentCount > 1: divide after VAT adjustment
Our revenue analysis uses TotalLineItemAmount (net) so VAT adjustment is only needed for gross forecasting

8. Exclusion Rules

We exclude from revenue analysis:

Cancelled orders: Order.StateCodeName === "Canceled"
Pre-migration orders: Order.SignedDate < 2017-09-19 (before CRM migration)
Print products: ProductGroup === "DIR - PRINT" (legacy print directory)
Cancelled order lines: StatusName === "Canceled" (filtered in the API transform)
Zero-amount lines: Lines with BaseAmount <= 0 and no BG (orphan filter)

9. Billing Group Replacement

When a BG needs to change (e.g., payment method update, price change):

A new BG is created with PreviousBillingGroup pointing to the old BG ID
Order lines are updated to point to the new BG
New RI(s) are created from the new BG
Old RI(s) are set to Inactive/Expired
The old BG’s RecurringInvoices still exist in the data — this is why we pick the latest Active RI

Impact on analysis: After replacement, the old BG/RIs remain in the JSON but are Inactive. Our RI selection logic (prefer Active, then most recent) handles this correctly.

10. Edge Cases We’ve Encountered

Same BG on multiple order lines

Halo Security (30612672): 3 SEA lines + 1 Website line on same order, same BG
Must split RI amount proportionally, not just take the first line

Annual billing with suspension

Abbey Vet (11782542): PMS - Saymore at €90/mo (Period: 12, Amount: €1,080/year), suspended
Monthly value = €1,080 / 12 = €90/mo for lost revenue calculation

BG replacement clears suspension evidence

Spick & Span (11279600): Was suspended per weekly report, but BG was replaced on March 4th — new Active RI has no SuspendTill, old RI is gone
This is why we need BQ snapshots for historical tracking

Zero-amount Active RIs

Some accounts have Active RIs with TotalLineItemAmount = 0 — these are legitimate (e.g., Saymore that was fully cancelled but BG not replaced). Filter out in reporting.

Multiple Active RIs on same BG

Can happen with partial cancellations where the sequence wasn’t clean
Our rule: sort by CreatedOn DESC, take the first Active one

Products not in JSON

Product.name can be undefined in the BQ JSON — fall back to Product.number then ProductGroup.Name

11. Data Sources & Dashboard Data Flow

How the Dashboard Gets Billing Data

BigQuery table ──→ n8n webhook ──→ Dashboard (browser)
(source of truth)   (proxy/API)     (all analysis runs here)

User searches for an account in the dashboard
Dashboard calls GET /copysheet-account?subscriberId=... (n8n webhook)
n8n queries BigQuery table listingmanager-1529856313699.DYNAMICS_DATA.Accounts_and_related_data
Returns the full nested JSON: [{ dynamics: {...}, listings: [...] }]
transformDynamicsResponse() in apiService.js normalizes field names and passes through BG/RI data
analyzeRevenue() in revenueAnalysis.js processes the BG/RI chain client-side in the browser

The dashboard does NOT query BigQuery directly. All data flows through n8n webhooks. The revenue analysis (BG dedup, RI selection, amount splitting, suspension detection) is pure JavaScript running in the browser.

BigQuery (Direct) — Portfolio-Wide Queries

Table: listingmanager-1529856313699.DYNAMICS_DATA.Accounts_and_related_data
Nested structure: account.orders[].salesOrderDetails[].billingGroup.recurringInvoices[]
Updated periodically from CRM — slight lag vs live data
Used for portfolio-wide queries that the per-account n8n endpoint can’t do:
- billing-schedule.sql — monthly billing forecast across all accounts
- suspended-revenue.sql — suspended revenue totals by Account Manager
- suspension-snapshot.sql — weekly suspension state capture
These are run manually in the BigQuery console, not from the dashboard

CLI Tools (Node.js scripts via n8n API)

billing-schedule.mjs — fetches one account via n8n, generates a CSV billing schedule
check-suspended.mjs / check-unsuspended.mjs — spot-check SuspendTill data via n8n
check-billing-gaps.mjs — investigate gaps between RI billing dates
These call the same n8n webhooks as the dashboard

Weekly Suspension Snapshots

n8n workflow captures current suspended RIs to BQ weekly
Essential because live data loses suspension history when accounts are unsuspended
Without snapshots, we cannot track when suspensions started or how long they lasted

12. What We Still Don’t Have

Invoice Claims — actual invoices that were issued (historical billing). Would give us “what was actually billed” vs our current “what should have been billed” forecast.
Invoice Payments — whether invoices were actually paid. Would enable aging/collections analysis.
Recurring Invoice Detail — the per-product breakdown within an RI. Currently we reconstruct this from order lines, but the RI Detail would be authoritative.
Document Queue — PDF generation status. Not relevant for revenue analysis.
Credit Notes — adjustments and refunds.

13. Reference Files

File	Location	Purpose
`billing-schedule.mjs`	copysheet root	CLI: generates monthly billing CSV from CRM JSON
`billing-schedule.sql`	copysheet root	BigQuery version of same logic
`suspended-revenue.sql`	copysheet root	BQ: suspended revenue by AM with monthly lost columns
`suspension-snapshot.sql`	copysheet root	BQ: current suspension state for weekly snapshot
`check-billing-gaps.mjs`	copysheet root	CLI: investigates gaps between RI billing dates
`check-suspended.mjs`	copysheet root	CLI: spot-check SuspendTill on specific accounts
`check-unsuspended.mjs`	copysheet root	CLI: check if unsuspended accounts still have SuspendTill
`Overview...billing process.html`	copysheet root	Definitive billing entity docs from CRM team
`query...Billing Summary v14.sql`	copysheet root	CRM SQL showing all billing scenarios
`e mail on crm json.txt`	copysheet root	Email explaining the query and filtering rules
`suspended.xlsx`	Downloads	Weekly suspension report from operations
`billing-schedule-*.csv`	copysheet root	Generated billing schedules for specific accounts

Ask the docsRAG over this site

Ask anything about the FCR Dashboard platform — architecture, BigQuery, the worker routes, billing rules, the LRC stack, scoring… Answers are grounded in this documentation, with source links.

How does the deal-brief refresh work? Which routes are Worker vs n8n? How is account health scored?