Analysis of Bank Customers using Dashboard in Power BI
By pretending to be a Data Analyst from a Bank in Czech Republic, I hope to help the bank to increase their profits from issuing new loans and credit cards to new customers. Using the bank accounts and demographics data provided from the bank to create an Analytics Dashboard in Power BI. There are 2 matters that can be explored.
First, its marketing strategy, which group/area of potential customers should the bank be focusing on? We can look at the population and average income of the people in the region to decide where we can target our advertising/promotions on for their loan and credit card products.
Second, it is to identify the traits of bad credit customers, assuming customers who have defaulted on their loan or have late payments so we can avoid or set stricter conditions for loans to these people to avoid losses when in the event of default.
Note that the data have been divided into 8 regions within Czech Republic including its capital, Prague. Its currency is the Czech koruna (sign: Kč).
From loan dataset, we could assume that the year 1999, given that a 12 months loan issued in Jan 1998 is still in service.
And a 12 months loan issued in Dec 1997 has been repaid in full.
Data Modelling
Manage relationship
The “disp” file will be use as the main connector as it contains most key identifier (account id, client id and disp id) which can be use to relates the 8 data files together. The “district” file is use to link the client profile geographically with “district id”
Modelling for Gender and Age data
Notice that the Gender and age of the client are missing from the data. These can be formulated from the birth number YYMMDD where at months (the 3rd and 4th digits) greater than 50 means that client is a Female. We can create a column for Gender.
For birthday, we need to reduce the birth month of the female by 50 and then change the date format to DD/MM/YYYY adding 1900 to the year.
For Age, we shall assume it is year 1999 as explain previously and use it to minus from the birth year.
Replacing values
Set some fields to English for easy understanding, we replace values to English with the Power Query Editor.
Changing the order of Region name at Power Query
Duplicate the “district /region” then split column using space as delimiter.
Then merge column by Region and direction. Refer to applied steps for details.
Grouping of age by ranges
As the customers’ age ranges from 12 to 88, we shall group them into different generation age range for easier profiling. Referring to https://marketingartfully.com/customer-demographics-age-ranges-numbers/ (Jacobsen, 2019), we will group the ages into 5 groups.
The Gen Y are youths,
Gen X are young working adults, some starting their families
Baby Boomer are working adults with families.
The silent Generations some are working and retired, living on pensions.
The greatest Generation, retired elderly living on pensions.
Credit Rating and Loan Status
As the Loan status uses A, B, C, D which are not reader friendly. We can add a column to represent what it stands for, we also simplify the classification of those with late or default on payment as bad credit, refer to the table below for details on the new columns added.
Values of such as “account Id” have also been set as Text.
And District name have been categorized as place to be use for the map to show the sum of the inhabitants in each region.
Overview of Dashboard
The cover shows our objectives with 3 buttons gg to each respective page, demographics analysis for marketing, Loan analysis and Credit card analysis.
The demographics page displays the population distribution of Czech Republic and their average salary rank from highest. User can filter by region to see where are the people located. There is also a donut chart which shows the age group distribution which can be drilldown to gender. User can also filter the gender and the type of account holder.
The side of the loan page show the filters that can be used such as loan status. The top shows a table of the total no. of loan and the sum of the loan amount and also a bar chart of the loan amt. Lower half of the page shows traits of bad credit that we have found out which will be explained later.
The credit card page shows a table with the card issued each year from 93 to 98. There is a waterfall chart which give us more insights to the changes of card issuance per year. Another table that shows the no. of card issued for each age group with a bar colour conditional formatting where the higher the no. of card issue, the more of its cell will be filled. So, we can see that the classic card are hold by the baby boomers. There are also filter at the side to choose the card type and the region to drill down on the card ownership.
Key Charts, Analysis & Implications
Marketing analysis
One of a good indicator of credit worthiness is the income so higher salary would be a better borrower so we should target promotion and advertising for the population with the highest average salary, Prague, also noticed on the map at the outer circle of the capital Prague is Bohemia Central, the 2nd highest average salary. Since these 2 areas are very close to each other and highly populated. Unlike other regions where people are scatter around a huge area of land as shown in the map.
The huge gap (yellow arrow) between the no. of inhabitants and the no. of bank’s client in that Prague also indicates that this is a large untapped area of potential customers for the bank. We need to gather more data on the preferences of the people in the city to come out with good marketing.
Loan and Credit Analysis
The borrowing rate is the highest for Baby Boomers at 45%, taking up also most ½ of the loan borrowing. These Baby Boomers are our main target for our loan products, another significant group are the Gen X at 37%. The sum of the age from 20–54 makes up more than 80% of the borrowers. We can find out further what type of purchase they are making for the loan and provide more specialised loan with better interest or free items for them.
From further analysis on target age group, comparing the % of good and bad borrowers in each age group around 10–11% would default. (4.99% / 45.02%). We should look into these bad credit borrowers’ traits and improve our credit review at loan approval.
There isn’t a significant relationship to show that a credit card owner will default on their loan given that there are only 2 card owners that default out of 892 cards issued, less than 1%.
From the amount of bad debts, we can see that the Moravia North and South have the highest amount of defaults and late payment. (darkest red = highest amount).
Another trait of bad borrowers is that they have a lower average account balance that the average account balance of $34,060.66. As shown in these brown cells have highlighted all the borrowers that default and make late payments. We can consider adding the borrowers’ account balance as a criterion in our credit review for loan approval. Also, we should avoid giving out loans to those age 55 and above as we can see that those without loan (see brown cell in the “blank” column) have very low average balance of less than $30,000.
The “blank column” also flag out some potential good credit customers for us, highlighted in green. These are current bank customers are age 20–54 who have a good average balance with the bank. The bank can consider targeting them for direct marketing for its loan and card products.
Also from data we can see that the loan granted are short term loan, from 1 to 5 years. The bank can consider granting longer term loan to customers.
Credit Card analysis
Another income generating area would be the credit card, from the date we could see that the bank has been getting more aggressive in their credit card promotion by the increase of the card issued each year.
Using a waterfall chart, we further analyze the increase of card issuance year on year. Where green shows an increase in the card issue as compared to previous year and red shows a decline. A % change on the year on year can also be seen when we hover over the bar.
Limitations
More data on the borrowers could be collected for a better analysis of their credit. Data such as the customers’ credit history, being their payment history, total debt owned , length of credit history, its credit mix to gain more insights of the traits of bad borrowers and effective credit review for the future loan. We can get data from the credit card spending to also find out what these customers are interested in so that we can give gifts that are attractive to them to entice them to take up our loans and credit card products.
The data provided is from 1993–1998, it is less than 10 years and outdated over 20 years ago. More recent data would be able to give us a more relevant insights on the customers behaviours and demographics as technology have improve and make changes to people jobs and lifestyle.
More data on the population in each region such as the age, gender, job of the population would be able to help us to group them into better customer profile.
Video presentation
References & Images credits
Jacobsen, T. (2019, 03 19). MARKETING ARTFULLY. Retrieved from CUSTOMER DEMOGRAPHICS — AGE RANGES, GENERATIONAL NAMES AND NUMBERS: https://marketingartfully.com/customer-demographics-age-ranges-numbers/
Cover page background
https://eeagrants.org/countries/czech-republic
Credit card icon https://www.iconfinder.com/icons/2224480/card_credit_card_debit_card_master_card_icon
Loan icon https://www.businesstoday.in/top-story/what-will-happen-to-your-loan-after-banks-link-it-to-external-benchmark/story/380795.html
Marketing icon https://www.feedough.com/what-is-marketing-strategy-examples-components-planning/
