# Check catalogue

Every check the profiler runs, with its population, condition, status rule, reason and how to confirm it in the CRM. Defaults come from `assets/rules-template.json`; change one only with a `decisions.md` entry.

## Status meanings

- FAIL: the data can message someone who opted out or bounced, put revenue in the wrong place, leave an open deal or customer without an accountable owner, or make joins and merges unsafe. Zero tolerance unless `decisions.md` records an exception; cite it when downgrading.
- WARN: above a non-blocking threshold, or a candidate that needs a person's judgement.
- INFO: context that changes how other checks read (role mailboxes, personal domains, bounce classes).
- PASS: ran on its full population and found nothing above threshold, reported with numerator and denominator.
- NOT CHECKED: a needed column is unmapped, blank on every row, no definition exists, or the check's population is empty (0 of 0 proves nothing). Never shown as PASS.
- N/A: does not apply to this CRM or object (Salesforce ID shape on HubSpot, marketing contact status outside HubSpot).

## Populations and denominators

- Every rate is numerator over the population the rule applies to, never all records. The summary prints both and names the population. How to read each line: [profiler.md](profiler.md).
- Test and internal records (F07) leave every population except the X family, F07 and E02, which scan every row because junk values are how test data shows up.
- Converted Salesforce leads (IsConverted true) are archived source records that nobody works. They count only in the X family, F07 and L10; every other check, the duplicate engine and the marketing population leave them out, and summary.md says how many were set aside. Keep them in the export with IsConverted, because L10 needs them; without that column every lead counts as open, summary.md says so and L10 is NOT CHECKED. An open lead with a converted lead's email is D07 (convert it into the contact), never a merge.
- Marketing population: HubSpot "Marketing contact status" when the column exists; otherwise records with an email that are not opted out (Salesforce, Attio, generic). The summary says which definition ran.
- Overlaps are never added. A record can sit in five checks; "unique affected records" is computed per object from record IDs.
- Coverage: a rate on a PARTIAL or UNCONFIRMED export is labelled with it. Never call an object clean without FULL coverage.

## X: export integrity and coverage (read these first)

- X01 duplicate-record-ids. All objects. Same record ID on more than one row, or rows without an ID. FAIL at 1. Report exports repeat parents on child rows; files get concatenated. Confirm: search the ID in the CRM, it returns one record.
- X02 id-damage. Record ID, association IDs, unique key, phone. Scientific notation (1.23457E+11) or a trailing .0. FAIL at 1. The file went through a spreadsheet and digits are lost; nothing built on these IDs is safe. Re-export.
- X03 salesforce-id-shape. Salesforce preset, or 90% of IDs shaped like Salesforce IDs. 15-character IDs WARN. FAIL when two 15-character IDs are equal once lowercased, two 18-character IDs are equal once lowercased, or an 18-character ID's last three characters do not match its letter case. Valid 18-character IDs that differ only in the case of their first 15 characters are different records and pass: letter case is part of the ID, so such pairs are normal. Otherwise N/A. 15-character IDs are case-sensitive and case-insensitive tools join the wrong records. Convert with `CASESAFEID(Id)` before any join, while the case is intact.
- X04 encoding. Mojibake such as "é" or "’", replacement characters, or a file that only decodes as cp1252. WARN. Names and matching break; re-export as UTF-8.
- X05 structure. Rows with the wrong number of fields. FAIL at 1. Rows were shifted or cut; counts are unknown. For Salesforce Data Export, choose "Replace carriage returns with spaces".
- X06 blank-column. A mapped column, unique key or rules column blank on every row. WARN, reported as NOT RETRIEVED, and every check that needs it is NOT CHECKED, never FAIL. Causes: Salesforce field-level security, Attio enriched attributes, a HubSpot view export without the property, a wrong mapping.
- X07 association-truncation. A HubSpot association cell with 1,000 or more IDs. WARN. The default export includes up to 1,000 associated IDs per column; export with "All associated records" (CSV only).
- X08 coverage. Rows against `--expected-count`. PASS when equal (FULL); FAIL when different (PARTIAL until the difference is explained, for example records created after the export time); NOT CHECKED without a count (UNCONFIRMED).
- X09 mapping. Two headers that normalise to the same name. WARN; fix with `--map OBJECT.FIELD=Header`. The mapping report lists every canonical field as mapped, not mapped or ambiguous; a preset is a guess until the report is checked.
- X10 formula-leading-values. Cells starting with = or @, with + or - followed by a letter, bracket or =, with a tab, carriage return or line feed, or with the full-width forms of =, +, - and @. WARN. A spreadsheet would run them (CSV injection). The script writes any cell starting with one of those characters into its own CSV files with a leading apostrophe, phone numbers and negative amounts included.

## F: fields

- F01 required-missing. One line per `rules.required` entry. Missing means blank, spaces or a placeholder. Critical: FAIL at 1. Non-critical: FAIL above 20% of the population and at least 20 records, otherwise WARN at 1. A field missing on more than a fifth of its records cannot drive routing, segmentation or automation. No entries for an object: NOT CHECKED.
- F02 placeholder-values. Names, phones, title, company, domain, location, picklists, unique key. n/a, na, none, null, unknown, tbd, test, asdf, x, -, --, ., all-same-digit or sequential phones, one character repeated. Values in `allowed_values` are exempt; recognised country codes are exempt. WARN, counted apart from blanks so completeness is not inflated.
- F03 picklist-drift. One line per `allowed_values` entry, compared after folding case and punctuation. WARN; FAIL when the field is critical in `required`. API writes and unrestricted picklists accept anything and filters silently miss the records.
- F04 format-variants. Country and US state. One concept stored several ways (US, USA, United States). WARN with the variants, counts and the proposed canonical value (the most common spelling). The country alias table covers about 60 countries by ISO code, alpha-3 and English names; unknown spellings are not flagged.
- F05 spreadsheet-damage. A 4-digit postal code where the country uses 5 digits (US, DE, FR, IT, ES), phones in scientific notation, ISO and slash dates mixed in one column. WARN.
- F06 whitespace-and-case. Leading, trailing or double spaces; non-breaking or zero-width characters; all-caps names of 4 or more letters. INFO; WARN when invisible characters sit in email, domain, website or ID fields. HubSpot's data quality tool does not flag a record again after a later bad write, so a cleared queue proves nothing.
- F07 test-and-internal. Internal domains, test domains, reserved documentation domains (example.com, example.net, example.org and the .example, .test, .invalid, .localhost names, unless `--allow-reserved-domains`), and first or last name test, asdf, dummy or sample. WARN; excluded from every other denominator; listed for the retention owner.

## E: email and reachability (persons)

- E01 invalid-email. Not exactly one @, empty local part or one over 64 octets, domain over 255 octets, a domain label over 63 octets, address over 254 characters, spaces, consecutive dots, labels starting or ending with a hyphen, a last label that is neither 2 or more letters nor an xn-- label (limits from RFC 5321). Internationalised domains are converted to their xn-- form first, so they pass. FAIL at 1 in the marketing population, WARN elsewhere.
- E02 placeholder-email. Local part none, noemail, no-email, test, na, unknown, asdf; test domains; reserved domains unless allowed. WARN. Scans test rows too.
- E03 typo-domain. Misspellings of major mailbox providers (built-in list plus `rules.typo_domains`). WARN with the correction as a draft; never auto-correct. A typo can reach a stranger.
- E04 role-address. RFC 2142 role names (info, marketing, sales, support, abuse, noc, security, postmaster, hostmaster, usenet, news, webmaster, www, uucp, ftp) and common ones (admin, billing, contact, hello, help, office, team, enquiries, inquiries, accounts, hr, jobs, careers, noreply, no-reply, donotreply). INFO; excluded from person matching.
- E05 personal-email-on-b2b. Personal provider domains when `rules.b2b` is true. INFO; they never corroborate a company match. N/A for B2C.
- E06 bounced-still-marketable. Bounce reason present, bounced flag true (IsEmailBounced, on Salesforce contacts) or a bounce date (EmailBouncedDate, on Salesforce contacts and leads), inside the marketing population. FAIL at 1. In HubSpot the platform already drops hard-bounced addresses from future marketing emails and sends no marketing email to contacts unsubscribed from all email, so the risk is the marketing contact bill and any tool that syncs marketing status. In Salesforce, or a CRM that feeds another sender, these addresses can be emailed today. A HubSpot global bounce can leave "Email hard bounce reason" empty when your own sends never bounced, so an empty reason never proves an address is sendable.
- E07 opted-out-but-billed. "Unsubscribed from all email" true on a HubSpot marketing contact. WARN. Marketing status is billing classification, not permission. N/A without a marketing status column.
- E08 bounce-classes. INFO with counts: address-dead (unknown user, no such user, does not exist, invalid recipient, mailbox disabled, recipient not found, address not found, enhanced codes 5.1.0 to 5.1.3, 5.1.6, 5.1.10 and 5.2.1), mailbox-full (mailbox full, over quota, 5.2.2), sender-side (content, spam, policy, blocked, reputation, DMARC, SPF, DKIM, authentication, 5.7.x), temporary (4.x.x, pending, soft), unknown. A code wins over words. Words match only at the start of a word, and "soft" only as a whole word, so neither "Microsoft" nor "software" reads as a soft bounce. Underscores read as spaces, so UNKNOWN_USER is address-dead. EmailBouncedReason holds the bounce reason as text, up to 255 characters on Lead. Salesforce doesn't document its format, so check real values before relying on the word matching. Only address-dead bounces are decay (R03); a full mailbox can recover, but it still counts in E06 because HubSpot stops marketing sends to it; sender-side bounces go to deliverability.

## R: decay and engagement (marketing population)

- R01 dormant. Latest contact-initiated engagement (last engagement, last form submission, last click, whichever are mapped) older than 365 days. WARN at 10% of the population, else PASS with the count. Rep-logged activity is not engagement and opens are weak evidence. Report for a sunset decision; never delete from this audit.
- R02 never-engaged. Created more than 180 days ago with no engagement date. WARN at 1. Candidates for non-marketing status at the next update date.
- R03 address-decay-rate. Address-dead bounces over records with at least one send (when a send count is mapped), else over records with an email. INFO; comparable month to month only with the same denominator.
- R04 decay sample (agent step, not script). Sample 200 records from one population, verify the fact you rely on (employer, role, reachability) with first-party evidence only. Report verification coverage and the defect rate among verified records. Arithmetic: 200 sampled, 160 verifiable, 10 wrong gives 80% coverage and 6.25% defects among verified, not a whole-CRM claim. Method: [decay-and-bounces.md](decay-and-bounces.md).

## D: duplicates (full population only)

- D01 same-unique-key. Same non-blank, non-placeholder value in a declared unique key (product user ID, billing account ID, source opportunity ID). Certain; FAIL at 1 group. WARN when one key value sits on more than 50 records (too many to compare: the key is not unique). NOT CHECKED when no key is declared.
- D02 same-email. Same normalised email across primary and additional emails. Certain; FAIL at 1 group. Role mailboxes shared by several records are INFO "shared role mailbox" and never grouped. HubSpot blocks duplicate primary emails, so HubSpot hits come from additional emails or invisible characters; Salesforce has no such block.
- D03 email-variant. Same mailbox after removing a plus tag (any domain) or dots in the local part (dot-insensitive domains). Probable (`d03_band`); WARN. Plus tags are often deliberate per subscription: review consent before merging.
- D04 person-fuzzy. Name similarity at least 0.92 with a corroborating key (same non-personal email domain, same associated company, same phone): probable (`d04_band`). At least 0.88 with the same company name only: possible. WARN. Two people with the same name at one company domain score 1.0, which no similarity threshold can separate: when calibration finds them, set `d04_band` to possible.
- D05 company. Same domain and name similarity at least 0.93: probable. Same normalised name and phone: probable (`d05_name_phone_band`). Same normalised name and country: probable (`d05_name_country_band`), but possible when both records hold domains and none is shared ("same name, different domains"). Same domain, different names: possible (shared-domain review). Name similarity at least 0.93 in the same country when a domain is missing: possible. WARN. Companies created through the HubSpot API, sync apps included, are not deduplicated by domain.
- D06 deal. Same company and pipeline, both open, amounts within 5%, created within 30 days: probable (`d06_band`). Same company and deal name, both open: possible. Excluded: a renewal deal type, or close dates 300 or more days apart. WARN. NOT CHECKED without deal to company association columns.
- D07 lead-matches-contact. Salesforce unconverted lead with a contact's email. Probable; WARN. Convert the lead into the contact instead of creating a second person. This is how a person who comes back shows up: the old converted lead never joins a duplicate group.
- D08 shared-value. A phone shared by 4 or more different names, or a blocking value on more than 50 records. INFO; reported once, never paired. WARN when the oversized value is a unique key or an email: fix the key or the writer that fills it.
- D09 rule-breadth-and-chains. Any group above 10 records, or a rule flagging more than 10% of an object with an average group above 3 (the warning HubSpot shows for broad custom rules). WARN: split by hand, check the key.
- Different non-blank unique keys cap any pair at possible with a key conflict, and no group may hold two different keys: a link that would chain them is refused and listed as a possible pair. Rules, bands and calibration: [matching-rules.md](matching-rules.md).

## O: ownership

- O01 missing-owner. FAIL at 1 for open deals and for customers on the objects in `owner_critical_customer_objects` (default contacts and companies). WARN at 1 for persons at `owner_required_lifecycle` stages, unconverted Salesforce leads and companies with open deals. Teams that own customers at company level have two settings: remove contacts from `owner_critical_customer_objects` (customer contacts become WARN, or drop out when customer is also removed from `owner_required_lifecycle`), or set `contact_owner_from_company` to true, so a contact without an owner counts as owned when an associated company has an active human owner. The detail line counts the contacts owned that way; the setting needs contact to company association columns. O02 still reads each contact's own owner.
- O02 inactive-or-removed-owner. Owner inactive in the users file, or a HubSpot owner value reading "Deactivated/Removed (...)". FAIL for open deals and customers, WARN otherwise. HubSpot keeps deactivated users as owners; the rotate-record-owner workflow action skips them, but group and round-robin meeting pages keep booking them until they are removed or reassigned. NOT CHECKED without a users file unless removed labels are present.
- O03 unknown-owner. Owner not found by ID, name or email in the users file or queues. WARN. Stale IDs, Salesforce queues, or an incomplete users file.
- O04 integration-owner. Users file kind integration, or a whole-word match on `integration_owner_patterns`. WARN at 1; FAIL when 5% or more of open deals. A sync that sets owners hides routing failures.
- O05 queue-aging. Salesforce unconverted leads owned by a queue for more than 2 days. WARN; hand off to lead-routing-and-speed-to-lead. NOT CHECKED without queue rows.

## L: lifecycle and stages

- L01 customer-without-evidence. Contacts and companies at a customer lifecycle value with no closed-won deal (directly, or through the associated company for contacts) and no `customer_evidence_field`. WARN per record; FAIL above 5% of customer-stage records. Copied forms with a Customer lifecycle setting are a common cause. NOT CHECKED without association columns: companies need company to deal links; contacts need contact to deal links, or contact to company plus company to deal links.
- L02 won-but-not-customer. Company with a closed-won deal at an earlier or blank lifecycle stage. WARN. Check the lifecycle automation for won deals. NOT CHECKED without company to deal association columns.
- L03 won-without-amount. Closed-won deal with blank or zero amount, unless its deal type is in `zero_amount_deal_types`. FAIL at 1. Amounts whose separators cannot be read are left out of the denominator and named in the detail; re-export plain numbers.
- L04 closed-with-future-date. Closed deal with a close date more than 1 day after as-of. FAIL at 1. Bookings land in the wrong period.
- L05 open-past-close. Open deal with a close date before as-of. WARN; FAIL when deals more than 30 days past exceed 10% of open deals.
- L06 open-without-activity. Open deal with last activity older than 60 days, or blank and created more than 60 days ago. WARN. Thresholds are per process.
- L07 stage-not-in-pipeline. Deal stage not in its pipeline's list in `rules.pipelines`. FAIL at 1. Orphaned stage values after pipeline edits break reports and integrations keyed on stage IDs.
- L08 lifecycle-blank. Contact created more than 1 day before as-of with no lifecycle. WARN.
- L09 follow-up-conflict. Lead status in an open working state while lifecycle is customer. WARN.
- L10 conversion-opt-out-drift. Salesforce converted lead with Email Opt Out or Do Not Call true whose converted contact has it false. FAIL at 1. Salesforce does not update those fields when a lead converts into an existing contact. NOT CHECKED without IsConverted and ConvertedContactId in the leads export.
- L11 lost-without-reason. Closed-lost deal without a lost reason when `lost_reason_required` is true. WARN.
- HubSpot: lifecycle moves only forward through imports, forms, the API, workflows and the Salesforce integration. An earlier stage needs the value cleared first (`clear_then_set`), and moving backwards changes the legacy "Became a" date properties.
- Salesforce has no lifecycle field: map a custom field with `--map contacts.lifecycle_stage=Header` or L01, L08 and L09 stay NOT CHECKED.

## A: associations

- A01 contact-without-company. B2B contact with a valid, non-personal, non-role email and no company. WARN. NOT CHECKED without contact to company association columns (A04 and A06 too): an export without them would otherwise show every contact as unassociated.
- A02 open-deal-without-company. WARN; FAIL when `deal_company_required` is true.
- A03 open-deal-without-contact. WARN. Salesforce needs an OpportunityContactRole export (`--contact-roles`), otherwise NOT CHECKED.
- A04 wrong-company-domain. Contact associated with companies, none holding its email domain, while another company in the export does. WARN: wrong association or a job change. Domains held by no company are not flagged.
- A05 personal-mail-company. Company whose domain is a personal provider. WARN. HubSpot's automatic company association does not match personal domains (it falls back to the contact's Website URL), so usually an import, integration, enrichment tool or manual entry wrote a personal domain; find the writer with Record source.
- A06 over-association. Deal with more than 3 companies, contact with more than 5. WARN.
- A07 dangling-reference. Association ID pointing to a record absent from the matching export. WARN per source object. Partial export, or a deleted or merged record still referenced. NOT CHECKED for an object whose export holds no association IDs.

## Confirming a FAIL in the CRM

- Trace one flagged record per FAIL line (at least 10 flagged in total) and 10 passing records.
- Open the record by ID (HubSpot record page or `get_crm_objects`; Salesforce record page or a SObject Reads query; Attio record page or `get-records-by-ids`) and compare the field with the export value. Request only the record ID and the fields under check where the tool lets you choose properties, compare name, email and phone values inside the tool result, and write only match or mismatch.
- For relationship checks (L01, L02, A01 to A04, A07), confirm the relationship itself: the won deal, the company association, the referenced record. A matching field value proves nothing here.
- For duplicates, open both records and compare keys, associations and activity history; the band is evidence, not a decision.
- For owners, check the user's status in Users and Teams (HubSpot), Setup > Users (Salesforce) or workspace members (Attio).
- Without a read-only connection, give the user the record IDs and fields to look up, and wait for the answers.
- Record each trace as record ID, check, field and match or mismatch. For name, email and phone fields record match or mismatch, never the values. Pass when at least 95% match (19 of 20, 23 of 24) and every mismatch is explained; a mismatch from the export, the mapping or a rule is fixed and rerun before the report goes out.