Export Files
For the ALR Export files, the process will be a repetitive task. After you create your initial segment, you will head to Data + Automations>Import/Export>Export and start creating tasks for the files listed below. Each file below details how the export should be set up and details on the columns in each file. Some files require additional building as each school varies on how they name values, like terms, and may use different fields, these are noted within the Additional Instructions columns below.
Create EAB ALR Segments#
All exports will pull from the same group of students so creating a segment is the first step. Depending on the EAB contract, student data may need to go back X amount of years and only focus on specific student types, like first year or graduate. Element451’s recommendation on building a segment is using the filters “Active Term” and “Active Student Type” with operator “Not In” to remove the older terms or irrelevant student types. It is also recommended to remove test records, if they were labelled “Test Record”, adding a “Labels” filter with “Not In” operator will remove them.
You will want a generic EAB ALR segment that covers the audience above, and then you will create to additional segments that will be used in the Program files. For your additional segments, you’ll use the EAB ALR segment and add on to it “Milestone (All Properties)” has “Application Start” for the terms selected, this will be your EAB ALR Applicant segment. And for your EAB ALR Inquiry segment, you’ll take the “Milestone (All Properties)” and flip the “HAS” to “DOES NOT HAVE”, which will remove records with an Application milestone.
Student File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Student Layout
Filename: student_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| sis_student_id | user-identities-schoolid | 2 | |
| crm_student_id | user-elementid | 3 | |
| other_student_id | 4 | ||
| fname | user-first-name | 5 | |
| mname | user-middle-name | 6 | |
| lname | user-last-name | 7 | |
| preferred_fname | user-preferred-name | 8 | |
| us_citizenship_status | Map user-citizenship-us-status, click the blue gear, set to name or label, and check enabled. | 9 | |
| international_ind | If a True/False field of international students exist, map it here. If the field is Yes/No, map the field, click the blue gear, and add replace transformations to replace with TRUE or FALSE. If the field is not a Yes/No, and is dependent on another field, a calculated field may need to be created and put in the place as the unmapped field. Make sure export header is the same. Example: International is dependent on a specific application getting filled out. IF([user-calculated-last_submitted_app_guid]="insert_international_app_guid","TRUE","") | 10 | |
| birth_dt | user-dob | 11 | |
| gender | 12 | ||
| pref_gender | Map user-gender, click blue gear, set to name or label, check enabled. | 13 | |
| religious_affiliation | If you are collecting this data, map user-religion-name. Make sure blue gear settings is exporting the name if using this field. | 14 | |
| hs_grad_year | user-education-highschools-graduate-date | 15 | |
| origin_source | This field is already set up to pull the user’s first source. No additional steps required. | 16 | |
| origin_source_dt | user-sources-timestamp-* | 17 | |
| first_gen_ind | If a True/False field of first generation exists, map it here. If the field is Yes/No, map the field, click the blue gear, and add replace transformations to replace with TRUE or FALSE. If the field is not a Yes/No, and is dependent on another field, a calculated field may need to be created and put in the place as the unmapped field. Make sure export header is the same. | 18 | |
| military_service_status | If a field for military service status exists, map it here. | 19 | |
| alumni_relation_ind | If a field for alumni relationship exists, map it here. | 20 | |
| hispanic_ind | user-race-hispanic | 21 | |
| ethnicity | 22 | ||
| campus_visit_cnt | 23 | ||
| campus_visit_ind | 24 | ||
| first_campus_visit_dt | 25 | ||
| last_campus_visit_dt | 26 | ||
| mail_optout_ind | 27 | ||
| email_optout_ind | This field is already set up to pull from the email unsubscribe milestone. No additional steps required. | 28 | |
| phone_optout_ind | 29 | ||
| sms_optin_ind | 30 | ||
| sms_optout_ind | This field is already set up to pull from the SMS unsubscribe milestone. No additional steps required. | 31 | |
| opt_out_ind | 32 |
Mailing Address File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Mailing Address Layout
Filename: student_mailing_address_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_address/[date:now,format=Y-m-d
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| addr1 | user-addresses-mailing-street1 | 2 | |
| addr2 | user-addresses-mailing-street2 | 3 | |
| city | user-addresses-mailing-city | 4 | |
| state | This calculated field will send the US state if it exists or sends province if it exists. No additional actions are needed. | 5 | |
| county | user-addresses-mailing-county | 6 | |
| zip | user-addresses-mailing-zip | 7 | |
| country | user-addresses-mailing-country | 8 | |
| address_type | This calculated field is to indicate this is a mailing address. No additional actions are needed. | 9 |
Home Address File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Home Address Layout
Filename: student_home_address_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_address/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| county | user-addresses-home-county | 6 | |
| addr2 | user-addresses-home-street2 | 3 | |
| addr1 | user-addresses-home-street1 | 2 | |
| country | user-addresses-home-country | 8 | |
| primary_student_id | user-elementid | 1 | |
| address_type | This calculated field is to indicate this is a home address. No additional actions are needed. | 9 | |
| zip | user-addresses-home-zip | 7 | |
| city | user-addresses-home-city | 4 | |
| state | This calculated field will send the US state if it exists or sends province if it exists. No additional actions are needed. | 5 |
Personal Email File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Personal Email Layout
Filename: student_personal_email_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_email/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| user-email-address | 2 | ||
| email_type | This calculated field is to indicate this is a personal email address. No additional actions are needed. | 3 |
School Email File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR School Email Layout
Filename: student_school_email_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_email/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| user-identities-school-email | 2 | ||
| email_type | This calculated field is to indicate this is a school email address. No additional actions are needed. | 3 |
Home Phone File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Home Number Layout
Filename: student_home_phone_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_phone/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| phone_number | user-phone-home-number | 2 | |
| phone_number_type | This calculated field is to indicate this is a home number. No additional actions are needed. | 3 |
Cell Phone File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Cell Number Layout
Filename: student_cell_phone_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_phone/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| phone_number_type | This calculated field is to indicate this is a cell number. No additional actions are needed. | 3 | |
| phone_number | user-phone-cell-number | 2 | |
| primary_student_id | user-elementid | 1 |
Prior College File
Segment: EAB ALR
Repeat Rows Based On: Education
Template: EAB ALR Prior College Layout
Filename: student_prior_college_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_prior_college/[date:now,format=Y-m-d]
| Name | Element Slug | Row |
|---|---|---|
| primary_student_id | user-elementid | 1 |
| college_ceeb_cd | user-education-college-ceeb | 2 |
| college_name | user-education-college-name | 3 |
| college_city | user-education-college-city | 4 |
| college_state | user-education-college-state | 5 |
| college_gpa_nbr | user-education-college-gpa | 6 |
| college_transfer_credits | user-education-college-total-credits-earned | 7 |
Applicant Program File
Segment: EAB ALR Applicants
Repeat Rows Based On: Applications
Template: EAB ALR Program Layout
Filename: student_term_[date:now,format=Ymd]_applicant
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_program/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| curr_entry_term | EAB is expecting this field to be Fall, Spring, Summer, or Winter. Depending on the naming convention of the terms will determine this formula. Example when terms are named like Fall XXXX: SPLIT_INDEX(DB_MAP("term",[user-applications-term],"guid","name","")," ",1) Example when terms are named like Fall/August XXXX: SPLIT_INDEX(DB_MAP("term",[user-applications-term],"guid","name",""),"/",1) | 5 | |
| program_recruitment_status | 8 | ||
| curr_entry_year_academic | EAB is expecting this field to be year portion of the term. Depending on the naming convention of the terms will determine this formula. Example when terms are named like Fall XXXX: SPLIT_INDEX(DB_MAP("term",[user-applications-term],"guid","name","")," ",2) | 6 | |
| state_resident | Formula to calculated if a student is in state or out of state. It will check if the home address is the state code you insert and return TRUE or FALSE. Please insert your alpha 2 state code in the formula OR replace the formula with your resident custom field if you have one. IF([user-addresses-home-state]="<insert alpha2 code>","TRUE", IF([user-addresses-home-state]<>"<insert alpha2 code>","FALSE","")) | 11 | |
| inquiry_dt | user-milestones-prospect-date | 4 | |
| primary_student_id | user-elementid | 1 | |
| program | user-applications-major | 2 | |
| student_type | Map to user-applications-student-type and make sure it is being exported as name. | 7 | |
| degree_type | user-applications-degree | 10 | |
| campus | user-applications-campus | 9 | |
| inquiry_ind | This calculated field is to indicate if the student has a Date of Inquiry milestone on the profile. No additional action required. | 3 |
Inquiry Program File
Segment: EAB ALR Inquiries
Repeat Rows Based On: User Profile
Template: EAB ALR Program Layout
Filename: student_term_[date:now,format=Ymd]_inquiry
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_program/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| program | user-education-prefered-major | 2 | |
| inquiry_ind | This calculated field is to indicate if the student has a Date of Inquiry milestone on the profile. No additional action required. | 3 | |
| inquiry_dt | user-milestones-prospect-date | 4 | |
| curr_entry_term | EAB is expecting this field to be Fall, Spring, Summer, or Winter. Depending on the naming convention of the terms will determine this formula. Example when terms are named like Fall XXXX: SPLIT_INDEX(DB_MAP("term",[user-education-term],"guid","name","")," ",1) Example when terms are named like Fall/August XXXX: SPLIT_INDEX(DB_MAP("term",[user-education-term],"guid","name",""),"/",1) | 5 | |
| curr_entry_year_academic | EAB is expecting this field to be year portion of the term. Depending on the naming convention of the terms will determine this formula. Example when terms are named like Fall XXXX: SPLIT_INDEX(DB_MAP("term",[user-education-term],"guid","name","")," ",2) | 6 | |
| student_type | Map to user-intended-student-type and make sure it is being exported as name. | 7 | |
| program_recruitment_status | 8 | ||
| campus | user-education-campus | 9 | |
| degree_type | user-education-degree | 10 | |
| state_resident | Formula to calculated if a student is in state or out of state. It will check if the home address is the state code you insert and return TRUE or FALSE. Please insert your alpha 2 state code in the formula OR replace the formula with your resident custom field if you have one. IF([user-addresses-home-state]="<insert alpha2 code>","TRUE", IF([user-addresses-home-state]<>"<insert alpha2 code>","FALSE","")) | 11 |
Race File
Segment: EAB ALR
Repeat Rows Based On: User Profiles
Template: EAB ALR Race Layout
Filename: student_race_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_race/[date:now,format=Y-m-d]
| Name | Element Slug | Row |
|---|---|---|
| primary_student_id | user-elementid | 1 |
| race | user-race-categories | 2 |
Term Application File
Segment: EAB ALR
Repeat Rows Based On: Applications, Export Last Application
Template: EAB ALR Term Application Layout
Filename: student_term_application_[date:now,format=Ymd]
Settings: Comma (,), .csv
Destination: Create a new folder on selected path and paste: ALR/student_term_application/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| student_type | Map user-applications-student-type and export as name. | 2 | |
| curr_entry_term | EAB is expecting this field to be Fall, Spring, Summer, or Winter. Depending on the naming convention of the terms will determine this formula. Example when terms are named like Fall XXXX: SPLIT_INDEX(DB_MAP("term",[user-calculated-active_term-new],"guid","name","")," ",1) Example when terms are named like Fall/August XXXX: SPLIT_INDEX(DB_MAP("term",[user-calculated-active_term-new],"guid","name",""),"/",1) | 3 | |
| curr_entry_year_academic | EAB is expecting this field to be year portion of the term. Depending on the naming convention of the terms will determine this formula. Example when terms are named like Fall XXXX: SPLIT_INDEX(DB_MAP("term",[user-calculated-active_term-new],"guid","name","")," ",2) | 4 | |
| application_id | user-applications_registration-id | 5 | |
| app_start_ind | This field will return TRUE/FALSE depending on the presence of a application start milestone with a term that matches their current active term. No additional set up required. | 6 | |
| app_start_dt | This field will return a date depending on the presence of a application start milestone with a term that matches their current active term. No additional set up required. | 7 | |
| app_submit_ind | This field will return TRUE/FALSE depending on the presence of a application submit milestone with a term that matches their current active term. No additional set up required. | 8 | |
| app_submit_dt | This field will return a date depending on the presence of a application submit milestone with a term that matches their current active term. No additional set up required. | 9 | |
| app_complete_ind | This field will return TRUE/FALSE depending on the presence of a application complete milestone with a term that matches their current active term. No additional set up required. | 10 | |
| app_completion_dt | This field will return a date depending on the presence of a application complete milestone with a term that matches their current active term. No additional set up required. | 11 | |
| app_withdrawal_ind | This field will return TRUE/FALSE depending on the presence of a withdrawn milestone with a term that matches their current active term. No additional set up required. | 12 | |
| app_withdrawal_dt | This field will return a date depending on the presence of a withdraw milestone with a term that matches their current active term. No additional set up required. | 13 | |
| app_type | Add Replace transformations to transform the app guid to the application name. Application GUID can be found on the application module in the URL of the applications. | user-applications-guid | 14 |
| app_decision | 15 | ||
| admit_ind | This field will return TRUE/FALSE depending on the presence of an admit milestone with a term that matches their current active term. No additional set up required. | 16 | |
| admit_dt | This field will return a date depending on the presence of an admit milestone with a term that matches their current active term. No additional set up required. | 17 | |
| cond_admit_ind | This field will return TRUE/FALSE depending on the presence of a conditional admit milestone with a term that matches their current active term. No additional set up required. | 18 | |
| cond_admit_dt | This field will return a date depending on the presence of a conditional admit milestone with a term that matches their current active term. No additional set up required. | 19 | |
| cond_admit_reason | 20 | ||
| admit_notify_dt | 21 | ||
| deny_ind | This field will return TRUE/FALSE depending on the presence of a deny milestone with a term that matches their current active term. No additional set up required. | 22 | |
| deny_dt | This field will return a date depending on the presence of a deny milestone with a term that matches their current active term. No additional set up required. | 23 | |
| deferral_ind | This field will return TRUE/FALSE depending on the presence of a deferral milestone with a term that matches their current active term. No additional set up required. | 24 | |
| deferral_dt | This field will return a date depending on the presence of a deferral milestone with a term that matches their current active term. No additional set up required. | 25 | |
| deferral_from_term | 26 | ||
| deferral_to_term | 27 | ||
| waitlist_ind | This field will return TRUE/FALSE depending on the presence of a waitlist milestone with a term that matches their current active term. No additional set up required. | 28 | |
| waitlist_admit_ind | 29 | ||
| waitlist_dt | This field will return a date depending on the presence of a waitlist milestone with a term that matches their current active term. No additional set up required. | 30 | |
| deposit_ind | This field will return TRUE/FALSE depending on the presence of a deposit milestone with a term that matches their current active term. No additional set up required. | 31 | |
| deposit_dt | This field will return a date depending on the presence of a deposit milestone with a term that matches their current active term. No additional set up required. | 32 | |
| deposit_2_ind | 33 | ||
| deposit_2_dt | 34 | ||
| housing_deposit_ind | 35 | ||
| housing_deposit_dt | 36 | ||
| enroll_ind | This field will return TRUE/FALSE depending on the presence of an enroll milestone with a term that matches their current active term. No additional set up required. | 37 | |
| enroll_dt | user-education-academic-load | 38 | |
| withdrawal_ind | 39 | ||
| withdrawal_dt | 40 | ||
| test_optional_ind | 41 | ||
| full_part_time | user-education-academic-load | 42 | |
| school_applied | 43 | ||
| program | user-applications-major | 44 | |
| degree_type | user-applications-degree | 45 | |
| school_concentration | 46 | ||
| cohort_desc | 47 | ||
| enroll_type | 48 | ||
| commuter_type | user-applications-housing | 49 | |
| campus_location | user-applications-campus | 50 | |
| cancel_dt | 51 | ||
| major | user-applications-major | 52 |
Test Score File
Segment: EAB ALR
Repeat Rows Based On: Evaluations, Filter on Types that are collected by the school (like ACT and SAT)
Template: EAB ALR Test Score Layout
Filename: student_test_scores_[date:now,format=Ymd]
Settings: Comma (,), .txt
Destination: Create a new folder on selected path and paste: ALR/student_test_scores/[date:now,format=Y-m-d]
| Name | Additional Instructions | Element Slug | Row |
|---|---|---|---|
| primary_student_id | user-elementid | 1 | |
| exam_code | This formula will glue the exam type and exam name together. If only sending composite scores, this can be replaced with just the exam type. | 2 | |
| exam_score | Insert evaluation score here depending on the exam types being used. Example: IF(CONTAINS([user-evaluations-type],"Math")="TRUE",[user-evaluations-math-score], IF(CONTAINS([user-evaluations-type],"English")="TRUE",[user-evaluations-english-score], IF(CONTAINS([user-evaluations-type],"Evidence")="TRUE",[user-evaluations-evidence_rw-score], IF(CONTAINS([user-evaluations-type],"Science")="TRUE",[user-evaluations-science-score], IF(CONTAINS([user-evaluations-type],"Reading")="TRUE",[user-evaluations-reading-score], IF(CONTAINS([user-evaluations-type],"Writing")="TRUE",[user-evaluations-writing-score], IF(CONTAINS([user-evaluations-type],"Composite")="TRUE",[user-evaluations-composite-score],""))))))) | 3 | |
| exam_dt | user-evaluations-date | 4 |