Decision Letter Data Flow for Graduate Admissions

This article will document the data flow of an applicant's information from submission of application for the admission and recruiting groups to admit/deny letters. The intended audience is those that are admitting/denying an application and the staff members responsible for sending decision letters.

Through the process of applying, admitting and sending of decisions letters to incoming students, the data flows through several systems. This knowledge article will help you understand how and when the data flows between the different systems.  It also includes some areas when you can see interruptions to the process [see attached Data Flow for Admit to Accept Letter]

LIAISON CAS/PEOPLESOFT SYSTEMS

Application Import Integration Process

1. An applicant submits their application via the CAS (Centralized Application System) student portal.
2. A daily integration process runs at 11:30 PM that does the following:
   • Creates an application in PeopleSoft for applicants who submitted their application that day.  The integration ONLY pulls in the following application statuses:
     • Outcomes: Received, Complete
     • WebAdMIT: Received, Complete, Verified 
   • Sends emails to admissions end users containing application import integration reports.
3. The application import integration report email contains two spreadsheets titled, Import_Results_[School Name]_[Date Stamp]_[Time Stamp] & SMP_Results_[School Name]_[Date Stamp]_[Time Stamp].
   • The spreadsheet, Import_Results_[School Name]_[Date Stamp]_[Time Stamp], lists all of the applicants who submitted applications that day.
   • The spreadsheet, SMP_Results_[School Name]_[Date Stamp]_[Time Stamp], lists applications that were suspended or errored.

Decision Integration 

NOTE: For this process to run successfully, the application has to already exist in PeopleSoft via the Application Import Integration Process described above.

1. A decision value is entered in Outcomes or WebAdMIT (part of CAS).
2. An export runs at 11pm daily that captures all the decision values entered and runs them to a directory on the FTP server.
3. A daily integration process runs at 12:30 AM that does the following:
   • Grabs the decision values file (mentioned above) from the FTP server and puts in Peoplesoft
   • Creates the decision row on the application in PeopleSoft. 
   • Sends emails to admissions end users contains decision integration reports.
4. The decision integration report email contains a spreadsheet titled, Decisions_Import_[School Name]_[Date Stamp].
   • The spreadsheet lists applications whose decisions imported successfully and/or "error".

DATA WAREHOUSE

Peoplesoft data is sent to the Data Warehouse starting at 1 AM every night. The University Data Warehouse provides a centralized location for all of the University's data, making it easier to access and analyze. 

SALESFORCE

Integration from the Data Warehouse to Salesforce

• The integration from the Data Warehouse to Salesforce runs every weekday morning starting at 4:30 am.  This daily process includes several components. 
• First, contact information is pulled in from the Data Warehouse into Salesforce.  Contact information is checked against existing contacts and if enough matching data is found, an existing contact is updated.  If no match is found, a new contact is created.
• Next, the data on application records is brought over.  Application data needs to be associated with an existing contact record to be successfully imported.  This is why the contacts are synchronized first.  Application data is also checked for existing records and an existing record will be updated if a match is found.  The match is found based on application center, term, academic plan, and academic sub plan.  If no match is found, a new record is created.  Salesforce expects each application to have one academic plan and no more than one academic sub plan.  If an application has more than one academic plan it will need to be update manually via a help ticket.
• Finally, any additional information related to the application is brought over.  This includes items like admission comment codes, scholarship values, and other sub items attached to the application record.
• The Salesforce team expects the daily synchronization with the data warehouse to completed by 10 am on a day with typical volume.  On high volume days, we send out an email letting interested parties know that the integration is delayed and a second email once the integration has been completed. 
  Note: High volume days can occur due to a large influx of new records into the data warehouse which happens frequently close to application deadlines, but it can also occur when there is a mass data update in the data warehouse (ex: the FAFSA 50K record+ update of 2024).
• Records can only be imported to Salesforce if they are present in the data warehouse.  If the record did not sync from Peoplesoft to the data warehouse in time, as described in the previous step, they will not show up in Salesforce until the following synchronization.
• Tasks can be added to Opportunities at this point to add financial or provisional information to letters.  This is typically done using macros.

MARKETING CLOUD

Estimated time from top of Marketing Cloud to your business unit's accept/deny data extension (2-2.5 hours)

• Once your data is correct in Salesforce, an API connector syncs the objects in Salesforce to Marketing Cloud every 15 minutes at the top level (the parent University of Pittsburgh Business unit).  
• Automations on the top level run at the top of the hour and the 30 minute mark every hour (24 hours a day). These automations put your data into "master" data extensions that the business units will query.
• In your business unit, all schools have a "source" automation that runs hourly (often it runs staggered with the top level automations mentioned above ex: at a quarter past the hour). This source automation for the school joins together the schools data into data extensions like; Student_Master (used for decision splits in Journeys) and email_contact. 
• In your business unit there is a decision letter automation. It also runs hourly, often staggered from the time of the source automation in the business unit (ex: at a half after the hour). It combines data tables for tasks and applicant information (like programs) in the email_contact data extension that are either in an admit, deposited, or deny stage and have an email address listed (if your applicant doesn't have an email address listed upstream in Salesforce or Peoplesoft they won't populate into this data extension). Once combined it puts these applicants into the accept deny decision letter data extension for your school.

Most Common Interruptions to Your Data Flow

• Liaison CAS/Peoplesoft
  • A decision is recorded in Outcomes/WebAdmit on a application that is not in received, complete or verified status (Only applications in those three statuses are pulled in to Peoplesoft).
  • An application was suspended or had an error upon initial load and the admitting office never went in to Peoplesoft to do a manual data reconciliation.
  • The decision recorded on an application had an error and needs to be manually reviewed by the admitting office. 
  • If a decision is made between 11pm and 12:30 am there is a chance that all the data will be delayed by a day between the CAS/Peoplesoft Applications.
     
• Data Warehouse
  • PeopleSoft to Data Warehouse Integration:
    The database job will sometimes have an issue or fail. When this happens an email is sent out by the Enterprise ERM (Salesforce) team notifying the affected department contacts of the delay and a follow email is sent when the jobs are complete.
  • Data Warehouse to Salesforce Integration:
    The integration jobs that pull the data from the Data Warehouse into Salesforce sometimes take longer to run when there is an extreme increase of records being imported. When this happens an email is sent out by the Enterprise ERM (Salesforce) team notifying the affected department contacts of the delay and a follow email is sent when the jobs are complete.
    
    NOTE: If you are a member of an admitting office using the data flow, you can request the Salesforce team add you to the above email lists.
     
• Salesforce:
  • The "MC Active" box isn't checked in Salesforce (thus preventing them from flowing into Marketing Cloud)
  • When contact information and application information are initially created in Peoplesoft, it will typically take two synchronizations (2 days) for the data to completely come over to Salesforce.  The contact record needs to be created in Salesforce, which happens during the first sync.  The resulting Salesforce contact ID is then sent to the data warehouse and associated with the existing record.  With that match made the application data can be successfully matched to the proper contact during the next synchronization.  This delay usually goes unnoticed as it is resolved during the APPL stage of the application.  It can cause a delay if a record is created and admitted in Peoplesoft on the same day.
  • The synchronization between Peoplesoft and Salesforce can be disrupted if there are multiple academic plans on the peoplesoft application record.
  • The synchronization between Salesforce and Marketing Cloud can be disrupted if there are duplicate contact records in Salesforce (open a help ticket for a Salesforce admin to merge records)
• Marketing Cloud: 
  • The Application isn't in an "Admit", "Deny" or "Deposit" state, thus their name will not populate into the Accept/Deny data extension
  • The person doesn't have an email address listed on their contact record in Salesforce so they will not populate into the Accept/Deny data extension