Staging and intermediate models for edx courses and enrollments

[rachellougee]

User Story

Look into edx courses and enrollments data and create corresponding staging and intermediate models, like what we have done for MITxonline, xPro and bootcamp

Description/Context

edx course metadata

Source table

• raw__irx__edxorg__bigquery__mitx_course
• https://github.com/MIT-IR/simeon/blob/main/simeon/upload/schemas/schema_mitx_course.json

edx enrollments

Source table

• raw__irx__edxorg__bigquery__mitx_person_course
• https://github.com/MIT-IR/simeon/blob/main/simeon/upload/schemas/schema_person_course.json

[rachellougee]

A large number of enrollment data (874k as of today) in person_courses are missing user_id and most of the fields are blank, It looks like the same parsing issue is propagated into this table. That will need to be addressed from the source. We might need to filter these blanks until it's fixed @dseaton FYI

[dseaton]

Thanks for letting me know. I'll reflect this in the doc.

[rachellougee]

mitx_course and person_course are staged, also added enrollments and certificates to intermediate layers.

Note a couple of data issues here:

• Some blank usernames in person_course, which seems to be a different bug from the blank user_id. This will be addressed when we stage users' info so that we can fill these blank usernames in on our end.
• mitx_course is not a complete list of all the courses from enrollments and grades
  • There are some course id found in person_courses that don't exist in courses metadata, e.g. MITx/Launch.x_3/1T2017
  • In intermediate, we use "inner join" to fetch course metadata, which would eliminate these enrollments for courses that don't exist in mitx_course.
  • If we need to show all the enrollments regardless of whether there is course metadata or not, we will need to change to left join

[rachellougee]

@dseaton I mentioned to you some courses in person_courses that don't exist in mitx_courses. There are actually only 6 of them, not a lot. Just FYI

MITx/CTL.CFx/1T18U5 MITx/CTL.CFx/1T18U8 MITx/Launch.x_3/1T2017 MITx/18.03Fx/2T2020 MITx/20.305x/1T2019 MITx/CTL.CFx/1T18U1

[dseaton]

Talked to Peter. The "*.CFx" courses are final exam courses, i.e., a course where a learner takes a final exam, but doesn't have other learner activity (example). We think this was an artifact before online proctoring. IMHO - I am ok that these courses aren't in mitx_courses. Whether we drop them or not from person course is something we should discuss.

I don't know why 18.03Fx (part of an x-series) and 20.305x are not in "mitx_courses". We may need to ask.

Launch.x_3 might be related to bootcamps in some way, but again, need to check why it isn't in the list.

[rachellougee]

To clarify these missing course runs, there are MITx/18.03Fx/1T2020 and MITx/20.305x/2T2019 run in mitx_course, but missing MITx/18.03Fx/2T2020 and MITx/20.305x/1T2019