Cross-Vendor Billing Reconciliation

Cross-Vendor Billing Reconciliation

Overview

MSPs purchase cloud subscriptions through distributors like Pax8 and resell them to clients, billing through an accounting platform such as Xero or QuickBooks Online. Revenue leakage occurs when active subscriptions are not reflected on client invoices -- a common problem as seat counts change, new products are provisioned, or billing staff miss updates. This skill teaches Claude how to systematically compare Pax8 subscription data against accounting invoices to identify billing gaps, quantity mismatches, price discrepancies, and margin erosion.

The reconciliation answers one fundamental question: "Is every active Pax8 subscription being billed to the correct client at the correct quantity and price?"

Reconciliation Workflow

Step 1: Pull Active Pax8 Subscriptions with Pricing

Fetch all active subscriptions from Pax8, grouped by company. Each subscription includes product name, quantity (seat count), unit price, billing term, and status.

GET /v1/subscriptions?status=Active&page=0&size=200
Authorization: Bearer YOUR_TOKEN

For a specific company:

GET /v1/subscriptions?companyId={companyId}&status=Active&page=0&size=200

Key fields to extract per subscription:

• companyId -- resolve to company name via /v1/companies/{companyId}
• productId -- resolve to product name via /v1/products/{productId}
• quantity -- number of seats/licenses
• price -- unit price (your cost from Pax8)
• billingTerm -- Monthly or Annual
• startDate / endDate -- subscription period
• status -- confirm Active

Build a subscription ledger:

Pax8 Subscription Ledger
────────────────────────────────────────────────
Company          Product                    Qty  Unit Price  Monthly Total
Acme Corp        M365 Business Premium       25     $17.10       $427.50
Acme Corp        Azure AD P1                 25      $7.50       $187.50
Acme Corp        Acronis Backup 500GB         1     $85.00        $85.00
Beta LLC         M365 Business Basic         10      $7.20        $72.00
Beta LLC         SentinelOne Complete        15     $4.00         $60.00
────────────────────────────────────────────────

Step 2: Pull Recent Invoices from Accounting Platform

Xero

Fetch sales invoices (ACCREC) for the billing period:

curl -s -X GET "https://api.xero.com/api.xro/2.0/Invoices?where=Type==%22ACCREC%22&&Date>=DateTime(2026,2,1)&&Date<=DateTime(2026,2,28)" \
  -H "Authorization: Bearer ${ACCESS_TOKEN}" \
  -H "xero-tenant-id: ${XERO_TENANT_ID}" \
  -H "Accept: application/json"

For a specific contact (client):

curl -s -X GET "https://api.xero.com/api.xro/2.0/Invoices?where=Contact.ContactID==guid(%22${CONTACT_ID}%22)&&Type==%22ACCREC%22&&Date>=DateTime(2026,2,1)" \
  -H "Authorization: Bearer ${ACCESS_TOKEN}" \
  -H "xero-tenant-id: ${XERO_TENANT_ID}" \
  -H "Accept: application/json"

Key fields: Contact.Name, LineItems[].Description, LineItems[].Quantity, LineItems[].UnitAmount, LineItems[].LineAmount, Invoice.Status, Invoice.Date

QuickBooks Online

Query invoices for the billing period:

SELECT * FROM Invoice WHERE TxnDate >= '2026-02-01' AND TxnDate <= '2026-02-28' ORDERBY TxnDate DESC

For a specific customer:

SELECT * FROM Invoice WHERE CustomerRef = '123' AND TxnDate >= '2026-02-01'

Key fields: CustomerRef.name, Line[].Description, Line[].SalesItemLineDetail.Qty, Line[].SalesItemLineDetail.UnitPrice, Line[].Amount, Balance, TxnDate

Step 3: Match Subscriptions to Invoice Line Items

For each active Pax8 subscription, search the corresponding client's invoices for a matching line item. Matching uses a combination of product name, quantity, and amount (see Matching Strategy below).

Matching priority:

1. Company name match (Pax8 company to Xero contact / QBO customer)
2. Product name fuzzy match (subscription product to invoice line description)
3. Quantity comparison
4. Amount comparison (within tolerance)

Step 4: Identify Gaps -- Active Subscriptions with No Matching Invoice Line

Any active Pax8 subscription that cannot be matched to an invoice line item is a billing gap. These represent potential revenue leakage.

Flag as CRITICAL:

• Active subscription exists in Pax8
• No invoice line found in the accounting platform for this product/client combination in the billing period
• Estimated monthly revenue loss = quantity x sell price

Step 5: Identify Discrepancies -- Quantity Mismatches, Price Differences, Margin Erosion

When a match is found but the numbers do not align:

• Quantity mismatch: Pax8 shows 25 seats but invoice shows 20 seats (5 seats unbilled)
• Price discrepancy: Unit price on invoice does not maintain expected margin over Pax8 cost
• Total mismatch: Line amount does not equal quantity x unit price (possible manual override)

Calculate margin:

Margin % = ((Sell Price - Pax8 Cost) / Sell Price) x 100

Example:
  Pax8 cost:  $17.10/seat
  Invoice:    $22.00/seat
  Margin:     22.3%

  If target margin is 25%, flag as MEDIUM (margin erosion)

Step 6: Generate Reconciliation Report with Severity Indicators

Compile all findings into a structured report grouped by severity.

Matching Strategy

Matching Pax8 subscriptions to accounting line items is the core challenge. Product names are rarely identical across systems because MSPs abbreviate, customize, or bundle products on invoices.

Product Name Fuzzy Matching

| Pax8 Product Name | Possible Invoice Line Description | |---|---| | Microsoft 365 Business Premium | M365 Bus Premium, Microsoft 365 BP, M365 Business Premium Licenses | | Microsoft 365 Business Basic | M365 Basic, Microsoft 365 BB, O365 Business Basic | | SentinelOne Singularity Complete | SentinelOne, S1 Complete, Endpoint Protection | | Acronis Cyber Protect Cloud | Acronis Backup, Cloud Backup, Acronis BDR | | Microsoft Azure AD P1 | Azure AD Premium, Entra ID P1, AAD P1 | | Datto SaaS Protection | Datto Backupify, SaaS Backup, M365 Email Backup |

Matching rules (apply in order):

1. Exact contains -- Invoice description contains the full Pax8 product name (case-insensitive)
2. Key token match -- Core product tokens match (e.g., "365" + "Premium" or "SentinelOne" + "Complete")
3. Abbreviation expansion -- Common MSP abbreviations: M365 = Microsoft 365, O365 = Office 365, S1 = SentinelOne, BDR = Backup and Disaster Recovery
4. Manual mapping table -- MSP-maintained table of Pax8 product to invoice line description mappings (recommended for accuracy)

Company Name Matching

The company name is the cross-vendor correlation key:

1. Exact match -- Pax8 company name equals Xero contact name / QBO customer display name
2. Contains match -- Partial match (e.g., "Acme" matches "Acme Corporation" or "Acme Corp")
3. DBA / trading name -- Check Xero Contact.Name and Contact.FirstName/Contact.LastName, or QBO Customer.CompanyName vs Customer.DisplayName
4. Ask user -- If no match or multiple matches, present options and confirm

Amount Comparison with Tolerance

Invoice amounts may differ slightly from Pax8 amounts due to rounding, tax handling, or currency conversion:

• Tolerance: +/-5% of expected amount
• Expected sell amount: Pax8 quantity x sell price (where sell price = Pax8 cost + margin)
• Exact match: Within $0.01 difference
• Close match: Within 5% difference (flag as INFO)
• Discrepancy: Greater than 5% difference (flag based on severity)

Quantity Comparison

Quantity should match exactly between Pax8 subscription and invoice line item:

• Exact match: Quantities are equal
• Minor discrepancy (<=10%): May indicate recent seat change not yet reflected
• Major discrepancy (>10%): Flag as HIGH severity

Vendor Field Mappings

| Concept | Pax8 Field | Xero Field | QBO Field | |------