StandardsBased Electronic Transcripts for the University of Michigan

1
Standards-Based Electronic Transcripts for the
University of Michigan April 23, 2007
2
Speakers

• Jeff AldersonDirector of Strategic
  PartnershipsConnectEdu, Inc.
• Duane MorinService ArchitectConnectEdu, Inc.
• Mark NelsonProduct Manager, Recruiting and
  AdmissionsUniversity of Michigan

3

• The High School Transcript Opportunity
• Colleges need electronic HS transcripts
• HS lacks expertise, time, resources ()
• Transcript-only solutions have historically low
  utilization rates value prop is too limited
• Solution?
• Deliver electronic transcripts as part of a
  going-to-college platform that meets the needs of
  students, parents, and counselors

4

• About ConnectEdu
• Provide students and families with a
  comprehensive college planning, admissions and
  financial aid solution
• Help high schools, community colleges, and
  colleges simplify and streamline administrative
  tasks
• Provide colleges (2 and 4 year) with a
  cost-effective method to recruit applicants and
  improve admissions processes

5

• Our model
• Deploy in partnership with area colleges
• Invite feeder high schools to join for free
• Train and equip high school staff, parents and
  students
• Commit staff to the geography to provide
  long-term support
• Build value to colleges by creating a base of
  electronic document-capable school districts

6

• How I Spent My First Week At Connect!Theres
  this new PESC standard for High School
  Transcripts. Implement it.
• July, 2006

7

• PESC High School Transcript Approved June, 2006
• XML via SOAP Web Services
• Consistent data delivery. System to system with
  no re-entering of data
• Encryption, authentication
• Immediate delivery
• Build it in .NET (C)

8
Sample XML

• Easily readable
• Well organized
• Easy to find what you need
• Many optional and/or repeatable fields
• User-defined extensions

• ltTransmissionDatagt
• ltSourcegt ltOrganization/gt
• lt/Sourcegt
• ltDestinationgt
• ltOrganization/gt
• lt/Destinationgt
• lt/TransmissionDatagt
• ltStudentgt
• ltPersongt
• ltName/gt
• ltContactsgt
• ltAddress/gt
• lt/Contactsgt
• lt/Persongt
• ltAcademicRecordgt
• ltAcademicSummary/gt
• ltAcademicSessiongt
• ltCourse/gt
• ltCourse/gt

9

• Converted our older transcript repository into
  PESC-compatible object model

10

• Reality Check!Our first implementation will
  be University of Michigan. They want to use EDI.

11

• University of Michigan Platform
• Oracle PeopleSoft Campus Solutions 8.1
• PeopleTools 8.22
• Oracle 10g
• Upgrading to PS 9.0 in June 2008

12

• UMichigan Today
• 27000 incoming applications
• 2-3 copies of transcript (in progress and final)
• Letters of recommendation from multiple sources
• Complete applications reviewed up to 3 times
• The Problems with Paper
• Slow, unreliable
• Postal delayed / lost in the mail
• Postage costs!
• Manual processing time-consuming and error prone

13
For those whove never seen EDI

• 999ADM_TRNS_TS130 ADTRADM_EDI_STX
  UNIVADMISSIONOFFICE 13000049885220061207
  162341 15
  
  
  
  REF487456928
  ORG 231032
  FOUR-O-LETTERGRADE HIGH SCHOOL20 PARK PLAZA
  
  BOSTON MA 02116
  USAPER04 NAM02AMY
  NAM05O'REILLY
  ADR21 GALLUP TRAIL
  
  POMPANO BEACH FL 33067
  USAC SES2 OTHR 20030901 20031224 SUMOTH
  4.22 52.5 7 4.22
  CRS CRS1 EDIRGB
  ALGEBRA I HON UNT2
  B AL-01-H ... REF487697465
  ORG 952119 BUILD26.1
  20 PARK PLAZA
  ST. 930 BOSTON
  MA 02116 USAPER04

14

• Thanks for the roadmap!
• Organization, PersonSession Summary
• Hey, wait a second! This looks familiar!

15
(No Transcript)
16
(No Transcript)
17

• Go with the flow, dont get in the way
• Guidance counselors still have to review and
  approve

18

• Electronic Workflow
• Student makes request to send transcript to an
  electronically enhanced college partner.
• Guidance counselor reviews and approves, but now
  request moves into READY state. Experience for
  the student and GC is the same as traditional
  sending to college partners.
• Once per day, all READY requests are processed
  and a batch is placed onto SFTP server.
• READY becomes SENT.
• College partner user can still view/print
  transcripts during this process, just not change
  their state.
• Once per day, college partner system logs in
  (automatically), downloads files for local
  processing.

19

• Electronic loading of transcript data for UM
• Each night a batched data file is automatically
  picked up by UM and placed on our server
• A process is run to read the file and load the
  data into a series of suspense tables
• Data validation occurs, error reports are
  generated if something is amiss
• Search/Match process occurs. Process identifies
  the high school code in our system, and locates
  the applicants record
• Post process loads transcript data into the
  appropriate tables in PeopleSoft
• Problem records remain in suspense. Undergrad
  Admissions staff check these tables to resolve
  any issues that stop post from occurring

20
But Wait! Theres More!

• Letters of Recommendation
• Mostly freeform text, but meta data still applies
  (source, destination, contact info, transmission
  data.)
• No standard
• Image (PDF) to be scanned
• Workflow (almost) exactly same
• Request. Approve? Make available.
• Once per day, pick up a batch.
• Acknowledge receipt.
• Unlike transcript, LOR has multiple sources and
  multiple documents.
• Matching done by Connect identifier, relies on
  the transcript already existing in the system.

21
(No Transcript)
22

• An LOR BatchArchive CU20061107-10001-9.zip
  CID7279939-1.pdf OK CID7279939-1.txt
  OK CID7279939-2.pdf OK
  CID7279939-2.txt OK CID7279939-3.pdf
  OK CID7279939-3.txt OK
  CID7028859-1.pdf OK CID7028859-1.txt
  OK CID7028859-2.pdf OK
  CID7028859-2.txt OK CID7028859-3.pdf
  OK CID7028859-3.txt OK
  CID7363519-1.pdf OK CID7363519-1.txt
  OK CID7376338-1.pdf OK
  CID7376338-1.txt OK CID7382972-1.pdf
  OK CID7382972-1.txt OK

23

• CU20061107-10001-9
• 20061107 timestamp
• 10001 unique token (multiple files per day?)
• 9 number of letters to expect in the file
• CID7279939-2.pdf/txt
• 72279939 student identifier
• 2 sequence (i.e. second letter of the batch)
• .pdf the letter itself
• .txt meta information about the letter

24

• Electronic loading of Recommendation Images
• Each night a batched data file is automatically
  picked up by UM and placed on our server
• A process is run to read the file and load the
  images into an Import Agent
• Corresponding index file is used to match the
  Connect identifier (loaded into PeopleSoft via
  the transcript load) with the specific related
  images
• Images are loaded into Imaging Software using the
  applicants UMID as one of the key values
• Images are immediately available to be viewed by
  Admissions staff
• Non-matches are held in special queue for
  Admissions staff to review and resolve

25

• What about acknowledgement?
• User interface can track sent but what about
  received?
• FTP Acknowledgement Handshake
• IDX FileltTranscriptsgt ltIDgt5A909E9D3FFF39E1BD28B6
  9EBC503CBFlt/IDgt ltIDgt5DDAB9143FFF39E1B7923D5DC16E1
  85Flt/IDgtlt/Transcriptsgt
• College system must delete the associated data
  file from the server, signaling We have received
  the transcripts/letters for these students.
• All or nothing!

26

• The Ultimate Success Story GPA Recalculation
• Now we just need a database dump of every course
  taken at every high school. How are you
  getting that now?Every summer admissions staff
  call up the high schools and get the paper
  catalogs.

27

• GPA Recalculation Manual
• UM does not use delivered GPA from High Schools
  (weighted vs. unweighted)
• Courses are flagged as academic or
  non-academic
• Only academic courses factor into UM GPA
• Every transcript is manually recalculated with
  this model
• Each transcript can take up to 10 minutes to
  recalculate, longer if an applicant has more than
  one transcript to review

28

• GPA Recalculation Automated
• Incoming Course data is matched to courses in a
  set up table
• There must be a one to one match of course
  details in setup for incoming courses to post
  successfully
• Courses are flagged as academic or non-academic
  before loading into course tables
• Custom program calculates GPA using academic
  courses and UM-defined rules

29

• GPA Recalculation Benefits
• Over 100 High School Partners in Michigan signed
  on with ConnectEdu
• Estimating 7500 transcripts will be received
  electronically next season
• 1250 hours saved by auto calculating GPA
• Doesnt include non-Michigan High School
  ConnectEdu partners!
• Transcript standardization
• Many different transcript layouts
• Customized display page of transcript data
  streamlines review process

30
(No Transcript)
31

• Course Catalog Web Service
• A one-time SQL query became our High School
  Course Catalog web service(Currently run by
  human interaction, hence not a true Web Service.)
• Each summer, High School course data is queried
  from ConnectEdu
• Existing course data setup is queried from
  PeopleSoft and compared
• New/modified courses are uploaded to setup table
• Greatly streamlines UM current process and
  eliminates reliance on external side system for
  maintaining High School course data

32

• Lessons Learned

33

• Getting Started Assembling The Team
• What platform will consume/process the data? How
  much coding is required, and who is doing it?
• IT/Security needs to be brought in as soon as
  possible to make sure their requirements are met
• Both teams need to understand how the other
  teams data systems work, what data is available,
  and how required data needs to be used for
  specific processes

34

• Matching Records
• Any time data is loaded from an external source,
  there must be a high level of confidence that we
  are loading the data to the correct record.
• Typical data matching fields include first and
  last name, SSN, birth date, and address
• There are various confidence levels of matching
  some levels are a clear match, some levels are a
  clear no-match, and everything in the middle is
  anyones guess
• Some user intervention is necessary for the
  ambiguous cases
• Lesson learned encourage High Schools to send
  all key matching fields
• Without these fields, most records end up in
  suspense, requiring manual matching to occur
• ISSUE Some personal data best used to
  identify/match students becomes more of a
  security / privacy risk for the organization,
  resulting in more resources spent protecting the
  data.

35

• GPA Recalculation
• Everyone does it differently
• Up-to-date course catalog is mandatory!
• Course names and descriptions change more often
  than expected
• Electronic transcript management produces a
  courses taken catalog as a side benefit
• Standard categorization of course (i.e. Algebra
  101gtIntermediate Math) goes a long way toward
  centralizing and streamlining the process

36

• Transcript AcknowledgementStudent Did the
  college receive my transcript?Parent ltfreak
  outgt ltphone callgtGuidance Counselor I dont
  know, the system never responded. Let me make a
  call and see what I can find out
• The sending institution needs feedback on proper
  receipt and processing of the transcript,
  otherwise its all shrugs and phone calls.
• Daily FTP batch is more error prone in this
  regard
• PESC Transcript Batch and Acknowledgement
  Standard nearing completion (for web services)
• Still, the acknowledgement standard twice
  references if something goes wrong, get on the
  phone.

37

• Where Are We Now?
• University of Michigan
• With over 100 High Schools in Michigan
  participating next year, we expect significant
  positive impacts to the admissions process
• Reduced staff time for recalculating GPAs
• Reduced staff time opening mail
• Reduced staff time for imaging Transcripts and
  Letters of Recommendation
• Faster completion rates for applications
• Improved customer service for our High School
  counselors and applicants!

38

• Where Are We Now?
• Connect!
• Web Service Delivering PESC Standard HS
  Transcripts Running (March, 2007)
• Michigan State University
• Letters of Recommendation (ConnectXML) included
• On-demand, with acknowledgement for each document
• Coming Soon (hopefully before my one year
  review!)
• Data Transport Specification (DTS)
• Newly collected fields graduation date, diploma
  type, mid-semester grades, class rank
• New standard tests PSAT, AP

39

• Questions?
• Jeff Aldersonjalderson_at_connectedu.net
• Duane Morindmorin_at_connectedu.net
• Mark Nelson mnelson_at_umich.edu