CRM: Billing Rules by Dashboard View

All four views query the same BQ table: DYNAMICS_DATA.Accounts_and_related_data (nested CRM snapshot). This document compares how each view handles the same billing concepts.

1. Base Filters (What rows get included)

Order-Level Cancellation

View	Filter	Notes
AM Portfolio (list/portfolio CY)	`o.stateCodeName != 'Canceled'`	Hard exclude. Cancelled orders never counted.
AM Portfolio (PY queries)	`o.stateCodeName != 'Canceled' OR ol.dateOfCancellation >= TIMESTAMP('{PY}-01-01')`	Softer - keeps recently cancelled to see PY churn.
Revenue Bridge	`o.stateCodeName != 'Canceled' OR ol.dateOfCancellation >= TIMESTAMP('{PY}-12-01')`	Softer - includes cancelled if cancellation is recent (needed to track churn month).
Solution Portfolio (CY)	`o.stateCodeName != 'Canceled'`	Hard exclude for CY.
Solution Portfolio (PY)	Omits filter entirely	Most permissive - captures churned accounts for PY comparison.
Company View (suspension)	`o.stateCodeName != 'Canceled'`	Hard exclude.

INCONSISTENCY: AM Portfolio PY uses {PY}-01-01 as cancellation cutoff, Revenue Bridge uses {PY}-12-01. These should probably be the same. Solution PY drops the filter entirely, which is the most permissive.

Order Line Cancellation

View	Filter
AM Portfolio (CY)	`COALESCE(ol.statusName, '') != 'Canceled'`
AM Portfolio (PY)	`COALESCE(ol.statusName, '') != 'Canceled' OR ol.dateOfCancellation >= TIMESTAMP('{PY}-01-01')`
Revenue Bridge	`COALESCE(ol.statusName, '') != 'Canceled' OR ol.dateOfCancellation >= TIMESTAMP('{PY}-12-01')`
Solution Portfolio (CY)	`COALESCE(ol.statusName, '') != 'Canceled'`
Solution Portfolio (PY)	Omits filter entirely

Same pattern: Bridge is softer on line cancellation to track churn timing. Solution PY is most permissive.

Signed Date Minimum

View	Filter
All views	`o.signedDate IS NULL OR o.signedDate >= TIMESTAMP('2017-09-04')`

CONSISTENT across all views. Excludes legacy pre-migration orders.

Product Exclusions

View	Filter
All views	`COALESCE(ol.product.productGroup.name, '') != 'DIR - PRINT'`
All views	`ol.product IS NOT NULL`
All views (subs)	`COALESCE(ol.product.subscription, FALSE) = TRUE`

CONSISTENT. DIR - PRINT always excluded, null products excluded, subscription flag respected.

2. Recurring Invoice (RI) Filters

Deposit Exclusion

View	Filter
All views	`COALESCE(ri.isDeposit, FALSE) = FALSE`

CONSISTENT. Deposit RIs never count toward revenue.

RI Amount and Period

View	Filter
All views (except bundled)	`COALESCE(ri.totalLineItemAmount, 0) > 0 AND COALESCE(ri.invoicePeriod, 0) > 0`
AM Portfolio (bundled PMS)	`COALESCE(ri.totalLineItemAmount, 0) = 0 AND COALESCE(ri.invoicePeriod, 0) > 0`

CONSISTENT. Zero-amount RIs excluded from revenue (except bundled PMS tracking in AM Portfolio).

RI Date Window (Active/Expired)

This is where the biggest differences are.

View	CY Active Filter	PY Active Filter
AM Portfolio (best_ri)	`ri.expireOn IS NULL OR ri.expireOn > CURRENT_TIMESTAMP()`	`ri.firstInvoiceOn <= TIMESTAMP('{sameDatePY}') AND (ri.expireOn IS NULL OR ri.expireOn > TIMESTAMP('{sameDatePY}'))`
AM Portfolio (active_lines)	`ri.firstInvoiceOn <= CURRENT_TIMESTAMP() AND (ri.expireOn IS NULL OR ri.expireOn > CURRENT_TIMESTAMP())`	Same pattern with PY date
Revenue Bridge	`ri.firstInvoiceOn < TIMESTAMP('{CY+1}-01-01') AND (ri.expireOn IS NULL OR ri.expireOn > TIMESTAMP('{PY}-12-01'))`	N/A (wide window, monthly filtering in SQL)
Solution Portfolio (CY)	`ri.nextInvoiceOn >= NOW() AND (ri.expireOn IS NULL OR ri.expireOn > NOW())` + suspension check	`ri.firstInvoiceOn <= Dec 31 of year AND (ri.expireOn IS NULL OR ri.expireOn > Jan 1 of year)`
Company View (suspension)	`ri.expireOn IS NULL OR ri.expireOn > CURRENT_TIMESTAMP()` + `suspendTill > CURRENT_TIMESTAMP()`	N/A

KEY DIFFERENCES:

AM Portfolio best_ri does NOT check firstInvoiceOn for CY (relies on ROW_NUMBER to pick best RI). This means future-dated RIs that have not started billing yet WILL be included.

Revenue Bridge uses the widest window (firstInvoiceOn < next year start, expireOn > prior Dec) then does monthly filtering in SQL with CROSS JOIN months.

Solution Portfolio CY uses nextInvoiceOn >= NOW() which is stricter than AM Portfolio - it requires the RI to have a future invoice scheduled.

AM Portfolio active_lines adds firstInvoiceOn <= NOW() which best_ri does NOT have.

3. Best RI Selection (ROW_NUMBER Dedup)

Each billing group can have multiple RIs. All views pick “the best one”:

View	PARTITION BY	ORDER BY
AM Portfolio (best_ri)	`bg.id`	`IF(ri.nextInvoiceOn >= NOW(), 0, 1), ri.createdOn DESC`
AM Portfolio (py_best_ri)	`bg.id`	`ri.createdOn DESC`
Revenue Bridge (bg_ri)	`bg.id, ol.salesOrderDetailId`	CY: `IF(ri.nextInvoiceOn >= NOW(), 0, 1), ri.createdOn DESC`; Historical: `ri.createdOn DESC`
Solution Portfolio	`bg.id`	Same pattern as AM Portfolio

KEY DIFFERENCE: Revenue Bridge partitions by (bgId, salesOrderDetailId) - meaning it picks one RI per BG per order line. AM Portfolio and Solution partition by bgId alone - one RI per BG regardless of how many lines share that BG.

Impact: If a BG has 3 order lines, Revenue Bridge gets 3 rows (one per line), AM Portfolio gets 1 row (for the whole BG). Revenue Bridge then distributes the RI amount across lines via proportional split, so the math should converge, but the intermediate structures differ.

4. MRR Calculation

View	Method
AM Portfolio (list)	`riAmount / riPeriod` - simple BG-level, no line allocation
AM Portfolio (portfolio)	`riAmount * (ol.baseAmount / totalBase) / riPeriod` - proportional split per line
Revenue Bridge	`riAmount * (baseAmount / totalBase) / riPeriod` - proportional split per line, with `lineCount` fallback
Solution Portfolio	Same proportional split

INCONSISTENCY: AM Portfolio LIST mode uses simple riAmount / riPeriod per BG. This is fine for AM-level totals (one BG = one value), but the PORTFOLIO mode uses the proportional split to show per-account product breakdowns. The two modes may not agree at the margins due to rounding.

5. Suspended vs Active Distinction

View	Suspended =	Active =
AM Portfolio	`suspendTill IS NOT NULL AND suspendTill > NOW()`	`nextInvoiceOn IS NOT NULL AND nextInvoiceOn >= NOW()`
Revenue Bridge	`suspendTill` captured but monthly filtering is by `firstInvoiceOn/expireOn/dateOfCancellation` - suspension NOT explicitly filtered in SQL
Solution Portfolio	`suspendTill > cutoff` as separate bridge category
Company View	`suspendTill > NOW()` (dedicated suspension query)

INCONSISTENCY: Revenue Bridge does NOT exclude suspended accounts from “active” MRR per month. If a BG is suspended but its RI window covers the month, it appears as active revenue. The bridge JS may handle this, but the SQL does not.

AM Portfolio separates suspended MRR from active MRR cleanly at the SQL level.

6. Setup/DEA Revenue

Setup Identification

View	Method
All views	`ol.product.setup = TRUE` OR `product.name LIKE '%setup%/%set up%/%set-up%'` OR `product.number IN ('site st', 'sitepro multi')`

CONSISTENT across all views.

Setup/DEA Year Window

View	Year Filter
AM Portfolio	Complex 3-way OR: (1) fromDate/toDate overlap with CY + has non-deposit RI, (2) no dates + RI firstInvoiceOn in CY, (3) no RI + signedDate in CY
Revenue Bridge	`ri.firstInvoiceOn` in selected year (simpler)
Solution Portfolio	Same 3-way OR as AM Portfolio (prorated by month overlap)

INCONSISTENCY: Revenue Bridge uses a simpler filter (just firstInvoiceOn in year). AM Portfolio and Solution have the full 3-way OR that handles edge cases (lines with service periods but no RI, lines with RI but no dates).

Impact: Revenue Bridge may miss setup/DEA lines that have fromDate/toDate spanning the year but whose RI firstInvoiceOn is in a different year.

Setup/DEA Proration

View	Method
AM Portfolio	Prorated: `baseAmount * (months_in_year / total_months)` using SAFE_DIVIDE with DATE_DIFF
Revenue Bridge	No proration - full `baseAmount` attributed to the month of `firstInvoiceOn`
Solution Portfolio	Same proration as AM Portfolio

INCONSISTENCY: A 24-month setup fee of E2,400 spanning 2025-2026 would show as:

AM Portfolio: E1,200 (12/24 months in CY)

Revenue Bridge: E2,400 (full amount in the firstInvoiceOn month)

Solution Portfolio: E1,200 (prorated)

7. Prior Year Comparison

View	PY MRR Snapshot Date	Method
AM Portfolio	`{sameDatePY}` (same month/day last year)	Point-in-time: RI active on that exact date
Revenue Bridge	Dec of prior year	Opening balance = Dec PY monthly values
Solution Portfolio	CY: same month/day PY; Historical: full year window	Depends on whether selected year is current

INCONSISTENCY: AM Portfolio compares to same-day-last-year, Revenue Bridge compares to Dec 31 prior year. For an AM looking at March 23 2026:

AM Portfolio PY MRR = what was active on March 23 2025

Revenue Bridge Opening = what was active in Dec 2025

8. BG Replacement Proxy

View	Uses bg_replacement_proxy?	How?
AM Portfolio	Yes	PY MRR and PY active lines include proxy rows via UNION ALL
Revenue Bridge	Yes	Synthetic Dec PY rows injected in JS for opening balance
Solution Portfolio	Yes	PY comparison includes proxy via UNION ALL
Company View	No	Only shows current suspended BGs

CONSISTENT (except Company View which does not need PY comparison).

9. Summary of Inconsistencies to Fix

#	Issue	Impact	Priority
1	Cancellation cutoff dates differ: AM PY uses `{PY}-01-01`, Bridge uses `{PY}-12-01`, Solution PY drops filter	Could count/miss recently cancelled accounts differently	Medium
2	AM best_ri does not check firstInvoiceOn for CY	Future-dated RIs (not yet billing) included in current MRR	High
3	Revenue Bridge does not filter suspension in SQL	Suspended accounts appear as “active” revenue in bridge months	High
4	Setup/DEA proration: Bridge does not prorate, AM Portfolio and Solution do	Setup revenue can be 2x in Bridge vs AM Portfolio for multi-year setup fees	Medium
5	Setup/DEA year filter: Bridge uses simple firstInvoiceOn, others use 3-way OR	Bridge may miss setup lines with service periods in CY but RI in PY	Low
6	PY snapshot date: AM uses same-day-PY, Bridge uses Dec PY	YoY comparison means different things in each view	Low (by design)
7	ROW_NUMBER partition: Bridge uses `(bgId, lineId)`, AM uses `(bgId)` only	Should not affect totals but creates different intermediate structures	Low

10. Shared Rules (Canonical - should be the same everywhere)

These are the “golden rules” that should be identical across all views:

1. Exclude DIR - PRINT product group
2. Exclude null products
3. Exclude orders signed before 2017-09-04
4. Exclude deposit RIs (ri.isDeposit = TRUE)
5. Exclude zero-amount RIs (ri.totalLineItemAmount = 0)
6. Exclude zero-period RIs (ri.invoicePeriod = 0)
7. MRR = riAmount / riPeriod (per BG), allocated by baseAmount proportion across lines
8. Setup = product.setup flag OR name pattern matching
9. DEA = non-subscription, non-setup one-time fees
10. BG Replacement: use bg_replacement_proxy for PY comparison

11. Where Each View Lives

View	SQL Location	Runtime
AM Portfolio	`worker/src/handlers/am-portfolio.js` (lines 27-845)	Cloudflare Worker
Revenue Bridge	`worker/src/handlers/revenue-bridge.js`	Cloudflare Worker
Solution Portfolio	`n8n-workflows/dashboard-solution-portfolio.json` (Build Query Code node)	n8n (Worker falls back)
Company View	`src/screens/CompanyView.jsx` (inline SQL, lines 363-440)	Frontend (BQ execute)

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?