End to End Power BI Project - Customer Churn Analysis | Power BI Mastery #powerbitraining

hi and welcome to another data to dashboard video with pi with goodread my name is good preet and my mission is to help train and Empower people like you with life-changing data skills in this particular video we're going to learn all about customer Channel analysis what it is how to perform it and how to create a dashboard in the same respect that can be used by the business users to make informed decisions and form the strategies to improve the customer churn rate I have provided a link to the exercise files in the video description so you can follow along and create your own version of this dashboard and add it to your portfolio of projects for power bi so without further Ado let's get started so first things first let's understand what is customer churn analysis now every business has customers and not all customers remain with the business forever customers come and go the rate at which customers leave a business against the total customers they have is called rate of loss of customers or churn rate and it's also sometimes referred to as customer attrition so what we do in customer chat analysis is look at this churn rate against various factors and find what could be the main contributing factors in increasing this churn rate and then the business users can use that knowledge that inside that information to make informed decisions and to strategize about how to reduce this churn rate and that is the main goal of customer term analysis and that's what we're going to do in this particular video so let's get started and now look at our raw data so here we are looking at the raw data so we have raw data in the form of a CSV file this data is for the customers of a bank and we have 10 000 rows of worth of data here and we have various factors here in terms of their credit score which country they belong to the gender the age the tenure so how long they have been with bank or they were with bank their balance and this is the account balance the product number so which particular product so there are looks like few different products one two three four whether they had a credit card or not whether they were active they are active members or not so whether they do any transactions or they just there as a customers that but don't often do any transactions what's their estimated salary and this is the annual salary and whether the customer was churned what that means is whether they are still a customer or they left so one indicates a customer which has left the bank and zero is which is still an existing customer so now you should be familiar with the raw data that will be you will be working with and I'll recommend that you always do this look at the raw data and understand each and every column what it involves in there if you don't have that information you should go and ask the business users where the data is coming from if there's no data dictionary available so the very very important step before you actually do anything with the data is to understand it so that you can make a decision which data is important for your analysis which is relevant for it which is not relevant so make sure you always familiarize yourself with the raw Data before you actually start working with it whether before you do any sort of data preparation so now you should be well familiar with this data where that we have for 10 000 customers from a bank and now let's look at how we will import this into the power bi model using power query editor and also look at how we will prepare it how we will clean it prepare it transform it reshape it to make it ideal and ready for data modeling and data analysis and visualization so let's jump in onto the desktop again I've got power bi desktop open here so the first step is that we want to bring the raw data into Power bi for that we have to use the relevant connector so get data this is the one place where we can go to to connect to any relevant data source and power bi provides really powerful connectors for more than 170 different types of data sources in this case our source is a CSV file so we need the text CSV data connector just click there and I have kept my data here in my downloads folder and I've also provided a link to the CSV file in the video description you can download and practice along so once we do that we see this preview of the data this is Western European data so we understand countries here France Spain Germany so yes it's Western European data comma delimited file which is correct and we are looking at data type detection based on first 200 rows so that is fine I think we will leave everything at default but we do want to work on preparing the data so we're going to click on transform data that will launch power query editor if you were happy with the data you could just press load and start working at analysis of the data but here we do want to work with the data so we're going to click transform data and once we do that here we can see we have power query editor which opens in its own window so behind us there is the power bi desktop and this is the power query editor so that's launched in its own window and we can see we have one query which is the data that we pulled by connecting to the CSV file first things first I'm going to just change the name of this query to customer data okay that's great now always keep an eye on this applied Stacks window so whatever Transformations we do whatever steps we perform here in terms of preparing our data there will be an applied step created here and it's always good to keep this clean and organized wherever possible and at the top here we have the formula bar so anything that we do generates an M code automatically for this implied steps and that code can be viewed here and also can be edited as well so here in this case we connected to the source data these headers were promoted automatically so and then the data types were changed and applied as well I'm going to remove this change type and I'm also going to remove this promoted header so this is how it came through so this Source step is by default will always be there and then it has got this gear icon there so you can change the setting you can connect to a different file and do all those things from here you can change the region and whatever so that's there in terms of the setting any step which is as a gear icon that means you can click on the gear icon and then change the settings for that particular step so that's the slow step so the very first step we want to do is promote this first row as headers and the way to do it is by going to the Home tab just click here use row as headers and there we have it now once I all have this I it's still done the change type automatically I'm going to remove that for now now once this happens I always look at my data and just to give myself a bit more space I'm going to minimize this queries window here or queries pane so I've got a bit more working area here with my data so now always look at if there is any data that I really don't need which is not relevant for my particular analysis it's always better to reduce your data as much as possible so in this case customers ID yes we want to keep it unique ID the credits code could be effective the country location so gender the age and then the tenure the balance product number the credit card whether they had a credit card not active member or not estimated salary now this is not real salary this is estimated salary because we already have the account balance we're going to remove this factor from here for now and the way to do it is just right click on here and click remove and when we do that you can see here the removed columns step has been added now you can also do is you can right click here and rename it and change it to removed estimated salary so now it's more meaningful for us and you can see here the M code for it where it says table dot remove columns and this comes after the promoted header step and the column we removed is estimated salary okay that's great now another thing I do is always look at the column names and if they are meaningful and if I need to edit them so I'm going to just make them a bit more friendly so that when they show up in our visualizations the more meaningful customer ID this is credit score country gender if I can spell it correctly age tenure this is how long the customer has been with the bank or was with the bank before they left account balance so and then we have next column which is product number we can just say product credit cards active and churned status okay so now our column names are more meaningful for us next thing you want to do is now look at the data types and apply the relevant data types so customer ID is looks like this is a numeric field so we don't have any alphabets in it so I'm going to just change it to whole number the credit score is a number again so whole number the country is uh basically alphanumeric so it's text so a text is fine gender is also text and also keep an eye on the different types of values in there so this is a categorical variable and we have male and female so no misspellings or anything like that as well also look at the country as well and you can load more so that you see all the values all the data and that's yes that's all fine as well age um and you might be noticing I'm seeing this chart part chart here at the bottom so because in my view options our chord column profile ticked here so by doing that what I'm doing what I'm able to do is I'm able to look at the stats here so I'm just going to turn this off for now so it's not distracting and I'll come back to this in a minute so customer age is we know it's a whole number the tenure is also a whole number the balance is in currency so fixed decimal number is the format that is used here in power bi desktop next we have the product Now product is again whole number it's a product number credit card we have one and zero whole number active we have uh here again whole number churn status is also one and zero so we have it whole number so those are the sort of data types now we have looked at and prepared our data from that perspective next things you want to look at is that here say if I bring in this product one two three four it won't make any sense if I create a bar chart by where I'm looking at different products just saying one two three four maybe I should change this to product one product two product three product four so on and so forth so there are various ways of doing it easiest way is to look at add column and add a column by example and here I'm going to say for let's say fraud one then here it's already picked up because I already had the product column selected so it's picked up that it's based on one two three four I just want to prefix with it prod and that's done a pretty good job so that's there I'm going to call it products okay now this column do we still need it we really don't need it now so I I'm going to remove this basically so now we want to do the same thing for credit card let's change the name of this column to credit card status and what I want to do is I want to say whether it's they have they own a credit card or not owned basically so here wherever it's one we want to say owned wherever it's zero we want to say it is not done so that means we're going to change this into a text column because those are text value so this is another way of doing this especially if you don't have too many values so in this case what I'm going to do is first to change the data type to text and then right click here and then say replace values so I want to replace the one with owned and then again right click replace values and I want to change the own zero with not okay so there we go that's another way of doing the same thing now similarly active so so here maybe we can say status activity status let's rename the column and again it's just one and zero so it's easier to do this way so just change it to text right click and then replace values wherever it says one it is active and wherever again right click replace values and wherever it is 0 not active or inactive let's say inactive makes more sense so there we have it now churn status a similar Thing One and zeros let's change it to text and we want to say wherever it is one we want to right click replace change it to change the ones to churned and right click replace values and we're 0 which means not churned so not judge so there we have it so we have converted our those numbers into some sort of meaningful information if we were to create charts from it okay next thing we want to do is now we have all this um say age here for all these customers and now I'm going to go to view and click on column profile by doing that what happens is here at the bottom I can see the stats so here there are 60 distinct values eight unique values and also it gives me minimum and maximum so minimum age is 18 maximum age is 82. okay so now we know the minimum maximum it will be meaningful and useful to categorize our data so group these edges into some sort of category say less than 20 between 20 and 30 30 and 40 so on and so forth and create some age categories so that then we can analyze this data around those instead of looking at all these distinct age values not very meaningful way of analysis analyzing it for that what we do is we go to add columns so make sure your 8 column we selected and create a conditional column okay let's call this age groups okay this is based on age and if it is less than or equal to 20 which will cover anything between 18 and 20 we will say less than 20. okay next add another clause and follow the same method so anything which is less than or equal to 30 is 21 to 30. [Music] [Music] so there we have our categories or age groups so nicely done you can see here drop down load more as well we have all these categories so less than 20 and so on and so forth similarly we need to create categories for the credit score maybe because again current score has so many values 354 distinct values so if you want to do any analysis around it won't be meaningful again we have the minimum which is 376 and the maximum which is 850 so we can now group this into some sort of categories and create credit score groups so right click add column Tab and click on conditional column let's call it credit score a secret discredits course basically and select the credit score here and we say is less than so if it is less than 400 then we will say 0 to 400. add a close or maybe let's just say less than because it takes up less space so let's say less than 400 and then if we have credit score of less than equal to 500 then it is 400 to 500 let's change this to less than equal to and this is less than equal to this becomes 500 so this is from 401 to 500. don't want the categories to overlap as well that's also something you have to always keep in mind so no overlapping should be there so this one is form five under one two six hundred [Music] so there we have it press ok and we have the credit scores as well in some sort of groupings meaningful groupings here let's look at any other data that we need to grow so here we have country we have gender age we have already done tenure let's see what the sort of values are there in tenure column so we have 11 distinct values the minimum is 0 maximum is 10 so 0 1 2 3 4 so it's it's just distinct value is up to 10. so it's not too many values let's let's leave it like that now the balance column again we have minimum of zero and a maximum of two hundred and thirteen thousand one hundred forty six so that's a huge range and there are 650 distinct values again if we want to do any analysis around the account balance then it's better to create some groupings here as well so again we are going to create a conditional column and call it accounts balance and here refer to the balance column and if it is equals zero let's call it zero because you know we want to look at distinct wherever this balance is zero and next one we say is less than or equal to 1000 it is so one two one k [Music] last but not the least we now wrap up by saying anything greater than two hundred thousand so and then see here greater than 200k so there we have our categories for account balance and let's look at the data types for these columns where we have created these groups so and these are all should be text because we have the dash in there so make sure you change those to Text data type and always try and group your steps together so instant in terms of changing this data type we're going to change it in one go for all three columns so that we only have one step added here in the applied steps for that change of data type and it shows us which columns we change for and what type we changed so that is that looks pretty good in terms of now analyzing our data preparing it here the I think data is in good clean State and we have done enough preparation now another thing you want to look at is when you actually start creating charts so suppose I'm creating charts for age groups okay now if I was to arrange this in ascending order you can see here the way it's arranged let's actually look at the drop down here and load more and here you can see if it is ascending order is starting with 21 to 30 whereas the first category is less than 20. so these will never be because of the way we have used symbols it's difficult to arrange them in some sort of order here and its text values so it's always better to create a separate table for these groups and add a column that can be then used for um sort of reordering these and arranging these values in some sort of order so the way to do it is we go right click here so I've just expanded the queries pane right click on the query here customer data query and say reference okay now what happens is all the previous steps were already applied and we just got the end result from there and now first things first let's just change this to h groups okay and the only column we want to keep from this data is the age groups column so just right click and say remove other columns and this will remove all the other columns and next just make sure this column is selected go to the Home tab and here say remove rows and say remove duplicates and there we go there we go we have the unique values here for the age groups now we need to create an index column but if we just pick and add so add column if I just go here and index add an index column from one two three four five six so it's based on how it's sorted here which will never be right so that's not what we want to do what we have to do is we have to again because this is a sort of custom sorting we are doing here so we have to say here age in groups ID okay and it's based on age groups equals and we just have to now go through each and every value the first value is less than 20 and we're going to say this is value of 1 and similarly we'll just go and add all these Clauses so we have seven distinct values so we need seven of these one two three four five six seven and then just provide the value so next one is 21 to 30 and this has a value of 2 next one is 31 to 40 and this has a value of three so on and so forth and now here we can see that eight groups are sorted in a decent order the way we would like to see them in a chart and we now have to repeat the same process for the balance account balance the credit scores as well so the credit scores the way we will do it I'm just going to do it quickly without explaining each and every step you can just look at age groups and just follow the same method so right click on the main query create a reference query rename it so this one is account balance groups so go and select the column which is the account balance column and remove other columns right click on that I mean click on that column Go Home tab remove rows remove duplicates and if you try to arrange this in ascending order it is showing see 100K first and then 10K then 1K which is not the order we want so we have to go add column conditional column and this is account balance ID and we select account balance and we create Clauses we're going to create six Clauses so the one for which we don't have data at the moment but tomorrow we do refresh data it might be some data in there so we want to have that category available here in our sorting order so here the first one is zero next one is category of one two one k 1 K2 10K and then we have 10K to 100K and then we have 100k to 200k and then we have greater than 200k and here one two three four five and six there we go and we're going to move it here arrange it in ascending order and we can see our account balances are increa set up in the ascending order okay uh account balance groups next we have the credit scores and we're going to do the same thing with the credit scores so right click the customer data query create a reference query rename it to credit score groups make sure we select the credit scores column right click remove other columns home remove duplicates see ascending order so see less than equal to 400 should be the first one which is not so again we will do add a conditional column call this candidates scores ID based on credit scores how many distinct values we have six so we need six clauses all based on credit scores there we go and the first value is less than equal to 400 next we have is 400 1-500 and then we have 501 to 600 601 to 700 7012 800 and then last but not least greater than equal to eight hundred one and the numbers are one two three four five and six and press ok so you see here this one didn't get it here as null didn't pick it up as null because looks like there's a space between greater than and equal to so I can fix it here but it's better to fix it in the source so where we created the conditional column of credit score so there what I'm going to do is change this notation remove this space press ok and by doing that because our credit score groups is based on the reference query so it references to it that got connected here automatically so that's how because we referenced it those steps get applied first and then this happens so this got fixed automatically here so now we can sort it actually change this to whole number bring it to the first and arrange it in ascending order let's look at this we're going to change the data type here as well this is whole number and then we already have it arranged and this one also account groups ID is a whole number and it's already arranged in ascending order okay so that is that so we have kind of modeled our data to some extent so we created we cleaned our data and then we did this this is part of modeling where we organize this in a way that will help us which analyze and visualize this data effectively so once we are happy with whatever preparation we needed to do with the data then we can just go home and click close And apply and by doing this step we are committing all the Transformations they get applied to the entire data set and then that get data gets imported into our power bi data model so here we have the data so we can go to the data View and we can see here all our queries listed here now let's go to the model tab here you can see let's just make it a bit smaller so we can see all the tables so we have the customer data which is our main fact table which contains all the data main data and then we have all these reference tables so the way you want to look at this model is prepare your reference table on the top we call them as lookup tables as well so look up so you look up from here to there so that's why lookup tables so it's best to like to make it easy for you to visualize these relationships it's better if you do like this where you place this on the top so we have the account balance credit score groups and they are all linked with the account balance it's linked with the account balance column the credit scores linked with credit scores column and then eight groups so these have already been pre-detected by power bi if I need to you can go here look to manage relationships on the Home tab and you can go on any of these relationships and you can edit them so here it's linked with this account balance this account balance with this account balance which is fine it's a many-to-one relationship because there's only one value for many of these in this table so similarly we have all the other two relationships here as well we don't have any date field in our main data so we dot we don't need any date table as such for this particular analysis so now moving on to the next step next step is you look at the main facts in there that you want to create some sort of calculations and Analysis on so we want to calculate first look at number of customers okay so that will be one of the measures so just right click on the customer data table and click new measure and there we have it this is where we will type our Dax to create this measure so we are calling this number of or you can just say customers as well if you want if it makes it more sense sometimes just calling it customers is also fine how many customers so just customers 10K customers whatever so what we can do is because we have the customer ID which is a distinct value for each customer so we can just use that to count this customer ID from the customer data group in the data table close this enter and this is a whole number so you can always check whether you got it right by creating a card and pulling so here this calculator symbol shows against a measure so you can see this is a measure customer so here 10K customers so that's that's kind of meaningful useful here so that's I'm happy with that next we want to create a measure that gives me customers that left so again customers data here customers lost okay and the way we want to do it now is we want to look at the column which is the churned column or churn status column where we the value is churned so those are the customers that have left so we need to filter while we are counting so for that the best way to do it is using the calculate formula so what are we going to calculate we're going to count in the customer data churn status okay we're going to count where the churn status is equal to churned okay and this will also be a whole number so I'm just going to copy this card here and put the customers lost field in here so we can see how many customers were lost 2037 customers were lost next we want to now look at his churn rate which is the customers lost divided by the total number of customers represented as a percentage so churn rate so we can reuse our measures so customers lost measure divided by the customers measure which is the total number of customers and if we press enter here and then change this to percentage and we'll keep it to one decimal value turn rate change it to one decimal value and again I'm just going to copy this card [Music] here instead of customers lost I'm going to put the turn right here and it's 20.4 percent so we have the churn rate we have number of customers we have customers lost so those are kind of the main measures so we don't need to look at customers lost here I think this will I'm going to remove this one so we just want to look at churn rate and we want to look at total number of customers I'm going to minimize this filters pane don't need that for now so that we get more working area we will adjust these visualizations and spacing and all those things to make beautify the report later on for now let's just create the visualizations that we need to get the right type of insights once we have done that next thing I want to do is now I want to get a good Insight of the data in terms of what sort of distribution is there between male and female between active and active and all those things so for that I'm going to use the donut chart and in this donor chart we're going to look at first is the gender so gender becomes our Legend and the values is the customers so total number of customers gender and I'm going to just quickly format it where I want to say the legend should be top Center and the title should be just fine gender and here internal the title I want to place it in the center okay so that is my one of the donut charts and I wanted what I want to do is I want to create multiple of these but because it says male and female and it says customers by gender we don't need this title for the Legend as well so I'm just going to remove that again to save space so for the legend go and turn off this title so there we have customers by Legend male female kind of very very easy to read now now what we do is just because we've already done the formatting just copy paste this particular visual we're going to replace the gender with activity status and we see active inactive split of that it's already formatted for us and next one I want to look at is whether they had a credit card or not so for that we look at the credit card status so just put that here and we can see not owned and owned that split is pretty visible let's look at by country so what's the split by country so just change the legend here to Country now and we have three countries France Germany Spain looks like the biggest number of customers here 5K of these are from France and one more we're going to do is so we have four donor charts here so let's copy paste this one and obtain this to so we have looked at turn rate churn status country and maybe let's look at products as well so just add the legend here for products and then here we can speed the split so 5K of the customers are for product one almost 5K 45 percent so just slightly less than 5000 for product two and there's a very small number for product three only 266 and then even smaller for product for only 60 customers with product 4 so numbers very very small for those products so let's make space here for our fourth chart s for now I'm not worried too much about the spacing we'll come back to this and adjust the spacing so there we have those charts next I want to now look at is how does this look at for Say by age groups so the chart we're going to pick is this one here line and clustered column chart so click there the x-axis we're going to put the age groups so go to the age groups table and put the eight groups on the x-axis and on the y-axis I'm going to put the customers so how many customers and then we also have a secondary y-axis which is will be a line chart and we're going to put here the churn rate and if I just make it slightly bigger first thing we notice here is that the ages are not sorted correctly so let's go to the age groups column select it in the age groups table and then here you should see sort by and select age groups ID and that will sort the age groups now here in this particular table chart go to more options we're going to sort the access by age groups and we want to sort it in ascending order so now we have the eight groups sorted in ascending order make a where it's easier to read these ages so I want to keep it in one line there so less than 20 21 to 30 31 to 40 so on and so forth we can see number of customers for each age group and also the churn rate looks like there is the churn rate is really high for the age group of 51 to 60. now first things first I just want to change the formatting of this so I want the title so go to General here title make it Center aligned and in the visual category for the legend again make it top Center okay and the title for the legend we don't need it so I'm going to keep it off so customers and churn rate by age groups Now by naming our columns the right way we don't have to change the titles they become meaningful automatically this all depends on how you name your columns and how you name your measures so here customers and churn rate by age groups if you don't if you feel the customers itself is not meaningful maybe you can change it to number of customers or total customers okay so here we can come here to the customers measure and change this to number of customers and you press enter and those charts get automatically updated but now you see it takes up a lot more space so again depends on how crunched you are for space sometimes you might have to pick a suitable name for your measure so I'm just going to keep it for customers for now so we have this chart here another chart we're going to create is just for ease and save time I'm just going to copy paste this particular chart and the second chart I'm going to do is I'm going to instead of looking at the age groups we still are going to look at total number of customers in churn rate but we're going to look at it from the credit scores perspective okay and again it's not sorted correctly so make sure your credit score column is selected sort by customer sorry the credit score ID and then here for this chart sort the access by credit scores and then sort it in ascending order and there we have it now we can bring it into one line like that I think that's it so that's the credits course and everything else is fine there and we need one more chart so let's make it closer to this and copy and paste this and the third one is the account balance so now instead of eight groups we're going to look at the account balance to just change the x-axis to account balance and again we need to sort it so select the account balance column there so sort by account balance ID and then select more options here sort the access by account balance and then sort the access in ascending order so we can see here 0 1 K to 10K 10K 200k so on and so forth you might need to address the space there so that's pretty much most of the charts we wanted to do here are done I'm just going to make this one bit more smaller okay so as I said most of the charts are done now another thing I want to do here is I want to provide a drop down where somebody can just look for customers which have been lost so for that we're going to provide the slicer okay and the slicer is based on churn status okay and it's got two values and I'm just going to go to the formatting here and change the slicer settings instead of vertical list to drop down and make the small and and also we can reduce the width as well here we don't need it to be this wide because there's only two names in there and bring it here to the top okay next is also I want to create a gate here which shows us the churn rate and look at it from what's the maximum churn rate minimum churn rate what's the target generate and then how we are doing against that so if I bring in the gauge visual we need the values we need is we need the actual value which will be our churn rate measure we need the minimum churn rate maximum churn rate and we also need the target generate the easiest option for this is if you go to the transform data open power query again and then in the customer data if we go and add column and we just add a custom column so so we need three columns min churn rate and this is going to be zero so okay and this will be a percentage so it will show zero percent there next one will be another custom column this will be Max generate and this will be one because we're going to show it as percentage so one becomes hundred percent so let's change the data time to percentage that's 100 and also we're going to add the target which for this one here is say 15 percent and this is just from the zoom number for here it will depends on a real business what they are and depending on the business sector the churn rate will vary in terms of the target what is a good size sub for some Industries like say utility companies and there's less Choice available so the turn rate is low whereas a mobile phone providers the channel it is Highway people shop around and change their mobile providers so depends on the nature of business and the sector so this target will vary but for now we are saying it is 0.15 so it becomes 15 percent and again change the data type to percentage now come to home and close and apply okay so now let's put the populate the values for this particular visual so the actual value here is the churn rate which is 20.4 percent four percent the minimum so let's find the minimum churn rate so that will go there and instead of sum we have to change this to minimum the maximum join rate let's change this to maximum so it's 100 percent and the target generate let's put that here and change that to also minimum maximum whatever it doesn't make a difference because it will just be one one value okay and look at the title there meaningless anyway we don't need that it's self-explanatory so we'll go to the format visuals go to General and turn the title off we don't need it let's make it smaller and see if we can fit it here at the top okay there we have it so that's that's that I think we've pretty much done in terms of what we wanted to create here now I can look at the churn rate so if I just want to see mail so mail is the dark blue one here if I click there for mail customers the maximum churn rate is in the category of 51 to 60 year olds which is 47.5 percent pretty bad but the overall churn rate for male population is 16.5 and similarly the churn rate for the as per credit scores the highest is for you know people with really low credit scores less than 400 and for the account balance the churn rate is high for customers with really high or the highest account balance so people with more than 200k in their bank accounts they shop around because you know they have money in the account they want the best interest rates and best facilities and best customer service so they shop around so uh so that's that's we can look at from gender perspective so now this number and this number is redundant so it's like showing twice so we I'm just going to remove this visual we don't need that and we have this here maybe we'll change the order these two visuals and bring this here this formatting kind of thing is the last thing you do first you create your visuals look at whether it's giving you the required meaningful information or not that's the most important thing you want to do first okay now in terms of making this visual beautiful we want to look at go here View and we can pick up some sort of theme from here pre-built theme so let's let's pick up this theme which is a dark theme so that's that but maybe I don't want a dark theme for this one again depends on sort of what sort of visualization you're creating you can try different themes from here it just push button anyway it doesn't take long to try different visuals I think I like these colors the green and this one but I'm going to change this so you can modify a theme quite easily basically so if you have applied the theme you like the colors but I just want to slightly modify these themes so you can go customize current theme and you can select these colors so this green one I want to make this into like a rust color so let's change this here to a dark kind of rusty color and this dark blue I want to change this to a almost a gray but a dark gray and quite new to Black and the third color I want to make it a bit of orange but a light orange that sort of Orange okay and also the page you can change the page background I don't want it white I want a a light so that sort of color and I'm gonna copy this code and change the page background also to this okay and also the visuals we want to leave it white color transparency we don't want it to be transparent so zero transparency don't want any borders and if you want to switch on the borders you can and make them round so again change this color to White and then you can specify a border so for all visuals it's five pixels we can apply some sort of header background colors and all those things we don't need text colors this particular theme doesn't allow you to change the text colors and now the filters pane you can filter cards you can supply filter background you know all those things for now let's let's just see what happens when we just click apply so there we have our report now the background for our charts basically for visualizations is 100 transparent so let's go here and go to General go to effects change the background make it 100 White entering the transparency to zero and there we have it so that's what it looks like with that but now with this one the legend is hidden we want the legend to be visible as well okay and the title color is not great actually it's too light so let's make the title color a bit dark and there we go so that's our chart one of the charts and once you're happy with the formatting of this one you can go select the chart go to home ribbon click on format painter and there you go automatically you can apply the formatting to other visuals so you don't have to individually do this for your chart so there we go we have applied the formatting for all three visuals pretty quickly another thing is we want to apply the same formatting for the this visual as well so now let's do that and okay so visuals are ready and now we want to look at the header as well so for the header part I'm just going to go insert shapes and we're going to insert a rectangle at the top you can beautify your report as much as you want they can spend a lot of time on creating and visualizing but main idea is not to learn the beautification of these reports you can now go to format here and send this to you back and also I want to go for this shape and change the style backgrounds to White okay and the Border I want to change the Border also to quite okay I want to reduce the height of this one so we have some sort of space there between and see what it looks like okay there we go so now we can see what these things in terms of the cards and other things that we put up what they look like on this visual so make it smaller or bigger adjust the spacing and make this also a bit bigger if there is base for this one the turn status looks really really small so slicer header I want to make it slightly bigger 10 maybe 10 is fine and the font color let's keep it consistent change it to that one okay and also I want to put a title for my report and we're going to call it turn analysis and make this text sign say 32 change the color to Orange make it bold let's just change the font to see and maybe actually I can change this to you port let's see what it looks like yeah I think that looks better so the size here of this text box and then drag it here to the top and then I'm going to add another text box and put here Bank customer the and we're going to change the size so to 12. and change the color to this gray make this box smaller and bring it here under this okay that's what it looks like and now next thing is we want to make these of the same size so maybe in the properties to get bit more control over the sizing the height is 254 we can actually reduce the height to say 245 and we can change the width to say 244. actually let's change it to 245 to 45 so it's a square to 45 . 45 45 and last but not the least 245 and 45 so there we have it if we align these sort of make sure we align these to the edges there and then if I select all these visualizations all five go to format and then align and I will say align middle and then I want to distribute horizontally so that the spacing is equal between them and there you go that's what it looks like now now these three again we can increase the height slightly of each one just to so that the spacing between these visual kind of remains consistent that's fine so let's look at the general properties here so the height is 340 and the width is 433 now we need some spacing between these so this one I can reduce the width without affecting the graph too much so let's see how much I can reduce it without making it completely unreadable I think that's fine this one can move here so you can increase the width here as well so again you can be very precise with these measurements I'm just doing it quickly just to save time I think pretty much there another thing I would like to do is just add some separation between these three things so again go to insert go to shapes and insert a line and I'm going to change the rotation to 90 degrees so it's becomes a vertical line make it bit smaller reduce this width here we don't need it to be this wide here to the top again the height needs to be reduced further I think that's fine a bit smaller from the top I think that looks pretty good just copy paste it and put one here in between these two and then put one here between these two here and for all these I can select all all of these and obtain the alignment so again format a line and we want to align the middles and there we go that's what it looks like maybe I just want to add another thing here is make this line show up here at the end it's slightly bigger to match the Box and also the border let's change the thickness bit and now what I will do is I'll move this text bit other way from it looks a bit dark okay the visual borders don't need that powder color let's change that light must be the same here for this one this one so there we have it that's our chart we can test it so if I want to look at data only for customers which are churned it doesn't look very meaningful but not churned you can see and also you can see how if I only want to look at people who own the credit card you can select here you can see the overall um so churn rate is 20.2 Which is less than the overall average seven thousand customers seven thousand customers own it but the churn rate is highest for 51 to 60. so looks like the churn rate is highest for 51 to 60 customers across many of these factors and 200 is high for people with really bad credit scores less than 400 and this varies depending on what we are looking at so now let's look at people who don't own a credit card for them again the highest is for the ages is always 51 to 60 looks like and credit score but if they don't own then again one of the things is people with high count balance looks like they shop around and switch around their accounts so age groups 51 to 60 low credit scores and people with really high account balance more than 200k in their account balance Also let's look at the products as well so these product one it's 27.7 the churn rate is high for this product one obviously it constitutes a big percentage of the customers more than 50 percent but the factors are same there if you see for product two it's kind of same 51 to 60 all that and but for customers product to a lot of customers between this range of 1K to 10K account balance the churn rate is 100 so need to look at that why that is and see how those customers could be retained and if we really makes it sense to retain those customers as well so how many of those customers are active and inactive is another thing so the big chunk um is so if I was to select this inactive customers as well you can see now again the overall percentage 26.9 so so you can play around with this so the business user can now play around with this and formula from a sort of look at what's what needs to be addressed what are the key factors and what they can do about it also for this chart you can try the area chart as well and so that looks pretty good so if you see here when you have two access you can look at area chart another thing to look at is the scale here for these for the secondary axis so it's starting with zero K but this one only goes up to 60. so it looks they're all same but this goes to 100 this only goes to sixty percent maybe it's better if we fix that so if you go and change the secondary axis the minimum should be zero and the maximum should be 100 similarly this one also let's fix this instead of Auto to zero and this two one and the last one also zero and this one also one so there we have it and also let's add markers makes it look better so on I think that's it that's the what I wanted to do in terms of the report now you can once you're happy with it make sure you save your work so save call it customer analysis I'm going to say here demo save it and then I'm already logged into Power bi service with my account if you are not you can create a free account and then once you're signed in you can click here to publish and we'll show you where you want to publish it I want to publish it under my workspace click select and now this report is being pushed onto power bi service and once that's done you can click here on open and this will open this particular report on the power bi service and now from here you can actually export it you can share it so you can share it within your organization there are other things you can copy your link send it across so permissions and all those things this is a whole thing about it which is not part of this particular dashboard the idea was to learn customer Channel analysis but you can do so many things you can also export this as a PDF if you want you can also create dashboard out of this so this is a report this is not a dashboard so create a dashboard you select what you want to include and just pin it and by doing that you keep on creating a dashboard we'll do that in a separate video but for this one I think that's what we wanted to cover last but not the least let's rename this tool action analysis so this report page and you can also push these changes so by pressing save and it will say here and then when you press select it will say that it's an existing report and you can replace it with this new one so that the tab name is also correct and it's updated on the online report as well so there we are we started with raw data in a CSV file and understood what customer Channel analysis is and then we clean our data we modeled it we analyzed it we created our measures and then we visualize it and finally we uploaded it onto the power bi service for sharing so basically we followed the end-to-end process step by step and created our customer Channel analysis dashboard I hope you have enjoyed learning this make sure you follow along use the practice files provided in the description create your own version and upload it onto the GitHub or somewhere create your own repository of projects my mission is to help you learn this practical way so that you become confident of working with any type of data cleaning it preparing it modeling it analyzing and visualizing it you should be confident with the entire process and using power bi as a tool to achieve that okay so if you have any more questions or comments regarding this video do share them and if you like what you learned today make sure you subscribe to the channel to get notified about the future videos and also share it further if you find and if you think that somebody else can benefit from it that's what I'm doing here I am creating sharing this knowledge for the benefit of everybody and you can help spread this further so that more people can benefit from it okay so thanks for watching and I will see you next time

Channel: BI With Gurpreet

Views: 7,925

Keywords: bi with gurpreet, biwithgurpreet, free power bi training, gurpreet dhindsa, learn power bi, master power bi, power bi components, power bi dashboard, power bi desktop download, power bi desktop training, power bi exercise, power bi exercises, power bi masterclass, power bi mastery, power bi platform overview, power bi practical, power bi project, power bi training, power bi training gurpreet, power bi tutorial, power bi tutorial for beginners, power query training

Id: HHu0FLM6Fp0

Length: 75min 11sec (4511 seconds)

Published: Tue Jan 17 2023