Retention

This vignette delves into the specifics of retention rate metrics, which are divided into:

1. Cohort Retention
   • Focuses on tracking a specific group of students, known as a cohort, across their educational journey.
2. Term-to-Term Retention
   • Examines the retention of students from one academic term to subsequent terms.

	Group of Students	Retention Includes	Retention Excludes
Cohort Retention	Cohort, First-time Freshman and/or Transfer Students	Returned term indicated	Exclusions
Term to Term Come Back Rate	All degree seeking students	Returned term indicated	Exclusions
Term to Term Return Rate	All degree seeking students	Returned or completed a credential by the term indicated	Exclusions

Cohort Retention

A cohort refers to a group of students who share common characteristics and are tracked for reporting purposes. In the context of University enrollment, cohorts play a significant role in understanding student demographics.

When new degree-seeking students join the University, they are assigned to a specific cohort. This assignment is based on data extracted from the census enrollment snapshot. The snapshot captures information about student demographics, enrollment status, and other relevant factors.

There are eight distinct cohorts students are categorized into based on enrollment status, degree pursuit and entry type:

• Full-time, Bachelors degree seeking, freshman
• Full-time, Bachelors degree seeking, transfer
• Part-time, Bachelors degree seeking, freshman
• Part-time, Bachelors degree seeking, transfer
• Full-time, Associates degree seeking, freshman
• Full-time, Associates degree seeking, transfer
• Part-time, Associates degree seeking, freshman
• Part-time, Associates degree seeking, transfer

Cohort retention metrics track the progress of student cohorts as they advance toward graduation.

The formula for calculating the cohort retention rate is as follows:

• Denominator: The total number of students in the cohort without an exclusion flag.
• Numerator: The number of students from the denominator who were enrolled as of the census date for the return term.

\[ \text{Retention Rate} = \frac{\text{Number of Retained Students}}{\text{Total Number of Non-Excluded Students}} \]

/*
 Cohort Retention
 Uses student term cohort table as the base table. Comprises of students in the first-time freshman cohort in the fall semester.
 Includes data to calculate Cohort Retention over time.
 */
    SELECT a.student_id,
           a.cohort_start_term_id,
           a.cohort_desc,
           a.full_time_part_time_code,
           a.cohort_degree_level_desc,
           b.term_desc,
           b.season,
           SUBSTRING(b.term_id, 1, 4) AS year,
           a.is_exclusion,
           -- Second fall return rate
           a.is_returned_next_fall,
           a.is_graduated_year_1,
           -- Third fall return rate
           a.is_returned_fall_3,
           a.is_degree_completer_2,
           -- Fourth fall return rate
           a.is_returned_fall_4,
           a.is_degree_completer_3,
           --
           c.primary_major_desc,
           c.primary_degree_id,
           c.primary_degree_desc,
           d.college_abbrv,
           d.college_desc,
           e.gender_code,
           CASE
           WHEN e.gender_code = 'M' THEN 'Male'
           WHEN e.gender_code = 'F' THEN 'Female'
           ELSE 'Unspecified'
           END AS gender_desc,
           e.ipeds_race_ethnicity,
           e.is_veteran,
           e.is_international,
           e.is_athlete,
           e.is_first_generation
     FROM export.student_term_cohort a
LEFT JOIN export.term b
       ON b.term_id = a.cohort_start_term_id
LEFT JOIN export.student_term_level_version c
       ON c.student_id = a.student_id
      AND c.term_id = a.cohort_start_term_id
      AND c.is_enrolled IS TRUE
      AND c.is_primary_level IS TRUE
      AND c.is_census_version IS TRUE
LEFT JOIN export.academic_programs d
       ON d.program_id = c.primary_program_id
LEFT JOIN export.student e
       ON e.student_id = a.student_id
    WHERE b.season = 'Fall'
      AND a.cohort_desc = 'First-Time Freshman'
      AND DATE_PART('year', NOW()) - b.academic_year_code :: INT <= 8 -- Current year plus last 8 years so that there is 5 years of data for fourth fall return rate

Term to Term Retention

Term-to-term metrics include:

• Comeback rate fall to spring
• Comeback rate fall to fall
• Return rate fall to spring
• Return rate fall to fall

Comeback rate metrics track if the degree seeking students were enrolled (retained) as of census for the return term indicated; exclusions are removed from this metric. The denominator for this metric is the number of degree seeking students who do not have an exclusion flag. The denominator for the metric is the number of students from the numerator who were enrolled as of census for the return term indicated.

Return rate metrics track if the degree seeking students were enrolled (retained) as of census or completed a credential by the return term indicated; exclusions are removed from this metric. The denominator for this metric is the number of degree seeking students who do not have an exclusion flag. The numerator for the metric is the number of students from the denominator who were enrolled as of census or completed a credential by the return term indicated.

/*
 Term to term retention
 Uses student term outcome as the base table. Includes students enrolled as of census.
 Includes data to calculate Term to Term Retention (both Come Back rate and Return rate).
 */
   SELECT a.student_id,
          a.term_id,
          b.term_desc,
          b.season,
          SUBSTRING(a.term_id, 1, 4) AS year,
          a.is_returned_next_spring,
          a.is_returned_next_fall,
          a.is_graduated
            OR a.is_degree_completer_certificate
            OR a.is_degree_completer_associates
            OR a.is_degree_completer_bachelors
            OR a.is_degree_completer_masters
            OR a.is_degree_completer_doctorate AS is_credential_completer,
          c.primary_major_desc,
          c.primary_degree_id,
          c.primary_degree_desc,
          d.college_abbrv,
          d.college_desc,
          a.is_degree_seeking,
          c.level_desc,
          c.student_type_desc,
          c.full_time_part_time_code,
          e.gender_code,
          CASE
           WHEN e.gender_code = 'M' THEN 'Male'
           WHEN e.gender_code = 'F' THEN 'Female'
           ELSE 'Unspecified'
           END AS gender_desc,
          e.ipeds_race_ethnicity,
          e.is_veteran,
          e.is_international,
          e.is_athlete,
          e.is_first_generation,
          COALESCE(f.is_exclusion, FALSE) AS is_exclusion,
          COALESCE(f.cohort_start_term_id, 'None') AS cohort_start_term_id,
          COALESCE(f.cohort_desc, 'None') AS cohort_desc
     FROM export.student_term_outcome a
LEFT JOIN export.term b
       ON b.term_id = a.term_id
LEFT JOIN export.student_term_level_version c
       ON c.student_id = a.student_id
      AND c.term_id = a.term_id
      AND c.is_enrolled IS TRUE
      AND c.is_primary_level IS TRUE
      AND c.is_census_version IS TRUE
LEFT JOIN export.academic_programs d
       ON d.program_id = c.primary_program_id
LEFT JOIN export.student e
       ON e.student_id = a.student_id
LEFT JOIN export.student_term_cohort f
       ON f.student_id = a.student_id
      AND f.cohort_desc IN ('First-Time Freshman', 'Transfer')
    WHERE a.is_enrolled_census IS TRUE
      AND b.season = 'Fall'
      AND DATE_PART('year', NOW()) - b.academic_year_code :: INT <= 5 -- Current year plus last 5 years