# Comparison contracts and bridges

Copies must agree; different systems get a bridge, never a forced match. All numbers below are fictional (Acme Cloud, USD).

## Rules for every comparison

- Write the comparison contract before any query ([../assets/comparison-contract-template.yaml](../assets/comparison-contract-template.yaml)): entity, eligibility, key, period, date basis on each side, time zone, currency and value basis, counting unit, attribution scope, maturity cutoff, extract times, tolerance, owner. The comparison can run on the draft; until the owner approves it, REC-01 is WARN. Never edit it afterwards to bring the numbers closer.
- Compare record sets before totals. Keep missing keys (in A, not in B), unexpected keys (in B, not in A), duplicated keys and mismatched values apart; never net them.
- Report the signed difference (direction) and the absolute difference (how much disagreement the net hides), per currency. A key whose currency differs between the sides is never compared: both of its values count as disagreement, each in its own currency, and it is listed apart (KEY-08).
- Compare money in the source's currency and unit. Convert only under the documented FX policy, after the record-level comparison.
- Use mature periods only: days up to run date minus 4 (the GA4 update window); for ad conversions, the days inside the reload window at the last successful sync, with the platform export pulled after that sync and before the next (REC-03); closed months for billing.
- Rates need at least 200 eligible records; below that, report counts and extend the period.

## Equal totals can hide two errors

Billing has INV-1 100.00, INV-2 250.00 and INV-3 250.00. The warehouse has INV-1, INV-2 and INV-2 again. Both sides show 3 rows and 600.00. Run the record-level comparison (from the run folder, with `SKILL` set as in the procedure's step 5):

```bash
python3 "$SKILL/scripts/reconcile_exports.py" billing.csv warehouse.csv \
  --key invoice_id --measures amount --currency-col currency \
  --label-a billing --label-b warehouse
```

The output shows 1 missing key (INV-3), 1 duplicated key in the warehouse (INV-2, 1 excess row), a signed difference of 0.00 and an absolute difference of 500.00. A net-total check would have passed. After the repair, the pass condition is the exact key set plus the total, not the total alone.

## Copies: expected to match

The only by-design difference between a source and its copy is rounding. Everything else is a defect with a check ID.

- Platform to warehouse spend (REC-02): account currency, account time zone, account-day key, mature days. Each matched account-day within 1% or 1.00 (whichever is larger); each account-month within 0.5%. Missing keys score in FRS-03, currency in KEY-08, duplicates in KEY-01.
- Platform to warehouse conversions (REC-03): the same action set, the same column (`conversions`, not `all_conversions`) and the same date basis, for the days inside the reload window at the last successful sync (a 30-day rollback run on 2026-11-01 covers roughly 2026-10-03 onward); within 2% per account over that period. Record both extract times.
- Warehouse to dashboard (REC-08): three tiles per output, the same filters, period and time zone; within 0.1%.
- Implementation to implementation (MET-02): the same closed period, exported by date; within 0.1%.

Commands:

```bash
# Spend, platform export against Q19 output; months rolled up from the date column.
python3 "$SKILL/scripts/reconcile_exports.py" platform.csv warehouse.csv \
  --key platform,account_id,date --measures spend --currency-col currency \
  --rollup platform,account_id,month --month-from date --mature-through 2026-10-28
# A Google Ads export with cost in micros: add --scale-a spend=0.000001
# Two implementations of one metric (Q20 output for each):
python3 "$SKILL/scripts/reconcile_exports.py" dbt_metric.csv bi_field.csv \
  --key date --measures value --rel-tol 0.001 --abs-tol 0.01
```

## Analytics to backend (REC-04)

- Join by transaction ID, event ID or user ID on a frozen period. For ecommerce orders, hand the order-by-order classification to the purchase-reconciliation skill; its thresholds apply here (unexplained gap 2% pass and 5% fail, duplicate flags 0.5%, value flags 1%).
- Explained differences need evidence: consent (CMP logs or consent rates), measured blockers, documented exclusions. Everything else is the unexplained gap.
- Acme: GA4 `sign_up` against backend trials by event ID shows an unexplained gap of 3.1%, a WARN.

## Form log to CRM (REC-05)

- Key: a submission ID the form passes to the CRM (hidden field) or a backend submission log. Add a column `records` with the value 1 to each export and compare with `--key submission_id --measures records`.
- Classes: reached the CRM (created or matched to an existing record), missing in the CRM, and unexpected web-form records without a submission (often imports labelled as web forms).
- Explained: spam rejected by a documented rule; submissions merged into an existing contact when the merge is recorded.

## CRM to billing (REC-06)

Keep four measures separate. Each answers a different question, so none of them should be renamed "revenue":

- Booked contract value, excluding tax (sales).
- Invoiced amount excluding tax, after credits (billing).
- Customer cash: payments received less refunds, including tax (collections).
- Balance activity: cash less processing fees, in settlement currency (processor).

Relationships to model explicitly, never through email: internal customer ID to billing customer IDs; agreement ID to the CRM deal; agreement lines to subscription items; invoice lines to billable items; payment allocations to invoices; balance transactions to payments and adjustments. Include the billing account and live or test environment in every source key.

Worked bridge, Acme Cloud, as of 2026-10-31:

- On 2026-09-26 sales closes an annual plan of 24,000.00 billed 6,000.00 quarterly, plus a 3,000.00 onboarding fee on the first invoice. Booked contract value excluding tax: 27,000.00.
- On 2026-10-01 the first invoice is 9,000.00 excluding tax plus 10% tax of 900.00, total 9,900.00. It is paid on 2026-10-04 with a processing fee of 287.40.
- On 2026-10-15 a 600.00 service credit (excluding tax) is issued and refunded with its tax: 660.00.

The bridge:

1. Contract: 27,000.00 = first invoice 9,000.00 + future instalments 18,000.00 (all excluding tax).
2. Invoiced after the credit: 9,000.00 - 600.00 = 8,400.00.
3. Customer cash: 9,900.00 - 660.00 = 9,240.00. Cash includes tax, so it never equals the invoiced figure.
4. Balance activity: 9,240.00 - 287.40 = 8,952.60. This is processor activity, not a bank deposit.

- September bookings and October cash belong to different periods. Moving the deal's close date to October to make them meet hides the commercial history.
- The credit does not rewrite the booking. Record it as an adjustment; whether the contract was amended is a separate fact.
- The credit note and the refund describe one adjustment: the credit reduces the invoiced measure, the refund reduces cash. Subtracting both from one measure double counts the reduction.
- Never join invoice lines to every payment and balance transaction and then sum invoice totals. Aggregate each measure at its own grain, then join the aggregates, and count source IDs before and after each join.

Exception categories, each with amount, currency, source IDs, first-seen date, owner and next action:

- No commercial agreement: a self-serve purchase without a deal. Route it to the self-serve category; it is not an error.
- No billing relationship: the deal exists but its agreement ID never reached the subscription or invoice.
- Timing difference: a signed agreement awaiting its billing date.
- Amount-basis difference: tax, onboarding, credits or a different term on one side.
- Duplicate association: one invoice carries two full-value deal mappings (a REC-06 FAIL on its own).
- Incomplete extraction: missing invoice lines or payment allocations prevent a valid comparison.
- Unexplained adjustment: a credit, refund or dispute without commercial context.

Unexplained residual = sum of unexplained items / the period's billed value. Acme's October residual is 1.8% with no duplicate associations: WARN.

## Platform cost to finance spend (CAC numerator)

"CAC disagrees with finance" is usually a numerator problem before it is a data problem. The platform reports cost against activity dates; finance books invoiced media; a fully loaded CAC adds non-media cost. Compare per platform and month, never as one blended total.

- Read the CAC metric contract first. It must name its numerator: platform cost, invoiced media, or fully loaded sales and marketing cost. A dashboard and a finance pack that use different numerators under the name CAC fail MET-02, whatever this bridge shows.
- Compare platform cost (account currency, activity month) with invoiced media net of credits (finance, same platform and month).
- Remove these components in order, each with its amount and source document:
  1. Invalid-activity credits: Google credits invalid clicks found after billing on a later invoice where it can, so the credit lands in a different month from the clicks.
  2. Tax on invoices (VAT, GST, sales tax); platform cost excludes it.
  3. Agency fees and markups, when media is bought through an agency.
  4. FX at billing: the rate finance booked against the rate the dashboard used.
  5. Billing period against activity date: threshold or monthly billing that does not follow calendar months.
  6. Promotional credits and coupons: they reduce what is paid, not campaign cost.
  7. Non-media costs, only when the numerator is fully loaded: people, tools, content, events.
  8. Residual: what remains, labelled. An unexplained residual over 1% of invoiced media is a REC-07 WARN.
- The dashboard's numerator usually comes from the warehouse, not the platform. A gap between platform cost and the warehouse copy is FRS-03 or REC-02, never a bridge component.

Worked bridge, Acme Cloud, Google Ads, October 2026 (USD):

- Platform cost: 64,400.00.
- Finance's October invoice line for Google Ads media, excluding tax: 63,950.00, after a 450.00 invalid-activity credit for September clicks.
- Add back the credit, which belongs to September: 63,950.00 + 450.00 = 64,400.00. Residual 0.00: PASS.
- The Exec KPIs CAC read 57,500.00 for Google Ads from the warehouse because three days were missing. That 6,900.00 is FRS-03, scored there and not here.

## Platform claims to business outcomes (REC-07)

Platforms count differently by design, so this bridge explains the gap and labels what is left. It never forces a match and never fails on by-design differences. Remove components in this order:

1. Date basis: click date against conversion date; Meta impression time against conversion time.
2. Time zone: account time zone against business time zone at period edges.
3. Action set: which actions the platform counts (primary only, or all).
4. Counting: every conversion against one per click.
5. Window eligibility: platform windows (up to 90 days) against your own lookback.
6. View-through and engaged-view conversions: no click, so the backend cannot see them.
7. Modelled conversions: platforms include them in their conversion columns without a per-row count. Leave them in the residual unless the platform shows a figure for the account, cite where that figure came from, and never subtract an estimate as if it were reported. They stand for real outcomes the backend may already hold, so they are not a by-design gap.
8. Cross-platform overlap: the same outcome claimed by several platforms.
9. Consent: sessions the analytics side never saw.
10. Collection defects: confirmed losses, scored in REC-04 or MET-07, not here.
11. Residual: what remains, labelled. WARN when it exceeds 15% of business outcomes.

Worked bridge, Acme Cloud, Meta, October: Meta claims 520 trials (7-day click plus 1-day view). The backend attributes 226 trials to paid social sessions, so the claim is 2.3 times the outcome.

- 520 claimed
- minus 160 one-day view conversions without a click: 360
- minus 12 conversions outside the business-time-zone period: 348
- minus 48 trials whose clicks Google Ads also claims: 300
- modelled conversions: not removed. The Insights API documents no field that counts them, so they stay inside the residual.
- Residual against 226 backend trials: 74, which is 32.7% of business outcomes. WARN: investigate, and keep the residual labelled in every report that shows the claim.

## Materiality and tolerance

- Rounding is the only tolerance for copies.
- For different systems there is no blanket percentage: every difference gets a category, and the residual is what stays unexplained.
- Review exceptions by count and by value. Fifty unmatched 10.00 purchases and one unmatched 50,000.00 agreement need different attention.
- Track new, resolved and ageing exceptions separately, so a growing backlog cannot hide behind a stable match rate.