Application Funnel Reporting Improvements

Detailed description of desired deliverable:

Research:

Outcome:

Created a custom entity for Reporting Data (Application) in the TUP database, which is application-scoped and can be used to store 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
• created date
• submitted date
• date status change from awaiting materials to decided (most recent effective date)
• 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
• MBS
• 2023
• 2024
• DPT-BOS
• 2024
• DPT-PHX
• 2024
• DPT-SEA
• 2024
• O-MPH
• 2023 (summer and fall only)
• 2024
• MPH
• 2023
• 2024
• B/MPH
• 2023
• 2024
• MS/MPH
• 2023
• 2024
• JD/MPH
• 2023
• 2024
• MBS/MPH
• 2023
• 2024
• DVM/MPH
• 2023
• 2024

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