Puget Sound: custom modeling

Rigor Comp calculation 
Edit section

 

The “rigor comp” formula is this: (Rigor Comp Score on a 200-3200 scale) = (High Score on a 0-1600 scale) + (GPA on a 0-800 scale) + (Rigor Code Score on a 200-800 scale).

The Rigor Comp Score is created as a score record with the test name of “Rigor Comp”, a test date of the data on which it was calculated, and a source of "Analytics".

The High Score for Puget Sound is based on the SAT math+verbal score, so it is on the correct scale.

The GPA for Puget Sound is on a 0-4 scale, so it will be multiplied by 200 to reach the correct scale. Only the GPA of the active organization will be used, and it will only be used if the GPA is not self-reported.

The Rigor Code Score is entered by the client as a score record with the test name of “Rigor Code” and has this translation, which is stored in the PugetSound_Analytics.RigorCode table:

8800
7760
6720
5640
4520
3400
2240
1200

 Flow
Edit section

 

  • A user at Puget Sound creates in the database a student record with the necessary data for the rigor comp calculation.Overnight, a rigor comp score is created for that student.
  • Or, a user at Puget Sound creates in the database a student record without the necessary data for the rigor comp calculation.Overnight, a rigor comp score is not created for that student.
  • Or, a user a Puget Sound updates a student record which has a rigor comp score so that some or all of the data which is used in the calculation is changed.Overnight, an updated rigor comp score with the newly calculated score replaces the previous rigor comp score for that student.
  • Or, a user a Puget Sound updates a student record which has a rigor comp score so that none of the student’s calculation data is changed.Overnight, no change is made to the student’s rigor comp score.
  • Or, a user a Puget Sound updates a student record which has a rigor comp score so that some of the data which is used in the calculation is no longer available.Overnight, no change is made to the student’s rigor comp score.

Pre-conditions
Edit section

 

  • The student’s opportunity record must be open and of type “Freshman”.
  • The student must have an active high school organization with a non-self-reported GPA.
  • The student must have a high score.
  • The student must have a rigor code score.

 

Post-conditions
Edit section

The student will have a rigor comp score with a source of “Analytics”, a test date which is the day on which the data used for calculation last changed to a valid configuration, and a score calculated from the latest data.If the student has never had valid data, no rigor comp score is present.

Technical details
Edit section

The Rigor Comp calculation is defined in the database PugetSound_Analytics as the view vw_Model_RigorCompScore.

 

 

  1. The required data for the calculation along with any additional data required for the creation of a valid import record is selected from the warehouse database for Puget Sound.
    1. Required data: High Score (from opportunity/person data), non-self-reported GPA for active organization (from personorg data), Score for the “Rigor Code” test (from score data).
      1. The “Rigor Code” test is hardcoded as the GUID {51114465-FE51-DF11-BD47-0015C5F1F23D}.
    2. Additional data: Organization Name (hardcoded to “PugetSound”), First Name, Last Name, and Person ID (from opportunity/person).
    3. Data will only be selected for opportunities with a state code of 0 (open) and a type of “Freshman”.
  2. Scores metadata will be selected from the score analytic modeling views (which point to the CRM database) for Puget Sound.
    1. Metadata selected is the test name for the “Rigor Comp Score” test and the source guid for the “Analytics” source.
      1. The “Rigor Comp” test is hardcoded as the GUID {A554D6E0-2852-DF11-BD47-0015C5F1F23D}.
      2. The “Analytics” source is hardcoded as the name “Analytics”.
  3. The conversion for the “Rigor Code” score will be selected from a lookup table in Puget Sound’s analytics database.
  4. Comparison data for the rigor comp formula’s value will be selected from the warehouse database for Puget Sound.
    1. The score from the”Rigor Comp Score” score record will be compared to the results of the rigor comp formula to determine whether the results of the formula are new.
      1. The “Rigor Comp” test is hardcoded as the GUID {A554D6E0-2852-DF11-BD47-0015C5F1F23D}.

 

 

See Puget Sound's Client Services folder for further documentation of the Rigor Comp modeling.

 

Custom job
Edit section

The data is placed in Puget Sound's AnalyticsScores table for import into CRM.

See Modeling & Analytics for scores import information.

 

Obsolete: Custom job and package 
Edit section

This method of custom modeling is not in use

The Puget Sound extract calls a custom job on the report server named "PugetSound RigorComp create import file", which executes a custom SSIS package named "C:\SSISPackages\PugetSound_RigorComp.dtsx". This job runs only when called by the extract and does not run on an independent schedule, although it may be run on demand as often as needed.

When run, this job exports records from the PugetSound_Analytics.vw_Model_RigorComp view on the report server (filtered on IsNew=1) into a CSV file which will be used as an import file containing records with the Rigor Comp values as scores.  The import file is placed in the \\422BIZ001\ClientDataServices\DataImports\Analytics Scores folder and named AnalyticsScores_PugetSound_RigorComp.csv.  An existing file of this name will be deleted and replaced with the new one; if the import has failed to run then the next modeling results will contain the unimported results from the deleted file, since the modeling results include all Rigor Comp scores which do no match existing scores.

See Modeling & Analytics for scores import information.


Comments