Retail Trade Report Generator

# Retail Trade Weekly Report Generator - Skill Documentation

## Overview
This skill processes multiple weekly sales report Excel files to generate a consolidated Retail Trade Weekly Report with week-over-week (WoW) comparisons across different channels (DRP, DXS, License Store) and product types (Mobile Prepaid/Postpaid, FWA 4G/5G).

## Purpose
- Consolidate 12 Excel files (6 current week + 6 previous week) into a single comprehensive weekly report
- Calculate Average Daily Acquisition (ADA) metrics by region and channel
- Compute Week-over-Week (WoW) performance indicators
- Generate formatted Excel output with charts and color-coded performance indicators

## Input Requirements

### Required Files (12 total)
**Current Week (6 files):**
1. `DRP_Channel_Sales_Report_DRP_M_DD-M_DD.xlsx`
2. `DRP_Special_SIM_Monitor_Report_Daily_TECNO_M_DD-M_DD.xlsx`
3. `License_Store_Performance_Monitor_Report_LS_M_DD-M_DD.xlsx`
4. `DXS_Acquisition_Report_Mobile_Prepaid_M_DD-M_DD.xlsx`
5. `DXS_Acquisition_Report_Mobile_Postpaid_M_DD-M_DD.xlsx`
6. `DXS_Acquisition_Report_FWA_M_DD-M_DD.xlsx`

**Previous Week (6 files with earlier dates):**
Same file types with earlier date ranges in filename

### Store Mapping CSV
File containing Store Name to Region mapping with aliases support:
```csv
Store Name,Region,Aliases
SM Megamall,NCR,"Megamall|SM Mega|MEGAMALL"
...
```

## Data Processing Logic

### 1. File Identification
- Extract date ranges from filenames (format: `M_DD-M_DD`)
- Auto-group into current week vs previous week based on date comparison
- Validate all 12 required files are present

### 2. Data Extraction Rules

#### DRP Channel Sales Report
- **Header rows:** Skip rows 0-7
- **Data rows:** Start from row 8
- **Region field:** Column 0 (AREA)
- **Key columns:**
  - Column 1: MOBILE POSTPAID > TOTAL ACTIVATION
  - Column 5: MOBILE PREPAID > TOTAL ACTIVATION
  - Column 6: Double Data_Sum
  - Column 9: 4G WiFi 980 SIM_Sum (FWA 4G)
  - Column 10: Unli 5G WIFI 100Mbps Starter SIM_Sum (FWA 5G)
  - Column 11: 5G WiFi 4990 SIM_Sum (FWA 5G)

#### DRP TECNO Report
- **Header rows:** Skip rows 0-6
- **Data rows:** Start from row 7
- **Region field:** Column 0 (Activation Area)
- **Key columns:**
  - Column 1: CARMON Activation (CAMON 40)
  - Column 2: POVA Activation (POVA 7)
  - Column 3: Total Activation (TECNO ADA = CAMON 40 + POVA 7)

#### License Store Report
- **Header rows:** Skip rows 0-7
- **Data rows:** Start from row 8
- **Store field:** Column 0 (Store Name) - **Requires mapping to Region**
- **Key columns:**
  - Column 1: Mobile Prepaid
  - Column 3: Mobile Postpaid
  - Column 29 (AD): DITO Home Prepaid 4G WiFi 980 SIM (FWA 4G)
  - Need to find: Unli 5G WIFI 100Mbps Starter SIM (FWA 5G)
  - Need to find: 5G WiFi 4990 SIM (FWA 5G)

#### DXS Mobile Prepaid Report
- **Header rows:** Skip rows 0-7
- **Data rows:** Start from row 8
- **Store field:** Column 0 (DXS Name) - **Requires mapping to Region**
- **Key column:**
  - Column 4: Total

#### DXS Mobile Postpaid Report
- **Header rows:** Skip rows 0-7
- **Data rows:** Start from row 8
- **Store field:** Column 0 (DXS Name) - **Requires mapping to Region**
- **Key column:**
  - Column 12: Total

#### DXS FWA Report
- **Header rows:** Skip rows 0-7
- **Data rows:** Start from row 8
- **Store field:** Column 0 (DXS Name) - **Requires mapping to Region**
- **Key columns:**
  - Column 1: DITO Home Prepaid 4G WiFi 980 (FWA 4G)
  - Column 18: Total
  - **FWA 5G calculation:** Total (Col 18) - 4G (Col 1)

### 3. Store Name to Region Mapping
```python
# Build mapping dictionary from CSV
store_mapping = {}
for row in mapping_csv:
    main_name = row['Store Name']
    region = row['Region']
    aliases = row['Aliases'].split('|') if row['Aliases'] else []
    
    # Add main name and all aliases to mapping
    store_mapping[main_name.upper()] = region
    for alias in aliases:
        store_mapping[alias.strip().upper()] = region

# Apply fuzzy matching for unmatched stores
def map_store_to_region(store_name):
    # Exact match (case-insensitive)
    if store_name.upper() in store_mapping:
        return store_mapping[store_name.upper()]
    
    # Fuzzy match using substring search
    for key in store_mapping:
        if key in store_name.upper() or store_name.upper() in key:
            return store_mapping[key]
    
    # Default to "Others" if no match
    return "Others"
```

### 4. Regional Aggregation

**Standard Regions:** NCR, SLZ, NLZ, CLZ, EVIS, WVIS, MIN, Others

For each product type and region:
```python
# DRP data: Direct mapping (already by region)
DRP_ADA = drp_data[region][product_column]

# DXS data: Aggregate stores by region
DXS_ADA = sum(dxs_data[store][product_column] 
              for store in dxs_data 
              if map_store_to_region(store) == region)

# LS data: Aggregate stores by region
LS_ADA = sum(ls_data[store][product_column] 
             for store in ls_data 
             if map_store_to_region(store) == region)

# Total for region
RT_Total_ADA = DRP_ADA + DXS_ADA + LS_ADA
```

### 5. WoW Calculation
```python
WoW = (current_week_value - previous_week_value) / previous_week_value

# Formatting rules:
# - Display as percentage (e.g., "21%", "-13%")
# - Round to nearest integer
# - Handle division by zero: display "-" if previous_week_value == 0
# - Handle cases where current = 0 and previous > 0: show "-100%"
```

### 6. Special Calculations

#### FWA 5G Components
```python
# DRP FWA 5G
DRP_FWA_5G = Column_10 + Column_11

# DXS FWA 5G
DXS_FWA_5G = Total - Column_1_4G

# LS FWA 5G
LS_FWA_5G = Unli_5G_WIFI_100Mbps + WiFi_4990_SIM
```

#### TECNO ADA
```python
TECNO_ADA = CAMON_40 + POVA_7
```

## Output Format

### Excel Structure
**Single Sheet:** "Weekly Report"

**Sections:**
1. Report Header (Rows 1-2)
   - Title: "Retail Trade Weekly Report"
   - Date ranges: "Last Week: [dates] | This Week: [dates]"

2. Channel Summary (Rows 4-9)
   - Columns: Channel | Program name | This Week ADA | WoW | MoM
   - Rows: DRP BAU, DRP TECNO, License Store, DXS, RT Total

3. Mobile Prepaid by Region (Rows 11-21)
   - Columns: Region | RT Total ADA | WoW | DXS ADA | WoW | LS ADA | WoW | DRP ADA | WoW
   - Rows: 8 regions + Total

4. DRP Prepaid Program (Rows 23-33)
   - Columns: Region | Double Data ADA | WoW | TECNO ADA | WoW | CAMON 40 | WoW | POVA 7 | WoW
   - Rows: 8 regions + Total

5. Mobile Postpaid by Region (Rows 35-45)
   - Same structure as Mobile Prepaid

6. FWA 4G by Region (Rows 47-57)
   - Same structure as Mobile Prepaid

7. FWA 5G by Region (Rows 59-69)
   - Same structure as Mobile Prepaid

### Formatting Rules

#### Number Formatting
- ADA values: Integer format with thousand separators (e.g., "1,876")
- WoW percentages: Integer percentage (e.g., "21%", "-13%")
- Small ADA values (< 10): Show 1 decimal place (e.g., "0.6", "2.9")

#### Color Coding
- **WoW Positive (>0%):** Green text (#008000)
- **WoW Negative (<0%):** Red text (#FF0000)
- **WoW Zero (0%):** Black text
- **WoW N/A ("-"):** Gray text (#808080)

#### Cell Styling
- **Headers:** Bold, centered, light gray background (#F0F0F0)
- **Region names:** Bold
- **Total rows:** Bold, light blue background (#E6F2FF)
- **Borders:** Thin borders around all data cells

### Charts

**Chart 1: Channel Performance Comparison**
- Type: Clustered Column Chart
- Data: This Week ADA by Channel (DRP BAU, DRP TECNO, License Store, DXS)
- Position: Right side of Channel Summary section
- Size: 6 columns wide x 15 rows tall

**Chart 2: Regional Mobile Prepaid Distribution**
- Type: Stacked Column Chart
- Data: DRP ADA, DXS ADA, LS ADA by Region
- Position: Right side of Mobile Prepaid section
- Size: 6 columns wide x 15 rows tall

**Chart 3: WoW Trend - Top 3 Regions**
- Type: Line Chart with Markers
- Data: WoW % for top 3 regions by RT Total ADA
- Position: Below main tables
- Size: 12 columns wide x 12 rows tall

## Error Handling

### Missing Files
```python
if len(current_week_files) != 6:
    raise ValueError(f"Expected 6 current week files, found {len(current_week_files)}")

if len(previous_week_files) != 6:
    raise ValueError(f"Expected 6 previous week files, found {len(previous_week_files)}")
```

### Unmapped Stores
```python
unmapped_stores = []
for store in all_stores:
    if map_store_to_region(store) == "Others":
        # Log warning but continue processing
        unmapped_stores.append(store)

if unmapped_stores:
    print(f"Warning: {len(unmapped_stores)} stores mapped to 'Others' region")
```

### Data Quality Checks
```python
# Check for negative values
if any_value < 0:
    print(f"Warning: Negative value found in {file}:{column}")

# Check for missing regions
expected_regions = {"NCR", "SLZ", "NLZ", "CLZ", "EVIS", "WVIS", "MIN", "Others"}
missing_regions = expected_regions - set(actual_regions)
if missing_regions:
    print(f"Warning: Missing regions: {missing_regions}")
```

## Implementation Notes

### Python Libraries
```python
import pandas as pd
import openpyxl
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.chart import BarChart, LineChart, Reference
import re
from datetime import datetime
```

### Key Functions

#### 1. File Parser
```python
def extract_date_from_filename(filename):
    """Extract date range from filename like 'Report_1_11-1_17.xlsx'"""
    pattern = r'_(\d+)_(\d+)-(\d+)_(\d+)\.xlsx'
    match = re.search(pattern, filename)
    if match:
        start_month, start_day, end_month, end_day = match.groups()
        return (int(start_month), int(start_day), int(end_month), int(end_day))
    return None

def identify_file_type(filename):
    """Identify file type from filename"""
    if 'DRP_Channel_Sales' in filename:
        return 'DRP'
    elif 'TECNO' in filename:
        return 'TECNO'
    elif 'License_Store' in filename:
        return 'LS'
    elif 'Mobile_Prepaid' in filename:
        return 'DXS_Prepaid'
    elif 'Mobile_Postpaid' in filename:
        return 'DXS_Postpaid'
    elif 'FWA' in filename:
        return 'DXS_FWA'
    return 'Unknown'
```

#### 2. Data Extractor
```python
def extract_drp_data(filepath):
    """Extract DRP channel sales data"""
    df = pd.read_excel(filepath, sheet_name='Sheet0', header=None)
    
    # Find data start row (usually row 8)
    data_start = 8
    
    # Extract by region
    regions_data = {}
    for idx in range(data_start, len(df)):
        region = df.iloc[idx, 0]
        if pd.isna(region) or region == 'Total':
            continue
            
        regions_data[region] = {
            'mobile_postpaid': df.iloc[idx, 1],
            'mobile_prepaid': df.iloc[idx, 5],
            'double_data': df.iloc[idx, 6],
            'fwa_4g': df.iloc[idx, 9],
            'fwa_5g': df.iloc[idx, 10] + df.iloc[idx, 11]
        }
    
    return regions_data

def extract_dxs_data(filepath, product_type):
    """Extract DXS acquisition data"""
    df = pd.read_excel(filepath, sheet_name='Sheet1', header=None)
    
    # Determine column based on product type
    if product_type == 'prepaid':
        value_col = 4
    elif product_type == 'postpaid':
        value_col = 12
    elif product_type == 'fwa':
        return extract_dxs_fwa_data(df)
    
    stores_data = {}
    for idx in range(8, len(df)):
        store = df.iloc[idx, 0]
        if pd.isna(store) or store in ['Grand Total', '-']:
            continue
        
        value = df.iloc[idx, value_col]
        if pd.notna(value):
            stores_data[store] = value
    
    return stores_data

def extract_dxs_fwa_data(df):
    """Extract FWA data with 4G/5G split"""
    stores_data = {}
    for idx in range(8, len(df)):
        store = df.iloc[idx, 0]
        if pd.isna(store) or store in ['Grand Total', '-']:
            continue
        
        fwa_4g = df.iloc[idx, 1] if pd.notna(df.iloc[idx, 1]) else 0
        total = df.iloc[idx, 18] if pd.notna(df.iloc[idx, 18]) else 0
        fwa_5g = total - fwa_4g
        
        stores_data[store] = {
            'fwa_4g': fwa_4g,
            'fwa_5g': fwa_5g
        }
    
    return stores_data
```

#### 3. Region Aggregator
```python
def aggregate_by_region(stores_data, mapping_dict, regions):
    """Aggregate store data by region"""
    regional_totals = {region: 0 for region in regions}
    
    for store, value in stores_data.items():
        region = map_store_to_region(store, mapping_dict)
        if isinstance(value, dict):
            # Handle nested data (e.g., FWA with 4G/5G)
            for key in value:
                if key not in regional_totals:
                    regional_totals[key] = {region: 0 for region in regions}
                regional_totals[key][region] += value[key]
        else:
            regional_totals[region] += value
    
    return regional_totals
```

#### 4. WoW Calculator
```python
def calculate_wow(current, previous):
    """Calculate week-over-week percentage change"""
    if previous == 0 or pd.isna(previous):
        return "-"
    
    if current == 0 or pd.isna(current):
        return "-100%"
    
    wow = ((current - previous) / previous) * 100
    return f"{int(round(wow))}%"
```

#### 5. Excel Formatter
```python
def apply_formatting(ws, start_row, start_col, end_row, end_col):
    """Apply formatting to Excel worksheet"""
    # Define styles
    header_fill = PatternFill(start_color="F0F0F0", end_color="F0F0F0", fill_type="solid")
    total_fill = PatternFill(start_color="E6F2FF", end_color="E6F2FF", fill_type="solid")
    
    green_font = Font(color="008000")
    red_font = Font(color="FF0000")
    gray_font = Font(color="808080")
    bold_font = Font(bold=True)
    
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    
    # Apply to cells
    for row in ws.iter_rows(min_row=start_row, max_row=end_row, 
                            min_col=start_col, max_col=end_col):
        for cell in row:
            cell.border = thin_border
            
            # Color code WoW values
            if isinstance(cell.value, str) and '%' in cell.value:
                try:
                    pct_value = int(cell.value.replace('%', ''))
                    if pct_value > 0:
                        cell.font = green_font
                    elif pct_value < 0:
                        cell.font = red_font
                except:
                    if cell.value == '-':
                        cell.font = gray_font

def add_chart(ws, chart_type, data_range, position, title):
    """Add chart to worksheet"""
    if chart_type == 'column':
        chart = BarChart()
    elif chart_type == 'line':
        chart = LineChart()
    
    chart.title = title
    chart.style = 10
    chart.height = 10
    chart.width = 15
    
    data = Reference(ws, min_col=data_range[0], min_row=data_range[1],
                     max_col=data_range[2], max_row=data_range[3])
    chart.add_data(data, titles_from_data=True)
    
    ws.add_chart(chart, position)
```

## Usage Example

```python
from retail_trade_report_skill import generate_weekly_report

# Input files directory
input_dir = "/mnt/user-data/uploads/"

# Store mapping CSV
mapping_file = "/mnt/user-data/uploads/store_mapping.csv"

# Generate report
output_file = generate_weekly_report(
    input_dir=input_dir,
    mapping_csv=mapping_file,
    output_path="/mnt/user-data/outputs/Retail_Trade_Weekly_Report.xlsx"
)

print(f"Report generated: {output_file}")
```

## Validation Checklist

Before finalizing output:
- [ ] All 12 input files identified and processed
- [ ] Date ranges correctly extracted and displayed
- [ ] All stores mapped to regions (log unmapped as "Others")
- [ ] All WoW calculations completed
- [ ] No negative ADA values (except in error logs)
- [ ] All formulas validated against sample data
- [ ] Charts render correctly
- [ ] Color coding applied to all WoW cells
- [ ] Total rows sum correctly
- [ ] Output file opens without errors

## Performance Considerations

- Expected processing time: 10-30 seconds for 12 files
- Memory usage: ~50-100 MB
- Large file handling: Files up to 10MB each supported
- Concurrent processing: Process files in parallel where possible

## Troubleshooting

### Common Issues

**Issue:** "File not found" error
- **Solution:** Verify all 12 files are uploaded and filenames match expected pattern

**Issue:** Store name not mapping to region
- **Solution:** Check mapping CSV for typos, add aliases for common variations

**Issue:** WoW showing "N/A" for all values
- **Solution:** Verify previous week files are correctly identified (earlier dates)

**Issue:** Charts not displaying
- **Solution:** Check openpyxl version >= 3.0, verify chart data ranges

**Issue:** Negative ADA values
- **Solution:** Check source data for errors, verify column indices

## Version History

- **v1.0** (2026-02-02): Initial skill creation
  - Support for 12-file weekly report generation
  - WoW calculations with color coding
  - Store-to-region mapping with aliases
  - Three chart types for visualization