Table: fct_prospective_enrollment - Database Schema Documentation

Description

This fact table enables counting prospective enrollment opportunities (application_schedule_id), applications (application_id), and unique students (student_key). When this table is paired with fct_prospective_enrollment_stage it allows these same counts at that have completed various stages. Note the grain of this fact table can vary. The most common and detailed grain is at at the application schedule level (prospective enrollment) consisting of application + student + school + class. However, for application flows that allow application without assigning students to classes, the grain can be at a higher level consisting of application + student + school. This fact table, coupled with accompanying child fact table & dimension tables, can help users to understand questions such as: How many prospective enrollment opportunities are in various stages across time? How much in registration fees have been collected? it enables counting the various stages historically (stages may include 'Early Registration', 'In Progress', 'Applied', 'Approval Pending', 'Waitlisted', 'Completed', and 'Lost').

Columns

Column Name	Description
`business_id`	Unique identifier for the business
`early_registration_date`	The early registration date; only available for Early Bird enrollments or V1 enrollments mapped to Early Bird. For Early Bird enrollments, this is the inquiry date. For V1 enrollments mapped to Early Bird, it refers to the inquiry date of the associated Early Bird registration.
`in_progress_date`	The in progress date; only available for Program flow enrollments and not from Early Bird (EB) enrollments
`applied_date`	The applied date; for Early Bird (EB) enrollments or IOE flow enrollments, it is the inquiry date. For program flow enrollments, it is either the approval pending date or the waitlisted date, depending on whether the enrollment was waitlisted.
`offered_date`	The offered date; only available for IOE flow enrollments.
`waitlisted_date`	The waitlisted date; for program flow enrollments, this is the waitlisted date. For IOE flow enrollments, it is the inquiry date.
`approval_pending_date`	The approval pending date; only available for Program flow enrollments.
`completed_date`	The completed date.
`lost_date`	The lost date.
`lastest_stage_date`	The date of the most recent stage (e.g., waitlisted, completed, lost, etc.)
`latest_stage`	The latest recorded stage, indicating the current status of the prospective enrollment.
`flow_type`	The flow type used for registration; enumerate: 'IOE' (Inquire > Offer > Enrolment), 'IPEA' (Inquiry > Program > Enrolment > Approval), 'PFAA' (Program Inquiry > Form > Accept & Pay > Approve). For Early Bird enrollment this field is NULL.
`school_tag_school_id`	Unique identifier for a school within the tag associated with the prospective enrollment; this id is useful for connecting with school tags (with Early Bird registration the enrolling school may not be known)
`registration_type`	A designation of whether the registration was initiated in the Early Bird feature; enumerated: 'Standard' or 'Early Registration'
`registration_group_key`	Unique identifier for the registration group (connects to dim_registration_group); registration_group_type dictate the following key to id mappings: 'Mapped Program Group' - program_group_id, 'Mapped Registration Link' - registration_id, 'Unmapped Program Group' - program_group_id, or 'No Program' - {business_id}_{Age of student at enrollment_start_date}
`registration_term_key`	Unique identifier for the registration term; registration_term_type dictate the following key to id mappings: 'Mapped Program Term' - program_group_id, 'Mapped Registration Link' - registration_id, 'Unmapped Program Group' - {business_id}{YEAR(program.start_date)}, or 'No Program' - {business_id}
`prospective_enrollment_id`	Unique identifier associated with the prospective enrollment; this is useful for counting the prospective enrollment opportunities. The most common grain is application + student + school + class. However there are other grains when students are yet to be assigned to a class.
`application_id`	Unique identifier for the application (applications may contain multiple students and programs); this is useful for counting unique applications
`student_key`	Unique natural key for students (first name + last name + date of birth); this is useful for counting unique students
`family_status`	The designation of whether the family has had a prior enrollment with the business; enumerated: 'New Family', 'Existing Family', or 'New Center For Existing Family'
`enrollment_start_date`	The requested start date for the prospective enrollment
`enrollment_end_date`	The requested end date for the prospective enrollment
`application_student_key`	Identifier for the student; this is not unique because the same student may actually have multiple different identifiers
`customer_id`	Unique identifier for the customer; connects to the dim_customer table
`school_id`	Unique identifier for the school associated with the prospective enrollment; connects to the dim_school table
`class_id`	Unique identifier for the class associated with the prospective enrollment; connects to the dim_class table
`program_id`	Unique identifier for the program associated with the prospective enrollment (program = single class per school across the business)
`program_group_id`	Unique identifier for the program group associated with the prospective enrollment (a way of grouping programs to associate with a single class fee)
`class_fee_id`	Unique identifier for the original fee (the standard fee of the class); connects to the dim_fee table
`class`	The class associated with the prospective enrollment
`customer`	The customer associated with the application
`primary_contacts`	The primary contact(s) associated with the application
`student_full_name`	The full name of the student associated with the application
`claimed_subsidy`	Did the applicant claim subsidy; enumerated: 'Yes' or 'No'