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?


We’re fast approaching the half-way mark of my ‘Gamified vs non-gamified‘ Moodle course experiment. At week 5, students have met their first interim deadline of completing the ‘Investigate’ part of the course. This week I am comparing activity completion rates.

Activity completion

I started gamifying my course in a bid to increase engagement with Moodle, as my course is full of really useful organisational tools (or at least I think they’re useful…). There are a few ways to measure engagement with a VLE/LMS, and this week I decided to have a look at activity completion rates. Here is a visual representation of the activity completed/not completed by both groups of students. 

Activity completion rate for students enrolled in a gamified Moodle courseActivity completion rate for students enrolled in a non gamified Moodle course
Click here for full size imageClick here for full size image 

Activity completion gamified vs. non-gamified Moodle course

Activities completed481438
Activities not completed186229
Completion rate72%66%

Activity completion statistics – bold is better

You can see at first glance that there are some disparities between the two courses. Here are some conclusions.

No huge differences

As you can see from the visualisations, although there are some differences between the two courses, it isn’t exactly a void (+10% in favour of the gamified course). Aside from 2 or 3 students, most have been doing their work relatively conscientiously. Most of the activities that have not been completed are optional and might be completed later in the course – full statistical analysis will be available in early July 2013.  

Gamified students complete more activities

Nothing much to say here. Students enrolled in the gamified course have an average completion rate of 72%, whilst students enrolled in the non-gamified course have an average completion rate of 66%. 

Gamified students complete more meaningful activities

This is where things start to get interesting. Students were encouraged to share their first Scratch creations with their peers using the database, and also to attempt quizzes on Scratch blocks. None of these activities were compulsory but strongly encouraged.

46% of students in the non-gamified course completed the Scratch database activity vs. 83% for the gamified course. If you are equally mathematically challenged as I am, that’s almost twice as many students completing the task. This activity is best witnessed in the classroom as students download each other’s creations and tinker with it – a remixing of sorts.

Similar findings for the quizzes, although the difference isn’t quite as striking (see visuals for the full picture). More students in the gamified course have attempted quizzes than in the non-gamified course.

Gamified students try harder to complete activities

Not only did more students in the gamified course complete the database activity, they also shared more creations (70 vs. 51). They also attempted the quizzes more times (112 vs. 79).

Gamification is no cure for disappointment

I am disappointed that so few students read the level descriptors for the task. It *could* be that students made use of the level descriptor displays in my classroom, although from my observations I highly doubt that.


As explained in a previous post, I have gamified my course using badges. Only the students enrolled in the gamified course are able to unlock badges, but it is possible to look at what badges students in the non-gamified group would have unlocked, looking at the activity completion report, and a bit of Excel trickery. Here is what it would look like:

Badges unlocked by students enrolled in a gamified Moodle courseBadges that would have been unlocked by students enrolled in a non-gamified Moodle course
Click here for full size imageClick here for full size image

Students enrolled in the gamified course have unlocked 87 badges, whilst students enrolled in the non-gamified course would have unlocked 69 badges. This difference of 18 might not sound very significant, but to me it is huge, here is why. If you look closely at the graphics, you will notice that the first 2 badges have been unlocked by pretty much everyone, the first one was unlocked during the first lesson (students had to complete the activities to set up their workspace) and the second one was compulsory homework (install Scratch on home computer). The remaining 4 badges are linked to activities that students were encouraged to complete, but not forced to do (sharing Scratch animations, and attempt quizzes). It is clear that students enrolled in the gamified course took more care completing the optional activities than their non-gamified counterparts.


Last week it wasn’t so clear who was the most engaged with my Moodle course. With the numbers I have looked at this week, I can argue that the students in the gamified course are paying more attention to completing activities. I am really looking forward to the end of this unit of work to have a full picture (and *also* to enjoy the summer holidays!).

Completion rate72%66%
Badges unlocked8769
Quiz attempts11279
Scratch animations shared7051

Activity completion & other useful stats

Bold is better

Next week’s post will be more technical and I’ll share some MySQL queries to extract useful completion statistics out of your Moodle database. Stay tuned.


Week 4 of the ‘Gamify or not gamify‘ a Moodle course experiment. In week 1, I looked at early statistics and wondered what students were actually up to on the course, how they use it, what they click on the most, etc. This week, I am taking a closer look at the students’ activity around the course so far.

Gamifying a Moodle course. What difference does it make Students’ activity in the first 4 weeks of using their Moodle courses

Click here for full size

First impressions

I was rather surprised to see that students enrolled in the non-gamified course have interacted with activities & resources more than the students enrolled in the gamified course (3,634 clicks vs 3,423), as it contradicts figures gathered in week 1 of the experiment. However, the figures above only show clicks to resources & activities, and do not include visits to the course homepage, to check if badges have been unlocked, for example.

Only resources & activities3,4233,634
Including course homepage4,9044,728

Number of click (or hits) to the courses

As you can see, students who are enrolled in the gamified course have visited the course homepage more times than students in the non-gamified course. I suspect it is mainly to check whether they had unlocked badges. I am not sure whether this is positive or detrimental to the students’ learning.

Some activities have been very popular in both courses, namely the database, the checklist and the lesson. This was to be expected as students were encouraged to share at least two of their Scratch animations, based on resources available in the lesson. As they progress through the course, students are also encouraged to keep track of their progress using the checklist.


Example of a lesson in Moodle

Students were encouraged to use the links to Scratch tutorials provided in the lesson. Students in the non-gamified course used this quite a bit more (+39%) than the students in the gamified course. Students were allowed to look for their own tutorials to follow, or to re-use some of the projects they had completed in primary school. Students in the gamified course may have chosen those options over the using the tutorials provided, it is hard to tell. Based on classroom observation, both sets of students thoroughly enjoyed their lessons, whether beginner or advanced users, always good to see. 


Breakdown of clicks for each page in the lesson


Moodle database to share and peer assess

Once students have completed a Scratch animation, they are to share it with their peers using the database provided. They then have to comment on, and rate each other’s work. As you can see, both sets of students have used the database a fairly similar amount of times, with a slight edge to the non-gamified course. However, the students in the gamified course produced more Scratch animations, and of better quality (I cannot share them here, so you’ll have to take my word for it). From the quick survey I did at the beginning of the course, there was an even split of Scratch skills between both courses so I am not quite sure what to make of this data. I was not satisfied with the quality of the comments provided by the students – we clearly need to work on that.


Breakdown of activity in the database

Lectures & video help

Example of Kaltura with Moodle

As mentioned in the ‘week 2‘ post, I recorded videos explaining what the different parts of the project are about. I also recorded some videos showing how to perform specific actions, such as ‘How to upload a Scratch animation to the database?’. Those videos proved more useful than I thought they would. 

► Whole project lecture52214821
► Investigate lecture187236319
► How to share a Scratch animation89237321

Hits for each video in the ‘Investigate’ section

I am not surprised that the students in the gamified course watched the ‘Investigate lecture’ so many times, as many were struggling to understand concepts such as ‘Design problem’, ‘Design brief’ and ‘Design specification’ in the lessons. Almost all of the students needed help uploading their Scratch animation to the database, although it wasn’t especially hard – there goes the ‘kids know how to use computers, anyway‘ mantra…


Example of a checklist in Moodle

As mentioned, students are encouraged to keep track of their progress using checklists. Students in the gamified course have been more diligent than those in the non-gamified course (+25%).

Checklist visits587469

Hits to the checklist


Example of a quiz question

The quizzes in this course are not compulsory and are used to stretch those students who feel up for a challenge. Students have the choice to take the quizzes during, or outside the lessons. So far, students in the non-gamified course have attempted quizzes more than their gamified counterparts. This makes sense to me, as students in the non-gamified course have gone through documenting their work faster than the students in the gamified course (not all students, but most). This might also be due to the fact that students in the gamified course spent longer practicing their Scratch skills, sharing more of the outcomes with their peers. It will be interesting to revisit these figures when more students are done with documenting the ‘investigate’ part of this project.

Quizz hits351                          545

Hits to the quizzes

Other resources

The exemplar has been downloaded a total of 226 times, by all 46 students enrolled in the courses. I will modify the course in the future, so that this appears as a book rather than a PDF file, as clearly students do not access the file from where they have saved it, they prefer downloading it again. I would probably turn this into a lesson with questions, or use the book module.

I am not very happy that so few students have viewed the level descriptors.


From the data I have gathered, so far  having badges on the course doesn’t seem to have had much of an impact. However, it is worth noting that quite a few students enrolled in the gamified course thought that the penguins were the only thing they had to collect, and had not made a clear connection between completing activities and being awarded badges. This became clear to me when I started turning the badges into ‘house points’ (the school’s reward system), as they were asking for more points than they had gathered badges – they were asking for as many points as they had gathered penguins. If the last two sentences don’t make any sense to you, you should read the first post of this series. It is also worth noting that students enrolled in the gamified course seem to have been more diligent in their documentation of the process, and created better Scratch animations as part of their going through tutorials, although this evidence is anecdotal.

I was worried in week 1 that students in the gamified course would go on a clicking spree to get as many badges as possible – this clearly has not been the case so far.

Stay tuned to see what happens in the next few weeks, when all students have understood the relationship between completed activities/resources and awarding of badges.