Creating a Competition Analysis Chart from IPEDS and Clearinghouse Data

The National Student Clearinghouse provides information about applicants to your college that ended up enrolling somewhere else. By combining this with admissions and financial aid data that you have (e.g. high school grades, standardized test score, home state), and with public IPEDS data on institutional characteristics, you can graph the average characteristics of competitor institutions. It's not perfect--the IPEDS data is a couple of years out of date. Nevertheless, it's insightful.

There are many types of reports you can generate using this combination of data, but I'll focus on one that resembles a supply/demand relationship between the quality of student and the quality of the the attended institution. An example is shown below.

Here, the students are split into three categories: those who attended your institution (horizontal black lines), those who chose a private college other than yours (green), and those who went to a public institution. The levels under the circles indicate the academic preparedness as determined by the admissions process (usually a logistic regression model on HSGPA and standardized test scores). I've left the scale off of the graphs so as not to give away all our secrets, but the arrangement of dots in the scatterplot is real.  It shows that students generally sort themselves out pretty well, matching their academic ability (proxied by HSGPA) to the quality of the institution (proxied by graduation rate). Moreover, there is a pretty consistent gap in institutional quality that at least partially justifies the price difference (you can also create a scatterplot like this using graduation rate versus tuition). 

In my actual plot, I have a horizontal line that shows my college in relationship to these. Instead of showing you that, I've drawn three possible ones called case 1 through case 3. If your college's line looks like Case 1, you're not competing on quality because you're losing students to lower quality institutions. In Case 3, the opposite is true, where quality may be an important factor--definitely something to follow up with, e.g. the Admitted Student Questionnaire from the College Board. The middle line (Case 2) leads to a guess that your college is competing on quality with other privates and on something else (like price) for the mid-to-lower tier student, but that for the best qualified students, quality is still an issue. 

None of these conclusions is demonstrated here to be causal, of course. This is a starting point, not an ending point for analysis. But one of the advantages of this type of graph is that it's easily communicated to stakeholder, and generates the right kinds of questions about admissions requirements, marketing, and financial aid.

If you want to try this at home, I've already downloaded and combined some of the data you need. Here's a list:

• [download] Selected IPEDS categories from the most recent years (2010,11), with the OPE institutional ID included, so you can easily connect to your Clearinghouse data
• [download]A rudimentary data dictionary. I renamed columns to be more intuititve than the IPEDS ones.
• [download] A Perl script for joining tables without using a database. You'll need a little experience with Perl (you have to install a module or two), but it's a real time-saver.

Tips:

1. Remove cross-enrolled high school/college credit by filtering on the enroll date, so you don't accidentally pick up a community college a student earned credit in while still in high school.
2. Remove duplicates and generally give the data a careful look as you would for any such project.
3. I recommend Tableau as a reporting tool. It's expensive but worth it.

Feel free to contact me with questions, or leave a note below.

Edit: As a bonus, here's a scatterplot of just the IPEDS data, comparing the 75th percentile of SATR and SATM combined versus graduation rate (click to see a bigger version).

Automated Reporting

One of the most time-consuming tasks of an IR office is to provide answers to a constant stream of questions like "how many female psychology majors do we have?" For advisors, there are detailed questions about courses, student schedules, and course histories that are essential to have timely answers to as well, and these come all at once. Of course, any college nowadays has and electronic system of databases and interfaces that allows answers to such questions. The ones I'm familiar with, however, are much better at storing information than reporting it back--it can be done, it's just not always as easy as one might like.

What do you do if you want to find a water fountain in a building? This seems like a non sequitur, but bear with me for a moment. There are two things I always emphasize to my young daughter when we're out and about. The first is, turn around once in a while so you'll know what your path looks like on your way back, and it will be familiar. The second is "think like the designer." If you want to know where the water fountain is, try to think like the architect. A water fountain is an add-on, so it's likely to be close to other water sources: the bathrooms, for example. It's likely to be on a traveled hallway, etc... This works with interfaces too. The best software interfaces seem effortless because the designer put a lot of thought into consistency and ease of use. Most university software systems like Datatel, Banner, Jenzabar, aren't as easy to use. If you want to figure out why a particular thing is the way it is, you should keep in mind that the designer was probably a programmer, not a user. There's a big difference.

Programmers like modes, for example. Switching from one thing to another. Does your system have "modules?" You can bet some programmer cooked up that idea. Do you have to switch screens three times to do the job of one? Same thing. What's convenient for a programmer is not usually convenient for a user. An example is Datatel's implementation of its windows-based user interface. For years we used a terminal mode that looked like it was out of the 1960s, and probably was. It was ugly, but it worked, and it was fast. It was programmer-designed, no doubt, but it had the advantage that the network demands were very small for sending little packets of text back and forth and throwing them up on the screen. Response time was great, and once you learned the dictionary of short-cut keys, users could fly through the screens. Not a perfect situation, but acceptable. Datatel was probably taking heat for it though--everybody wants a mouse interface, pull-down menus, and a nice GUI look, right? Rather than starting from scratch and building the thing, as far as I can tell, they just layered it on top of the existing software stack. What this means to the user is that it was very slooooooow. After our implementation, the staff complained bitterly about it, in some cases reporting tasks that took more than five times as long as before. Of course we passed this on to the technical reps at Datatel, but they'd heard it all before, and had little tweaks instead of solutions. I hope they've fixed it by now, but this is a good example of a programmer's "solution" to a user problem--it actually made things worse. Note that this isn't an indictment of the whole Datatel system--the database itself was very stable and usable. With a package to get data out, reporting is bearable. There have been some very unfortunate design decisions in the last releases from the user point of view, but underneath I found it to be very solid (after about eight years of use as IR guy and at one point sysadmin).

So if you want to minimize calls to IR and you want to provide data to users, ease of use and response time are premium qualities to keep in mind. A few tenths of a second on a web page refresh make a difference in the real world... Simple and to the point is good.

Here's a partial solution to the problem of getting what you want out of a cranky database. It's not perfect, and it's designed by a programmer (me), but one who is also a user of the system. So it's a compromise between time programming and elegance of design. The users love it, once they get over the small learning curve.

Schematically, here's the architecture. openIGOR is my open-source project for a document repository, but I also use it to push out portal functions. It uses perl's DBI interface to talk to the databases it needs to. All of them are SQL, so this is pretty straightforward. Note that it's not at all straightforward if you mix linux and windows, but I've now ported openIGOR to windows and will be releasing that code soon.
The warehouse database contains a Reports table that has the name of the report, SQL code for the report, a list of field names to assign to the columns, and a report type. The SQL code can have variables embedded in it, which are interpolated before being sent out. For example:

SELECT student_id FROM Students WHERE (Start_Term eq '_current_term');

This is an invented example where the _current_term will be interpolated by the perl script to put in the actual current term: a literal like "09FA" or something. This makes the reports general and customizable.

In addition, the field names from the report table can have markups in them. The field names are used to label columns on the report that gets cranked out, but there are two specifications for filtering that can be included. First I use this syntax:

(simple_date) Date

To tell the reporter to run the subroutine "simple_date" on the data before sending it to output. In this case, the date that comes out of the database is a long ugly thing and I want to simplify it down to day/month/year. The second type of markup is a bit more complicated. For example,

(sub_2) [Schedule]

tells the reporter that instead of printing data in this field, it is to be a sub-report. A list of students could include a field that hyperlinks to that student's current course schedule, for example. The "2" means to hyperlink to sub-report number 2, and the brackets mean to provide a hyperlink instead of the actual data.

So how does all this programese translate to user experience? Here's what it looks like. After a pulldown menu to choose the report, the user gets a list of possible fields to choose from. Here's the one for Current Course Enrollment:

The interface is a bit busy, but it's simple to use. Select the boxes next to fields you want to see, and enter any restrictive filters on the right. Here, I've chosen to see all math classes. Here's a portion of the report that comes back:
The green hyperlinks take you to the student roster for that class. Once there, you can click to see an individual student's course schedule or see their whole course history with grades (there are some security precautions there for FERPA--I had to work it out with the college lawyer).

The requests for these reports use the cgi GET method, which means in practice that every report can be bookmarked for later use. Compared to trying to get this information out of our Jenzabar interface, this is MUCH easier.

The ability to deliver reports through the web opens up even more powerful reporting methods. For example, Excel has the ability to look at a table on a web page and grab data off of it to populate a field. Then you can do calculations. So if you wanted to know the total number of students enrolled in either math or English courses, with a few clicks you could generate those reports, embed the results into a spreadsheet and calculate the total.

But here's the really cool part. If you look at the report designer again you'll see a box at the bottom that produces the output in a text delimited format, rather than as HTML. This is great for feeding into other number crunchers. You could use Yahoo Pipes, for example, if the system weren't locked behind a firewall for security. This week I got a request for a custom report: financial aid needs to know right away if a student crosses the 12-credit mark in either direction. If they drop below, they're no longer full time, and if they go from 9 credits to 12 they become full time. Either way, there are financial aid ramifications.

Normally, solving this reporting problem would start with identifying the SQL query to pull down the data. I don't have to do that now because I have an interface. I just clicked through the report generator to ask for student identification information and the number of enrolled hours, and for good measure added the course schedule link for each student. Then I created a perl script, using the LWP web module, to run the web request, pull back the data in delimited form, parse it out, and generate a file with student ID and number of credits. Each day at 5:30 a cron job runs to check the current data against the day before, and if there's a student who passed the 12 credit threshold, email the right people. It takes perhaps a second longer to run the http request than to run a direct SQL query from the database. But in this context, a second delay means nothing.

Advantages. In general, the architecture presented above has some significant advantages. Database security is restricted to the the point of query--the reporter script--which runs on a secured database. So rather than checking each user's credentials for each report, this can be handled through openIGOR and not touch the Jenzabar credentials table at all. In addition to security, it's much more accessible: you don't need a specialized executable sitting on your desktop to run the report, just a web browser. You can do it from your phone. From the perspective of the IR office, imagine being able to send a hyperlink to a live report back to a requestor, rather than a number that will only satisfy them for the moment. Most users are sophisticated enough to generate their own reports after being shown the interface.

Disadvantages. The reporter does not try to be fancy about SQL queries. It works from the one given in the report definition, which generally pulls a swath of data on a topic, like enrollment. The filtering happens only after the whole table has been downloaded. This is an inefficiency because it's a lot faster to know up front if you only want math classes, for example--then you can as Jenzabar (or other database) for just the math classes, rather than all classes. Therefore, the reporter as described would not be suitable for very heavily used reports, running all the time; those you'd want to optimize. Rather, it's designed to be very flexible and to make it easy to drop in new reports.

Implementation. The code for running all this is not all that complex. I've made it work for Datatel and Jenzabar, and in principle it works for any reasonable configuration of database tables. Setting it up to work the first time requires some expertise--it's not plug and play. But I'd be happy to help others do that if there's demand.

Curricular Networks

I wrote the following report years ago, and stumbled upon it. Most of us are probably used to the idea of curriculum mapping as a way to find out what gets taught where, but there is another way to make a map of the curriculum. I have edited it somewhat.

---

1. Introduction

Course prerequisites ideally keep students from taking classes they are unprepared for. As a logistical side effect, they theoretically limit enrollment in classes with prerequisites. Taken together, prerequisites form a network of courses that theoretically describes the curriculum in terms of logistical flow. We will look at this network as it exists in theory and compare it to some actual data.

There is a wide variety of types of prerequisites, but they fall into two categories that may be combined with a conjunction.

AND Requirements

These simply list a group of courses that must be taken prior to the target course. Each listed course restricts further the potential enrollment for the target.

OR Requirements

In the most general description, these are requirements to take a fixed number of courses from a list. In the simplest case, it’s of the form Take ABC-101 or ABC-102 or ABC-103. In the more complicated form, they may require more than one course from a list, as in Take 2 courses from ABC-101, ABC-102, or ABC-103.

All of the prerequisites that currently exist are described in our registration database as being one or the other of the above types, or a combination of the two conjoined with AND.

2. Method of Analysis

Course prerequisites are described in a systematic way in the administrative database. These were obtained in electronic form and parsed. Student transcripts were then used to count how many students satisfied the requirements for a class and how many actually took it. This process is shown in the schematic below.

In the first step, a symbolic logic representation of each prerequisite tree is generated. Some of these get quite complicated, and in fact there were a few that were infinitely long because of ‘looping’ prerequisites in the Dance curriculum. A few such prerequisites were artificially truncated in order to let the computation finish within the lifetime of the universe.

As an illustration, here’s the recursive logic for BIO 211:

BIO-211=(MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-101+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111*MAT-100*MAT-101*BIO-102)*(MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-101+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111*MAT-100*MAT-101*BIO-102)*(MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-101+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111*MAT-100*MAT-101*BIO-102)*(MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*BIO-101+MAT-100*MAT-101*BIO-110*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111+MAT-00*MAT-101*BIO-110*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*BIO-101*MAT-100*MAT-101*BIO-102+MAT-100*MAT-101*MAT-100*MAT-101*BIO-110*BIO-111*MAT-100*MAT-101*BIO-102)

This is by no means the simplest way to express the proper relationship, but it has the advantage of being completely machine generated and can be processed by existing algorithms normally used for teaching computer architecture courses.

Once the logic is generated, a ten year range of transcripts was processed to compute how many students during a semester had satisfied the prerequisites for courses offered that semester. It was a simple matter to count up how many actually had taken the course, so then comparisons can be made. Both recursive and non-recursive methods were used, and ultimately I decided that the non-recursive ones were the most useful. Basically that means that a student is counted as having satisfied the requirements for a course if he or she meets the overt requirements, but not necessarily the sub-requirements. The long process of working through decisions like this one boiled down to finding an appropriate metaphor for the curricular network. Ideas that seemed reasonable, but were ultimately discarded were modeling the requirement tree on a digestive system (yuck), a river (too simple), an electrical network (no way to include the complicated ‘or’ prerequisites), a quantum mechanical system (no one would understand it), pure probability (leads to the computation of huge correlation matrices), and multi-valued logic (problems with commutivity). I finally settled on using the model of a rather simple computer chip. You could, in fact, burn the course requirements onto a piece of silicon (excepting the Dance department, with their infinite loops).
The diagram above shows an imaginary set of course requirements for ABC-300, implemented with logic gates. The one for the biology example above could be simplified to fit on one page, but would be more work than it’s worth.

3. Historical Data

It would be natural to expect that the more severe the prerequisite restrictions, as reflected by the number of students who qualify to take a given target course, the fewer students would actually take it. In other words, one would expect to see the emergence of a straightforward linear relationship with nice correlation. The truth is a lot stranger. The scatter plot below summarizes five years of data.

Each dot is an individual course type taught during a semester. So all HIS-210’s for a semester are lumped together, for example. The vertical scale shows how many students during that semester met the requirements for that course, according to their transcripts (transfer courses were NOT included, which must be taken into account when drawing conclusions). The lines at the top are actually accumulations of dots that represent courses with no prerequisites, so all students qualified. The horizontal scale shows how many students actually took a course type (all sections within a semester being aggregated).

The data has a correlation coefficient of .2, which means it isn’t particularly linear, an obvious conclusion one can draw from the picture. The conclusion that does force itself upon us, just from inspection, is that there are essentially two types of courses. Those with no prerequisites (top of the chart) and those with prerequisites (bottom of the chart). If we only consider the group at the bottom, we get a correlation coefficient of .4, which is a stronger indication of linearity than before, but still not very compelling (it would need to be near 1).

Since many prerequisites are of the AND type, one would expect demand to drop exponentially with each additional requirement, which is a strong hint that we should look at the graph with a log scale on the vertical axis.
Now we can clearly see the ‘funnel effect’ of restrictions on class entry. The relationship isn’t a simple linear one, where fewer restrictions equals more enrollment, but rather sets the upper limit on class enrollment statistically. It’s easy to see this boundary where the dots stop toward the right of the graph at each level.

Of course, there are many factors that determine why a student takes one course rather than another. One would guess that a main consideration is the student’s major. This is borne out in an analysis of class correlations. The chart below shows a matrix of correlations between classes taught over a ten year period. Courses are in alphabetical order, which has the advantage of grouping together ones from the same discipline.
A perfectly distributed curriculum, where every student was equally likely to take any given class, would give a perfectly uniform matrix. The ‘clumpiness’ visible above is evidence of a high degree of selectivity. The dot size, ranging from specks to balloons, is indicative of how strong the correlation is. The fact that most of the larger ones are distributed around the diagonal shows that students have a tendency to choose classes within the same discipline. General education classes or other popular classes will appear as horizontal and vertical streaks (correlation matrices are symmetrical). The main diagonal of the matrix has been omitted, because all it would show is relative class size for each class, and would obscure the relationships we’re looking for.

4. Conclusions

Prerequisites for classes create barriers to entry. The example in Exhibit 6 below shows a diagram of the prerequisites in Math and History.


The average number of prerequisites per course is 2.09 for Math and .95 for History.

Given the same number of majors, you could get by with fewer History classes per semester than Math classes. This depends also on actual requirements for the degree, of course, and not just on the information presented above.

Obviously if most students can’t take a particular course, it’s going to have lower enrollment. More than half of courses taught probably fall into that category. In many cases we can’t do anything about that unless we radically change the way we think about majors and disciplines.

In some cases we have introductory level courses segregated by the type of student. For example, Biology has separate courses for majors and non-majors, while English has a separate course for transfer students. This is another kind of prerequisite.

It would be a good thing for programs to review their curriculum from this "barrier to entry" approach periodically in order to reduce unnecessary complications and to perhaps unwind an infinite loop or two.

First, Do No Harm

Our state meeting of independent schools' institutional researchers was yesterday. As usual, I picked up some new ideas. I learned about Remark Office optical mark recognition, which can be used to create your own scannable forms for doing surveys. This eliminates having to buy the expensive forms from Scantron (which work well, however--that's what we currently use without problems). The scanner is apparently more forgiving about what kind of marks are made, also. So no more sharpening 1,000 pencils for student evaluation day.

There was also a good discussion of the role of the IR office in strategic planning. The 'safe' option for an IR office is to produce reports and forward them on to the decision-makers. I've done a fair amount of that myself when I was finding my way as a newly appointed IR director. The problem with that is that decision-makers aren't necessarily in the best position to use the data. Also, the information we work from is often incomplete and open to interpretation. The accreditors like SACS seem to imagine a perfect world where assessment inevitably leads to improvement. But the essence of leadership is courage, not data. In mulling this over, I have come to the conclusion that we in the IR business should help the decision makers as follows:

1. First, do no harm. If the data are pretty strong against a current or proposed policy, use IR's influence to get the policy changed. Example: One of my colleagues at the meeting mentioned that he'd just finished a project having to do with AP testing. In this case, the faculty were convinced that students who used AP credit to skip the introductory class in a subject were ill-prepared to handle the second course. In looking at the data, however, he found that the opposite was true, except in one case. In that case the curriculum for the AP class wasn't a good match for the 'target' course. So he helped kill a proposal that would have eliminated AP credit at the university--a decision that would have had massive admissions implications.
2. Most of the time, the data aren't going to point one way or the other. In these cases, it is better to encourage decision-makers to adopt policies that subjectively may have a positive affect. That is, rather than creating 'data paralysis' when there is no clear direction, keep moving and trying out new things. At worst you identify which policies are bad, and at best you may find the right one accidentally. Example. We adopted an initiative to improve student writing. We have some assessment data on writing, but nothing that clearly and unequivocally says DO THIS! So the committee assembled a variety of opinions about remedies and eventually adopted some of these as policy. They are quite reasonable and may be expected to improve the situation. In parallel, our assessments are improving, so we may be able to tell in a year or two whether or now we have succeeded. This is much better than simply giving up because there is no clear way forward.
3. If the data clearly indicate the need for a new policy, do whatever you can to get it implemented. This is what an IR officer should live for--that one study or report that shows YES--this thing really matters and we should act on it. This is where the courage part of leadership comes into play because said IR director will be out there making the case as the spokesperson. Simply passing it on isn't good enough. Make it happen! Example. We recently did a shotgun-style survey of our students and found stark differences between first-generation students and students of college graduated parents. The differences were in attitudes about money, plans after graduation, and attitudes toward offices and services provided by the college. Given the national statistics on attrition for this group, this data cries out for action. So I've used three committees to sell the idea--the IE committee, our retention committee, and the one on improving writing. The issue of preparing first-gen students for their first year cuts across all these areas.

In summary, I advocate the position that the IR director form opinions about the usefulness of the data and then take the leadership to act on it. If it's equivocal, encourage the debate and look for opportunities to use data or at least improve assessment. But don't just publish a report full of charts and numbers with lists of standard errors and dump it on somebody's desk. Taking action by advocating for or against a policy takes courage because you can find yourself on the 'wrong' side of the administration, AND you can be wrong! But I believe it vastly increases the usefulness of the IR office to the institution. There is a danger, of course, that IR will seem to be more politicized. But if you call them as you see them rather than being a sycophant to the administration, you'll be respected for it.

This relates to another role of IR--to provide artificial certainty. The question "how many students do we have?" probably has a dozen possible answers, depending on what you count as a student (part-time? FTE? degree-seekers?) and when you take the snapshot. Decision-makers don't need to know all the possibilites. So figure out a method of measuring this fuzzy number and then publish it as fact. And it shall be so. As a story about three umpires has it, the third says of called plays: "They ain't nothing until I call them."