Admissions and Recruiting Data Mart

With the Admissions and Recruiting Data Mart you can analyze the recruiting and admissions lifecycle. Applicant rates can be compared year over year by academic program to offer insight on the conversion percentage of applicants to students and the programs in which they are enrolled. External GPA and class percentile ranking can be tracked by institution and degree program to measure applicant academic excellence. Application evaluation and student responses can be tracked in detail to obtain insight into the admission process and cycle. The Admissions and Recruiting Data Mart provides a complete Admissions Funnel subject area that enables you to follow prospects and students through the admissions life cycle, analyzing their respective yields.

With the Admissions and Recruiting Data Mart you can answer questions such as:

How many prospects are there for a particular academic program?
How many applicants are admitted and enrolled in classes for a particular academic program in a particular admit term?

Admissions and Recruiting Data Mart Delivered Fact Tables

The following table describes the delivered Admissions and Recruiting Data Mart fact tables.

Note: In the table, the Helps Answer column includes examples of the type of answers a fact table can provide; it does not contain the complete list of answers.

Fact Name	Fact Record Name	PeopleSoft Source	Description	Helps Answer
Student Admission Application	F_ADM_APPL	PS_ADM_APPL_SBPLAN PS_ADM_APPL_PLAN PS_ADM_APPL_DATA PS_ADM_APP_PROG	Contains current information of student applications (based on the warehouse load date) and provides information on applicants and their applications, and associated academic institutions, academic careers, programs, plans and subplans. There is one row of data per applicant, Institution, Career, career number , application, program, plan and subplan.	Current pool of applicants (specific admit term) by ethnicity, gender, applicant type, academic level. Applications by program action and program action reason (when populated) to analyze for example students that rejected applications and reasons, as well as withdrew students and other program actions. Number of waitlisted students.
Application Evaluation	F_ADM_APPL_EVAL	PS_ADM_APPL_DATA PS_ADM_APP_CAR_SEQ PS_ADM_APPL_PROG PS_ADM_APPL_EVAL PS_ADM_APPL_CMP	Contains information about the application evaluation process, which are used to evaluate applicants on specific criteria for the academic career and program to which they are applying. This fact table provides insight into evaluation information, ratings, committees and additional information related to the application evaluation. CAREER_SID provides institution and career information and RATING_CMP_SID provides rating scheme and rating comp info. Institution and rating scheme are indirectly part of the key since they are derived from academic career and rating component respectively. There is one row of data per applicant, academic career, rating component, student career number, applicant number, application program number, application evaluation number and evaluation code.	Number of application evaluations by status (committee, final, on-hold statuses) Number of evaluations are still in progress by Institution, academic career and program. Ranking of application centers by number of application evaluations in progress. Analysis of application evaluations overall rating by Institution, academic career, program and admit type. Report number of application evaluations for a given rating component above a certain rating value.
Student Admission Applications Status	F_ADM_APPL_STAT	PS_ADM_APPL_DATA PS_ADM_APPL_PLAN PS_ADM_APPL_PROG PS_ADM_APPL_SBPLAN	Contains application historical data and status changes, and provides information on applicants and their applications, and associated academic institutions, academic careers, programs, plans and subplans. There is one row of data per applicant, institution, career, career number, application, program, plan, subplan, action date and effective sequence.	Number of students went through a specific status during the admission process for a specific career, institution, and so forth. Number of students waitlisted during the admissions process. Number of changes in status did an application have.
Admissions Funnel	F_ADM_FUNNEL	PS_ADM_PRSPCT_CAR PS_ADM_PRSPCT_PROG PS_ADM_PRSPCT_PLAN PS_ADM_APPL_DATA	Contains information on number of prospects, applicants, admitted, confirmed and enrolled students. This table enables funnel report analysis by applicant type, academic level, academic load, last school attended, as well as by term, institution, career, program, campus, and so forth. The admit term is used as the date of capture for the snapshot data. There is one row of data per person, academic career, institution, admit term, academic program and academic plan.	Number of prospects, applicants, admitted, confirmed and enrolled students by admit term, institution, campus, career, and so forth. Funnel information by ethnicity, gender, academic level, academic type, and so forth. Yield trends by academic year, admit term, and so forth.
Recruitment Funnel	F_CRM_FUNNEL	From PeopleSoft CRM: PS_RD_CAREER PS_RD_ PROGRAM PS_RD_PLAN From PeopleSoft Campus Solutions: PS_ADM_ APPL_DATA PS_ADM_ APPL_PROG PS_ADM_ APPL_PLAN	Contains information on number of prospects, leads, opportunities, applicants, admitted, confirmed and enrolled students for the student recruitment business process. This funnel fact is used if you do not license the CRM Sales product. This fact table is an accumulating snapshot which means existing rows for a student are updated whenever there is activity in the source systems. Note: Admit term is used as a lookup in the job that loads this fact table. Without this value reports using this table will be incorrect.	Monitor recruiting stages and statuses for constituents. Analyze recruiting campaign effectiveness and its return on investment. Analyze retention campaign effectiveness and its return on investment. Monitor the current state of recruitment and recruiting performance for individual recruiters and the recruiting team.
Recruitment Funnel (Sales)	F_CRM_FUNNEL_S	From PeopleSoft CRM: PS_RD_CAREER PS_RD_PROGRAM PS_RD_PLAN PS_RSF_LEAD PS_RSF_OPPORTUNITY From PeopleSoft Campus Solutions: PS_ADM_ APPL_DATA PS_ADM_ APPL_PROG PS_ADM_ APPL_PLAN	Contains information on number of prospects, leads, opportunities, applicants, admitted, confirmed and enrolled students for the student recruitment business process. This funnel fact is used if you license the CRM Sales product. This fact table is an accumulating snapshot which means existing rows for a student are updated whenever there is activity in the source systems. Note: Admit term is used as a lookup in the job that loads this fact table. Without this value reports using this table will be incorrect. See PeopleSoft Customer Relationship Management Sales	Analyze recruiter performance, and sales team (recruiter team) performance.
External Academic Summary	F_EXT_ACAD_SUMM	PS_EXT_ACAD_SUM	Contains information on the external academic information of prospects and applicants, and an external academic summary entry of a prospect/applicant for each particular external academic career at a particular external organization, derived with a particular summary type. There is one row of data per person, external organization, external academic career, external data number (sequence number) and external summary type.	Average GPA of the prospect/applicant in a particular external organization. Number of course units the prospect/applicant attempted in a particular external organization. Number of course units the prospect/applicant completed in a particular external organization. Converted GPA of the prospect/applicant.
External Test Scores	F_EXT_TESTSCORE	PS_STDNT_TEST_COMP	Contains information on all internal and external test scores of prospects and applicants, and contains every test score entry of a prospect/applicant for a particular test and the related components taken on a particular date, obtained from a particular data source. Test ID is indirectly part of the key since it is derived from test component. EXT_TST_CMPNT_SID performs a lookup to D_EXT_TST_CMPNT by Test ID and Test Component. There is one row of data per person, test component, test date and test data source. To link test score information to admissions applications you need to use this fact table in conjunction with another bridge table provided in the warehouse (R_TESTSCORE_APPL).	List of students with ACT/math rating greater than average. Min, Max, Average test value by test component, test and percentile. Number of students with self reported test scores. Number of students that have taken specific test component/test more than once.
Marketing Campaign Detail	F_MKT_CMPGN_D	From PeopleSoft CRM: PS_RA_ CMPGN_WAVE PS_RA_LIST_ REC PS_RA_PROGRAM_TBL PS_RAE_ EVENT PS_RY_ DIALOG_HE	Captures campaign costs, audience (target student), and their recruitment status information for each campaign activity, dialog, and event. This fact table tracks recruitment status for each campaign audience member and is an accumulating snapshot, which means existing rows for a student are updated whenever there is activity in the source systems.	Number of applicants. Number of admits. Number of confirmed students. Number of enrolled students.
Marketing Campaign Summary	F_MKT_CMPGN_S	F_MKT_ CMPGN_D	Captures rolled up campaign costs, audience (target student), and their recruitment status information for each campaign activity, dialog, and event. Data in this summary fact table is sourced from theMarketing Campaign Detail (F_MKT_CMPGN_D) Campus SolutionsWarehouse fact table.	Number of applicants. Number of admits. Number of confirmed students. Number of enrolled students.
Student Response	F_STDNT_RESP	PS_STDNT_RESPONSE PS_ADM_APPL_DATA PS_ADM_APP_CAR_SEQ PS_ADM_APPL_PROG	Contains information about student responses to your institution and enables you to analyze, for example, why students chose not attend your institution and where they attended instead. You can also analyze why students did select your school. There is one row of data per applicant, career, academic career number, admission application number, academic program, effective date and effective sequence. Institution is indirectly part of they key since it is derived from academic career.	Number of students offered acceptance that have not responded. Reasons why students declined admission to your institution. Number of negative/positive student responses by reason and by admit term, institution, program, and admit type.
Student Recruiting	F_STU_RECRT	PS_ADM_PRSPCT_CAR PS_PRSPCT_RECRTER PS_PRSPCT_RCR_CAT PS_ADM_PRSPCT_PLAN PS_ADM_PRSPCT_PROG PS_ADM_PRSPCT_SBPL	Contains data on prospective students, associated academic institutions, academic careers, academic programs, plans and subplans. This fact also contains data on the recruiting categories, regions and the recruiters the prospective students are assigned to. There is one row of data per prospect, institution, academic career, academic program, plan, subplan, recruiter category and recruiter. The measures provided in this table enable you to analyze the number of prospects by recruiter, recruiter category, referral source, and so forth.	Number of prospects by recruiter, recruiter category, referral source (and so forth) by institution, campus, program, plan for a specific admit term. Top high schools from where students originate.

Admissions and Recruiting Data Mart Delivered Dimension Tables

The following table describes the delivered Admissions and Recruiting Data Mart dimension tables.

Dimension Name	Dimension Record Name	Description
Academic Rank Type	D_ACAD_RANK_TYP	Defines various rank types.
Academic Unit Type	D_ACAD_UNIT_TYP	Defines various academic unit types.
Application Center	D_APPL_CNTR	Defines centers where admissions applications are processed.
Application Method	D_APPL_MTHD	Indicates how or in what form an application was received.
Student Ethnicity	D_ETHNICTY	Stores information about the ethnic group to which a student belongs. This dimension is snowflaked to the Person (D_PERSON) dimension.
Evaluation Code	D_EVAL_CODE	Defines evaluation codes for admission applications.
Evaluation Status	D_EVAL_STATUS	Defines the status of admission application evaluations.
External Academic Career	D_EXT_ACAD_CAR	Defines academic careers from external organizations.
External Academic Level	D_EXT_ACAD_LVL	Defines academic levels from external organizations.
External Academic Summary Type	D_EXT_SUMM_TYP	Defines the types of academic summary data from external organizations that is captured for prospects and applicants.
External Term	D_EXT_TERM	Defines the terms from external organizations.
External Test Component	D_EXT_TST_CMPNT	Defines the components of a test, such as a quantitative or verbal election.
GPA Type	D_GPA_TYPE	Defines the different grade point average types.
Marketing Event	D_MKT_EVENT	Stores marketing event information, such as event status, market, and objective. Sourced from the following PeopleSoft CRM tables: PS_RA_CAMPAIGN PS_RA_CONTENT PS_RA_LIST PS_RA_UD_CMPGN_OBJ PS_RAE_EVENT PS_RY_DIALOG
Marketing Program	D_MKT_PROG	Stores marketing program information, such as program type, program status, and event name. Sourced from the following PeopleSoft CRM tables: PS_RA_PROGRAM_TBL PS_RAE_EVENT PS_S_PSXLATITEM PS_RY_DIALOG
Rating Component	D_RATING_CMP	Defines the rating components for application evaluations.
Rating Scheme	D_RATING_SCH	Defines the rating schemes for application evaluations.
Recruiting Application Center	D_REC_APPL_CNTR	Stores information about the recruiting application center associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiting Center	D_RECRT_CNTR	Contains a list of recruiting centers which identify the prospects and recruiters who belong to a particular recruiting office.
Recruitment Category	D_RECRT_CTGRY	Defines special categories of prospective students to be targeted for recruiting and admissions purposes.
Recruiting Status	D_RECRT_STAT	Indicates the level of interest, at the academic career level, that the institution has in the prospective student.
Recruiter	D_RECRTR	Stores recruiter information, such as recruiter name, ID, type, and institution.
Recruiter Category	D_RECRTR_CAT	Stores information about the category associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiting Center	D_RECRTR_CNTR	Stores information about the recruiting center associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiter External Org	D_RECRTR_EXT	Stores information about the external organization associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiter Academic Plan	D_RECRTR_PLAN	Stores information about the academic plan associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiter Academic Program	D_RECRTR_PROG	Stores information about the academic program associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiter Region	D_RECRTR_REG	Stores information about the region associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Recruiter Role	D_RECRTR_ROLE	Stores information about the role associated with the recruiter. This dimension is snowflaked to the Recruiter (D_RECRTR) dimension.
Student Response	D_RESP_RSN	Stores information about the detailed reasons given with a student's responses for an admission application.
Referral Source	D_RFRL_SRC	Tracks how prospects or applicants learned of the institution and indicates why they were originally added to the system.
Sales (Recruiting) Team	D_SALES_TEAM	Stores sales team information, such as team ID, status, and email address. Sourced from the following PeopleSoft CRM tables: PS_RSF_TEAM PS_S_PSXLATITEM
Test Data Source	D_TST_DATA_SRC	Indicates how the test scores were provided to the institution.
Admissions Recruiter (Relationship Table)	R_ADM_RECRTR	Links the one to many relationships between the Student Admission Application fact (F_ADM_APPL) and the Recruiter dimension (D_RECRTR).
External Degree (Relationship Table)	R_EXT_DEG	Lists the degrees a prospect or applicant has received from external organizations.
Prospect Recruiter (Relationship Table)	R_PRSPCT_RECRTR	Links the one to many relationships between the Student Recruiting fact (F_STU_RECRT) and the Recruiter dimension (D_RECRTR).
Relate Sales (Recruiting) Team (Relationship Table)	R_SALES_TEAM	Links the Sales (Recruiting) Representative (D_SALES_REP) dimension to the Sales (Recruiting) Team (D_SALES_TEAM) dimension.
Test Score Application (Relationship Table)	R_TESTSCORE_APPL	Links the one to many relationships between the External Test Scores fact (F_EXT_TESTSCORE) and the Student Admission Application fact (F_ADM_APPL).