How to identify the real issues within a branch attrition rate.

If you have already built a strong retention/attrition program and have started to identify issues within your organization, it is now time to put in place a data collection tool that will allow you to understand what makes up your attrition rate.

It is one thing to reprimand associates for bad behavior, however another if you are doing it without any knowledge of how the issues occurred.  With any project I research it is not to call out anyone, but a way to look at a process, improve on it, then coach or redefine a new behavior to obtain the needed results.

Here is how to look at breaking down attrition: Accounts close all of the time for many many reasons, however of all of the reasons, can we determine if they are life cycle, process issues, relocation, death, divorce, a closing of a location, personnel, ingress, egress issues, NSF or anything that force the client or the institution to close an account.   

What you will find is that of the 10% to 25% of those clients that attrited, most are issues beyond ones control, however what this exercise will accomplish is of those that can be controlled if fixed can it change the retention rates.

I must add a simple note here.  At times depending on your clientele, attriting is not a bad idea.  I'll leave the deeper aspect of identifying profitable clients to a separate blog, however sometimes retaining clients just to retain them is not always a good thing.  That is why it is so important to research all aspects of attrition before coming to conclusions.  This information is to identify issues then to fix them...not a way to hang and burn people.

You are first going to have to find a way to collect data from the front line.  If your core system has the ability to collect reason codes that will be your best bet.  It may be as simple as an open field that you can use, however contact your IT group to find out.

Our sales group implemented the first go around and boy was that a failure.  The attempt was knowable, but the results were a mess.  In their defense they gathered the troupes and implemented the data collection process, however that is about a far as it went.  It got better, however keeping 1,500 associates capable of entering data informed as to the process, and the churn factor of employees just didn't help.

Keep this in mind when working with data and lots of it there has to be commonalities so one can work with and review the results quickly.  Problem #1 the sales staff chose a alpha numeric field to enter information in.  This is fine if you truely want to read about the issues, however disastrous if you have 4,000 closed accounts in a month and they all have different verbiage.  The only way to review the data is to look at it and try to match it in silos.  It is just not going to happen.

I quickly made a change to the input of data.  With the help of the sales staff I asked for as many common issues we could think of then I assigned a numeric code to each.

They were as follows:
1 - Rate
2 - Facility Closure
3 - Moving out of the market
4 - Account funds used for purpose intended
5 - Deceased
6 - Removal of name
7 - Dissatisfied with service
8 - Dissatisfied with product
9 - Competitors promotion
10 - Convenience - moving within the market
11 - Transfer funds to another account within the institution
12 - Divorced
13 - Other
14 - NSF/Overdraft
15 - Married
16 - Fraud
17 - Dormant
18- No longer needs accounts
19 - Lost job


If you are looking to implement such a program use these only as guidelines.  You may need to begin with a few then add as needed.  I provided a simple sheet that could be posted close so they could refer to it as often as needed.       

After implementing these new standards it became easier for both sides.  It was less work for the associates and for me I could now do something with the data.  We still struggled with even this part for some associates who felt they were being helpful would instead of just entering the number they would enter "#12" or "number 12" instead of just "12."  If I had the capabilities to change the field to just numeric I would have.  If you can, do it.

I was able to identify those associate issues and then passed that information on to their branch manager.  After some time it got better.

The results in graph form allowed myself and associates to quickly see where the issues were.

Once a month in the core system I had a query run in periodic options that would collect the information I needed to build the report.  I was able to through Excel build a path through an ODBC function with iSeries that after building macros would shake hands with the query grab the data then bring it back and configure the report they way I wanted to see it and deliver it in graphic form for the company and by branch.  This report could be built in seconds each month.

The report provided results in three multiple ways:  1. Numerically providing numbers associated with a graph.  2. The same results, however in graph form.  3. The numeric data was broken down by branch. 4. Each code was given a factor as to if an associate could have changed the behavior of the client which became "issues."  A percentage was provided as to of all the closed accounts what percent was the branch in control of. 5. Where and who the data came from.  


I must bring back once again how important it is for you to learn Excel.  This report on its own could take several hours to build, however once set up it takes longer to open the report than it does to run it.  Learn your core or find someone in IT to work with you.  Have them look into iSeries or ODBC in Excel to connect with your core system.  Learn how to build macros in both your core and Excel.  And also learn how and understand pivot tables. 


It will be hard at first, however once you get the hang of it you will be able to understand the data, build it quickly and start spending more time analyzing it. 


If you need additional help please feel free to contact me through e-mail

Retention - Attrition

Retention; What does it really mean to a financial organization?  To most, if not all, it is your life-line, it is the way you stay in business.  It is the difference between knowing your customer and producing life-time or even generational value for your organization or constantly looking for new customers.

Here is a simple example:  If I bring 100 new customers to the company in January and then 12 months from then I look at these same customers and only 75 remain, my retention rate is 75%.  The inverse of this or 25% is the attrition rate or those customers that have left.

Taking this example one step further; If a branches goal was to increase clients by 10% of the prior year and the total number of customers was 100, a manager would have to work hard to bring in 35 new clients because 25% would attrite throughout the year.

I actually worked at a financial institution that for years didn't understand why their managers could not even come close to meeting their annual goals.  It was because their models were flawed, they just didn't account for attrition.

So what does one look at for a retention rate?  If you ask most MCIF vendors the likely answer with be household retention. However there is one fundamental flaw in that analogy.  If your organization offers Loans, certificates of deposit, or even mortgages you will never know for sure, or you will get a false sense of security because of their terms.

What you want to understand is, are you losing customers and how many.  Take for example a mortgage; Because of the time and cost commitment if I get upset at the organization I would keep this product there and possibly move the rest.  If I was looking at household retention the household from my report would not show a change.  This would also occur with any other loan or even the length of a certificate of deposit.

This is the  reason why I look at transaction accounts.  Interest, non-interest checking, savings, and money market accounts.  Now I don't look at these individually, however as a group.  I would however always split out business from retail.

Now you may be saying to yourself why not separate them and look at a retention rate for each? Well I found this out the hard way, I did just that for a period of time and I found that depending on the interest rate paid clients would shift their deposits if one would pay a higher amount.  It was always interesting getting list requests when rates would change.  Branches as a part of what they felt was a fiduciary responsibility to the client would call them and tell them about the new program.  This of course would increase their sales numbers, however increase the interest expense to the company.

Those issues were to be handled by senior management, my job was to correctly identify how well we retained the customer.  By grouping these products or services I could circumvent all of the issues and provide a single retention number no matter what happened based only on the account.

Here could be your second question; Why are you looking at transaction account retention instead of household retention?  Here is the simple answer; If I'm upset a the organization what would be the first accounts that I would close and move?  Any or all of my transaction accounts.  The other accounts would follow only when they matured or were paid off.

So it is very important to determine which branches have the highest attrition rates.  If you are looking for an average it will be some place around 86%.  We had locations any where from 64% up to the high 90's.

My suggestion would be to use this rate to find issues or bright spots within the organization.  Those that have high retention rates find out what they are doing right.  Those that don't will require additional research.  Is there a personnel issue?  Is there a process issue?  Look at everything and work hard to fix it.  


We posted ours by trend each month for each branch then for that month ranked each branch.  You didn't want to be at the bottom.

Going back to the first example, if I can control my retention or attrition rate I will one have to not work as hard to reach my goals and two increase revenue to the organization.

Although I've answered these questions I'm sure you are asking more.  How do I create a retention rate.  I'll provide you with two examples, however each will require either equipment or an advanced knowledge in Excel.

The first is to deploy the use of your MCIF system if you have one.  You will need a research file account by account of those products or services that will make up your transaction group.  You will have to obtain this information in a historical time period 12 months prior to the current date for comparison.  Now you can use a different period of time, however your results will not be comparable with peer averages.  

Each month obtain this information by branch and either build a trend line from history or start now.  The information will tell you where your issues are and once fixed you will begin to see a change in the trend line.  Hopefully upwards.  Just remember if you fix the problem, you will not see results the next month.  Since you are looking at 12 months each month will get you closer but the full affected results will be after a year.


The second option requires you to begin now gathering the data, however it will be a year before you can begin reviewing the results.  Unless you can get point in time historical data from your core system.  

Either by query or asking your IT group obtain a list of account numbers of your service or product group you are going to review.  Ask for the output to be in Excel providing only the account number and branch number.  Do this for each month until a year passes.  Keep each file separate and date them.

When your starting month comes up 12 months later ask for the same information.  Save this information for you will use it once again 12 months later, so save it.

What you are going to do is build a Vlookup table comparing one months year to the next.  I could explain this function in Excel, however it would be best to obtain an Excel book or Google Vlookup and begin understanding them.  If an account is still there indicate the presence by an "X" in another column.  Once finished count the "X's."   With Excel functions this can be completed in seconds, so it will be worth your time to familiarize yourself with software.  

So if you have a month end total of 1,000 accounts, July 2009 and you compared these against a new month end total of 1,200 accounts, you may have 850 accounts from the original 1,000 that remained.  850 /1,000 = .85 or 85% where retained or the inverse, attrition of 15% lost.

This should be completed for each branch and then trended every month.

Excel has some great functions that will allow you to build the branch distribution.  Just push yourself to figure them out. Get the book Excel 2003 or 2007 or what ever version you are working with.

If you do have issues and would like help working through the above information please feel free to contact by e-mail.

Just in closing, Retention is extremely important to the organization.  It can tell you where your brightest stars are and those that are not.  Use it to help coach those that are in need of help.

I've created retentions rates for retail and business transaction accounts, CORE accounts where a client must have a Savings, Checking, and a Loan product, then Checking and Internet Banking and Checking and Bill Pay Accounts.

The last ones were to provide a point on how important cross-selling is.  We consistently saw a 10% lift in transaction account retention if combined with and the client used Bill Pay.

Look for the continuation of this blog "How to identify the real issues within a branch attrition rate."   This blog will help you identify what percentage of attrition is affected by the organization and what could be affected by your facility, the area, products, or personnel.