The campaign performance reporting process for Westside was manual, time-consuming, and prone to human error. Analysts had to manually extract, compile, and clean data from multiple SMS, WhatsApp, and sales reports — often spending 4–5 hours daily, which introduced inconsistencies and delayed business insights.
Objective
To automate the integration and analysis of:
- Campaign performance metrics
- Sales data from MoEngage dashboards
The goal was to consolidate all performance metrics into a single Excel-based ROI dashboard, enabling faster decision-making and improved data accuracy.
Tools & Technologies
- Microsoft Excel
- VBA Macros (Visual Basic for Applications)
Solution Overview
We designed and deployed three custom VBA macros that automated end-to-end data collation and KPI mapping:
1. Import Campaign Name Columns
Function:
Automatically pulls “Campaign Name” data from multiple Excel/CSV files into a master sheet.
Highlights:
- Dynamically identifies “Campaign Name” columns in each input sheet.
- Allows user-controlled file and sheet selection through prompt windows.
- Supports both Excel and CSV file formats.
- Appends data smartly while preserving existing header structures.
2. Import SMS Sent Data
Function:
Integrates campaign metrics such as Sent, Delivered, CTR, CVR, Read Rate for both SMS and WhatsApp campaigns.
Highlights:
- Auto-detects headers in source and destination files (no hardcoding).
- Matches campaign metrics using campaign name and type (SMS/WhatsApp).
- Aggregates numeric data from multiple files.
- Calculates and populates CTR, CVR, and Read Rate dynamically.
- Uses a dictionary-based logic for smart, error-resistant mapping.
3. Extract Data To Westside ROI
Function:
Maps sales and shopper count data from transaction reports to the corresponding campaigns in the ROI dashboard.
Highlights:
- Flexible mapping using multiple possible source labels (e.g., “Sum of sales_amount” → “Offline Sales”).
- Smart row and column scanning for robust adaptability.
- Auto-finds the next empty row for seamless value insertion.
Processes multiple files in one go — significantly accelerating throughput.
Results & Impact
KPI | Before | After | Improvement |
Time Spent on Reporting | 4–5 hours | ~1 hour | ⏱️ 75–80% faster |
Error Frequency | High (manual copy-paste) | Minimal | ✅ Significant reduction |
Reporting Format | Inconsistent across teams | Standardized | 📊 Uniform & reliable |
Team Adoption | Manual effort | Full automation | 💼 Used weekly by analysis team |
Conclusion
The VBA macro solution transformed Westside’s campaign ROI reporting by eliminating manual inefficiencies and ensuring accurate, scalable, and faster reporting. This automation not only saved time but also improved trust in data — enabling the team to focus more on insights rather than operations.
