Skip to content

afawzy886619888866-cloud/Sierra-Estates / .agents/skills-archive/inventory-csv-merger

inventory-csv-merger

Verified

Merge new Owners-Rent or Owners-Buy CSV exports into the Sierra Estates master inventory CSV. Handles column mapping between Google Form / CRM export format and the canonical master schema, deduplicates by phone+code, back-fills sparse fields, transitions stale availability statuses, and regenerates the consolidated Excel workbook.

Inventory CSV Merger Skill

When to Use

Activate this skill whenever:

  • A new Owners-Rent CSV or Owners-Buy CSV arrives (from Google Forms, CRM export, or manual data entry)
  • The user asks to merge, import, update, or deduplicate rental/sale inventory
  • A new batch of owner contacts needs to be consolidated into the master

Prerequisites

  • Node.js 18+
  • papaparse and xlsx packages (already in project dependencies)

Source CSV Formats

New CSV Format (Google Form / CRM Export)

ColumnDescription
TimestampEntry date/time
NORow number
تاريخ اخر تحديثLast update date
NameOwner / contact name
MobilePhone number (10–13 digits, possibly with spaces)
AvailabltyStatus: Available, No answer, Not available, Follow up, Sold
bedroomsNumber of bedrooms
Location Compound/area name (free text, trailing space in header)
Unit PricePrice — may be EGP, USD ($), "Price on Call", or "per meter"
Furnished or notFurnishing status
TypeDeal type: rent, sale, Furnished rent, اتباعت, تم الايجار
Property TybeProperty type (note: typo in original)
CodeUnit listing code
OwnerAlways "Owner" for direct owner intake
GardenGarden area in sqm
SpaceBuilt-up area in sqm
PoolPool availability (yes/no)
CommentFree-text notes

Master CSV Format (Target)

ColumnMaps From
Unit CodeCode
Compound / CommunityLocation → gazetteer lookup
Zone / AreaLocation → gazetteer zone
Property TypeProperty Tybe (normalized)
Monthly Rent (EGP)Unit Price (cleaned)
Rent DisplayFormatted price string
Area (sqm)Space
Bedroomsbedrooms
Bathrooms(not in source)
FurnishingFurnished or not
Owner / Contact NameName
Owner PhoneMobile (formatted +20...)
Direct WhatsAppGenerated from phone
Listing StatusAvailablty + Type (mapped)
Source ChannelAlways "Direct Owner Intake"
Listing NotesComment
Record IDSHA-256 hash of phone+code
Garden (sqm)Garden
PoolPool
Availability RawAvailablty (original text)
Deal TypeType → rent/sale
Last Updatedتاريخ اخر تحديث or Timestamp

Deduplication Strategy

  1. Primary key: normalizedPhone (last 10 digits) + Code (uppercased)
  2. When matched:
    • Keep whichever record has more filled fields (field score)
    • Back-fill empty/placeholder fields from the other record
    • Update status if new data says "Not available"/"اتباعت" but old says "Available"
  3. Fallback: code-only match when phone differs (same unit, different contact)

Running the Merge

# Dry run (preview only, no writes)
node scripts/merge-owners-rent-csv.mjs <path-to-new-csv> --dry-run

# Actual merge
node scripts/merge-owners-rent-csv.mjs <path-to-new-csv>

Post-Merge Checklist

After running the merge:

  1. Verify the merged CSV row count and compound distribution
  2. Regenerate consolidated Excel:
    node scripts/create-consolidated-rent-master.mjs
    
  3. Copy to data directory (if needed):
    cp Sierra_Estates_Owners_Rent_Master.csv apps/sierra-estates-realty/data/
    
  4. Update snapshot for the inventory API fallback
  5. Redeploy to Vercel if the snapshot changed
  6. Verify map at /explore — check compound badges show updated counts

Location Resolution

The merge script uses a built-in gazetteer to resolve free-text location names to canonical compound names and zones. Known aliases include:

  • Mevida → Mivida
  • CFC → Cairo Festival City
  • rehab → Al Rehab
  • eypet hose elkurfenl → Dar Misr (El Koronfel)
  • banfcg → Al Banafsaj
  • andlos → Al Andalus
  • gardina city → Gardenia City

If a location can't be resolved, it falls through to Unresolved and should be manually corrected in the output CSV.

Price Handling

  • Comma-grouped numbers (8,500,000) → stripped to integer
  • USD prices (1,400$, 700$) → converted at 50 EGP/USD rate
  • "per meter" or "negotiable" → mapped to "Price on Call"
  • Sale prices (≥1M) detected even when Type says "rent" — heuristic override

Status Mapping

Source ValuesMaster Status
AvailableAvailable for Rent
No answerNo Answer
Not availableUnavailable
Follow upFollow Up
Sold, اتباعت, تم الايجارUnavailable
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall