Architecture Skill

LLM-agnostic productivity and task automation skill that instructs any AI model (Claude, GPT-4o, Gemini, Cursor) to work with Architecture — HubSpot Import Formatter. Use when wor…

Otherv1.0.0UniversalFreeAgent Skills spec
# Architecture — HubSpot Import Formatter

A lightweight, fully local skill. No servers, no API keys, no data leaves the machine.
Everything runs as Python over the files the rep already has.

## Component map

```
hubspot-import-formatter/
├── SKILL.md                 # Trigger + workflow instructions Claude reads
├── README.md                # Human guide for collaborators & end users
├── ARCHITECTURE.md          # This file
├── EXEC_OVERVIEW.md         # ELT / leadership one-pager
├── scripts/
│   ├── format_import.py        # The engine: map → normalize → dedup → validate → write
│   ├── hubspot_upload.py       # Optional: upsert to HubSpot + build a static list
│   ├── hubspot_props.py        # Shared template→internal map + enum validation (1 source)
│   ├── industry_resolver.py    # Semantic free-text industry → valid HubSpot value + NAICS
│   ├── refresh_hubspot_props.py# Refresh the live dropdown-option snapshot
│   └── kpi_report.py           # Reads the run ledger, prints adoption/volume KPIs
└── references/
    ├── templates.md            # Canonical template specs + alias/normalization rules
    ├── hubspot_upload.md       # Property map, dedupe strategy, token scopes
    ├── hubspot_lists.md        # Static vs active lists + filterBranch format
    ├── list_questionnaire.md   # Exact list-creation question flow + answer→flag mapping
    ├── hubspot_properties.json # Live snapshot: valid props + dropdown options
    ├── industry_mapping.md     # Semantic industry resolver docs
    └── industry_mapping.json   # 184 HubSpot industries → NAICS + synonyms (from Make.com)
```

`hubspot_props.py` is the single source of truth both the formatter and uploader import,
so they can never disagree on property names or valid values — that's what guarantees
no mis-mapping errors on import. `industry_resolver.py` layers semantic industry matching
on top of it.

Secrets live **outside** this folder at `~/.{{COMPANY_NAME}}/hubspot.env` (git-ignored) so a packaged
skill never ships a token.

## Data flow

```mermaid
flowchart TD
    A[Rep has a messy spreadsheet<br/>CSV / XLSX / multi-sheet] --> B[format_import.py]
    B --> C{Detect per sheet}
    C -->|person fields| D[Contacts pipeline]
    C -->|company identity| E[Companies pipeline]

    subgraph PIPE[For each object type]
      F[Map source cols → template fields<br/>exact-normalized, then substring]
      G[Normalize<br/>lowercase email · clean domain · derive domain from email]
      H[Dedup by unique ID<br/>Email / Company Domain Name]
      I[Validate required fields → Issues flag]
    end
    D --> PIPE
    E --> PIPE

    PIPE --> J[Exact-template sheet + CSV<br/>pristine, import-ready]
    PIPE --> K[Extended sheet<br/>+ leftover cols + Issues, flagged rows highlighted]
    PIPE --> L[Append run to KPI ledger<br/>~/.{{COMPANY_NAME}}/hubspot_import_ledger.csv]

    J --> M[HubSpot import wizard<br/>imported under Batch ID name]
    J --> U[hubspot_upload.py<br/>optional direct push]
    L --> N[kpi_report.py<br/>adoption & volume KPIs]
    M -.deal attribution via Batch ID.-> N
    U --> H1[Upsert: contacts by email,<br/>companies by domain]
    H1 --> H2[Create static list per type<br/>+ add members]
    H2 --> H3[Return list link]
```

### Upload path (`hubspot_upload.py`)

Optional. Reads the formatted workbook, sends only properties that exist in the portal,
validates enum/numeric fields, then writes via the dedupe-correct path per object:
**contacts upsert by email** (email is a HubSpot-unique property); **companies
search-then-update/create by domain** (domain usually isn't flagged unique, so direct
upsert is rejected). It then creates a static MANUAL list per type, adds the records, and
returns the link. Batches of 100 with per-record fallback so one bad row can't fail the
import. Token comes from `~/.{{COMPANY_NAME}}/hubspot.env`; uploads run as the token owner.

## Design decisions (and why)

| Decision | Choice | Why |
|---|---|---|
| Object detection | **Auto-detect & split** | One Sales Nav/Apollo export usually = people *and* their companies. Splitting produces both clean imports from one file, deduped independently. |
| Bad rows | **Keep but flag** | Reps must *see* what's missing, not have rows vanish. Flags live only on the Extended tab so the import tab/CSV stay pristine. |
| Owner field | **Map if present, else blank** | Never invent CRM ownership; let HubSpot assign on import if no source column exists. |
| Unique identifiers | Email (contacts) / Company Domain Name (companies) | Exactly what HubSpot dedupes on — the root cause of the duplicate problem this solves. |
| Logic location | **One bundled script, not ad-hoc per run** | Consistency is the entire point. A script guarantees every rep's output is byte-identical in structure. |

## The engine, in five stages (`format_import.py`)

1. **Load** — reads CSV or every sheet of an XLSX as strings (no silent type coercion).
2. **Detect** — per sheet, decides contacts / companies / both from which fields match.
3. **Map** — normalizes headers (lowercase, strip non-alphanumerics), matches against
   alias lists; exact match first, then substring; each source column used once.
4. **Normalize & dedup** — lowercases emails, strips URLs to bare domains, derives a
   company domain from a work email when no website column exists (free-mail ignored),
   drops duplicate unique IDs keeping the first.
5. **Validate & write** — flags rows missing required fields, writes the workbook
   (exact + extended tabs with template header styling) and the CSV(s), then appends one
   row to the KPI ledger.

## KPI layer

- **Local ledger** (`~/.{{COMPANY_NAME}}/hubspot_import_ledger.csv`, override via `{{COMPANY_PREFIX}}_IMPORT_LEDGER`):
  append-only, one row per run — batch ID, timestamp, operator, counts, flagged counts.
- **`kpi_report.py`** aggregates it: total imports, rows prepared, data-quality rate,
  breakdown by operator and month.
- **Deal attribution** is intentionally *not* computed here (the script can't see the
  CRM). Each run emits a **Batch ID** meant to be used as the HubSpot import name;
  HubSpot/the GTM agent then reports deals traced to those import batches.

## Dependencies

Python 3 with `pandas` and `openpyxl` (both already present in Patrick's environment).
`xlrd` is used only for legacy `.xls`. No network calls.

## Extending it

Templates or aliases change? Edit the constants at the top of `format_import.py`
(`CONTACT_TEMPLATE`, `COMPANY_TEMPLATE`, `*_REQUIRED`, `*_ALIASES`) and mirror the change
in `references/templates.md`. Adding a new object type = add a template/required/alias
set and a detection rule.