• MPColetti

DIY Project: How to Manually Decile Your Retail Customers Into Profitability Buckets

Decile analysis involves examining the profitability of your customers in terms of ten buckets of relative profitability, e.g. your first tier is your customers in the 90th percentile and above whom are more profitable than the 90% of your other customers. Decile analysis is not new, but capitalizing upon the strategy now can be more effective than ever given the array of delivery channels. Meanwhile, third party tools and consultants can be expensive in arriving at this analysis, so you may have to rely on what you have available internally.

This analysis is important for several reasons: (1) You want to retain your most profitable customers, so you may focus a greater amount of retention efforts or service resources toward them; (2) You want to move your 20-40% profitability bucket into the top 20% bucket, so you may target them with marketing relating to products they’re not currently carrying with your institution, but which may interest them in expanding their relationship; (3) 40-80% of customers may not be using you for a primary banking institution. Use this as an opportunity to target market this group on the value proposition you present as a primary banking institution in comparison to your peers and solicit with stronger incentives to convert these customers to you as a primary banking institution; and (4) for the bottom bucket, you may want to ensure the accounts are reviewed for dormancy and closed as appropriate.

So here’s a DIY process to employ using a report writer from your core system. Here’s what you’ll need:


(1) Generate a custom report from your core system which is sortable in .xls format. In this report populate the following fields and aggregate information either from a sub-report or separate tab within the same report:

  • Column A: Unique customer ID number at the relationship level or complete household number if you have one. This ID will start each row and each row will represent a unique customer or household at your bank. Each data point which follows should pertain to that unique customer ID.

  • Column B: Combined deposit ledger balance for checking accounts.

  • Column C: Total number of checking account units to relate back to the ledger balance

  • Column D: Any APY rate paid on the checking accounts, but we’ll assume they’re free.

  • Column E: If accessible, any aggregate fees earned on the checking accounts.

  • Column F: Monthly POS transactions for aggregate checking accounts for the cycle.

  • Column G: Combined deposit ledger balance for savings accounts.

  • Column H: Total number of savings accounts units to relate back to the ledger balance

  • Column I: APY posted end of last cycle for each savings account. If a tiered account, then use the blended rate.

  • Column J: If accessible, any aggregate fees earned on the savings accounts.

  • Complete the above process for both money market accounts and CDs until you have a complete set of columns for all account type dollars and units for the general deposit product categories.

  • Column K: Combined first mortgage loan balances.

  • Column L: Combined first mortgage units.

  • Column M: Interest rate posted end of last cycle for first mortgage loan.

  • Column N: If accessible, any aggregate fees earned on the mortgage loans.

  • Column O: Combined second mortgage loan balances.

  • Column P: Combined second mortgage units.

  • Column Q: Interest rate posted end of last cycle for second mortgage loans.

  • Column R: If accessible, any aggregate fees earned on the second mortgage loans.

  • Column S: Combined auto loan balances.

  • Column T: Combined second mortgage units.

  • Column U: Interest rate posted end of last cycle for auto loans.

  • Column V: If accessible any aggregate fees earned on the auto loans.

  • Column W: Combined credit card balances.

  • Column X: Combined credit card units.

  • Column Y: POS transactions for the credit card for the cycle you’re reviewing.

  • Column Z: If accessible, any aggregate fees earned on the credit cards,

  • Complete the above process for any other consumer loans which may be within your product suite.

(2) With this report, you’ll have approximate visibility into the wallet of each customer of your bank. You should be able to view their total deposits across the number of accounts, total loans across the number of account, interest expense owing on deposit accounts, interest income earnings on loans, fee income and POS transaction activity. These will form the basis of your decile profitability analysis.

(3) Products are generally profitable in this order with deposits weighted heaviest. Save your master report and separate the products into separate tabs. For each deposit and mortgage account, sort in ascending order of ledger balance with first with high balances at the top.

  1. Checking Accounts: No or low interest deposits are the best source of funding for banks

  2. Savings Account: Generally, savings accounts yield relatively lower rate deposits with more consistency in balances

  3. CDs: Though significantly higher yielding and close to the federal funds rates, there is benefit in the certainty of CD funds and withdrawal penalties to support them.

  4. Money Market Accounts: Like CDs, money market accounts often pay higher rates of interest now even closer to fed borrowing rates which makes these lower yielding products for the bank, but still better than the alternative sources of funding despite the variable balances as compared to CDs.

  5. Other deposit accounts

  6. Credit cards: Credit cards generate the highest interest yields and greatest interchange fee opportunities

  7. Second mortgage loan: Second mortgage loans generally come at higher interest rates than first mortgage loans and have greater fee income earning opportunity while still providing a lower degree of risk

  8. First mortgage loans: First mortgage loans, though often consuming more capital, yield favorable rate on large balances and can also provide a supply of fee-based income when portfolios are sold

  9. Auto loans: Auto loans today are yielding around the same as residential secured loans, often less, and carry lower balances, but are generally low

  10. Other loans

  11. Credit POS transactions: Sort ascending by total POS transactions

  12. Checking POS transactions: Sort ascending by total POS transactions

(4) Of the ascending sort now for each tab, identify the total number of customer IDs in column A and parse into ten separate equal buckets. Assign a score to each bucket of 10 to 1 with 10 being the highest, 1 being the lowest. For checking and savings account deposits, I would use a score range of 15 to 5 to correlate to the buckets to allocate greater weight to them.

(5) This part is important. Once your decile bucket score is assigned for the customer, you can then sort the master tabs back into ascending order by customer ID so that all of your tabs have the same customer list in the same ascending order so that the profitability score for the separate tabs will be the same customer in each row across each tab.

(6) I would repeat the above process for loan rates and POS transactions. For loans, I would sort ascending with higher rates should be awarded being awarded on a scale of 5 to 0 and working on only five tiers of 20% of each population. I would do the same for POS transactions, sorting in ascending order with the greatest number of transactions in the higher tiers. I would assign these lower scores in relation to the deposit accounts because the overall impact to profitability is of lesser weight. For example, it is wonderful to yield greater than four percent on a $650,000 jumbo mortgage, but the off-setting retail deposit requirements to maintain an even liquidity ratio for that jumbo mortgage against an can hypothetically devalue its position in your portfolio as an asset in this equation where your average deposit account might be closer to $3,000 on average. This is why its important to include the total units to understand the relationship between your overall portfolio to your average customer profile. This can help you in setting baseline profitability assumptions.

(7) Aggregate these separate tab scores back into a new column B in your master report by inserting a column. This will give the profitability score closely to the customer ID. To relate the new column B to the decile score from another tab, your master column B equation may look like this where you will aggregate the sum of the decile buckets from the respective tabs by customer ID row. In other words, you want to add the decile bucket score of each customer ID from each of the separate tabs: =SUM(Deposittab!AA1+Savingstab!AA1+Loanstab!AA1)

(8) By the end of the aggregation process, you should have a score on the master for each customer ID that relates to their decile bucket from each tab. If you have fifteen products, services and activities across fifteen separate tabs, and you have a unique customer ID that is appearing in the top 10% of each bucket, then that customer ID on the master will be a 150+ which would be your premium score.

(9) Now, you have a master sheet which you can break into ten further decile buckets for a master profitability sheet. Condensing the ten buckets into five is advisable as a better means of segmentation. Your top 20% now will be your most profitable. Your bottom 80% will be your less profitable customers.

(10) Work these profitability scores into your CRM and refresh them on a recurring basis.

Like I said, this isn’t a new concept, but it can be effective especially if you don’t want to deal with NDAs, third party vendor processes, SFTP transfers, or invoices for analysis that you might not immediately use. Give it a try with your core data and see what you find. You might be surprised!

4 views0 comments