standardize-geo-values

Files (4)

• SKILL.md
• after.py · scripts/
• before.py · scripts/
• execute.py · scripts/

SKILL.md (first lines)

# Standardize Country and State/Region Values

Convert inconsistent geographic formats (e.g., "US", "USA", "U.S." vs "United States"; "NY" vs "New York") to a single standard format across all contact and company records.

## Why This Matters

Inconsistent geo values break geographic segmentation. A list filtering for "United States" will miss contacts labeled "US" or "USA". For B2B companies running region-specific campaigns or reporting by geography, this means inaccurate audience sizes and missed contacts.

## Prerequisites

- Phase 1 hygiene processes completed (invalid/deleted contacts removed first)
- A HubSpot service key (`HUBSPOT_ACCESS_TOKEN` in `.env`; an existing legacy private app token also works) with contact and company read/write scopes
- Python 3.10+ with [`uv`](https://github.com/astral-sh/uv)
- Access to Contacts and Companies views with bulk edit permissions
- **Key constraint:** If your CRM integrates with another system (e.g., Salesforce, marketing automation), agree on the standard format (full names vs. ISO codes) with that system's admin BEFORE standardizing. Mismatched formats between synced systems will cause ongoing data conflicts.
- Decision on standard format. This skill recommends:
  - Countries: Full names (e.g., "United States", "United Kingdom")
  - States (contact level): Full names (e.g., "New York", "California")
  - This matches HubSpot's default form behavior

## Interview: Gather Requirements

Before executing, collect the following information from the user:

**Q1: What format do you prefer for country values -- full names (United States) or ISO codes (US)?**
- Examples: Full names ("United States", "United Kingdom"), ISO 2-letter codes ("US", "GB"), ISO 3-letter codes ("USA", "GBR")
- Default: Full names (e.g., "United States", "United Kingdom") -- this matches HubSpot's default form behavior

**Q2: Do you have a Salesforce or other CRM integration that requires a specific format?**
- Examples: "Yes, Salesforce uses ISO 2-letter codes", "Yes, our ERP uses full country names", "No integrations to worry about"
- Default: No integration constraints -- use HubSpot's default full-name format

## Scripts

Script paths are relative to this skill's directory. Run them from your project directory so `.env`, `data/`, and `reports/` resolve there: prefix each path with wherever this skill is installed (`skills/standardize-geo-values/` in a clone of the repo).

| Stage | Script | Run with |
|-------|--------|----------|
| Before | [`scripts/before.py`](./scripts/before.py) | `uv run scripts/before.py` |
| Execute | [`scripts/execute.py`](./scripts/execute.py) | `uv run scripts/execute.py` |
| After | [`scripts/after.py`](./scripts/after.py) | `uv run scripts/after.py` |

`before.py` counts contacts and companies per variant value. `execute.py` applies the variant-to-standard mapping via batch update (review and extend its `COUNTRY_MAPPING`/`STATE_MAPPING` tables first). `after.py` verifies all variants return zero.

## Plan

1. Audit all non-standard country and state values (before state)
2. Build a mapping table of variants to standard values
3. Batch update via API script or manual bulk edit in HubSpot UI
4. Prevent future inconsistencies by configuring property types and forms
5. Verify all values are standardized (after state)

## Before

### API Audit Script

Run `uv run scripts/before.py`. The core query pattern:

```python
variants = ["US", "USA", "U.S.", "U.S.A.", "America"]
for object_type in ["contacts", "companies"]:
    for variant in variants:
        resp = requests.post(f"{BASE}/crm/v3/objects/{object_type}/search",
                             headers=HEADERS, json={
            "filterGroups": [{"filters": [
                {"propertyName": "country", "operator": "EQ", "value": variant},
            ]}],
            "limit": 1,
        })
        total = resp.json()["total"]
        if total:
            print(f"{object_type} with country = '{variant}': {total}")
```

### Manual Audit

1. Go to Contacts > filter by Country/Region > is any of > "US". Note count.
2. Repeat for "USA", "U.S.", and any other suspected variants.
3. Repeat at the company level.
4. For states: filter by State/Region > is any of > "NY", "CA", "TX" to check for abbreviation variants.

Record all variant counts as your baseline.

## Execute

### Method 1: API Batch Update (Recommended for Large Volumes)

This is scripted: `uv run scripts/execute.py`. The mapping tables it applies:

```python
COUNTRY_MAPPING = {
    "US": "United States",
    "USA": "United States",
    "U.S.": "United States",
    "U.S.A.": "United States",
    "America": "United States",
    "UK": "United Kingdom",
    "GB": "United Kingdom",
    "Great Britain": "United Kingdom",
    # Add other mappings as discovered in your audit
}

STATE_MAPPING = {
    "NY": "New York",
    "CA": "California",
    "TX": "Texas",
    "FL": "Florida",
    "IL": "Illinois",
    "PA": "Pennsylvania",
    "OH": "Ohio",
    "GA": "Georgia",
    "NC": "North Carolina",
    "NJ": "New Jersey",
    "VA": "Virginia",
    "WA": "Washington",
    "MA": "Massachusetts",
    "AZ": "Arizona",
    "CO": "Colorado",
    "MD": "Maryland",

From TomGranot/hubspot-admin-skills, MIT.

More from TomGranot/hubspot-admin-skills