Week 9 of experimenting with gamified and non-gamified Moodle courses. Today marks the beginning of iMoot 2013, the Worldwide e-conference on all things Moodle, and this year there are a few presentations on the gamification of Moodle. In this post, I list all of the sessions that ‘gamifiers’ might be interested in (all session times in Perth time, Australia, or UTC+8). If you have not registered for iMoot 2013, you can do so here.

Using Gamification to Increase Course Engagement and Autonomy of Non-Traditional Online Students

Presenter: Thomas Wilson

Sessions: Friday 24th May 2013 02:00, Saturday 25th May 03:30

[quote style=”boxed”]Thomas works for an institution that provides online courses for non-traditional students, namely, persons who are ordered by a court or agency to complete a specialized class such as anger management, alcohol/drug awareness, or DUI risk education to satisfy court requirements. Since students are responding to the game elements of the courses, i.e., maximizing “points”, he decided to use this game principle to (1) keep students more engaged and (2) increase autonomy, i.e., to reduce the number of phone calls for support.[/quote]


 (click here instead – picture doesn’t work :()


Gamification in Moodle. More than just Moodle badges

Presenter: Natalie Denmeade

Sessions: Saturday 25th May 2013 14:00

[quote style=”boxed”]This presentation includes demonstrations of core Moodle features and plugins to provide learners with rewards, feedback, levels, progression loops, boss fights, and achievement badges. You will see examples of Conditional Activities, customised Progress Bars, custom scales, and the newly released Badges Block.This presentation includes demonstrations of core Moodle features and plugins to provide learners with rewards, feedback, levels, progression loops, boss fights, and achievement badges. You will see examples of Conditional Activities, customised Progress Bars, custom scales, and the newly released Badges Block.[/quote]  

Dissection of a Gamified Moodle Course

Presenter: Julian Ridden

Sessions: Friday 24th May 03:30Sunday 26th May 06:30

There is no excerpt for this session (yet) but having seen what Julian, aka Moodleman has done before, I would strongly recommend you to drop by one of his sessions.

Open Badges in Moodle

Presenters: Emily Gogligoski, Julian Ridden

Session: Saturday 25th May 05:00

[quote style=”boxed”]Hear about Open Badges from Emily Gogligosky from the Mozilla foundation, whilst Julian Ridden will talk about the implementation of Open Badges into Moodle. I am really excited about this session. This session will talk about the exciting potentials offered by OpenBadges and Moodle.[/quote]
I am *really* excited by this session; It’ll be interesting to hear about Open Badges from someone who works at Mozilla foundation.

Gamify your Moodle courses – Increase student engagement with conditional activities & badges

Presenter: Frederic Nevers

Sessions: Sunday 26th May 14:00, Monday 27th May 06:30

[quote style=”boxed”]It is possible to ‘gamify’ your Moodle courses using out-of-the-box Moodle capabilities (i.e. without relying on third-party modules). In this presentation, Fred will show you how to gamify your existing courses, using free and easy-to-use tools. He will be sharing all of the tips & tricks he has gathered building & using gamified courses, and also talk about the mistakes he has made.[/quote]

Right, I’d better go and finish preparing for my session…


I have been sick for most of this week so no massive updates. I will soon be asking my students for feedback on the gamified/non-gamified Moodle courses. Are there any questions you think I should ask? Please write your questions using the Google Moderator series embedded below. If you don’t have a Google account, please write your question in the ‘Comments’ section at the bottom of this page. If you cannot really use the Google moderator form below, please click this link. You can upvote or downvote questions. I would really appreciate your help with this.

[cjtoolbox id=”4″]

Note: For those wondering how I got Google moderator on this page, I used this and this.


It is possible to use Google Analytics to report on users’ activity on a Moodle site. This week I have decided to take a closer look at this option to compare my gamified Moodle course against the non-gamified version. This is week 7 of the ‘what difference does it make to gamify a Moodle course?‘ experiment.

Setting up Google Analytics for Moodle

There is no plugin as such to install Google Analytics on Moodle 2. As explained in this blog post, Moodle URL’s are not Google Analytics friendly and you need to apply a simple tweak to your server for it to work well. It is fairly simple to get it going following the instructions put together by Bas Brand. You can read more about Google Analytics and Moodle here


Gamified courseNon-gamified course
Gamified course - All dataNon gamified course - All data

The students enrolled in the gamified course continue to generate more pageviews than their counterparts in the non-gamified course (7,529 vs. 6,559 or +15%). The spikes represent days when students had Computer Technology lessons (the scales are different in both graphs). The most viewed pages data roughly coincide with my previous findings. Students have mainly been working on their designs lately, and have not needed to use Moodle very much, aside from updating their checklists, hence the low level of pageviews for the past 10 days or so.


Until this post I have mainly relied on the Moodle database to find out what my students are up to in my courses. Whilst it is the method of choice for this type of project as the data is extremely accurate, there are a few tools that Google Analytics offer that the database method cannot match, for example reporting on the type of technology my students use to access Moodle. 


Gamified courseNon-gamified course
Gamified course web browserNon-gamified course - Most widely used Web browsers

It came as no surprise that overall most of the visits were done so using Safari (61% vs. 47%), as my computer lab is equipped with iMacs. I am surprised with the differences between the two groups, especially considering Google Chrome (21% vs. 51%) is not installed on the school computers. Are the results ‘skewed’ as one of the top users uses Chrome? Is there someone who has influenced students not to use Internet Explorer? More investigation is needed.

It is also possible with Google Analytics to find out the browser version used (although not for a filtered set of pages), and I discovered that quite a large proportion of our students use an outdated version of their web browsers. This sort of data can be used to prompt a discussion on ‘staying safe on the Web’ with your students. 

Operating system

Gamified courseNon-gamified course
Gamified course Operating systemNon-gamified course - Operating systems

Again no big surprises here, with a vast majority of the visits done using Mac OS (76% vs. 64%). However I was surprised with how few students access Moodle using a mobile devices/tablets (less than 1% in both courses). One point to keep in mind: if you have resources that use Flash, iOS users won’t be able to access them.

Screen resolution

Gamified courseNon-gamified course
Gamified Moodle course screen resolutionNon-gamified course - Top 5 screen resolutions

Again no major surprises here as 1920 x 1080 pixels is the default screen resolution on the 21″ iMacs in my computer lab. This setting is useful when you design courses. For example, I was worried that quite a few students wouldn’t be able to view the ‘Design Cycle’ image at the top of my course as I thought it may be too wide. This clearly isn’t the case for the vast majority of my students.

Student flow


Gamified courseNon-gamified course
Navigation summary - gamified courseNon-gamified course - Navigation

The ‘Navigation summary‘ feature in Google Analytics shows the pages users visited before and after a specific page. For example, I wanted to know whether students have been using the navigation menus to access the course homepage, or whether they used ‘My Moodle‘ page, the category route or a shortcut (roughly). As I expected, most of the pageviews were generated by students who used the navigation menus and shortcuts. There seems to be quite a few students who keep using Moodle the ‘hard way’ and click on categories & sub-categories to reach courses.

Traffic sources

Gamified courseNon-gamified course
Gamified course traffic sourcesNon-gamified course - traffic sources

A majority of students accessed Moodle entering the address in their Web browsers (65% vs. 55%). A significant proportion of students searched for Moodle using their search engine (23% vs. 33%), and just over 10 % used various links to access the site (e.g. link available on the school website, in emails, etc.). I suspect the difference in keyword searches is due to more students in the non-gamified course using Google Chrome as their web browser, where the ‘address bar’ and ‘search bar’ are in the same location (the version of Safari we use doesn’t have this feature), although I cannot be sure.

Student behaviour

Hours of visit

Gamified courseNon-gamified course
Gamified course Busiest hoursNon-gamified course - Busiest hours

The busiest hours are during lesson times, not too surprised here. However, I think too many students are doing their homework too late. I am aware that students in Hong Kong are under a lot of pressure with extra-curricular activities (private tuition, etc.) but I am not happy to see 12/13 year old students working past 10pm. More investigation is needed.


Although I have found this an interesting exercise, it seems as though Google Analytics is best used on large data sets, where accuracy is not paramount and trends are more important e.g. whole Moodle course, categories, etc. Whilst it may not be completely appropriate to use Google Analytics for the experiment I have been running, I find it extremely useful as a Moodle administrator. There are some metrics that I find intriguing, such as ‘time spent on page’, and I will need to do more research on the topic to judge whether this metric, amongst others, can really be trusted. 

What are your experiences of using Google Analytics with Moodle?


Week 6 of my experiment working out to what extent gamifying Moodle courses increase student engagement with the platform. This week’s post is aimed at Moodlers who enjoy the technical side of Moodle. In this post I share some MySQL queries on activity completion that go beyond the activity completion report. You can re-use these queries directly in PHPMyAdmin, command line, or using the excellent configurable reports plugin.

Moodle URL’s 

Most queries will need information that is found in Moodle web page addresses (URL, or Unique Resource Locator) so it’s important that you understand what to look for. Each page has a unique identifier (or URL), which can be found by either hovering your mouse over the resource/activity you’re interested in (URL should show up at the bottom of your window), or by simply visiting the page and checking the address in your web browser address bar. We’re particularly interested in the ID numbers.

Moodle URL structureMoodle URL in web browser address bar
Hover your mouse over nameVisit the activity

Examples of Moodle URL’s

ItemURL example

Unix Epoch times

Moodle stores all time related data in the database as Unix Epoch time, or the number of seconds that have elapsed since 1st January 1970 at midnight UTC. For example, if a student submitted an assignment on 28th April 2013 at 6.59 am GMT time, then Moodle would store the time stamp for this event as 1367132340 in the database.

Human readable date & timeUnix Epoch time

You can find out how to convert Epoch time into human readable date/time by looking at the ‘Number of hits per student, per day’ query below, or by using this online converter.

SQL queries

Here are some of the queries I have used. I am very much a beginner with SQL so keep in mind I might not have adhered to best practices. In all queries, change ‘prefix_’ with the actual prefix for your Moodle database (more often than not, it will be ‘mdl_’) – leave as is if using the configurable reports plugin. You shouldn’t be able to break anything using this but don’t come and blame me if you do 🙂

Number of hits in a course

Number of hits in a Moodle course

This MySQL query adds up how many clicks to activity/resources have been recorded in the logs for a specific course. 

SELECT course, COUNT(*) AS hits
FROM `prefix_log`
WHERE `course` = 1693
AND `userid` != 782
GROUP BY `course`

Change the course ID to suit your needs. In this example, I have removed clicks performed by myself (userid 782) as to not skew the results. You can find your own user ID by visiting your profile and checking the URL.

 Number of hits per student

Number of hits per student in Moodle

This query calculates the number of hits each student has performed in a given course for a specific period of time, and orders the results in decreasing number of hits.

SELECT u.firstname, u.lastname, COUNT(*) AS hits
FROM prefix_log l
LEFT JOIN prefix_user u ON
WHERE `course` = 1693 
AND `time` BETWEEN 1363795201 AND 1366819199 
AND `userid` != 782
GROUP BY `userid`

To customise, modify ‘course’ and ‘userid’, as explained for the query above. You can also change the Epoch values to adjust the period of time you are interested in.

Number of hits per student, per day

Hits per student per day in Moodle

This query calculates the number of hits students have performed each day in a given course. The results are ordered chronologically. 

SELECT, u.firstname, u.lastname, DATE(FROM_UNIXTIME(`time`)) AS `Date`, COUNT(*) AS hits 
FROM prefix_log l
LEFT JOIN prefix_user u ON
WHERE `course` = 1693
AND `userid` != 782
GROUP BY `userid`, DATE(FROM_UNIXTIME(`time`))

Number of visits to resources/activities

Number of visits to Moodle resources & activities

Calculates how many times all resources & activities have been visited in a given course for a specific period of time.

SELECT COUNT(cmid) AS hits, cmid, module  
FROM prefix_log 
WHERE `time` BETWEEN 1363795201 AND 1366819199 
AND `userid` != 782 
AND `course` = 1693 
GROUP BY `cmid` 

To customise, see ‘Number of hits per student’ query above.

Number of visitors to resources/activities

Number of visitors per resource & activity in Moodle

Works out how many students have visited resources & activities at least once in a given course, for a specific period of time.

SELECT COUNT(Distinct userid) AS hits, cmid, module 
FROM prefix_log
WHERE `time` BETWEEN 1363795201 AND 1366819199 
AND `userid` != 782 
AND `course` = 1693
GROUP BY `cmid`

To customise, see ‘Number of hits per student’ query above.

Number of hits during lessons

Number of hits during lesson time

This query works out the number of hits performed during lesson time. I can then subtract it from the ‘Hits in a course’ query to find out the ratio of hits in/outside lessons.

SELECT course, COUNT(*) AS hits
FROM `prefix_log` 
(`time` BETWEEN 1363923300 AND 1363926600)
OR (`time` BETWEEN 1364190300 AND 1364196900)
OR (`time` BETWEEN 1364362800 AND 1364366400)
OR (`time` BETWEEN 1365564000 AND 1365567000)
OR (`time` BETWEEN 1365658800 AND 1365665700)
OR (`time` BETWEEN 1366004400 AND 1366008000)
OR (`time` BETWEEN 1366255800 AND 1366259400)
OR (`time` BETWEEN 1366350000 AND 1366356900)
OR (`time` BETWEEN 1366695600 AND 1366699200)
OR (`time` BETWEEN 1366687800 AND 1366691400)
AND `userid` != 782
AND `course` = 1693
GROUP BY `course`

Note: this query could possibly be simplified if your lesson times are always the same every week. My timetable is an absolute mess, with a 6 day cycle with Wednesdays following a different schedule. 

Badges awarded to each student

Badges awarded to students in Moodle

This query works out what badges have been unlocked by every student in a given course. As it is a rather long query, I have attached it to a text file, that you can download here.  Here is a simplified sample:

    case BADGE_1
        when 3 then 'y'
        else '-'
    END as BADGE_1,
    case BADGE_2
        when 1 then 'y'
        else '-'
    END as BADGE_2
            sum(col14415) + sum(col14416) + sum(col14417) as BADGE_1,
            sum(col14413) as BADGE_2
            case coursemoduleid
                when 14413 then 1
                else 0
            end as col14413,
            case coursemoduleid
                when 14415 then 1
                else 0
            end as col14415,
            case coursemoduleid
                when 14416 then 1
                else 0
            end as col14416,
            case coursemoduleid
                when 14417 then 1
                else 0
            end as col14417
        completionstate = 1
    GROUP BY userid , coursemoduleid) t
    GROUP BY userid) t2
    userid BETWEEN 1223 AND 1243

I might create a short video showing how to customise this query, if there is interest. However, if you take a look at the attached file along with this sample, you should be able to work out what to do to make it suit your needs.

Note: It takes a long time to set up. 

Activity has been completed, but student has not marked it complete

Done but not marked done

This query finds all of the resources & activities that have been visited by a student, but have not been marked as actually completed by the student. A student might not have marked the resource/activity as completed for several reasons, such as forgetting to do it, not feeling confident he/she has understood the contents and would like to revisit, etc. The query creates a clickable link to the resource to help you judge the possible reason(s).

SELECT u.firstname, u.lastname, u.institution AS form, u.department AS house, l.module AS type, l.course, CONCAT('<a href="%%WWWROOT%%/mod/',l.module,'/',l.url,'" target="_new">',l.cmid,'</a>') AS module
prefix_log l
LEFT JOIN prefix_user u ON = l.userid
        SELECT  NULL
        FROM prefix_course_modules_completion c
WHERE l.cmid = c.coursemoduleid 
AND c.userid = l.userid
AND l.course = 1693
AND l.userid !=782
AND l.cmid !=0
AND l.module !="data"
AND l.module !="checklist"
GROUP BY l.userid
ORDER BY l.course ASC, u.firstname

Due to the way I have set up the conditions, it is possible that a student has visited the ‘database’ and/or the ‘checklist’ activities and the system has not yet marked it as completed. For this reason, I have removed it from the list of modules to be evaluated (e.g. l.module !=”data”)

Activity has not been visited, but student has marked it done

Marked done but not done

This query works out the opposite of the one above. Sometimes students might mark an activity as completed, even though they have not visited it. This query works out just that.

SELECT u.firstname, u.lastname, u.institution AS form, u.department AS house, concat('<a href="%%WWWROOT%%/mod/',,'/view.php?id=',c.coursemoduleid,'" target="_new">',c.coursemoduleid,'</a>') AS module, AS type, m.course
prefix_course_modules_completion c
LEFT JOIN prefix_course_modules m ON c.coursemoduleid =
LEFT JOIN prefix_user u ON c.userid =
LEFT JOIN prefix_modules p ON m.module =
        SELECT  NULL
        FROM prefix_log l
WHERE c.coursemoduleid = l.cmid
AND c.userid = l.userid
AND m.module != 9
AND m.course = 1693
AND c.completionstate = 1
ORDER BY c.userid

I have removed the ‘label’ module type from this query as it may cause problems, as a label can be marked as complete but it is impossible for a student to visit it (i.e. click on it and open a specific page for it). I probably should also be removing the ‘turnitintool’ type as well but have not done it yet.

You can find more useful Moodle SQL queries here. Are there any queries you would like to share?