# 🔍 Data Validation System - Sales Dashboard

## Overview
Your Sales Dashboard now has a **comprehensive data quality monitoring system** that automatically validates your Google Sheet data and alerts you to any issues.

## ✅ What's Implemented

### 1. Automated Data Validation
The system checks for:

#### **Critical Issues** (🚨 Must fix immediately)
- Missing required columns (Comercial, Cliente, Faturação, Quant, Mês, Familia)
- Unparseable date formats (more than a few rows)
- High percentage of missing values in critical fields (>10%)
- Invalid objectives sheet structure

#### **Warnings** (⚠️ Should review)  
- Non-standard Familia spellings (e.g., "Urna Nova" instead of "Urnas")
- Invalid numeric values in Faturação or Quantidade
- Missing values in important fields (<10%)
- Potential duplicate rows
- Similar comercial names (possible typos)

#### **Information** (ℹ️ For review)
- Distribution of Familia values
- List of all comercials with sales counts
- Unusually high Faturação values (outliers)
- Data quality statistics

### 2. Access the System

**Admin Dashboard Access:**
Navigate to: `http://localhost:5000/data-health`

Or add this link to your admin panel (recommended - see implementation below)

**When to Check:**
- ✅ After adding new month data to Google Sheet
- ✅ Weekly as part of data quality routine
- ✅ Before generating important reports
- ✅ When calculations seem off

### 3. How It Works

The validation system:
1. Fetches current data from Google Sheets
2. Runs 8 different validation checks
3. Categorizes issues by severity (Critical/Warning/Info)
4. Shows affected row numbers
5. Provides actionable recommendations for each issue
6. Displays data health status (HEALTHY/WARNING/CRITICAL)

### 4. Understanding the Report

**Health Status:**
- 🎉 **HEALTHY**: All checks passed, data is clean
- ⚠️ **WARNING**: Some issues found, review recommended
- 🚨 **CRITICAL**: Serious issues detected, immediate action needed

**Issue Cards Show:**
- **Type**: Technical identifier (e.g., `INVALID_DATES`, `FAMILIA_SPELLING`)
- **Message**: Clear description of the issue
- **Affected Rows**: Specific sheet row numbers with problems
- **Samples**: Examples of problematic data
- **Recommendation**: Exactly what to fix

## 📋 Usage Examples

### Example 1: Date Format Issue
```
🚨 CRITICAL
INVALID_DATES

12 rows with unparseable dates
Affected rows: 145, 167, 189, ...
Sample values: "March 2026", "3-2026", "2026,03"

💡 Recommendation: Use format YYYY/MM or MM/YYYY. 
Examples: 2026/03 or 03/2026
```

**Action**: Go to Google Sheet rows 145, 167, 189 and fix the date format.

### Example 2: Familia Spelling Variant
```
⚠️ WARNING
FAMILIA_SPELLING

Found 3 non-standard Urnas spellings
Variants: urna nova, urnas antigas, URNAS
Counts: 45, 12, 8

💡 Recommendation: Standardize to exactly "Urnas" or "Urna" 
(case insensitive)
```

**Action**: Use Find & Replace in Google Sheet to standardize spellings.

### Example 3: Missing Values
```
⚠️ WARNING  
MISSING_CLIENTE

23 rows missing Cliente
Affected rows: 501, 502, 503, ...
Percentage: 1.8% of data

💡 Recommendation: Fill in missing Cliente values in sheet
```

**Action**: Review those rows and add missing client names.

## 🔧 Integration with Dashboard

### Add Health Status Indicator

To add a data health indicator to your admin dashboard, add this code to the dashboard route (for admins only):

```html
<!-- Data Health Quick Check (Admin Only) -->
<div class="admin-panel" style="margin-top: 20px; padding: 20px; background: white; border-radius: 8px;">
    <h3 style="margin-bottom: 15px;">🔍 Data Health Status</h3>
    <p style="color: #64748b; margin-bottom: 15px;">
        Monitor Google Sheet data quality and integrity
    </p>
    <a href="/data-health" style="display: inline-block; padding: 12px 24px; background: #667eea; color: white; text-decoration: none; border-radius: 6px; font-weight: 600;">
        View Full Health Report →
    </a>
    <p style="font-size: 12px; color: #94a3b8; margin-top: 10px;">
        Last check: Updated on pageload
    </p>
</div>
```

## 🚨 Alert System (Optional Enhancement)

### Future: Email Notifications
You can extend the system to send email alerts for critical issues:

```python
# In data-health route, after validation:
if critical_count > 0:
    send_admin_alert(
        subject="🚨 Data Health: Critical Issues Detected",
        issues=results['critical']
    )
```

This would notify admins immediately when critical issues are detected.

## 📊 Validation Logic

### What Gets Checked

1. **Column Existence**: Required columns must be present
2. **Date Parsing**: Dates must match YYYY/MM or MM/YYYY format
3. **Numeric Values**: Faturação and Quant must be valid numbers
4. **Familia Consistency**: Checks for spelling variants (especially urnas)
5. **Missing Data**: Flags rows with empty critical fields
6. **Duplicates**: Detects exact duplicate rows
7. **Outliers**: Identifies unusually high values (>3 standard deviations)
8. **Name Consistency**: Looks for similar comercial names (typos)

### Validation Thresholds

- **Critical**: Issues affecting >10% of data or blocking calculations
- **Warning**: Issues affecting <10% of data or minor inconsistencies
- **Info**: Statistical information for review

## 🛠️ Maintenance Workflow

### Monthly (After Sheet Update)
1. Add new month data to Google Sheet
2. Visit `/data-health` to validate
3. Fix any Critical issues immediately
4. Review Warnings when convenient
5. Clear cache: `/clear-cache`
6. Verify calculations in `/performance`

### Weekly
- Quick health check to catch data entry errors early
- Review Info section for data quality trends

### As Needed
- Before important reports or presentations
- When users report calculation discrepancies
- After bulk data imports or sheet restructuring

## 📁 Technical Files

### Created Files
- `data_validation.py`: Validation logic module
- `MONTHLY_UPDATE_GUIDE.md`: Comprehensive update procedures
- This file: `DATA_VALIDATION_GUIDE.md`

### Modified Files
- `app.py`: Added `/data-health` route with DataValidator integration

### Dependencies
All existing - no new packages required:
- pandas (data manipulation)
- re (regex for date parsing)
- collections.Counter (frequency analysis)

## 🎯 Success Criteria

Your data is production-ready when:
- ✅ Health status shows HEALTHY
- ✅ Zero Critical issues
- ✅ Zero or minimal Warnings
- ✅ All calculations match manual verification

## ⚡ Quick Reference

| Issue Type | Severity | Typical Fix Time | Action Required |
|------------|----------|------------------|-----------------|
| MISSING_COLUMNS | 🚨 Critical | 1 hour | Add columns to sheet |
| INVALID_DATES | 🚨 Critical | 15-30 min | Fix date formatting |
| FAMILIA_SPELLING | ⚠️ Warning | 10-15 min | Use Find & Replace |
| MISSING_VALUES | ⚠️ Warning | Variable | Fill in data |
| OUTLIERS | ℹ️ Info | 5 min | Verify values correct |
| DUPLICATES | ⚠️ Warning | 10 min | Remove duplicates |

## 💡 Pro Tips

1. **Fix Critical issues first** - they block calculations
2. **Use Google Sheet Find & Replace** for bulk spelling fixes
3. **Check health before month-end** reporting
4. **Document recurring issues** in a separate log
5. **Set calendar reminder** for weekly health checks

---

**Last Updated**: March 2026  
**System Status**: Active  
**Access Level**: Admin only