FCR Media — 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.

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

1. Calculate monthlyValue = RI.TotalLineItemAmount / RI.InvoicePeriod
2. 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
3. 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)

1. Calculate perInstallment = RI.TotalLineItemAmount / BG.InstallmentCount
2. Generate months from RI.FirstInvoiceOn, one per installment
3. 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:

1. Sum BaseAmount across all lines in the BG
2. Each line's share = RI.TotalLineItemAmount × (line.BaseAmount / sumBaseAmount)
3. If all BaseAmounts are 0, split equally
4. 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:

1. monthlyValue = RI.TotalLineItemAmount / RI.InvoicePeriod
2. wouldBillMonths = months in CY where billing would have occurred (from FirstInvoiceOn, no end)
3. actualBillMonths = months in CY where billing actually occurred (from FirstInvoiceOn to LastInvoiceOn/SuspendTill)
4. lostMonths = wouldBillMonths - actualBillMonths
5. 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:

Invoice Timing by Payment Type (Non-Deposit)

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):

1. A new BG is created with PreviousBillingGroup pointing to the old BG ID
2. Order lines are updated to point to the new BG
3. New RI(s) are created from the new BG
4. Old RI(s) are set to Inactive/Expired
5. 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)

1. User searches for an account in the dashboard
2. Dashboard calls GET /copysheet-account?subscriberId=... (n8n webhook)
3. n8n queries BigQuery table listingmanager-1529856313699.DYNAMICS_DATA.Accounts_and_related_data
4. Returns the full nested JSON: [{ dynamics: {...}, listings: [...] }]
5. transformDynamicsResponse() in apiService.js normalizes field names and passes through BG/RI data
6. 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

1. 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.
2. Invoice Payments — whether invoices were actually paid. Would enable aging/collections analysis.
3. Recurring Invoice Detail — the per-product breakdown within an RI. Currently we reconstruct this from order lines, but the RI Detail would be authoritative.
4. Document Queue — PDF generation status. Not relevant for revenue analysis.
5. Credit Notes — adjustments and refunds.

13. Reference Files

FCR Dashboard documentation · generated from docs/ · keep counts verified, not guessed.