Client Overview
The client, a leading Internet Service Provider (ISP) in the UK and USA and one of the leading in the world, manages subscribers across multiple cities, each with different service plans, pricing models, and billing conditions. Handling subscriber billing manually was time-consuming and error-prone, leading to inefficiencies in generating reports, tracking revenue, and making data-driven decisions.
Business Challenge
The ISP faced several operational challenges:
- Manual Data Processing – Generating city-wise subscriber reports involved significant manual effort, leading to delays and inconsistencies.
- Inconsistent Report Structures – The lack of a uniform format made it difficult to compare data across different cities.
- Delayed Decision-Making – Manual processes slowed access to key subscriber insights, impacting business strategy.
- Complex Billing Calculations – Subscription charges varied based on factors like city, ISP, property type, connection speed, bulk agreements, and fiber aid.
- Scalability Issues – With an increasing number of subscribers, managing multiple cities without automation became unmanageable.
Objective
The primary goal was to automate subscriber billing reports for each active city, ensuring accurate and timely data delivery. The project focused on:
1. Automating Excel Report Generation – Each city should have its own structured file with categorized sheets for easy reference.
2. Streamlining Data Calculations – Automating key billing metrics like subscription charges, new connection fees, and change of service (COS) costs.
3. Enhancing Data Visualization – Using Power BI to provide real-time insights into revenue, subscriber growth, and service changes.
4. Improving Operational Efficiency – Enabling proactive decision-making with automated data pipelines and reporting.
Solution Implementation
The implementation was structured into three key areas, each addressing a specific challenge in the billing process:
1. Automating Data Processing
Designed a data pipeline to pull subscriber data from Smartsheet automatically.
Structured data into different categories, including active subscribers, new subscribers, COS transactions, and terminated services.
2. Automated Excel Report Generation
Built a city-wise reporting system, generating a separate Excel file for each active city.
Each report included structured sheets:
- Summary of Statuses – An overview of subscriber activities in the city.
- Active Subscribers – Details of subscribers with ongoing service.
- New Subscribers – Information about newly connected users.
- Change of Service (COS) – Service modifications and applicable additional charges.
- Terminated Subscribers – Records of subscribers who disconnected.
Set up automated email distribution, ensuring reports are delivered within the first 7 business days of each month.
3. Power BI Integration for Advanced Analytics
- Data Modeling – Structured subscriber data in Power BI for easy access and visualization.
- Interactive Dashboards – Created real-time Power BI dashboards to track key metrics.
- Revenue Insights – Breakdown of total chargeable amounts across cities.
- Subscriber Growth Trends – Visualizing new subscribers and churn rates.
- City-Wise Performance – Comparing revenue, subscriber retention, and service adoption.
- Service Change Analysis – Tracking COS trends and related revenue impact.
- Customer Retention Indicators – Identifying subscribers at risk of churn.
Automated Data Refresh – Ensured that dashboards update with the latest data for real-time insights.
Key Metrics and Calculations
Several key metrics were automated to ensure accurate billing and financial tracking:
1. Monthly Subscription Rate – Determined based on city, ISP, property type (residential or business), connection speed, bulk rate agreements, and fiber city aid.
2. Subscription Rate Per Day – Calculated by dividing the monthly subscription rate by the number of days in the previous month.
3. Service Days Chargeable Amount – This is obtained by multiplying the daily subscription rate by the number of days a subscriber had active service during the month.
4. New Connection Charge – Additional charges applied to new subscribers based on their city, ISP, and property type.
5. Change of Service (COS) Chargeable Amount – Fees for subscribers who changed their service plan outside the 14-day grace period.
6. Total Chargeable Amount – The final amount billed to a subscriber, combining the service days chargeable amount, new connection fees, and any COS charges.
These calculations ensured billing accuracy, revenue predictability, and automated processing, making financial reporting more efficient.
Impact & Business Value
- Time Savings – Report generation was reduced from several days to minutes, improving operational efficiency.
- Improved Accuracy – Eliminated human errors in calculations, ensuring correct billing and financial reporting.
- Real-Time Insights – Power BI dashboards allowed instant tracking of revenue, subscriber trends, and service changes.
- Scalability – The system seamlessly handled multiple cities and subscriber growth without additional manual effort.
- Strategic Decision-Making – Leadership gained access to actionable data for pricing strategies, marketing, and customer retention efforts.
Future Enhancements
- AI-Driven Churn Prediction – Implementing machine learning to identify subscribers at risk of leaving.
- Dynamic Pricing Models – Using predictive analytics to recommend optimized pricing strategies.
- Automated SLA Monitoring – Tracking service levels for different ISPs and cities.
- Self-Service Analytics for Stakeholders – Enabling city managers and finance teams to generate their own reports on demand.
Conclusion
By leveraging automation, data analytics, and Power BI, the ISP transformed its billing operations into an efficient, accurate, and scalable process. The new system eliminated manual inefficiencies, improved financial transparency, and provided leadership with real-time, data-driven decision-making tools.
