Investigation: Data and model updates needed for Aspects v1 reports

[SoryRawyer]

Following the product feedback on v1 report requirements for Aspects, I've put together a high-level summary of the data we'll need to address those requirements.

New data needed in ClickHouse

Cross-cutting initiatives

• Completion events
  • Would these events meet the criteria for showing when learners have completed a problem, section, course, etc? Or is the definition of "completion" that triggers this event too narrow for our purposes?
  • GitHub issue: openedx-aspects #88
• Certificate/badge events
  • GitHub issue: openedx-aspects #90
• Device type (mobile, desktop, etc)
  • Potentially add a new "session start" event with device type

Existing data that meets our needs

• Problem response types (this is included in the problem interaction events, but if this is part of the problem block metadata then it might be convenient to include in a lookup table to easily filter on ORA problems)
• Registered learners not enrolled in any courses

Data model updates

• Update fact_learner_problem_summary to include grades
• Additional problem block metadata: gradable and response type (to distinguish between ORA and non-ORA problems)
• New fact tables for the following event types: navigation, completion, certification, grading, and forum interactions
  • Forum interactions should include the forum topic in addition to common dimensions like org, course_key, entity names, and actor_id

[Ian2012]

Data needed

• Total students (include inactive)
• Total active students
• Total enrollments (include inactive)
• Total active enrollments
• Approval rate (approved vs total students)
• Total paid enrollments (enrollments of mode professional, or any paid mode)
• Average student progress
• Total video time watched per video
• Average tries per gradeable problem.
• Average tries per non-gradable problem.

Demographics

• Distribution by gender
• Distribution by level of education
• Distribution per country

Aggregated data

• Exam's approval rate
• Median duration of visualization per video

Student activity

• Users with activity in the last period (week, month, etc)
• Students progress per section/subsection
• User sessions/logins

[bmtcril]

I know we've made some progress on these, @SoryRawyer @Ian2012 can you update with any additional news?

[Ian2012]

We have now a user_profile sink that can provide insights on demographics data, but there is no model to get the latest status of this information.

We have grade events for subsections, problems, and courses, but there is no model to get the latest status of this information.

We are missing the definition of a paid enrollment.

[bmtcril]

Login / logout events have been added