MEGS-KT: Learning Analytics with Moodle (Jisc project)

As part of our series of posts on the MEGS-KT project, today we'll look at Learning Analytics with Moodle. Specifically, how might we go about figuring out which resources ("learning objects") from the Moodle VLE are of particular interest to a wider audience? Let's see...

If your institution is a Moodle user, you will be used to seeing a view of the service like the one above - this is from Loughborough's customized version of Moodle known as "Learn". However, this is just the front end. Behind the scenes the Moodle software is busy logging every click (or tap, for tablet and smartphone users!) into a database. Perhaps we can extract some useful information from this...

Finding a course

First off, we need to figure out what the Moodle internal identifier is for our course. [As an aside: confusingly at Loughborough use the term "modules" to refer to the component parts of a "programme", which is the overall course that the student is enrolled on.] Let's pick "12ELC022", Power Electronics for Renewables, taught by our Centre for Renewable Energy Systems Technology.

Now we can fire up the MySQL console and do a bit of rummaging around. Moodle keeps its course information in a table called "course": (although many installations will have this prefixed with a site specific value, e.g. "lboro_course")

mysql> describe course;

+------------------------+-----------------------+------+-----+---------+----------------+

+------------------------+-----------------------+------+-----+---------+----------------+

| id | bigint(10) unsigned | NO | PRI | NULL | auto_increment |

| category | bigint(10) unsigned | NO | MUL | 0 | |

| sortorder | bigint(10) unsigned | NO | | 0 | |

| shortname | varchar(255) | NO | MUL | | |

| idnumber | varchar(100) | NO | MUL | | |

| summaryformat | tinyint(2) unsigned | NO | | 0 | |

| showgrades | tinyint(2) unsigned | NO | | 1 | |

| newsitems | mediumint(5) unsigned | NO | | 1 | |

| startdate | bigint(10) unsigned | NO | | 0 | |

| numsections | mediumint(5) unsigned | NO | | 1 | |

| marker | bigint(10) unsigned | NO | | 0 | |

| maxbytes | bigint(10) unsigned | NO | | 0 | |

| legacyfiles | smallint(4) unsigned | NO | | 0 | |

| showreports | smallint(4) unsigned | NO | | 0 | |

| visible | tinyint(1) unsigned | NO | | 1 | |

| visibleold | tinyint(1) unsigned | NO | | 1 | |

| hiddensections | tinyint(2) unsigned | NO | | 0 | |

| groupmode | smallint(4) unsigned | NO | | 0 | |

| groupmodeforce | smallint(4) unsigned | NO | | 0 | |

| defaultgroupingid | bigint(10) unsigned | NO | | 0 | |

| timecreated | bigint(10) unsigned | NO | | 0 | |

| timemodified | bigint(10) unsigned | NO | | 0 | |

| requested | tinyint(1) unsigned | NO | | 0 | |

| restrictmodules | tinyint(1) unsigned | NO | | 0 | |

| enablecompletion | tinyint(1) unsigned | NO | | 0 | |

| completionnotify | tinyint(1) unsigned | NO | | 0 | |

| completionstartonenrol | tinyint(1) unsigned | NO | | 0 | |

| creditweighting | int(5) | NO | | 0 | |

| semester | int(2) | NO | | 4 | |

+------------------------+-----------------------+------+-----+---------+----------------+

35 rows in set (0.00 sec)

I happen to know already that we are interested in the "id" field (the internal Moodle identifier for the course) and the "shortname", which corresponds to our module code, so we can do this to map from the module code to the internal Moodle ID:

mysql> select id from course where shortname='12ELC022';

+------+

| id |

+------+

| 1880 |

+------+

1 row in set (0.00 sec)

What are the resources associated with a course?

Conveniently, these are in a table called "resource". Let's see what information is kept in there:

mysql> describe resource;

+-----------------+----------------------+------+-----+---------+----------------+

+-----------------+----------------------+------+-----+---------+----------------+

| id | bigint(10) unsigned | NO | PRI | NULL | auto_increment |

| course | bigint(10) unsigned | NO | MUL | 0 | |

| introformat | smallint(4) unsigned | NO | | 0 | |

| tobemigrated | smallint(4) unsigned | NO | | 0 | |

| legacyfiles | smallint(4) unsigned | NO | | 0 | |

| display | smallint(4) unsigned | NO | | 0 | |

| filterfiles | smallint(4) unsigned | NO | | 0 | |

| revision | bigint(10) unsigned | NO | | 0 | |

| timemodified | bigint(10) unsigned | NO | | 0 | |

+-----------------+----------------------+------+-----+---------+----------------+

13 rows in set (0.00 sec)

Like courses, resources have their own internal Moodle identifier, which is the "id" field. The "course" field links back to the internal identifier for the course, so we can dump out a list of resources associated with a Moodle course (aka Loughborough "module") like so:

mysql> select * from resource where course=1880;

+--------+--------+----------------------------------------------------------------+-------------------------------------------------+-------------+--------------+-------------+-----------------+---------+------------------------------------------------------+-------------+----------+--------------+

| 123957 | 1880 | Downloadable copy of this simulator |

| 1 | 0 | 0 | NULL | 4 | a:2:{s:12:"printheading";i:0;s:10:"printintro";i:1;} | 0 | 0 | 1331122483 |

| 137542 | 1880 | 1_Introduction_04_Oct_2011.mp4 |

| 1 | 0 | 0 | NULL | 4 | a:2:{s:12:"printheading";i:0;s:10:"printintro";i:1;} | 0 | 0 | 1317973001 |

| 137543 | 1880 | 1_Introduction_04_Oct_2011.mp3 |

| 1 | 0 | 0 | NULL | 4 | a:2:{s:12:"printheading";i:0;s:10:"printintro";i:1;} | 0 | 0 | 1317973055 |

[...]

| 187213 | 1880 | 15 Grid Connected Inverters 12 Feb 2013.mp4 | | 1 | 0 | 0 | NULL | 0 | a:2:{s:12:"printheading";i:0;s:10:"printintro";i:0;} | 0 | 1 | 1361353325 |

| 187216 | 1880 | 16 Photovoltaic Cells 19 Feb 2013.pdf | | 1 | 0 | 0 | NULL | 0 | a:2:{s:12:"printheading";i:0;s:10:"printintro";i:0;} | 0 | 1 | 1361353596 |

157 rows in set (0.38 sec)

Here I've truncated the output for brevity, hence the ellipses. You'll note that there is embedded HTML and JSON data in the table, so some postprocessing would be necessary if we were to attempt to feed this automatically into our MEGS-KT website.

Moodle logging of resource usage

What does the system log tell us about a particular resource? As an example, let's pick the downloadable circuit simulator resource from 12ELC022. Moodle's "log" table records pretty much every interaction with the software. Here's what it looks like:

mysql> describe log;

+--------+---------------------+------+-----+---------+----------------+

+--------+---------------------+------+-----+---------+----------------+

| id | bigint(10) unsigned | NO | PRI | NULL | auto_increment |

| time | bigint(10) unsigned | NO | MUL | 0 | |

| userid | bigint(10) unsigned | NO | MUL | 0 | |

| ip | varchar(45) | NO | | | |

| course | bigint(10) unsigned | NO | MUL | 0 | |

| cmid | bigint(10) unsigned | NO | MUL | 0 | |

| action | varchar(40) | NO | MUL | | |

| url | varchar(100) | NO | | | |

+--------+---------------------+------+-----+---------+----------------+

10 rows in set (0.00 sec)

Doing a little digging, we can see the pattern of interactions with this resource, including initial checking and updating by the course tutor - as per below. In case you were wondering how it came to be that the resource was already associated with the course, this is because we normally carry over resources from one academic year to the next. Carried over resources are normally hidden from view so that the course tutors can review them and decide whether to re-use, update or replace them before the course commences.

mysql> select from_unixtime(time),module,action,url,info from log where course=1880 and info like '%142046%' order by time;

+---------------------+----------+------------+------------------------------------+-----------------+

+---------------------+----------+------------+------------------------------------+-----------------+

[...]

+---------------------+----------+------------+------------------------------------+-----------------+

122 rows in set (0.01 sec)

What's in a number?

You'll recall that our course had 157 resources associated with it. We might ask how many times these resources were viewed:

mysql> select count(info) from log where course=1880 and module='resource' and action='view';

+-------------+

| count(info) |

+-------------+

| 1476 |

+-------------+

1 row in set (0.01 sec)

But how many times was each resource downloaded? We can get MySQL to tot up these figures automatically for us:

mysql> select info, count(info) from log where course=1880 and module='resource' and action='view' group by info;

+--------+-------------+

| info | count(info) |

+--------+-------------+

| 123955 | 9 |

| 123957 | 73 |

| 137542 | 11 |

| 137543 | 5 |

| 137550 | 18 |

| 137551 | 11 |

| 138383 | 22 |

| 138385 | 8 |

| 138389 | 1 |

| 138390 | 10 |

[...]

| 183472 | 2 |

| 185640 | 2 |

| 185643 | 8 |

| 185647 | 5 |

| 185651 | 3 |

| 186051 | 3 |

| 186053 | 4 |

| 187018 | 3 |

| 187021 | 7 |

| 187210 | 1 |

| 187212 | 9 |

| 187213 | 6 |

| 187216 | 5 |

+--------+-------------+

157 rows in set (0.02 sec)

But wouldn't it be more convenient to just get, say, the details of the top 5 resources, and to be able to refer to the course by name rather than internal Moodle identifier? We now have enough info to be able to do this:

mysql> select log.info, count(log.info) as occurrences, resource.name from log,resource,course where log.course=course.id and course.shortname='12ELC022' and log.module='resource' and log.action='view' and resource.id=log.info group by info order by occurrences desc limit 5;

+--------+-------------+--------------------------------------+

| info | occurrences | name |

+--------+-------------+--------------------------------------+

| 142046 | 118 | Coursework Part 1 Instructions.pdf |

| 147356 | 93 | Coursework Part 2 Instructions.pdf |

| 123957 | 73 | Downloadable copy of this simulator |

| 170121 | 71 | Lecture Timetable |

| 176417 | 63 | 6 Rectifiers and RMS 13 Nov 2012.pdf |

+--------+-------------+--------------------------------------+

5 rows in set (0.02 sec)

Conclusions

In this post we have seen how to programmatically generate a list of resources associated with a Moodle course, and extract some statistics on usage and "hot" resources that are particularly popular.

The Moodle log table also includes the user's internal Moodle ID, and the IP address that requested the logged operation. This is more typical fare for Learning Analytics projects, and will be discussed in a future Learning Analytics post.

Ideally Moodle resources would have Dublin Core style metadata, with classification schemes, long descriptions, author information, copyright disposition and so on. This would make it feasible to automatically produce a feed (perhaps an RSS feed) of popular resources that had been copyright cleared and released for distribution as Open Educational Resources.

In terms of the MEGS-KT project, this feed could in turn be a data source for our website. The lack of metadata from Moodle means that we are some way away from being able to achieve this goal, but we have demonstrated a method to flag resources of potential interest.

I hope you will find this blog post of interest - if you are also looking into Learning Analytics with Moodle, do leave a comment with any thoughts you may have on this work.

Digital Futures ~ Martin Hamilton

MEGS-KT: Learning Analytics with Moodle (Jisc project)

No comments:

Post a Comment