Objective:
In this article, we will be talking about the best way to find the average number of products per product type across households using Core iQ. We will explore the following topics:
- What report you will need to run to find the rollup of products per physical address, as well as total balance per product type
- How to manipulate the data outside of Core iQ to analyze using Excel
How to gather data on the number of products per address
To find out the number of products that your customers have, you can use a few different reports. However, only one of these reports rolls the number of products up to the address level. To get this information, we will want to run the Products Per Address report, which is found in the Account Level tab of the Actionable Reports menu. This report will give you not only the number of Core Deposit/Loan/Ancillary/etc. products that your customers have, but it will also roll up their balances into a total balance for each category.
To get the broadest view for this report, we will leave the date field empty. This will bring in every customer in our financial institution, rather than those that became customers before or after a certain date. You will most likely want to exclude employees and directors using the Is Employee and Is Director drop-downs, which should be set to NO. Finally, if you only want to view Business or Nonbusiness accounts, then you can use the Customer Type drop-down to do so. Once you have this criteria in place, it’s time to run the report.
The results from this report will give you a breakdown of each physical address and the product adoption at that address across each of the main product types. You can export these results from Core iQ in order to see a listing of each product type for the different categories as well. This will give you a total number of products across addresses, but not an average across households. To do that, you will need to do the following in Excel.
How to easily find the average products per household in your Excel file
The data that we having coming from Core iQ has a lot of blanks in cells where no accounts are present. Because of this, we will need to use a variation of the AVERAGE command in Excel. At the bottom of your # Loans column, you will want to place the following:
where B12345 is the final row of your table. This will account for the blank entries within the row and will count them as zeros. Once you are done typing in this entry and before clicking out the cell, simply press CTRL+SHIFT+ENTER to finish the calculation. You should now see an average for the column with all of the blank entries taken into account. If you want to find the average without the blanks, simply remove the ‘0+’ portion of the equation.
You can then copy this equation across the relevant columns to see an average of all the different balances and totals. What you have now is a calculated total of the number of products, along with the average total balance, across each of the five major product categories. With this data, you should be able to see the overall product adoption across all product types within households at your financial institution. You can analyze where you are the strongest and where you are the weakest in order to assess marketing efforts in the future. If you kick off a new campaign and your product adoption in that category goes up the next time you run the report, then mark that as a huge success!