# Reflect 3 Attempt 03

> HarborCRM CRM-Marketing Task Solver

- Skill: `prism-shadow/reflect-3-attempt-03-9` (Agent Skill)
- Install (CLI): `npx skillmds@latest add prism-shadow/reflect-3-attempt-03-9`
- Raw SKILL.md: https://api.skillmd.com/api/skills/prism-shadow/reflect-3-attempt-03-9/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Marketing & Growth
- Author: prism-shadow (https://skillmd.com/u/prism-shadow)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/prism-shadow/reflect-3-attempt-03-9

---

# HarborCRM CRM-Marketing Task Solver

Transferable skill for solving HarborCRM post-event / prospecting / import-batch tasks.
Learned by iterating train tasks against the train-only judge. Generalized — no hardcoded
account names, ids, or numeric answers.

## 0. Environment

- Base API URL: supplied by the runner (e.g. `<remote-env-url>`). All data is read-only GET.
- Authoritative rule constants live at `GET /api/policies`:
  - `prospecting.platform_enums` = `["AUV","ROV","Underwater Camera"]`
  - `sponsor_handoff.status_enums` = `["paid_deferred","open_invoice","proposal_only","not_sponsor"]`
- Every task returns ONE JSON object only (no prose outside JSON), matching the provided `answer_template.json`.
- Re-read the answer template for EACH task: field names, enums, and ordering rules differ per task
  even within the same family. Do not assume a field from one task exists in another.

## 1. Data model (endpoints you will need)

- Events: `/api/events/{event_id}`, `/orders`, `/badges`, `/sponsor_packages`
- Finance: `/api/finance/invoices?event_id={event_id}`
- CRM: `/api/crm/accounts`, `/api/crm/contacts`, `/api/crm/opportunities`, `/api/crm/campaign_members?event_id={event_id}`
- Tradeshows: `/api/tradeshows`, `/api/tradeshows/{show_id}/exhibitors`, `/api/tradeshows/{show_id}/meeting_interest`
- Import batches: `/api/import_batches`, `/api/import_batches/{batch_id}/raw_contacts`, `/api/import_batches/{batch_id}/suppression`

Key cross-references: accounts have `account_id`, `domain`, `status` (customer/prospect/disqualified),
`disqualified_reason`. Contacts have `contact_id`, `account_id`, `email`, `opted_out`. Matching between
badge/import rows and CRM is by **email domain** for accounts and **exact email/name** for contacts.

## 2. Step-by-step solver workflow

### Step A — Classify the task family
Read the prompt + template top-level keys and decide:
- **Prospecting** (tradeshow exhibitors → qualified leads): keys like `qualified_exhibitors` / `ranked_leads`, `excluded_near_misses`, `aggregate_counts`. → workflow B.
- **Sponsor + lead handoff / reconciliation** (event badges + sponsor orders + finance): keys like `sponsor_statuses`, `qualified_lead_accounts` / `badge_decisions`, `follow_up`, `crm_action_counts` / `exclusion_counts`. → workflow C.
- **Import batch cleaning**: keys like `clean_contacts`, `duplicate_summary`, `removal_summary`, `import_action_totals`. → workflow D.

### Step B — Prospecting workflow
1. Fetch exhibitors + meeting_interest + accounts + policies.
2. For each exhibitor, decide **qualified vs excluded** using the description + company context:
   - QUALIFIED = the exhibitor **manufactures / OEM-builds** at least one target platform
     (`AUV`, `ROV`, `Underwater Camera`). "Builds", "manufactures", "OEM ... manufacturer" = builds.
   - EXCLUDED = only adjacent: distributor/reseller ("distributor_only"),
     service/consulting/operates-rented ("service_only"), sensor-only vendor ("sensor_vendor_only"/"sensor_only"),
     research/academic ("research_only"), or otherwise not a platform builder ("not_target_market" where allowed).
   - Map the company's platforms from the description (e.g. "AUVs and ROVs" → `[AUV, ROV]`;
     "underwater camera modules" → `[Underwater Camera]`). Sort platforms in enum order AUV, ROV, Underwater Camera.
3. Determine `priority_tier` from meeting_interest:
   - `A` = requested_demo AND interest_score ≥ 90
   - `B` = requested_demo AND interest_score ≥ 80
   - `C` = all other qualified leads
   (Apply the explicit rule in the prompt when present; the same thresholds are the implied default.)
4. Opportunity sizing by tier when the prompt gives amounts: A/B/C map to the stated USD figures
   (e.g. 120000 / 90000 / 50000). Use them verbatim.
5. CRM action per qualified lead: `create_account` if no `crm_account_id`, else `update_existing`.
   `crm_account_id` = the exhibitor's `crm_account_id` field (null when absent).
6. Ranking (when template wants `ranked_leads`): demo-requested first, then interest_score descending,
   then broader platform coverage (more platforms first), then company_name ascending. `rank` is 1-based contiguous.
7. Aggregates: `qualified_total`, `excluded_*_total`, `platform_counts` (count of qualified leads covering each platform),
   `priority_counts`, `existing_crm_overlap_count` + `existing_crm_overlap_account_ids` (qualified leads that have a CRM account, IDs ascending),
   `total_estimated_opportunity_usd` (sum of tier amounts).
8. Sort outputs exactly as the template states (usually company_name ascending, or rank ascending).

### Step C — Sponsor + lead handoff / reconciliation workflow
1. Fetch event, orders, badges, invoices, accounts, contacts, opportunities, campaign_members, policies.
2. **Sponsor statuses** (one row per ACTIVE sponsor account, sorted by account_name asc):
   - Match each sponsor order to its finance invoice by `account_id`.
   - `paid_deferred` = invoice `status` paid_deferred (paid_amount == amount, deferred).
   - `open_invoice` = invoice `status` open (paid_amount < amount).
   - `proposal_only` = order `order_status` proposal_sent AND no invoice.
   - Exclude **canceled** sponsor orders from sponsor_statuses (they go to excluded_records as `inactive_sponsor_record`).
   - `amount_usd` / `package_amount` = the order `amount`. `paid_amount` and `open_balance` (= amount − paid_amount) from the invoice.
   - `not_sponsor` enum value is reserved for non-sponsor accounts only when the template explicitly lists it and context requires it; do not invent it otherwise.
3. **Sponsor revenue totals** = sum of the **package/contract `amount`** per status bucket (NOT paid cash, NOT open balance):
   - `paid_deferred` = Σ package amounts of paid_deferred sponsors
   - `open_invoice` = Σ package amounts of open_invoice sponsors
   - `proposal_only` = Σ package amounts of proposal_only sponsors
   - `open_invoice_balance` = Σ open balances of open_invoice sponsors (reported separately)
4. **Follow-up dates**: `lead_due_date`/`lead_followup_due_date` = event `end_date` + `followup_days_after_end`.
   `sponsor_finance_due_date`/`sponsor_followup_due_date` = `end_date` + `sponsor_followup_days_after_end`.
5. **Sponsor finance follow-up targets** = sponsors that are **not fully paid** = `open_invoice` + `proposal_only`
   (do NOT include `paid_deferred`). `task_count` = number of those accounts; `unpaid_sponsor_total_usd` = Σ their package amounts.
6. **Qualified non-sponsor leads** come from event badges:
   - Include attendee-type badges whose company is NOT a sponsor (any order_status) and whose CRM account is not disqualified.
   - Use the event's `lead_opportunity_amount` per qualified account; `lead_pipeline_total` = count × that amount.
   - CRM account action: `update_existing` if a CRM account matches the badge email domain, else `create_account`.
   - CRM **contact** action: `create_contact` if that specific person is not already a CRM contact (even when the account exists),
     else `update_existing`. Match contacts by exact email/name, NOT by account.
   - `campaign_member_action` for qualified leads = `add_campaign_member` (they are new members).
7. **Excluded records** (badges filtered out), with controlled reasons:
   - `sponsor_attendee` — badge_type sponsor OR the badge company has a sponsor order (incl. proposal/canceled).
   - `non_business_badge` — student, press, or other non-business badge types.
   - `existing_disqualified` — the badge company's CRM account `status` is disqualified.
   - `inactive_sponsor_record` — a canceled sponsor record / its ticket contact.
   - When multiple reasons could apply to one record, prefer the most sponsor-specific one first
     (sponsor_attendee / inactive_sponsor_record) before existing_disqualified; a canceled sponsor's contact is `inactive_sponsor_record`.
8. **exclusion_counts** tally each exclusion reason across badges.
9. **crm_action_counts**: accounts_create/update and contacts_create/update follow the per-lead actions above;
   `campaign_members_create` = number of qualified leads; `campaign_members_update` = **0** (do not count excluded
   or disqualified existing members as updates).
10. For the richer reconciliation template (`badge_decisions`, `campaign_member_actions`, `opportunity_summary`,
    `badge_only_contacts`):
    - `badge_decisions`: one per badge, sorted by badge_id asc. `classification` ∈ {sponsor_attendee, qualified_non_sponsor_lead, excluded}.
      `crm_action` ∈ {create_account_contact_campaign_member (new account+contact+member), create_contact_campaign_member (existing account, new contact+member),
      add_campaign_member (existing contact), update_campaign_member (existing member reconciled), no_action, no_import}.
      `exclusion_reason` null when classification is qualified.
    - A badge with email="" but a phone present is still a valid qualified lead (badge_only_contact allows empty email).
    - `badge_only_contacts` = qualified leads with NO matching CRM account (normalized email lowercase-trimmed, phone digits-only; empty string allowed for missing email/phone).
    - Proposal-only sponsor attendees are EXCLUDED (sponsor_attendee) and are NOT created as campaign members (no_import).
    - `opportunity_summary.lead_opportunity_amount_usd` = the event lead opportunity amount applied to qualified non-sponsor accounts
      (report as the total pipeline = count × per-account amount); `open_opportunity_total_usd`/`open_opportunity_count` = existing OPEN CRM opportunities for the qualified accounts (usually 0 when they have no CRM account).
    - `campaign_member_actions`: existing sponsor members are reconciled (no_action when status already matches attendance; update otherwise);
      new qualified leads → create with target_status `attended`; excluded → no_import with target_status `excluded`. Sort by subject_key ascending.

### Step D — Import batch cleaning workflow
1. Fetch raw_contacts, suppression, accounts, contacts, policies; identify `batch_id` and `campaign_code` from `/api/import_batches`.
2. **Normalize** each raw row: email = lowercase + trim (whitespace stripped); phone = digits only (strip `+ - ( ) . spaces`, keep leading `1`).
   A row with empty email but a phone is still usable. A row with BOTH empty email and empty phone = `missing_contact`.
3. **Deduplicate** by normalized email (the duplicate key = normalized email):
   - Group rows sharing the same normalized email.
   - **Winner = the most recent `captured_at`**; on a timestamp tie, partner-style sources outrank self-serve forms
     (source priority roughly: badge_scan, sponsor_form, partner_upload, webinar_form, exhibitor_form, manual_upload — earlier enum = higher priority).
     (Do NOT use first-seen/earliest — that is wrong.)
   - The winner becomes a clean contact (its `clean_contact_id` = `source_row_id` = winning row_id; `captured_at` from the winning row;
     `company_name` = the winning row's raw company name). Losers are removed with reason `duplicate`.
   - `duplicate_summary.duplicate_removed_count` = number of loser rows; one `duplicate_keys` entry per group (key, winner_row_id, removed_row_ids[]), sorted by key ascending.
4. **Suppress**: any row whose normalized email OR normalized phone appears in the suppression list → removed with reason `suppressed`.
5. **removal_summary**: `unusable_removed_count` = count of `missing_contact` rows ONLY (do NOT add duplicates here);
   `suppressed_removed_count` = count of suppressed rows; `removed_rows` lists ALL removed rows (duplicates + missing + suppressed),
   sorted by row_id ascending, each with its reason ∈ {duplicate, missing_contact, suppressed}.
6. **Clean contacts** (survivors), sorted by clean_contact_id ascending:
   - `existing_account_id` = CRM account matched by email domain (else null). `existing_contact_id` = CRM contact matched by exact email (else null; a different person on the same account is still null).
   - `crm_action`: `update_existing` if existing_account_id set, else `create_account`. (no_import/suppress apply only to removed rows.)
7. **import_action_totals**: tally the `crm_action` of the surviving clean contacts
   (`create_account` / `update_existing`). `no_import` and `suppress` categories are 0 unless the template clearly wants removed-row dispositions counted.
8. **campaign_member_import_count** = number of surviving clean contacts (all of them become members of the batch campaign).

## 3. Universal normalization & formatting rules
- Email: lowercase, strip surrounding whitespace. ` " Dana.Ruiz@Foo.example " ` → `dana.ruiz@foo.example`.
- Phone: keep digits only (drop `+`, spaces, dashes, dots, parens). `+1 (415) 555-0188` → `14155550188`. Empty string when none.
- All money fields: integer USD.
- Dates: `YYYY-MM-DD`. Date arithmetic = event `end_date` + the policy day counts.
- Sorting: always ascending on the field the template names; tie-break by the next named field.
- Output: exactly one JSON object, only the keys the template declares, no extra fields, no prose.

## 4. Pitfalls learned from judge feedback (concrete corrections)

These are mistakes that the judge penalized — avoid them. Each was confirmed by a score drop when the wrong version was submitted.

1. **Dedup winner is MOST RECENT, not first-seen.** Submitting the earliest row as the winner collapsed
   the import-batch score from 0.71 to 0.21. Use latest `captured_at`; break ties by source priority (partner_upload over webinar_form).

2. **`unusable_removed_count` = missing-contact rows only.** Adding duplicates into "unusable" dropped the
   import-batch score from 0.71 to 0.57. Duplicates have their own `duplicate_summary`; only `missing_contact` rows count as unusable.

3. **Contact action is per-person, not per-account.** For a lead on an EXISTING account whose contact person
   is NEW, use `create_contact` (not `update_existing`). Mirroring the account action onto the contact dropped the
   sponsor-handoff score from 0.87 to 0.60. Match contacts by exact email/name; only reuse `update_existing` when that specific person already exists.

4. **Do not update campaign members for excluded/disqualified records.** Setting `campaign_members_update=1`
   for a disqualified existing member dropped the sponsor-handoff score from 0.87 to 0.80. Excluded records take no campaign-member action;
   `campaign_members_update` stays 0 when all eligible members are new.

5. **Sponsor finance follow-up includes `proposal_only`, not just `open_invoice`.** Narrowing finance follow-up
   to open-invoice-only (excluding proposal-only sponsors) crashed the reconciliation score from 0.54 to 0.38. The finance/unpaid
   set = every sponsor that has not fully paid = `open_invoice` + `proposal_only`. `unpaid_sponsor_total_usd` = Σ their package amounts.

6. **Proposal-only sponsor attendees are NOT created as campaign members.** Marking a proposal-only sponsor's
   badge-scanning contact as a created campaign member (attended_sponsor) crashed the reconciliation score from 0.54 to 0.38.
   Only confirmed sponsors' attendees are campaign members; proposal-only attendees stay `no_import` / excluded.

7. **`sponsor_revenue_totals` uses package/contract amounts, not paid cash.** Reporting `open_invoice` as the
   paid_amount (10000) and `proposal_only` as 0 dropped the sponsor-handoff score from 0.87 to 0.73. Use the full package `amount`
   for each status bucket; report the unpaid portion separately in `open_invoice_balance`.

8. **A no-email badge with a phone is still a qualified lead.** The badge-only-contacts template explicitly
   permits `normalized_email: ""`. Do not classify an email-less badge with a usable phone as `missing_contact`; `missing_contact`
   requires BOTH email and phone to be absent.

9. **Sort everything the template mentions.** Forgetting an ascending sort (by account_name / company_name /
   badge_id / row_id / key / rank / subject_key) silently breaks otherwise-correct content.

10. **Re-read each template's enums.** Exclusion-reason enums differ across tasks
    (`sensor_vendor_only` in one, `sensor_only` in another; `not_target_market` only in some). Use exactly the task's enum strings.

## 5. Open uncertainties (treat carefully on test tasks)

The judge scores below 1.0 on the sponsor-handoff/import/reconciliation train tasks could not be fully closed.
Likely culprits if a similar test task scores low:
- **`import_action_totals`**: whether `suppress`/`no_import` should also count removed rows (suppressed/missing)
  in addition to clean-contact create/update was judge-neutral on train; try clean-contact-only first, and if the
  whole section is wrong, also report suppress=count(suppressed) and no_import=count(missing).
- **`campaign_member_actions` `subject_key`**: the exact identifier format (email vs contact_id vs contact_name)
  and whether no_action vs update applies to already-correct existing sponsor members — verify against the template's
  wording; sort by whatever stable key you choose and keep it consistent.
- **`opportunity_summary.lead_opportunity_amount_usd`**: report as the total lead pipeline
  (qualified count × per-account lead_opportunity_amount) when the field sits next to a `_total_` field; if a task's
  template reads it as the single configured amount, switch to the per-account value.

When stuck, prefer the interpretation that keeps totals internally consistent (counts sum correctly, totals = Σ of their items)
and that matches the exact enum strings and ordering in that task's template.