quick wins and shortcuts to get the most out of idea

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