Power Bi in Data Analytics Virtual Case — PWC Project
Call Center Task
Introduction
Throughout this project, I will be tasked with working on various interactive exercises and challenges designed to help me gain practical experience in creating dynamic dashboards and reports, analyzing data, and sharing insights with others.
First, I will be helping the manager of a Telecom company get to know the customers much better by preparing dashboard that visualizes trends for the call center manager.
Second, I will analyze data of call retention. The telecom has been losing customers and we have to know the reason why, how can we active to change the situation. Then, I will prepare dashboard to tell the manager the insights of data.
Finally, I will apply Power BI skills for HR at the Telecom.
Task
It’s omnipresent: telecom marketing. Better price here. Better service there. Best for small businesses here. Best for young urbanites there. But what do customers really want? Our client, a big telecom company needs to know. This email just arrived for me:
Create a dashboard in Power BI for Claire that reflects all relevant Key Performance Indicators (KPIs) and metrics in the dataset. Get creative!
Possible KPIs include (to get you started, but not limited to):
- Overall customer satisfaction
- Overall calls answered/abandoned
- Calls by time
- Average speed of answer
- Agent’s performance quadrant -> average handle time (talk duration) vs calls answered
Data Source
https://cdn.theforage.com/vinternships/companyassets/4sLyCPgmsy8DA6Dh3/01%20Call-Center-Dataset.xlsx
Dashboard
The dashboard below shows the performance of Call Centre
Insights
- On average, more than 160 calls per day, avarage call length is 3.25 minutes.
- 81.08% of total calls answered and 72.95% of them resolved.
- Average rating of satisfaction is 3.40.
- The number of calls answered and resolved has no difference between the first 3 months of 2021.
Customer Retention Task
Background Information
A few weeks after presenting your dashboard to the management, the Retention Manager from the telecom reaches out to you directly. He was impressed by your work and asked if you can put together a dashboard about customer retention.
In addition, to better understand the data, the telecom Retention Manager has scheduled a meeting with the engagement partner at PwC to cover these points:
- Customers in the telecom industry are hard-earned: we don’t want to lose them
- The retention department is here to get customers back in case of termination
- Currently, we get in touch after they have terminated the contract, but this is reactionary: it would be better to know in advance who is at risk
- We have done customer analysis with Excel: it has always ended in a dead-end
- We would like to know more about our customers: visualized clearly so that it’s self-explanatory for our management
The Retentions Manager has provided some information, have a look through the resource section.
Task
- Define proper KPIs
- Create a dashboard for the retention manager reflecting the KPIs
- Write a short email to him (the engagement partner) explaining your findings, and include suggestions as to what needs to be changed
Data Source
https://cdn.theforage.com/vinternships/companyassets/4sLyCPgmsy8DA6Dh3/02%20Churn-Dataset.xlsx
Dashboard
The churn dashboard reflects the demographics, account information of customers and services customers signed up. The next dashboard analyze the customer risk base on internet services, type of contract and payment method.
Insights
Customer Overview
- There are total 7,043 customers subscribed to the client, out of which 49.76% are Females and 50.24% are Males, & the revenue earned is $16.1M.
- By tenure, 31% customer are subscribed to below 12 months, as a result there are more customers with month-to-month contract.
- Most customers have Fiber optic internet for which they are paying high charges.
- 73.5% customer have been retained
Churn Customers
- 26.5% customers have churned, out of which 380 customers churned last month itself.
- Revenue lost cause of churn is $2.9M
- Churn ratio of Males and Females customers is the same, gender is not the factor
- 89% customers churned had month-to-month contract.
- Customer paying high charges have churned the most (1274), along with fiber optic internet have also churned the most.
Churning factors
- Customers with tenure of last than 12 months
- Customers with month-to-month contract
- Customers subscribed to fiber optic
- Paying high charges
Diversity & Inclusion Task
Background information
Human Resources at our telecom client is highly into diversity and inclusion. They’ve been working hard to improve gender balance at the executive management level, but they’re not seeing any progress. They’re reaching out to us for help.
At PwC Switzerland we are often approached by clients seeking support with diversity and inclusion. Companies need a workforce of diverse talents and backgrounds to succeed in an increasingly complex and heterogeneous world. To us, diversity and inclusion are business imperatives, not just nice-to-haves. We aim for all of our teams to feel welcome and appreciated. But actually achieving this and unlocking its potential involves a whole set of practical challenges.
Task
- Define relevant KPIs in hiring, promotion, performance and turnover, and create a visualisation
- Write what you think some root causes of their slow progress might be
KPI defining
Calculating the measures:
- # of men
- # of women
- # of leavers
- % employees promoted (FY21)
- % of women promoted
- % of hires men
- % of hires women
- % turnover
- Average performance rating: men
- Average Performance rating: women
Data Source
Dashboard
The two pages of dashboard below shows the KPIs in hiring, promotion, performance and turnover of telecom company.
Insight
- The recruitment structure shows that, in senior executive positions, most personnel are male while officers have a more evenly distributed gender structure.
- Promotion has a significantly higher difference for male employees than for female employees from manager level and above.
- The age of the staff is the highest at the age of 20–29, accounting for 215 employees, most of them are officers.
- 100% new hires is male, and 100% promoting in FY21 is male.