# Checks: identity (IDN), metric definitions and owners (MET), reconciliation (REC)

Conventions are the same as in [checks-freshness-and-keys.md](checks-freshness-and-keys.md): impact class in brackets, default thresholds that `metrics.md` or `decisions.md` can replace, NOT CHECKED when the evidence route is unavailable (record what would close it).

Minimum volumes for every rate in this file: at least 200 eligible records (contacts, leads, orders, outcomes) or 1,000 sessions. Below that, report the counts, mark WARN with "extend window" and do not quote a rate as a pass or fail.

Personal data stays in the warehouse. Every identity query returns counts, flags, account or record IDs that are not personal, or salted, truncated hash references that never leave the session. Never select emails, phones, names or click ID values, and never return a plain hash of one. The findings register records identity type, counts and flags; the owner reruns the query in the warehouse to locate the identifiers.

## Control segments for IDN-01

- Analytics join route (preferred): web-form CRM records created in the window whose first session was an auto-tagged Google Ads click (the analytics session carries a gclid). Those records should all hold a click ID.
- CRM-only route (when no analytics join exists): records whose original source is paid search from Google. Weaker, because CRM source logic can be wrong too (CHN-08); say which route was used.
- Why lowercase gclids fail: Google Ads click IDs are case-sensitive. A workflow, form script or spreadsheet step that lowercases them makes every offline upload unmatchable. The test is cheap: a gclid with lowercase letters and no uppercase letters at all.
- iOS app and web-to-app traffic can carry gbraid or wbraid instead of a gclid; count any of the three as a click ID.

## IDN-01 Click IDs captured into the CRM (B)

- Evidence: Q12 on the control segment.
- PASS: at least 95% hold a click ID (gclid, gbraid or wbraid), malformed or placeholder values at most 1%, lowercase-only gclids at most 1%.
- WARN: coverage from 80% to under 95%.
- FAIL: coverage under 80%, malformed values over 1%, or lowercase-only gclids over 1%.
- N/A: no Google Ads spend and no offline conversion route.
- Repeat for Meta (`_fbc` or fbclid) and LinkedIn (`li_fat_id`) when uploads depend on them. Thresholds match the tracking-audit skill's click ID check; capture fixes belong there.
- Precedence: click ID loss scores here, never in REC-07.

## IDN-02 Analytics-to-CRM join coverage (B)

- Evidence: share of web-form CRM records carrying an analytics key (client ID, user ID or lead event ID) that joins to a session in the warehouse.
- PASS: at least 90%, stating whether consent-denied submissions were excluded.
- WARN: 75% to under 90%. FAIL: under 75%.
- N/A: no output uses web behaviour joined to CRM records.

## IDN-03 CRM-to-billing crosswalk coverage (A)

- Evidence: paying billing customers in the window joined to CRM accounts through an explicit ID (billing customer metadata or a crosswalk table), by value and by count.
- PASS: at least 98% of billed value and 95% of paying customers mapped, all through explicit IDs.
- WARN: at least 90% of billed value mapped.
- FAIL: under 90% of billed value mapped, or email or domain matching acts as the authoritative join.
- N/A: no output joins billing to CRM.
- Note: metadata set on a billing customer does not always propagate to invoices and charges. Check the field on the object you actually extract.
- Wrong fix: fuzzy matching presented as fact. A manually approved mapping with a recorded reason is more defensible.

## IDN-04 No conflicting current mappings (A)

- Evidence: Q13a, source IDs with more than one current person or account mapping in the same namespace (environment, source system, source account, identity type). Record the namespace and the counts; the salted reference stays in the session.
- PASS: none. WARN: conflicts only in a mapping no critical output or activation uses. FAIL: any conflict in a mapping that feeds a critical output or activation.
- Never resolve conflicts with MIN(), the newest row or a random pick. Those hide the decision; list the conflicts for the owner.

## IDN-05 No over-linking through shared identifiers (A)

- Evidence: Q13b identifier fan-in, returned as a salted, truncated SHA-256 reference and a role-address flag (billing@, info@, sales@, noreply@ and similar), never the value. The register keeps identity type, the people count and the flag only; the owner reruns Q13b in the warehouse to find the identifier.
- PASS: no identifier value links more than 5 persons, or a documented block list covers them.
- WARN: values linking 6 to 20 persons, reviewed and documented as shared.
- FAIL: any value linking more than 20 persons, a placeholder acting as an identity key, or a role address merging distinct people in a mapping used by a critical output or activation.
- The 5 and 20 limits are defaults; set your own in `decisions.md`. Watch transitive merges: A shares an email with B, B shares a device with C, and an unrestricted graph merges all three.

## IDN-06 Identifier normalisation safe (B)

- Evidence: Q14a case, whitespace and leading-zero collisions (hashed); Q14b ID columns typed FLOAT64, NUMERIC, BIGNUMERIC, Snowflake FLOAT or NUMBER; scientific notation from spreadsheet imports.
- PASS: no collisions in join keys; IDs stored as strings. WARN: collisions only in display fields. FAIL: any collision or precision loss in a join key.
- Wrong fix: lowercasing every ID. Case-distinct source IDs then collide; keep the original and map it.

## Metric contract completeness (MET-01)

A complete contract fills 15 required fields: decision, entity, grain, numerator, denominator, time basis, time zone, window, maturity, exclusions, supported dimensions, revision policy, version, business owner and technical owner. "none" counts as filled when it is the answer (a count has no denominator). The template's other keys are recommended and do not count. Template: [../assets/metric-contract-template.yaml](../assets/metric-contract-template.yaml).

Completeness = filled required fields over required fields, across every metric shown on the critical outputs. It is a field count, not a count of complete metrics: 13 metrics need 195 fields, so 165 filled is 84.6% even if no single metric is complete.

## MET-01 Contract and owner per critical metric (B)

- Evidence: `metrics.md` or the semantic layer against every metric on the critical outputs.
- PASS: every metric fills every required field.
- WARN: at least 80% of required fields filled, gaps listed by field, and every money or conversion metric has a business owner.
- FAIL: under 80% of required fields filled, or a money or conversion metric without a business owner.
- A CAC contract names its numerator: platform cost, invoiced media, or fully loaded sales and marketing cost. A CAC contract without one has an empty numerator field.

## MET-02 One implementation per metric (A)

- Evidence: every implementation of each critical metric (semantic layer, mart column, BI calculated field, spreadsheet) run for the same closed period, exported by date (Q20) and compared with reconcile_exports.py `--key date --measures value`.
- PASS: implementations agree within 0.1%, or differences are documented versions with distinct names.
- WARN: they agree within 0.5%, or results are identical but the logic is duplicated in two or more places.
- FAIL: two implementations under one name differ by more than 0.5%, or use different filters, statuses or currency rules.
- CAC under one name with different numerators (platform cost on the dashboard, invoiced media in the finance pack) fails here. The bridge that explains the gap between the two numerators is in [reconciliation-bridges.md](reconciliation-bridges.md#platform-cost-to-finance-spend-cac-numerator).
- GA4 reports against the export: reports and the Data API estimate Sessions and Active users with HyperLogLog++, while the export holds exact rows (Google's own example shows 1,463 sessions in reports and 1,501 in BigQuery for one week, 2.5% apart). Compare a GA4-report implementation with an export implementation on event or key event counts. For sessions and users, record the gap as by design instead of scoring it.
- Precedence: two implementations that differ score here; REC-08 covers cache, filter scope and blends against the tile's declared source.
- Wrong fix: picking the larger number.

## MET-03 Aggregation rules respected (A)

- Evidence: SQL and BI field review; recompute one period from numerators and denominators.
- PASS: ratios computed from summed numerators and denominators, snapshots not summed over time, no SUM(DISTINCT amount), weights explicit.
- FAIL: an averaged ratio (for example the mean of platform ROAS values), a summed snapshot such as daily MRR, or a distinct-value sum in a critical output. WARN: not used.
- Example: campaign A has 10 conversions from 100 clicks (10%), B has 100 from 10,000 (1%). The mean of rates is 5.5%; the true rate is 110 / 10,100 = 1.09%.

## MET-04 Attributed totals kept apart from business totals (A)

- Evidence: tile and model definitions; business totals compared with the system of record.
- PASS: business totals come from the system of record; attributed values are labelled by platform and rule; no cross-platform sum of claimed conversions or revenue is presented as a company total.
- FAIL: a critical output sums platform-claimed conversions or revenue across platforms, or labels attributed value as revenue. WARN: not used.
- Wrong fix: de-duplicating platform claims by guesswork. Show the business total and the labelled claims side by side.

## MET-05 Currency conversion policy and rate table sound (B)

- Evidence: FX policy in `decisions.md`; rate table uniqueness per pair, policy and date; rate direction; conversion before aggregation.
- PASS: rate type, date basis and direction documented (write it as "reporting currency units per one original unit"); one rate per pair, policy and date; conversion before aggregation.
- WARN: the policy is undocumented but applied consistently.
- FAIL: duplicate rate rows for a pair and date (they multiply amounts in the join), the wrong rate direction, or ratios averaged across currencies.
- N/A: single-currency business with single-currency sources.
- Decision owner: finance. The audit reports the policy; it does not choose it.

## MET-06 Definition changes versioned (C)

- Evidence: change log or version history for critical metrics.
- PASS: dated versions with the historical treatment (restated, parallel series or cutover).
- WARN: no change log, or a change of meaning found without a historical treatment. FAIL: not used.

## MET-07 Platform conversion and spend fields mapped correctly (A)

- Evidence: distinct action types and columns per platform against the platform's own report for the same period.
- Google Ads: `conversions` (primary actions in the Conversions column) against `all_conversions`; interaction-date columns against `conversions_by_conversion_date` (shown as "Conversions (by conv. time)"); primary against secondary actions.
- Meta: action types, `action_report_time` and attribution windows. Aggregate action types overlap with their component types; verify the hierarchy for the account at run time. Fivetran warns that summing per-action rows can double count overlapping events; its `total_actions` is pre-aggregated.
- LinkedIn and Microsoft: which conversion definitions feed the column the model reads.
- PASS: mapping documented per platform; no overlapping action types summed; column and date basis match the metric contract.
- WARN: mapping correct on inspection but undocumented.
- FAIL: overlapping action types summed, `all_conversions` used where `conversions` was intended, interaction-date and conversion-time columns mixed, or Meta 7-day or 28-day view windows still expected (not returned since 12 January 2026) so nulls read as zero.
- Precedence: a wrong column or overlapping types score here even when REC-03 shows the gap.

## Reconciliation thresholds by pair

Write the comparison contract first ([../assets/comparison-contract-template.yaml](../assets/comparison-contract-template.yaml)). Copies must agree; different systems get a bridge, never a forced match. Arithmetic and bridge components: [reconciliation-bridges.md](reconciliation-bridges.md).

## REC-01 Comparison contract per pair (B)

- PASS: each compared pair has an approved contract: entity, eligibility, period and date basis, time zone, currency and value basis, counting unit, attribution scope, key, maturity cutoff, tolerance and owner.
- WARN: contracts drafted during the audit and awaiting approval.
- FAIL: a recurring reconciliation report compares totals with different date bases, scopes or currencies.
- N/A: never.

## REC-02 Ad spend agrees between platform and warehouse (A)

- Evidence: reconcile_exports.py on a platform export (Google Ads MCP `search`, Ads Manager or Campaign Manager export) against Q19, per account and day, mature days, matched keys.
- PASS: every matched account-day within 1% or 1.00 in account currency (whichever is larger), and every account-month within 0.5%.
- WARN: some account-days outside, but every account-month within 2%, each explained.
- FAIL: any account-month outside 2% on matched keys.
- Precedence: missing slices score in FRS-03, currency or unit errors in KEY-08, duplicates in KEY-01. REC-02 reads the matched-only lines of the script output; keys whose currency differs between the sides never reach those lines, because the script lists them apart as not compared.

## REC-03 Ad conversions agree between platform and warehouse copy (A)

- Period: the days inside the reload window at the last successful sync, read from the connector's sync log and settings, up to the window end. A 30-day rollback that last ran on 2026-11-01 covers roughly 2026-10-03 onward, so with a window ending 2026-10-28 REC-03 compares 2026-10-03 to 2026-10-28. When click windows (up to 90 days) are longer than the reload window, no whole month qualifies; never compare a calendar month that started before the reload window.
- Timing: pull the platform export after that sync and before the next one, and record both extract timestamps (`as_of_a`, `as_of_b` in the comparison contract). Conversions recorded after the sync are not a defect in the copy.
- Evidence: the same action set, column and date basis on both sides, for that period.
- PASS: within 2% per account over the period. WARN: over 2% and up to 5%. FAIL: over 5% with the same action set and column.
- A gap on days older than the reload window is evidence for FRS-05; a different action set or column scores in MET-07.

## REC-04 Analytics outcomes agree with backend or billing (A)

- Evidence: record-level 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.
- PASS: unexplained gap at most 2% of eligible outcomes and value; duplicate flags at most 0.5%; value flags at most 1%.
- WARN: unexplained gap over 2% and up to 5%.
- FAIL: unexplained gap over 5%, duplicate flags over 0.5%, value flags over 1%, or analytics outcomes for records that never reached the paid or created state.
- N/A: no analytics outcome is used by a critical output. Thresholds match purchase-reconciliation.
- Precedence: confirmed collection defects score here; REC-07 never fails on by-design differences.

## REC-05 Lead submissions reach the CRM (B)

- Evidence: form or backend submission log against CRM records created or matched, keyed by submission ID (reconcile_exports.py).
- PASS: at least 98% of eligible submissions reach the CRM and unexpected web-form records without a submission are at most 1%.
- WARN: 95% to under 98%, or unexpected records over 1% and up to 3%.
- FAIL: under 95%, or unexpected records over 3%. N/A: no lead outputs.

## REC-06 CRM and billing bridge explained (A)

- Evidence: a bridge of booked, invoiced, collected and balance measures with categorised exceptions: no agreement, no billing relationship, timing, amount basis, duplicate association, incomplete extraction, unexplained adjustment.
- PASS: unexplained residual at most 1% of the period's billed value, no single unexplained item over 0.5%, self-serve purchases classified.
- WARN: unexplained residual over 1% and up to 3%.
- FAIL: residual over 3%, any invoice associated with two full-value deals, or the bridge cannot be built because allocations or lines are missing.
- N/A: no output combines CRM and billing. Revenue truth stays with billing and finance.

## REC-07 Platform numbers bridged to business totals (B)

- Evidence: a bridge per platform from claimed conversions to business outcomes: date basis, time zone, action set, counting, window eligibility, view-through and engaged-view, modelled conversions (left in the residual unless the platform reports a figure), cross-platform overlap, consent, collection defects, residual. When an output shows CAC or ROAS next to finance numbers, also the bridge from platform cost to invoiced media per platform and month ([reconciliation-bridges.md](reconciliation-bridges.md)).
- PASS: a bridge per platform with named components and a labelled residual, and a cost residual of at most 1% of invoiced media.
- WARN: a conversion residual over 15% of business outcomes after named components (investigate), a cost residual over 1% of invoiced media, or a bridge only partly built.
- FAIL: not used. By-design differences are never defects; confirmed delivery defects score in REC-04 or MET-07, different CAC numerators under one name in MET-02, missing spend days in FRS-03.
- N/A: no output shows platform-attributed results or CAC against finance.

## REC-08 Consumers show the warehouse result (B)

Dashboards:

- Evidence: three tiles per critical output re-run against their declared source with the same filters, period and time zone; cache and extract age; blends.
- PASS: exact match (within 0.1%) and a data-through date shown.
- WARN: match but no data-through or provisional label, or a cache older than the refresh contract.
- FAIL: a difference over 0.5% for the same filters, a blend or join in the BI tool inflating totals, or stale data shown as current.
- Blend check: a campaign-day spend row joined to three lead rows repeats the spend three times. Aggregate leads to campaign-day before the join; never paper over it with MAX().

Audience syncs and conversion uploads:

- Evidence: three numbers for the same days. Rows the model selects (a count query); rows the sync sent, from its run log (added, changed, removed, failed); rows the destination accepted. For Google Ads uploads, the destination numbers come from the Google Ads MCP `search` tool on `offline_conversion_upload_client_summary` and `offline_conversion_upload_conversion_action_summary` ([read-only-access.md](read-only-access.md#platform-reads)): status, success rate, `daily_summaries` for the last 7 days, `job_summaries` for the last 7 jobs and alerts by OfflineConversionError. Pending rows can take up to 24 hours, so read days that have settled.
- PASS: rows sent match rows selected (differences explained by documented exclusions), and failed plus rejected rows are at most 2% of rows sent.
- WARN: failed plus rejected rows over 2% and up to 5% of rows sent, or the status is NEEDS_ATTENTION. GOOD changes nothing by itself; record its alerts as evidence.
- FAIL: failed plus rejected rows over 5%, a scheduled upload showing NO_RECENT_UPLOAD (no data for 28 days), or rows rejected for format (a `conversion_date_time` without a time zone offset, or a conversion before its click).
- Consent: count the rows sent without `consent.ad_user_data`, by region. Google warns that such conversions may not be attributable. Any such row is a WARN for the privacy owner to decide; the audit never chooses the consent basis.
- Audiences: compare model rows with the sync's added and removed counts and the destination's own list size or match report; record the platform's match rate as evidence, not as a defect, because matching depends on the platform's user base.
- Precedence: rows the model never selected because of a broken join score in KEY or IDN checks; this check covers what happens after the model.