Objective
In this article, we will be talking about the best way to find what products are held by customers of different ages at your financial institution. We will explore the following topics:
How to gather account-level information about your customer by age
To begin gathering the data that we need in order to figure out what products our different age bands have, we need to answer a few questions: What product types do we care about? What age groups do we care about? Do we only want to look at one age band or all age bands? After we have answered these three questions, we can begin to surface our product information in Core iQ.
To gather the different account-level information from our core data, we will want to use the following Account Listing reports:
- Core Deposit Account Listing Report
- Loan Account Listing Report
- Time Deposit Account Listing Report
- Safe Deposit Box Listing Report
- Ancillary Account Listing Report
If we only care about a certain product mix, then we want to run only the reports dealing with that product type. In this case, we will assume that you care about a holistic view of your customers and have run all five Account Listing reports. Once you run the reports, you will want to export the findings to Excel. By exporting to Excel, we gain access to additional metrics that we can use to create unique age bands for our customers. From there, we can merge our five Excel exports to create a document that houses every product a customer has.
How to use Excel to further analyze the product data
This will obviously be a very large document, so now we need to discuss how to use Excel to create a simple Pivot Table to manipulate our data into custom age bands and rollups of the total number of people with that product. To create a simple Pivot Table with customer age bands, we will start by selecting all of the data in our large Excel worksheet. Then, use the Insert menu to insert a Pivot Table. Your range should already be correct, so you will want to create your Pivot Table in a new worksheet. Next, add the following fields in these areas:
This should get you a list of products in the first column and the complete breakdown of ages across the top row. To group those ages together to create age bands, we will right click on the top row (any age number) and select the Group and Outline option from the menu. Here, there is an option to Group the data together. Excel will naturally select the first and last age in your worksheet, but we recommend working from a starting age of 25, and an interval of 10. This will create commonly used age bands (25-34, 35-44, etc.) for you to view the total number of accounts relating to each age band and product.
If you want to view the data as a percentage per age band, then simply click on the icon next to the "Count by Age" field. Then, select the Summarize data as option and choose '% of row' from the drop-down. This will change each entry from a numeric count to a percentage of the entire row, so you can see the percentage breakdown for each product across age bands.