Anyone who has worked on a field project in the social sector will tell you that it is extremely hard work and that it is full of uncertainty and extremely complex. They will also tell you that everyday work(contrary to popular belief) is often mundane and full of manual processes that are tedious but necessary.
We found ourselves in a similar situation while working on the Bolo English Project. The project aimed to strengthen the spoken English of children in low-income private schools across 5 states. We were working with close to 300+ schools, 5000 teachers, and 68,000 students. To do this we leveraged a mobile application on which children could take two minutes of gamified lessons and thereby strengthen their spoken English.
The program was taking place during Covid. Thus, traditional routes of field engagement were not possible. To drive engagement among students to use the mobile application; we realized that we will need to leverage data in form of weekly school reports, School Engagement Lists and Student Certificates. This allowed the stakeholders to be aware of their progress and also remain motivated enough to finish the program.
The challenge that the team faced, was towards crunching this very data into usable formats of reports and certificates. It used to take the team anywhere between 40 hours (5 full working days) hours per week to create school and teacher reports for 300 schools and 5000+ teachers. Needless to say, a lot of the program team’s time was spent crunching data vs time on training teachers or conducting Spoken English Webinars.
It was during this time that they realised that they had to find a better solution towards this. Technology has become an indispensable tool in the social sector, enabling organizations to reach more people, solve problems more efficiently, and create greater impact. Thus, we reached out to Dhwani RIS to help us create a solution for this.
We broke down the requirement into 4 requirements.
- School Reports – Showcasing the student’s progress at a teacher and school level.
- Live Dashboard – To show the overall progress of the program. It was used by multiple stakeholders such as the program team, the senior leadership team and the donors alike.
- Student Certificates – For successfully completing the program and showing progress.
- School Engagement list – To track schools’ progress across different geographies.
The program data was available to us in form of a single excel sheet that had to be uploaded with updated data. All the different analyses and visualizations had to be created with that one data source which was ever-changing. Given below is the technical approach that was leveraged to create this.
1) Program Dashboard: We implemented charts and cards using charts to show the count of teachers and students in this program and their performance statewide and grade-wise. The dashboard was made on PHP. We could have used power BI which is a commercial product that requires a subscription or license fee. PHP is a highly scalable language that can handle large amounts of data and traffic. The requirement needed us to manage high levels of data and had multiple skip-level logics and customizations. Thus, it became the natural choice.
2) School Engagement List – This list was to be used by the program team to prioritize their support to schools and teachers to drive engagement among schools. Thus, a categorization method was created for schools. Those with the highest number of students who had completed the course were put in the Gold, Silver and Bronze categories respectively. To do this analysis, we used MySQL queries which are very helpful when implementing case-wise data fetching.
3) Reports: The schools often have multiple changes that take place throughout the year. For eg: Teachers are regularly reassigned to different grades. This created an issue as it was impossible to track and update changes of 30,000 students and teachers manually every week. Thus, we created the concept of master data. We stored data in the master data table in two categories, one is the part where the primary data of a student is stored (name, email, school, teacher) and another is the variable part that changes depending upon the student’s weekly test performances. By doing this we reduced the amount of data stored and data redundancy.
4) Certifications: Certificates are provided to students who complete the required number of lessons. Students who have shown improvement are provided Merit certificates while others get a participant certificate. Around 500 certificates had to be created in one go. So for this, we implemented the CRON job(request goes to a database table having status flags, one request query served at a time according to the flag ) where we generated a request for 10 school certificates at a time. This is stacked in the database and the server runs the CRON every 10 mins and serves the pending request for certificates. The purpose of implementing the CRON job was to generate the certificates in the backend so that the user doesn’t have wait for hours to get that large amount of data and which sometimes can result in a browser crash.
Overall the technology leveraged for this program reduced manual work, improved accuracy, and allowed the team to spend more time doing program-related work. The reports that took about 5 days to create are now created in about 10 mins. Similarly, certificates that were generated over 2 weeks are now generated in 10 mins. Leveraging such a technology allows the team to reduce hours of their time to drive engagement. The program team was able to have deeper data-backed conversations with teachers, students, and school leaders. It allowed for all stakeholders involved to be constantly aware of their progress and identify strategies to strengthen impact.
Written by: Suprava Koley and Mehek Rastogi
This blog is part of our Tech Tuesdays Series curated for CTO For Non-Profits Community