Hello! We are Laura Beals (program evaluator) and Noah Schectman (database administrator) from Jewish Family and Children’s Service (JF&CS), located in Boston, MA. At JF&CS we use a cloud-based case management system to facilitate data collection about our clients and services. We are part of the internal evaluation department at JF&CS (i.e., he is not part of IT) and so we work closely together to ensure that we are collecting data in a way that allows us to complete our analyses in the most efficient manner possible.
Lessons Learned: Though our system has built-in reporting tools, we often download data for more complex analysis in another tool, such as Access or SPSS. In addition, though the data collection tools are designed as easy-to-complete forms in the system, we do have to bulk upload data regularly.
Many case management/performance management systems allow for back-end customization of the data collection tools—you may have the ability to do so in-house (as we do) or you may have to work with a third-party developer. Regardless, as an evaluator, if you are working with an online performance management system, you should ask yourself: “What does the data need to look like when it is downloaded? When it is uploaded?” In general, we first think about how the data will be used, then design the data architecture to match.
Hot Tips: When designing new data collection tools in our database, we ask several key questions about how the data should be formatted on the back-end, including:
– What are the unique identifiers for each case that will need to be downloaded with or uploaded to the database?
– Should the data be arranged so that a case is on a row or each assessment is on a row?
– For each variable, are the variable labels or the numerical values used?
– How are multiple response variables formatted? As dummy variables?
– If names are used, how will they be formatted? What about addresses? What about dates?
Even when we think we have it figured out, we always enter fake assessments for fake clients in the system, through the online form and through a bulk upload, and then download the data. We then review the resulting import/download and triple-check that the data is formatted in the manner we expect. We find doing the work to prepare the system ahead of time saves us a lot of data formatting and manipulation down the road!
Do you have questions, concerns, kudos, or content to extend this aea365 contribution? Please add them in the comments section for this post on the aea365 webpage so that we may enrich our community of practice. Would you like to submit an aea365 Tip? Please send a note of interest to aea365@eval.org . aea365 is sponsored by the American Evaluation Association and provides a Tip-a-Day by and for evaluators.
Thank you Aaron, Jess, and Bethany for your comments! We will be looking into “Option 5,” as you illustrated Aaron. If you, or anyone else in the community, has other suggestions for data formatting, please do add a comment! My experience is limited to the systems with which I have worked, so it would be great to have other perspectives of people using different systems.
And yes, Jess, evaluators do often have to work with the databases that are set up by others…we all know that pain! But, when we have a chance to have input at the beginning, it can really make things more efficient in the long-term! Thanks for the input!
A 5th option that we often use in our data management systems is to have an intermediary lists table. In this case there might be a list of flavors either in an icecream_flavors table or in a more generic “lists” table looking something like this:
list_id flavor
1 Chocolate
2 Vanilla
3 Strawberry
4 Fudge Swirl
The intermediary table would then map people to flavors like this:
table: people2icecream
person_id list_id
person_1 1
person_1 2
person_2 2
person_3 2
person_3 3
While adding a layer of complexity, this allows updating the list of available flavors without making changes to the back-end database. It also allows building report logic that isn’t dependent on a specific database schema (as flavors are added, the reports can adapt automatically).
This option may or may not be available depending on the cloud-based platform but might be something to consider. Thanks for the article!
Laura and Noah are hitting on good points for all data management, not just performance data.
Thinking about how the data will be used and testing dummy data greatly reduce the burden of data munging for analysis. These steps also can ensure that the planned analysis to answer the research questions can be completed.
We, as evaluators, often have to use datasets already created that we cannot control. Ensuring that our own datasets are well organized can make analyzing them a joy.
Great reminder for us all!
Very helpful picture! Thank you!!