Build a way for TUSMGP to accurately and consistently report enrollment funnel data, specifically year-to-year comparisons for started, submitted, complete, admitted, and intends to enroll data points.
Detailed description of desired deliverable:
Research:
Entity was named Reporting Data (Application)
AFD = Application Funnel Dates
Outcome:
Created a custom entity for Reporting Data (Application) in the TUP database, which is application-scoped and can be used to store status change date (SCD) values for the application record for use in application funnel reporting.
Reviewed 2023 and 2024 application cycle data to do data cleanup and ensure accuracy.
Built process for monitoring and regularly reviewing application data to ensure accuracy.
DPT-BOS, DPT-PHX for 2023 data
Pull file from WebAdMIT (for CAS dates and started and submitted dates)
Pull file from Slate (for date imported = complete)
Clean WebAdMIT and Slate file, combine using XLOOKUP and match on CAS ID
Create import XLS file to include
Corrected CAS/Application ID
Started Date
Submitted Date
Verified Date
CAS Complete Date
CAS Verified Date
Complete Date
Import file through source format in test
Review records to check for errors or issues
Import file through source format in prod
Review records in prod to check for errors or issues
Check totals in Slate report against the past enrollment numbers from spreadsheets to ensure totals are matching
DPT-BOS 2023
DPT-PHX 2023
O-MPH for Spring 2023
Pull file from Slate to include
Submitted date
2U complete date
Import file through source format in test
Review records to check for errors or issues
Import file through source format in prod
Review records in prod to check for errors or issues
DPT-BOS, DPT-PHX for 2024 and MBS, MPH, O-MPH, and Dual Degrees for 2023 and 2024
Pull file from Slate to include
Clean Slate file
Import file through source format in test
Review records to check for errors or issues
Import file through source format in prod
Review records in prod to check for errors or issues
Check totals in Slate report against the past enrollment numbers from spreadsheets to ensure totals are matching
Notes on Data Audit / Cleanup process
Retention policies to delete apps without certain fields after a period of time? (No program, start term/year, etc.) - need to be careful this wouldn’t delete real applications that just had errors for some reason.
Retention policies to delete JD/MPH apps without a reply to the “combined MPH program status” question?
No applications that are deferred (cloned into this cycle) should have created, submitted, or decided dates in the Reporting data entity.
What to do about B/MPH now that they are letting them apply during sophomore year? When people apply “late” how do we count them in the numbers (have app in same cycle/not deferred?)
What about people with more than one application to the same program in the same round? (i.e., one for spring and one for fall)
Retention rules to delete reporting data rows with no date value (extra from import)
Retention rules to delete apps w/o program for more than 2 weeks in awaiting submission status?
Retention rules to delete reporting data rows on apps tagged with Error - Do not Use
Query to look for applications that have more than one of each kind of AFD
Program = PA or PA/MPH
Start Term/Year = Spring 2025
Initial query shows that Reporting Data (Application) (RDA) values are:
Created = NULL
Submitted = populating with date for submitted apps
Complete = populating with date
8/2/2024 - spent the day creating files and testing imports into both prod and TEST. Was able to identify the issue with the complete date populating (even though we hadn’t decided how to measure that). Was happening because the existing export/import loop was not excluding PA students and so was passing through the date their Slate app became awaiting decision. Updated the export, then did a retro refresh to delete all existing values, then ran an import with the prereq fulfilled date from CASPA.
For 2024 data, figured out that the only applications in Slate are those that got interviewed last cycle, leaving the in progress, submitted but incomplete, and the complete but not reviewed/denied before interview files not in Slate. Created two sets files saved in Box > Projects > Slate Data Audit Cleanup > CASPA 2024 Application Funnel Dates folder, one for the existing apps to update and one for the records that didn’t already have a 2024 application. Plan is for the first group to import just to update the fields on the existing app; for the second group, will import and create unsubmitted applications with AFD updated. This way we aren’t adding to our total for billing reasons.
Created a source format in TEST to import the file with the “no applications” records. This worked, matched with existing records or created new ones. Not having the rules fire means the header doesn’t get updated and the CAS ID doesn’t get indexed for the search field… still think that’s better than letting the rules run but should maybe test that?
On Monday, need to do the import for the existing applications. also need to update the test source format with the program (forgot about PA vs. PA/MPH but updated the spreadsheet so it is there now). Then run the import for the non-existing applications and have Alessa/Hannah check the deliver campaigns to make sure that those applicants aren’t going to get any emails. Maybe refresh test first?
Problems to solve still:
Long-term solution to getting the date prereq fulfilled out of CASPA and into Slate, since the WebAdMIT export DOESN’T EXPORT AS A DATE and instead is a text string AARRARAHRH
Should we import bio/demo data into the 2024 PA applications? Probably good for future reporting, but also annoying
Should we also add final decisions to the 2024 PA applications? Deny, incomplete, etc.?
Program
Created Date
Submitted Date
Complete Date
Program
Created Date
Submitted Date
Complete Date
PA and PA/MPH
CAS/Liaision In Progress Date (date they select our program from CAS)
CAS/Liaision Submitted Date
Date that prereq requirements were reviewed and marked fulfilled