What This Is
This Google Sheet + App Script combo is a done-with-you email generator that builds local real estate market reports—automatically, for the respective ZIP/postal codes of every (or any) contact in your CRM.
Once you import your contacts and MLS data, the App Script runs behind the scenes to segment your list by area, pull the matching market data, and generate localized market report emails using Claude, formatted in email-safe HTML with inline CSS and table-based layout (that means the emails have a clean, styled design—with colors, sections, spacing, and formatting that look great in major inboxes like Gmail and Outlook). The finished emails are ready to paste directly into Mailchimp, Constant Contact, or your email marketing platform of choice.
For each ZIP code, it writes a custom market report based on actual housing activity—like pricing trends, inventory shifts, days on market, and more. It also creates a general version for contacts without ZIPs.
The App Script creates two new tabs (worksheets) in your Google Sheet:
- My HTML – your email content, ready to copy/paste into Mailchimp, Constant Contact, or Zapier. Just double-click the cell, copy the code, and paste it into your email campaign.
- Tagged Recipients – a contact list organized by ZIP code, ready to import into your email platform. Each contact is tagged (e.g., code-37064) so you can easily send the right market report to the right people—no extra sorting needed.
How To Use It
You don’t need to code—just follow these steps carefully, and you’ll be up and running in a few minutes.
- Make a copy of the Google Sheet
Click below to copy the template into your own Google Drive.
👉 Click here to make a copy
🔐 Make sure you’re logged into the Google Account where you want the file saved. - Import your CRM contacts and MLS data
You’ll need two .CSV files—one for contacts and one for MLS data.- Open the correct tab in the Google Sheet (CRM Export or MLS Export)
- Go to File > Import > Upload
- Select your .CSV file
- Under Import location, choose Replace current sheet
- Click Import data
Repeat this 👆 for both tabs.
💡 Note: The exact steps to get your contact and MLS data will depend on the platforms you use.- For your CRM, look for an “Export” or “Download Contacts” option and save it as a .CSV.
- For your MLS, export listings from a saved search or custom report—again, download it as a .CSV file. If you’re unsure how to export from your systems, check their help docs or contact your provider. Once you have both files, you’re ready to keep going.
- Open the Apps Script editor
In the top menu, go to Extensions > Apps Script - Add the custom script
- Delete any existing code in the editor
- Paste the full script into the code editor from the copyable box below.
- Click the 💾 floppy disk icon in the toolbar to save
- Connect your Claude API key
This allows the script to talk to Claude and generate your email content.- On the lefthand sidebar, click the ⚙️ gear icon (Project Settings)
- Scroll down to Script Properties
- Under Property, enter:
ANTHROPIC_CLAUDE - Under Value, paste your unique Claude API key
⚠️ Important: You’ll need an API key from console.anthropic.com, which is separate from the regular Claude chat experience. This is Claude’s developer platform, where you can create API keys and manage billing. It’s what lets this script communicate with Claude behind the scenes.
To set it up:- Go to console.anthropic.com
- Log in (or create an account)
- Set up a billing method (you’ll need to purchase API credits—most users spend just a few dollars.)
- In the left-hand menu, click API Keys
- Click Create Key, give it a name, and copy it
- Paste the key into the Value field in your Script Properties
🔒 Keep your API key private. It works like a password—don’t share it.
- Fill out your preferences
In the Agent Info Google Sheet tab, customize:- Your name and contact info
- Your market area (e.g., “Raleigh, NC” or “Florida Space Coast”)
- Optional: any brand colors, fonts, or stylistic notes
- Assign the script to the button
- Click the graphic button on the Sheet
- Click the three dots in the corner > Assign script
- Type in:
generateAllEmails - Click OK
- Click the button to generate your emails
Once the script runs, it’ll automatically create two new tabs in your Sheet—My HTML and Tagged Recipients—so you don’t need to create anything manually.- My HTML → Contains styled email content by ZIP/postal code. Just double-click any cell to copy the code and paste it into Mailchimp, Constant Contact, or your mass email platform.
- Tagged Recipients → A clean contact list with ZIP/postal tags (e.g., code-37064). Export this tab as a .CSV and import it into your email platform to send the right market report to the right people.
To export the contact list:- Go to the Tagged Recipients tab
- In the top menu, click File > Download > Comma-separated values (.csv)
- This action 👆 saves the file to your computer
- Import it into your email platform (e.g., Mailchimp, Constant Contact)
- Use the Tag column (like code-37064) to segment your list and send the correct market report to each group
DISCLAIMER: This resource provides prompts, instructions, and content to help professionals use AI tools more effectively. Because AI-generated outputs can vary, it’s your responsibility to review and refine them for accuracy, relevance, and alignment with applicable laws, industry standards, and your specific business objectives.
/**
* Real Estate Market Report Generator - Refactored for Efficiency
*/
/**
* Accepted sheet‑name aliases so users can label tabs their own way
*/
const SHEET_ALIASES = {
crm: ['CRM Export', 'CRM Data'],
mls: ['MLS Export', 'MLS Data'],
agent: ['Agent Info', 'My Data']
};
// 1. FULL RUN - Generate all emails (assign to "RUN SCRIPT" button)
function generateAllEmails() {
try {
const { contactSegments, fieldMapping, brandingData, senderInfo } = initializeData();
const estimation = estimateApiCosts(contactSegments);
logGenerationInfo(estimation);
const emailTemplates = [];
let currentCall = 0;
const totalCalls = contactSegments.sellerSegments.length + (contactSegments.generalSegment.contacts.length > 0 ? 1 : 0);
// Generate seller emails
for (const segment of contactSegments.sellerSegments) {
currentCall++;
try {
if (currentCall > 1) {
Utilities.sleep(30000);
}
const result = generateSingleEmail(segment, fieldMapping, brandingData, senderInfo, 'seller');
if (result) {
emailTemplates.push(result);
}
} catch (error) {
console.error(`✗ Failed to generate email for ${segment.postalCode}:`, error.message);
}
}
// Generate general newsletter
if (contactSegments.generalSegment.contacts.length > 0) {
currentCall++;
try {
const result = generateSingleEmail(contactSegments.generalSegment, fieldMapping, brandingData, senderInfo, 'general');
if (result) {
emailTemplates.push(result);
}
} catch (error) {
console.error('✗ Failed to generate general newsletter:', error.message);
}
}
if (emailTemplates.length > 0) {
createOutputSheet(emailTemplates);
createTaggedRecipientsSheet(emailTemplates);
}
} catch (error) {
}
}
// 2. TEST SINGLE ZIP EMAIL - Uses first ZIP code found in data
function testSingleZipEmail() {
try {
const { contactSegments, fieldMapping, brandingData, senderInfo } = initializeData();
if (contactSegments.sellerSegments.length === 0) {
}
const firstSegment = contactSegments.sellerSegments[0];
const result = generateSingleEmail(firstSegment, fieldMapping, brandingData, senderInfo, 'seller');
if (result) {
createTestOutput(result.postalCode, 'Test Seller Email', result.htmlContent, result.contactCount);
}
} catch (error) {
}
}
// 3. TEST GENERAL NEWSLETTER - For contacts without ZIP codes
function testGeneralNewsletter() {
try {
const { contactSegments, fieldMapping, brandingData, senderInfo } = initializeData();
if (contactSegments.generalSegment.contacts.length === 0) {
}
const result = generateSingleEmail(contactSegments.generalSegment, fieldMapping, brandingData, senderInfo, 'general');
if (result) {
createTestOutput('ALL_AREAS', 'Test General Newsletter', result.htmlContent, result.contactCount);
}
} catch (error) {
}
}
function checkSetup() {
try {
const apiKey = PropertiesService.getScriptProperties().getProperty('ANTHROPIC_CLAUDE');
const { crmSheet, mlsSheet, myDataSheet } = getRequiredSheets();
if (crmSheet && mlsSheet && myDataSheet) {
const { contactSegments } = initializeData();
const estimation = estimateApiCosts(contactSegments);
logDataAnalysis(contactSegments, estimation);
}
} catch (error) {
}
}
function initializeData() {
validateRequiredTabs();
extractPostalCodes();
const contactSegments = segmentContacts();
const fieldMapping = identifyMLSFields();
const brandingData = getBrandingData();
const senderInfo = getSenderInfo();
return { contactSegments, fieldMapping, brandingData, senderInfo };
}
function generateSingleEmail(contactSegment, fieldMapping, brandingData, senderInfo, emailType) {
let postalCode;
if (emailType === 'seller') {
postalCode = contactSegment.postalCode;
} else {
// For general newsletter, create area name from all ZIP codes
const { contactSegments } = getContactSegmentsForAreaName();
postalCode = createAreaName(contactSegments.sellerSegments);
}
// Get market data
const marketData = emailType === 'seller'
? getMarketDataForPostalCode(contactSegment.postalCode, fieldMapping)
: getAllMarketData(fieldMapping);
if (!marketData || marketData.length === 0) {
console.log(`❌ No market data found for ${postalCode}`);
return null;
}
// Pre-calculate market insights for Claude
const marketInsights = calculateMarketInsights(marketData, fieldMapping);
// Generate email content with enhanced data
const htmlContent = generateEmailContent(contactSegment, marketData, marketInsights, senderInfo, brandingData, emailType);
return {
postalCode: postalCode,
emailType: emailType === 'seller' ? 'Seller-Focused' : 'General Newsletter',
htmlContent: htmlContent,
contactCount: contactSegment.contacts.length,
contacts: contactSegment.contacts
};
}
function getContactSegmentsForAreaName() {
// Re-segment contacts to get all ZIP codes for area naming
return { contactSegments: segmentContacts() };
}
function createAreaName(sellerSegments) {
if (!sellerSegments || sellerSegments.length === 0) return 'LOCAL MARKET';
const zipCodes = sellerSegments
.map(seg => seg.postalCode)
.filter(Boolean)
.sort();
if (zipCodes.length === 1) return `${zipCodes[0]} Market`;
if (zipCodes.length <= 3) return `${zipCodes.join(', ')} Market`;
return 'Regional Market';
}
function validateRequiredTabs() {
const { crmSheet, mlsSheet, myDataSheet } = getRequiredSheets();
if (!crmSheet) throw new Error('CRM Export (or CRM Data) tab is missing');
if (!mlsSheet || mlsSheet.getLastRow() <= 1) throw new Error('MLS Export (or MLS Data) tab is missing or empty');
if (!myDataSheet) throw new Error('Agent Info (or My Data) tab is missing');
}
function getRequiredSheets() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const findSheet = names => names.map(n => ss.getSheetByName(n)).find(s => s);
return {
crmSheet: findSheet(SHEET_ALIASES.crm),
mlsSheet: findSheet(SHEET_ALIASES.mls),
myDataSheet:findSheet(SHEET_ALIASES.agent)
};
}
function extractPostalCodes() {
const { crmSheet } = getRequiredSheets();
const data = crmSheet.getDataRange().getValues();
// Ensure postal code column exists
if (data[0].length < 5 || data[0][4] !== 'Postal Code') {
crmSheet.getRange(1, 5).setValue('Postal Code');
}
const patterns = getPostalCodePatterns();
for (let i = 1; i < data.length; i++) {
const address = data[i][2]; // Column C (Home Address)
if (address) {
const postalCode = extractPostalCodeFromAddress(address.toString(), patterns);
if (postalCode) {
crmSheet.getRange(i + 1, 5).setValue(postalCode);
}
}
}
}
function getPostalCodePatterns() {
return {
us: /\b\d{5}(-\d{4})?\b(?=\s|$)/, // Must be followed by space or end of string
canada: /\b[A-Z]\d[A-Z]\s?\d[A-Z]\d\b/i,
uk: /\b[A-Z]{1,2}\d[A-Z\d]?\s?\d[A-Z]{2}\b/i,
europe: /\b\d{5}\b(?=\s|$)/, // Must be followed by space or end of string
mexico: /\b\d{5}\b(?=\s|$)/ // Must be followed by space or end of string
};
}
function extractPostalCodeFromAddress(address, patterns) {
// Look for postal codes at the END of the address (most common location)
const addressParts = address.split(' ');
// Check last few words first (ZIP codes usually at end)
for (let i = Math.max(0, addressParts.length - 3); i < addressParts.length; i++) {
const word = addressParts[i];
for (let country in patterns) {
const match = word.match(patterns[country]);
if (match) {
return match[0].trim();
}
}
}
// If not found at end, check entire address but be more strict
for (let country in patterns) {
const match = address.match(patterns[country]);
if (match) {
const matchedText = match[0].trim();
// Additional validation: must not be part of street number
if (!isPartOfStreetAddress(matchedText, address)) {
return matchedText;
}
}
}
return null;
}
function isPartOfStreetAddress(potentialZip, fullAddress) {
// Check if this number appears early in the address (likely street number)
const zipIndex = fullAddress.indexOf(potentialZip);
const beforeZip = fullAddress.substring(0, zipIndex).trim();
// If ZIP appears in first 20 characters, it's probably a street number
if (zipIndex < 20) return true;
// If there are common street words before it, it's probably a street number
const streetWords = /\b(apt|apartment|unit|suite|#|street|st|avenue|ave|road|rd|drive|dr|lane|ln|way|court|ct|circle|cir|place|pl)\b/i;
if (beforeZip.length < 50 && !streetWords.test(beforeZip)) {
return true; // Short address without street indicators - likely street number
}
return false;
}
function segmentContacts() {
const { crmSheet } = getRequiredSheets();
const data = crmSheet.getDataRange().getValues();
const sellerSegments = [];
const generalContacts = [];
const postalCodeGroups = {};
for (let i = 1; i < data.length; i++) {
const contact = {
firstName: data[i][0],
lastName: data[i][1],
homeAddress: data[i][2],
email: data[i][3],
postalCode: data[i][4]
};
if (contact.homeAddress && contact.postalCode) {
if (!postalCodeGroups[contact.postalCode]) {
postalCodeGroups[contact.postalCode] = [];
}
postalCodeGroups[contact.postalCode].push(contact);
} else {
generalContacts.push(contact);
}
}
for (const postalCode in postalCodeGroups) {
sellerSegments.push({
postalCode: postalCode,
contacts: postalCodeGroups[postalCode]
});
}
return {
sellerSegments: sellerSegments,
generalSegment: { contacts: generalContacts }
};
}
function identifyMLSFields() {
const { mlsSheet } = getRequiredSheets();
const headers = mlsSheet.getRange(1, 1, 1, mlsSheet.getLastColumn()).getValues()[0];
const fieldMapping = {
zipField: headers.findIndex(h => h && /zip|postal/i.test(h.toString())),
priceFields: [],
dateFields: [],
statusField: headers.findIndex(h => h && /status|state/i.test(h.toString())),
sqftField: headers.findIndex(h => h && /sqft|area|size|total/i.test(h.toString())),
addressField: headers.findIndex(h => h && /address|street/i.test(h.toString())),
cityField: headers.findIndex(h => h && /city/i.test(h.toString())),
bedroomField: headers.findIndex(h => h && /bedroom/i.test(h.toString())),
bathroomField: headers.findIndex(h => h && /bath/i.test(h.toString())),
headers: headers
};
headers.forEach((header, index) => {
if (header) {
if (/price|amount/i.test(header.toString())) {
fieldMapping.priceFields.push({name: header, index: index});
}
if (/date|time/i.test(header.toString())) {
fieldMapping.dateFields.push({name: header, index: index});
}
}
});
return fieldMapping;
}
function getMarketDataForPostalCode(postalCode, fieldMapping) {
const { mlsSheet } = getRequiredSheets();
const data = mlsSheet.getDataRange().getValues();
if (fieldMapping.zipField === -1) {
return [];
}
const relevantData = [];
for (let i = 1; i < data.length; i++) {
const rowZip = data[i][fieldMapping.zipField];
if (rowZip && rowZip.toString() === postalCode.toString()) {
const rowData = {};
fieldMapping.headers.forEach((header, index) => {
rowData[header] = data[i][index];
});
relevantData.push(rowData);
}
}
return relevantData;
}
function getAllMarketData(fieldMapping) {
const { mlsSheet } = getRequiredSheets();
const data = mlsSheet.getDataRange().getValues();
const allData = [];
for (let i = 1; i < data.length; i++) {
const rowData = {};
fieldMapping.headers.forEach((header, index) => {
rowData[header] = data[i][index];
});
allData.push(rowData);
}
return allData;
}
function getBrandingData() {
const { myDataSheet } = getRequiredSheets();
const data = myDataSheet.getDataRange().getValues();
const brandingData = {};
for (let i = 0; i < data.length; i++) {
if (data[i][0] && data[i][1]) {
brandingData[data[i][0]] = data[i][1];
}
}
return brandingData;
}
function getSenderInfo() {
const { myDataSheet } = getRequiredSheets();
const data = myDataSheet.getDataRange().getValues();
const senderInfo = {};
for (let i = 0; i < data.length; i++) {
if (data[i][0] && data[i][1]) {
senderInfo[data[i][0]] = data[i][1];
}
}
return senderInfo;
}
function calculateMarketInsights(marketData, fieldMapping) {
try {
const datasetSize = marketData.length;
// Validate dataset size
if (datasetSize < 3) {
return {
totalProperties: datasetSize,
dataQuality: 'insufficient',
message: 'Limited data available - insights may not be statistically significant',
basicStats: getBasicStats(marketData, fieldMapping)
};
}
const insights = {
totalProperties: datasetSize,
dataQuality: getDataQuality(datasetSize),
priceAnalysis: calculatePriceAnalysis(marketData, fieldMapping, datasetSize),
timeOnMarket: calculateTimeOnMarket(marketData, fieldMapping, datasetSize),
inventoryAnalysis: calculateInventoryAnalysis(marketData, fieldMapping, datasetSize),
propertyTypeAnalysis: calculatePropertyTypeAnalysis(marketData, fieldMapping, datasetSize),
seasonalTrends: calculateSeasonalTrends(marketData, fieldMapping, datasetSize),
competitiveMetrics: calculateCompetitiveMetrics(marketData, fieldMapping, datasetSize)
};
return insights;
} catch (error) {
return {
totalProperties: marketData.length,
dataQuality: 'error',
message: 'Unable to calculate detailed insights - using basic data only',
basicStats: getBasicStats(marketData, fieldMapping)
};
}
}
function getDataQuality(size) {
if (size >= 50) return 'excellent';
if (size >= 20) return 'good';
if (size >= 10) return 'fair';
return 'limited';
}
function getBasicStats(marketData, fieldMapping) {
const prices = [];
marketData.forEach(record => {
const price = getNumericValue(record, ['ListPrice', 'List Price', 'SalesPrice', 'Sales Price']);
if (price) prices.push(price);
});
return {
propertyCount: marketData.length,
averagePrice: prices.length > 0 ? calculateAverage(prices) : null,
priceRange: prices.length > 0 ? { min: Math.min(...prices), max: Math.max(...prices) } : null
};
}
function calculatePriceAnalysis(marketData, fieldMapping, datasetSize) {
try {
const prices = [];
const pricePerSqFt = [];
// Optimized single-pass data extraction
marketData.forEach(record => {
const listPrice = getNumericValue(record, ['ListPrice', 'List Price', 'Price']);
const salesPrice = getNumericValue(record, ['SalesPrice', 'Sales Price', 'Sold Price', 'Sale Price']);
const sqft = getNumericValue(record, ['SqFtTotal', 'Square Feet', 'Sq Ft', 'Total Sq Ft']);
// Validate price ranges (adjust based on market area)
const validPrice = (price) => price >= 50000 && price <= 10000000;
if (listPrice && validPrice(listPrice)) prices.push(listPrice);
if (salesPrice && validPrice(salesPrice)) prices.push(salesPrice);
// Calculate price per sq ft with validation
const effectivePrice = salesPrice || listPrice;
if (effectivePrice && sqft && sqft >= 500 && sqft <= 20000 && validPrice(effectivePrice)) {
const psf = effectivePrice / sqft;
if (psf >= 50 && psf <= 1000) { // Reasonable PSF range
pricePerSqFt.push(psf);
}
}
});
if (prices.length === 0) {
return { message: 'No valid price data available' };
}
const result = {
averagePrice: calculateAverage(prices),
medianPrice: calculateMedian(prices),
priceRange: { min: Math.min(...prices), max: Math.max(...prices) },
sampleSize: prices.length,
confidence: getConfidenceLevel(prices.length, datasetSize)
};
if (pricePerSqFt.length >= 3) {
result.averagePricePerSqFt = Math.round(calculateAverage(pricePerSqFt));
result.psfSampleSize = pricePerSqFt.length;
}
if (datasetSize >= 10) {
result.priceDistribution = calculatePriceDistribution(prices);
}
return result;
} catch (error) {
return { message: 'Price analysis unavailable due to data issues' };
}
}
function calculateTimeOnMarket(marketData, fieldMapping) {
const daysOnMarket = [];
marketData.forEach(record => {
const dom = getNumericValue(record, ['DaysOnMarket', 'Days On Market', 'DOM']);
if (dom && dom > 0 && dom < 365) { // Filter out unrealistic values
daysOnMarket.push(dom);
}
});
if (daysOnMarket.length === 0) return null;
return {
averageDays: Math.round(calculateAverage(daysOnMarket)),
medianDays: calculateMedian(daysOnMarket),
quickSales: daysOnMarket.filter(d => d <= 14).length,
totalSales: daysOnMarket.length,
quickSalePercentage: Math.round((daysOnMarket.filter(d => d <= 14).length / daysOnMarket.length) * 100)
};
}
function calculateInventoryAnalysis(marketData, fieldMapping, datasetSize) {
try {
const statusCounts = {};
const activeListings = [];
const soldListings = [];
let statusDataCount = 0;
marketData.forEach(record => {
const status = getStringValue(record, ['ListingStatus', 'Status', 'Property Status']);
if (status) {
statusDataCount++;
const normalizedStatus = status.toLowerCase();
statusCounts[normalizedStatus] = (statusCounts[normalizedStatus] || 0) + 1;
if (normalizedStatus.includes('active') || normalizedStatus.includes('pending')) {
activeListings.push(record);
} else if (normalizedStatus.includes('sold') || normalizedStatus.includes('closed')) {
soldListings.push(record);
}
}
});
if (statusDataCount < 3) {
return { message: 'Insufficient status data for inventory analysis' };
}
// Calculate absorption rate (estimate based on available data)
const monthlyAbsorption = soldListings.length > 0 ? Math.max(1, Math.round(soldListings.length / 3)) : 1;
const monthsOfInventory = activeListings.length > 0 ? Math.round(activeListings.length / monthlyAbsorption * 10) / 10 : 0;
return {
activeCount: activeListings.length,
soldCount: soldListings.length,
monthsOfInventory: monthsOfInventory,
marketBalance: getMarketBalance(monthsOfInventory),
statusBreakdown: statusCounts,
statusDataCount: statusDataCount,
confidence: getConfidenceLevel(statusDataCount, datasetSize)
};
} catch (error) {
return { message: 'Inventory analysis unavailable due to data issues' };
}
}
function calculatePropertyTypeAnalysis(marketData, fieldMapping, datasetSize) {
try {
const bedrooms = [];
const bathrooms = [];
const sqftRanges = {};
let validPropertyCount = 0;
marketData.forEach(record => {
const beds = getNumericValue(record, ['TotalBedrooms', 'Bedrooms', 'Beds']);
const baths = getNumericValue(record, ['TotalFullBaths', 'Bathrooms', 'Baths', 'Full Baths']);
const sqft = getNumericValue(record, ['SqFtTotal', 'Square Feet', 'Sq Ft']);
// Validate realistic property specs
if (beds && beds >= 1 && beds <= 10) {
bedrooms.push(beds);
validPropertyCount++;
}
if (baths && baths >= 1 && baths <= 10) {
bathrooms.push(baths);
}
if (sqft && sqft >= 500 && sqft <= 20000) {
const range = getSqftRange(sqft);
sqftRanges[range] = (sqftRanges[range] || 0) + 1;
}
});
if (validPropertyCount < 3) {
return { message: 'Insufficient property configuration data for analysis' };
}
const result = {
sampleSize: validPropertyCount,
confidence: getConfidenceLevel(validPropertyCount, datasetSize)
};
if (bedrooms.length >= 3) {
result.averageBedrooms = Math.round(calculateAverage(bedrooms) * 10) / 10;
}
if (bathrooms.length >= 3) {
result.averageBathrooms = Math.round(calculateAverage(bathrooms) * 10) / 10;
}
if (Object.keys(sqftRanges).length > 0) {
result.sqftDistribution = sqftRanges;
}
if (bedrooms.length >= 3 && bathrooms.length >= 3) {
result.commonConfiguration = getMostCommonConfig(bedrooms, bathrooms);
}
return result;
} catch (error) {
return { message: 'Property type analysis unavailable due to data issues' };
}
}
function calculateSeasonalTrends(marketData, fieldMapping) {
const listingsByMonth = {};
const salesByMonth = {};
marketData.forEach(record => {
// Check list dates
const listDate = getDateValue(record, ['ListDate', 'List Date', 'Listed Date']);
if (listDate) {
const month = listDate.getMonth() + 1; // 1-12
listingsByMonth[month] = (listingsByMonth[month] || 0) + 1;
}
// Check sales dates
const soldDate = getDateValue(record, ['ClosedDate', 'Sold Date', 'Sale Date']);
if (soldDate) {
const month = soldDate.getMonth() + 1; // 1-12
salesByMonth[month] = (salesByMonth[month] || 0) + 1;
}
});
return {
listingTrends: listingsByMonth,
salesTrends: salesByMonth,
peakListingMonth: getMonthWithMostActivity(listingsByMonth),
peakSalesMonth: getMonthWithMostActivity(salesByMonth)
};
}
function calculateCompetitiveMetrics(marketData, fieldMapping, datasetSize) {
try {
const listToSaleRatios = [];
const priceReductions = [];
let ratioCount = 0;
let reductionCount = 0;
marketData.forEach(record => {
const listPrice = getNumericValue(record, ['ListPrice', 'List Price']);
const salesPrice = getNumericValue(record, ['SalesPrice', 'Sales Price', 'Sold Price']);
const originalPrice = getNumericValue(record, ['OriginalListPrice', 'Original Price']);
// Calculate list-to-sale ratio with validation
if (listPrice && salesPrice && listPrice >= 50000 && salesPrice >= 50000) {
const ratio = (salesPrice / listPrice) * 100;
if (ratio >= 70 && ratio <= 130) { // Reasonable ratio range
listToSaleRatios.push(ratio);
ratioCount++;
}
}
// Calculate price reductions with validation
if (originalPrice && listPrice && originalPrice !== listPrice && originalPrice >= 50000) {
const reduction = ((originalPrice - listPrice) / originalPrice) * 100;
if (reduction > 0 && reduction <= 30) { // Reasonable reduction range
priceReductions.push(reduction);
reductionCount++;
}
}
});
const result = {};
if (ratioCount >= 3) {
const avgRatio = Math.round(calculateAverage(listToSaleRatios) * 10) / 10;
result.averageListToSaleRatio = avgRatio;
result.ratioSampleSize = ratioCount;
result.competitivePressure = getCompetitivePressure(listToSaleRatios);
result.ratioConfidence = getConfidenceLevel(ratioCount, datasetSize);
}
if (reductionCount >= 1) {
result.propertiesWithReductions = reductionCount;
result.averagePriceReduction = Math.round(calculateAverage(priceReductions) * 10) / 10;
result.reductionPercentage = Math.round((reductionCount / datasetSize) * 100);
}
if (Object.keys(result).length === 0) {
return { message: 'Insufficient pricing data for competitive analysis' };
}
return result;
} catch (error) {
return { message: 'Competitive analysis unavailable due to data issues' };
}
}
// Helper functions for enhanced validation and performance
function getConfidenceLevel(sampleSize, totalSize) {
const percentage = (sampleSize / totalSize) * 100;
if (percentage >= 80 && sampleSize >= 10) return 'high';
if (percentage >= 50 && sampleSize >= 5) return 'moderate';
return 'low';
}
function getMarketVelocity(avgDays) {
if (avgDays <= 14) return 'Very Fast';
if (avgDays <= 30) return 'Fast';
if (avgDays <= 60) return 'Moderate';
if (avgDays <= 120) return 'Slow';
return 'Very Slow';
}
function getNumericValue(record, fieldNames) {
for (const fieldName of fieldNames) {
if (record.hasOwnProperty(fieldName)) {
const value = record[fieldName];
if (value !== undefined && value !== null && value !== '') {
const numValue = parseFloat(value.toString().replace(/[,$%]/g, ''));
if (!isNaN(numValue) && isFinite(numValue)) return numValue;
}
}
}
return null;
}
function getStringValue(record, fieldNames) {
for (const fieldName of fieldNames) {
if (record.hasOwnProperty(fieldName)) {
const value = record[fieldName];
if (value !== undefined && value !== null && value !== '') {
const stringValue = value.toString().trim();
if (stringValue.length > 0) return stringValue;
}
}
}
return null;
}
function getDateValue(record, fieldNames) {
for (const fieldName of fieldNames) {
if (record.hasOwnProperty(fieldName)) {
const value = record[fieldName];
if (value !== undefined && value !== null && value !== '') {
const date = new Date(value);
if (!isNaN(date.getTime()) && date.getFullYear() >= 2020 && date.getFullYear() <= new Date().getFullYear() + 1) {
return date;
}
}
}
}
return null;
}
function calculateAverage(numbers) {
if (numbers.length === 0) return 0;
return Math.round(numbers.reduce((sum, num) => sum + num, 0) / numbers.length);
}
function calculateMedian(numbers) {
if (numbers.length === 0) return 0;
const sorted = [...numbers].sort((a, b) => a - b);
const mid = Math.floor(sorted.length / 2);
return sorted.length % 2 === 0 ? (sorted[mid - 1] + sorted[mid]) / 2 : sorted[mid];
}
function calculatePriceDistribution(prices) {
if (prices.length === 0) return {};
const ranges = {
'Under $300K': prices.filter(p => p < 300000).length,
'$300K-$500K': prices.filter(p => p >= 300000 && p < 500000).length,
'$500K-$750K': prices.filter(p => p >= 500000 && p < 750000).length,
'$750K-$1M': prices.filter(p => p >= 750000 && p < 1000000).length,
'Over $1M': prices.filter(p => p >= 1000000).length
};
return ranges;
}
function getSqftRange(sqft) {
if (sqft < 1500) return 'Under 1,500';
if (sqft < 2500) return '1,500-2,500';
if (sqft < 3500) return '2,500-3,500';
return 'Over 3,500';
}
function getMarketBalance(monthsOfInventory) {
if (monthsOfInventory < 3) return 'Strong Seller\'s Market';
if (monthsOfInventory < 6) return 'Balanced Market';
return 'Buyer\'s Market';
}
function getMostCommonConfig(bedrooms, bathrooms) {
if (bedrooms.length === 0) return 'N/A';
const avgBeds = Math.round(calculateAverage(bedrooms));
const avgBaths = Math.round(calculateAverage(bathrooms));
return `${avgBeds} bed, ${avgBaths} bath`;
}
function getMonthWithMostActivity(monthData) {
let maxMonth = null;
let maxCount = 0;
for (const [month, count] of Object.entries(monthData)) {
if (count > maxCount) {
maxCount = count;
maxMonth = parseInt(month);
}
}
if (maxMonth) {
const monthNames = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
return monthNames[maxMonth - 1];
}
return 'N/A';
}
function getCompetitivePressure(ratios) {
if (ratios.length === 0) return 'Unknown';
const avgRatio = calculateAverage(ratios);
if (avgRatio > 102) return 'High - Properties selling above list';
if (avgRatio > 98) return 'Moderate - Properties selling near list';
return 'Low - Properties selling below list';
}
function generateEmailContent(contactSegment, marketData, marketInsights, senderInfo, brandingData, emailType) {
const anthropicApiKey = PropertiesService.getScriptProperties().getProperty('ANTHROPIC_CLAUDE');
if (!anthropicApiKey) {
throw new Error('ANTHROPIC_CLAUDE API key not found in script properties');
}
const prompt = createClaudePrompt(contactSegment, marketData, marketInsights, senderInfo, brandingData, emailType);
const payload = {
model: "claude-3-5-sonnet-20241022",
max_tokens: 4000,
messages: [{ role: "user", content: prompt }]
};
const options = {
method: 'POST',
headers: {
'Content-Type': 'application/json',
'x-api-key': anthropicApiKey,
'anthropic-version': '2023-06-01'
},
payload: JSON.stringify(payload)
};
try {
const response = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', options);
const responseData = JSON.parse(response.getContentText());
if (responseData.error) {
throw new Error(`Claude API error: ${responseData.error.message}`);
}
if (responseData.content && responseData.content[0] && responseData.content[0].text) {
return responseData.content[0].text;
} else {
throw new Error('Invalid response from Claude API');
}
} catch (error) {
console.error('Error calling Claude API:', error);
throw error;
}
}
function createClaudePrompt(contactSegment, marketData, marketInsights, senderInfo, brandingData, emailType) {
const dataTimeframe = determineDataTimeframe(marketData);
let postalCode;
if (emailType === 'seller') {
postalCode = contactSegment.postalCode;
} else {
// For general newsletter, create area name from all ZIP codes
const { contactSegments } = getContactSegmentsForAreaName();
postalCode = createAreaName(contactSegments.sellerSegments);
}
return `Create a professional HTML email for real estate market report following EXACT visual specifications.
AGENT INFO: ${JSON.stringify(senderInfo)}
BRANDING: ${JSON.stringify(brandingData)}
AREA: ${postalCode}
EMAIL TYPE: ${emailType}
MARKET DATA SAMPLE (${dataTimeframe}):
${JSON.stringify(marketData.slice(0, 8))}
CALCULATED MARKET INSIGHTS:
${JSON.stringify(marketInsights, null, 2)}
ANALYSIS INSTRUCTIONS:
- Use the CALCULATED MARKET INSIGHTS above as your primary source for statistics and trends
- The insights include confidence levels and sample sizes - adjust your language accordingly
- For "limited" or "low confidence" data, use softer language like "early indicators suggest" or "based on available data"
- For "excellent" or "high confidence" data, use stronger language like "market data clearly shows" or "consistent pattern indicates"
- Focus on the most statistically significant insights (those with good sample sizes and confidence levels)
- If certain calculations show "insufficient data" messages, skip those metrics and focus on available insights
REQUIRED VISUAL STRUCTURE (Follow exactly):
0. EMAIL CONTAINER:
- Main table: max-width: 600px, margin: 0 auto, background: #ffffff
- Outer wrapper: width="100%" with background color for full bleed
- Center alignment: align="center" on outer table
- Mobile responsive: @media max-width 600px { main table width: 100% !important; padding: 0 10px; }
1. HEADER SECTION:
- Background: Teal to blue gradient using exact hex colors from branding data
- White text with simple title format: "##### Market Report" (just ZIP code, no city)
- Title format: "37064 Market Report" or "${postalCode} Market Report"
- Subtitle: "${dataTimeframe} • Prepared by [Agent Name]"
2. SALUTATION:
- Always use: "Hi {{FNAME}},"
- Standard black text from style guide
3. MARKET INSIGHT SECTION:
- 4-6 substantive sentences about market findings that demonstrate deep local expertise
- Focus ONLY on property/market metrics - NEVER mention agent names, brokerage performance, or realtor statistics
- Create "wow factor" insights that make homeowners think "this agent really knows their stuff"
- Look for compelling patterns: price trends by property type, timing advantages, inventory pressure, competitive dynamics
- Use bullet points if helpful for clarity
- Text color: Black shade from style guide
- CONSISTENT LINE SPACING: Use line-height: 1.6 for all body text paragraphs
- USE BOLD AND ITALICS strategically for emphasis on key statistics and insights
- Find genuinely compelling insights from the actual data that would motivate homeowners to take action
4. METRICS GRID (2x2 layout):
- Use TABLE structure: <table cellpadding="0" cellspacing="15" width="100%">
- Each TD width="50%" with inner metric box having proper padding (20px)
- Structure: <td><div style="background:#f7f9fc; border:1px solid #e2e8f0; border-radius:8px; padding:20px; margin:5px; text-align:center;">[content]</div></td>
- Light gray background (#f7f9fc) with 1px solid border (#e2e8f0) on inner boxes
- Border-radius: 8px on each metric box
- ALL TEXT CENTERED within each metric box ONLY
- Metric titles and values in teal or blue from branding colors
- Mobile responsive: @media max-width 600px, stack vertically (width: 100%)
- Use cellspacing="15" for proper spacing between boxes
5. STRATEGIC ADVICE SECTION:
- Brief paragraph on what this means for sellers
- Same black text color as insight section
- CONSISTENT LINE SPACING: Use line-height: 1.6 for all body text paragraphs
- USE BOLD AND ITALICS strategically for emphasis on key advice points
6. CALL-TO-ACTION:
- Button using primary brand color
- ALWAYS CENTER the button: text-align: center on container
- Link to real email/phone only - NO fake website URLs or 404 links
- ALWAYS use mailto: links that open the user's email client to send an email
- Vary the CTA text based on email content and insights (e.g., "Get Your Home's Value", "Discuss Your Selling Timeline", "Schedule Your Market Analysis")
- Natural conversation starter text written in SECOND PERSON (you/your perspective)
7. FOOTER:
- Horizontal line separator above footer content (use <hr> with proper styling)
- Agent name in BOLD as first line
- Company name on second line (not bold)
- Title/designation on third line (not bold)
- Phone number on fourth line (not bold)
- Email address on fifth line (not bold, as clickable mailto: link)
- EXACT legal disclaimer on final line: "This market report is for informational purposes only. Data and calculations sourced from MLS and public records. Not intended to solicit properties currently listed for sale."
- Legal disclaimer in gray text, smaller font
- ALWAYS LEFT-ALIGNED: Use text-align: left for all footer content
- Tight line spacing with line-height: 1.4
- NO centering of any footer elements
- Consistent visual treatment with proper hierarchy
CRITICAL REQUIREMENTS:
- Email-safe HTML with inline CSS only - USE TABLE LAYOUTS NOT DIV/FLEXBOX
- Main content container: max-width 600px, centered with margin: 0 auto
- Use EXACT hex colors from branding data
- Use font family specified in branding data (fallback to web-safe fonts)
- CONSISTENT BODY TEXT FORMATTING: All paragraphs must use style="line-height: 1.6; margin: 0 0 16px 0;"
- Include mobile responsive CSS: @media only screen and (max-width: 600px) { container width: 100% !important; padding: 0 10px; }
- 2x2 metrics must use proper table structure with no overlapping
- All metrics must use actual data from market data provided
- Never fabricate data or create fake links
- Maintain visual consistency throughout
- Maximum 500 words of content
- Write conversationally for homeowners considering selling using SECOND PERSON (you/your)
OUTPUT: Complete HTML email code only, no explanations.`;
}
function determineDataTimeframe(marketData) {
if (!marketData || marketData.length === 0) {
return "Current Period";
}
let earliestDate = null;
let latestDate = null;
// Process each record to find the "ending event" date for that listing
marketData.forEach(record => {
let recordEndDate = null;
// Priority 1: Closed/Sold dates (actual transactions)
const closedFields = ['closeddate', 'closed_date', 'solddate', 'sold_date', 'saledate', 'sale_date'];
for (const field of closedFields) {
const fieldKey = Object.keys(record).find(key =>
key.toLowerCase().replace(/[^a-z]/g, '') === field
);
if (fieldKey && record[fieldKey]) {
const date = new Date(record[fieldKey]);
if (!isNaN(date.getTime()) && date.getFullYear() >= 2022) {
recordEndDate = date;
break; // Closed date trumps all
}
}
}
// Priority 2: Expired/Cancelled/Withdrawn dates (if no closed date)
if (!recordEndDate) {
const expiredFields = ['expireddate', 'expired_date', 'cancelleddate', 'cancelled_date',
'canceleddate', 'canceled_date', 'withdrawndate', 'withdrawn_date',
'terminateddate', 'terminated_date', 'offmarketdate', 'off_market_date'];
for (const field of expiredFields) {
const fieldKey = Object.keys(record).find(key =>
key.toLowerCase().replace(/[^a-z]/g, '') === field
);
if (fieldKey && record[fieldKey]) {
const date = new Date(record[fieldKey]);
if (!isNaN(date.getTime()) && date.getFullYear() >= 2022) {
recordEndDate = date;
break;
}
}
}
}
// Priority 3: For Active/Pending listings, use current date
if (!recordEndDate) {
const statusField = Object.keys(record).find(key =>
key.toLowerCase().includes('status')
);
if (statusField) {
const status = record[statusField]?.toString().toLowerCase();
if (status && (status.includes('active') || status.includes('pending') ||
status.includes('under contract') || status.includes('contingent'))) {
recordEndDate = new Date();
}
}
}
// Add this record's end date to our range
if (recordEndDate) {
if (!earliestDate || recordEndDate < earliestDate) earliestDate = recordEndDate;
if (!latestDate || recordEndDate > latestDate) latestDate = recordEndDate;
}
});
if (earliestDate && latestDate) {
const formatDate = (date) => {
return date.toLocaleDateString('en-US', { month: 'short', year: 'numeric' });
};
// If same month/year, just show that period
if (earliestDate.getFullYear() === latestDate.getFullYear() &&
earliestDate.getMonth() === latestDate.getMonth()) {
return formatDate(earliestDate);
} else {
return `${formatDate(earliestDate)} - ${formatDate(latestDate)}`;
}
}
return "Recent Market Activity";
}
function createOutputSheet(emailTemplates) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const zapierSheetName = 'My HTML';
// Get or create Zapier sheet (always append, never overwrite)
let zapierSheet = spreadsheet.getSheetByName(zapierSheetName);
if (!zapierSheet) {
zapierSheet = spreadsheet.insertSheet(zapierSheetName);
// Add headers for new sheet
const headers = ['Timestamp', 'Postal Code', 'Email Type', 'Contact Count', 'HTML Content', 'Contact List'];
zapierSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
zapierSheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
}
const currentTimestamp = new Date().toLocaleString();
emailTemplates.forEach(template => {
const contactList = template.contacts.map(c =>
`${c.firstName || ''} ${c.lastName || ''} (${c.email || 'No Email'})`
).join('\n');
// Add to Zapier sheet with timestamp
const zapierRowData = [currentTimestamp, template.postalCode, template.emailType, template.contactCount, template.htmlContent, contactList];
const lastRow = zapierSheet.getLastRow();
zapierSheet.getRange(lastRow + 1, 1, 1, zapierRowData.length).setValues([zapierRowData]);
});
// Format Zapier sheet
zapierSheet.setColumnWidth(5, 500);
zapierSheet.setColumnWidth(6, 300);
}
function createTestOutput(postalCode, emailType, htmlContent, contactCount) {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const zapierSheetName = 'My HTML';
// Get or create Zapier sheet (always append, never overwrite)
let zapierSheet = spreadsheet.getSheetByName(zapierSheetName);
if (!zapierSheet) {
zapierSheet = spreadsheet.insertSheet(zapierSheetName);
// Add headers for new sheet
const headers = ['Timestamp', 'Postal Code', 'Email Type', 'Contact Count', 'HTML Content', 'Contact List'];
zapierSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
zapierSheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
}
// Add to Zapier sheet with timestamp
const currentTimestamp = new Date().toLocaleString();
const zapierRowData = [currentTimestamp, postalCode, emailType, contactCount, htmlContent, 'Test Generation - No Contact List'];
const lastRow = zapierSheet.getLastRow();
zapierSheet.getRange(lastRow + 1, 1, 1, zapierRowData.length).setValues([zapierRowData]);
// Format Zapier sheet
zapierSheet.setColumnWidth(5, 500);
zapierSheet.setColumnWidth(6, 300);
}
function createTaggedRecipientsSheet(emailTemplates) {
try {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const sheetName = 'Tagged Recipients';
// Delete existing sheet if it exists
const existingSheet = spreadsheet.getSheetByName(sheetName);
if (existingSheet) {
spreadsheet.deleteSheet(existingSheet);
}
// Create new sheet
const recipientsSheet = spreadsheet.insertSheet(sheetName);
// Determine which columns have data (excluding phone)
const availableColumns = determineAvailableColumns(emailTemplates);
// Set up dynamic headers based on available data
const headers = [];
const columnMap = {};
let colIndex = 0;
if (availableColumns.hasFirstName) {
headers.push('First Name');
columnMap.firstName = colIndex++;
}
if (availableColumns.hasLastName) {
headers.push('Last Name');
columnMap.lastName = colIndex++;
}
if (availableColumns.hasEmail) {
headers.push('Email');
columnMap.email = colIndex++;
}
if (availableColumns.hasAddress) {
headers.push('Address');
columnMap.address = colIndex++;
}
// Always include Tag column
headers.push('Tag');
columnMap.tag = colIndex++;
recipientsSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
recipientsSheet.getRange(1, 1, 1, headers.length).setFontWeight('bold');
const allRecipients = [];
emailTemplates.forEach(template => {
const tag = template.postalCode.includes('AREA') || template.postalCode.includes('REGION') ? 'code-GENERAL' : `code-${template.postalCode}`;
if (template.contacts && template.contacts.length > 0) {
template.contacts.forEach(contact => {
const { firstName, lastName } = separateFullName(contact.firstName, contact.lastName);
// Build row with only available columns (no phone)
const row = new Array(headers.length);
if (availableColumns.hasFirstName) row[columnMap.firstName] = firstName || '';
if (availableColumns.hasLastName) row[columnMap.lastName] = lastName || '';
if (availableColumns.hasEmail) row[columnMap.email] = contact.email || '';
if (availableColumns.hasAddress) row[columnMap.address] = contact.homeAddress || '';
row[columnMap.tag] = tag;
allRecipients.push(row);
});
}
});
if (allRecipients.length > 0) {
recipientsSheet.getRange(2, 1, allRecipients.length, headers.length).setValues(allRecipients);
// Dynamic column formatting based on available columns
let currentCol = 1;
if (availableColumns.hasFirstName) recipientsSheet.setColumnWidth(currentCol++, 120);
if (availableColumns.hasLastName) recipientsSheet.setColumnWidth(currentCol++, 120);
if (availableColumns.hasEmail) recipientsSheet.setColumnWidth(currentCol++, 200);
if (availableColumns.hasAddress) recipientsSheet.setColumnWidth(currentCol++, 250);
recipientsSheet.setColumnWidth(currentCol, 100); // Tag column
}
} catch (error) {
}
}
function determineAvailableColumns(emailTemplates) {
let hasFirstName = false;
let hasLastName = false;
let hasEmail = false;
let hasAddress = false;
// Check all contacts across all templates for available data (excluding phone)
emailTemplates.forEach(template => {
if (template.contacts && template.contacts.length > 0) {
template.contacts.forEach(contact => {
const { firstName, lastName } = separateFullName(contact.firstName, contact.lastName);
if (firstName && firstName.trim()) hasFirstName = true;
if (lastName && lastName.trim()) hasLastName = true;
if (contact.email && contact.email.toString().trim()) hasEmail = true;
if (contact.homeAddress && contact.homeAddress.toString().trim()) hasAddress = true;
});
}
});
return {
hasFirstName,
hasLastName,
hasEmail,
hasAddress
};
}
function separateFullName(firstName, lastName) {
try {
// If we already have separate first and last names, use them
if (firstName && lastName) {
return {
firstName: firstName.toString().trim(),
lastName: lastName.toString().trim()
};
}
// If we only have firstName but it might be a full name
if (firstName && !lastName) {
const fullName = firstName.toString().trim();
const nameParts = fullName.split(' ').filter(part => part.length > 0);
if (nameParts.length >= 2) {
return {
firstName: nameParts[0],
lastName: nameParts.slice(1).join(' ') // Everything after first word
};
} else {
return { firstName: fullName, lastName: '' };
}
}
// If we only have lastName but it might be a full name
if (lastName && !firstName) {
const fullName = lastName.toString().trim();
const nameParts = fullName.split(' ').filter(part => part.length > 0);
if (nameParts.length >= 2) {
return {
firstName: nameParts[0],
lastName: nameParts.slice(1).join(' ')
};
} else {
return { firstName: fullName, lastName: '' };
}
}
// If neither exists
return { firstName: '', lastName: '' };
} catch (error) {
console.error('Error separating name:', error);
return { firstName: '', lastName: '' };
}
}
function estimateApiCosts(contactSegments) {
const sellerCalls = contactSegments.sellerSegments.length;
const generalCalls = contactSegments.generalSegment.contacts.length > 0 ? 1 : 0;
const totalCalls = sellerCalls + generalCalls;
const estimatedCost = (totalCalls * 0.015).toFixed(3);
const sellerContacts = contactSegments.sellerSegments.reduce((sum, segment) => sum + segment.contacts.length, 0);
const generalContacts = contactSegments.generalSegment.contacts.length;
return {
totalCalls: totalCalls,
estimatedCost: estimatedCost,
sellerContacts: sellerContacts,
generalContacts: generalContacts
};
}
function isRateLimitError(error) {
const message = error.message.toLowerCase();
return message.includes('credit') || message.includes('quota') || message.includes('rate_limit');
}
function logGenerationInfo(estimation) {
// Logging disabled for production
}
function logDataAnalysis(contactSegments, estimation) {
// Logging disabled for production
}