SuperAPI AI Directory

vertical-list-builder

General & other

View repository

Updated 10-16 08:45

About this skill

Vertical List Builder — v2.0.0

<v2_changes_read_first> v2.0.0 rebuilds this skill from a broken, never-installed v1.0.2 (it existed only as a zip with a single file; its 6 vertical reference files, 4 connector cookbooks, champion-detection doc, and 2 Python scripts were referenced but never written, and its MCP tool names belonged to a different environment). This version: (1) fixes every MCP tool name to what actually resolves in this environment, (2) adds geo/language/rep_scope/named_owner_scope — this skill had zero territory or rep-assignment concept before, only a single hardcoded "USA + Canada" default baked into the SQL, (3) adds Production/Broadcast, Corporate AV, and Healthcare as 3 new verticals (9 total, up from 6), and (4) actually authors the previously-missing reference files and scripts. See the Changelog at the bottom for the full list, and references/verticals/*.md for which of the 9 verticals carry real deal/documented evidence vs. which are hypothesis-only. </v2_changes_read_first>

<quick_start> Trigger: "Build vertical list for [vertical]" or "Build all vertical lists" (M-F 6:30 AM ET via scheduled task, per territory's own working hours — see the geo note below)

Inputs:

  • vertical — one or more of: higher-ed, community-colleges, live-events, courts-gov, houses-of-worship, k12, production-broadcast, corporate-av, healthcare.
  • volume — default 35 callable leads per vertical (range 25-50).
  • geo — one of usa / canada / usa-canada (default, unchanged from v1.x) / uk / latam-spanish. See references/geo-country-codes.md for the exact country sets and each geo's default language. This replaces the old hardcoded US/Canada-only country filter — a UK or LatAm run before v2.0.0 silently returned zero rows.
  • rep_scope (optional) — a seat key from references/seats.md (edgar, nyasha, vasil). Resolves to that seat's territories + language and pre-scopes the pool; does not replace the vertical-pool model for NA runs where no rep_scope is given.
  • named_owner_scope (optional) — a HubSpot owner id to INCLUDE-scope the whole pull to. For a UK run this resolves to Anthony Taroni's book, owner id 165812850 (resolved live via search_owners — see references/seats.md; he has no Nooks seat of his own, since Nyasha/Vasil work his book, not him). The architectural inverse of the AE-exclusion block below.
  • SDR owner_ids (optional) — if provided, prioritizes contacts already owned by these reps within the resolved pool; otherwise treats the pool as unassigned.

Outputs:

  • HubSpot static list per vertical+geo: Daily Call List - [Vertical] - [Geo] - YYYY-MM-DD
  • Google Sheet per vertical with ranked leads, ATL/BTL/Champion tags, language column (en/es), vertical-specific intel column
  • Outcome sidecar: ~/.claude/skill-analytics/last-outcome-vertical-list-builder.json

Dependencies (this environment's actual tool names — v1.x's names do not resolve here):

  • mcp__claude_ai_Epiphan_Ai__execute_sql / ask_agent — HubSpot warehouse SQL + Clari calls
  • mcp__claude_ai_Epiphan_Ai__hubspot_search_contacts / hubspot_search_companies / hubspot_get_contact / hubspot_get_company — entity lookups and live-write verification
  • mcp__claude_ai_Apollo_io__apollo_mixed_companies_search / apollo_mixed_people_api_search — greenfield prospecting + enrichment
  • mcp__claude_ai_Clay__find-and-enrich-contacts-at-company / find-and-enrich-list-of-contacts / get-task-context — phone/email waterfall (async — poll, don't assume a null means "not found")
  • mcp__claude_ai_HubSpot__manage_crm_objects — the ONLY tool that can set a company's owner after creation (no equivalent exists in the Epiphan AI MCP); requires its own mandatory confirmation step before writing, per its own contract — never call it silently.
  • Google Drive MCP — for Sheets export
  • Context7 — on-demand docs for any of the above when an API parameter shape is unclear </quick_start>

<house_rules> Customer-facing skill. Before output, run the gates in order (details: ~/.claude/skills/epiphan-house-rules-skill/reference/gates.md): S: search_product_catalog (Epiphan AI) or Epiphan Knowledge search before any product claim. B: check_my_copy on every customer-facing string. Q: qualify_lead right after the candidate pull, before tiering/enrichment/drafts. D: outbound text becomes a Gmail draft, never a send. Identifiers (owner IDs, portal, emails, seats) come from reference/identifiers.md in that skill — do not restate them here. On any gate failure follow reference/self-healing.md (retry, degrade with a flag, alert Tim, halt) and append a line to ~/.claude/skill-runs/.jsonl. </house_rules>

<golden_rules> Inherited from bdr-ops/CLAUDE.md — these are non-negotiable gates. Apply BEFORE classification:

  1. Customer Exclusion — drop hubspot_contacts.lifecyclestage resolving to "Customer"

  2. Channel Partner Check — drop reseller-employee contacts (proAV, B&H, CDW, JB&A, Howard, DGI, Extron-Direct)

  3. AE Exclusion with Reactivation Exception (CRITICAL) — by default, exclude contacts where hubspot_owner_id is one of:

    • 82625923 — Lex Evans
    • 190030668 — Phillip Sandler

    These are AEs Tim feeds demos to. SDR outbound to their actively-owned book causes double-touch and burns the relationship.

    Reactivation Exception — an AE-owned contact CAN be re-added to a vertical list if BOTH conditions are true:

    • No email contact in the last 90 days (notes_last_contacted IS NULL OR notes_last_contacted < NOW() - INTERVAL '90 days')
    • No associated open deal (NOT EXISTS in hubspot_deal_contact joined to hubspot_deals where the deal is in any stage other than 'closedwon' or 'closedlost')

    Greenfield Apollo dedupe must also apply this — if a greenfield contact's email matches an AE-owned HubSpot contact, run the same 90-day + no-deal test before deciding whether to drop or include.

  4. ATL-First — when target volume is constrained, fill ATL slots before BTL slots

  5. Geo Filter — respect the geo argument (default usa-canada, unchanged) — resolved via references/geo-country-codes.md and scripts/build_vertical_query.py, never a hardcoded literal in this file or a query again.

  6. Suppression — drop opted-out, hard-bounced, bad-address, and contacts touched within 2 days

  7. Active Sequence Exclusion — drop contacts currently in an Apollo sequence (prospecting_enrollments.status IN ('active','sending'))

  8. Named-owner scope is an INCLUSION filter, never an exclusion one (new in v2.0.0) — if named_owner_scope is set, the pool is filtered TO that owner's book, on top of (not instead of) rules 1-7. See references/seats.md. </golden_rules>

<ae_exclusion_owners> Maintain this list as the single source of truth. Update if AE roster changes.

Owner ID Name Role Email
82625923 Lex Evans AE lexevans@epiphan.com
190030668 Phillip Sandler AE psandler@epiphan.com

The full injected SQL clause (unchanged from v1.x) lives in scripts/build_vertical_query.py — do not hand-retype it inline here or in a query; call that script and use its output.

When a contact is reactivated under this rule, tag it in the Google Sheet with Tier = REACTIVATED (overrides the normal ATL/BTL/Champion bucket) so SDRs handle the conversation with extra care. </ae_exclusion_owners>

Stage 1: Resolve Vertical + Territory Targets

Read the target vertical(s), geo, and optional rep_scope/named_owner_scope from the prompt.

Vertical Argument Reference File Evidence
higher-ed references/verticals/higher-ed.md Loss-derived (real)
community-colleges references/verticals/community-colleges.md Documented (real ladder, no independent conversion study)
live-events references/verticals/live-events.md Deal-derived (real, measured conversion)
courts-gov references/verticals/courts-gov.md Hypothesis (lane proven, titles not individually ranked)
houses-of-worship references/verticals/houses-of-worship.md Hypothesis, zero deal evidence — the least validated of the 9
k12 references/verticals/k12.md Deal-derived but thin (5 won / 3 lost titled)
production-broadcast references/verticals/production-broadcast.md Documented (real ladder) — new in v2.0.0
corporate-av references/verticals/corporate-av.md Documented (real ladder) — new in v2.0.0
healthcare references/verticals/healthcare.md Deal-derived, real sample (56 won / 18 lost) — new in v2.0.0

If "all" or no specific vertical is named, run all 9 in parallel.

If rep_scope is given, resolve it against references/seats.md FIRST — it determines the effective geo (and, for Edgar, potentially more than one) and the default language before Stage 2 runs. If named_owner_scope is given but no real owner id has been supplied yet (Anthony Taroni's case as of this writing), treat it as a no-op and say so in the run's output rather than silently filtering to nothing.


Stage 2: HubSpot-Owned Pull (Primary Source)

For each vertical+geo combination, get the query from scripts/build_vertical_query.py rather than hand-writing SQL inline:

python3 scripts/build_vertical_query.py <vertical> --geo <geo> [--named-owner-id <id>]

Run the printed SQL via mcp__claude_ai_Epiphan_Ai__execute_sql. Keep any manual follow-up query under ~1500 chars to stay clear of the tool's char limit — split into CTEs if needed.

Query B: Champion Signals — see references/champion-detection.md for the full 3-signal logic (job-changer alumni / high HubSpot engagement / past Clari call participant). These signals are territory-agnostic — they key off Epiphan's own global CRM history, not a geo-specific table.

Champions are the highest-priority slots in the daily list — top of the dial queue regardless of ATL/BTL.


Stage 3: ATL / BTL Classification (Vertical- AND Language-Specific)

Use scripts/classify_vertical.py:

python3 scripts/classify_vertical.py <vertical> "<title>" [geo]

It runs the vertical's English rubric always, and its Spanish rubric too only when geo == "latam-spanish" — running a Spanish rubric against a purely English-market title is unsafe (a bare Spanish executive pattern can accidentally match an English acronym like "CEO" and override a deliberate English-ladder tier; found and fixed during this rebuild — see the script's own docstring). Not every vertical has a ported Spanish rubric yet (Houses of Worship, Community Colleges, Production/Broadcast, Healthcare are EN-only for now) — that is not a bug.

Classification order (unchanged from v1.x):

  1. Champion (any of the 3 signals) → tier = CHAMPION, regardless of title
  2. Reseller employee (company_is_reseller + senior title) → CHANNEL (excluded from list)
  3. Vertical-specific ATL (from reference file) → ATL (tier 5, TIER_ECONOMIC_BUYER)
  4. Vertical-specific BTL (from reference file) → BTL (tier 4, TIER_CHAMPION)
  5. Technical validator → tier 3, pair with a champion, never carry alone
  6. NEVER patterns (see each vertical's reference file) → drop entirely — reject explicitly, never let a hard-reject title fall through as a generic "no match"
  7. Else → TIER_OPENER/TIER_LOW_YIELD (keep, don't drop — a BDR can disqualify later)

Set the language column from classify_vertical.py's winning-language note ([en]/[es]), falling back to the geo's default language (references/geo-country-codes.md) if the title was blank/unclassifiable. This is what flags a Spanish lead for Edgar's Spanish-language call scripts and Clay enrichment prompts, per this rebuild's original ask.


Stage 4: Greenfield Fill (Apollo) — IF HubSpot bucket short

If post-classification HubSpot universe yields fewer than the target ATL count, fill the gap from Apollo. Use mcp__claude_ai_Apollo_io__apollo_mixed_companies_search and apollo_mixed_people_api_search. Per-vertical filters live in each references/verticals/*.md's own "Apollo search filters" section; organization_locations is resolved from the run's geo, not hardcoded. See references/connectors/apollo-cookbook.md for the greenfield-dedupe procedure (against both the AE-exclusion set and, if set, named_owner_scope) and the Spanish-title-search caveat (search Apollo on accent-safe stems, not full accented phrases — classify locally after).

If unsure about Apollo filter syntax, query Context7:

Use mcp__Context7__resolve-library-id with query "apollo.io api"
Then mcp__Context7__get-library-docs to pull the relevant section

Stage 5: Phone/Email Enrichment (Clay Waterfall)

For any greenfield contact lacking a phone, run the Clay waterfall per references/connectors/clay-cookbook.md — enrichment is async, poll get-task-context with the specific entityIds you selected, don't assume a null means unresolved forever (clay_negative _cache is a real final answer, don't re-spend on it). Drop contacts that come back phone-less after Clay — not callable today, queue for re-enrichment tomorrow.


Stage 6: Rank + Slot

Within each vertical+geo bucket, rank contacts by:

  1. Champions (any signal) — top of list
  2. ATL with verified phone — by company size descending, then by lastmodifieddate (freshest)
  3. ATL without phone (post-Clay) — drop to bottom unless bucket is short
  4. BTL with verified phone — by company size, then by recency
  5. Anything else — drop

Slot to target volume (default 35, range 25-50). Mix should land near 40% ATL / 40% BTL / 20% Champion. A Champion count < 5 is normal — don't force-promote BTLs to fill that slot.


Stage 7: Output

Output 1: HubSpot Static List Daily Call List - [Vertical] - [Geo] - YYYY-MM-DD (geo added to the name in v2.0.0 so an Edgar LatAm list and an NA list for the same vertical never collide). Add selected contact IDs via mcp__claude_ai_HubSpot__manage_crm_objects if list management is exposed there; otherwise document the contact IDs for a one-time manual list creation.

Output 2: Google Sheet

Tier | Champion Signal | First | Last | Title | Company | Phone | Email |
HubSpot URL | Vertical Intel | Last Touch | Days in System | Owner | Language | Geo

Language and Geo are new columns in v2.0.0. The "Vertical Intel" column pulls a one-sentence "why now" hook from the vertical's reference file — several verticals (Houses of Worship, Production/Broadcast, Healthcare) have NO validated hook yet; use their generic placeholder rather than inventing a seasonal one.

Output 3: Sidecar Write ~/.claude/skill-analytics/last-outcome-vertical-list-builder.json:

{
  "ts": "ISO8601",
  "skill": "vertical-list-builder",
  "version": "2.0.0",
  "verticals_run": ["higher-ed", "k12"],
  "geo": "usa-canada",
  "rep_scope": null,
  "metrics": {
    "higher-ed": {"target": 35, "delivered": 35, "atl": 14, "btl": 15, "champion": 6,
                  "greenfield_fill": 8, "language_mix": {"en": 35, "es": 0}},
    "k12": {"target": 35, "delivered": 32, "atl": 12, "btl": 18, "champion": 2,
            "greenfield_fill": 12, "language_mix": {"en": 32, "es": 0}}
  },
  "alerts": [],
  "runtime_ms": 0,
  "session_id": "YYYY-MM-DD"
}

language_mix is new in v2.0.0 — a latam-spanish run that comes back entirely en is itself an alert-worthy signal (something upstream may be translating titles, or the Spanish rubric isn't firing), same discipline mexico_personas.language_mix() uses in the sibling repo.


Stage 8: Alerts

Alert Threshold Level
Vertical bucket under target delivered < 25 ⚠️ Warning
ATL share below 30% atl/delivered < 0.30 ⚠️ Warning
Greenfield fill > 50% greenfield/delivered > 0.50 🔍 Review (HubSpot book is depleting)
Clay enrichment failure rate > 30% unverified/attempted > 0.30 🚨 Critical (waterfall broken)
Champion count = 0 across all verticals sum(champion) = 0 🔍 Review (signal logic might be misfiring)
A latam-spanish run's language_mix is 100% en new in v2.0.0 🔍 Review — Spanish rubric may not be firing, or titles are pre-translated upstream
named_owner_scope was requested but no real owner id exists yet new in v2.0.0 ⚠️ Warning — ran unscoped, say so explicitly, don't silently return the full pool as if it were scoped

<success_criteria>

  • Every requested vertical/territory bucket returns 25-50 callable contacts (phone verified via Apollo → Clay waterfall), or a zero-with-reason line naming the empty stage
  • Every contact carries an ATL / BTL / Champion tier derived from the vertical's own decision-maker patterns in the title's language, never generic seniority
  • Golden Rules applied: no customers (device_count >= 1), no channel partners, no AE-owned contacts with activity < 90 days, no suppressed leads
  • Output delivered as a HubSpot static list AND a Google Sheet tagged with Vertical, Territory, Language, Tier columns
  • Seat scope resolved from references/seats.md at run time, never hardcoded rep names
  • Outcome sidecar written to ~/.claude/skill-analytics/last-outcome-vertical-list-builder.json </success_criteria>

Working with Connectors

This skill chains 4 different MCPs. When you hit a parameter or response shape you don't recognize, always probe Context7 before guessing — misshaping an Apollo or Clay call burns credits and silently produces wrong leads.

For deeper guidance on each connector, read:

  • references/connectors/hubspot-warehouse.md — warehouse schema, common joins, and the real traps found building this rebuild (LEFT JOIN + COUNT(*), subdomain matches, owner-write gaps)
  • references/connectors/apollo-cookbook.md — search filters, greenfield dedupe, Spanish-title search caveats
  • references/connectors/clay-cookbook.md — async enrichment states, wrong-person risk
  • references/champion-detection.md — the 3-signal logic, territory-agnostic by design
  • references/geo-country-codes.md — the geo → country/language lookup every other file reads
  • references/seats.md — rep → territory → language, and the Anthony-Taroni-id gap

Failure Modes + Recovery

Failure Recovery
execute_sql query too long Split into 2-3 sub-queries chained via CTE
execute_sql timeout Retry simpler; if still timing out, drop the join causing the most rows
Apollo returns 0 results for greenfield Loosen person_seniorities first, then organization_num_employees_ranges
Clay returns 0 phones Verify the input contact has a verified email; Clay needs an anchor
HubSpot list creation fails Fall back to writing contact IDs to the Sheet's "HubSpot Bulk Add" tab
Vertical universe < 50 raw contacts Don't fail — emit warning, run greenfield fill more aggressively
A geo run returns 0 rows Check references/geo-country-codes.md's country set actually matches this run's geo — a silent 0 was v1.x's core bug; never assume 0 means "no real accounts there" without checking the filter first
named_owner_scope set but resolves to 0 rows Confirm the owner id is real and active (not a departed rep) before concluding the book is empty

Changelog

v2.0.0 (2026-09-12) — Territory/language rebuild, 9 verticals, actually installed

  • Extracted the never-installed v1.0.2 zip to ~/.claude/skills/vertical-list-builder/ — it previously existed nowhere runnable.
  • Fixed every MCP tool name from a different environment's connector-hash naming to this environment's actual names.
  • Added geo (usa/canada/usa-canada/uk/latam-spanish) — replaces the hardcoded country IN ('United States','Canada','US','CA') SQL literal, which silently returned zero rows for any non-US/Canada run. New references/geo-country-codes.md is the single lookup.
  • Added language (en/es) to the contact schema, Sheet, and JSON sidecar — did not exist anywhere in v1.x. Derived from geo, with per-contact override.
  • Added rep_scope (new references/seats.md, mirroring the sibling repo's config/sdr_seats.json pattern) — represents Edgar's USA+Canada+LatAm scope and Nyasha's Canada+UK dual-territory scope. v1.x had zero rep→territory concept at all.
  • Added named_owner_scope — an inclusion filter (architectural inverse of the existing AE-exclusion block) for a named account owner's book (Anthony Taroni's UK case). Resolved live via search_owners same day: owner id 165812850, no Nooks seat of his own.
  • Added 3 verticals: Production/Broadcast, Corporate AV, Healthcare (9 total, up from 6) — ported from the sibling epiphan-lead-harvester repo's persona_ladder.py, which already had real ladders for all three that this skill simply never used.
  • Authored all previously-missing dependencies: 9 references/verticals/*.md, 3 references/connectors/*.md, references/champion-detection.md, references/geo-country- codes.md, references/seats.md, scripts/classify_vertical.py, scripts/ build_vertical_query.py.
  • Found and fixed two real bugs while spot-checking the new scripts (per this rebuild's own verification step): (1) running a Spanish rubric unconditionally let a bare CEO acronym match override an English ladder's deliberate founder-trap demotion — fixed by scoping Spanish rubric consultation to latam-spanish runs only; (2) the Corporate AV ladder (ported from persona_ladder.py, a bug present in the SOURCE too, not introduced here) never matched "audiovisual" spelled out, only bare "av" — fixed here; not yet fixed in the sibling repo's own persona_ladder.py, flag that back if this skill's fix should be ported upstream too.

v1.0.2 (2026-04-29) — AE reactivation exception (unchanged, see prior history below)

  • Softened "AE Hard-Exclude" to "AE Exclusion with Reactivation Exception"
  • AE-owned contacts can re-enter vertical lists if BOTH: notes_last_contacted is NULL or > 90 days old, AND no open deal exists in hubspot_deal_contact
  • Reactivated contacts get Tier = REACTIVATED tag in output (overrides ATL/BTL/Champion)

v1.0.1 (2026-04-29) — AE hard-exclude

  • Promoted "AE-owned <90d" Golden Rule to "AE Hard-Exclude"
  • Added explicit <ae_exclusion_owners> block as single source of truth for AE roster

v1.0.0 (2026-04-29) — Initial release (never actually installed until v2.0.0)

  • 6 verticals: Higher Ed, Community Colleges, Live Events, Courts/Gov, Houses of Worship, K-12
  • Vertical-first architecture (SDR-agnostic) — list belongs to vertical, any SDR can work it
  • Champion detection via 3 signals (job-changer, engagement, Clari participant)
  • HubSpot-first sourcing → Apollo greenfield fill → Clay phone enrichment
  • Default geo USA + Canada, hardcoded
  • Outputs HubSpot static list + Google Sheet per vertical
  • Inherits Golden Rules from bdr-ops/CLAUDE.md
  • Hard target: 25-50 callable per vertical per day, ~40/40/20 ATL/BTL/Champion mix

Roadmap (v2.1+ candidates)

  • Supply Anthony Taroni's real HubSpot owner id — resolved 2026-09-12: 165812850, no Nooks seat. named_owner_scope can filter for real now.
  • Supply Nyasha Chigwedere's real HubSpot owner id — resolved 2026-09-12: 94135434, Nooks user LGCdlTv6v8dSAiSsoJ7ETWQHVKl1.
  • Resolve whether Nyasha's Canada scope is a handoff from Edgar or an addition (see references/seats.md's open question).
  • Port this rebuild's corporate-av word-order fix (bare "av" missing "audiovisual") back upstream into the sibling repo's persona_ladder.py.
  • Build a Spanish rubric for Houses of Worship, Community Colleges, Production/Broadcast, and Healthcare if a LatAm run ever needs one of those verticals — currently EN-only.
  • Auto-suppress contacts that didn't connect after 5 dial attempts (cold list rotation)
  • Vertical-specific email templates auto-attached to Sheet (one-touch SDR launch)
  • Slack daily digest posted to #sdr-team channel with bucket-level metrics
  • Win-loss-aware re-targeting (drop verticals/sub-segments where win rate < 5%)