Embedded Files
This is part of a virtual internship by Pwc which during which we get the chance to learn Data Visualization using Power Bi, I have already taken some other courses on Coursera and YouTube on how to use the Power Bi Desktop and this Virtual internship gave me the chance to put things to practice.
The First part before creating charts and visuals is to :
1 - Understand the Problem that needs to be solved
2 - understand the business data and find the right KPIs that needs to be visualized, some information in the data can give us more insights than others
Part N°1:
Part N°1:
So we have a Data set from a Call center, and we need to understand each column and extract meaningful KPIs to show good insights in a Dashboard with the help of Power BI and Power Query Editor
I added a column using Power Query to extract month name
= Table.AddColumn(#"Removed Columns", "Custom", each Date.MonthName([Date]))
used in the following column charts where I grouped the Number of Answered vs unanswered calls by month
Another column I created from the Time stamp of the calls to split the times of call into Morning and Afternoon
= Table.AddColumn(#"Renamed Columns", "TimeOfDay", each if [Time] < #time(12, 0, 0) then "Morning" else if [Time] > #time(12, 0, 0) then "Afternoon" else null)
Time to create that dashboard
The Possible KPIs we have include:
Overall customer satisfaction
Overall calls answered/abandoned
Calls by time
Average speed of answer
Average talk duration vs calls answered
The two column charts below shows distribution of Call Topics for Each call operator, and the number of calls received by topic, it appears there is almost equal distribution between call operators (left Chart) and number of calls per topic is also close (right chart)
Next Dashboard is going to be about customer retention.
Part N°2 :
Part N°2 :
Scenario: 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
We will do the following tasks:
Define proper KPIs
Create a dashboard for the retention manager reflecting the KPIs
Write a short email to him (the engagement partner) our findings, and include suggestions as to what needs to be changed
Link to Data sheet : Churn-Dataset.xlsx
So the way we can go about this, is to make 2 dashboard, First one to show information on customers in general, and a 2nd dashboard to show KPIs for to get insights on Customer who are churning/ risk of Churning
Employee churn is the overall turnover in an organization's staff as existing employees leave and new ones are hired. The churn rate is usually calculated as the percentage of employees leaving the company over some specified time period.
The data shown In this Dashboard is filtered for Customers churning,
As you can see below , Demographic Data about the customers who are churning and the type of services they subscribed to, type of contract .., these are vital info to get to know the customers well in order to know what made them churn, and it's also important for when the company tries to reach them and gain them back and be able to address all their issues.
What we can derive from the dashboard above:
The Most Common payment method is "Electronic check"
The Majority of churning customers had less than 1 year of subscription time
Fiber optics is the most common internet service type about 70%
Next we calculate the percentage of several values such as Percentage of Dependents, Tech Support, Device Protection .. to see how many of all churning customer matched a certain description/category
Example :
Dependent % = DIVIDE(CALCULATE(COUNT('01 Churn-Dataset'[Dependents]),'01 Churn-Dataset'[Churn]= "Yes",'01 Churn-Dataset'[Dependents] ="Yes"),CALCULATE(COUNT('01 Churn-Dataset'[Dependents]),'01 Churn-Dataset'[Churn] ="Yes"),0)
The Column chart above shows the Churn rate by Internet service type, and we can see more of customers who churn had Fiber optics (Fiber Optics offers very fast internet so more investigation is required to understand the reason behind the churning problem
Building the 2nd Dashboard:
1 - Adding a Slicer on the Left hand side for multiple column values filtering is very helpful, upper rectangle on the right has Total customer number and the Churn rate (new Calculated Column)
Churn Rate % = DIVIDE(CALCULATE(COUNT('01 Churn-Dataset'[Churn]),'01 Churn-Dataset'[Churn] = "Yes"),CALCULATE(COUNT('01 Churn-Dataset'[Churn])),0)
2 - Add Total number of customers and the Total Yearly charges of all customers
Now that we know more about the customers who are at risk of Churning
Next, we have to show the churn rate and see if there is a specific category that company should try to target it's solution towards it.
Next, we are going to see how much churn rate in other areas/categories, which are : Tenure, Contract, Payment Method
The column chart above shows how much the churning customer affect the revenue of the company
the Dashboard :
I would like to also add a comparison in some KPIs between Consumers Churning Vs No Churning
The visuals shows various KPIs so the Stake holders can narrow down on where to implement solutions
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.
https://www.pwc.ch/en/careers-with-pwc/careers-blog.html
https://www.pwc.ch/en/services/people-organisation/diversity-and-inclusion.html
The Data we have seems to be from Year 2020 to 2021
Link to Data set : Diversity-Inclusion-Dataset
Calculating the following measures could help to define proper KPIs:
# 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
The task is to do the following:
Define relevant KPIs in hiring, promotion, performance and turnover, and create a visualization..
Write some root causes of their slow progress.
We Calculate the required KPI measures inside power bi
Men % = DIVIDE(CALCULATE(COUNT('Pharma Group AG'[Gender]),'Pharma Group AG'[Gender] = "Male"),COUNT('Pharma Group AG'[Gender]),0)
Women % = DIVIDE(CALCULATE(COUNT('Pharma Group AG'[Gender]),'Pharma Group AG'[Gender] = "Female"),COUNT('Pharma Group AG'[Gender]),0)
To know how many people left the company in 22020 we just use the column "FY20 leaver" and filter for "YES"
,As For Promotions, we can see that from Grade Perspective and Function Perspective
We also need to measure Performance Rating and do comparison
Github Link to Work Files
Github Link to Work Files
Page updated
Google Sites
Report abuse