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:

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

# 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:

# 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

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

# 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

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]"

1. Channel Summary (Rows 4-9)

- Columns: Channel | Program name | This Week ADA | WoW | MoM - Rows: DRP BAU, DRP TECNO, License Store, DXS, RT Total

1. 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

1. 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

1. Mobile Postpaid by Region (Rows 35-45)

- Same structure as Mobile Prepaid

1. FWA 4G by Region (Rows 47-57)

- Same structure as Mobile Prepaid

1. 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

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

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

# 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

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

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

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

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

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

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

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