Implementation Guide: Transcribe showing feedback calls and aggregate buyer sentiment across properties

curl -X POST https://api.openai.com/v1/chat/completions \
  -H 'Content-Type: application/json' \
  -H 'Authorization: Bearer sk-YOUR_API_KEY' \
  -d '{
    "model": "gpt-5.4-mini",
    "response_format": { "type": "json_object" },
    "messages": [
      {
        "role": "system",
        "content": "You are a real estate showing feedback analyst. Analyze the following transcript of a post-showing feedback conversation and return a JSON object with these fields: overall_sentiment_score (float -1.0 to 1.0), sentiment_label (string: Very Negative/Negative/Neutral/Positive/Very Positive), purchase_likelihood (int 1-5), positive_themes (array of strings), negative_themes (array of strings), specific_objections (array of strings), price_feedback (string or null), condition_feedback (string or null), location_feedback (string or null), summary (string, 2-3 sentences)."
      },
      {
        "role": "user",
        "content": "Listing Agent: Hi Sarah, thanks for showing 123 Oak Street today. How did your buyers like it?\nBuyer Agent: They really loved the open floor plan and the updated kitchen. The natural light in the living room was a big hit. Their main concern was the backyard is smaller than they expected, and they mentioned the price feels a bit high compared to the comp on Maple Drive that sold last month. Overall though, they are interested and want to come back for a second showing."
      }
    ]
  }'

// Code by Zapier: Extract transcript text and meeting metadata from webhook
// payload

const data = JSON.parse(inputData.body);
return {
  transcript: data.transcript || data.sentences.map(s => s.text).join(' '),
  meeting_title: data.title || 'Unknown',
  date: data.date || new Date().toISOString(),
  duration: data.duration || 0,
  participants: (data.participants || []).join(', ')
};

// Code by Zapier: Parse OpenAI JSON response and extract fields

const result = JSON.parse(inputData.openai_response);
return {
  sentiment_score: result.overall_sentiment_score,
  sentiment_label: result.sentiment_label,
  purchase_likelihood: result.purchase_likelihood,
  positive_themes: (result.positive_themes || []).join('; '),
  negative_themes: (result.negative_themes || []).join('; '),
  objections: (result.specific_objections || []).join('; '),
  price_feedback: result.price_feedback || '',
  condition_feedback: result.condition_feedback || '',
  location_feedback: result.location_feedback || '',
  summary: result.summary,
  mls_number: result.mls_number || 'NEEDS_MANUAL_ENTRY'
};

# Webhooks by Zapier: POST request body to Follow Up Boss API
# (https://api.followupboss.com/v1/notes) with Authorization: Basic
# {{base64(api_key:)}}

{
  "personId": "(lookup or skip)",
  "subject": "Showing Feedback: {{Step5.mls_number}}",
  "body": "Sentiment: {{Step5.sentiment_label}} ({{Step5.sentiment_score}})\n\nSummary: {{Step5.summary}}\n\nPositives: {{Step5.positive_themes}}\n\nConcerns: {{Step5.negative_themes}}\n\nObjections: {{Step5.objections}}\n\nPrice Feedback: {{Step5.price_feedback}}\n\nPurchase Likelihood: {{Step5.purchase_likelihood}}/5"
}

curl -X GET https://api.followupboss.com/v1/people \
  -H 'Authorization: Basic BASE64_ENCODED_API_KEY' \
  -H 'Content-Type: application/json'

# FUB API uses HTTP Basic Auth where username = API key and password is blank
# Base64 encode:
echo -n 'YOUR_API_KEY:' | base64

curl -X POST https://api.followupboss.com/v1/notes \
  -H 'Authorization: Basic BASE64_ENCODED_API_KEY' \
  -H 'Content-Type: application/json' \
  -d '{
    "personId": 12345,
    "subject": "Showing Feedback: MLS# 12345678 - 123 Oak St",
    "body": "**Sentiment: Positive (0.72)**\n\n**Summary:** Buyers loved the open floor plan and updated kitchen. Concerned about backyard size and pricing relative to recent comps.\n\n**Positives:** Open floor plan, updated kitchen, natural light\n**Concerns:** Small backyard, price seems high\n**Purchase Likelihood:** 4/5\n\n_Recorded: 2025-01-15 | Duration: 8 min | Source: Phone (Fireflies)_"
  }'

curl -X GET 'https://api.followupboss.com/v1/people?q=Sarah+Johnson' \
  -H 'Authorization: Basic BASE64_ENCODED_API_KEY'

'Hi [name], before we discuss the showing, I want to let you know
 this call is being recorded to help us capture your feedback accurately.
 The recording will be transcribed and used internally by our brokerage.
 Is that okay with you?'

'I'd like to record our conversation about the showing so I can
 capture your feedback accurately. The recording is used internally
 by our team. Do I have your permission to record?'

You are an expert real estate showing feedback analyst working for a residential real estate brokerage. Your job is to analyze transcripts of conversations between listing agents and buyer's agents (or buyers directly) that occur after a property showing.

Analyze the provided transcript and return a JSON object with EXACTLY these fields:

{
  "mls_number": "string - Extract MLS number if mentioned, otherwise 'NEEDS_MANUAL_ENTRY'",
  "property_address": "string - Extract property address if mentioned, otherwise 'Unknown'",
  "overall_sentiment_score": "float between -1.0 (extremely negative) and 1.0 (extremely positive). 0.0 is neutral.",
  "sentiment_label": "string - exactly one of: 'Very Negative' | 'Negative' | 'Neutral' | 'Positive' | 'Very Positive'",
  "purchase_likelihood": "integer 1-5 where 1=No interest, 2=Unlikely, 3=Maybe, 4=Interested, 5=Very likely to make offer",
  "positive_themes": ["array of specific positive aspects mentioned, e.g. 'Updated kitchen', 'Natural light', 'Quiet street'"],
  "negative_themes": ["array of specific negative aspects mentioned, e.g. 'Small backyard', 'Outdated bathrooms', 'Street noise'"],
  "specific_objections": ["array of deal-breaker or significant concerns, e.g. 'Price too high vs. comps', 'Needs new roof', 'School district concern'"],
  "price_feedback": "string summarizing any comments about listing price, or null if not discussed",
  "condition_feedback": "string summarizing comments about property condition/repairs, or null if not discussed",
  "location_feedback": "string summarizing comments about location/neighborhood, or null if not discussed",
  "buyer_next_steps": "string - what the buyer indicated they want to do next (e.g. 'Second showing requested', 'Will submit offer', 'Not interested', 'Thinking about it')",
  "summary": "string - 2-3 sentence executive summary of the feedback suitable for a listing agent to read quickly"
}

Rules:
1. Base your analysis ONLY on what is explicitly stated in the transcript. Do not infer or fabricate information.
2. If the transcript is too short or unclear to determine a field, use null for optional fields or 'Unknown' for strings.
3. Sentiment scoring guide: casual positive comments = 0.3-0.5, enthusiastic praise = 0.6-0.9, mild concerns = -0.1 to -0.3, strong objections = -0.4 to -0.7, deal-killer negativity = -0.8 to -1.0. Most feedback calls land between -0.3 and 0.7.
4. Purchase likelihood guide: If buyer says they want to make an offer = 5. If they want a second showing = 4. If they say 'we'll think about it' = 3. If they have major concerns but didn't say no = 2. If they explicitly passed = 1.
5. For positive_themes and negative_themes, be specific — use the actual features mentioned, not generic categories.
6. NEVER include personally identifiable information about the buyers (names, financial details, family composition) in the themes or summary. Focus on property feedback only.
7. Return ONLY the JSON object, no additional text.

// MLS Property Matcher
// Attempts to find which property the feedback is about by searching the transcript

const transcript = (inputData.transcript || '').toLowerCase();
let properties = [];

try {
  properties = JSON.parse(inputData.properties_json || '[]');
} catch(e) {
  // If properties JSON fails to parse, return unknown
  return { mls_number: 'NEEDS_MANUAL_ENTRY', property_address: 'Unknown', confidence: 'none' };
}

let bestMatch = null;
let bestScore = 0;

for (const prop of properties) {
  let score = 0;
  
  // Check for MLS number match (highest confidence)
  if (prop.mls_number && transcript.includes(prop.mls_number.toLowerCase())) {
    score += 100;
  }
  
  // Check for street number + street name match
  if (prop.address) {
    const addressParts = prop.address.toLowerCase().split(/[\s,]+/);
    const streetNumber = addressParts[0];
    const streetName = addressParts.slice(1, 3).join(' ');
    
    if (streetNumber && transcript.includes(streetNumber)) {
      score += 20;
    }
    if (streetName && streetName.length > 3 && transcript.includes(streetName)) {
      score += 40;
    }
    // Full address match
    if (transcript.includes(prop.address.toLowerCase())) {
      score += 80;
    }
  }
  
  // Check for subdivision/community name
  if (prop.subdivision && prop.subdivision.length > 3 && transcript.includes(prop.subdivision.toLowerCase())) {
    score += 15;
  }
  
  if (score > bestScore) {
    bestScore = score;
    bestMatch = prop;
  }
}

if (bestMatch && bestScore >= 40) {
  return {
    mls_number: bestMatch.mls_number,
    property_address: bestMatch.address,
    list_price: bestMatch.list_price || '',
    listing_agent: bestMatch.listing_agent || '',
    confidence: bestScore >= 80 ? 'high' : 'medium'
  };
} else {
  return {
    mls_number: 'NEEDS_MANUAL_ENTRY',
    property_address: 'Unknown',
    list_price: '',
    listing_agent: '',
    confidence: 'none'
  };
}

You are a real estate market intelligence analyst. Generate a concise weekly showing feedback digest email for a real estate brokerage's management team.

You will receive a JSON array of this week's showing feedback records. Each record contains: mls_number, property_address, list_price, listing_agent, overall_sentiment_score, sentiment_label, purchase_likelihood, positive_themes, negative_themes, specific_objections, price_feedback, buyer_next_steps.

Generate an email body (plain text with markdown formatting) with these sections:

## 📊 Weekly ShowingInsight Digest — [date range]

### Summary
- Total feedback sessions this week: [count]
- Average sentiment: [score] ([label])
- Average purchase likelihood: [X]/5
- Properties with feedback: [count]

### 🏆 Top Performing Properties (highest avg sentiment)
List top 3 properties by average sentiment with their address, sentiment score, and what buyers loved.

### ⚠️ Properties Needing Attention (lowest avg sentiment)
List bottom 3 properties by average sentiment with their address, sentiment score, and common objections.

### 💰 Price Feedback Alerts
List any properties where buyers specifically commented on price being too high, with the exact feedback.

### 📋 Most Common Objections This Week
Ranked list of the top 5 most frequently mentioned objections across all properties.

### 🎯 Recommended Actions
Based on the data, suggest 2-3 specific actions (e.g., 'Consider price reduction on [address]', 'Schedule maintenance for [issue] at [address]').

### 📈 Offer Pipeline
List any feedback sessions where purchase_likelihood was 4 or 5, with the property and buyer's next steps.

Keep the tone professional but conversational. Use actual data from the records provided. If fewer than 3 properties in a category, list whatever is available.

const transcript = (inputData.transcript || '').toLowerCase();
const first500 = transcript.substring(0, 500);

const consentPhrases = [
  'recorded', 'recording', 'is that okay', 'permission to record',
  'do you consent', 'being recorded', 'record this call',
  'okay with you', 'mind if i record', 'agree to be recorded'
];

let consentDetected = false;
let matchedPhrase = '';

for (const phrase of consentPhrases) {
  if (first500.includes(phrase)) {
    consentDetected = true;
    matchedPhrase = phrase;
    break;
  }
}

return {
  consent_detected: consentDetected,
  consent_phrase_matched: matchedPhrase,
  requires_manual_review: !consentDetected
};

// aggregatePropertySentiment() and createDailyTrigger()

function aggregatePropertySentiment() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const feedbackSheet = ss.getSheetByName('Feedback Records');
  const propertiesSheet = ss.getSheetByName('Properties');
  
  if (!feedbackSheet || !propertiesSheet) {
    Logger.log('Error: Required sheets not found');
    return;
  }
  
  // Get all feedback data (skip header row)
  const feedbackData = feedbackSheet.getDataRange().getValues();
  const headers = feedbackData[0];
  
  // Find column indices
  const mlsCol = headers.indexOf('MLS Number');
  const addressCol = headers.indexOf('Property Address');
  const priceCol = headers.indexOf('List Price');
  const agentCol = headers.indexOf('Listing Agent');
  const sentimentCol = headers.indexOf('Overall Sentiment Score');
  const likelihoodCol = headers.indexOf('Purchase Likelihood');
  const dateCol = headers.indexOf('Date');
  const negThemesCol = headers.indexOf('Key Negative Themes');
  
  if (mlsCol === -1 || sentimentCol === -1) {
    Logger.log('Error: Required columns not found');
    return;
  }
  
  // Aggregate by MLS number
  const propertyMap = {};
  
  for (let i = 1; i < feedbackData.length; i++) {
    const row = feedbackData[i];
    const mls = String(row[mlsCol]).trim();
    
    if (!mls || mls === '' || mls === 'NEEDS_MANUAL_ENTRY') continue;
    
    if (!propertyMap[mls]) {
      propertyMap[mls] = {
        address: row[addressCol] || '',
        listPrice: row[priceCol] || '',
        listingAgent: row[agentCol] || '',
        sentimentScores: [],
        likelihoodScores: [],
        dates: [],
        objections: []
      };
    }
    
    const sentiment = parseFloat(row[sentimentCol]);
    if (!isNaN(sentiment)) {
      propertyMap[mls].sentimentScores.push(sentiment);
    }
    
    const likelihood = parseInt(row[likelihoodCol]);
    if (!isNaN(likelihood)) {
      propertyMap[mls].likelihoodScores.push(likelihood);
    }
    
    if (row[dateCol]) {
      propertyMap[mls].dates.push(new Date(row[dateCol]));
    }
    
    if (row[negThemesCol]) {
      propertyMap[mls].objections.push(String(row[negThemesCol]));
    }
  }
  
  // Calculate aggregates and write to Properties sheet
  // Clear existing data (keep header)
  const lastRow = propertiesSheet.getLastRow();
  if (lastRow > 1) {
    propertiesSheet.getRange(2, 1, lastRow - 1, 10).clearContent();
  }
  
  const outputRows = [];
  
  for (const [mls, data] of Object.entries(propertyMap)) {
    const avgSentiment = data.sentimentScores.length > 0
      ? (data.sentimentScores.reduce((a, b) => a + b, 0) / data.sentimentScores.length).toFixed(3)
      : 'N/A';
    
    const avgLikelihood = data.likelihoodScores.length > 0
      ? (data.likelihoodScores.reduce((a, b) => a + b, 0) / data.likelihoodScores.length).toFixed(1)
      : 'N/A';
    
    // Calculate trend (last 3 vs previous 3)
    let trend = 'Stable';
    if (data.sentimentScores.length >= 6) {
      const recent3 = data.sentimentScores.slice(-3).reduce((a, b) => a + b, 0) / 3;
      const prev3 = data.sentimentScores.slice(-6, -3).reduce((a, b) => a + b, 0) / 3;
      if (recent3 > prev3 + 0.1) trend = '📈 Improving';
      else if (recent3 < prev3 - 0.1) trend = '📉 Declining';
    }
    
    // Most recent feedback date
    const lastFeedback = data.dates.length > 0
      ? new Date(Math.max(...data.dates.map(d => d.getTime())))
      : '';
    
    // Top objection (most frequent)
    const objectionCounts = {};
    data.objections.forEach(obj => {
      obj.split(';').forEach(o => {
        const trimmed = o.trim().toLowerCase();
        if (trimmed) objectionCounts[trimmed] = (objectionCounts[trimmed] || 0) + 1;
      });
    });
    const topObjection = Object.entries(objectionCounts)
      .sort((a, b) => b[1] - a[1])[0];
    
    outputRows.push([
      mls,
      data.address,
      data.listPrice,
      data.listingAgent,
      data.sentimentScores.length,  // Total feedback count
      avgSentiment,
      avgLikelihood,
      trend,
      topObjection ? topObjection[0] : '',
      lastFeedback ? Utilities.formatDate(lastFeedback, Session.getScriptTimeZone(), 'yyyy-MM-dd') : ''
    ]);
  }
  
  if (outputRows.length > 0) {
    propertiesSheet.getRange(2, 1, outputRows.length, 10).setValues(outputRows);
  }
  
  Logger.log('Aggregation complete. ' + outputRows.length + ' properties updated.');
}

// Set up daily trigger
function createDailyTrigger() {
  // Delete existing triggers to avoid duplicates
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'aggregatePropertySentiment') {
      ScriptApp.deleteTrigger(trigger);
    }
  });
  
  // Create new daily trigger at 6 AM
  ScriptApp.newTrigger('aggregatePropertySentiment')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
  
  Logger.log('Daily trigger created for 6 AM.');
}