A large, international nonprofit with a mission to improve quality of life through job and education support needed to standardize data collection and reporting. The nonprofit provides community-based programs and employment placement services and has over 160 member organizations across the nation. Each member has the same general business model but makes minor tweaks to better suit its local community.
The headquarters at this organization identified an initiative to collect data from each of its member organizations in a more streamlined and scalable manner.
When we began our engagement, there was no standardized reporting across these independent member organizations. Existing reporting was manually produced and released after the extensive data cleaning processes, delaying valuable insights. Team members were unable to effectively share insights across the member network, and data collection, submission, and validation was tedious and manual.
Keyrus first performed a discovery exercise with the client that included business, informational, and architectural elements. This allowed us to create a project plan with a phased approach.
Member Data Ingestion Solution
Members needed to be able to choose from automatic or manual data submission options. The automatic method involved building an API to which members could automatically send their data programmatically.
To build this solution, Keyrus performed the following tasks:
Configured and uploaded a web server
Developed and configured file processing routines including data validation checks and alerting features for failures
Provided ability to save the data and developed documentation for end users and technical resources
Developed and implemented security procedures to protect data from authorized access
Developed and tested the API ingestion capability
Developed standardized connectors and connector frameworks to/from commonly used member systems to accelerate and de-risk adoption
Conducted a trial with pilot members
Both the automatic and manual methods required immediate feedback on whether the data was accepted or rejected. If it was rejected, the system also provided the reason. This meant that Keyrus needed to develop submission templates in various formats (XML, CSV, etc.) with relevant documentation for six different datasets that the members were eligible or required to submit.
Upon successful data validation, the data is properly structured and inserted into a data warehouse to automatically feed into downstream reporting processes.
Member Data Transformation & Management
Once a member’s data submission is successfully uploaded, it’s combined with other members’ submissions into a consolidated format while still maintaining both the standard and custom attributes of that member. This allows the members to view the data from two perspectives - both the standardized organization view and the custom view of the specific member.
Throughout this process, we monitor data quality issues automatically using quilliup to ensure that the data we expose passes data quality checks. In addition, Keyrus deployed a master data management component to harmonize nomenclature and create a common ground for analysis across members. For example, one member might use the terminology “t-shirt” while another used “short sleeve shirt.” The MDM component categorizes these two options as “textile,” thus enabling grouping and comparisons.
Member Reporting & Analytics Solution
The client’s business goals were to track growth for each member and compare the members to one another on a variety of metrics spanning multiple business units. They requested to see how the members’ performance stacks up for various categories and departments over time so that each member could identify areas of improvement and act on them. This is a challenge because many members track their data differently, meaning data is collected at different levels of granularity and with different nomenclature.
After building the relevant DWH and MDM solutions, Keyrus created five Tableau dashboards for thousands of end users. A common theme through all of the dashboards is to present national and regional benchmarks so that members can spot actionable gaps in performance.
The first dashboard gives an overview of a single member’s sales and donations for high level executives.
The member could then move to an analysis dashboard where they compare all of its locations to one another. They are also able to select a subset of their locations to compare in this dashboard, which would be useful for a regional manager.
The third dashboard drills down to a single location’s data. This gives a store manager a deep dive into how that location is doing in all aspects of business.
The final two dashboards allow the member to compare itself to other members and locations. There is a characteristics dashboard where the member selects the characteristics they want to compare against. This is valuable in cases where they only want to compare against similar members (for example in size, geography, and local demographic). It’s also valuable in cases where the member is considering modifying their operating model and want to compare themselves to members who already operate differently. Using this dashboard as a filter, the member then moves to a comparative dashboard that does a side by side comparison of the selected members.
Lastly, Keyrus provided help pages to explain each dashboard as well as a data dictionary to clarify any calculations that are on these dashboards.
Change Management
We began the change management process by building a change management plan with the client. We started with a controlled roll-out plan to a pilot audience before continuing to a wider rollout, including communication, training, and support.
Communication: A cascading communication line from management to the members of the initiative, including user group meetings and feedback surveys, to educate members on the initiative’s purpose and benefits and to promote buy-in.
Training: Created role-specific walkthroughs to utilize each Tableau dashboard for common analysis scenarios. Utilized these in Train-the-Trainer content to enable the client to scale their rollout. Train-the-Trainer sessions were recorded and documented.
Support: We have created a support methodology based on “office hours” each week, a common Sharepoint feedback portal, specific shared email lists, and even commentary on the actual Tableau dashboards. We also have a proactive support function which monitors actual usage of the dashboards and alerts us to members who are or are not using the system.
A foundation for data-driven growth
As a result of the data and analytics initiative, the organization has a web-friendly user interface for data submission. They can also rely on a new data warehouse as a single source of truth for reporting, including a full data quality alerting framework. Member organizations can also build comparison analytics dashboards to improve their performance.
Prior to the engagement, the Annual Statistical Report (ASR) would not be released to members until July after intensive manual evaluation of missing data, data ranges, and then running the statistical analysis and generating the report.
The ASR can now be generated the day after their data collection is complete, and with the automation of reporting and data integrity, the data collection period is much shorter as well. The ASR is ready to be released in February instead of July, greatly shortening the delivery window.
Overall, members are now able to make better business decisions by comparing against similar member organizations.