Needs
The State of North Carolina’s Pandemic Recovery Office (NCPRO) worked with U.S. Digital Response to design an automated and user-friendly KPI data collection tool to gather performance insights essential for accountability, decision-making, and required federal reporting.
NCPRO was established in May 2020 under the North Carolina Office of State Budget and Management to coordinate the state’s fiscal response to COVID-19. NCPRO oversees the distribution of $11 billion in federal pandemic aid funds. This includes $5.4 billion in State and Local Fiscal Recovery Funds, authorized by the American Rescue Plan Act (ARPA) for over 200 projects across North Carolina, ranging from smart school buses to scholarships, COVID-19 tests, and more. NCPRO and its leadership were committed to using evidence to measure performance on these projects. Due to changing state requirements and pressures from external stakeholders, including the state auditor, creating an efficient and effective KPI data collection tool was critical and time-sensitive.
The Challenge
With only 3.5 years to allocate funding and a State Auditor eager to monitor performance on pandemic recovery projects, NCPRO needed an intuitive system to collect performance data from agencies, ensure compliance, and help state leaders analyze the impact of spending. To get started, NCPRO had already invested in training for all agencies implementing projects funded by pandemic recovery funds to collect evidence on program outcomes and monitor KPIs to measure performance. Time, budget, and staff constraints ruled out investing in an expensive custom-built system through an outside vendor.
Initially, the team built a tool in Excel. Andy MacCracken, Reporting and Strategic Initiatives Manager of NCPRO explained, "We tried reporting just using Excel. It became very unwieldy very quickly. It became all the more challenging because there's no one-size-fits-all metric for many projects spanning every facet of North Carolina life." Agencies had already developed multiple KPIs per project to track and report to NCPRO. Problems emerged with the massive Excel spreadsheet. With over 130 columns, it was challenging for agency partners to accurately submit data on a quarterly reporting cadence. Hundreds of unused cells wasted space and created confusion.
Then, NCPRO’s data team tried to address the challenge with a different tool–they built a survey tool in SmartSheet Gov, which the state had previously acquired. This experiment demonstrated significant limitations, which NCPRO knew would create roadblocks for users to adopt the program. For example, the survey tool provided no way to save incremental work or validate inputs. Managing growing piles of incomplete responses proved nearly impossible.
Why did NCPRO reach out to USDR?
Andy reached out to U.S. Digital Response (USDR) to seek additional capacity and expertise, and Natalie Garrett, Program Analyst, led the project on NCPRO’s side. "The collaboration process was great. Pari Sabety from USDR kept everybody on track, and the product exceeded our expectations," Natalie said. USDR quickly located a volunteer, Shawn Selleck, who knew SmartSheet's commercial product well and that it had features that would support NCPRO’s needs. Moreover, after years of experience leading large-scale technology implementations for the public and private sectors, Shawn deeply understood the need for easy-to-use interfaces to boost user acceptance.
Our approach
The Solution
After examining the unwieldy 130-column Excel spreadsheet, Shawn concluded the overarching system design itself needed to be revised for data scale and flexibility over the long term. So, he broke this down into separate interlinked sheets in SmartSheet:
- A "project profile" sheet with 30 non-KPI fields covering descriptive details like project name, grant recipient, and budget expenditure timing.
- A streamlined "KPI list" sheet with one row per indicator enabling unlimited metrics tied to each project via ID numbers. This replaced hundreds of empty, unused columns when only a few KPIs were collected.
The new database structure is far more versatile, establishing relationships between multiple levels of data. The relational database can expand infinitely to store performance indicators across projects. It enables easier future expansion by adding data categories or new analysis features. It also includes robust error-catching for improved reliability.
To boost user acceptance across over 200 projects and multiple state agencies, the USDR team set up automation in SmartSheet's commercial platform to push KPI collection requests out to grant recipients by email based on scheduled reporting timelines. The embedded forms also allow simple submission of metric values through clutter-free interfaces. "The brainstorming with USDR helped us simplify and design a tool as user-friendly as possible on the grant partner side,” Yongjun Lei, Database Manager of NCPRO, said.
Together, USDR and NCPRO were able to develop a solution using the state’s existing toolset by using:
- Flexible and responsive collaboration: USDR and NCPRO worked within shifting needs, providing rapid prototypes and adjustments through an agile approach.
- A focus on user-centric design: Drawing on experience across many jurisdictions, USDR advised simplifying and tailoring the tool for non-technical state agency users.
- Access to USDR’s volunteer base with expert technical capabilities: USDR volunteers provided skilled guidance on optimizing SmartSheet's commercial platform to enable automated workflows that seamlessly collect, validate, and organize performance data.
This approach focused on using existing tools and rethinking the project's architecture, which saved NCPRO significant time and money that would have come with procuring a new system.
"Ringing endorsement. I recommend USDR to any organization seeking to solve complex problems. We gained everything and lost nothing. I understand that high-quality free assistance seems almost too good to be true, but I'd advise other states not to hesitate. I can confidently say this collaboration was more efficient and valuable to our organization than some other collaborations with costly external contractors, not to mention the fun we had working with the USDR team. The process overall was excellent."
Natalie Garrett
Program Analyst
North Carolina Pandemic Recovery Office
Impact
The collaboration between NCPRO and USDR delivered tremendous impact in just three months–it was up and running well before the deadline the state set to gather and publicize standardized performance data. The result was a flexible, scalable platform positioned to evolve alongside the client's data needs.
Rather than a single fixed tool, USDR delivered a customizable foundation to manage performance metrics both presently and into the future. USDR provided this guidance and customized solution at no cost, allowing NCPRO to allocate constrained resources toward other pandemic recovery priorities.
After hands-on guidance and walking through the system step-by-step, Shawn’s technical counterpart at NCPRO, Yongjun, presented the solution independently in a meeting for external leadership and critical agency stakeholders. Equipped to enhance the platform as needs evolve, NCPRO gained not just a tool but long-term capacity through this collaboration.
The USDR team also helped NCPRO staff develop and refine their skills and knowledge about Smartsheet’s features and functions, enabling NCPRO to improve the reporting and dashboard development process. This knowledge transfer is also expected to substantially improve the efficiency of other NCPRO functions and processes moving forward. NCPRO said they were also grateful for the quality and efficiency of the collaboration and information exchange.
What’s Next?
With simplified data collection now underway, NCPRO aims to eventually add user dashboards, allowing state agencies to self-monitor their performance indicator submissions. Though realizing this future vision extends beyond USDR’s initial scope, NCPRO gained essential capabilities and knowledge to drive their enhancements.
"Ringing endorsement. I recommend USDR to any organization seeking to solve complex problems. We gained everything and lost nothing. I understand that high-quality free assistance seems almost too good to be true, but I'd advise other states not to hesitate. I can confidently say this collaboration was more efficient and valuable to our organization than some other collaborations with costly external contractors, not to mention the fun we had working with the USDR team. The process overall was excellent.” said Natalie Garrett from NCPRO.
This unique no-cost partnership delivered outsized value to the North Carolina Pandemic Recovery Office by providing them with an optimized KPI reporting system that neither time nor internal capacity allowed them to build independently.
