Lending Club’s loan Dashboard Through Tableau Part 1
Today we will go through lending club loan dashboard visualization through the tableau bi tool. I am using the Tableau Public free version of the tableau desktop.
As you see in above pictures show different charts and graphs. We will go through each graph chart how it was created and why it was created.
We will be exploring Lending Club’s loan origination data. Please download the dataset in CSV format (loan.csv) and associated dictionary (LCDataDictionary.xlsx) from https://www.kaggle.com/puneeshk/lending-loan-club-dataset.
We will analyze the data and will create visualization (“dashboard”) to share this analysis with stakeholders. Assume that your audience will consist of Lending Club senior management and their business teams.
The graph is created based on three business requirements such as:
1. Assuming the loans with the status that are “Current”, “Issued” and “Fully Paid” can be called “Good Loans”, what is the percentage of Good Loans for each loan segment? Please segment the loans by term length (36 or 60 months) and grade.
2. Looking deeper at Good Loans, what can we learn from the employment characteristics of the borrowers, such as job title and employment length (years)? Are there any themes?
3. What are the most frequent Purpose values for Bad Loans? What can we infer from this?
Hands-on
First, we will create KPI which contain the following information such as:1)Fully Paid,2)Current loan,3)Recovery,4)Charged Off
Before that, we will create parameters as we will show KPI as per loan and member ID and parameters will help to create dynamic.
- Fully Paid: if [Select Measure]=’L’ THEN SUM([Loan Amnt])
ELSE Count([Member Id])
END and put loan status in filters and select fully paid options - Current Loan: use the same calculation but select current options in filters.
- Recovery: if [Select Measure]=’L’ THEN SUM([Recoveries])
ELSE COUNT([Member Id])END - Charged Off: use the same calculation but select charged off options in filters.
Assuming the loans with the status that are “Current”, “Issued” and “Fully Paid” can be called “Good Loans”, what is the percentage of Good Loans for each loan segment? Please segment the loans by term length (36 or 60 months) and grade.
First, we will create a calculation that will divide between good loans and bad loans such as :
GL/ BL: IF [Loan Status]=’ Current’ or [Loan Status]=’ Issued’ or [Loan Status]=’ Fully Paid’ THEN ‘Good Loans’
ELSE ‘Bad Loans’ END and bring previous calculations if [Select Measure]=’ L’ THEN SUM([Loan Amnt]) ELSE Count([Member Id]) END let us assume this calculation as K. Drag K calculation in angle and text in mark card and then select percent of the total from quick table calculation options.
for the second requirement we will bring the same calculations into the same marks cards but this time we will drag Term dimensions(contain 36-months term and 60-month term)from the dimensions panel and place them in columns along with calculation two AGG(sum(0)) and use the dual-axis method helps to create the donut chart.
I will continue Lending Club’s loan Dashboard explanation with another post.
