Embedded Files
Project: Customer Billing Analytics and Revenue Segmentation Dashboard – Electro Sur Este S.A.A.
Objective:
To develop a centralized analytics dashboard to monitor customer billing, segment customers by consumption and payment behavior, and optimize revenue collection strategies across distributed service areas.
Context:
Electro Sur Este S.A.A. (ELSE) is a major electricity distribution company operating in southern Peru, serving over 600,000 customers across the regions of Cusco, Apurímac, and Madre de Dios. Each month, ELSE bills its customers based on their energy consumption, consolidating unpaid balances from previous months into the current bill. This project was designed to build a data-driven dashboard solution to identify high-value customers, analyze payment trends, and track revenue collection efficiency through digital and traditional payment channels.
Tools & Technologies Used:
SQL Server Integration Services (SSIS) – ETL development
SQL Server – Data warehousing
Power BI – Dashboard design and data visualization
DAX – Custom measures and KPIs
Process Overview:
Phase 1 – Billing & Collection Dashboard
Data Integration: Aggregated billing and payment records from multiple distributed commercial databases.
ETL Process: Designed and deployed SSIS packages to extract, transform, and load data into a centralized analytical data warehouse. Recursive logic was implemented to handle distributed data sources efficiently.
Data Cleaning: Standardized records, fixed inconsistencies, and removed duplicates.
Incremental Load: Only new or updated data (e.g., recent payments, re-billed accounts) is inserted to ensure optimized performance and data accuracy.
Scheduling: ETL processes are automated via SQL Server Agent, scheduled during non-peak hours to minimize impact on operational systems.
Data Modeling & DAX: Created logical data models in Power BI and developed key performance indicators using DAX.
Reporting: Built interactive dashboards for billing, delinquency, and collection performance by customer segments.
Phase 2 – Revenue Trend Analysis
Developed dashboards to track historical and comparative collection metrics (e.g., month-over-month, year-over-year).
Analyzed performance by collection centers (payment offices), enabling comparative analysis of payment volume and efficiency.
Phase 3 – Web Payment Analytics
Mapped the end-to-end process of online payment: debt inquiry, payment initiation, and transaction confirmation.
Built a dashboard showing the number of users completing each stage and the total amount collected via web transactions.
Key Insights & Recommendations:
Average collection rate: 72%; delinquency rate: 28%.
Low-consumption customers (< S/15/month) represent 80% of accounts but only 2.5% of revenue.
High-value customers (> S/1000/month) are only 1.2% of accounts but contribute over 35% of total revenue.
Prioritize follow-up with high-value accounts to significantly improve collection KPIs.
Most delinquent accounts have only one month of overdue balance; targeted campaigns could help increase timely payments.
Cash remains the dominant payment method (as of January 2024); promote digital payment channels to increase efficiency.
In web transactions, a significant drop-off exists between users checking their debt and completing payment. Further analysis is needed to identify UX or technical bottlenecks.
Visualizations Included:
Billing vs. Collection by customer segment and region
Collection trends (monthly, quarterly, annual)
Delinquency heatmaps by customer profile
Payment method breakdown (cash, web, bank, app)
Funnel analysis of the web payment process
Performance by payment centers
Dataset:
Due to confidentiality agreements, the dataset and final report cannot be publicly shared. All assets are the property of Electro Sur Este S.A.A.
Visualizations:
Page updated
Google Sites
Report abuse