Creating a Vendor Performance Scorecard:
Prompt for AI

Directions:

Copy and paste the prompt below into your company's authorized Gen AI too to determine Lead Scoring.

ERP Data Extraction Guide for Vendor Scorecard
Version 1.0 

1. Required Data Files

 1.1 Vendor Master Data (vendor_master.csv)
```csv
vendor_id,vendor_name,contact_name,contact_email,contact_phone,vendor_status
```

 1.2 Purchase Orders (purchase_orders.csv)
```csv
po_number,vendor_id,order_date,requested_delivery_date,actual_delivery_date,total_amount,status,edi_flag
line_number,item_code,quantity_ordered,quantity_received,unit_price,receipt_date
```

 1.3 Quality Records (quality_records.csv)
```csv
inspection_id,vendor_id,po_number,line_number,inspection_date,defect_count,total_inspected,defect_type,severity
```

 1.4 Invoice Records (invoice_records.csv)
```csv
invoice_number,vendor_id,po_number,invoice_date,invoice_amount,payment_terms,payment_date,edi_processed
line_number,item_code,quantity,unit_price,line_amount
```

 1.5 Returns Data (returns.csv)
```csv
return_id,vendor_id,po_number,return_date,reason_code,quantity_returned,processed_date,resolution
```

 2. SQL Queries for Metric Calculation

 2.1 Delivery Performance Metrics
```sql
-- On-Time Delivery Rate
SELECT 
    vendor_id,
    COUNT(CASE WHEN actual_delivery_date <= requested_delivery_date THEN 1 END) * 100.0 / COUNT(*) as otd_rate
FROM purchase_orders
WHERE order_date BETWEEN :start_date AND :end_date
GROUP BY vendor_id;

-- Order Fill Rate
SELECT 
    po.vendor_id,
    SUM(CASE WHEN pol.quantity_received >= pol.quantity_ordered THEN 1 ELSE 0 END) * 100.0 / COUNT(*) as fill_rate
FROM purchase_orders po
JOIN purchase_order_lines pol ON po.po_number = pol.po_number
WHERE po.order_date BETWEEN :start_date AND :end_date
GROUP BY po.vendor_id;
```

 2.2 Quality Metrics
```sql
-- Product Quality Rating
SELECT 
    vendor_id,
    (1 - SUM(defect_count) * 1.0 / SUM(total_inspected)) * 100 as quality_rate
FROM quality_records
WHERE inspection_date BETWEEN :start_date AND :end_date
GROUP BY vendor_id;

-- Returns Processing Time
SELECT 
    vendor_id,
    AVG(DATEDIFF(day, return_date, processed_date)) as avg_processing_time
FROM returns
WHERE return_date BETWEEN :start_date AND :end_date
GROUP BY vendor_id;
```

 2.3 Cost Management Metrics
```sql
-- Price Competitiveness
WITH price_variance AS (
    SELECT 
        vendor_id,
        item_code,
        AVG(unit_price) as avg_price,
        LAG(AVG(unit_price)) OVER (PARTITION BY item_code ORDER BY order_date) as prev_price
    FROM purchase_orders
    GROUP BY vendor_id, item_code, DATETRUNC('month', order_date)
)
SELECT 
    vendor_id,
    AVG(CASE WHEN prev_price > 0 THEN (prev_price - avg_price) / prev_price * 100 ELSE 0 END) as price_improvement
FROM price_variance
GROUP BY vendor_id;

3. Data Transformation Steps

 3.1 Required Aggregations
1. Monthly performance rollups
2. Weighted score calculations
3. Trend analysis calculations

 3.2 Calculation Formulas
```python
 Overall Score Calculation
weighted_score = (
    delivery_score * 0.25 +
    quality_score * 0.20 +
    cost_score * 0.15 +
    integration_score * 0.25 +
    innovation_score * 0.15
)

 Trend Calculation
monthly_trend = [
    calculate_weighted_score(month)
    for month in last_12_months
]
```

 4. Output File Structure

 4.1 Scorecard Base Data (scorecard_base.csv)
```csv
vendor_id,evaluation_period,metric_category,metric_name,metric_value,weight,target,actual,score
```

 4.2 Trend Data (scorecard_trend.csv)
```csv
vendor_id,year,month,overall_score,delivery_score,quality_score,cost_score,integration_score,innovation_score
```

 5. Implementation Instructions

 5.1 Data Extraction Process
1. Schedule daily extracts from ERP
2. Store in staging area
3. Perform data validation
4. Transform using provided SQL
5. Generate output files

 5.2 Validation Rules
```python
def validate_scores(df):
    """
    Validation rules for scorecard data
    """
    assert df['score'].between(0, 100).all()
    assert df.groupby('vendor_id')['weight'].sum() == 100
    assert not df['metric_value'].isnull().any()
```

 5.3 Error Handling
1. Log all data extraction errors
2. Flag incomplete metrics
3. Mark estimated values
4. Track missing data points

 6. Data Update Frequency

 6.1 Real-time Updates
- EDI transactions
- Order status
- Delivery confirmations

 6.2 Daily Updates
- Quality metrics
- Returns processing
- Invoice accuracy

 6.3 Monthly Updates
- Trend calculations
- Overall scores
- Performance reviews

 7. System Requirements

 7.1 Database
- Support for analytical queries
- Partitioning by date and vendor
- Historical data retention

 7.2 Processing
- Parallel processing capability
- Error handling and recovery
- Audit trail maintenance

 8. Security Considerations

 8.1 Data Access
- Role-based access control
- Vendor-specific data isolation
- Audit logging

 8.2 Data Retention
- Archive policy
- Data cleanup procedures
- Backup requirements