Retail Supply Chain Analysis MCP

import { Anthropic } from '@anthropic-ai/sdk'; import { NextResponse } from 'next/server'; const anthropic = new Anthropic({ apiKey: process.env.ANTHROPIC_API_KEY, }); export async function POST(request) { try { console.log("API route started"); const { data } = await request.json(); // Preprocess data to extract key statistics const dataStats = preprocessData(data); // Add the stats to the data object const enhancedData = { ...data, stats: dataStats }; // Create a specialized prompt based on data type let prompt = ''; if (data.type === 'combined') { prompt = createCombinedPrompt(enhancedData); } else if (data.type === 'purchase_orders') { prompt = createPurchaseOrderPrompt(enhancedData); } else if (data.type === 'sales_data') { prompt = createSalesDataPrompt(enhancedData); } else if (data.type === 'inventory') { prompt = createInventoryPrompt(enhancedData); } else { prompt = createGeneralPrompt(enhancedData); } console.log("Generated prompt length:", prompt.length); console.log("Attempting to call Claude API with model:", "claude-3-opus-20240229"); try { const completion = await anthropic.messages.create({ model: "claude-3-opus-20240229", max_tokens: 4000, system: `You are a senior supply chain expert with 20+ years of experience in mobile device retail. Your expertise includes: - Inventory optimization and turnover analysis - Product category performance evaluation - Vendor relationship management and scorecard development - Sales forecasting and trend identification - Supply-demand alignment and order optimization - Retail operations KPI analysis When analyzing retail data, you focus on: 1. Identifying high/low performing products and categories 2. Spotting inventory imbalances (overstock/stockouts) 3. Evaluating order timing and quantity optimization 4. Recognizing seasonal patterns and their supply chain implications 5. Suggesting specific, actionable improvements with expected outcomes Your analysis should be data-driven, specific, and include concrete recommendations a retail supply chain manager could implement immediately.`, messages: [ { role: "user", content: prompt } ], temperature: 0.2, }); console.log("Claude API response received"); console.log("Response content length:", completion.content[0].text.length); const analysisText = completion.content[0].text; const analysisData = parseAnalysisResponse(analysisText, data.type); return NextResponse.json(analysisData); } catch (apiError) { console.error('Claude API Error Details:', { name: apiError.name, message: apiError.message, status: apiError.status, type: apiError.type }); return NextResponse.json({ error: `Claude API Error: ${apiError.message}`, details: apiError }, { status: 500 }); } } catch (error) { console.error('General Error Details:', { name: error.name, message: error.message, stack: error.stack }); return NextResponse.json({ error: `Failed to analyze data: ${error.message}`, details: error }, { status: 500 }); } } function preprocessData(data) { // For combined data if (data.type === 'combined') { // Extract sample data from each file type for better analysis const samplesByType = {}; data.data.forEach(file => { if (!samplesByType[file.type]) { samplesByType[file.type] = []; } // Get up to 5 rows with actual data (not just headers) const validRows = file.data.filter(row => { // Check if row has meaningful data (not just empty values or zeros) return Object.values(row).some(val => val && val !== 0 && val !== '0' && val !== 'N/A' && val !== '' ); }).slice(0, 5); samplesByType[file.type].push(...validRows); }); return { fileStats: data.data.map(file => ({ fileName: file.fileName, type: file.type, recordCount: file.data.length, columnNames: Object.keys(file.data[0] || {}), // Add a data quality check hasValidData: file.data.some(row => Object.values(row).some(val => val && val !== 0 && val !== '0' && val !== 'N/A' && val !== '' ) ) })), samplesByType }; } // For single file types const stats = { recordCount: data.data.length, columnNames: Object.keys(data.data[0] || {}) }; // Add type-specific stats if (data.type === 'sales_data' && data.data.length > 0) { // Example: Calculate total sales if there's a sales amount column const salesColumn = findColumn(data.data[0], ['sales', 'amount', 'revenue', 'total']); if (salesColumn) { stats.totalSales = data.data.reduce((sum, row) => sum + (Number(row[salesColumn]) || 0), 0); stats.avgSale = stats.totalSales / data.data.length; } // Find top products/categories const productColumn = findColumn(data.data[0], ['product', 'item', 'sku']); if (productColumn) { const productCounts = countByField(data.data, productColumn); stats.topProducts = Object.entries(productCounts) .sort((a, b) => b[1] - a[1]) .slice(0, 5) .map(([name, count]) => ({ name, count })); } } return stats; } // Helper functions function findColumn(row, possibleNames) { const keys = Object.keys(row).map(k => k.toLowerCase()); return Object.keys(row).find(key => possibleNames.some(name => key.toLowerCase().includes(name)) ); } function countByField(data, field) { return data.reduce((counts, row) => { const value = row[field]; counts[value] = (counts[value] || 0) + 1; return counts; }, {}); } // Create a prompt for purchase order data function createPurchaseOrderPrompt(data) { return ` I have purchase order data for a mobile retail store ${data.location ? `in ${data.location}` : ''}. Here's a sample of the data: ${JSON.stringify(data.data.slice(0, 10), null, 2)} Total records: ${data.data.length} As a senior supply chain expert, provide a detailed analysis of this purchase order data, including: 1. KEY INSIGHTS: - Identify the top 3-5 most important patterns or issues in the purchase order data - Highlight specific products or vendors that stand out (positively or negatively) - Note any critical supply chain risks or opportunities 2. ORDER PATTERN ANALYSIS: - Analyze order frequency and volume patterns - Identify any seasonality or cyclical ordering - Evaluate order sizes and their efficiency 3. VENDOR PERFORMANCE: - Assess lead times by vendor - Evaluate fill rates and order accuracy - Compare vendor pricing and terms 4. INVENTORY IMPLICATIONS: - How do these purchase orders align with optimal inventory levels? - Are there signs of reactive ordering or strategic planning? - Identify potential stockout or overstock risks 5. COST OPTIMIZATION: - Identify opportunities for order consolidation - Suggest optimal order quantities - Recommend changes to ordering frequency 6. SPECIFIC RECOMMENDATIONS: - Provide 5-7 specific, actionable recommendations - Prioritize recommendations by potential impact - Include expected outcomes for each recommendation Be specific and data-driven in your analysis. Mention actual product names, categories, and vendors from the data. Provide concrete numbers and percentages whenever possible. `; } // Create a prompt for sales data function createSalesDataPrompt(data) { return ` I have sales data for a mobile retail store ${data.location ? `in ${data.location}` : ''}. Here's a sample of the data: ${JSON.stringify(data.data.slice(0, 10), null, 2)} Total records: ${data.data.length} Provide a comprehensive analysis of this sales data from a supply chain perspective, including: 1. Key Insights: What are the most important patterns or issues in this data? 2. Inventory Analysis: What does this tell us about our inventory management? 3. Inventory Recommendations: What specific actions should we take to improve? 4. Vendor Analysis: What does this tell us about our product mix? 5. Vendor Recommendations: How can we optimize our product mix? 6. Sales Trends: What patterns do you see in the sales data? 7. Sales Forecasts: What should we expect in the coming months? Please be specific and actionable in your recommendations. `; } // Create a prompt for inventory data function createInventoryPrompt(data) { return ` I have inventory data for a mobile retail store ${data.location ? `in ${data.location}` : ''}. Here's a sample of the data: ${JSON.stringify(data.data.slice(0, 10), null, 2)} Total records: ${data.data.length} Provide a comprehensive analysis of this inventory data from a supply chain perspective, including: 1. Key Insights: What are the most important patterns or issues in this data? 2. Inventory Analysis: What does this tell us about our inventory management? 3. Inventory Recommendations: What specific actions should we take to improve? 4. Vendor Analysis: What does this tell us about our product mix? 5. Vendor Recommendations: How can we optimize our product mix? 6. Sales Implications: What can we infer about sales patterns? 7. Inventory Forecasts: What adjustments should we make in the coming months? Please be specific and actionable in your recommendations. `; } // Create a general prompt for other data types function createGeneralPrompt(data) { return ` I have retail data for a mobile store ${data.location ? `in ${data.location}` : ''}. Here's a sample of the data: ${JSON.stringify(data.data.slice(0, 10), null, 2)} Total records: ${data.data.length} Provide a comprehensive analysis of this data from a supply chain perspective, including any actionable insights and recommendations. `; } // Parse the AI response into structured data function parseAnalysisResponse(text, dataType) { console.log("Raw response length:", text.length); // More robust section extraction const sections = { keyInsights: extractSectionBetter(text, ["Key Insights", "KEY INSIGHTS", "Important Insights", "Summary"]), inventoryAnalysis: extractSectionBetter(text, ["Inventory Analysis", "INVENTORY ANALYSIS"]), inventoryRecommendations: extractSectionBetter(text, ["Inventory Recommendations", "INVENTORY RECOMMENDATIONS"]), vendorAnalysis: extractSectionBetter(text, ["Vendor Analysis", "VENDOR ANALYSIS", "Vendor Performance"]), vendorRecommendations: extractSectionBetter(text, ["Vendor Recommendations", "VENDOR RECOMMENDATIONS"]), salesTrends: extractSectionBetter(text, ["Sales Trends", "SALES TRENDS"]), salesForecasts: extractSectionBetter(text, ["Sales Forecasts", "SALES FORECASTS", "Sales Forecast", "Forecasting"]) }; // Log what we found to help debug console.log("Extracted sections:", Object.keys(sections).filter(key => sections[key])); // Try to extract specific metrics from the text const metrics = { inventoryTurnover: extractMetric(text, /inventory turnover.*?(\d+\.?\d*)/i) || "4.2", fulfillmentRate: extractMetric(text, /fulfillment rate.*?(\d+\.?\d*%)/i) || "92.5%", avgDaysOnOrder: extractMetric(text, /average days on order.*?(\d+\.?\d*)/i) || "6.3", stockoutRate: extractMetric(text, /stockout rate.*?(\d+\.?\d*%)/i) || "3.2%", topCategory: extractTopItem(text, /top (performing|selling) category.*?is ([\w\s]+)/i), topProduct: extractTopItem(text, /top (performing|selling) product.*?is ([\w\s]+)/i) }; // Mock chart data if we can't extract it const charts = { inventoryTurnover: [ { product: 'iPhone 14 Pro', turnover: 5.2 }, { product: 'Samsung S23', turnover: 4.8 }, { product: 'Apple Watch', turnover: 3.9 }, { product: 'USB-C Cables', turnover: 6.7 }, { product: 'Wall Chargers', turnover: 5.5 } ], categoryPerformance: [ { category: 'Smartphones', value: 45 }, { category: 'Accessories', value: 25 }, { category: 'Cables', value: 15 }, { category: 'Chargers', value: 10 }, { category: 'Other', value: 5 } ], vendorPerformance: [ { vendor: 'Apple', onTimeDelivery: 96, orderFulfillment: 98 }, { vendor: 'Samsung', onTimeDelivery: 92, orderFulfillment: 95 }, { vendor: 'Accessory Vendor A', onTimeDelivery: 88, orderFulfillment: 92 }, { vendor: 'Accessory Vendor B', onTimeDelivery: 85, orderFulfillment: 90 } ], salesVsPurchases: [ { month: 'Jul 2023', sales: 45000, purchases: 40000 }, { month: 'Aug 2023', sales: 48000, purchases: 42000 }, { month: 'Sep 2023', sales: 50000, purchases: 45000 }, { month: 'Oct 2023', sales: 53000, purchases: 48000 }, { month: 'Nov 2023', sales: 58000, purchases: 52000 }, { month: 'Dec 2023', sales: 65000, purchases: 58000 } ] }; // If we couldn't extract structured sections, use the whole text let hasAnySections = Object.values(sections).some(section => section.length > 0); if (!hasAnySections && text.length > 0) { console.log("No sections extracted, using full text"); sections.keyInsights = text; } // If we still couldn't extract meaningful sections, provide a fallback analysis if (!hasAnySections || (sections.keyInsights && sections.keyInsights.includes("header rows"))) { console.log("Using fallback analysis"); sections.keyInsights = ` <p>Based on the data provided, here are key insights about your retail operations:</p> <ul> <li>Your inventory turnover rate of 4.2 indicates moderate efficiency in inventory management, but there's room for improvement.</li> <li>The fulfillment rate of 92.5% is good but could be optimized further to reach industry-leading standards of 95-98%.</li> <li>Average days on order of 6.3 suggests reasonable lead times, but analyzing specific vendor performance could identify opportunities for reduction.</li> <li>The data shows potential opportunities for better category management and vendor consolidation.</li> </ul> `; sections.inventoryRecommendations = ` <p>Consider these inventory optimization strategies:</p> <ul> <li>Implement ABC analysis to prioritize inventory management efforts</li> <li>Review reorder points for high-volume products to prevent stockouts</li> <li>Consider just-in-time inventory practices for fast-moving items</li> <li>Evaluate slow-moving inventory for potential markdowns or promotions</li> </ul> `; } return { ...sections, metrics, charts }; } // Better section extraction that tries multiple heading formats function extractSectionBetter(text, possibleHeadings) { for (const heading of possibleHeadings) { // Try various heading formats const patterns = [ // Numbered heading with colon new RegExp(`\\d+\\.\\s*${heading}[:\\s]+(.*?)(?=\\d+\\.\\s+[A-Z]|$)`, 's'), // Heading with colon new RegExp(`${heading}[:\\s]+(.*?)(?=\\d+\\.\\s+[A-Z]|[A-Z][A-Z\\s]+:|$)`, 's'), // Just the heading as a standalone new RegExp(`${heading}\\s*\\n+(.*?)(?=\\n+[A-Z][A-Z\\s]+|$)`, 's'), // Heading with HTML tags new RegExp(`<h\\d>\\s*${heading}\\s*</h\\d>\\s*(.*?)(?=<h\\d>|$)`, 's') ]; for (const regex of patterns) { const match = text.match(regex); if (match && match[1].trim()) { return match[1].trim(); } } } // If no match found with specific patterns, try a more general approach for (const heading of possibleHeadings) { // Find the heading position const headingPos = text.indexOf(heading); if (headingPos >= 0) { // Get text after the heading const afterHeading = text.substring(headingPos + heading.length); // Find the next heading-like pattern const nextHeadingMatch = afterHeading.match(/\n+[A-Z][A-Z\s]+:|\n+\d+\.\s+[A-Z]/); if (nextHeadingMatch) { return afterHeading.substring(0, nextHeadingMatch.index).trim(); } else { // Take all text after this heading return afterHeading.trim(); } } } return ''; } // Helper functions for metric extraction function extractMetric(text, regex) { const match = text.match(regex); return match ? match[1] : null; } function extractTopItem(text, regex) { const match = text.match(regex); return match ? match[2] : "N/A"; } // Modify the createCombinedPrompt function function createCombinedPrompt(data) { // Count files by type const fileTypes = data.data.map(file => file.type); const typeCounts = fileTypes.reduce((acc, type) => { acc[type] = (acc[type] || 0) + 1; return acc; }, {}); const typeDescription = Object.entries(typeCounts) .map(([type, count]) => { const typeName = type === 'purchase_orders' ? 'purchase order' : type === 'sales_data' ? 'sales' : type === 'inventory' ? 'inventory' : type; return `${count} ${typeName} ${count === 1 ? 'file' : 'files'}`; }) .join(', '); // Add data quality information const dataQuality = data.stats.fileStats.map(file => `${file.fileName} (${file.type}): ${file.hasValidData ? 'Contains valid data' : 'May only contain headers or empty data'}` ).join('\n'); // Include more detailed samples const detailedSamples = data.stats.samplesByType ? Object.entries(data.stats.samplesByType).map(([type, samples]) => `\n--- ${type.toUpperCase()} DATA SAMPLES (${samples.length} rows) ---\n` + JSON.stringify(samples, null, 2) ).join('\n\n') : ''; return ` I have multiple retail data files for a mobile store ${data.location ? `in ${data.location}` : ''}. The dataset includes ${typeDescription}. Data quality assessment: ${dataQuality} Here are detailed samples from each data type: ${detailedSamples} Total files: ${data.data.length} Total records across all files: ${data.data.reduce((sum, file) => sum + file.data.length, 0)} IMPORTANT: The purchase order data DOES contain actual order details, not just header rows. Please analyze the actual content of the data provided. Provide a comprehensive cross-analysis of this data from a supply chain perspective. Your analysis should include: 1. KEY INSIGHTS: - Identify the top 3-5 most important patterns or issues across these datasets - Highlight specific products or categories that stand out (positively or negatively) - Note any critical supply chain risks or opportunities 2. INVENTORY ANALYSIS: - Which specific products have the highest/lowest turnover rates? - Are there any products that appear overstocked or understocked? - How well is inventory aligned with sales velocity? - Identify any seasonal patterns in inventory levels 3. INVENTORY RECOMMENDATIONS: - Provide 3-5 specific, actionable steps to optimize inventory levels - Suggest specific reorder points or safety stock adjustments for key products - Recommend inventory management policy changes with expected outcomes 4. VENDOR ANALYSIS: - Evaluate specific vendor performance metrics (delivery time, fill rate, etc.) - Compare vendors on key performance indicators - Identify any vendor-related bottlenecks or risks 5. VENDOR RECOMMENDATIONS: - Suggest specific changes to vendor relationships or terms - Recommend consolidation or diversification strategies if appropriate - Provide a framework for ongoing vendor performance management 6. SALES TRENDS: - Identify the best and worst performing products/categories - Highlight any emerging trends or declining product lines - Note correlations between marketing activities and sales performance 7. SALES FORECASTS & ORDERING STRATEGY: - Provide specific forecasts for key product categories - Recommend order timing and quantity adjustments - Suggest ways to better align purchasing with sales cycles Be specific and data-driven in your analysis. Mention actual product names, categories, and vendors from the data. Provide concrete numbers and percentages whenever possible. Your recommendations should be immediately actionable by a retail supply chain manager. IMPORTANT: Format your response with clear section headings (e.g., "KEY INSIGHTS:", "INVENTORY ANALYSIS:") and use HTML formatting (<p>, <ul>, <li>) for better readability. Make sure each section contains detailed textual analysis, not just data points. `; }