Tags

,

Tracking student progress is important.  Many advisors and other student success staff may find it difficult to see students in-person.  It used to be simple to ask a student to show their Blackboard courses and activity during an office session.  Can support staff and faculty get access to Blackboard Learn activity remotely?  Certainly there are enterprise early alert system vendors, who make it easy to access this data and collaborate on it.

However, Blackboard Data can be helpful in tracking current student LMS activity.  The report described in this post can be automated via email or another delivery process, so advisors can access it when needed.  The report provides insight into student activity within Blackboard Learn and Collaborate.

It is based on a package delivered earlier by Steve Bailey and Steve Grantham.  This report adds to the previous package: student current course grade, average comparison to students in the same course of course logins, hours of course activity, and interactions.  It also shows earliest and latest course access by other students in the course.  Instead of using term restrictions, the report simplifies the previous package and relies on the course_id pattern.  Finally, for early adopters of Blackboard Data who are not comfortable editing complex statements, the report below provides a single SQL line to collect courses of interest.

Snapshot of the data dictionary:

Screen Shot 2020-04-12 at 7.40.07 PM

Download the data dictionary:  V2_Student Activity Query Data Dictionary

 

; with my_courses as
(
    select id from cdm_lms.course where Course_Number like '2020.spring.%'

),
courses as
(
select 
  --  t.id as Term_Surr_Key,
  --  t.Source_ID as Term_PK1,
  --  t.name as Term_Name,
  --  t.Start_Date as Term_Start,
  --  t.End_Date as Term_End,   
    c.id as Course_Surr_Key,
    c.source_id as Course_PK1,
    C.Course_Number as Learn_Course_ID,    
    C.Name as Course_Name,
    --C.Start_Date as Course_Start,
    C.End_Date as Course_End,
    C.Available_Ind as Avail_Flag,
    C.Available_to_Students_Ind as Avail_To_Students_Flag,
    case when c.design_mode_source_code = 'U' then 'Ultra' else 'Classic' end as Ultra_Flag   
from 
    cdm_lms.course c        
   -- left join
   --     cdm_lms.term t
   --     on  c.term_id = t.id
where
    --Modify the following date criterion to suit operational needs
    --Course_Start between '2019-12-21' and sysdate()
    --C.Course_Number like '2020.spring.%'
     c.row_deleted_time is null
),

enrollment as
--all students enrolled in any of the above courses, according to person_course table
(
 select
    --distinct
    pc.id as Person_Course_ID,
    p.id as Student_Surr_Key,
    p.source_id as Student_PK1,
    p.last_name as Student_Last_Name,
    p.first_name as Student_First_Name,
    p.email as Student_Email,
   -- c.Term_Name,
   -- c.Term_Start,
    c.Course_Surr_Key,
    c.Course_PK1,
    c.Learn_Course_ID,    
    c.Course_Name
    --c.Course_Start
 from
    courses c
    inner join
        CDM_LMS.Person_Course pc
        on  pc.course_id = c.Course_Surr_Key and
            pc.Course_Role = 'S'
    inner join 
        CDM_LMS.Person P
        on P.ID = PC.Person_ID     
),


student_course_activity as
 (
 select
    e.Student_Surr_Key,
    e.Student_PK1,
    e.Student_Last_Name,
    e.Student_First_Name,
    e.Student_Email,
   -- e.Term_Name,
   -- e.Term_Start,
    e.Course_Surr_Key,
    e.Course_PK1,
    e.Learn_Course_ID,    
    e.Course_Name,
    --e.Course_Start,
    count(ca.id) as Student_Login_Count,
    date_trunc('minute',max(ca.LAST_ACCESSED_TIME)) as Student_Last_Access,
    round(sum(ca.duration_sum/3600),2) as Student_Course_Activity_Hours,
    sum(ca.Interaction_Cnt) as Student_Course_Interaction_Count
 from
    enrollment e
    left join
        CDM_LMS.Course_Activity ca
        on  ca.person_id = e.Student_Surr_Key and
            ca.course_id = e.Course_Surr_Key
  group by
    e.Student_Surr_Key,
    e.Student_PK1,
    e.Student_Last_Name,
    e.Student_First_Name,
    e.Student_Email,
    --e.Term_Name,
    --e.Term_Start,
    e.Course_Surr_Key,
    e.Course_PK1,
    e.Learn_Course_ID,    
    e.Course_Name
   -- e.Course_Start
 ),
 
 collab_room as
 (
 select
    cr.lms_course_id as Course_Surr_Key,
    count(distinct cr.clb_room_id) as Course_Collab_Room_Count,
    count(s.id) as Collab_Session_Count
 from
    cdm_map.course_room cr
    left join
        cdm_clb.session s
        on   s.room_id = cr.clb_room_id
 group by
    cr.lms_course_id
 ),
 
 
 collab_attend as
 (
 select
   cr.lms_course_id as Course_Surr_Key,
   a.person_id as collab_person_surr_key,
   mp.lms_person_id as learn_person_surr_key,
   round(sum(a.duration/3600),2) as Student_Course_Collab_Attendance_Hours
 from
    cdm_map.course_room cr
    inner join
        cdm_clb.session s
        on   s.room_id = cr.clb_room_id
    inner join
        cdm_clb.attendance a
        on  a.session_id = s.id
    inner join
        cdm_map.person  mp
        on  mp.clb_person_id = a.person_id

 group by
   cr.lms_course_id,
   --cr.clb_room_id,
   a.person_id,
   mp.lms_person_id
 ),
 
student_course_activity_with_collab as 
(
select  
    sca.Student_Surr_Key,
    sca.Student_PK1,
    sca.Student_Last_Name,
    sca.Student_First_Name,
    sca.Student_Email,
    --sca.Term_Name,
    --sca.Term_Start,
    sca.Course_Surr_Key,
    sca.Learn_Course_ID,    
    sca.Course_Name,
    --sca.Course_Start,
    sca.Student_Login_Count,
    sca.Student_Last_Access,
    sca.Student_Course_Activity_Hours,
    sca.Student_Course_Interaction_Count,
    case when cr.Course_Collab_Room_Count > 0 then 'Has Collab Room(s)' else 'No Collab Rooms' end as Course_Collab_Flag,
    ca.Student_Course_Collab_Attendance_Hours
from 
    student_course_activity sca
    left join
        collab_room cr
        on  cr.Course_Surr_Key = sca.Course_Surr_Key         
    left join
        collab_attend ca
        on  ca.Course_Surr_Key = sca.Course_Surr_Key and
            ca.learn_person_surr_key = sca.Student_Surr_Key
),

student_course_avg as
 (
 select
   
    (select count(*) from enrollment where Course_Surr_Key = c.Course_Surr_Key) as c,
    c.Course_Surr_Key,
    c.Course_PK1,
    c.Learn_Course_ID,    
    c.Course_Name,
    --e.Course_Start,
    case when c > 0 then round(count(ca.id) / c,0) else 0 end as Avg_Other_Students_Login_Count,
    date_trunc('minute',max(ca.LAST_ACCESSED_TIME))  as Max_Last_Access_Per_Course,
    date_trunc('minute',min(ca.LAST_ACCESSED_TIME))  as Min_Last_Access_Per_Course,

    case when c > 0 then round(sum(ca.duration_sum/3600)/c,2) else 0 end as Avg_Other_Students_Course_Activity_Hours,
    case when c > 0 then round(sum(ca.Interaction_Cnt)/c,0) else 0 end as Avg_Other_Students_Course_Interaction_Count
 from
    courses c
    left join
        CDM_LMS.Course_Activity ca
        on  
            ca.course_id = c.Course_Surr_Key
  group by
    c,
    c.Course_Surr_Key,
    c.Course_PK1,
    c.Learn_Course_ID,    
    c.Course_Name
   -- e.Course_Start
 ),
 
 student_grade as
 (
 select
    e.Student_Surr_Key,
    e.Course_Surr_Key,
    round(g.normalized_score*100,0) as total_grade_prct
   from
    enrollment e
    left join CDM_LMS.grade g
    on  g.person_course_id = e.Person_Course_ID 
        inner join cdm_lms.gradebook lgb
                on g.gradebook_id = lgb.id
        where lgb.final_grade_ind = 1
        and g.row_deleted_time is null
        
 )

     
    
 
select  
    scac.Student_PK1,
    scac.Student_Last_Name,
    scac.Student_First_Name,
    scac.Student_Email,
    --scac.Term_Name,
    --scac.Term_Start,
    --scac.Course_Start,
    scac.Learn_Course_ID,    
    scac.Course_Name,
   
    sg.total_grade_prct,
          
    scac.Student_Login_Count,
    scac.Student_Last_Access,
    scac.Student_Course_Activity_Hours,
    scac.Student_Course_Interaction_Count,
    scac.Course_Collab_Flag,
    scac.Student_Course_Collab_Attendance_Hours,
    stav.Avg_Other_Students_Login_Count,
    stav.Max_Last_Access_Per_Course,
    stav.Min_Last_Access_Per_Course,
    stav.Avg_Other_Students_Course_Activity_Hours,
    stav.Avg_Other_Students_Course_Interaction_Count
from 
    student_course_activity_with_collab scac
    inner join my_courses my on my.id = scac.Course_Surr_Key
    left join student_course_avg stav
    on     scac.Course_Surr_Key = stav.Course_Surr_Key
     left join student_grade sg
    on     scac.Course_Surr_Key = sg.Course_Surr_Key and scac.Student_Surr_Key = sg.Student_Surr_Key

    
order by
    scac.Student_Last_Name,
    scac.Student_First_Name,
   -- scac.Student_PK1
   -- scac.Term_Start,
   -- scac.Course_Name
   Learn_Course_ID