Detecting Power Users Programmatically

1. Detecting Power Users Programmatically George Kroner Innovative Insights

2. Agenda • What is going on in there? • Why would I want to know? • Models and methods to consider and measurement metrics • Get to know the database • What tools exist to assist?

3. What is going on in there? • Did you know that… • Blackboard’s database is now open and documented for certain uses? • Blackboard records (nearly) every click made in the system in an audit log? • If you’ve turned this off, you likely still have HTTP logs that you can parse through • Years of system usage data are likely present in your Blackboard system? • The data locked inside is becoming increasingly relevant across a variety of applications?

7. Why would I want to know? • Data to reinforce (or contradict) user feedback • Find power users who can be advocates • Find users familiar with certain toolsets • Evaluating how the system meets your users’ needs • …

8. Models, methods, and measurement metrics • Who logs in the most • Who access the most pages • Who has the most students • Who teaches the most courses • Who adds or accesses the most content • Who uses the most available tools • Who interacts the most with others

9. Wider Deeper Logins Page hits General Tool usage Forum usage Total content by type User activity streams Total content Specific

10. Methods • Basic numbers • Thresholds • Trends • Patterns • Activity streams Easier More difficult, but more interesting

11. Database tables • Logins • Content • Connections (discussion forums, enrollments) • Tools • Page hits • bb_bb60 vs BBLEARN • _stats (eg: bb_bb60_stats) is populated when you run PurgeAccumulator.sh/.bat • Activity_accumulator_queue (flushed every 10 mins)

12. Logins & Page Hits • activity_accumulator • event_type (LOGIN_ATTEMPT, PAGE_ACCESS) • user_pk1 • timestamp • users • pk1 • user_id • institution_roles_pk1 • institution_roles • pk1 • role_name • description • role_id

13. Logins & Page Hits (examples) • Obtain total logins on a given day • select count(*) from activity_accumulator where event_type = 'LOGIN_ATTEMPT' and timestamp between convert(datetime, '2010-07-11', 120) and convert(datetime, '2010-07-11', 120) • to_date('2010-07-11','YYYY-MM-DD HH24:MI:SS') and to_date('2010-07-11','YYYY-MM-DD HH24:MI:SS') • Obtain total # users who logged in today • select count(*) from users where last_login_date between convert(datetime, '2010-07-10', 120) and convert(datetime, '2010-07-11', 120) • Obtain total page hits on a given day • select count(*) from activity_accumulator where event_type = 'PAGE_ACCESS' and timestamp between convert(datetime, '2010-07-11', 120) and convert(datetime, '2010-07-11', 120)

14. Content • content_handlers • handle • Name • course_content_handlers • crsmain_pk1 • content_handlers_pk1 • available_ind • course_contents • every content item in a course • cnthndlr_handle • dtcreated • dtmodified • crsmain_pk1 • available_ind • title • parent_pk1 • course_contents_files • links course_contents to files • files • storage_type (CS or LOCAL) • file_name • link_name • file_size

15. Content (example) • Obtain total content count by type • select count (*) from course_contents where cnthndlr_handle = 'resource/x-bb-document' • Obtain content added in the past day • select count(*) from course_contents where dtcreated between convert(datetime, '2010-07-11', 120) and convert(datetime, '2010-07-11', 120)

16. Connections (discussion forums) • Conference_main • Forum_main (course-level, group-level) • Msg_main (parent-child relationship for replies) • Example: • Obtain total messages • SELECT count(*) from msg_main

17. Connections (enrollments) • users • courses • course_users • Links pk1 of users and courses tables • course_roles • Example • Enabled instructor enrollments • SELECT COUNT(DISTINCT users.pk1) FROM course_users, users WHERE course_users.row_status = 0 AND course_users.available_ind = 'Y' AND course_users.role IN ('B', 'G', 'P', 'T') AND users.pk1 = course_users.users_pk1 AND users.row_status = 0 AND users.available_ind = 'Y'

18. Tools • navigation_item • application (indicator of product subsystem) • internal_handle • course_navigation_item • internal_handle • enabled_ind • application • plugins_pk1 indicates a Building Block • Blogs, journals, groups • Others have relevant data beyond on/off – but in their own tables

19. Activity Streams • Investigate individual user behavior for the duration of a session • Investigate course behavior/activity on a given day

20. Activity Streams (example) • User streams • SELECT pk1, event_type, user_pk1, course_pk1, timestamp, data FROM activity_accumulator WHERE user_pk1 IS NOT NULL ORDER BY user_pk1, timestamp • Course streams • SELECT pk1, event_type, user_pk1, course_pk1, timestamp, data FROM activity_accumulator WHERE course_pk1 IS NOT NULL ORDER BY course_pk1, timestamp

21. Tools to help visualize (picking the right one) • SQL Server Management Studio • Aqua Data Studio • Gephi (based on NetBeans) • Talend Open Profiler • Jaspersoft • Blackboard-specific development

22. SQL Server Management Studio

23. Aqua Data Studio

24. Gephi

25. Blackboard Social Network Analysis Tool (user-facing!)

26. Talend Open Profiler

27. Jaspersoft

28. Blackboard-specific: ASTRO

29. Blackboard-specific: UMBC Reports

30. Blackboard-specific: EduTechnica.com

31. Please provide feedback for this session by emailing DevConFeedback@blackboard.com.  The subject of the email should be title of this session: Detecting Power Users Programmatically