Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 154 Next »

Technical Details

Current Release Version: 2024.1.0

Release Date:

Report Location: PowerBI / Apps / SPS Admissions

Security Roles: Admissions, Leadership

Requested Features

Current Cycle

Release 2024.1.1 [April 2024]

Features to Complete

  • Factor in the Decision Release Date when comparing CTD values to give a more accurate depiction of the state of past pipelines at a specific point in time
    • Particularly useful to ensure that the fraud remains in the submits until they are actually flagged as fraud, and are then removed

Features to Consider

  • Add a target point to the current year in the KPI Tracker to show the ultimate goal
    • Include a trend-line for the current year to see how we are tracking toward the target
  • Add a view to show week over week progress for the tracked milestones
  • Add in an option to change the historic yield and selectivity percentages based on a multi-year average of all available years for the matriculant projection
    • Just add one more option to the basis year slicer called AVERAGE, and when selected switch the calculation to measures based on the collection instead of the single year
  • Add in Help Tooltips for major features to make it more clear how to use functionality within the user interface
    • CTD/EOT filter
    • Top term year filter
    • Comparison term year filter
    • Pipeline status gauge
    • Projected and actual gauges
    • All Years
    • Program Details
  • “What If” scenarios for individual rates to see how altering will change the overall numbers and outcomes
    • Selectivity, Submit Rate, RFI to App Start, Start to Submit
  • Change the Commit Date to the Deposit Received date where there is a value instead of the Decision Released date
    • Requires adding the Deposit date to the data feed from Slate and an update to the procedure for which date to pull into the report
  • Add the historic split between domestic and international to the basis parameters and include in the matrix
    • Use this value to alter the matriculant target when the report is filtered on domestic vs. international
  • Calculate the expected melt rate from history and add it to the grid
    • Update the overall projection based on the applied melt rate for each program
  • New columns (requires updated data pipelines)
    • Fraud rejects
    • Admit to ALP
    • Admit to future term
      • Currently handling as incoming deferrals in the enrollment term, and regular admits in the application term
    • HBCU
  • Matric Projected Calculations
    • Add subtotal to the column
      • Requires an update to the method for calculating from a filtered measure
    • Exclude deferrals to a future term from the projections for the current term
      • This is a very small fraction of the counts, but removal will yield more accurate projections
  • Add in an “Unfinished Rate” to show how many applications tend to be abandoned before completed for decision
    • This can be used to give a sense of how many submitted apps will move to the next phase based on history
    • This will require a new data point that separated Pending from the Incomplete applications
  • Create a detail page to show a more pointed view of the data for a selected program code
    • Visualizations
      • RFIs by Term
      • Actual vs Target Enrollment
  • Split the Pending category into Awaiting Decision and everything else to allow a better sense of how many can be acted on immediately vs. waiting for completion
    • This will likely alter the calculation and language for the Action column
  • Other requested demographics with filters
    • City, institution, work experience, age, gender, undergraduate GPA
  • Add red/green color coding to matriculant actuals column compared to matriculant target
  • Add ability to select the target to apply to the view
    • Currently the latest issued target is displayed, but it would be nice to be able to see how the pipeline performed against the original targets

Features Ready for Release

  • Admit, Commit, Decline, and Waitlist counts don't match with the top and bottom porting of the Enhanced report page
  • The bottom measures are correct, use the same measure on top and bottom

  • Measures added to the top, Admitted By Date Bottom, Committed By Date Bottom, Declined By Date Bottom and Waitlist By Date Bottom

Previous Cycles

Release 2024.1.0 [March 2024]

  1. Pull the data from Slate with the new columns and manually create a table. Name it APPL_SLATE_NEW.
  2. Created a view named a1_admissions_summary_dev. Removed the table APPL_SLATE and added APPL_SLATE_NEW to the a1_admissions_summary_dev view
  3. Added new fields called [Enrollment Payment Transaction Date] and [Commit (Decline) Date] in the a1_admissions_summary_dev view.
  4. Created a stored procedure and named it a2_asr_full_year_dev
  5. Changed vw_admissions_summary to a1_admissions_summary_dev in the a2_asr_full_year_dev procedure
  6. In the Admissions Summary v2023.1.7 -dev-final Power Bi report, change the data source on ALL Years query to 'execute cereports.dbo.a2_asr_full_year_dev NULL'
  7. Create a new measure as Admitted By Date Bottom, Waitlist By Date Bottom,

Release 2023.1.6 [October 2023]

  • Change the date that the report flips to viewing the Fall term for the upcoming year to start October 1st
    • E.g., currently the report turns over at the start of the new year, 1/1/2024 would be the first day that the previous year comparison is before the end of the most recent Fall term

Release 2023.1.5 [October 2023]

  • Year selectors in the default view of the Summary page do not include 2024

Release 2023.1.4a [September 2023]

  • Restore dropped Domestic filter lost in last release
  • Updated default views to Spring term
  • Updated bookmarks to ensure latest views are reflected for ASR email release

Release 2023.1.4 [August 2023]

  • Add Potential Matrics to the menu of the report
  • Add logic to the title for the Potential Matrics to change the tile if Domestic or International filtering exists
    • Add bookmarks to three views: All, International, Domestic

Release 2023.1.3c [August 2023]

  • SQL-ONLY: Remove enrolled students from the ghost indicator counts
  • More bookmark updates for Spring 2024 reporting

Release 2023.1.3b [August 2023]

  • Update bookmarks for Spring 2024 reporting

Release 2023.1.3a [July 2023]

  • KPI Tracker: widen the year selectors to keep it from scrolling
  • Minor cosmetic changes to the year selectors on the Summary page, and text callouts for fraud and potential matrics
  • SQL: Add two years to the data pulled for history (6 total)
  • Updates bookmarks to pull latest terms

Release 2023.1.2 [May 2023]

  • BUG: Fixed issue with drill-through page not showing selected comparisons years other than 2021

Release 2023.1.1 [April 2023]

  • Minor cosmetic changes to the year selectors on the Summary page
  • SQL: Add two years to the data pulled for history (6 total)

Release 2023.1.0 [February 2023]

  • Comma is missing in Awaiting Materials value (all pages)
  • Update bookmarks to remove outdated views, and to switch Spring 2023 to EOT

Release 2022.1.8 [December 2022]

  • Add a column that combines term commits + deferral commits - the total number of applications flagged as unlikely to matriculate
    • Remove the Program Code column to make room for the new value
  • Include the Ghost Application field from Slate in the daily warehouse import
  • Include a new change management page covering the modifications for the "ghost application" field
  • SQL-ONLY: Use the SIS Application_Received_Date as the default submit date instead of the Submit Date from Slate
    • The SIS date is more accurate, since that will include the date that the application fee was paid and the app sent to SIS, which can often be delayed
  • Projection Basis
    • Remove CTD/EOT visual and set underlying filter to always EOT

Release 2022.1.7b [November 2022]

  • The DeferredDuplicate records are not being suppressed in the Enhanced and Summary pages.
  • Add program description to the filter panel on the Summary page

Release 2022.1.7a [November 2022]

  • BUG: Waitlist is not being removed from the In Review data point.
  • Ensure that all submit, admit, and commit counts include deferrals ONLY in the application term

Release 2022.1.7 [November 2022]

  • Check the negative values in the Pending column in the lower matrix
  • Convert a2_atm_enrollment procedure to use universal a1_sps_enrollment view
    • New name: a2_asr_enrollment
    • Refactor to remove unused columns and simplify grouping logic
  • Convert a2_atm_deferrals procedure to use the a1_admissions_summary view
    • New name: a2_asr_deferrals
    • Refactor to remove unused columns and standardize deferrals using a single approach

Release 2022.1.6 [October 2022]

  • Split the Pending column into Awaiting Materials and In Review data points
    • Include the split in the latest term only since there is no clear date to associate with historically; keep the pending category for all previous terms to show volume comparison
  • Move to new procedure: a2_asr_full_year
    • Add CUID to the procedure output
    • Add Year column to the output
      • Simplify the __lookup_year table in Power BI to create a simple table based on the values in Year

Release 2022.1.5b [September 2022]

  • BUG: Future years were causing issues with the diff counts in the top view
  • Gracefully handle new years in the year lookup tables to eliminate manual edit requirement
  • Updated logic to look back differently depending on the time of year and terms
  • Update report for 2023
    • __lookup_year measure: Remove 2018, add 2023
    • Reset filters for Enhanced and Summary pages to Spring 2023 defaults
    • Add Spring 2023 bookmark for Summary page

Release 2022.1.4a [August 2022]

  • SQL-ONLY: Added exclusion for cases where Slate has a person-scoped Deferral tag on the profile, but the Fall 2022 application is not actually a deferred app (same application and enrollment term).

Release 2022.1.4 [May 2022]

  • Update default views to filter on Fall 2022 and Masters
  • Fraud changes
    • Added dynamic footer message to reflect whether fraud is included or not based on the state of the IsFraud filter
    • Updated all of the bookmarks to include new fraud display defaults
    • Set the default filters to remove fraud from all views
    • Create a change notice page explaining the fraud removal, and showing the impact of fraud on the pipeline
      • Bookmark the page for easy addition to the weekly send

Release 2022.1.3b [May 2022]

  • SQL-ONLY: Switch underlying query to use the shared a1_admissions_summary view
  • Fine tune the sizing to fit the summary to a single page for the print-ready version

Release 2022.1.3a [March 2022]

  • Tweak the row padding of the summary page to accommodate the new SCOM online program row in the printed Masters view

Release 2022.1.3 [March 2022]

  • SQL-ONLY: Added 'AI' (Admit to ALP) that meet the commit criteria to the Commit column counter for the current term
  • Add a pipeline fill rate visual for submits and commits to the KPI Tracker report page (hidden but bookmarked)

Release 2022.1.2 [February 2022]

  • Rename to Admissions Summary across the report views
    • Split into Enhanced and Summary report views
  • Replace the CPF degree code in the program lookup query with CPA/CPF
  • Change ASR Started column header to Started / Unsubmit
  • Move the card displaying the version of the targets information so that it does not block the export ellipsis for the top matrix
  • Removed the per row filters on the yield measures to fix issues with display in the projection basis review page
  • SQL-ONLY: Fold SPCEDR and SSHRP into the SPNDGH code for historic College Edge numbers

Release 2022.1.1 [February 2022]

  • Add a note on the main report page where the targets were sourced (e.g., Original Budget, Q1, Estimate, etc.)
  • Add a hidden page for building a print-ready version of the ASR for weekly distribution
  • SQL-ONLY: Pull matriculant data from U46 with tuition-bearing credits to align the numbers with the EvT report
    • The only concern would be if there are tuition-free programs that need to be shown in the ASR; currently there are none
  • SQL-ONLY: Fix cases where there is no decision date in the Admissions data pipeline (mostly ALP); use the SIS Manual_Decision_Date as the default
  • SQL-ONLY: Pass the Apply_Term into the Entering_Term when it is NULL

Release 2022.1.0 [January 2022]

  • Add CTD/EOT back to Basis Review page; set it to EOT by default
  • Ensure the grids are high enough in the layer hierarchy to allow the hover options button to stay visible
  • BUG: When a year other than the most current is selected, the CTD comparison date for the top section of the main report page is behind by one year from where it should be when the new year turns over (e.g., Fall 2021 should be 01/2021 in January)
  • BUG: The details sub-report page in CTD mode does not use the same comparison date as the main report page when calculating the difference YOY in the rate indicators, so the differences are not accurate

Release 2021.2.5 [December 2021]

  • BUG: The lower view is showing EOT numbers regardless of the CTD/EOT flag setting
  • Removed CTD/EOT slicer from the Basis Review page

Release 2021.2.4 [December 2021]

  • Updated the year logic to properly shift when the new year rolls over
  • Rate RFI App Starts measure used in the basis review page is broken with new data structure
  • With improved performance of the main view, update the procedure to pull the most recent five years of history
    • Ensure that the query is evergreen and automatically moves to the correct year based on the current data
  • Add a note to the previous year grid that the right side values are all EOT regardless of selection
  • Update banner elements to align with latest design brief

Release 2021.2.3 [December 2021]

  • Cosmetic updates to bring the report in line with the latest SPS design standard
  • Trimmed down the dataset footprint to reduce the overall memory requirements on the server-side
  • Removed matriculant projection details from the aggregated view to improve performance
    • Projection data can be viewed per program at the detail page level

Release 2021.2.2 [December 2021]

  • Update link to new Confluence documentation
  • Make a pass to improve performance, particularly when switching between years and CTD/EOT
    • Update any measure that is using multiple IFs to use a single SWITCH
    • SQL-ONLY: Limit the number years pulled to 2020+ in the procedure to improve performance
    • Removed CUID column to reduce database size by nearly 50%

Release 2021.2.1 [October 2021]

  • All Years
    • Add in a filter for IsFraud to view the pipeline with or without fraud apps
  • Add in the “official” estimate against the target from Finance to the program details view (when published)
    • a_u_target_atm_pbi [deprecated]
    • Requires an update to the table structure to allow for a specific version of the targets to use (e.g., Original, Q1, Q2, etc.)
    • Display the latest set of targets in the ATM, along with the version name to be clear of the source
    • Standardize on central U_TARGETS table
  • Remove dependency on the Reference table for Enrollments and remove from the data model

Release 2021.2.0 [October 2021]

  • Program Details Page
    • Submit Rate
    • Admit Rate
    • Commit Rate
    • Reject Rate
    • Fraud Rate
    • How many are coming from:
      • Commits * commit yield
      • Uncommits * uncommit yield
      • Commits * commit yield
      • Uncommits * uncommit yield
      • Domestic/International
      • Deferrals 
      • Potential commits from pipeline * commit yield
    • How many came from:
      • New: Commits / Uncommits
      • Deferrals: Commits / Uncommits
    • Target Apps = Enrollment Target / Yield Apps
    • Create a drill-through report page to replace the Action column, showing the prediction details in a more visual format
    • Show how the current term is tracking against the prediction term for the following indicators
    • Show the details behind the projected number (left side gauge)
    • Show the details behind the actual matriculant number (right side gauge)
    • Include a total application target based on historic submit rates, and current progress to goal
  • Matriculant Projection
    • Adjust the projection measure to use all relevant indicators from the funnel to predict outcomes
      • Used in the Projected result gauge
      • Used in the Actual result gauge
      • Potential Submits:
        (Total Apps * Submit Rate) - Submits
      • Potential Admits:
        ((Potential Submits + Submits) * Admit Rate) - Admits
      • Potential Commits:
        ((Potential Admits + Admits) * Commit Rate) - Commits
      • Potential Incoming:
        (Incoming Deferrals + Admits + Potential Admits) - Declines
      • Available Incoming:
        (Incoming Deferrals + Admits) - Declines
      • Domestic Rate:
        (Total Domestic Apps / Total Apps)
    • The calculation currently looks only at commits to predict the number of matriculants, which is not useful for early in the admission cycle
  • Include ‘By Date’ calcs for all of the rate measures to calculate based on the CTD for better comparisons of how the current term is performing at the specific point in time
  • Add a toggle button on the lower half to show the EOT numbers for the years being compared, instead of just the CTD numbers
    • Adjust the top half deltas to calculate based on the new selection
  • Use the comparison year (YearToView) for all predictive calculations and remove the separate slicer (YearToModel) from the UI
    • This should make it more intuitive, and also allow for comparisons beyond just the pre-defined three year period
    • Need to adjust all measures that use the YearToModel slicer
  • Shift the table structure to pull all years into a single table instead of separate tables per year to ease the transition from year to year and enable wider range of YOY comparisons
    • Show the comparison date in the view header to make it clear which date is being used for the comparison
    • This will allow the report to run without requiring modifications and redeployment with each new year
    • Add a year selector to filter the lower view based on the comparison year
    • Update all page elements and fields on the unified year view to use the new unified table
    • Update the ASR Build page to use the unified table
    • Remove the individual year pages and tables from the report
    • Update code to make the report independent of the year (e.g., all calculations on dates are -365, -730, etc.) and remove references to the actual year (e.g., use Term - Current Year, Term - Prior Year)
  • Projection Basis Review (Internal-only for now)
    • Fields in orange are end of term since there is no value until there are matriculants at the end of the cycle
    • Include new rates including admit, commit and reject/fraud rates
    • Include new yields including total apps, submit, and admit yields
    • Using the new report page, perform a deep analysis of the previous years to determine which indicators can reliably predict action
    • Use By Date fields so it can be split between CTD and EOT
    • Add projection column and a variance percentage against the actuals (when available)
  • SQL-ONLY: Ensure that the SIS decision code is included by default if one exists, otherwise resort to the Slate/Salesforce value
  • Handle “Admit to Future” cases
    • SQL-ONLY: Code mods for full year procedure (a2_atm_full_year)
    • SQL-ONLY: Calculate the commit status using deposits or program code for Admit to Future cases (a2_atm_deferrals)
    • Show everything through the admit in the applying term
    • Show commits in the enrollment term as committed deferrals
  • Action Column [DEPRECATED]
  • Include the new AD deferral status in the filtering on the Enrollments From Defer column in the previous year to get the numbers to align in terms after the change in coding deferrals
  • Add commas to all columns in the 2022 page view
  • Actual enrollments from the current and past terms do not adhere to the filtering on region
    • This is causing problems with the projection calculations when they are looking at the domestic/international yield percentages
    • The issue was that the Enrollments table was not linked to the Country lookup table in the relationship map
    • Pulling the country code from RESI instead of APPL for more consistent filtering
  • Add the projected number of matriculants instead of just the delta from the target
    • This would allow us to better see how many students will be coming when the filters are applied because they will not depend on the target, which is not divided into demographic segments

Release 2021.1.8a [September 2021]

  • Update the coloring of the subheadings for more contrast with the sliders
  • Update the ASR Build Tool bookmark to replace the old filters that were applied
  • Validate the PY calculations; there are still cases, particularly in SUMA, where they don’t seem right (look at Submit PY)
    • The issue was the DeferDuplicate was not being filtered in the calculated measure; added the filter to the overall datasets for both years in the view

Release 2021.1.8 [September 2021]

  • Change fonts to Segoe UI throughout for consistent rendering
  • Hide extraneous filters from view
  • Remove underscores in filter name labels
  • Add DeferDuplicate = 0 to the ASR build tool page for all visualizations
  • Update the table views to group into a matrix by degree code for grouping
  • Also allows the exported data to include the degree code for reporting on the dataset 

Release 2021.1.7 [August 2021]

  • Find out why some of the records in the matriculant stage have a blank value for the domestic field
    • All actual matriculants should have a a location value
    • This is a product of mismatched country codes in the lookup table
  • RFIs in the PY are not looking at the year to date, but are a total for the term
  • Add 2021 to the prediction basis selector (for use in the 2022 year)
  • Make the Domestic value TRUE/FALSE to be more intuitive in the filter
  • Changing the Domestic to TRUE/FALSE broke the Projected and Action measures, refactored to resolve the issue
  • SQL-ONLY: Remove the a7 scoped procedures from the DB server
    • All a2 procedures should now be in use
  • Hide the old single term report pages
  • Update the consolidated view to be for a specific calendar year
    • Add a new copy of the consolidated view for 2022
  • Hide any exposed filters that should not be visible
  • Update the Action column to use the Matriculant Delta measure for consistency
  • Remove the subtotal from all of the PY delta columns (not consistently accurate)
  • Remove the PY column from the previous year view to simplify the report view
    • Color code the full application funnel (similar to ASR)

Release 2021.1.6 [August 2021]

  • Add a consolidated view with a term selector to simplify the updates to the report
    • Fix issues with the data model properly filtering on deferrals and targets
  • Convert server procedure to production naming convention and document in the procedure repository (from a7 to a2)
    • Add all procedures to official active procedure list
    • Ensure latest report uses new procedures
  • New report page to show the basis values for all of the calculations that take place for projections for 2021, 2020 and 2019
    • For validation and informational purposes
    • Add all of the EOT values and percentages for every term in the underlying datasets for terms that can be used as a basis
    • Add a slicer to show the basis values for any given term (Spring, Summer, Fall)
    • Hide the report page, but create a bookmark to access
  • Strip out the subtotal values for all of the calculated percentage measures (yields, selectivity, submit rate)
  • Commit yield rates differ pretty dramatically across the domestic and international segments for many programs
    • Pull from the APPL / DEMO tables
    • Update the calculations for ΔProjected and Action measures to split rates across the two segments
    • 2019 and earlier (from Salesforce) does not have Country values
  • Filters (requires data model changes)
    • Disable all of the other filters so only the most relevant are displayed
    • Will need to add student status to the export for APPL_SLATE
      • Should pull the app-scoped status if there is an application, else pull the person-scoped status, else null (Custom SQL)
      • Update the data pipeline to load new field on CEREPORTS
    • Include in all year tables, deferrals, enrollments, and outcomes (RFIs)
    • Include in all year tables, deferrals, enrollments, and outcomes (RFIs)
    • Domestic split is simply USA and not USA
    • Country table should use three letter code, but map it to the user friendly country name for selection
    • Use the filter navigation instead of taking up report real estate
    • Part-time/full-time/non-credit
    • Country, region and domestic vs. international
  • The ALP actuals do not appear to be pulling correctly for the current term
    • Filtering on Entering_Term and ALP (among others) are not fully coded with an entering term; removed filter to get actuals
  • FIX: There is something wrong in the 2019 prediction flip calculations
  • WORKAROUND: The subtotal calculation at the bottom of the ΔProjected column is not correct
    • Issue with the way the calculated measure performs in subtotals, removing the value from the table for now and will push the bug to MS
  • Remove the calculation subtotal in the Action column since it is not something that should be rolled up

Release 2021.1.5a [July 2021]

  • SQL-ONLY: Add non-deposit cases for deposits to the a7_atm_enrollments procedure

Release 2021.1.5 [July 2021]

    • Admit/Defer Counts
      • The ASR shows raw admits, including deferrals or future term admits; ATM now replicates this behavior
    • ΔProjected and Action Calculations
      • Committed Yield %
      • Non-Committed Yield %
        • This includes only admits with the same enrollment term (current term admits) to ensure the percentage is based on actual potential yield for the specific term
      • Update all calculations to use the split yields (when applicable)
      • Create two new yield rate segments for a more refined projection
    • Add a column to show the number of DECLINED in the pipeline
      • Include in the YOY visual to see if there is an increase or decrease in declines
      • Include the declines in the calculation for the action column as they should not be part of the formula to determine potential matriculants
    • Exclude future term deferrals from the commit counts for the current term (leave as admits)
    • BUG: When switching to 2019 basis, the new and committed matriculant counts still reflect 2020; only switches the yield percentages
      • Change the Enrollments[TSTA_Term_Identifier] filter to point to use the selected term rather than the page default
    • Update the Slate export to exclude all records in the “Inactive Applications” round
    • Add a column to the atm_full_year procedure to set a flag if the record is considered a duplicate application from the old multiple application deferral method


  • Dupe applications using old method have a reply code of “D” and a Deposit code of “W”


Release 2021.1.4a [July 2021]

  • SQL-ONLY: Include IS NULL in the Deferral <> “D” exclusion so the nulls are not excluded
  • SQL-ONLY: Actual enrollment counts for AYI in the previous year for Fall are incorrect

Release 2021.1.4 [May 2021]

  • SQL-ONLY: Removed the filter to validate that a Program Code is present in the APPL_SLATE export as it was causing some applications to be excluded from the counts in Admit to ALP scenarios
  • Remove the PY delta for pending and rejected applications from the main view
    • Keep calculated measures for inclusion in a detail page (Pending Diff YYYY and Rejected Diff YYYY)
  • Fixed issue with overcounts in the Actual Enrollment column for the current year based on Entering Term and TermID mismatch
  • Create an ASR build view of the term data as another report tab and hide it by default
    • Three previous terms: Spring, Summer, Fall (one year ago)
    • Create a bookmark to open the ASR Build page content
    • Page should allow selection of the term identifier to display
    • Should be a table broken into the same categories from the ASR to make it easy to export and then copy and paste into the Excel report

Release 2021.1.3 [May 2021]

  • VALIDATE: Commits are undercounted as compared to ASR archive data
    • This was due to multiple decision dates from the previous method for coding deferrals with multiple applications.
    • Modified the Slate export to pull the FIRST decision release date to eliminate this issue.
  • Add more detail to the ACTION column for cases where the target is expected to be exceeded
    • Percentage over the target, count over the target
  • Exclude deferral applications that are being double counted from the old methodology for coding deferrals with two applications
  • Need to populate the Committed Deferrals column from for current term (currently a placeholder)
    • Create new procedure to pull deferrals for all terms (a9_atm_deferrals)
    • Add Total, Committed, and Enrolled counts from the deferrals entering in the current term
  • Show actual enrollment in the current year (where available) to make it clear where we actually landed
    • Requires a second pull of the enrollment data and a filter on the selected term

Release 2021.1.2 [April 2021]

  • Add a slicer to allow the user to choose to use either 2019 or 2020 as the basis for the current term predictions (e.g., delta projected and action)
  • Add a Submit Rate measure to show how the percentage of submitted apps to unsubmitted
    • This is a calculation of the total number of apps * the previous year’s submit rate - the total submitted apps
    • The idea is to give an indication of how many unsubmitted apps could potentially be submitted by the end of the cycle
    • Include new text in the Action column giving the predicted number of submits to expect
    • Hook the Submit Rate into the new Prediction Year selector calculations
  • Include the values used in the calculations in the Action text

Release 2021.1.1 [April 2021 *DB ONLY*]

  • Add citizenship country and status to the underlying query from Slate
    • Update the table import to include the new columns
  • Updated procedure to join APPL table on Person_ID and TermIdentifier (instead of Program_Code) to align numbers for cases where the application was for one program, but the actual final matriculation was for another program.

Release 2021.1.1 [March 2021]

  • Clean up the titles for the term sections on each report to better indicate the data
  • Add notes to the footer to explain the CTD and EOT data sections
  • Add the RFI term of intent to the Slate extract for RPT_MKT_SLATE_RESPONSES to allow filtering of RFIs by term
    • Remove old date filtering for RFIs
  • Add RFIs to previous term section for all report pages
  • Add Summer 2021 report page
  • CEREPORTS SQL procedure
    • Requires updates to any records that are not properly synced
    • SHGEN1,SHGEN2 → SHGEN1
    • SHGEN3,SHGEN7 → SHGEN3
    • SPGRAD,SSSPL → SPGRAD
    • SPGRDV,SSGSP → SPGRDV
    • SPNDGV,SSUSP → SPNNDGV
    • SPNDGH,SSHSP → SPNDGH
    • SPGFNS,SPGFND → SPGFNS
    • Change count logic to use the SIS data for more accurate representation
    • Consolidate the following program codes into a single grouping

Release 2021.1.0 [January 2021]

  • Add the RFI totals to the beginning of the report to show the top of the funnel
    • Include RFI requests by program over the last six months
  • RFIs need to roll up into the ALALP aggregate category
  • There is no way to get RFI data from history to compare the rate
  • The Pending application counts appear to be off for the previous year section of all report pages
  • Declines are not included in the Admit counts
  • The conditional formatting for the Total column in the Fall 2021 report is not correct

Release 2020.1.4 [December 2020]

  • Update all PY delta calculations to replace null values with a zero; currently when there is a null, no calculations are performed
  • Include programs with no defined targets in case there are numbers to be seen
  • Add Action text to reflect when a target is not defined
  • Do not show rows in the tables when there are no applications in the pipeline
  • [SQL ONLY] Added ‘Merit Fellowship’ admits to the admission counts for programs

Release 2020.1.3 [October 2020]

  • Factor commits into the calculation toward the target; currently only looks at admit and PY yield rate, but we should consider the commit total as a weighted indication that the student will matriculate
    • New formula to calculate the projected matriculant and action statement:
      (Total Commits + Total Deferred Commits) +
      ((Total Admits - Total Commits) * PY Yield %)
      For example, all commits are expected to matriculate, and the previous year yield rate of the uncommitted admits are also expected to matriculate.
  • Add a Committed Deferral column to the current term to show the other inputs toward the target
    • Adjust the Action calculation to add the deferrals to the admits
  • Update the APPL_SLATE table
    • Remove counter columns that are no longer in use
    • Add columns to allow for proper deferral counting using new Slate fields
  • Update the SQL procedure to use the new deferral values to pull counts
  • Add the export option to the Fall 2020 visualization in the Fall 2021 page

Release 2020.1.2 [September 2020]

  • Shade the EOT values in the previous year section to make it clear they are NOT CTD
    • Yield, selectivity, actual enrollments
  • Strip out all deferrals from the calculations in the current term
  • Fix the CPF filter not showing properly in the 2021 data
  • Fix ‘AYI - Online’ values not showing up correctly
  • Fix issue with ALP in the current term not showing
  • Add a filter to show/hide rows with zero targets defined
  • Suppress showing an action narrative when there is a zero target value in the current year data
  • Streamline the APPL_SLATE data load process
  • Look closely at the Salesforce table data for Spring and Fall 20 to ensure there are no dupes with the imported Slate apps from those terms
  • Remove any rows from APPL_EXTENDED where the application was migrated to Slate (submitted and deferrals)
  • Update the atm_targets table to include the Spring 2021 targets
    • Update the Spring 2021 targets to include new deferrals (20% projected to matriculate) and the new Q1 projection
  • Remove the aggregated totals to simplify the view
  • Add report page for Spring 2021
  • Add report page for Fall 2021
  • Remove the Fall 2020 report page to simplify interface
  • Show zero targets by default
  • Change report behavior to always show CTD information for all of the primary columns (application funnel points)
    • Final enrollments, selectivity, yield, targets
    • Removed EOY and CTD button and bookmarks since CTD is the default view
    • Show all of the EOY data points without looking at CTD

Release 2020.1.1 [July 2020]

  • Need a button to switch back to non-CTD; default view should also be set to non-CTD (currently it looks like it is CTD at startup)
  • Combine the Program Description and Code into a single measure to ensure a unique row for cases where the description is identical
  • Add a new report page to show target performance history across all programs
    • Targets
    • EOT enrollments
    • Backfill all of the historic enrollment targets into the u_targets_atm table
    • Add measure to group into user friendly terms (e.g., Spring, Summer, Fall)
    • Add measure to group by fiscal year
    • Output field values for five years

Release 2020.1.0 [July 2020]

  • Display all active programs for the past two years
    • The third year data will be used to show +/- for past year only, no detailed counts displayed to ease readability
    • Underlying recordset should contain all applications for three years * three terms
  • Pipeline status data points include
    • Pending/Under Review (catch-all for anything not covered by others)
    • Rejected
    • Withdrawn
    • Waitlist
    • Admits (including to ALP)
    • Commits (including from deferrals)
    • Unsubmitted apps
    • Submitted apps
  • Default view displays the End of Cycle counts
    • Add a bookmark to show Cycle to Date counts
  • Pull in the generic targets data rather than combining with the enrollments since they do not need to be blended; this will simplify the filtering requirements
  • Break each page of the report into a specific term for ease of reading and to simplify the design and dataset requirements; use filters to section the data for each page
  • Show the counts for the current term through the current date, and each previous term should be filtered to cap application created date for the represented year
    • 2018 dataset has a calculated column called ‘OneYearAgo 2018’ and the page is filtered to include only those records from 2018
    • 2019 dataset has a calculated column called ‘OneYearAgo 2019’ and the page is filtered to include only those records from 2019
  • Calculate the actions to be taken based on the yield rate for each program for the previous year’s term
    • Sample data question to answer:
      “If ERM is aiming for 421 new students this fall, and expects a yield of 50%, it needs to accept 842 students.  If ERM believes that 50% of its applicants are worthy of admission, it needs 1,684 applications” [from Louise]
    • Calculate the expected yield rate based on the previous year’s data (e.g., admits/matriculants) and store in a measure for use in the presentation
  • Add selectivity as a calculated measure (admits/submitted apps) to the 2019 dataset
    • Use measure to update the recommended actions for 2020 with more detailed instructions
  • Create stored procedure to pull counts from the APPL_EXTENDED table for Salesforce sourced data by calendar year
    • Combine AL* program codes into a single ALALP code
    • a7_atm_full_year (@year parameter)
  • Include a separate page for each term (Fall, Spring, Summer) and configure the filters to ensure the correct data is used in the calculations and presentation
  • Split out matriculants from deferrals and new applications, calculate yield based only on the new applicants for the term
  • Add the waitlist count to the Action calculation for projected applicants in the pipeline
  • Cap projections based on a yield maximum of 100% (no >100% yields)
    • Factor in the deferrals to get a more accurate yield percentage for normal admits to the current term
  • Blend in the Salesforce data that overlaps with Slate term data for 2020 (e.g., some SF apps for programs were running in Slate terms, particularly in non-degree)
  • Resolved: ERM - Online, HCM -- Online, among others, are missing despite having targets defined for Fall 2020
  • Display programs that are new and do not have previous term data for comparison (e.g., Wealth and Insurance Management)
    • Since there is no historical data, none of the projections can be done so maybe this report is not the best place to show these programs, but can display the current state without actions
  • Do not show rows in the current term where there is no defined target or it is set to zero
  • When there is no previous year data (e.g., for new programs) use a standard selectivity and yield percentage for the projection data (start with 50% for both values)
  • No labels