# 📅 Monthly Update Guide - Sales Dashboard

## Overview
This guide ensures the app remains stable and accurate as new monthly data is added to the Google Sheet.

## ✅ What's Already Protected

### 1. **Dynamic Year Handling**
- ✅ All year references use `datetime.now().year` (current year)
- ✅ Previous year calculated as `current_year - 1`
- ✅ Performance labels show dynamic years (e.g., "META {current_year}")
- ✅ No hardcoded year filters in production calculations

### 2. **Robust Date Parsing**
- ✅ Supports multiple date formats: `YYYY/MM`, `YYYY-MM`, `MM/YYYY`
- ✅ Handles missing/null/empty date values safely
- ✅ Validates month range (1-12)
- ✅ Logs unparsed dates for debugging

### 3. **Familia Filtering (Urnas)**
- ✅ Centralized `filter_urnas_family_rows()` helper function
- ✅ Strict primary matching: 'urna' or 'urnas' (exact, case-insensitive)
- ✅ Fallback regex for legacy data: `\burnas?\b`
- ✅ Applied consistently across all 13+ calculation points

### 4. **Numeric Parsing**
- ✅ Handles European format (comma decimal: `1.234,56`)
- ✅ Handles US format (dot decimal: `1,234.56`)
- ✅ Safely converts to numeric with `pd.to_numeric(..., errors='coerce')`
- ✅ Returns 0 for invalid/missing values

## 📋 Monthly Update Checklist

### Before Adding New Month Data
1. **Verify Date Format Consistency**
   - Ensure new month follows existing format (e.g., `2026/03` or `03/2026`)
   - Check for typos, extra spaces, or inconsistent separators

2. **Test Data Quality**
   - Run a test import on a copy of the sheet first
   - Verify Familia column has exact values: `Urna`, `Urnas`, or other families
   - Check numeric columns have proper formatting (no text mixed with numbers)

### After Adding New Month Data
1. **Clear Application Cache**
   ```python
   # Navigate to: http://localhost:5000/clear-cache
   # Or restart the Flask app
   ```

2. **Verify Performance Calculations**
   - Open `/performance` route
   - Check that new month data appears in current year totals
   - Verify urnas quantities match manual Google Sheet sum
   - Compare revenue totals with Looker Studio (if available)

3. **Check Objectives Progress**
   - Verify percentage completion updates correctly
   - Ensure "Falta para meta" calculations are accurate
   - Check year-over-year growth percentages

4. **Test Dashboard Filters**
   - Apply filters by comercial, zone, client
   - Verify all data displays correctly with new month included

## 🔍 Key Areas to Monitor

### 1. Date Column (`Mês`)
- **Current Logic**: Parses YYYY/MM or MM/YYYY formats
- **What Could Break**: 
  - New format introduced (e.g., `March 2026`, `03-Mar-2026`)
  - Extra whitespace or special characters
  - Missing date values in new rows
- **Fix**: Check app logs for `[PARSE WARNING]` messages indicating unparsed dates

### 2. Familia Column
- **Current Logic**: Strict match `urna`/`urnas` (case-insensitive, trimmed)
- **What Could Break**:
  - Typos: `Una`, `URNAS `, `Urna Nova`
  - New product families added without documentation
- **Fix**: Verify exact spelling in Google Sheet matches expected values

### 3. Numeric Columns (`Faturação`, `Quant`)
- **Current Logic**: European format with comma as decimal separator
- **What Could Break**:
  - Mixed formats in sheet (some rows use dot, some use comma)
  - Text values mixed with numbers (`€1.234,56` if € symbol not stripped)
  - Formula errors in sheet showing `#DIV/0!` or `#VALUE!`
- **Fix**: Use `parse_number()` function which handles multiple formats

### 4. Performance vs Objectives
- **Current Logic**: Compares current year sales against objectives sheet targets
- **What Could Break**:
  - Objectives sheet not updated for new year
  - Comercial names mismatch between sheets
  - Missing "Total" row in objectives
- **Fix**: Run `/setup-objectives` to regenerate objectives for new year

## 🚨 Error Scenarios & Solutions

### Scenario 1: "No data showing for current month"
**Cause**: Cache not cleared after sheet update
**Solution**: 
```
1. Navigate to http://localhost:5000/clear-cache
2. Hard refresh browser (Ctrl+F5)
```

### Scenario 2: "Urnas total is wrong"
**Cause**: Familia column has typos or unexpected values
**Solution**:
```
1. Check Google Sheet Familia column for exact spelling
2. Look for app logs: [PARSE WARNING] messages
3. Update sheet to use exact "Urnas" or "Urna" values
4. Clear cache and refresh
```

### Scenario 3: "Performance percentages stuck at old values"
**Cause**: Browser cached old HTML or objectives not updated
**Solution**:
```
1. Clear app cache: /clear-cache
2. Logout and login again
3. Hard refresh browser (Ctrl+F5)
4. If still wrong, regenerate objectives: /setup-objectives
```

### Scenario 4: "New month data not parsed (shows in raw but not dashboard)"
**Cause**: Date format not recognized by parse_period()
**Solution**:
```
1. Check app terminal for [PARSE WARNING] messages
2. Identify the problematic date format
3. Update parse_period() function to handle new format
4. Or standardize sheet date format to YYYY/MM
```

## 🔧 Maintenance Tips

### Quarterly Tasks
- Review app logs for recurring [PARSE WARNING] messages
- Audit Familia column values for consistency
- Verify all comercial names match between sales and objectives sheets

### Annual Tasks (Year Transition)
1. **Generate New Year Objectives** (December/January)
   - Navigate to `/setup-objectives`
   - Set growth targets or absolute values per scope
   - Verify baseline year is correctly identified

2. **Verify Year Filters**
   - Confirm dashboard shows new year as "current"
   - Check performance comparisons use correct previous year
   - Test historical trends include new year data

3. **Archive Old Data (Optional)**
   - Consider moving data older than 3-4 years to archive sheet
   - Update historical year cutoff if needed (currently ≥2022)

## 📊 Data Quality Validation

### Manual Spot Checks
Access test route to verify totals: `http://localhost:5000/test-data`

This returns:
```json
{
  "total_rows": 12500,
  "total_faturacao_all": 15000000.00,
  "total_faturacao_2025": 6005182.69,
  "previous_year": "2025",
  "columns_found": {...}
}
```

Compare against:
- Google Sheet SUM() formulas
- Looker Studio totals (if available)
- Previous month cumulative + new month = new cumulative

### Automated Checks (Future Enhancement)
Consider adding:
- Daily scheduled data integrity checks
- Email alerts for parsing warnings
- Dashboard health status indicator

## 📝 Notes for Developers

### Code Locations
- **Date Parsing**: `parse_period()` function (~7 instances - consider centralizing)
- **Urnas Filtering**: `filter_urnas_family_rows()` (line ~520)
- **Numeric Parsing**: `parse_number()` (line ~465)
- **Performance Calculations**: `calculate_performance()` (line ~775)
- **Dynamic Year Labels**: `/performance` route (line ~5780+)

### Best Practices
1. Always use `datetime.now().year` for current year
2. Use `filter_urnas_family_rows()` for familia filtering (never inline `.str.contains()`)
3. Use `pd.to_numeric(..., errors='coerce')` for numeric columns
4. Handle empty DataFrames with `.empty` checks before operations
5. Log warnings for unparsed data rather than failing silently

### Testing Monthly Updates
```python
# Local testing workflow:
1. Update test Google Sheet with new month data
2. Clear cache: visit /clear-cache
3. Check logs for parsing warnings
4. Visit /performance and verify totals
5. Compare with manual sheet calculations
6. If correct, apply to production sheet
```

## 🎯 Success Criteria

After each monthly update, verify:
- [ ] No Python errors in app terminal
- [ ] No [PARSE WARNING] messages for new month
- [ ] Dashboard shows new month data in current year section
- [ ] Urnas totals match manual Google Sheet SUM()
- [ ] Performance percentages update correctly
- [ ] Historical trends include all months up to current
- [ ] All filters (comercial, zone, client) work correctly

---

**Last Updated**: March 2026  
**Maintained By**: Development Team  
**Questions**: Check app logs first, then review this guide