{"skill":{"slug":"retail-trade-report-generator","displayName":"retail-trade-report-generator","summary":"Generates a consolidated weekly retail trade report by processing 12 Excel sales files, mapping stores to regions, calculating ADA metrics, WoW comparisons,...","description":"# Retail Trade Weekly Report Generator - Skill Documentation\n\n## Overview\nThis 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).\n\n## Purpose\n- Consolidate 12 Excel files (6 current week + 6 previous week) into a single comprehensive weekly report\n- Calculate Average Daily Acquisition (ADA) metrics by region and channel\n- Compute Week-over-Week (WoW) performance indicators\n- Generate formatted Excel output with charts and color-coded performance indicators\n\n## Input Requirements\n\n### Required Files (12 total)\n**Current Week (6 files):**\n1. `DRP_Channel_Sales_Report_DRP_M_DD-M_DD.xlsx`\n2. `DRP_Special_SIM_Monitor_Report_Daily_TECNO_M_DD-M_DD.xlsx`\n3. `License_Store_Performance_Monitor_Report_LS_M_DD-M_DD.xlsx`\n4. `DXS_Acquisition_Report_Mobile_Prepaid_M_DD-M_DD.xlsx`\n5. `DXS_Acquisition_Report_Mobile_Postpaid_M_DD-M_DD.xlsx`\n6. `DXS_Acquisition_Report_FWA_M_DD-M_DD.xlsx`\n\n**Previous Week (6 files with earlier dates):**\nSame file types with earlier date ranges in filename\n\n### Store Mapping CSV\nFile containing Store Name to Region mapping with aliases support:\n```csv\nStore Name,Region,Aliases\nSM Megamall,NCR,\"Megamall|SM Mega|MEGAMALL\"\n...\n```\n\n## Data Processing Logic\n\n### 1. File Identification\n- Extract date ranges from filenames (format: `M_DD-M_DD`)\n- Auto-group into current week vs previous week based on date comparison\n- Validate all 12 required files are present\n\n### 2. Data Extraction Rules\n\n#### DRP Channel Sales Report\n- **Header rows:** Skip rows 0-7\n- **Data rows:** Start from row 8\n- **Region field:** Column 0 (AREA)\n- **Key columns:**\n  - Column 1: MOBILE POSTPAID > TOTAL ACTIVATION\n  - Column 5: MOBILE PREPAID > TOTAL ACTIVATION\n  - Column 6: Double Data_Sum\n  - Column 9: 4G WiFi 980 SIM_Sum (FWA 4G)\n  - Column 10: Unli 5G WIFI 100Mbps Starter SIM_Sum (FWA 5G)\n  - Column 11: 5G WiFi 4990 SIM_Sum (FWA 5G)\n\n#### DRP TECNO Report\n- **Header rows:** Skip rows 0-6\n- **Data rows:** Start from row 7\n- **Region field:** Column 0 (Activation Area)\n- **Key columns:**\n  - Column 1: CARMON Activation (CAMON 40)\n  - Column 2: POVA Activation (POVA 7)\n  - Column 3: Total Activation (TECNO ADA = CAMON 40 + POVA 7)\n\n#### License Store Report\n- **Header rows:** Skip rows 0-7\n- **Data rows:** Start from row 8\n- **Store field:** Column 0 (Store Name) - **Requires mapping to Region**\n- **Key columns:**\n  - Column 1: Mobile Prepaid\n  - Column 3: Mobile Postpaid\n  - Column 29 (AD): DITO Home Prepaid 4G WiFi 980 SIM (FWA 4G)\n  - Need to find: Unli 5G WIFI 100Mbps Starter SIM (FWA 5G)\n  - Need to find: 5G WiFi 4990 SIM (FWA 5G)\n\n#### DXS Mobile Prepaid Report\n- **Header rows:** Skip rows 0-7\n- **Data rows:** Start from row 8\n- **Store field:** Column 0 (DXS Name) - **Requires mapping to Region**\n- **Key column:**\n  - Column 4: Total\n\n#### DXS Mobile Postpaid Report\n- **Header rows:** Skip rows 0-7\n- **Data rows:** Start from row 8\n- **Store field:** Column 0 (DXS Name) - **Requires mapping to Region**\n- **Key column:**\n  - Column 12: Total\n\n#### DXS FWA Report\n- **Header rows:** Skip rows 0-7\n- **Data rows:** Start from row 8\n- **Store field:** Column 0 (DXS Name) - **Requires mapping to Region**\n- **Key columns:**\n  - Column 1: DITO Home Prepaid 4G WiFi 980 (FWA 4G)\n  - Column 18: Total\n  - **FWA 5G calculation:** Total (Col 18) - 4G (Col 1)\n\n### 3. Store Name to Region Mapping\n```python\n# Build mapping dictionary from CSV\nstore_mapping = {}\nfor row in mapping_csv:\n    main_name = row['Store Name']\n    region = row['Region']\n    aliases = row['Aliases'].split('|') if row['Aliases'] else []\n    \n    # Add main name and all aliases to mapping\n    store_mapping[main_name.upper()] = region\n    for alias in aliases:\n        store_mapping[alias.strip().upper()] = region\n\n# Apply fuzzy matching for unmatched stores\ndef map_store_to_region(store_name):\n    # Exact match (case-insensitive)\n    if store_name.upper() in store_mapping:\n        return store_mapping[store_name.upper()]\n    \n    # Fuzzy match using substring search\n    for key in store_mapping:\n        if key in store_name.upper() or store_name.upper() in key:\n            return store_mapping[key]\n    \n    # Default to \"Others\" if no match\n    return \"Others\"\n```\n\n### 4. Regional Aggregation\n\n**Standard Regions:** NCR, SLZ, NLZ, CLZ, EVIS, WVIS, MIN, Others\n\nFor each product type and region:\n```python\n# DRP data: Direct mapping (already by region)\nDRP_ADA = drp_data[region][product_column]\n\n# DXS data: Aggregate stores by region\nDXS_ADA = sum(dxs_data[store][product_column] \n              for store in dxs_data \n              if map_store_to_region(store) == region)\n\n# LS data: Aggregate stores by region\nLS_ADA = sum(ls_data[store][product_column] \n             for store in ls_data \n             if map_store_to_region(store) == region)\n\n# Total for region\nRT_Total_ADA = DRP_ADA + DXS_ADA + LS_ADA\n```\n\n### 5. WoW Calculation\n```python\nWoW = (current_week_value - previous_week_value) / previous_week_value\n\n# Formatting rules:\n# - Display as percentage (e.g., \"21%\", \"-13%\")\n# - Round to nearest integer\n# - Handle division by zero: display \"-\" if previous_week_value == 0\n# - Handle cases where current = 0 and previous > 0: show \"-100%\"\n```\n\n### 6. Special Calculations\n\n#### FWA 5G Components\n```python\n# DRP FWA 5G\nDRP_FWA_5G = Column_10 + Column_11\n\n# DXS FWA 5G\nDXS_FWA_5G = Total - Column_1_4G\n\n# LS FWA 5G\nLS_FWA_5G = Unli_5G_WIFI_100Mbps + WiFi_4990_SIM\n```\n\n#### TECNO ADA\n```python\nTECNO_ADA = CAMON_40 + POVA_7\n```\n\n## Output Format\n\n### Excel Structure\n**Single Sheet:** \"Weekly Report\"\n\n**Sections:**\n1. Report Header (Rows 1-2)\n   - Title: \"Retail Trade Weekly Report\"\n   - Date ranges: \"Last Week: [dates] | This Week: [dates]\"\n\n2. Channel Summary (Rows 4-9)\n   - Columns: Channel | Program name | This Week ADA | WoW | MoM\n   - Rows: DRP BAU, DRP TECNO, License Store, DXS, RT Total\n\n3. Mobile Prepaid by Region (Rows 11-21)\n   - Columns: Region | RT Total ADA | WoW | DXS ADA | WoW | LS ADA | WoW | DRP ADA | WoW\n   - Rows: 8 regions + Total\n\n4. DRP Prepaid Program (Rows 23-33)\n   - Columns: Region | Double Data ADA | WoW | TECNO ADA | WoW | CAMON 40 | WoW | POVA 7 | WoW\n   - Rows: 8 regions + Total\n\n5. Mobile Postpaid by Region (Rows 35-45)\n   - Same structure as Mobile Prepaid\n\n6. FWA 4G by Region (Rows 47-57)\n   - Same structure as Mobile Prepaid\n\n7. FWA 5G by Region (Rows 59-69)\n   - Same structure as Mobile Prepaid\n\n### Formatting Rules\n\n#### Number Formatting\n- ADA values: Integer format with thousand separators (e.g., \"1,876\")\n- WoW percentages: Integer percentage (e.g., \"21%\", \"-13%\")\n- Small ADA values (< 10): Show 1 decimal place (e.g., \"0.6\", \"2.9\")\n\n#### Color Coding\n- **WoW Positive (>0%):** Green text (#008000)\n- **WoW Negative (<0%):** Red text (#FF0000)\n- **WoW Zero (0%):** Black text\n- **WoW N/A (\"-\"):** Gray text (#808080)\n\n#### Cell Styling\n- **Headers:** Bold, centered, light gray background (#F0F0F0)\n- **Region names:** Bold\n- **Total rows:** Bold, light blue background (#E6F2FF)\n- **Borders:** Thin borders around all data cells\n\n### Charts\n\n**Chart 1: Channel Performance Comparison**\n- Type: Clustered Column Chart\n- Data: This Week ADA by Channel (DRP BAU, DRP TECNO, License Store, DXS)\n- Position: Right side of Channel Summary section\n- Size: 6 columns wide x 15 rows tall\n\n**Chart 2: Regional Mobile Prepaid Distribution**\n- Type: Stacked Column Chart\n- Data: DRP ADA, DXS ADA, LS ADA by Region\n- Position: Right side of Mobile Prepaid section\n- Size: 6 columns wide x 15 rows tall\n\n**Chart 3: WoW Trend - Top 3 Regions**\n- Type: Line Chart with Markers\n- Data: WoW % for top 3 regions by RT Total ADA\n- Position: Below main tables\n- Size: 12 columns wide x 12 rows tall\n\n## Error Handling\n\n### Missing Files\n```python\nif len(current_week_files) != 6:\n    raise ValueError(f\"Expected 6 current week files, found {len(current_week_files)}\")\n\nif len(previous_week_files) != 6:\n    raise ValueError(f\"Expected 6 previous week files, found {len(previous_week_files)}\")\n```\n\n### Unmapped Stores\n```python\nunmapped_stores = []\nfor store in all_stores:\n    if map_store_to_region(store) == \"Others\":\n        # Log warning but continue processing\n        unmapped_stores.append(store)\n\nif unmapped_stores:\n    print(f\"Warning: {len(unmapped_stores)} stores mapped to 'Others' region\")\n```\n\n### Data Quality Checks\n```python\n# Check for negative values\nif any_value < 0:\n    print(f\"Warning: Negative value found in {file}:{column}\")\n\n# Check for missing regions\nexpected_regions = {\"NCR\", \"SLZ\", \"NLZ\", \"CLZ\", \"EVIS\", \"WVIS\", \"MIN\", \"Others\"}\nmissing_regions = expected_regions - set(actual_regions)\nif missing_regions:\n    print(f\"Warning: Missing regions: {missing_regions}\")\n```\n\n## Implementation Notes\n\n### Python Libraries\n```python\nimport pandas as pd\nimport openpyxl\nfrom openpyxl.styles import Font, PatternFill, Alignment, Border, Side\nfrom openpyxl.chart import BarChart, LineChart, Reference\nimport re\nfrom datetime import datetime\n```\n\n### Key Functions\n\n#### 1. File Parser\n```python\ndef extract_date_from_filename(filename):\n    \"\"\"Extract date range from filename like 'Report_1_11-1_17.xlsx'\"\"\"\n    pattern = r'_(\\d+)_(\\d+)-(\\d+)_(\\d+)\\.xlsx'\n    match = re.search(pattern, filename)\n    if match:\n        start_month, start_day, end_month, end_day = match.groups()\n        return (int(start_month), int(start_day), int(end_month), int(end_day))\n    return None\n\ndef identify_file_type(filename):\n    \"\"\"Identify file type from filename\"\"\"\n    if 'DRP_Channel_Sales' in filename:\n        return 'DRP'\n    elif 'TECNO' in filename:\n        return 'TECNO'\n    elif 'License_Store' in filename:\n        return 'LS'\n    elif 'Mobile_Prepaid' in filename:\n        return 'DXS_Prepaid'\n    elif 'Mobile_Postpaid' in filename:\n        return 'DXS_Postpaid'\n    elif 'FWA' in filename:\n        return 'DXS_FWA'\n    return 'Unknown'\n```\n\n#### 2. Data Extractor\n```python\ndef extract_drp_data(filepath):\n    \"\"\"Extract DRP channel sales data\"\"\"\n    df = pd.read_excel(filepath, sheet_name='Sheet0', header=None)\n    \n    # Find data start row (usually row 8)\n    data_start = 8\n    \n    # Extract by region\n    regions_data = {}\n    for idx in range(data_start, len(df)):\n        region = df.iloc[idx, 0]\n        if pd.isna(region) or region == 'Total':\n            continue\n            \n        regions_data[region] = {\n            'mobile_postpaid': df.iloc[idx, 1],\n            'mobile_prepaid': df.iloc[idx, 5],\n            'double_data': df.iloc[idx, 6],\n            'fwa_4g': df.iloc[idx, 9],\n            'fwa_5g': df.iloc[idx, 10] + df.iloc[idx, 11]\n        }\n    \n    return regions_data\n\ndef extract_dxs_data(filepath, product_type):\n    \"\"\"Extract DXS acquisition data\"\"\"\n    df = pd.read_excel(filepath, sheet_name='Sheet1', header=None)\n    \n    # Determine column based on product type\n    if product_type == 'prepaid':\n        value_col = 4\n    elif product_type == 'postpaid':\n        value_col = 12\n    elif product_type == 'fwa':\n        return extract_dxs_fwa_data(df)\n    \n    stores_data = {}\n    for idx in range(8, len(df)):\n        store = df.iloc[idx, 0]\n        if pd.isna(store) or store in ['Grand Total', '-']:\n            continue\n        \n        value = df.iloc[idx, value_col]\n        if pd.notna(value):\n            stores_data[store] = value\n    \n    return stores_data\n\ndef extract_dxs_fwa_data(df):\n    \"\"\"Extract FWA data with 4G/5G split\"\"\"\n    stores_data = {}\n    for idx in range(8, len(df)):\n        store = df.iloc[idx, 0]\n        if pd.isna(store) or store in ['Grand Total', '-']:\n            continue\n        \n        fwa_4g = df.iloc[idx, 1] if pd.notna(df.iloc[idx, 1]) else 0\n        total = df.iloc[idx, 18] if pd.notna(df.iloc[idx, 18]) else 0\n        fwa_5g = total - fwa_4g\n        \n        stores_data[store] = {\n            'fwa_4g': fwa_4g,\n            'fwa_5g': fwa_5g\n        }\n    \n    return stores_data\n```\n\n#### 3. Region Aggregator\n```python\ndef aggregate_by_region(stores_data, mapping_dict, regions):\n    \"\"\"Aggregate store data by region\"\"\"\n    regional_totals = {region: 0 for region in regions}\n    \n    for store, value in stores_data.items():\n        region = map_store_to_region(store, mapping_dict)\n        if isinstance(value, dict):\n            # Handle nested data (e.g., FWA with 4G/5G)\n            for key in value:\n                if key not in regional_totals:\n                    regional_totals[key] = {region: 0 for region in regions}\n                regional_totals[key][region] += value[key]\n        else:\n            regional_totals[region] += value\n    \n    return regional_totals\n```\n\n#### 4. WoW Calculator\n```python\ndef calculate_wow(current, previous):\n    \"\"\"Calculate week-over-week percentage change\"\"\"\n    if previous == 0 or pd.isna(previous):\n        return \"-\"\n    \n    if current == 0 or pd.isna(current):\n        return \"-100%\"\n    \n    wow = ((current - previous) / previous) * 100\n    return f\"{int(round(wow))}%\"\n```\n\n#### 5. Excel Formatter\n```python\ndef apply_formatting(ws, start_row, start_col, end_row, end_col):\n    \"\"\"Apply formatting to Excel worksheet\"\"\"\n    # Define styles\n    header_fill = PatternFill(start_color=\"F0F0F0\", end_color=\"F0F0F0\", fill_type=\"solid\")\n    total_fill = PatternFill(start_color=\"E6F2FF\", end_color=\"E6F2FF\", fill_type=\"solid\")\n    \n    green_font = Font(color=\"008000\")\n    red_font = Font(color=\"FF0000\")\n    gray_font = Font(color=\"808080\")\n    bold_font = Font(bold=True)\n    \n    thin_border = Border(\n        left=Side(style='thin'),\n        right=Side(style='thin'),\n        top=Side(style='thin'),\n        bottom=Side(style='thin')\n    )\n    \n    # Apply to cells\n    for row in ws.iter_rows(min_row=start_row, max_row=end_row, \n                            min_col=start_col, max_col=end_col):\n        for cell in row:\n            cell.border = thin_border\n            \n            # Color code WoW values\n            if isinstance(cell.value, str) and '%' in cell.value:\n                try:\n                    pct_value = int(cell.value.replace('%', ''))\n                    if pct_value > 0:\n                        cell.font = green_font\n                    elif pct_value < 0:\n                        cell.font = red_font\n                except:\n                    if cell.value == '-':\n                        cell.font = gray_font\n\ndef add_chart(ws, chart_type, data_range, position, title):\n    \"\"\"Add chart to worksheet\"\"\"\n    if chart_type == 'column':\n        chart = BarChart()\n    elif chart_type == 'line':\n        chart = LineChart()\n    \n    chart.title = title\n    chart.style = 10\n    chart.height = 10\n    chart.width = 15\n    \n    data = Reference(ws, min_col=data_range[0], min_row=data_range[1],\n                     max_col=data_range[2], max_row=data_range[3])\n    chart.add_data(data, titles_from_data=True)\n    \n    ws.add_chart(chart, position)\n```\n\n## Usage Example\n\n```python\nfrom retail_trade_report_skill import generate_weekly_report\n\n# Input files directory\ninput_dir = \"/mnt/user-data/uploads/\"\n\n# Store mapping CSV\nmapping_file = \"/mnt/user-data/uploads/store_mapping.csv\"\n\n# Generate report\noutput_file = generate_weekly_report(\n    input_dir=input_dir,\n    mapping_csv=mapping_file,\n    output_path=\"/mnt/user-data/outputs/Retail_Trade_Weekly_Report.xlsx\"\n)\n\nprint(f\"Report generated: {output_file}\")\n```\n\n## Validation Checklist\n\nBefore finalizing output:\n- [ ] All 12 input files identified and processed\n- [ ] Date ranges correctly extracted and displayed\n- [ ] All stores mapped to regions (log unmapped as \"Others\")\n- [ ] All WoW calculations completed\n- [ ] No negative ADA values (except in error logs)\n- [ ] All formulas validated against sample data\n- [ ] Charts render correctly\n- [ ] Color coding applied to all WoW cells\n- [ ] Total rows sum correctly\n- [ ] Output file opens without errors\n\n## Performance Considerations\n\n- Expected processing time: 10-30 seconds for 12 files\n- Memory usage: ~50-100 MB\n- Large file handling: Files up to 10MB each supported\n- Concurrent processing: Process files in parallel where possible\n\n## Troubleshooting\n\n### Common Issues\n\n**Issue:** \"File not found\" error\n- **Solution:** Verify all 12 files are uploaded and filenames match expected pattern\n\n**Issue:** Store name not mapping to region\n- **Solution:** Check mapping CSV for typos, add aliases for common variations\n\n**Issue:** WoW showing \"N/A\" for all values\n- **Solution:** Verify previous week files are correctly identified (earlier dates)\n\n**Issue:** Charts not displaying\n- **Solution:** Check openpyxl version >= 3.0, verify chart data ranges\n\n**Issue:** Negative ADA values\n- **Solution:** Check source data for errors, verify column indices\n\n## Version History\n\n- **v1.0** (2026-02-02): Initial skill creation\n  - Support for 12-file weekly report generation\n  - WoW calculations with color coding\n  - Store-to-region mapping with aliases\n  - Three chart types for visualization\n","topics":["Documentation"],"tags":{"latest":"1.0.0"},"stats":{"comments":0,"downloads":1931,"installsAllTime":73,"installsCurrent":2,"stars":1,"versions":1},"createdAt":1770002572661,"updatedAt":1778485987525},"latestVersion":{"version":"1.0.0","createdAt":1770002572661,"changelog":"Retail Trade Weekly Report Generator 1.0.0\n\n- Initial release.\n- Consolidates 12 weekly sales Excel files into a comprehensive Retail Trade Weekly Report with channel, region, and product breakdown.\n- Calculates Average Daily Acquisition (ADA) and Week-over-Week (WoW) performance.\n- Uses detailed mapping rules to aggregate data by standard regions, handling fuzzy store name matching.\n- Outputs a formatted Excel workbook with styled summary tables and three embedded charts.\n- Includes robust error handling for missing or mismatched input files.","license":null},"metadata":null,"owner":{"handle":"wuminmin","userId":"s1785018hb94cbkbt9816hsyjd8856q1","displayName":"wuminmin","image":"https://avatars.githubusercontent.com/u/16083471?v=4"},"moderation":null}