After I presented at Frog’s ‘Eat, Learn and Share’ I’ve had a few e-mails asking how to carry out impact analytics in Frog. In particular, how to correlate student usage with attainment. So I thought it best to put it all down here as a reference point for others.
Step One – Sample Population
Before starting work out which students you wish to analyse. You must decide whether it students in a key stage or a particular year group that you wish to analyse. Like any analysis, knowing the specific group for the study is a msut to even begin collecting data. Once you’ve decided that load up the ‘Users’ application from an admin account. You can do this by typing ‘Users’ into the search bar in FrogLearn and waiting for the option to appear.
Once you’re here, click the cogwheel in the top right of the application and then ‘Export’
Open up the CSV file in either Excel or Google Sheets and use a filter on the top row. For this example, let’s pretend we want to analyse Year 11, so filter and under ‘Year’ only tick Year 11. Copy and paste the filtered data into a new spreadsheet and save it as ‘withname.csv’
Step Two – Admission Numbers
Copy ‘withname.csv’ and name the new file ‘withadmission.csv’. In withadmission.csv clear all cells under ‘First Name’, ‘Middle Name’ and ‘Surname’. However, make sure that you keep the column titles in Row 1 exactly the same (or they won’t import). Under ‘First Name’ paste in the data from the admission number column. However, once again, make sure that you keep the column title in Row 1 as ‘First Name’.
Step Three – Data Fiddling
Go back to the Frog ‘Users’ application, click the cogwheel, and ‘Import’. You will want to import ‘withadmission.csv’, ignore any errors and click ‘Import Users’ when prompted. Then wait for the process to complete. Then close the ‘Users’ application.
Step Four – Getting the data
Load up the ‘Analytics’ app. This can be accessed by typing ‘Analytics’ into the top search bar in FrogLearn and then by clicking on ‘Analytics’. Click ‘Users’ on the left and then click ‘User Name’ to sort users. You will see that the users you selected in Step One are all assigned their admission number as their ‘User Name’ and are grouped at the top for you. To export data from the launch of your platform click ‘Export all data to CSV’. If you set a filter by using a date range you may have the option to export filtered data. However, this option is often greyed out. If it is grey, you can copy and paste the filtered table data from the bottom into a spreadsheet. You may have to go through a few pages to get all the data. We have 240 users in a year group and it can take 12 pages to get all the data copied and pasted. You should paste the data into a sheet in a spreadsheet called ‘Frog’.
Step Five – Set Users Back
Close Analytics. Go back to the Frog ‘Users’ application, click the cogwheel, and ‘Import’. You will want to import ‘withname.csv’, ignore any errors and click ‘Import Users’ when prompted. Then wait for the process to complete. Then close the ‘Users’ application. The users you filtered will now have their names.
Step Six – Analysis
Now you can run a report in your school’s MIS. We use SIMs, as do a lot of schools, and exporting data takes a little bit of practice but it’s actually not too complicated. There’s lots of guides online and it’s always worth a chat with your Data Manager about this as they can get you up and running really quickly. For the analysis I ran a report for admission numbers of a particular Year Group with all of the achievement data from a particular date range. Once this data has been exported from your MIS, open up your exported data from Frog and create a new sheet called ‘Achievement’, and paste it in.
From here you should have two sheets, one from Frog data called ‘Frog’ and another called ‘Achievement’. If you open the achievement sheet and at the far right create a new column with the title ‘Frog Logins’ it’s quite easy to match the data. Paste the following formula into the row of the first user that you wish to analyse. You will need to edit it based on how your sheets are setup\exported.
Where Frog!C:C matches the list of logins (or any data you are trying to return from the Frog sheet)
Where A2 is the admission number in your ‘Achievement’ sheet
Where Frog!A:A is the list of admission numbers in the exported Frog sheet.
From here you have the spreadsheet you need, time to get busy with filters and data sorts to analyse your findings. A good starting point is to filter one key piece of achievement data that you wish to analyse (Progress 8, Alps, etc.) and data sort by Frog logins. If you group your students into quartiles and analyse each separately you can begin to see patterns emerge.
My next post will show you how you can link in a self-submitting Google Form to track the usernames of students who hop on external resources that you link to in your platform. The Frog APIs can be made to write to a Google Sheet using Google’s APIs.by