ATLIQO TELECOM

Problem Statement

AtliQo is one of the leading telecom providers in India and launched its 5G plans in May 2022 along with other telecom providers.

However, the management noticed a decline in their active users and revenue growth post 5G launch in May 2022. AtliQo’s business director requested their analytics team to provide a comparison report of KPIs between pre and post-periods of the 5G launch. The management is keen to compare the performance between these periods and get insights that would enable them to make informed decisions to recover their active user rate and other key metrics. They also wonder if they can optimize their internet plans to get more active users.

Start Now

Dashboard Requirement

Task:

Create the comparison report based on the mock-up provided. Please note the mock-up is created by a business user who has minimal idea about dashboarding. Hence, you need to represent the insights in a much better way.
The target audience of this dashboard is top-level management - hence the dashboard should be self-explanatory and easy to understand.
Create relevant insights not provided in the metric list/mock-up dashboard to support the cause.

Data Organized

This dataset was downloaded from codebasics.io website and stored in Telecom Domain folder on PC.

It contains 6 data files: dim_cities.csv, dim_date.csv, dim_plan.csv, fact_atliqo_metrics.csv, fact_market_share.csv, and fact_plan_revenue.csv.

Data Cleaning & Transformation

Microsoft Power Query and Power BI will be used

Duplicates were checked with remove duplicates function
Gaps were checked with TRIM function
Blank and NULL checked with filter function

Data Analyzing

Measures

Active Users After 5G = CALCULATE(AVERAGE(fact_atliqo_metrics[active_users_lakhs]), dim_date[before/after_5g] = "After 5G")
Active Users Before 5G = CALCULATE(AVERAGE(fact_atliqo_metrics[active_users_lakhs]), dim_date[before/after_5g] = "Before 5G")
Active Users Before After Change % = DIVIDE([Active Users After 5G]-[Active Users Before 5G],[Active Users Before 5G],0)
ARPU = AVERAGE(fact_atliqo_metrics[arpu])
ARPU After 5G = CALCULATE(AVERAGE(fact_atliqo_metrics[arpu]),dim_date[before/after_5g] = "After 5G")
ARPU Before 5G = CALCULATE(AVERAGE(fact_atliqo_metrics[arpu]), dim_date[before/after_5g] = "Before 5G")
ARPU Before After Change % = DIVIDE([ARPU After 5G]-[ARPU Before 5G],[ARPU Before 5G],0)
Average Revenue = AVERAGE(fact_atliqo_metrics[atliqo_revenue_crores])
Market Share % = AVERAGE(fact_market_share[ms_pct])
Monthly Active Users = DIVIDE([Total Active Users], COUNT(dim_date[month_name]))
Monthly Unsubscribed Users = DIVIDE([Total Unsubscribed Users], COUNT(dim_date[month_name]),0)
Revenue After 5G = CALCULATE(SUM(fact_atliqo_metrics[atliqo_revenue_crores]), dim_date[before/after_5g] = "After 5G")
Revenue Before 5G = CALCULATE(SUM(fact_atliqo_metrics[atliqo_revenue_crores]), dim_date[before/after_5g] = "Before 5G")
Revenue Before After Change % = DIVIDE([Revenue After 5G]-[Revenue Before 5G],[Revenue Before 5G],0)
Total Active Users = SUM(fact_atliqo_metrics[active_users_lakhs])
Total Revenue = SUM(fact_atliqo_metrics[atliqo_revenue_crores])
Total Unsubscribed Users = SUM(fact_atliqo_metrics[unsubscribed_users_lakhs])
Unsubscribed Users After 5G = CALCULATE(AVERAGE(fact_atliqo_metrics[unsubscribed_users_lakhs]), dim_date[before/after_5g] = "After 5G")
Unsubscribed Users Before 5G = CALCULATE(AVERAGE(fact_atliqo_metrics[unsubscribed_users_lakhs]), dim_date[before/after_5g] = "Before 5G")
Unsubscribed Users Before After Change % = DIVIDE([Unsubscribed Users After 5G]-[Unsubscribed Users Before 5G],[Unsubscribed Users Before 5G],0)

Data Modelling

Data Sharing

1. Visualization

Microsoft Power BI was used to visualize data.

2. Insights

After 5G revenue decreased by -0.50% compared to Before 5G. Additionally, the revenues of the cities of Delhi, Chennai, Ahmedabad, Hyderabad, Chandigarh and Kolkata have decreased after the arrival of 5G.
After 5G ARPU increased by 11.05% compared to Before 5G. Moreover, although there was a decrease in the cities of Pune and Chennai after the arrival of 5G, the ARPU value of all other cities increased significantly.
After 5G Monthly Active Users decreased by -8.28% compared to Before 5G. After the arrival of 5G, the number of active users has decreased significantly in the majority of cities.
After 5G Monthly Unsubscribed User increased by 23.50% compared to Before 5G. In addition, after the arrival of 5G, the number of non-member users decreased significantly in the majority of cities.
Atliqo ranks third in terms of market share in all months, regardless of Before or After 5G.
P1, P2 and P3 are the plans with the best revenue performance, P13, P9 and P10 are the plans with the worst revenue performance.
After 5G arrives, P1, P11 and P12 are the plans with the best revenue performance. P11 and P12 achieved a high output. Users mostly prefer Combo packages.

3. Results

Since the launch of 5G, our plan revenues have increased, but the trend of active users has decreased and the number of unsubscribed customers has increased. Regarding this, various campaigns can be made to currently active users to ensure that they continue their membership. In addition, advantageous plans can be offered to new customers with new campaigns and long-term contracts.
After 5G, Plans 8, 9 and 10 were stopped, and Plans 11, 12 and 13 were put into effect. We observe a decline in income growth in Plans 4, 5, 6 and 7. There needs to be more focus on plans whose revenues decreased after 5G. The majority of users prefer combo packages. Accordingly, an arrangement can be made or various campaigns can be offered.
It's recommended to pay attention to Mumbai, Delhi and Kolkata city. Apart from that, we could introduce modified attractive plans and roll out offers to increase the engagement for Patna, Coimbatore, Chandigarh, Gurgaon, and Raipur as these are the bottom 5 cities in terms of revenue generation.
Though we have experienced a steady growth of market value in the cities across the months, the percentage of market share gained by the respective company from the total market value declined in recent months. We can adopt the resilient strategy and rethink policy design following the first 4months of Before 5G phase.