Beyond Spreadsheets: Building an ARC-PA reporting system that survives staff turnover

0:00Welcome, everyone, on a Friday afternoon. So, kind of the genesis of this talk really is about a problem that I’ve seen time and time again in my consulting practice, when we begin to look at all the data for an SSR. Oftentimes, if there is disruption in, say, turnover of faculty or staff, sometimes the data goes with it or is lost. So I want to talk about sustainability of assessment, so you’re able to gather the same data every single year and be able to answer the six questions, or sorry, seven questions, in the Sixth Edition standards now.

0:42So, a couple things about what this is and what it isn’t. I want to talk about really why manual reporting breaks down, and there’s a number of reasons for it. This is a framework you can use, whatever tools you have. It’s not just a product demonstration, although toward the end I’m going to show you kind of what Edulytic Solutions can do for you if you decide you want to look at it.

1:11It’s designed to be for those that are somewhat responsible for compliance or involved with compliance. And again, it’s not saying that your spreadsheets are wrong or anything like that. It’s just a matter of how you use them and how you maintain them.

1:27So this is kind of the run of the show as we go forward. So let me go forward.

1:34So the question is, where does your data actually live? Oftentimes data lives in seven places, but you need it in one. To be able to pull it and meaningfully analyze the data at the right time, you need it in one place. And oftentimes it is spread out over multiple domains. So, inventory. Think about the number of sources that feed one annual report for you as you aggregate your data and start answering the questions.

2:08This gives you an example of what might typically be seen: anywhere between 5 to 7 or more sources of data, depending upon the institution. So we have a live poll. If you can give us an answer of how many distinct sources feed your program’s annual report, to give a sense of where you get your data from. And Jacqueline’s going to launch the poll.

2:49You got the poll, Jacqueline? There we go. So, give you a little time just to decide. Awesome. I think we’re ready to tabulate this.

3:31So you can see it spread anywhere between three or fewer and more than ten. So again, I think the sweet spot tends to be about seven. That’s the number of places you get your data from. Thank you for participating. So again, the inventory is not the problem, because you don’t control the vendors. The problem is what happens to it on the way to a report. And what I mean by a report is having the data in one place so you can organize it.

4:07And again, I’m going to just go ahead and say this might be the C1.01 and C1.02 templates that ARC-PA created. Ultimately you aggregate the data into those reports. So you may look at it like you have a larger spreadsheet with more detail, then you have overall average scores, or all the data points, and then that’s transferred into that report, which can then be reviewed on a regular basis for compliance.

4:37And sometimes the journey of the data is all manual. Again, that’s just the way it is. But one of the things you want to do is you want to create an accurate cycle that, you know, that does not create a defensible five-year trend. So that is the problem: you want to be able to have five years of data going forward. And that’s really along the lines of what I think is looked for with an SSR.

5:13So let’s talk about, excuse me, some failure modes. Excuse me a second. So why does manual reporting fail? And I’ve lived this myself. One is drift. And we’ll go over each of these in a little more detail. Dependency: who owns the workbook and the logic. Usually it lives... sometimes there’s one person. The assembly tax: that’s the effort. Irreproducibility: you can’t get back to the original numbers if the numbers are lost.

5:58And none is an effort problem. None is a competence problem. So let’s take a look at that. So, drift. If data changes over time, categories get renamed. Data is lost, drops off. It’s lost forever. So you can’t reproduce it if you don’t have it. And oftentimes then people are looking back in their archives to the best of their ability. What a reviewer will see is a trend line where the movement, the movement is measurement, not performance.

6:30So again, we want to look at performance over time, but you measure the differences. You want to measure whether or not there’s a downward trend, upward trend or an abrupt difference.

6:44Three years of data that can’t be compared to each other because you don’t have the same variables. And then again, nobody decides to change methodology. It changes because the person changes. I can’t tell you how many times I have heard this, where the person who is in charge, the data manager, is gone, and now the data is lost to the rest of the faculty. So dependency is really about one person being too much enmeshed in the process, the data memory, as it would be.

7:21So the single point of failure is that discontinuity at the point of transition. So if that person leaves, then you’ve lost those longitudinal data points that you need.

7:37The assembly tax. I think this is a really significant one to think about. Think about in your program who’s responsible for locating, formatting and combining, who’s responsible for all the tabulation of your data, taking raw data and translating it. And then what happens with the interpretation? Oftentimes there’s too much shuffling of data to try to make sense of it. You really need to be looking and interpreting and analyzing the data during these meetings, not trying to figure it out.

8:11So again, the number of hours really needs to be understood, because again, that’s workload. So what a reviewer might see is a ratio that’s upside down.

8:26You don’t want to describe the data. You want to analyze it. And I’m sure you’ve heard that before in our kinds of things. So the assembly tax is a big deal. And again, it speaks to a workload issue within programs. So what if it’s not reproducible? If the number was derived six months later or has been edited... One of the things I want to say to you, which is really important, is that there needs to be ongoing, continuous collection and analysis of data.

9:06It can’t be a retrospective gathering of data, which often happens because people don’t have enough resources to be able to do that. So to be able to do that means that you have the ability to look at micro trends, which is really important, in between the times when you usually look at data. So, kind of looking at it from a reviewer’s viewpoint: defensible-sounding numbers that cannot be defended.

9:35The difference between data and evidence. And then a number you cannot reproduce is an anecdote with a decimal point. So, latency. Latency means that the cohort has moved on. This is what I’m talking about: that in-between time frame when you’re not able to constantly analyze the data, and in particular when students are struggling. Think about this: if you’re not gathering data continuously, you’re not capturing student performance adequately and frequently enough.

10:11So improvement arrives really one cohort too late, because you’re looking back in time. And again, going back, you know, 20, 30 years, it was common to look at data once per year. And the quote I’m going to say is: if you learn about the PANCE problem from the PANCE report because you have a low pass rate, you should have already been doing that. So the PANCE report is simply a reproduction of what you’ve already analyzed.

10:40But unfortunately, that often happens. To be able to constantly do this, again, easier said than done. So, looking at this as a spreadsheet, as a document: what the program needs is a system. And what I want to talk about is a system of analysis, a system of documentation, to try to make sense of this.

11:05So let’s talk about what ARC-PA is asking for. And obviously we’re looking at the Sixth Edition assessment expectations. They’re not asking for a report, although they do ask for an SSR. But what they want is a process. So your report is a deliverable. It’s rebuilt. But when it’s rebuilt from source files, it’s too late. Oftentimes what’s happening is it’s being reconstructed from memory. But rather, the process as deliverable means that you are collecting and tabulating data on a defined cadence.

11:48Each submission extends the same longitudinal evidence record. So in between times, you follow your timeline the way that you had put it together. And that is, you gather data, whether it be course evals, instructor evals, outcome data, on a regular basis throughout the academic year as well. And then the methodology is written, repeatable. So the one thing you can do, which can actually be done, is if you have this process down, you can actually analyze the questions in C1.01 and C1.02 on an annual basis, and write the narratives on an annual basis.

12:29So you look at C1.01, there’s a number of data points here. You can see that it runs across many different domains. And it’s not easy to put everything into one source, because many times there’s different types of data. But the beauty of the spreadsheets that ARC-PA provides is that you can put all of these within its home base, within that spreadsheet. But in the meantime, how do you put it together?

13:00And I’ve got some ideas for you. The Sixth Edition process really was the first time that I’ve seen it where they’ve reinforced that it’s the continuous collection of data, consistency, to be able to use critical analysis. And so if they come to your program and they see right away that you’re recollecting data and reconstructing data, they’re going to know that based upon the way that you even talk about it.

13:31So this is all the data in C1.01. And some of this data was not collected as much before, but now needs to be collected on an annual basis. And then C1.02, also some new information. Remember to pay attention to the required questions on sufficiency of personnel by type of task. And they have specific questions for both, you know, students to evaluate faculty and staff, and then vice versa.

14:05And then look at FTEs and attrition rates, and then the workload calculations, which, to me, is like talking oftentimes to deans about workload calculations is like you’re talking over their heads, because it’s a new concept. It really hasn’t trickled into, I think, graduate medical education adequately, and that is being able to define what the workload calculation is for each individual.

14:36So I’m going to call this a source of truth file. So if you’re able to put all the data together into one place, that’s your source of truth file. So if you’re able to longitudinally put it together... And again, you can use the C1.01 and C1.02 data points as the owner. That’s where you place it. But outcome data can be put together all in one place, though.

14:59And that allows you then to analyze outcome data. Say, for example, and I know that stats are not required in the Sixth Edition standards, but that’s how you could use that for statistical or regression purposes, or even just tabulation or stratification purposes. But again, you can see the assessment data family that you’re looking at. And then the source of truth really is where it’s coming from, and then the owner of each.

15:27So the cadence really needs to be, you know, some of these per cohort, some of these per term, some annual, some each event. And the purpose for assessment really depends, but it now is more aligned with the questions and mission. The effectiveness, you know, graduate effectiveness. So again, that kind of speaks to how you can put that together. So let’s say you have the source of truth file.

15:58I’m going to say the C1.01 and C1.02 templates. And it allows you to put three additional spreadsheets in each one of those. So if you want to put down, you know, the national average versus program average for, say, your PACKRAT, PANCE, you can do that. But the collected data is placed into those areas. So you have the cadence, the source, and then you have completeness. So you look at it for its completeness every single year.

16:29If there’s gaps, you assign an owner to recollect. But let’s say the tabulation is consistent: each cohort, each year, each point. This gives you the opportunity to do critical analysis in a way that you have not done before. You can look at it in real time. You can look at it on a frequent basis. And you can look for whether there’s a conclusion. There’s always some sort of conclusion.

16:56Either it means the monitoring is needed because there’s no action needed, or there is action needed. You draw conclusions to see if there’s a strength or area needing improvement. Just one thing I will say is that in the Sixth Edition standards, ARC-PA does not require you to use strengths in your SSR, but you can still use strengths for program purposes in terms of what your aspirational benchmarks might be.

17:24So if it does need an action plan, and again, you decide the action plan, being two or three data sets that align. And you have to think of it from a contextualization viewpoint because the data all interacts with each other. So some data is going up, some is going down, but you’re looking for patterns. And I would say that it’s not so much microdata like it was in the Fifth, but it’s more, I’m going to say, it’s more context-based, looking at the data all at once in one place.

17:59So a self-improvement action plan then is implemented with a measure, owner and timeline. If that would be to modify a course, modify a curricular element, then you would close the loop, and then you would update the source of truth with the next cycle. So again, this is the opportunity to take your data from multiple places and put it into that one place where, on a regular basis, you can do this now.

18:31One thing I’ll also say is that if you’re familiar with the C1.01 questions, there’s five questions each year. You can plan a time frame where you can go through each question. It may not be the same day. It may be, you know, over a cycle. But to be able to analyze your admissions process, analyze faculty work outside the classroom, analyze the didactic... Once you have all the data, then you’re able to find those trends rapidly, and not just be looking back in time.

19:10So in the Sixth Edition, reporting time is faculty workload. So, the requirements around faculty workload and sufficiency. Think about the time that’s spent collecting, tabulating, reconciling, validating data.

19:29I’ve been working with, my company has been involved with running data for programs now for going on five years. And using a manual source, say if you have multiple spreadsheets or you get it in PDF, that is an enormous task. And sometimes it would be 15-plus hours to be able to do that. And I’m going to tell you how it can be done more rapidly. But in most programs, this is where I think the stopping point comes, because in having a conversation with administration, they often don’t understand how much time this takes, how much time it’s taking away from student-centered activities.

20:21So let’s talk about a system that holds up: one structure organized by required assessment data. So you define the methodology once, applied each cycle. So if you have the cycle, if you have the cadence, then a new director could step in without any trouble whatsoever. It’s already defined. And you can define your system so easily. I mean, again, I use AI quite frequently, but you could develop a cadence system through AI, even, that would define it for you.

20:57And there you have it: that way there is no point of failure that happens. And those in the audience, I would be interested to hear, if you ever were to share, if you’ve experienced where there’s a point of failure with your data and all of a sudden it’s lost and you can’t find it and you can’t recollect it. Interpretation attached to every output, at the same time as the number.

21:26And the cadence runs ongoing, each time, each year, whether or not a visit is coming. So if you think of it that way, then if you’re on a ten-year cycle, doing an SSR should be a piece of cake, because if you’ve done this every single year, and then you’re able to analyze the questions each year, then you’re able to see it over a period of years. And now you can start saying, I’m seeing the contextualization and the trends over time.

22:01So you can build all these properties yourself. But unfortunately, you know, oftentimes what breaks down is the cadence, where it just isn’t done. And, you know, working with programs that come out of initial provisional into provisional monitoring stage, it’s a hard sell to be thinking about data collection and analysis when you have a first class.

22:26This is the framework of all five: one structure, organized the way you’re evaluated; methodology defined once, applied each cycle; no point of failure; interpretation attached to each output; and a cadence. And again, I’m not gushing over the C1.01 and C1.02 spreadsheets, but boy, they’re a great instrument for you, because now you have a home base that you can put your data into.

23:01So, a little bit about Edulytic Solutions, folks. This is a company that I co-founded, and it’s been under construction now going on its second year. It’s really about a reporting system and a continuity system for programs. So in looking at the time investment that Edulytic is able to do: I talked about the five-hour manual process for any kind of tabulated data of any format: PDF, CSV, Excel.

23:38It’s five hours versus two minutes. I’m going to give you just a brief glimpse of that. The workflow is a direct upload and then AI validation, and then you get a notice that it’s ready for you to download. Narratives are written under pressure, oftentimes. But in this case, you get an analytical summary with every data point that you get. So if you upload a whole year of course evals, you will get an analytic summary for every single course that you analyzed.

24:14The numbers: it’s been based upon a lot of quality assurance over a period of about a year. So running it manually versus running it through the system, which was coded from original Python code and then connected to internal AI. It’s not connected outside. It has greater than 99% accuracy. And obviously the speed is quite significant. And again, there’s always oversight of this with every output. So as I mentioned, upload into the data hub, and then translate, validate, tabulate.

24:57And then you get your data and report fairly rapidly. Again, our goal is seven-day turnaround for everybody. And then there’s no new system of record, no integrations, no IT approval. I’ll talk about security with you a little bit. So the other day I just had this done. So this is just like a mock-up of an actual course. Just drop it into the system. So we just did course A, B and C for several years, grades, response rates, et cetera.

25:31So this is all automated. Also automated is the course eval analysis summary. We do this for every data point. So you get an executive summary and qualitative patterns. And then you get course-level analysis, performance trends, qualitative patterns for every course as well. This can be used within your committee structure to be able to, in real time, look at the analysis and make the connections. This is another example of what came out of that one output.

26:07So what this does not do, though: it doesn’t replace internal decision-making or committee review. It doesn’t remove your responsibility for oversight and documentation. So there are SSR components outside its scope. So MACS is my consulting company. We do SSRs for programs. So it’s kind of like a crossover. Edulytic Solutions is a division of MACS, but it’s a separate company. And so we do SSRs as part of that as well.

26:38And as far as security, we’ve gone through a SOC 2 compliance process, and then the handling as well. And so there is a high degree of security that takes place within the platform.

26:52So let’s kind of go back to you now. Let’s talk about some things you can do before your next reporting cycle. One is to inventory your sources and write the numbers down. Document the methodologies for the three most-cited metrics. Name a second person who can produce the annual report. Then have them do that. Have them do one as well. None of this requires buying anything. The system that I talked about is something that you don’t have to purchase: the ability to have one place for your data, and to be able to develop a cadence that can be written down and followed.

27:35But I will say that there’s some barriers. One of the barriers is that institutions have to support programs in the Sixth Edition standards. It’s no longer, basically, one substandard, A1.02. I think it’s B or C: that the institution supports the assessment for the program. They’re responsible for it. So if you’re going to your dean or provost and there’s not enough resources to be able to do this, your program is already a year behind.

28:08So oftentimes I’m having conversations about how you have to have resources, either from institutional assessment, which I would say one out of three times is adequate, or internally in a program, somebody that it can be delegated to. Some faculty are really skilled with this, but they have to have workload assigned to it. It could be a data science person that is either a staff member or a PhD-level faculty member.

28:38So somebody or something has to be able to be responsible for this to work completely.

28:49So I have some questions, actually. What I want to do: some questions, the more rhetorical questions, and anything else. So, you already have a shared drive and a naming convention. Is that a system? Not quite a system. So if you have a shared drive and a naming convention for the files, how does the data get analyzed, and how is the cadence developed?

29:59What implementation is required for my faculty? So again, let’s talk about the system that I talked about. It really requires buy-in from everyone that the program will be an assessment-based culture. It really has to be that everyone shares and owns data. Everybody owns the responsibility. But if it’s defined and it’s not something ethereal, and it’s not just in one committee or sitting somewhere, then nobody’s going to be able to own it.

30:35The other question is, who’s looking at your students’ data? And I think we’ve already talked enough about that to be understood. So, you know, we’re a small program. Is our N large enough for this to mean anything? Well, you’re looking at cohort-level data. So when you analyze cohort-level data, being at 100, being at 20, you’re still looking at cohort-level data. You’re still looking at cohort-level data trended over time.

31:04So the numbers really are not that relevant. We’re not talking about statistical power numbers here. We’re talking about comparison across cohorts.