The post outlines the process of how to build an enhanced analytics tool for FrogLearn which captures which sites individual users visit, when they last visited the site and it allows you to call this data easily in a dynamic Google Sheet. I hope that this post will be of use to schools who wish to enhance the tracking and impact data surrounding their use of FrogLearn. I have outlined the reason why I developed this tool in this post.
I would like to thank Graham Quince who visited Dixons Allerton Academy and showed me a really neat bit of code to call FrogLearn APIs within a site as this code made the project possible. Thank you to Chris and Luke from FrogLearn as well as John from Ashington who helped me to debug a problem that I had during development.
I have often required more detailed analytics than what FrogLearn’s native analytics tool can provide. For example, I have needed to know the number of times that a particular student has visited a particular page. Class teachers have wanted to know which members of certain classes have viewed particular pages. Moreover, subject and senior leaders have wanted to know which students have visited particular sites. As the person responsible for E-Learning within my Academy I consider it a professional duty to know if scholars in Year 11 access resources, and whether students who use resources have enhanced performance in that subject. If we, as an Academy, are to continue with having a VLE it is important to know to what extent it is enhancing the outcomes of scholars. It has always been my philosophy if any technology can’t prove that it enhances the outcomes of students, then it doesn’t have a place in schools.
At present, Frog’s native Analytics tool can produce statistics on how often users login to Frog and a generic hitcount on the number of times that a particular site has been accessed. This data was not very useful when I needed to see if specific Year 11s who accessed the Year 11 English Site on our Year 11 revision centre (see previous post here) were making accellerated progress. Therefore, it was time to set about on designing an inhouse analytics tool that would give me data on how often students visited particular sites and when they last accessed them. I could then use this data in an analysis of student progress. If you want to build a similar analytics tool I’ve outlined a guide below.
Step One – Create a Google Form
Your first step is to create a Google Form. This is fairly easy to do, just head to https://forms.google.com and create a form with a Google Account. You need to create a form with five short text answer questions. Click the cogwheel at the top and ensure that anyone can submit an answer to your quiz. This form will be used to capture data on all site visits so it is important that anyone can submit so that all data is captured. The form can capture data on mutliple sites so you may wish to create a form to track a range of sites with similar purposes as the data is deposited into one tracking spreadsheet. For example, a form (with a linked spreadsheet) to track accesses to staff sites, Year 8 sites, Year 11 sites, Post-16 sites etc.
Ensure that your questions have the following titles:
- Question One – Username
- Question Two – Site UUID
- Question Three – Access
- Question Four – Code
- Question Five – Username and Subject
Click ‘Send’ once the form is complete and click the link icon. Make sure that you keep note of the link. It will be needed for the next step.
Step Two – Turn on an API and make an API call
You are now ready to modify and insert the code at the end of this step on FrogLearn sites. The code places a hidden version of your new Google Form on Frog sites which automatically records that a user has visited your FrogLearn site, along with their details, as soon as they open up the site. However, you will need to make a few modifications to the code and FrogLearn before starting:
(i) Search for ‘Groups and Policies’ in the bar at the top of FrogLearn and, in turn, select all major profiles, including students, staff and parents. When a group is selected click ‘Policy’ -> ‘Frog Developer Platform’ ->’Users’ and turn on the ‘Use get users API’ and save your new settings. Please note, that you only need to turn this API on once and it will stay on unless you turn it off.
(ii) Open the Google Form link generated in step one, right click on the page and click ‘view source’. Using CTRL+F5 search for “entry.” (without the quotation marks). You should get five numbers from four ‘entry.’ marks. These are the unique codes used to submit data into your Google Form. Make a note of each number following the “entry.” in the code. You will need all five codes to submit data from Frog into Google.
(iii) You will need to customise the code below:
var user = FrogOS.getUser();
var identify = user.attr(‘username’);
var number = Math.floor(Date.now() / 10000);
var subject = ‘nameofsite’;
var usersite = identify + subject;
var uniquecode = identify + number + subject;
$(“#nameofsite”).html(“<iframe src=https://docs.google.com/a/dixonsaa.com/forms/d/XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/formResponse?entry.num1=” +identify+ “&entry.num2=nameofsite&entry.num3=1&entry.num4=” +uniquecode+ “&entry.num5=” +usersite + “&submit=Submit width=0 height=0 border=0></iframe>”);
Firstly, you need to replace the string of XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX marks with the unique identifier of your Google Form. Open the Google Form link that you created in step one and copy down the Form’s unique ID. This will be an alphanumeric sequence which appears after /forms/d/ in the URL of your Google Form but before /viewform. Copy the string over the Xs in the code above. Then replace num1, num2, num3, num4 and num5 with the five numbers that you obtained when you searched for the ‘entry.’ codes at the start of this process.
Finally, you will see that ‘nameofsite’ appears three times in the code above. You can replace this in a text editor using find and replace. You must replace the code with a unique code for each site that you wish to use this code on. A good way to do this is to use a very specific code that is unlikely to be used again. For example y11spanishgrammarsite1 would be a good code. Write down this unique code, it will be needed at the very end once again!
Step Three – Place your code
Now, that you have generated your code you can place it on a Frog site. You will need to have the HTML widget turned on, which (if you have not done so already) requires you to request that this is turned on at firstname.lastname@example.org. Just paste the code that you generated above into a Frog site within the HTML widget. If you want to collect data on multiple Frog sites change your unique code (the part of the code which was ‘nameofsite’) with a new unique identifier for each site.
Step Four – Create a spreadsheet (your new Analytics tracker)
Go back to your Google Form, and click ‘Responses’. Then click the Google Sheets icon to create a new spreadsheet and then follow the instructions to create your spreadsheet.
Step Five – Finish your spreadsheet
In your spreadsheet you should have five columns as below:
|Timestamp||Username||Site-UUID||Access||Code||Username and Subject|
Rename this sheet “RawData” (without quotation marks) by right clicking on the cell and clicking “Rename…”
Create a new sheet by clicking “+” and name it after the data that you are trying to collect. For me, it was data on usage of Year 11 sites so I called it ‘Year 11’.
Place the usernames that you wish to track in the first column of your new sheet. You can export a list of usernames from the users tool in Frog. Just type ‘Users’ in the FrogLearn search bar and click on ‘Users’, then the cogwheel and export.
The column to the right can record accesses to any site tagged with a unique code generated at the end of step two. Just place the formula below in cell B1 and use the column painter (bottom right of cell) to drag it down alongside all of the usernames in column A.
This will look to the cell on the left (starting with A1) and note the number of times that this user has accessed the site. Where the formula states SITECODEHERE just write the unique site code you created at the end of step two.
Go to column C and use this formula in cell C1:
This will tell you the last time that a user in colomn aaccessed the site. Once again, use the formula painter to drag it down to cover the length of the list of usernames. Once again where the formula states SITECODEHERE just write the unique site code you created at the end of step two.
Now, you can adapt the formulas above to track the number of times any user visits a particular site and when they last visited the site. Really handy when you’re trying to track usage and carry out detailed analytics!
Next I intend to write a post on using Google Sheet Query URLs to be able to pull data and create a full analytics tracking database from the data that you gather.by