Moodle Analytics Explained
Moodle analytics provides a powerful tool for educators to improve teaching and learning outcomes by leveraging the vast amounts of data generated by the Moodle LMS. It allows educators and administrators to track and analyze student performance data in Moodle, an open-source learning management system (LMS). Moodle Analytics provides insights into student engagement, progress, and performance, which can help educators and administrators make data-driven decisions to improve student outcomes.
Moodle provides a simple interface to generate reports In this blog, we will cover the following:
- How to generate reports that can be used, with metrics, to assess the quality of your learning platform
- Using the Ad-hoc database query plugin to investigate selecting data from the database
- Understanding how queries should be optimized to prevent database stress How to generate Excel reports from selected data
- Creating a novel bubble chart report block to better visualize learner engagement
Let’s start our investigation by understanding the structure of the Moodle database; this is best done by interrogating it for some relevant data. If you want to learn about Moodle architecture please read Moodle in a Nutshell blog
Reporting
A great place to begin learning how data reports can be developed is with the Open University’s Ad-hoc database queries plugin, available from the Moodle plugin directory at https://moodle.org/plugins/report_customsql. As this plugin essentially lets us query the Moodle database in any way we choose, along with providing the functionality to schedule report generation (including saving reports as a CSV file and emailing them out to a chosen recipient), it makes sense to use this plugin as the foundation for any reporting plugin we develop. To that end, rather than downloading the code from the plugin directory, we can fork it in GitHub. Go to https://github.com/moodleou/moodle-report_customsql and click on the Fork button
You can now clone your fork to your development machine. Moodle reports are copied into the /report folder:
Report plugin structure
Expand the /customsql folder, and you will notice that a report plugin follows the general Moodle plugin structure:
The full details are given in the developer documentation at https://docs.moodle.org/dev/Reports. The /settings.php script adds a link to the report to the site administration menu:
$ADMIN->add('reports', new admin_externalpage('report_customsql',
get_string('pluginname', 'report_customsql'),
new moodle_url('/report/customsql/index.php'),
'report/customsql:view'));
The /index.php script is the report itself. As this is a more complex report, the report is displayed to the user via the /view.php script. All other scripts are standard (such as language support, supporting PHP classes, and versioning information).
Grabbing data
The ad-hoc database query plugin is supported by a great many reports (essentially, SQL queries) contributed by the Moodle community and freely available from https://docs.moodle.org/31/en/ad-hoc_contributed_reports. Here’s the complete query:
SELECT c.fullname, COUNT(ue.id) AS Enroled
FROM prefix_course AS c
JOIN prefix_enrol AS en ON en.courseid = c.id
JOIN prefix_user_enrolments AS ue ON ue.enrolid = en.id
GROUP BY c.id
ORDER BY c.fullnameAs we are querying the database directly, we do need to pay attention to the efficiency of the database we are querying. Although databases are specifically designed for the efficient extraction of data, as more data is added, and as datasets are updated (and data is always being added and updated on a busy Moodle site), databases can very quickly become inefficient. Of course, the Moodle database is specifically designed to support the efficient day-to-day operation of a Moodle site.
Determining certificates issued
The following is a basic query that, given a user’s email and a time frame, selects a list of courses for which certificates have been issued:
SELECT
user.email AS email,
course.fullname AS coursename,
certissues.timecreated AS timecreated
FROM mdl_user AS user
INNER JOIN mdl_customcert_issues AS certissues ON user.id =
certissues.userid
INNER JOIN mdl_customcert AS customcert ON certissues.customcertid =
customcert.id
INNER JOIN mdl_course AS course ON customcert.course = course.id
WHERE user.email = "ian@example.com"
AND certissues.timecreated>=0 AND certissues.timecreated<=1495781129
As described, we need to ensure the efficient selection of data, but how can we do this? In the following section, we will learn how.
EXPLAIN a query
A MySQL database has a built-in query optimizer which, when you issue a SELECT query, will attempt to extract and present the required data as efficiently as possible. We can investigate the optimizer’s query plan by prefixing the query with EXPLAIN. This function is easily accessible from the phpMyAdmin tools. For example, here’s the query to determine the number of certificates issued, which has been explained by prefixing it with the EXPLAIN keyword:
In order to speed up the extraction of data from a table, a key is used. If the data is spread across a number of tables, or needs to be sorted in some way, MySQL may as well create a temporary table. this can impair database performance as MySQL writes out to, and reads from, yet another table. Clearly, having access to this information means that we can construct more effective queries.
Having extracted data, the next problem is what to do with it. An obvious solution is to save it to a file, the options for which we will explore in the following sections.
Saving data
A new Ad-hoc database query has been configured to allow the currently logged in user to see which courses they have completed:
The Ad-hoc database queries plugin provides an option to output selected data to a Comma Separated Variable (CSV) file:
The CSV file is output directly to the browser from the /customsql/download.php script. Moodle does, however, possess its own internal API for generating standard-format data output files, including CSV files, based on the PHPExcel library. Let’s investigate this library now.
Creating spreadsheets
If we are wanting to output Excel, ODS, or CSV spreadsheet-based reports, the PHPExcel library is the most straightforward way to create them. The library is accessed via an internal API called PHPExcel. Let’s update the Ad-hoc database queries plugin to allow the user to download Excel files as well as CSV files:
The fully implemented code is available at https://github.com/iandavidwild/moodle-report_customsql. This is a forked copy of the original Ad-hoc database query code at https://github.com/moodleou/moodle-report_c ustomsql. The modifications described in this section are in the master branch.
When the Ad-hoc database query plugin runs a report, the data generated is written to a temporary CSV file. We can direct the PHPExcel library to load this file:
list($csvfilename) = report_customsql_csv_filename($report, $csvtimestamp);
if (!is_readable($csvfilename))
{
print_error('unknowndownloadfile', 'report_customsql',
report_customsql_url('view.php?id=' . $id));
}
$objReader = PHPExcel_IOFactory::createReader('CSV');
$objPHPExcel = $objReader->load($csvfilename);
Then, we can write an excel file straight back out to the browser:
$filename = 'results';
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=$filename.xlsx");
header("Content-Transfer-Encoding: binary ");
$objWriter = new PHPExcel_Writer_Excel2007($objPHPExcel);
$objWriter->setOffice2003Compatibility(true);
$objWriter->save('php://output');
Displaying activity data – a bubble chart example
As a way of gauging the success of the online resources, our resilience project sponsors are wanting to report on the number of interactions users have with particular resources and activities. They want to be able to see, at a glance, which resources have been the most popular. To this end, a number of chart types were suggested, but the one that appealed the most is the bubble chart.
The more popular the resource or activity, the bigger the bubble, which means you can gauge the popularity (or otherwise) of resources and activities a course includes at a glance. A wireframe has been produced, which shows a course with a block (which is only visible to teachers) listing different report types:
Each report will open a full-page graphic. For example, here’s the report showing a bubble chart, as described earlier:
Accessing the report
This client has requested that the report has the following properties:
- Easy access from a course page
- Only available to users with the correct permissions
The completed block is available from GitHub at https://github.com/iandavidwild/moo dle-block_course_report. Having agreed to the minimum functionality and having confirmed the user interface with the client, let’s start creating our new block.
Building the block
By now, you should be familiar with the structure of a Moodle plugin generally, and Moodle blocks in particular from the earlier chapters. We start by refactoring the HTML block. Copy the HTML block into a new folder called /course_report, and then refactor each script accordingly. Remember that using an integrated development environment such as Eclipse makes this task straightforward:
Having updated all the relevant scripts, your new block will be ready to install.
Extracting the data
The code to determine the number of times a user has interacted with a resource or activity. We will create a new script called /locallib.php and add a new function to return the number of interactions with each resource or activity.
This code can be taken from the Heatmap block code:
Let’s work through this function step by step to see how it operates. The function is passed the ID number of the course for which we need user interaction data:
function course_report_get_views($courseid)
{
global $DB;
$course = get_course($courseid);
Then, we set an error flag, assuming success at the outset:
// set an error flag - assume success until we decide otherwise $error = CR_SUCCESS;
Next, we retrieve some block global settings. This includes specifying a time window within which to grab interaction data (either all data or data from when the course started):
// Get global settings.
$activitysince = get_config('block_course_report', 'activitysince');
if ($activitysince === false)
{
$activitysince = 'sincestart';
}
$now = time();
// Check that the course has started.
if ($activitysince == 'sincestart' && $now < $course->startdate)
{
$error = CR_COURSE_NOT_STARTED;
$arguments = array(
); }
$error,
return $arguments;
}Remember that we are checking whether the course has actually started. If not, an error is returned. Note that we need to ensure that all data the function returns is consistent (hence, we return the error code in an array).
Activity interaction data can be read in two ways:
$useinternalreader = false; // Flag to determine if we should use the
internal reader.
$uselegacyreader = false; // Flag to determine if we should use the legacy
reader.
// Get list of readers.
$logmanager = get_log_manager();
$readers = $logmanager->get_readers();
// Get preferred reader.
if (!empty($readers))
{
foreach ($readers as $readerpluginname => $reader)
{
// If sql_internal_table_reader is preferred reader.
if ($reader instanceof \core\log\sql_internal_table_reader)
{
$useinternalreader = true;
$logtable = $reader->get_internal_log_table_name();
}
// If legacy reader is preferred reader.
if ($readerpluginname == 'logstore_legacy')
{
$uselegacyreader = true;
}
} }
// If no legacy and no internal log then don't proceed.
if (!$uselegacyreader && !$useinternalreader)
{
$error = CR_NO_LOG_READER_ENABLED;
$arguments = array($error, );
return $arguments;
}Having decided on how to obtain it, we need to extract the relevant data from the Moodle log:
// Get record from sql_internal_table_reader
if ($useinternalreader)
{
$timesince = ($activitysince == 'sincestart') ? 'AND timecreated
>= :coursestart' : '';
$sql = "SELECT contextinstanceid as cmid, COUNT('x') AS numviews,
COUNT(DISTINCT userid) AS distinctusers
FROM {" . $logtable . "} l
WHERE courseid = :courseid
$timesince
AND anonymous = 0
AND crud = 'r'
AND contextlevel = :contextmodule
GROUP BY contextinstanceid";
$params = array('courseid' => $course->id, 'contextmodule' =>
CONTEXT_MODULE, 'coursestart' => $course->startdate);
$views = $DB->get_records_sql($sql, $params);
}
else if ($uselegacyreader)
{
// If using legacy log then get activity usage from old table.
$logactionlike = $DB->sql_like('l.action', ':action');
$timesince = ($activitysince == 'sincestart') ? 'AND l.time >=
:coursestart' : '';
$sql = "SELECT cm.id, COUNT('x') AS numviews, COUNT(DISTINCT
userid) AS distinctusers
FROM {course_modules} cm
JOIN {modules} m
ON m.id = cm.module
JOIN {log} l
ON l.cmid = cm.id
WHERE cm.course = :courseid
$timesince
AND $logactionlike
AND m.visible = 1
GROUP BY cm.id";
$params = array('courseid' => $course->id, 'action' => 'view%',
'coursestart' => $course->startdate);
if (!empty($minloginternalreader)) {
$params['timeto'] = $minloginternalreader;
}
$views = $DB->get_records_sql($sql, $params);
}Finally, having extracted some data, we need to cleanse it into a form to be used later:
// Get the min, max and totals.
$firstactivity = array_shift($views);
$totalviews = $firstactivity->numviews;
$totalusers = $firstactivity->distinctusers;
$minviews = $firstactivity->numviews;
$maxviews = $firstactivity->numviews;
foreach ($views as $key => $activity)
{
$totalviews += $activity->numviews;
if ($activity->numviews < $minviews)
{
$minviews = $activity->numviews;
}
if ($activity->numviews > $maxviews)
{
}
$maxviews = $activity->numviews;
$totalusers += $activity->distinctusers;
}
array_unshift($views, $firstactivity);
foreach ($views as $key => $activity)
{
if($cm = $DB->get_record('course_modules', array('id' =>
$activity->cmid)))
{
$modname = $DB->get_field('modules', 'name', array('id' =>
$cm->module));
if ($name = $DB->get_field("$modname", 'name', array('id'
=> $cm->instance)))
{
} }
else {
$activity->name = $name;
$activity->name = get_string('unknownmod',
\'block_course_report');
}
}
$arguments = array(
$error,
json_encode($views),
$minviews,
$maxviews);
return $arguments;
}Having obtained the data, we now need to display it. In the next section, we investigate including the D3.js charting library in our new reporting block.
Including D3.js libraries
The D3.js libraries are available directly from https://d3js.org/. Create a new folder called /thirdparty and copy the relevant files into it. We require a new page to display the chart. In Eclipse, create a new script called /viewreport.php:
The /viewreport.php script will render the report page. The code is straightforward:
- Obtain activity data by calling course_report_get_views().
- Load the D3.js library.
3. Call on a JavaScript function to display the bubble chart by passing this function the relevant activity data.
Here’s the fragment of code that calls on JavaScript to display the chart:
$json_data = json_encode($paramdata);
$js_params = array('graph', $json_data);
$PAGE->requires->js_call_amd('block_course_report/chart_renderer',
'drawChart', $js_params);
Rendering a bubble chart
D3.js being a JavaScript library, we need to implement a JavaScript function to load when the page is loaded to create the chart.
This is the JavaScript code (an AMD module) to display the chart:
define(['jquery'], function($)
{
var t = {
drawChart: function(chartEl, dataset)
{
var dataset = $.parseJSON(dataset);
var diameter = 600;
var color = d3.scaleOrdinal(d3.schemeCategory20);
var bubble = d3.pack(dataset)
.size([diameter, diameter])
.padding(1.5);
var svg = d3.select("#graph")
.append("svg")
.attr("width", diameter)
.attr("height", diameter)
.attr("class", "bubble");
var nodes = d3.hierarchy(dataset)
.sum(function(d) { return d.interactions; });
[ 294 ]
Moodle Analytics
var node = svg.selectAll(".node")
.data(bubble(nodes).descendants())
.enter()
.filter(function(d){
return !d.children
})
.append("g")
.attr("class", "node")
.attr("transform", function(d)
{
return "translate(" + d.x + "," + d.y + ")";
})
node.append("circle")
.attr("r", function(d)
{
return d.r;
})
.style("fill", function(d)
{
return color(d.activity);
});
d3.select(self.frameElement)
.style("height", diameter + "px");
}
};
return t;
});Now, let’s include mouse hover text and a title for each bubble:
node.append("title").text(function(d)
{
return d.data.activity + ' : ' + d.data.interactions + '
interaction/s';
});
node.append("text")
.attr("dy", ".3em")
.style("text-anchor", "middle")
.text(function(d) {
return d.data.activity.substring(0, d.r/3);
});That done, the following is a screen grab of the report in action:
Further improvements
The color of each bubble is currently fixed. Take a look at the drawChart() function and see whether you can work out how to change the bubble colors.
Conclusion
We saw how data can be extracted from the Moodle database and reported directly to the screen via a downloadable file (CSV or Excel). We saw that we need to exercise care when selecting data from the database in order to prevent an inefficient query from impairing database performance. We saw that facilities are provided by MySQL to help us; for example, using EXPLAIN to show a query execution plan.
Hope this is helpful!