CUNA Technology Council
Best Practice Award
Purdue Employees Federal Credit Union
Bill Arnold, VP of Information Technology
Ryan Erickson, Database Administrator
Purdue Employees Federal Credit Union serves 56,000 members with 8 branches in the
Lafayette and West Lafayette area. They have roughly 500-million in assets, and currently
have 160 employees.
PEFCU’s new corporate goal is to be their Member’s Financial Partner for Life. They plan
to do so by acquiring new members, deepening relationships with existing members, and
Background of Problem
With a brand new corporate goal, PEFCU was ready to succeed by dedicating all resources to
the problem. Management Team determined that they must dedicate additional resources to
their sales staff since they have direct contact with members, but they quickly realized that
without adequate reports, they would have difficulty measuring their efforts. The current
Sales Reporting Model was extremely inadequate, and was not satisfying the needs of the
organization. Management Team decided that changes must be made if they were going to
better service their members, and a measuring tool was a key component.
In summary, the current reporting model pulled data from both the Sales Database and
Referrals Database, and reports were updated weekly by a Data Analyst. Reports were then
disbursed as text files to Sales Managers, who analyzed branch production and disbursed
details to Sales Staff. Staff would analyze their individual production and report erroneous
details to their manager, and these corrections would eventually be made by the Data
The biggest problem with this model was the tedious update process; queries were manually
run from each data source, and tables were manually updated. Then after a few manual
query revisions in Microsoft Access, Rich Text Reports were created and exported, and links
to the new reports were emailed to Sales Managers. The Data Analyst dedicated about two
hours every week to update the reports.
Another problem was the inconvenience it had on sales staff. Since the reports were updated
on a weekly basis, it was difficult for staff to monitor their achievements. Sales Staff must
know at all times how their production measured against their monthly goals, but since
reports were updated weekly, staff was expected to manually track their production. And at a
higher level, Sales Managers were expected to know at all times how their branch was
performing. But since sales staff was manually tracking their weekly production, managers
wouldn’t be informed of poor performance until the week was over.
But the biggest problem with the existing model was the impact it had on member service.
Sales staff was expected to contact members for cross-sale opportunities, using a list of
2|P a g e
referrals generated by the model. But since the report was updated weekly, staff was
expected to manually filter through the list, and would contact members at their own
discretion. Furthermore, reports didn’t effectively measure a salesman’s success rate at cross
sales, and Management Team didn’t have high-level reports that rolled this data up to the
branch level. How could they tell if one branch better satisfied member needs? And how
could they determine if our services were satisfying? But most importantly, it was
impossible to determine if an individual member was offered the proper product mix that
would satisfy their individual needs.
The bottom line: if PEFCU wanted to achieve their goal of being their Member’s Financial
Partner for Life, reports must be created that measure their success at member service.
The Making of a New Solution
Management Team recruited the help of their Database Administrator to develop a new
reporting model that would benefit the organization, and the team determined a set of
business requirements. From an infrastructure standpoint, the new model must be dynamic,
automated, and efficient. Data must also be updated on a daily basis, which would eliminate
the burden of manual tracking by staff. But at the same time, reports must be delivered in a
user-friendly front end environment that satisfies the needs of Sales Staff (at a detailed level)
and the Macro needs of Management Team.
Issues with the Core Processor also came into account; since suffixes were often reused on
membership accounts, a full list of historical sales is not available from the Core Database.
Thus snapshots of sales must be taken to capture this activity for trending purposes. Luckily
this issue was addressed in the existing sales model; data snapshots were saved since 2005,
so historical snapshots were not an issue.
To satisfy the infrastructure requirements, a set of flat files is automatically generated every
night from the Core Database that captures all data required for the snapshot process. These
files are imported into a SQL database, and new production is appended to the Sales
Snapshot table every night. All data pertaining to the origination of the suffix is captured:
origination branch, staff initials, original amount, etc. Other data is also linked from
PEFCU’s CRM Database, where workflows are filled out as staff is talking to the member.
The database is dynamic because it uses a set of manually-updated tables to identify specific
products, branches and employees that should be included in the reports. This guarantees
Referential Integrity by ensuring that only relevant production data is reported. Additional
branches, products, and employees can be added to the reporting by adding records to these
tables, which can easily be done by the AVP of Sales.
But the most difficult task was delivering adequate reports to various staff members. Sales
staff demands detailed reports that allow them to view individually sold items. But these
details must also be rolled up to summary levels for Management; Branch Management
needs branch-level and employee-level reporting, and Management Team needs
organization-level trending reports to make decisions. Cognos was selected as the reporting
medium because it is able to satisfy all these requirements with its drill-down capabilities.
3|P a g e
Interactive web-based canned reports were created and distributed via the corporate portal
(see Figure A), which satisfies the day-to-day needs of the company. Add to that Cognos’
ability for ad-hoc reporting, and management is now able to view production based on
New Reports are a Success!
Since the new reporting is updated on a daily basis, sales staff is able to shift their resources
to better serve the member. First, a Pending Referral Report shows every referral that has yet
to sell; this provides staff with a full list of potential cross-sales, and even includes the
member’s contact information. This allows staff to follow up with members who have
expressed interest in specific products. Staff is able to provide much better service for
members, and the number of attempted cross-sales is up almost 300% from 2007. Also
available is a Detailed Sales Report (see Fig. B) that allows staff to view every sale they have
made in a given month. They can see exactly how close they are to achieving their sales
Branch Managers utilize employee-level reporting to analyze productivity. Sales Sumary
Reports (see Fig. C) measure employee success by showing their contribution to specific
corporate measures (% with Visa, % with Checking, Relationships per Member, etc), and
also shows monthly-trended data which assists in goal-setting. Also available is a Daily
Summary Report which helps managers properly staff their branch during busy times of the
month. For referral reporting, a Summary report (see Fig. D) shows managers how
successful their staff cross-sells products; staff with a low Closing Ratio might receive tips
and tricks from managers or other employees.
Management Team then uses a high-level report to compare branch production (see Fig. E).
This report is similar to the employee-level report, in that it measures success by showing
each branch’s contribution to corporate measures. This report is crucial to the goal-setting
process, and also assists in understanding which branch is most successful in satisfying our
And with that, PEFCU has a fully functional Sales Management Model. Not only have
internal processes been improved, but those saved resources can now be focused on member
service, and efforts can be measured. But that’s not to say this project is over…
More Enhancements to Come
Although the original business requirements have been fulfilled, this project still has a large
wish-list of desired items. The Sales Reporting Team is currently developing Incentive
Templates within Cognos, which would allow sales staff to see future payout amounts based
on their monthly production. Also, portal modules will be built that will allow managers to
control what gets reported; by adding/removing Employee Names and Product Codes, the
team can tweak incentive structures on-the-fly.
4|P a g e
Figure A: All reports are
launched via HTML links
posted on the corporate portal.
Figure B: Detailed Sales
Report shows all sales that a
specific employee has done in
one month. If goals are entered
for this employee, a % OF
GOAL is calculated.
5|P a g e
Figure C: Sales Summary
Report shows total production
for all Sales Staff. Compares
actual production vs.
Figure D: Referral Summary
Report shows the Closing Ratio
for each employee. Branch
Management can determine
how successful their staff is at
6|P a g e
Figure E: Branch Summary
Report compares production
among all branches. Also
compares total production vs.
7|P a g e