Extracting Active Billing from Dynamics CRM (BigQuery)

Table Structure

The source table is deeply nested:

Accounts_and_related_data
  └── orders[]                          (UNNEST as o)
       └── salesOrderDetails[]          (UNNEST as ol)
            ├── product.name
            ├── product.productGroup.name
            ├── product.subscription      (TRUE = recurring, FALSE = setup/DEA)
            ├── baseAmount                (line-level net amount)
            └── billingGroup              (UNNEST as bg)
                 ├── billingGroupNumber
                 ├── rootBillingGroupNumber  (links replacement chains)
                 └── recurringInvoices[]    (UNNEST as ri)
                      ├── totalLineItemAmount  (total RI value across all lines in this BG)
                      ├── invoicePeriod        (months covered: 1=monthly, 12=annual)
                      ├── firstInvoiceOn       (can be NULL for delayed starts)
                      ├── nextInvoiceOn        (next billing date)
                      ├── expireOn             (NULL = perpetual)
                      ├── suspendTill          (NULL = not suspended)
                      ├── isDeposit            (TRUE = deposit RI, exclude these)
                      └── createdOn

Key Gotchas

1. Multiple RIs per billing group: A BG can have several RIs (renewals, replacements). Always pick the best/current one using ROW_NUMBER() partitioned by the root BG chain.

2. Monthly value calculation: The RI totalLineItemAmount covers ALL lines in that BG. To get per-line monthly value:

   monthlyValue = riAmount * (lineBaseAmount / sumOfAllBaseAmountsInBG) / invoicePeriod
   

3. firstInvoiceOn can be NULL: Delayed starts and some replacement BGs have NULL. Fall back to nextInvoiceOn.

4. UTC timezone offset: CRM stores Irish dates as UTC. Irish midnight = 23:00 UTC the prior day. Use DATE(timestamp, "Europe/Dublin") for accurate dates.

5. Zero-value bundled lines: Lines with parentItemGuid IS NOT NULL AND baseAmount = 0 are "Sold With" add-ons. They ride on the parent's BG but have no revenue. Filter with (ol.parentItemGuid IS NULL OR ol.baseAmount > 0).

6. Deposits: Filter out ri.isDeposit = TRUE — these are one-off deposit invoices, not recurring billing.

Active Billing Query — DMS Websites

WITH best_ri AS (
  SELECT * FROM (
    SELECT
      a.accountNumber                                        AS subscriber_id,
      a.name                                                 AS account_name,
      COALESCE(a.accountManager.fullName, 'House')           AS account_manager,
      o.orderNumber                                          AS order_number,
      o.signedDate                                           AS signed_date,
      COALESCE(ol.product.productGroup.name, 'Other')        AS product_group,
      COALESCE(ol.product.name, ol.product.number, 'Unknown') AS product_name,
      ol.product.number                                      AS product_code,
      COALESCE(ol.product.subscription, FALSE)               AS is_subscription,
      ol.baseAmount                                          AS base_amount,
      ol.salesOrderDetailId,
      bg.id                                                  AS bg_id,
      bg.billingGroupNumber                                  AS bg_number,
      COALESCE(bg.rootBillingGroupNumber, bg.billingGroupNumber) AS root_bg_number,
      COALESCE(ri.totalLineItemAmount, 0)                    AS ri_amount,
      COALESCE(ri.invoicePeriod, 0)                          AS ri_period,
      ri.firstInvoiceOn,
      ri.nextInvoiceOn,
      ri.expireOn,
      ri.suspendTill,
      ri.createdOn                                           AS ri_created,
      ol.dateOfCancellation,
      ol.statusName                                          AS line_status,
      o.stateCodeName                                        AS order_state,

      ROW_NUMBER() OVER (
        PARTITION BY COALESCE(bg.rootBillingGroupNumber, bg.billingGroupNumber),
                     ol.salesOrderDetailId
        -- Prefer RI with future nextInvoiceOn, then most recently created
        ORDER BY IF(ri.nextInvoiceOn >= CURRENT_TIMESTAMP(), 0, 1),
                 ri.createdOn DESC
      ) AS rn

    FROM `listingmanager-1529856313699.DYNAMICS_DATA.Accounts_and_related_data` a,
      UNNEST(a.orders) o,
      UNNEST(o.salesOrderDetails) ol,
      UNNEST([ol.billingGroup]) bg,
      UNNEST(bg.recurringInvoices) ri

    WHERE
      -- Exclude cancelled orders (unless recently cancelled)
      o.stateCodeName != 'Canceled'
      -- Post-migration orders only
      AND (o.signedDate IS NULL OR o.signedDate >= TIMESTAMP('2017-09-04'))
      -- Exclude print directory
      AND COALESCE(ol.product.productGroup.name, '') != 'DIR - PRINT'
      AND ol.product IS NOT NULL
      -- Subscription lines only (set FALSE for setup/DEA)
      AND COALESCE(ol.product.subscription, FALSE) = TRUE
      -- Valid billing group
      AND bg.id IS NOT NULL
      -- Not a deposit
      AND COALESCE(ri.isDeposit, FALSE) = FALSE
      -- Has real billing value
      AND COALESCE(ri.totalLineItemAmount, 0) > 0
      AND COALESCE(ri.invoicePeriod, 0) > 0
      -- Exclude zero-value bundled lines
      AND (ol.parentItemGuid IS NULL OR ol.baseAmount > 0)
      -- ═══ ACTIVE BILLING FILTERS ═══
      -- Not expired (or no expiry = perpetual)
      AND (ri.expireOn IS NULL OR ri.expireOn > CURRENT_TIMESTAMP())
      -- Not suspended
      AND (ri.suspendTill IS NULL OR ri.suspendTill <= CURRENT_TIMESTAMP())
      -- Has a future billing date
      AND ri.nextInvoiceOn IS NOT NULL
      AND ri.nextInvoiceOn >= CURRENT_TIMESTAMP()

      -- ═══ PRODUCT FILTER ═══
      -- Option A: by product group
      AND ol.product.productGroup.name = 'DMS - Websites'

      -- Option B: by specific product names (uncomment and edit)
      -- AND ol.product.name IN (
      --   'Site Pro Multi Maintenance',
      --   'SitePro Single - Recurring',
      --   'e-Store Site Pro Multi Maintenance'
      -- )

  ) WHERE rn = 1  -- One RI per line per BG chain
),

-- Calculate per-line monthly value from the shared BG total
bg_totals AS (
  SELECT bg_id,
    SUM(base_amount) AS total_base,
    COUNT(*)         AS line_count
  FROM best_ri
  GROUP BY bg_id
)

SELECT
  r.subscriber_id,
  r.account_name,
  r.account_manager,
  r.order_number,
  r.signed_date,
  r.product_group,
  r.product_name,
  r.product_code,
  r.base_amount,
  r.ri_amount,
  r.ri_period,
  -- Monthly value: proportional share of the RI total
  ROUND(CASE
    WHEN bt.total_base > 0 THEN r.ri_amount * (r.base_amount / bt.total_base) / r.ri_period
    WHEN bt.line_count > 0 THEN r.ri_amount / bt.line_count / r.ri_period
    ELSE r.ri_amount / r.ri_period
  END, 2) AS monthly_value,
  DATE(COALESCE(r.firstInvoiceOn, r.nextInvoiceOn), 'Europe/Dublin') AS billing_start,
  DATE(r.nextInvoiceOn, 'Europe/Dublin')                             AS next_invoice,
  DATE(r.expireOn, 'Europe/Dublin')                                  AS expires,
  r.line_status,
  r.order_state,
  r.bg_number,
  r.root_bg_number

FROM best_ri r
JOIN bg_totals bt ON r.bg_id = bt.bg_id

ORDER BY monthly_value DESC

Adapting the Filter

By product group — change the AND ol.product.productGroup.name = '...' line:

• 'DMS - Websites' — SitePro, e-Store, web maintenance
• 'DMS - SEA' — Google Ads management
• 'DMS - SEO' — SEO packages
• 'PMS - Saymore' — SayMore listings/review management
• 'PMS - Snapsync' — SnapSync directory sync
• 'DIR - GPI' — Golden Pages online

By product list — comment out Option A, uncomment Option B and list product names.

Include Setup/DEA — change ol.product.subscription filter to FALSE and remove the RI active billing filters. Setup/DEA lines are one-off, not recurring.

Expected Output

Accounts with expires = NULL are perpetual (no end date). The monthly_value is the net recurring amount per month.

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