Normalizes messy bank-imported merchant names into clean, consistent labels using aggregate GROUP BY queries. Writes to the normalized_merchant overlay field, preserving original merchant names for provenance. Memory-backed for auto-applying known mappings on future runs.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
A direct command skips the review prompt. Inspect the source before running it.
Normalizes messy bank-imported merchant names into clean, consistent labels using aggregate GROUP BY queries. Writes to the normalized_merchant overlay field, preserving original merchant names for provenance. Memory-backed for auto-applying known mappings on future runs.
version
1.0.0
Merchant Normalization Skill
This skill guides the finance butler through normalizing messy bank-imported merchant
names (e.g., "WHOLEFDS MKT #10456 AUSTIN TX") into clean, consistent labels
(e.g., "Whole Foods"). The canonical form is stored in the normalized_merchant
metadata overlay field; the original merchant field is never modified.
CRITICAL CONSTRAINTS
READ THIS SECTION FIRST. These are hard rules, not suggestions.
MUST NOT: Call list_transactions
You MUST NOT call list_transactions or any other tool that reads individual
transaction rows into context at any point during this workflow.
Reason: A typical account has hundreds or thousands of transaction rows.
Reading them into context would exhaust the token budget and likely abort the
session before any normalization is applied. The unit of work for normalization
is the merchant name, not the individual transaction.
The only acceptable data source is list_distinct_merchants, which returns a
compact GROUP BY result — typically 50–200 rows even for a year of transactions.
MUST NOT: Read or re-record transaction rows
You MUST NOT attempt to normalize merchants by reading transactions and writing
them back one row at a time. This is both token-prohibitive and semantically
wrong — normalization is a metadata overlay, not a re-ingestion.
MUST: Use list_distinct_merchants as the sole data source
All merchant data used in this workflow comes from list_distinct_merchants. This
tool returns deduplicated merchant names with transaction counts and totals. It is
the correct and only acceptable source for normalization input.
MUST: Paginate if distinct merchant count exceeds 500
If the first call returns total > 500, you MUST process in paginated batches.
Use limit=500, offset=0, then limit=500, offset=500, etc., until all merchants
are processed. Do not skip pagination — large accounts can have hundreds of
distinct raw merchant strings.
MUST: Write to normalized_merchant overlay only
Normalization MUST write to the normalized_merchant metadata field via
bulk_update_transactions. It MUST NOT modify the original merchant field, and
MUST NOT touch subject, predicate, content, or embedding columns. The
original merchant value is the immutable provenance anchor and deduplication key.
Metadata Overlay Contract
The finance butler's fact layer is write-once on core columns. Normalization
uses the metadata overlay pattern instead:
Field
Role
merchant
Original raw bank string — never modified, preserved for provenance
normalized_merchant
Clean canonical name set by this skill — preferred for display and aggregation
Query and display tools (spending_summary, list_transactions, dashboard) prefer
normalized_merchant when present and fall back to merchant when absent. You do
not need to update any query logic — the overlay is transparently consumed.
Workflow
Step 1: Query Distinct Merchants
Call list_distinct_merchants to get the compact merchant list:
result = list_distinct_merchants(
unnormalized_only=True, # only merchants without a normalized_merchant overlay
min_count=2, # skip one-off merchants unlikely to need normalization
)
Inspect the response:
total — total number of distinct unnormalized merchants
merchants — list of {merchant, count, total_amount} entries
If total > 500, proceed in paginated batches (see CRITICAL CONSTRAINTS)
If total == 0, report "All merchants are already normalized." and stop.
Step 2: Recall Known Mappings from Memory
Before running any LLM inference, check whether this butler has seen and mapped
any of these merchants before:
Also try targeted recall for high-frequency merchants you see in the list:
memory_recall(topic="merchant_alias:WHOLEFDS")
Memory fact schema for aliases (written in Step 7):
subject: "merchant_alias:<RAW_PATTERN>" — the raw bank string or a prefix/pattern
predicate: "normalizes_to"
content: "Whole Foods" — the clean canonical name
tags: ["merchant-alias", "normalization"]
Collect all known mappings into a lookup table before proceeding.
Step 3: Auto-Apply Known Mappings
For every merchant in the list that matches a known alias, call
bulk_update_transactions immediately — no LLM review needed:
bulk_update_transactions(updates=[
{
"match": {"merchant_pattern": "WHOLEFDS%"},
"set": {"normalized_merchant": "Whole Foods"},
},
{
"match": {"merchant_pattern": "AMZN MKTP US%"},
"set": {"normalized_merchant": "Amazon"},
},
# ... one entry per known alias
])
Track which merchants from the list have been handled so they are excluded from
the LLM review in Step 4.
Step 4: LLM Normalizes Unknown Merchants
Present the remaining merchants (those not covered by known aliases) to
yourself for grouping. For each merchant, you have count (transaction frequency)
and total_amount (aggregate spend) as context for prioritization.
Group obvious variants into a single canonical name. Apply these heuristics:
Strip store numbers and location suffixes: "WHOLEFDS MKT #10456 AUSTIN TX" → "Whole Foods"
Processed in 2 batches (1,100 distinct merchants total).
Step 7: Remember New Normalization Rules
For each new mapping produced by the LLM in Step 4 (i.e., not already in memory),
store a memory fact so future runs can auto-apply it:
# Resolve or create the merchant entity first (follow memory-classification skill).# resolve_or_create_merchant_entity is shorthand for the try/except pattern below —# use the real MCP calls (as shown in the worked example's Step 7):try:
result = memory_entity_create(canonical_name="Whole Foods", entity_type="organization",
metadata={"source": "fact_storage", "source_butler": "finance"})
entity_id = result["entity_id"]
except ValueError:
candidates = memory_entity_resolve(name="Whole Foods", entity_type="organization")
entity_id = candidates[0]["entity_id"]
memory_store_fact(
subject="merchant_alias:WHOLEFDS",
predicate="normalizes_to",
content="Whole Foods",
entity_id=entity_id,
permanence="stable",
importance=7.0,
tags=["merchant-alias", "normalization"],
)
Store one fact per distinct raw-pattern-to-canonical mapping. Use the shortest
prefix that uniquely identifies the merchant family as the subject (e.g.,
"merchant_alias:WHOLEFDS" covers all Whole Foods store numbers).
Entity resolution for merchant facts: Follow the memory-classification skill's
Resolve-or-Create protocol. The canonical name (e.g., "Whole Foods") is the
entity's canonical_name with entity_type="organization".
Pagination Reference
When total > 500, loop over batches:
offset = 0
while True:
result = list_distinct_merchants(
unnormalized_only=True,
min_count=2,
limit=500,
offset=offset,
)
# ... process result.merchants (Steps 2–5 for this batch)
if offset + 500 >= result.total:
break
offset += 500
Process each batch through the full Steps 2–5 pipeline before moving to the next.
Memory recall (Step 2) carries over across batches — load known aliases once before
the loop and reuse them.
Worked Example
Scenario: A Chase Checking account was imported last week with 3 months of
transactions. Running list_distinct_merchants(unnormalized_only=True, min_count=2)
returns 22 merchants.
list_distinct_merchants returns total: 0 with unnormalized_only=True
Report "All merchants already normalized." and stop
bulk_update_transactions returns total_matched: 0 for a pattern
Pattern may be too specific; broaden with % or check for leading/trailing spaces in raw merchant string
Memory search returns no results
Proceed directly to LLM normalization; memory will be populated after Step 7
total > 500 merchants
Paginate in batches of 500 (see Pagination Reference above)
Merchant name is ambiguous (could be two different businesses)
Keep separate — do not collapse unless you are certain they are the same merchant
Relationship to Other Skills
Run before category-inference: Normalization collapses merchant variants first.
The category-inference skill then operates on a cleaner, deduplicated merchant list,
producing more accurate category assignments.
memory-classification: Follow its Resolve-or-Create protocol when storing
merchant alias facts in Step 7.
tool-reference: Consult for exact parameter names and types for
list_distinct_merchants and bulk_update_transactions.
