In order to grow the business to new heights of success, it is imperative to communicate the business objectives across the organization. This is where KPIs came into the picture. KPIs (Key Performance Indicators) are important as they help in keeping the business objectives at the forefront of decision making. And, to manage KPIs, a dashboard can be used to ensure that the business goals are kept at the top of mind. Kelly Gage explained the Key Performer Indicator Dashboard at the Transformation Summit 2020 organized by CULytics. Here are the details shared.
Red Canoe Credit Union started in 1937 and rebranded in 2007 and became a community charter. The organization is approaching 1 Billion in assets with a member base of 59,054 Members (including at least one in every state). Red Canoe Credit Union is operating from Longview, WA with an aim to serve the best to its members and associates.
The Business Intelligence Team at Red Canoe started in 2013. The team comprises of Russ Peters (Business Intelligence Development Manager), Jim Jesska (Senior Database Administrator), Dustin Leinweber (Business Intelligence Developer), and Kelly Gage (Senior Business Intelligence Analyst).
The motive was to produce a concise report on the state of the Credit Union to make the regular weekly meetings more effective and efficient. Microsoft excel was used as a reporting tool at that time. Reports were prepared by business line managers most by hand and came from disparate systems. Existing reports were manually refreshed with a live connection to the core general ledger data. It was not an easy tool to manage as it was:
- Inflexible to change, often broken, difficult to interpret
- Not enough data for those who wanted to dig deeper
- Business lines provided additional reports to their leadership and sometimes had conflicting information because of disparate systems.
- With all the discrepancies, it took time to get on the same page and to get through the meeting agenda.
Then, the business intelligence team provided a consistent, easy, and digestive set of information that can be shared by the executive team and reviewed at the meeting. This was the time when a thought was given to the Key Performance Indicator Dashboard.
“Begin With The End In Mind” -Dr. Stephen Covey, 7 Habits of Highly Effective People
The Key Performance Indicator (KPI) Dashboard
It was a – from-scratch - project. At the time, nothing existed with Credit Union Metrics all-in-one place. For Red Canoe Credit Union, the goal was to provide appropriate visualizations for the information and more frequent updates to enable faster ingestion of and reaction to the information. The main focus was on:
- More efficient data sources:
- Streamline and automate the data gathering process to improve consistency, accuracy, and efficiency
- Start at the deepest level and aggregate up- providing flexibility, leverage as a data source for other things
- Also benefits operational staff by automating their regular reporting
Three phases to deliver visualizations:
- Board/executive level metrics- this is the subject of today’s presentation
- Operational metrics
- Business line metrics
Laying a Foundation- GL Data and Views
GL data was important for the existing reporting. It was necessary to provide accurate validation and trending but there were some noticeable points to observe while working with the GL data. It made it easy to replicate most of the data points that appeared in excel reports and create fields as per the approach and 360 data warehouse. Dependency was on 5 SQL views – 3 GL views, KPI view, and Members view.
3 GL Views
Filtered to what was required for the production and portfolio measurements at each level:
- GL Category (Loan/Share portfolios)
- GL Branch (Branch portfolio)
- GL Transactions (Production and Prepayment Speed)
Aggregate major components by month: Total Loans, Total Shares, Investment Income, and Total 60+ DQ for use in calculations
Static count of new members by the branch at a monthly interval (gross new member count)
During the research, it has been found that reporting was being updated in excel report but it was not being connected with the underlined system. This was one of the data integrity problems exposed to this project. By creating the required SQL and table views, it became easy to update if corrections are necessary.
A Dashboard is Born!
5 months under the projects helped in gaining a solid database foundation that was good enough to start working towards visualization. Red Canoe Credit Union invested in Tableau and it was a perfect tool to design and implement graphics with the best ways suggestions to implement data. Leveraging SQL and Tableau views, Red Canoe Credit Union developed a dashboard of high-level metrics, using branded colors and fonts to provide a familiar look/feel to the users. It is interactive with features like tooltips and being able to summarize by selection. The user can also export the underlined data to excel for those who want to take a deeper dive.
The Chief Executive Officer, The Chief Financial Officer, and The Chief Information Officer are the primary stakeholders. In the initial dashboard, they decided to put more details. Therefore, the data models were prepared as per the requirements. After 6 months of work, the final version had 7 dashboards combining 30 worksheets. The dashboards were published to an internal server and source from extracts that refresh daily- a fast and automated process!
Keys to Success
- Ensure successful adoption by Executive Team:
- Dashboard walkthrough during a regular meeting to demonstrate interactivity and time for Q&A
- Created training materials for Tableau and this specific dashboard
- One-on-one follow up after the initial meeting
- Email subscription pushes either mobile or desktop view with a link to the interactive dashboard.
- Additional Factors:
- Core System Conversion completed 6/1/2019
- Changed data warehouse platforms 6 months later- the second iteration
- Standardization of data led to consolidated third iteration- 8 tabs from 16 sheets including operational metrics and more interactive features.
Comparing Results (MS Excel Vs KPI Dashboard)
- MS Excel (old way)
- Manual reporting in data tables using Excel
- Monthly report updates or Weekly/bi-weekly external reports Slow response time; waiting for data
- One-off requests for any specific asks or more granular view.
- Multiple sources could be inconsistent; data in multiple places and disconnected.
- KPI Dashboard
- Automated visualizations in Tableau. Initially high-level data trending
- Timely: New standard of daily reporting and monitoring drastically increased speed for decisions.
- Consistent: Multiple levels of granularity in the same place from the same source
- Monitor branch, region, and institution performance. Can focus in the right areas.
This project helped Red Canoe Credit Union to be data-centric and data-driven culture and operate more effectively and provide better experiences to members and staff. So, look into the company requirements and expectations of the members and adopt the right method to deliver the best of the offered services.