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

...