quick wins and shortcuts to get the most out of idea
TRANSCRIPT
Some quick wins and short cuts - a typical IDEA
investigationAndrew Morton
Risk MI Services
HBOS
1
Some quick wins and short cuts - a typical IDEA
investigation• 20% of the functionality gets you 80% of the way
there
• Plus, there are some little tricks we have found useful
• These techniques should yield some tangible outcomes, and give you the confidence to explore more of the functionality
2
Personal Loan applications March –September 2005
ROLLNO
STATE
DALLOC
DREPDEC
BRANCH
PERSNO
ARRADV
POSTCODE
MTHSINARR
APPLISCORE
LOANAMOUNT
SURNAME
CUSTDOB
Account number
Loan status 4=application, 8=open, 12=declined, 16=repaid
Date account allocated (coded)
Date account repaid or declined (coded)
Allocating branch code
Personnel number of allocating colleague
Amount of arrears or advances
Customer‟s post code
Months in arrears
Application credit score
Amount of loan (negative)
Customer‟s surname
Customer‟s date of birth (coded) 3
The initial investigation
• Summarise account states
• Summarise colleague performance and decline rates
• Identify extremes of colleague performance
• Identify multiple loans to the same customer
• Identify accounts for further investigation
• Convert coded dates to IDEA dates
• Summarise sales volumes per month
• Calculate age of customer at date of application
• Stratify sales by customer age bands
• Identify accounts for further investigation
4
Using a virtual numeric field to obtain a subtotal when summarising
@if(STATE = 12 , 1 , 0)
Condition
Action if condition is true
Action if condition is false
If your file has a „Yes/No‟ field you want to subtotal -
@if(<fieldname> = “Y” , 1 , 0)
5
Multiple sales to the same customer by the same colleague
Key field summarisation –
PERSNO
POSTCODE
SURNAME
CUSTDOB
and optionally to ID sales on the same day –
DALLOC
Summarise LOANAMOUNT
Criterion STATE = 8
6
The initial investigation
• Summarise account states
• Summarise colleague performance and decline rates
• Identify extremes of colleague performance
• Identify multiple loans to the same customer
• Identify accounts for further investigation
• Convert coded dates to IDEA dates
• Summarise sales volumes per month
• Calculate age of customer at date of application
• Stratify sales by customer age bands
• Identify accounts for further investigation
7
Date conversion
• HBOS dates are in the range –99999 to 99999, where –99999 is 01/01/1850
• 01/11/2005 is –43082, and 99999 will be 30/07/2397
• 01/01/1900 was -81739
• @daystod() converts the number of days since 01/01/1900 to an IDEA date
• Therefore, @daystod(DALLOC + 81739) will convert the HBOS allocation date to an IDEA date value
8
SAS dates
• Number of days since 01/01/1960
• 01/11/2005 is 16741
• Days elapsed from 01/01/1900 to 01/01/1960 = 21914
• @daystod(SASDATE + 21914) will convert SAS to IDEA format
• You may encounter SAS date/time format (number of seconds since 01/01/1960)
• 01/11/2005 is 1893369600
• Use @daystod((SASDATE / 86400) + 21914)
9
Using dates
• Elapsed time between two events, e.g.
– Service quality (account application to account opening)
– Product persistence (Accounts closed within three months of opening)
• @age() returns the number of days between two dates (most recent first)
• Customer age at account opening could be calculated as @age(@daystod(DALLOC),@daystod(CUSTDOB)) /365.25
• Can avoid need to convert into date format and back, using (DALLOC – CUSTDOB) /365.25
• To return age at last birthday, use@int((DALLOC – CUSTDOB) /365.25)
10
Techniques and Findings
• Virtual subtotal field in summaries
• Virtual percentage field in summaries
• Summarise by customer using unique identifiers
• Identify exceptions by occurrence and value
• Convert proprietary date fields to IDEA dates
• Calculate elapsed time using proprietary date formats
• Use virtual month field to summarise by product
• Identify high-activity colleagues for further investigation
• Identify colleagues with low decline rates for further investigation
• Customers with multiple loans by same colleague in short time period
• Quarter-end activity peaks
• High-value loans to older customers for investigation
• Multiple loans to older customers for investigation
11
QUESTIONS???????
12