| name | broker-analytics |
| description | Rank brokers and agents by motivated seller distress signals, with configurable price segment and market |
| user-invocable | true |
| argument-hint | [market name] [optional: min price, e.g. 500k, 2M, or 'all'] |
| allowed-tools | Read, Grep, Glob, Bash, Write, Edit, WebFetch |
Broker Analytics: Motivated Seller Analysis by Broker/Agent
Generates a 5-sheet Excel workbook analyzing agent and brokerage performance in a target market, ranked by motivated seller distress signals.
Target market: First argument (e.g., "Hamptons", "Houston", "Tampa")
Price segment: Second argument controls the minimum listing price filter:
2M or 2m (default if omitted): luxury segment, properties over $2,000,000500k or 500K: mid-market, properties over $500,0001M or 1m: upper market, properties over $1,000,0005M or 5m: ultra-luxury, properties over $5,000,000all or 0: no price filter, analyze the entire market
Parse the second argument by stripping the letter suffix and converting: k/K = multiply by 1,000, m/M = multiply by 1,000,000. If no second argument is provided, default to $2,000,000.
Examples:
/broker-analytics Hamptons
/broker-analytics Houston 500k
/broker-analytics Tampa all
/broker-analytics "Beverly Hills" 5M
Audience: Real estate teams prospecting for listing opportunities by identifying underperforming agents and brokerages with distressed inventory.
Display Names
Never display raw parcl_id values in user-facing output. Always resolve to human-readable names:
- ZIP parcl_ids to actual ZIP codes and town names (e.g.,
5452730 to "East Hampton (11937)")
- MSA parcl_ids to metro area names (e.g.,
2900417 to "Tampa-St. Petersburg-Clearwater, FL")
- Investor IDs to standardized names from
search_investors
Note: some MCP endpoints return IDs as floats with .0 suffix (e.g., 5452730.0). Strip the .0 before any lookup or display.
Required Tools
| Step | Tool | Purpose |
|---|
| Resolve market | search_locations | Convert market name to parcl_id(s). Get all relevant ZIP codes and cities. |
| Pull listings | motivated_seller_properties | All active for-sale listings with agent info, price, DOM, motivated seller scores. |
Execution Steps
1. Resolve Geography
Use search_locations to resolve the market name into parcl_id(s). Think broadly. Most markets span multiple ZIP codes and town names. Run multiple searches to capture all relevant areas. Focus on ZIP-level IDs.
2. Pull All Listings
Call motivated_seller_properties with:
parcl_ids: all resolved market IDssort_by: "latest_listing_price"sort_order: "desc"limit: 50000
Always run preview=True first to check credit cost, then preview=False to download.
3. Download & Filter
Retrieve the CSV via the presigned download_url. Filter to properties where latest_listing_price exceeds the parsed price threshold. If the user specified all, skip filtering and use all listings.
4. Generate Workbook
Use the bundled script:
python3 ${CLAUDE_SKILL_DIR}/scripts/build_workbook.py <input_csv> <output_xlsx> --market "Market Name"
If the script isn't available, follow the workbook structure below using openpyxl.
Workbook Structure: 5 Sheets
Color Palette
| Element | Color | Hex |
|---|
| Title bar background | Dark navy | #1F4E79 |
| Title text | White bold Arial 14 | #FFFFFF |
| Subtitle text | Gray Arial 10 | #666666 |
| Warning subtitle text | Dark red bold Arial 10 | #C00000 |
| Properties header row | Medium blue | #2E75B6 |
| Brokerage header row | Orange | #ED7D31 |
| Agent/Distress header row | Dark red | #C00000 |
| Fire sale row highlight | Light red | #FFC7CE |
| Motivated row highlight | Light yellow | #FFEB9C |
| Above-market-avg row highlight | Cream | #FFF2CC |
| "vs Market Avg" negative delta cell | Red text on red bg | text #9C0006, bg #FFC7CE |
| "vs Market Avg" positive delta cell | Green text on green bg | text #006100, bg #C6EFCE |
Formatting Standards
- Font: Arial 10pt throughout, Arial 14pt bold for titles
- Number formats: Currency
$#,##0, Percent 0.0%, Score 0.00, DOM 0, vs-Market +0.00;-0.00;0.00
- All data sheets: freeze panes below header, auto-filter on header row
- Title row: merged across all columns, dark navy background
- Row-level color coding on Sheet 2 based on
motivated_seller_index_label:
fire_sale → entire row gets #FFC7CEmotivated → entire row gets #FFEB9Cstubborn / neutral → no highlight
Sheet 1: Executive Summary
A dashboard with market-level KPIs. Layout (A=labels, B=values):
Row 1 (merged A1:H1): {MARKET} FOR-SALE MARKET: MOTIVATED SELLER ANALYSIS BY BROKER/AGENT, dark navy bg, white bold 14pt
Row 2 (merged A2:H2): Properties Listed Over {PRICE_THRESHOLD} | Data as of {date} | Source: Parcl Labs, gray 10pt
Row 4 (merged A4:C4): MARKET SNAPSHOT, bold, light blue bg (#D6E4F0), navy text (#1F4E79)
| Row | A (bold label) | B (value) |
|---|
| 5 | Total Listings | count |
| 6 | Listings with Agent Data | count where agent_name is not null |
| 7 | Unique Brokerages | nunique of agent_business |
| 8 | Unique Agents | nunique of agent_name |
| 9 | Median Listing Price | median of latest_listing_price (fmt: $#,##0) |
| 10 | Avg Days on Market | mean of days_on_market (fmt: 0) |
| 11 | Avg Motivated Seller Score | mean of motivated_seller_index_value (fmt: 0.00) |
| 12 | Fire Sale Properties | count where label == fire_sale |
| 13 | Motivated Properties | count where label == motivated |
| 14 | Stubborn Properties | count where label == stubborn |
| 15 | Neutral Properties | count where label == neutral |
Row 17 (merged A17:E17): MOTIVATED SELLER LABEL DISTRIBUTION, bold, light blue bg, navy text
| Row | A (bold) | B | C | D | E |
|---|
| 18 | Label | Count | % of Total | Avg Price | Avg DOM |
| 19-22 | fire_sale / motivated / stubborn / neutral | computed values | | | |
Row 24 (merged A24:H24): KEY INSIGHT, bold, light blue bg, navy text
Rows 25-26 (merged A25:H26): Narrative insight paragraph:
Market avg motivated seller score is {avg_score:.2f}. Brokerages/agents scoring ABOVE this average have listings that are sitting longer, cutting prices more, and signaling more seller distress, a potential indicator of overpricing or underperformance relative to the market.
Column widths: A=32, B=22, C=16, D-H=13
Sheet 2: All Properties Over {PRICE_THRESHOLD}
All listings above the price threshold sorted by motivated_seller_index_value descending (most distressed first), then by days_on_market descending.
Row 1 (merged): ALL {MARKET} PROPERTIES FOR SALE OVER {PRICE_THRESHOLD}, dark navy bg, white 14pt bold
Row 2 (header row): medium blue bg (#2E75B6), white bold Arial 10pt
| Column | Header | Source Field | Format |
|---|
| A | Parcl Property ID | parcl_property_id | |
| B | Address | address1 | |
| C | City | city | |
| D | ZIP | zip5 | |
| E | Type | property_type | |
| F | Beds | bedrooms | |
| G | Baths | bathrooms | |
| H | Sq Ft | sq_ft | |
| I | Year Built | year_built | |
| J | Current Price | latest_listing_price | $#,##0 |
| K | Initial Price | initial_listing_price | $#,##0 |
| L | Days on Market | days_on_market | |
| M | Price Cuts | num_price_cuts | |
| N | % Price Change | percent_change_listing_price | 0.0% |
| O | Motivated Score | motivated_seller_index_value | 0.00 |
| P | Motivated Label | motivated_seller_index_label | |
| Q | Agent Name | agent_name | |
| R | Brokerage | agent_business | |
| S | Agent Email | agent_email | |
| T | Agent Phone | agent_phone | |
| U | Owner Entity | entity_name | |
| V | Original Purchase Price | original_purchase_price | $#,##0 |
| W | Unrealized P&L | unrealized_net | $#,##0 |
Row coloring: #FFC7CE for fire_sale, #FFEB9C for motivated, no fill for others.
Freeze pane: A3. Auto-filter on row 2.
Sheet 3: Brokerage Analysis
Aggregated by agent_business, ranked by avg motivated seller score descending (worst performers at top).
Row 1 (merged): BROKERAGE PERFORMANCE: RANKED BY MOTIVATED SELLER SCORE (UNDERPERFORMERS AT TOP), dark navy bg, white 14pt bold
Row 2 (merged): Market Avg Motivated Score: {avg:.2f} | Brokerages above this line are underperforming the market, dark red bold 10pt (#C00000)
Row 3 (header): Orange bg (#ED7D31), white bold Arial 10pt
| Column | Header | Calculation |
|---|
| A | Brokerage | Group key (bold in data rows) |
| B | # Listings | Count |
| C | Avg Motivated Score | Mean motivated_seller_index_value (fmt: 0.00) |
| D | vs Market Avg | score - market_avg (fmt: +0.00;-0.00;0.00) |
| E | Avg Days on Market | Mean DOM (fmt: 0) |
| F | Avg Price Cuts | Mean num_price_cuts (fmt: 0.0) |
| G | Avg % Price Change | Mean percent_change_listing_price (fmt: 0.0%) |
| H | Total Listing Value | Sum latest_listing_price (fmt: $#,##0) |
| I | Avg Listing Price | Mean latest_listing_price (fmt: $#,##0) |
| J | Fire Sale | Count where label == fire_sale |
| K | Motivated | Count where label == motivated |
| L | Neutral + Stubborn | Count where label in (neutral, stubborn) |
Row highlighting: rows where avg score > market avg get cream bg (#FFF2CC). The "vs Market Avg" cell: positive values get red text (#9C0006) on red bg (#FFC7CE); negative/zero get green text (#006100) on green bg (#C6EFCE).
Freeze pane: A4. Auto-filter on row 3.
Sheet 4: Agent Analysis
Aggregated by agent_name + agent_business, ranked by avg motivated seller score descending.
Row 1 (merged): AGENT PERFORMANCE: RANKED BY MOTIVATED SELLER SCORE (UNDERPERFORMERS AT TOP), dark navy bg, white 14pt bold
Row 2 (merged): Market Avg Motivated Score: {avg:.2f} | Agents above this line have more distressed listings, dark red bold 10pt
Row 3 (header): Dark red bg (#C00000), white bold Arial 10pt
| Column | Header | Calculation |
|---|
| A | Agent | Group key (bold in data rows) |
| B | Brokerage | Group key |
| C | # Listings | Count |
| D | Avg Motivated Score | Mean score (fmt: 0.00) |
| E | vs Market Avg | score - market_avg (fmt: +0.00;-0.00;0.00) |
| F | Avg Days on Market | Mean DOM (fmt: 0) |
| G | Avg Price Cuts | Mean cuts (fmt: 0.0) |
| H | Avg % Price Change | Mean pct change (fmt: 0.0%) |
| I | Total Value | Sum latest_listing_price (fmt: $#,##0) |
| J | Fire Sales | Count fire_sale |
| K | Motivated | Count motivated |
Row highlighting: same logic as Sheet 3.
Freeze pane: A4. Auto-filter on row 3.
Sheet 5: Fire Sale & Motivated Detail
Only properties with motivated_seller_index_label in (fire_sale, motivated), sorted by score descending then DOM descending.
Row 1 (merged): FIRE SALE & MOTIVATED PROPERTIES: {MARKET}, dark red bg (#C00000), white 14pt bold
Row 2 (header): Dark red bg (#C00000), white bold Arial 10pt
| Column | Header | Source | Format |
|---|
| A | Parcl Property ID | parcl_property_id | |
| B | Address | address1 | |
| C | City | city | |
| D | ZIP | zip5 | |
| E | Current Price | latest_listing_price | $#,##0 |
| F | Initial Price | initial_listing_price | $#,##0 |
| G | Days on Market | days_on_market | |
| H | Price Cuts | num_price_cuts | |
| I | % Price Change | percent_change_listing_price | 0.0% |
| J | Motivated Score | motivated_seller_index_value | 0.00 |
| K | Label | motivated_seller_index_label | |
| L | Agent | agent_name | |
| M | Brokerage | agent_business | |
| N | Owner Entity | entity_name | |
| O | Unrealized P&L | unrealized_net | $#,##0 |
Row coloring: fire_sale rows get #FFC7CE, motivated rows get no special fill (to distinguish from fire_sale).
Freeze pane: A3. Auto-filter on row 2.
Example Usage
/broker-analytics Hamptons
/broker-analytics Beverly Hills
/broker-analytics Palm Beach
/broker-analytics Aspen
