=vLOOKUP(f2,f:m,8,FALSE)
f2 = first cell
f:m = range in which to look
8=the number of columns over from the start of range)
FALSE = just always here
AND, run pivot table on capella - grab email addresses from the pivot list to remove duplicates.
Run pivot on sophia - grab email addresses from pivot list to remove duplicates.
Run pivot on combined list and get rid of any 2's or 3's. First round, not one duplicate across both lists.
Prod Staging
https://www.prodstaging.sophiaapp.com/
sign in: user11245@sophia.org.blocked
pw: SophiaTeam!
You can mess around with anything here and change it around. Chris said it's okay to try and experiment here. Save, etc. Won't mess anything up.
Landing Page: Most flexible
Homebase: A little flexibility
In-Course: avoid unless there is a known partner with a lot of return. Customizing in-course messaging messes a bit with our model - we want to keep academics clean.
1. Program Manager, Edit the partner you want.
2. Homebase Message is what can be customized (Homebase Description isn't visible to learner).
3. Enter messaging, click Save.
4. add partner name to the staging url: https://coloradotech.prodstaging.sophiaapp.com/home
https://capella.prodstaging.sophiaapp.com/home
The partner name must match exactly what the url name is (ex: coloradotech vs coloradotechuniversity).
5. Try a course for free or create an account so you can see what the learner will see.
6. Click on Sophia's logo and messaging should be there.
1. create fake email (ex: indianatech1@sophia.org, indianatech2@sophia.org, etc).
2. go to partner site (log out, click on partner), then click on create an account or try course for free.
3. create account w/password.
4. Log in as Kelly, go to admin/sp users
a. SP Students, find student
b. State: Active, un-check Paid
c. Click "Edit" by Student Info
-----un-check SPCC Student and Marketing Lead
d. fill in address - use Sophia's address, add phone # 612-555-5555, check honor code. That way, new learner can go straight to course.
Pull Leads
Filter to ASB329 promo code - this is how we identify ASB students
Delete all other promo codes
If wanting to check Promo Codes, the program is "College Courses" for ASB.
Delete false students (Aicha Hassouna).
Pull Student Summary...find these students. Add column and paste promo code ASB329 when finding these students. This way, when you delete blanks, only the 3 that are associated with College Courses with promo ASB329 pops up.
Sort end date by Ascending. Save as .xls
Go to Admin/Section Manager:
Add section. Name of course, type is Cohort. Instructor is Success Coach (successcoach@jetblue.com). I think I need to send success coach the instructor access code.
If a student does not pull up in the "add student", then need to create an account.
To Create an Account:
1. Go to Admin/Users to check to see if they have an account.
2. Go to jetblue.sophia.org Create Account. Use password generator to create password.
3. If you find them in a separate section, check progress and see if extension can work. Or, if not much progress, then delete and add to new section.
4. If a student fails academically wants to re-register for the same course, need to go into Users and modify the old email address and add the "new" one to the course.
1. Pull SUMM and Leads reports
2. note: actual start date on Summary is NOT THE SAME AS REGISTRATION. Only way to get registered students is Leads report.
3. LEADS, remove blanks. Filter out blanks in Registered Date, deleted those who are not registered. SUMM, remove any FALSE (usually no false for Avon).
4.. paste them into tabs into the Avon Progress Report
3. Create pivot table. manual. select column b on student summary tab. drag courses into Row Labels and Values. Paste values only into column K for graph on summary page to update.
ADMINISTRATION
PROMO CODES/select partner, then View
NEW PROMOTION
Value: (name of promo such as WGU199)
Description: (copy such as "Sophia Courses Now Only $199!) or, if for ACE, just na since it may not be visible since only used in email blast for ACE and Scholarship.
Deadline:
# purchases avail: usually no cap
Discount either dollar or percent (do not include $ or % sign)
Limit purchases per user account: usually no limit
Display: click box if needed to be displayed on site. If not visible leave box un-checked.
If visible, sign out of Sophia, go to partner page, click on partner page to view and proof.
* if a code is used at any time for any partner, it cannot be used again. For ex: ASU used "50Summer" in 2014. Could not use for ACE in 2016. Solution: modify ASU to 50SummerEXPIRED (there was only 1 used - changed w/garrett's approval).
1. Delete FALSE students.
2. Paste into Formulaic template
3. Check English Comp - make sure all Touchstones are indicated/accounted for and make sense.
4. Check all completed dates - all need to be 100%
5. On flip side, if 100% course completion, but no completed date, they need IDVM - note this.
6. Sort by Completed Date, THEN Course Completion %
7. Insert rows at top. Who has 100% course completion, but no completed date? Investigate why and note.
1. login sophia, go to Program Manager
2. tier 2 (dack flips switch to flex) save
3. na in almost all areas - template simple
4. automated emails to at-risk students
5. send transcripts to partner
6. paste program name to external partner name (this indicates "equivalent to __" SAVE external email
7. ADD COURSE fill in.
8. once done, need URL and logo for Dack to flip switch. get from Garrett.
8.
Pull leads for.....completion date?
Pull student summary
1. pull student summary
2. Pull Dashboard info and add to "Data & Pivot Detail"
3. FORMULAS
3a. July MTD and June MTD are formulas, taking data from "Data & Pivot Detail".
3b. Partnership to Date is also formulaic from "Data & Pivot" for free trials, registration, conversions, completions,
4. Actual Start Date: Delete Blanks.
5. columns to %: avg milstone, best possible, and current score
6. certification: sort descending
7. Insert columns around Milestones Completed. Cut "[TOTAL] Milestones and add formula for % Milestone Completed. 75% and up, is Approaching Completion.
8. Add column headers for section who has not completed. Add Filters sort by ascending by % milestone completed (%).
** when pasting dates, select YES to convert into .xls
grab all completers and paste into progress tab under Completed
APPROACHING COMPLETION & IN PROGRESS
1. sort by end date.
2. Get rid of blanks.
3. sort end date by "after or equal to today's date" YES, I do want to delete dates for Approaching and In Progress, anything before that day's day. If the end date has passed, I'm not going to report that they are approaching or In Progress, because they're not. They fell off and in LC land.
4. Grab milestones (total) and cut and paste next to completed milestones.
5. Insert column with % done MS. whoever is at 75% or more, is in the approaching completion. Sort descending by % done w/MS to see who is at 75%.
6. Whoever is below 75%, is In Progress.
MOVE IDVM BY % MS COMPLETED AND CERTIFICATION DATE NEXT TO IDVM SO YOU CAN EASILY SEE WHAT WORK REMAINS AND IF THERE IS ANYTHING WONKY.
2. =IF formula (copy from 6.6) or paste (with appropriate cell #).
=IF(B196="College Algebra", 5, IF(B196="English Composition I", 5, IF(B196="Introduction to Psychology",6, IF(B196="Project Management", 4, IF(B196="Conflict Resolution", 6, IF(B196="Introduction to Statistics",6, IF(B196="Human Biology", 6, IF(B196="Microeconomics", 4, IF(B196="Accounting", 4, IF(B196="Environmental Science", 4, IF(B196="Macroeconomics", 4, IF(B196="Introduction to Sociology", 5, IF(B196="Introduction to Business", 4, IF(B196="Visual Communications", 4, IF(B196="Introduction to Art History", 6, IF(B196="Approaches to Studying Religions", 5))))))))))))))))
3. MS is now re-formatted to NOT have 4/5 complete, so now add formula: % Milestone Complete column. MS complete/Total MS.
4. Paste into "Approaching Completion" section on Progress Report tab.
5. Touchstones:
=IF(B197="English Composition I", 5)
IN PROGRESS: LESS THAN 75%
Avg # of courses taken by 1 student: pivot table - pull email or student name (registered only of course!) from summ or leads. highlight all the #'s, then click "average" at the bottom. BOOM.
IN PROGRESS will have Actual Start Dates in the future (about a month or two. For ex: for 5.31 report, there are students with 7.28.16 actual start dates).
THEN, pull Program Info for Summary Page. Free Trials, Registrations, etc.
Unhide RAW DATA tab. Update Free Trials, Registrations, Course Completions and Unique
*DIFFERENCE BETWEEN LEADS AND SUMM: LEADS FEATURES ALL STUDENTS, EVEN IF THEY DROPPED (THERE IS NOT A DROPPED INDICATOR).
To pull Registrations, Traffic - Visitors (not Visits), Free Trials, Completions, use the rails site:
https://dashboard-rails.sophiaapp.com
login: kelly.nordstrom@sophia.org
pw: sophia123
PIVOT TABLE: HOW-TO
To pull pivot data for registrations per course (turn FILTERS OFF! first)
Select data from Leads report (not student summ...summ has dropped).
student summary, filter by Actual Start Date to get only registered students. Clear out the blanks - delete. Create pivot table on another tab. go to student summary, and select entire Courses column
* NEED TO GO BACK TO ORIGINAL STUDENT SUMMARY TO GET DROPS IF YOU DELETED THEM IN THE BEGINNING *
Course completion %: Now a formula
Course completion % (sans drops): Now a formula
1. Total MS is column AA. Insert 2 columns next to it.
2. Cut Milestones Completed column and put next to Total.
3. formula completed/total = %
4. Filter out blanks for Actual Start Date.
5. sort by descending. filter out any % less than 75%
6. check out approaching completion documentation; see who you've previously communicated.
7. update approaching completion doc.
RASMUSSEN STEPS
1. pull student summary
2. add filters to 1st row
3. under "program", filter - select all, then un-select rasmussen.
4. command + shift +down arrow,
5. right click on rows and delete all programs
6. filter again under Programs and select rasmussen
7. real student column - remove false students.
8. filter and un-select "TRUE", so all the "FALSE" students show. command + shift + down and delete.
9. real student column - filter - select TRUE
10. 3 columns need to turn into percentages: Current Score, Best Possible Score, and Average Milestone Score.
11. Command + click to select multiple non-sequential columns. Highlight entire columns.
12. Click the % icon.
13. sort by actual start date and IDVM score
14. Click on Data. Sort. Click + to add "Add Then".
15. Click on values and change to "Actual Start Date" (order is newest to oldest)
16. Then by IDVM (all the way to the bottom) (order is Largest to Smallest).
17. Save As format ".xls"
18. Name it "Rasmussen Daily Report + date
19. Dena.tuschen@rasmussen.edu,
timothy.loatman@collegiseducation.com,
flexchoice.records@rasmussen.edu cc: Michael Patch
Pull program info...update UPDATED tab
Pull Leads (sort by end date) for course enrollments, completions, possible completions.
Count course completions.
Current Enrollment: sort out blanks in Completion. Sort end date by descending. Sort registration by descending. Want all that registered with an end date after today's date that have not completed yet. These are the current enrollments.
Possible completions: sort end date by descending. sort registration by descending so all the registrations float to the top. Everything below the current date is a possible completer. Do a count. This is for students who's end date has passed and had the opportunity to complete; in fact, some DID complete. they could have completed but either didn't or did...it's all about the opportunity to complete. This is the % that calculates % completions. The qualifers are: 1. end date has passed and 2. they are registered.
Summary tab divides #possible completers by completers for %
.
Avg days to complete. Insert column in Leads. =completion date column - actual start date. format new inserted column - - format cells to number with no decimal pts. Remove blanks in completed column to get rid of ridiculous numbers.
Avg # classes per student. Select section of email list in leads (registered only...by now you've deleted all the non-registered). Count in pivot, then select avg. Count Registered Students with a completed date.
Highlight courses column (only registered). seperate tab in excel, create Pivot table - manual. choose existing worksheet. highlight column with course titles. LOcation is column with course titles.
