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