This talk of Program Management software over the last few weeks has gotten me thinking about some of the issues connected to acquiring and using applications to manage data about client program enrollments. I just glanced through the ITRC consulting list and saw over 300 database projects since 1996– a bunch of them involving some form of enrollment tracking. So, taking advantage of the space that techno-babe Deborah has given me, I'll review some recent database events and why they have my attention.
I’ve been contemplating these projects lately while trying figure out what makes them unique and what common elements they share. I’ve worked on three recently and I’ll refer to the projects and organizations as P, B, & J in honor of the first names of my contacts at each agency and to allow a wider range of interpretation as to who they might be. I also am fond of PB&J sandwiches and the traits of homogeneity and individuality that these yummy and versatile snacks share with program enrollment tracking.
P is a family service agency that provides about 8,000 hours of counseling to around 500 families per year. B does job training and placement services for several hundred un or under employed individuals annually. J provides a range of youth services from pre-school through college scholarships to a gazillion public school students each year. Each group started with a similarly structured MS Access database as the core tracking system, but has modified the application to meet the peculiarities of their tastes and needs.
Common structures include a single table of names and addresses. Every sentient carbon based life form is recorded in the “Individuals” table. This allows the same person the ability to link back to the agency as staff, client, community leader, parent, parole officer, or any combination thereof. This has proven to be remarkably stable and useful over the years. For clients completing intakes there is a demographics table recording the big three of sex, age, and race. Interestingly enough, beyond this point there is a plethora of additional demographics that would crash the internet if listed here. A few are community area of residence, all of the various marital, veteran, job, and other statuses, along with things that only a funder could love. There’s a lot of customization when it comes to basic demographics.
Next, every system has program enrollments for those people who become clients. Counseling program X or Y, training program A or B, recreation program or English tutoring – everybody enrolls in a program on some date and will eventually have an exit date and completion status. P and J had business rules that assigned a case worker to each enrollment, so their systems link a staff person at enrollment. B’s culture promotes group ownership of the client’s enrollment, so doesn’t create explicit caseworker assignments (contacts and services are tracked by workers in all of the systems). This simple structure of program enrollments allows them to track the number of enrollments and exits per year, along with links back to demographics. Because each individual can have multiple enrollments it’s possible to answer continuum of care questions and other problems of how long and in what manner someone is connected to the agency.
Program completion alone is a poor indicator of impact, so each agency also tracks a set of multiple outcomes. For P, there is the question of progress toward counseling goal; for B the biggest outcome is job retention, and for J it is grade improvements and progress against the Search Institute and other tests. Interestingly enough, binary outcomes (yes or no; was there adequate progress? Did the person reach 90 day job retention? Did the youth graduate from 8th grade?) While data collection can be a challenge, it’s surprisingly easy to set target dates and count results.
B’s group also tracks job placements, following one or more individual’s placements with a range of employers. Pay rates, length of stay, reason for leaving, medical benefits and other factors required extra tables to track employers and placements. In the five or six years that they’ve been using the database some early placements are now senior staff at several employers and can help in placing future candidates. It’s these kinds of special needs that I find so very facinating. Every person has a program enrollment, but some groups have additional needs that, if implemented well, change the whole dynamic. B knew that the database would be important in tracking clients, but today it is an important contact management application with employers. Job developers use the system to identify prospective employers and make the best pitch for placement based on prior history – all of which is available within the span of a few clicks.
The change that brought B and me back together the other day was a staff demand for a change in the definition of client. Until recently staff had manually vetted clients and only after they actually showed up for the first day of training where they entered into the database. Now they wanted to track the application process from the first inquiry call. Part of the reason was developmental – they just weren’t ready to do this six years ago. They had treated the database as a special environment and the hoi poloi not yet enrolled were just not worthy a few key strokes. However, a few years of getting used to the system and it has lost some of its luster as “special.” Like Outlook, it was always open on every desktop and staff used it regularly throughout the day. The idea of a set of individual systems to recruit and screen candidates became strange. The other reason was more political. Their ability to manage information made them better at training and placement and demand for enrollment was building. The work load was getting heavier and the old way wasn’t cutting it. The merits of tracking inquiries made a lot of sense, and a side benefit would be an easier way to document demand in order show funders just how popular they were. The fix was to create a new “program” in the existing system called “inquiry/intake”– no changes to the database code and only minor training for staff.
The transformation with J definition of client was even more subtle. They see the world of youth as “members” or “participants.” Members fill out a membership form and participants attend events. Members actually enroll in programs and receive one-on-one services and have attendance taken while involved in long term activities. Participants show up for special field trips and single day activities. But while executives of the agency saw membership as exclusive of being a participant, line staff didn’t. The ITRC, anticipating the fluid nature of members and participants, built a system that allowed one person to be both and we sat back while J instituted a business rule that said that staff only had to record special event attendance by participants (non members).
A year and a half goes by and we’re having a database meeting for Master Youth Workers. One asks “I’d like to acknowledge the hard work that the Teen Board did on organizing the Halloween party.” Management wanted to record individual client contacts with each Board member – a five to ten minute job. I was asked my opinion and I pounced “Well, we could add the members to the event and acknowledge their contributions in the notes field – two minutes with copy and paste.” Staff loved it, but management was concerned about breaking the member/participant barrier. Then I explained the FY 06 counts showed that the sum of unique participants and unique members was higher than the total unduplicated count – because some participants had become members during the year and we had configured the counts to catch that. The solution to a new business problem and proof that the change wouldn’t produce bad numbers was all it took to change the rules and give staff better tools to understand who they are serving. I love those moments when staff suddenly understands something that’s been waiting for them all along.
P’s “ah ha” moment was less epiphany and more quantitative conundrum. P needed to count the number of ancillary people served. The primary client was fine in the existing data structure, but they also counted family members of the primary client. There’s a module to link multiple individuals together into households, but staff didn’t like it – too much work. The need was purely funder related – it played no role in delivering services – and was only needed once per year in that single funder report. There had been a tradition of doing this once at the end of the fiscal year. We tried a lot of different approaches and finally decided on a family demographics table hanging off of the client record. Not an elegant or even fully normalized approach, but something that is easily understood within the culture of the agency. Some of the fastidious caseworkers keep up with the family profile (ages and races) as they are assigned new clients. Others do pretty much what they’ve always done – they wait until the last week of the year and fill in the details. Logically it makes no sense, but from a human and operations standpoint it works very well and they have better data earlier than ever before.
Intellectual ruminant that I am this missive has helped me focus on the ability of a few large data structures to support an amazing variety of work being done by client-serving npos. Yet no matter how elegant the solution may be there’s a period of acclimation before people are ready to use everything in a system or the need to change things to fit the way that they think. That’s why I say that good database development is like being in love – if you’re doing it right you’re never finished. And as I’ve learned with my children and their friends, there’s an infinite number of ways to make a sandwich out of PB & J.
Tim Mills-Groninger
IT Resource Center, Chicago
