Automated Reconciliation Between CRM and Accounting Systems on Google Cloud

When B2B transaction volume grows, the total value of closed deals in the CRM inevitably diverges from the total value of invoices and payments in the accounting system. These systems track different stages of the commercial lifecycle: the CRM captures commercial intent, while the accounting ledger records legal billing and cash settlement.

1. Root Causes and Business Case

Before designing a pipeline, we need to isolate the exact structural causes of divergence between a CRM and an accounting ledger:

  1. Broken or Missing Foreign Keys: Sales representatives close deals in the CRM, while finance teams manually issue invoices in the ERP without copying the deal_id into the invoice reference field.
  2. Monetary Adjustments: Bank wire fees, withholding taxes, FX conversion spreads, or partial credit memos cause the settled payment amount to differ from the contracted deal value.
  3. Temporal Drift: The CRM logs closed_date when the contract is signed, whereas the accounting system logs invoice_date or payment_date days or weeks later according to net payment terms.
  4. Lifecycle Status Desynchronization: A deal remains marked CLOSED_WON in the CRM even after the corresponding invoice is voided, disputed, or written off in accounting.
  5. Structural Cardinality Mismatches (1-to-N): A single annual CRM contract is billed across multiple milestone invoices.

Concrete B2B Scenario Consider a B2B software and consulting company closing 500 contracts per quarter across multiple currencies. Sales operations record deals in HubSpot (crm_deals), while finance issues invoices and logs incoming wire payments in NetSuite or Xero (accounting_ledger). No live banking credentials or raw bank feeds are required to audit this: both datasets are standard tabular exports containing entity identifiers, timestamps, amounts, and document statuses. At month-end, the CRM reports $4,200,000 in closed business, while accounting shows $3,940,000 in billed and settled entries. Reconciling this gap manually in spreadsheets takes several days every month.

2. Progression of Solutions: From Simple to Complex

In my architecture designs on Google Cloud, I structure reconciliation in three tiers of increasing complexity, moving from deterministic SQL to combinatorial optimization:

  • Tier 1: Deterministic Multi-Key SQL (Dataform & BigQuery). We join records where crm.deal_id = acc.reference_id, or where customer_tax_id, currency, and exact amount match within a rigid date window. This resolves roughly 80–85% of records directly in the data warehouse.
  • Tier 2: Heuristic Windowing in SQL (and Why It Fails). A common next step is writing a fuzzy SQL join using tolerance windows (e.g., ABS(crm.amount - acc.amount) <= 250 and ABS(DATE_DIFF(crm.closed_date, acc.invoice_date, DAY)) <= 14) ranked by ROW_NUMBER(). The flaw here is greedy local matching. If the same customer signs two $10,000 deals three days apart and pays $9,965 (after a $35 wire fee) and $10,000, a greedy SQL window function frequently pairs the first deal with the wrong invoice, triggering a cascade of false AMOUNT_MISMATCH and MISSING_RECORD alerts.
  • Tier 3: Global Bipartite Assignment + Meet-in-the-Middle Subset Sum (Cloud Run). Instead of evaluating unresolved records row by row, we isolate the unmatched residuals from Tier 1 and solve them globally inside a Cloud Run service using two rare but highly effective algorithms:
    1. Augmented Jonker-Volgenant Bipartite Matching (O(V³)): Formulates 1-to-1 multi-key matching across an entire billing cohort as a Minimum-Weight Linear Assignment problem with slack nodes for missing records.
    2. Horowitz-Sahni Meet-in-the-Middle (O(2^(N/2))): Resolves 1-to-N split invoices with fee tolerances where standard dynamic programming fails on floating-point decimals.

3. GCP Architecture and Dataform Implementation

The pipeline runs entirely on BigQuery, Dataform, and Cloud Run:

  1. Ingestion: Raw extracts land in BigQuery tables raw.crm_deals and raw.accounting_ledger.
  2. Dataform Pass 1 (reconciliation_pass1.sqlx): Normalizes schemas, executes deterministic matching, classifies direct discrepancies, and writes unresolved orphans into an intermediate staging view.
  3. Cloud Run Solver: Pulls the unresolved residuals, executes the Jonker-Volgenant and Meet-in-the-Middle algorithms, and writes classified edge cases to BigQuery.
  4. Exceptions Mart (mart.reconciliation_exceptions): Consolidates only actionable discrepancies (MISSING_RECORD, AMOUNT_MISMATCH, DATE_DRIFT, STATUS_MISMATCH) for human review.

Plaintext

[raw.crm_deals]         [raw.accounting_ledger]
       \                       /
        v                     v
   +-------------------------------+
   |   Dataform (BigQuery SQLX)    |
   |  Deterministic Multi-Key Join |
   +-------------------------------+
          |                 |
   (Matched with     (Unresolved Orphans:
    Discrepancies)    Missing Foreign Keys)
          |                 |
          |                 v
          |     +-----------------------+
          |     |   Cloud Run Service   |
          |     |  - Jonker-Volgenant   |
          |     |  - Meet-in-the-Middle |
          |     +-----------------------+
          \                 /
           v               v

[mart.reconciliation_exceptions]

Step 1: Dataform Deterministic Matching (definitions/reconciliation_pass1.sqlx)

SQL

config {
  type: "table",
  schema: "reconciliation",
  name: "pass1_deterministic",
  description: "Tier 1 deterministic reconciliation and discrepancy classification"
}

WITH crm AS (
  SELECT
    deal_id,
    UPPER(TRIM(customer_tax_id)) AS tax_id,
    CAST(deal_amount AS NUMERIC) AS crm_amount,
    CAST(closed_date AS DATE) AS crm_date,
    UPPER(TRIM(deal_status)) AS crm_status
  FROM ${ref("raw", "crm_deals")}
  WHERE deal_status IN ('CLOSED_WON', 'CANCELLED')
),

acc AS (
  SELECT
    invoice_id,
    NULLIF(TRIM(crm_reference_id), '') AS ref_deal_id,
    UPPER(TRIM(customer_tax_id)) AS tax_id,
    CAST(invoice_amount AS NUMERIC) AS acc_amount,
    CAST(invoice_date AS DATE) AS acc_date,
    UPPER(TRIM(payment_status)) AS acc_status
  FROM ${ref("raw", "accounting_ledger")}
),

-- Pass 1: Match on explicit foreign key
matched_by_id AS (
  SELECT
    c.deal_id,
    a.invoice_id,
    c.tax_id,
    c.crm_amount,
    a.acc_amount,
    c.crm_date,
    a.acc_date,
    c.crm_status,
    a.acc_status,
    'EXACT_ID' AS match_method
  FROM crm c
  INNER JOIN acc a
    ON c.deal_id = a.ref_deal_id
)

SELECT
  deal_id,
  invoice_id,
  tax_id,
  crm_amount,
  acc_amount,
  crm_date,
  acc_date,
  crm_status,
  acc_status,
  match_method,
  ARRAY_TO_STRING(
    ARRAY_CONCAT(
      IF(crm_amount != acc_amount, ['AMOUNT_MISMATCH'], []),
      IF(ABS(DATE_DIFF(acc_date, crm_date, DAY)) > 5, ['DATE_DRIFT'], []),
      IF(
        (crm_status = 'CLOSED_WON' AND acc_status NOT IN ('PAID', 'ISSUED')) OR
        (crm_status = 'CANCELLED' AND acc_status != 'VOIDED'),
        ['STATUS_MISMATCH'], []
      )
    ),
    ', '
  ) AS discrepancy_class
FROM matched_by_id

Records from crm and acc that do not appear in pass1_deterministic have no valid ref_deal_id. Those unmatched rows are passed to the Cloud Run service.

4. The Algorithmic Core on Cloud Run

Mathematical Formulation: Augmented Bipartite Assignment

Suppose we have n unmatched CRM deals U = {u_1, ..., u_n} and m unmatched accounting entries V = {v_1, ..., v_m} within a billing window. Instead of greedy pairing, we construct a weighted bipartite cost matrix C of size (n + m) × (n + m).

For every real candidate pair (u_i, v_j), the composite multi-key distance Cost[i, j] combines four independent penalties:

Plaintext

Cost[i, j] = (w_tax  * TaxPenalty(tax_i, tax_j))
           + (w_amt  * abs(ln(Amount_i / Amount_j)))
           + (w_date * DatePenalty(Date_acc - Date_crm))
           + (w_stat * StatusPenalty(Status_i, Status_j))

Where each component is defined as follows:

  • TaxPenalty(tax_i, tax_j): Returns an infinite barrier (1e6) if counterparty tax IDs differ (tax_i ≠ tax_j), or a Jaro-Winkler string distance if matching on legal entity names.
  • abs(ln(Amount_i / Amount_j)): Measures scale-invariant relative divergence between the deal amount and invoice amount.
  • DatePenalty(days_diff): An asymmetric temporal penalty on days_diff = Date_acc - Date_crm. An invoice issued 0 to 5 days after deal close incurs a minor linear penalty, whereas an invoice dated before deal close incurs a steep multiplier.
  • StatusPenalty(Status_i, Status_j): Adds a fixed penalty when document states conflict (for example, CLOSED_WON paired with VOIDED).

To handle missing records directly inside the same optimization pass, we expand the matrix with n dummy columns (representing MISSING_IN_ACCOUNTING) and m dummy rows (representing MISSING_IN_CRM), placing a fixed rejection threshold tau on their diagonals:

Plaintext

                [ Real Invoices (m) ]   [ Dummy CRM Slots (n) ]
Real Deals (n)  |   Cost[n × m]       |     tau * I[n × n]    |
Dummy Acc (m)   |   tau * I[m × m]    |       0[m × n]        |

When we solve this Linear Assignment Problem using the Jonker-Volgenant shortest augmenting path algorithm, the solver finds the permutation that minimizes total discrepancy cost across the entire cohort at once. Any record assigned to a dummy slot with cost tau is automatically isolated as a MISSING_RECORD.

Example 1: Python Implementation (Global Bipartite Assignment)

In Python on Cloud Run, scipy.optimize.linear_sum_assignment implements the Jonker-Volgenant algorithm in compiled C++. Below is the complete reconciliation module that computes the multi-key cost matrix, runs global assignment, and classifies discrepancies.

Python

from dataclasses import dataclass
from datetime import date
from typing import List, Optional
import numpy as np
from scipy.optimize import linear_sum_assignment

@dataclass
class CrmDeal:
    deal_id: str
    tax_id: str
    amount: float
    closed_date: date
    status: str

@dataclass
class AccountingEntry:
    invoice_id: str
    tax_id: str
    amount: float
    invoice_date: date
    status: str

@dataclass
class ExceptionRecord:
    deal_id: Optional[str]
    invoice_id: Optional[str]
    discrepancy_types: List[str]
    amount_diff: Optional[float]
    date_diff_days: Optional[int]
    assignment_cost: float

def status_penalty(crm_status: str, acc_status: str) -> float:
    valid_pairs = {
        ("CLOSED_WON", "PAID"),
        ("CLOSED_WON", "ISSUED"),
        ("CANCELLED", "VOIDED"),
    }
    return 0.0 if (crm_status, acc_status) in valid_pairs else 25.0

def asymmetric_date_cost(days_diff: int) -> float:
    # Invoice issued 0..5 days after deal close is normal operational lag
    if 0 <= days_diff <= 5:
        return days_diff * 0.2
    # Invoice issued after >5 days is date drift
    if days_diff > 5:
        return 1.0 + (days_diff - 5) * 1.5
    # Invoice dated BEFORE deal close is highly irregular
    return abs(days_diff) * 4.0

def reconcile_bipartite(
    deals: List[CrmDeal],
    invoices: List[AccountingEntry],
    rejection_threshold: float = 45.0
) -> List[ExceptionRecord]:
    n, m = len(deals), len(invoices)
    total_dim = n + m
    INF = 1e6

    # Initialize augmented (n+m) x (n+m) matrix with INF
    cost_matrix = np.full((total_dim, total_dim), INF, dtype=np.float64)

    # 1. Populate real-to-real multi-key costs (top-left n x m block)
    for i, d in enumerate(deals):
        for j, inv in enumerate(invoices):
            if d.tax_id != inv.tax_id:
                continue  # Keep INF barrier for different counterparties

            # Log-ratio amount penalty (scaled)
            amt_cost = abs(np.log(max(d.amount, 0.01) / max(inv.amount, 0.01))) * 100.0
            days_diff = (inv.invoice_date - d.closed_date).days
            dt_cost = asymmetric_date_cost(days_diff)
            st_cost = status_penalty(d.status, inv.status)

            cost_matrix[i, j] = amt_cost + dt_cost + st_cost

    # 2. Populate slack diagonals for MISSING_RECORD detection
    for i in range(n):
        cost_matrix[i, m + i] = rejection_threshold
    for j in range(m):
        cost_matrix[n + j, j] = rejection_threshold

    # 3. Bottom-right m x n dummy-to-dummy block set to 0
    cost_matrix[n:, m:] = 0.0

    # Solve global assignment using Jonker-Volgenant algorithm
    row_ind, col_ind = linear_sum_assignment(cost_matrix)

    exceptions: List[ExceptionRecord] = []

    for r, c in zip(row_ind, col_ind):
        if r < n and c < m:
            # Paired CRM deal and Accounting invoice
            d, inv = deals[r], invoices[c]
            amt_diff = round(d.amount - inv.amount, 2)
            days_diff = (inv.invoice_date - d.closed_date).days
            reasons = []

            if abs(amt_diff) > 0.01:
                reasons.append("AMOUNT_MISMATCH")
            if days_diff < 0 or days_diff > 5:
                reasons.append("DATE_DRIFT")
            if status_penalty(d.status, inv.status) > 0:
                reasons.append("STATUS_MISMATCH")

            # Only emit to exceptions table if a discrepancy exists
            if reasons:
                exceptions.append(ExceptionRecord(
                    deal_id=d.deal_id,
                    invoice_id=inv.invoice_id,
                    discrepancy_types=reasons,
                    amount_diff=amt_diff,
                    date_diff_days=days_diff,
                    assignment_cost=round(float(cost_matrix[r, c]), 2)
                ))

        elif r < n and c >= m:
            # Deal matched to a dummy column -> Missing in Accounting
            d = deals[r]
            exceptions.append(ExceptionRecord(
                deal_id=d.deal_id,
                invoice_id=None,
                discrepancy_types=["MISSING_RECORD_IN_ACCOUNTING"],
                amount_diff=d.amount,
                date_diff_days=None,
                assignment_cost=rejection_threshold
            ))

        elif r >= n and c < m:
            # Invoice matched to a dummy row -> Missing in CRM
            inv = invoices[c]
            exceptions.append(ExceptionRecord(
                deal_id=None,
                invoice_id=inv.invoice_id,
                discrepancy_types=["MISSING_RECORD_IN_CRM"],
                amount_diff=-inv.amount,
                date_diff_days=None,
                assignment_cost=rejection_threshold
            ))

    return exceptions

Example 2: F# Implementation (Horowitz-Sahni Meet-in-the-Middle for Split Invoices & Type-Safe Classification)

What happens when a single $50,000 CRM deal is billed across three milestone invoices of $20,000, $15,000, and $14,950 (after a $50 wire fee)? In the 1-to-1 bipartite graph above, all four records exceed the rejection threshold tau and fall into the MISSING_RECORD bucket.

To resolve 1-to-N split payments with fee tolerance, standard dynamic programming subset-sum is unusable because currency amounts are decimals and the match must allow a fee tolerance ± epsilon. Naive exhaustive search takes O(2^N) operations.

Using F#, we implement the Horowitz-Sahni Meet-in-the-Middle algorithm:

  1. Split the customer’s N unassigned invoices into two halves A and B of size N / 2.
  2. Generate all subset combinations for both halves in O(2^(N/2)) space and time.
  3. Sort subset sums of B and run a binary search for the complement (Target - sum_A) ± epsilon.

For N = 24 open invoices on a single corporate account, naive brute force evaluates 2²⁴ = 16,777,216 combinations, whereas Meet-in-the-Middle evaluates only 2 × 2¹² = 8,192 states.

F#

module SplitReconciliation

open System

type Status =
    | ClosedWon
    | Cancelled
    | Paid
    | Issued
    | Voided

type Discrepancy =
    | MissingRecord of source: string
    | AmountMismatch of delta: decimal
    | DateDrift of days: int
    | StatusMismatch of crmStatus: Status * accStatus: Status

type CrmDeal = {
    DealId: string
    TaxId: string
    Amount: decimal
    ClosedDate: DateTime
    Status: Status
}

type AccountingInvoice = {
    InvoiceId: string
    TaxId: string
    Amount: decimal
    InvoiceDate: DateTime
    Status: Status
}

type SubsetCombination = {
    TotalAmount: decimal
    Invoices: AccountingInvoice list
}

/// Generates all 2^k subset sums for a list of invoices
let private generateSubsets (invoices: AccountingInvoice list) : SubsetCombination array =
    let rec loop (remaining: AccountingInvoice list) (acc: SubsetCombination list) =
        match remaining with
        | [] -> acc
        | inv :: tail ->
            let withCurrent =
                acc |> List.map (fun s -> {
                    TotalAmount = s.TotalAmount + inv.Amount
                    Invoices = inv :: s.Invoices
                })
            loop tail (acc @ withCurrent)
    loop invoices [ { TotalAmount = 0m; Invoices = [] } ]
    |> List.toArray

/// Binary search for the closest subset sum in sorted array B within fee tolerance
let private findComplement
    (target: decimal)
    (tolerance: decimal)
    (sortedB: SubsetCombination array) : SubsetCombination option =
    
    let mutable low = 0
    let mutable high = sortedB.Length - 1
    let mutable bestMatch: SubsetCombination option = None
    let mutable bestDiff = tolerance + 1m

    while low <= high do
        let mid = low + (high - low) / 2
        let candidate = sortedB.[mid]
        let diff = abs (target - candidate.TotalAmount)

        if diff <= tolerance && diff < bestDiff then
            bestDiff <- diff
            bestMatch <- Some candidate

        if candidate.TotalAmount < target then
            low <- mid + 1
        else
            high <- mid - 1

    bestMatch

/// Horowitz-Sahni Meet-in-the-Middle solver for 1-to-N split invoices
let resolveSplitDeal
    (deal: CrmDeal)
    (unassignedInvoices: AccountingInvoice list)
    (feeTolerance: decimal) : (AccountingInvoice list * Discrepancy list) option =

    let n = unassignedInvoices.Length
    if n = 0 then None
    else
        let mid = n / 2
        let leftHalf = unassignedInvoices |> List.take mid
        let rightHalf = unassignedInvoices |> List.skip mid

        let subsetsA = generateSubsets leftHalf
        let subsetsB =
            generateSubsets rightHalf
            |> Array.sortBy (fun s -> s.TotalAmount)

        subsetsA
        |> Array.choose (fun subA ->
            let needed = deal.Amount - subA.TotalAmount
            match findComplement needed feeTolerance subsetsB with
            | Some subB ->
                let combinedInvoices = subA.Invoices @ subB.Invoices
                if List.isEmpty combinedInvoices then None
                else
                    let combinedTotal = subA.TotalAmount + subB.TotalAmount
                    let delta = deal.Amount - combinedTotal
                    Some (combinedInvoices, abs delta, delta)
            | None -> None
        )
        |> Array.sortBy (fun (_, absDelta, _) -> absDelta)
        |> Array.tryHead
        |> Option.map (fun (matchedInvoices, _, signedDelta) ->
            let discrepancies = [
                if signedDelta <> 0m then
                    yield AmountMismatch signedDelta

                let maxDrift =
                    matchedInvoices
                    |> List.map (fun i -> (i.InvoiceDate - deal.ClosedDate).Days)
                    |> List.maxBy abs
                if maxDrift < 0 || maxDrift > 5 then
                    yield DateDrift maxDrift

                for inv in matchedInvoices do
                    match deal.Status, inv.Status with
                    | ClosedWon, (Paid | Issued) -> ()
                    | Cancelled, Voided -> ()
                    | cStat, aStat -> yield StatusMismatch (cStat, aStat)
            ]
            (matchedInvoices, List.distinct discrepancies)
        )

5. Final Exceptions Table for Manual Review

Both the Dataform deterministic pass and the Cloud Run solver write their outputs into a single BigQuery table: mart.reconciliation_exceptions. Clean matches with zero discrepancies are filtered out of the operational queue so finance teams only inspect confirmed anomalies.

deal_idinvoice_idstax_idcrm_amountacc_amountdiffdate_drift_daysmatch_methoddiscrepancy_class
DL-9012INV-4401UA-30918212,500.0012,465.0035.002JONKER_VOLGENANTAMOUNT_MISMATCH
DL-9045INV-4419DE-88120448,000.0048,000.000.0019EXACT_IDDATE_DRIFT
DL-9088INV-4450, INV-4482US-77410930,000.0029,950.0050.0012MEET_IN_THE_MIDDLEAMOUNT_MISMATCH, DATE_DRIFT
DL-9104NULLUK-11029315,000.00NULL15,000.00NULLJONKER_VOLGENANTMISSING_RECORD_IN_ACCOUNTING
DL-9115INV-4501PL-5591028,200.008,200.000.001EXACT_IDSTATUS_MISMATCH

Why This Architecture Holds Up in Production

  1. Elimination of Greedy SQL Collisions: Replacing ROW_NUMBER() fuzzy joins with the Jonker-Volgenant global assignment algorithm prevents a single shifted invoice from corrupting adjacent matches in the same billing period.
  2. Exact Classification Without Manual Hunting: Every row in mart.reconciliation_exceptions arrives pre-classified (MISSING_RECORD, AMOUNT_MISMATCH, DATE_DRIFT, STATUS_MISMATCH) with the exact monetary delta and day offset already calculated.
  3. Strict Separation of Compute: BigQuery and Dataform handle high-volume set operations in SQL, while Cloud Run executes O(V³) matrix assignment and O(2^(N/2)) subset-sum algorithms only on the unresolved residual subset.

Similar Posts