Implementation Guide: Generate mls listing descriptions, social media posts, and email drip campaigns

# OpenAI prompt settings and Google Sheets review queue setup

# Step 3: OpenAI (ChatGPT) — Send Prompt
#   Model: gpt-5.4-mini
#   System Prompt: (see Custom AI Components section - 'MLS Listing Description Generator')
#   User Prompt: 'Generate content for: {{address}}, {{price}}, {{beds}} bed/{{baths}} bath, {{sqft}} sqft. Features: {{description_notes}}'
#   Temperature: 0.7
#   Max Tokens: 2000

# Step 5: Google Sheets — Create Row in 'Content Review Queue'
#   Spreadsheet: '[Brokerage] AI Content Review'
#   Sheet: 'Pending Review'
#   Columns: Date, Address, Agent, MLS_Description, FB_Post, IG_Caption, LI_Post, Email_Subject, Email_Body, Status='PENDING'

# append to all AI content generation prompts

NEVER include language that references, implies, or could be perceived as referring to:
race, color, national origin, religion, sex, sexual orientation, gender identity,
disability, familial status, age, or any protected class under the Fair Housing Act.
DO NOT use phrases like: 'perfect for families', 'great bachelor pad', 'walking
distance to [church/temple/mosque]', 'master bedroom' (use 'primary bedroom'),
'man cave', 'ideal for young professionals', 'no children', 'quiet neighborhood
for retirees', 'exclusive community'. Focus ONLY on property features, not people.

# add to all email templates

Before sending: Verify this email contains NO references to resident
demographics, protected classes, or neighborhood descriptions that could
imply preference or exclusion based on race, religion, familial status,
disability, national origin, sex, or color.

Action: Mailchimp — Add/Update Subscriber
Audience: '[Brokerage] Contacts'
Email: {{lead_email}}
First Name: {{lead_first_name}}
Last Name: {{lead_last_name}}
Tags: 'buyer, new-lead'
# This triggers the Buyer Drip campaign in Mailchimp

Action: Mailchimp — Add/Update Subscriber
Audience: '[Brokerage] Contacts'
Email: {{lead_email}}
Tags: 'seller, new-lead'
# This triggers the Seller Drip campaign in Mailchimp

Action 1: Mailchimp — Add/Update Subscriber
Tags: 'unclassified, new-lead'

Action 2: Gmail/Outlook — Send notification to assigned agent
Message: 'New lead {{lead_name}} needs classification — please tag as buyer or seller in FUB'

Action: Follow Up Boss — Add Note to Lead
Note: 'Auto-enrolled in {{drip_type}} email drip campaign via Mailchimp on {{date}}'

Trigger: Follow Up Boss — Deal Stage Changed

If stage = 'Under Contract':
  Action: Mailchimp — Unsubscribe from tag (remove from active drip)

If stage = 'Closed':
  Action: Mailchimp — Move to 'past-client' tag, enroll in SOI monthly drip

SYSTEM PROMPT:
---
You are an expert real estate copywriter specializing in MLS listing descriptions. You write compelling, accurate, and SEO-optimized property descriptions that comply with the Fair Housing Act and MLS formatting standards.

RULES:
1. FAIR HOUSING COMPLIANCE (MANDATORY):
   - NEVER reference or imply preferences based on race, color, national origin, religion, sex, sexual orientation, gender identity, disability, familial status, or age.
   - NEVER use: 'perfect for families', 'great for singles', 'bachelor pad', 'man cave', 'master bedroom' (use 'primary bedroom'), 'walking distance to [religious institution]', 'quiet for retirees', 'exclusive community', 'no children', 'family-friendly', 'ideal for young professionals', 'Christian/Jewish/Muslim neighborhood', 'handicap' (use 'accessible'), 'safe neighborhood' (implies other areas are unsafe), 'integrated', 'ethnic'.
   - Focus ONLY on property features, architectural details, finishes, systems, and location amenities (parks, shopping, transit, schools by name only without demographic inference).

2. CONTENT STRUCTURE:
   - Opening hook: 1-2 sentences that create emotional appeal through property features (NOT occupant lifestyle).
   - Property overview: beds, baths, sqft, lot size, year built, style, condition.
   - Feature highlights: 3-5 key features with descriptive detail (kitchen, bathrooms, outdoor space, views, upgrades).
   - Location context: Nearby amenities, school district name (not quality rating), commute access, community features.
   - Closing CTA: Create urgency through market context, not exclusivity.

3. FORMATTING:
   - Length: 150-300 words (adjustable per MLS character limits)
   - NO ALL CAPS except for proper nouns
   - NO exclamation marks in sequence (max 2 per description)
   - Include relevant keywords naturally: [city name], [neighborhood name], [property type], [key features]
   - Use present tense
   - Avoid superlatives without basis ('best', 'most', 'finest')

4. OUTPUT FORMAT:
   Return a JSON object with these fields:
   {
     "mls_description": "[Full MLS listing description, 150-300 words]",
     "headline": "[MLS headline, max 80 characters]",
     "seo_keywords": ["keyword1", "keyword2", "keyword3", "keyword4", "keyword5"]
   }
---

USER PROMPT TEMPLATE:
Generate an MLS listing description for the following property:

Address: {{address}}
City/State: {{city}}, {{state}}
Neighborhood: {{neighborhood}}
Price: ${{price}}
Bedrooms: {{beds}}
Bathrooms: {{baths}}
Square Feet: {{sqft}}
Lot Size: {{lot_size}}
Year Built: {{year_built}}
Property Type: {{property_type}} (e.g., Single Family, Condo, Townhouse)
Style: {{style}} (e.g., Colonial, Ranch, Contemporary, Craftsman)
Key Features: {{features}} (comma-separated list from agent notes)
Recent Upgrades: {{upgrades}}
Parking: {{parking}}
HOA: {{hoa_amount}} per {{hoa_period}} (if applicable)
School District: {{school_district}}
Agent Notes: {{agent_notes}} (any special selling points or context)

import openai
import json

client = openai.OpenAI(api_key='sk-YOUR-API-KEY')

def generate_listing_description(property_data: dict) -> dict:
    system_prompt = """[Insert full system prompt above]"""
    
    user_prompt = f"""Generate an MLS listing description for the following property:
    Address: {property_data['address']}
    City/State: {property_data['city']}, {property_data['state']}
    Neighborhood: {property_data.get('neighborhood', 'N/A')}
    Price: ${property_data['price']:,}
    Bedrooms: {property_data['beds']}
    Bathrooms: {property_data['baths']}
    Square Feet: {property_data['sqft']:,}
    Lot Size: {property_data.get('lot_size', 'N/A')}
    Year Built: {property_data.get('year_built', 'N/A')}
    Property Type: {property_data['property_type']}
    Style: {property_data.get('style', 'N/A')}
    Key Features: {property_data.get('features', 'N/A')}
    Recent Upgrades: {property_data.get('upgrades', 'None noted')}
    Parking: {property_data.get('parking', 'N/A')}
    HOA: {property_data.get('hoa', 'N/A')}
    School District: {property_data.get('school_district', 'N/A')}
    Agent Notes: {property_data.get('agent_notes', 'None')}"""
    
    response = client.chat.completions.create(
        model='gpt-5.4-mini',
        messages=[
            {'role': 'system', 'content': system_prompt},
            {'role': 'user', 'content': user_prompt}
        ],
        temperature=0.7,
        max_tokens=1000,
        response_format={'type': 'json_object'}
    )
    
    return json.loads(response.choices[0].message.content)

# Example usage:
property_info = {
    'address': '123 Oak Street',
    'city': 'Austin', 'state': 'TX',
    'neighborhood': 'Travis Heights',
    'price': 625000,
    'beds': 3, 'baths': 2.5,
    'sqft': 2100,
    'lot_size': '0.18 acres',
    'year_built': '2019',
    'property_type': 'Single Family',
    'style': 'Contemporary',
    'features': 'open floor plan, quartz countertops, 10-foot ceilings, covered patio, smart home system',
    'upgrades': 'New landscaping 2024, added solar panels',
    'parking': '2-car attached garage',
    'school_district': 'Austin ISD',
    'agent_notes': 'Corner lot with mature trees, walking distance to South Congress shops'
}
result = generate_listing_description(property_info)
print(result['mls_description'])

SYSTEM PROMPT:
---
You are a social media marketing expert specializing in real estate content. You create engaging, platform-specific social media posts that drive property inquiries and brand awareness.

For each property, generate THREE separate posts optimized for different platforms.

RULES:
1. FAIR HOUSING COMPLIANCE: All rules from the MLS Description Generator apply. Never reference potential occupant demographics.
2. Each post must be self-contained and platform-optimized.
3. Include a clear call-to-action in every post.
4. Use the agent's name and contact info provided.

PLATFORM SPECIFICATIONS:

FACEBOOK POST:
- Length: 100-200 words
- Tone: Conversational, community-focused
- Structure: Hook line → 3-4 key features → neighborhood context → CTA with link
- Include 1-2 emojis per sentence (subtle, professional)
- End with: 'DM me or call [phone] for a private showing!'
- DO NOT include hashtags (Facebook algorithm deprioritizes them)

INSTAGRAM CAPTION:
- Length: 80-150 words for caption
- Tone: Visual, aspirational, lifestyle-focused (property lifestyle, NOT occupant lifestyle)
- Structure: Attention-grabbing first line (this shows before 'more') → Property highlights → CTA
- Include 5-8 emojis throughout
- End caption with CTA: 'Link in bio for details! 📲'
- After caption, add a line break then 20-25 hashtags in a comment block, mix of:
  - Location: #[city]realestate #[neighborhood]homes #[city]realtor
  - Feature: #newlisting #justlisted #dreamhome #househunting #[bedscount]bedroom
  - Industry: #realestate #realtor #realtorlife #homeforsale #property
  - Niche: #[style]home #[feature]kitchen #[feature]pool (as applicable)

LINKEDIN POST:
- Length: 100-150 words
- Tone: Professional, market-informed, analytical
- Structure: Market context opener → Property as case study → Value proposition → Professional CTA
- Minimal emoji use (0-2 max)
- Include 3-5 hashtags inline at the end
- End with: 'Interested in learning more about opportunities in [neighborhood]? Let's connect.'

OUTPUT FORMAT:
Return a JSON object:
{
  "facebook_post": "[Full Facebook post text]",
  "instagram_caption": "[Full Instagram caption with emojis]",
  "instagram_hashtags": "[20-25 hashtags separated by spaces]",
  "linkedin_post": "[Full LinkedIn post text]"
}
---

USER PROMPT TEMPLATE:
Generate social media posts for this new listing:

Property: {{address}}, {{city}}, {{state}}
Price: ${{price}}
Beds/Baths: {{beds}}bd / {{baths}}ba
Sqft: {{sqft}}
Key Features: {{features}}
Neighborhood: {{neighborhood}}
Agent Name: {{agent_name}}
Agent Phone: {{agent_phone}}
Listing URL: {{listing_url}}
Photo Description: {{photo_description}} (brief description of the best listing photo for caption context)

SYSTEM PROMPT:
---
You are an expert real estate email marketing copywriter. You create high-converting email drip campaign sequences that nurture leads from initial contact through conversion (buyer: purchase; seller: listing appointment).

RULES:
1. FAIR HOUSING COMPLIANCE: Never reference occupant demographics or protected classes.
2. CAN-SPAM COMPLIANCE: Every email must be identifiable as marketing content. Do not use deceptive subject lines.
3. Include Mailchimp merge tags for personalization: *|FNAME|* (first name), *|AGENT_NAME|* (agent name), *|AGENT_PHONE|* (agent phone), *|AGENT_EMAIL|* (agent email), *|COMPANY|* (brokerage name).
4. Each email should have a single, clear call-to-action.
5. Subject lines: 40-60 characters, create curiosity or provide value, no spam trigger words (FREE, ACT NOW, URGENT, etc.).
6. Preview text: 40-90 characters, complements subject line.
7. Body: 150-250 words, scannable with short paragraphs (2-3 sentences max).
8. Tone: Warm, professional, helpful — position the agent as a trusted advisor, not a salesperson.

OUTPUT FORMAT:
Return a JSON object:
{
  "campaign_name": "[Descriptive campaign name]",
  "campaign_type": "buyer|seller|soi",
  "emails": [
    {
      "sequence_number": 1,
      "send_delay_days": 0,
      "subject_line": "[Subject line with optional merge tag]",
      "preview_text": "[Preview text]",
      "body_html": "[Email body in simple HTML with paragraphs, bold, and links. Use merge tags for personalization.]",
      "cta_text": "[Button text]",
      "cta_url": "[Placeholder URL like {{agent_calendar_link}} or {{property_search_url}}]"
    }
  ]
}
---

USER PROMPT — BUYER DRIP:
Generate a complete 8-email buyer lead nurture drip campaign for a real estate agent.

Agent Name: {{agent_name}}
Brokerage: {{brokerage_name}}
Market Area: {{city}}, {{state}}
Agent Specialty: {{specialty}} (e.g., first-time buyers, luxury, relocation, investment)
Average Price Range: ${{price_low}} - ${{price_high}}
Key Neighborhood(s): {{neighborhoods}}

Email schedule:
- Email 1 (Day 0): Welcome + agent introduction
- Email 2 (Day 2): Local market overview with current stats
- Email 3 (Day 5): Home buying process step-by-step guide
- Email 4 (Day 10): Featured listings showcase (use generic placeholders for properties)
- Email 5 (Day 15): Mortgage and financing tips
- Email 6 (Day 22): Neighborhood spotlight on {{primary_neighborhood}}
- Email 7 (Day 30): Open house invitation or market update
- Email 8 (Day 45): Personal check-in + consultation scheduling CTA

USER PROMPT — SELLER DRIP:
Generate a complete 6-email seller lead nurture drip campaign.

Agent Name: {{agent_name}}
Brokerage: {{brokerage_name}}
Market Area: {{city}}, {{state}}
Average Days on Market: {{dom}}
Average Sale-to-List Ratio: {{sale_to_list}}%

Email schedule:
- Email 1 (Day 0): Welcome + free CMA offer
- Email 2 (Day 3): Home preparation checklist
- Email 3 (Day 7): Local market stats + recent comparable sales
- Email 4 (Day 14): Staging tips and ROI of preparation
- Email 5 (Day 21): Pricing strategy education
- Email 6 (Day 30): Personal check-in + listing appointment CTA

// JavaScript step for parsing OpenAI API response and extracting content
// fields

// Zapier Code by Zapier - JavaScript
// Input Data (configured in Zapier):
//   aiResponse: {{Step 3 OpenAI Response Content}}
//   propertyAddress: {{Step 2 Address}}
//   agentName: {{Step 2 Agent Name}}
//   agentPhone: {{Step 2 Agent Phone}}

const rawResponse = inputData.aiResponse;
const address = inputData.propertyAddress || 'Property';
const agentName = inputData.agentName || 'Your Agent';
const agentPhone = inputData.agentPhone || '';

let parsed;
try {
  parsed = JSON.parse(rawResponse);
} catch (e) {
  // If AI returned non-JSON, treat entire response as MLS description
  parsed = {
    mls_description: rawResponse,
    headline: address + ' - New Listing',
    facebook_post: 'New listing alert! ' + address + ' is now available. Contact ' + agentName + ' at ' + agentPhone + ' for details.',
    instagram_caption: '✨ Just Listed! ' + address + ' — Link in bio for details! 📲',
    instagram_hashtags: '#justlisted #newlisting #realestate #homeforsale #dreamhome',
    linkedin_post: 'Excited to bring ' + address + ' to market. Contact me to learn more about this opportunity.',
    email_subject: 'New Listing: ' + address,
    email_body: '<p>A new property has just hit the market at ' + address + '.</p><p>' + rawResponse.substring(0, 500) + '</p><p>Contact ' + agentName + ' at ' + agentPhone + ' for a private showing.</p>'
  };
}

// Validate and provide fallbacks for each field
const mlsDescription = (parsed.mls_description || '').trim();
const headline = (parsed.headline || address + ' - New Listing').trim();
const seoKeywords = Array.isArray(parsed.seo_keywords) ? parsed.seo_keywords.join(', ') : (parsed.seo_keywords || '');
const facebookPost = (parsed.facebook_post || 'New listing at ' + address + '! Contact ' + agentName + ' for details.').trim();
const instagramCaption = (parsed.instagram_caption || '✨ Just Listed! ' + address).trim();
const instagramHashtags = (parsed.instagram_hashtags || '#justlisted #newlisting #realestate').trim();
const linkedinPost = (parsed.linkedin_post || 'New listing opportunity at ' + address + '.').trim();
const emailSubject = (parsed.email_subject || 'New Listing: ' + address).trim();
const emailBody = (parsed.email_body || '<p>Check out this new listing at ' + address + '.</p>').trim();

// Character limit enforcement
const truncate = (str, max) => str.length > max ? str.substring(0, max - 3) + '...' : str;

output = {
  mls_description: truncate(mlsDescription, 4000),  // Most MLS systems cap at 4000 chars
  headline: truncate(headline, 80),
  seo_keywords: seoKeywords,
  facebook_post: truncate(facebookPost, 2000),
  instagram_caption: truncate(instagramCaption, 2200),  // IG caption limit
  instagram_hashtags: instagramHashtags,
  linkedin_post: truncate(linkedinPost, 3000),  // LinkedIn limit
  email_subject: truncate(emailSubject, 60),
  email_body: emailBody,
  content_generated_at: new Date().toISOString(),
  parse_status: 'success'
};

// Deploy as Zapier Code Step, AWS Lambda, or Google Cloud Function

// Fair Housing Language Validator
// Deploy as: Zapier Code Step, AWS Lambda, or Google Cloud Function
// Input: content string to validate
// Output: { is_clean: boolean, flags: array, remediation: array, severity: string }

const PROHIBITED_PHRASES = {
  high: [
    // Explicit protected class references
    { pattern: /\b(no\s+children|adults\s+only|no\s+kids)\b/gi, reason: 'Familial status discrimination', fix: 'Remove entirely — cannot restrict based on familial status' },
    { pattern: /\b(whites?\s+only|blacks?\s+only|no\s+(blacks?|whites?|hispanics?|asians?|mexicans?))\b/gi, reason: 'Race/national origin discrimination', fix: 'Remove entirely' },
    { pattern: /\b(no\s+wheelchairs?|no\s+disabled|no\s+handicap)\b/gi, reason: 'Disability discrimination', fix: 'Remove entirely' },
    { pattern: /\b(christian\s+community|jewish\s+neighborhood|muslim\s+area)\b/gi, reason: 'Religious discrimination', fix: 'Remove religious references' },
    { pattern: /\b(no\s+section\s+8|no\s+vouchers?)\b/gi, reason: 'Potential race/income discrimination (varies by jurisdiction)', fix: 'Remove — may violate local/state fair housing laws' }
  ],
  medium: [
    // Implied demographic preferences
    { pattern: /\b(perfect\s+for\s+families|family[- ]friendly|great\s+for\s+(singles?|couples?|retirees?|seniors?|young\s+professionals?|students?))\b/gi, reason: 'Implies preference for specific demographic group', fix: 'Describe property features instead of ideal occupants' },
    { pattern: /\b(bachelor\s+pad|man\s+cave|she[- ]shed|mother[- ]in[- ]law\s+suite)\b/gi, reason: 'Gender-stereotyped language', fix: 'Use: "bonus room", "private suite", "additional living space"' },
    { pattern: /\b(master\s+bedroom|master\s+suite|master\s+bath)\b/gi, reason: 'Potentially offensive historical connotation', fix: 'Use: "primary bedroom", "primary suite", "primary bathroom"' },
    { pattern: /\b(exclusive\s+(community|neighborhood|area|enclave))\b/gi, reason: 'Implies exclusionary practices', fix: 'Use: "private community", "gated community", or describe specific amenities' },
    { pattern: /\b(walking\s+distance\s+to\s+(church|temple|mosque|synagogue))\b/gi, reason: 'Religious institution proximity implies preference', fix: 'Remove or list as general "nearby places of worship"' },
    { pattern: /\b(ethnic\s+\w+|oriental|ghetto|barrio)\b/gi, reason: 'Racially/ethnically charged language', fix: 'Remove entirely — use neighborhood proper name only' }
  ],
  low: [
    // Subtle bias indicators — flag for human review
    { pattern: /\b(safe\s+neighborhood|low\s+crime|secure\s+area)\b/gi, reason: 'May imply other areas are unsafe (potential steering)', fix: 'Describe specific security features (gated entry, security system) instead of area characterization' },
    { pattern: /\b(quiet\s+neighborhood|peaceful\s+community)\b/gi, reason: 'May be coded language for excluding families with children', fix: 'Consider rephrasing to describe property features: "serene backyard setting"' },
    { pattern: /\b(close\s+to\s+public\s+transit|near\s+bus\s+stop)\b/gi, reason: 'Low risk but flagged — transit proximity can be steering indicator in some contexts', fix: 'Generally acceptable — include only if genuinely a selling point' },
    { pattern: /\b(up[- ]and[- ]coming|gentrifying|transitional\s+neighborhood)\b/gi, reason: 'May imply racial/economic demographic shifts', fix: 'Describe specific improvements: "new restaurants and shops opening on Main Street"' }
  ]
};

function validateFairHousing(content) {
  const flags = [];
  let highestSeverity = 'clean';
  
  for (const [severity, phrases] of Object.entries(PROHIBITED_PHRASES)) {
    for (const phrase of phrases) {
      const matches = content.match(phrase.pattern);
      if (matches) {
        flags.push({
          severity: severity,
          matched_text: matches[0],
          reason: phrase.reason,
          fix: phrase.fix,
          position: content.indexOf(matches[0])
        });
        if (severity === 'high') highestSeverity = 'high';
        else if (severity === 'medium' && highestSeverity !== 'high') highestSeverity = 'medium';
        else if (severity === 'low' && highestSeverity === 'clean') highestSeverity = 'low';
      }
    }
  }
  
  return {
    is_clean: flags.length === 0,
    flag_count: flags.length,
    highest_severity: highestSeverity,
    flags: flags,
    recommendation: flags.length === 0 
      ? 'Content passed Fair Housing language check. Proceed to human review.'
      : highestSeverity === 'high'
        ? 'BLOCK: Content contains high-severity Fair Housing violations. Must be revised before any publication.'
        : highestSeverity === 'medium'
          ? 'REVISE: Content contains language that may violate Fair Housing Act. Review flagged phrases and apply suggested fixes.'
          : 'REVIEW: Content contains subtle language that warrants human review. May be acceptable in context.',
    validated_at: new Date().toISOString()
  };
}

// --- Zapier Code Step Integration ---
// Input Data: contentToValidate (from previous step)
const result = validateFairHousing(inputData.contentToValidate);
output = {
  is_clean: result.is_clean.toString(),
  flag_count: result.flag_count.toString(),
  highest_severity: result.highest_severity,
  recommendation: result.recommendation,
  flag_details: JSON.stringify(result.flags),
  validated_at: result.validated_at
};

Total content generated this month: =COUNTIF('Pending Review'!A:A,">="&DATE(YEAR(TODAY()),MONTH(TODAY()),1))
Pending review:                      =COUNTIF('Pending Review'!M:M,"PENDING")
Approved:                            =COUNTIF('Pending Review'!M:M,"APPROVED")
Rejected:                            =COUNTIF('Pending Review'!M:M,"REJECTED")
Fair Housing flags:                  =COUNTIF('Pending Review'!K:K,"FLAG*")+COUNTIF('Pending Review'!K:K,"FAIL*")

Subject: 'Action Required: AI Content Awaiting Your Review'
Body: 'You have {{row_count}} listing descriptions pending review in the Content Queue. Please review and approve/reject within 24 hours to maintain your posting schedule. [Link to Sheet]'