How BigQuery and WhatsApp is helping teachers with their students report in Haryana
Akhilesh Negi
October 17, 2021

With Covid-19 and the global pandemic surge, NGOs faced major challenges in the delivery of their programs and carrying out their operations which gave rise to many platforms that can help them with their last-mile deliveries and digitalize their programs.

That’s how Glific was initiated to bridge the communication gap through a two-way communication platform using WhatsApp to help NGOs automate their conversations to effectively carry out their program with significantly fewer staff members.

Another group of people that recognized novel challenges associated with pandemic was Avanti Fellows, who developed their open-source tool named Plio that allows any educator to convert any YouTube video into interactive lessons and gather data based on user engagement, thus converting a video into a much more interactive and insightful way of learning.

Plio is an exceptional tool in the education sector where NGOs can use it to create educational content for students on a daily basis like using youtube videos and adding quizzes in between to enhance the learning experience. 

With 6000+ registered users as part of their at-home learning program through Plios, Avanti Fellows started with the next phase of their program which was to provide teachers with student learning reports to help them measure the effectiveness of their Plios.

With Glific, Avanti fellows envisioned sending reports with the metrics like average accuracy percent of the response, average watch time of videos, school details, plio details, etc., to teachers on a daily basis through a WhatsApp message.

Through our calls and discussions, we came up with the below plan

  • Gathering data for the reports
  • Generating reports that need to be sent to each teacher
  • Fetching reports in Glific and processing it
  • Sending reports on a daily basis over WhatsApp

Gathering data for the reports

To gather data for reporting we first needed to identify the data points for each Plio that needed to be collected for the final reports like the number of students who watched the Plio, the number of questions answered, view time of Plio for each student.

With data points identified, next, we needed a data warehouse to accumulate the data associated with each Plio over time. The Plio team was already using Google BigQuery for data warehousing. Thus at the end of each Plio, key data points are captured and synced in BigQuery.

Generating reports that need to be sent to each teacher 

As data is stored in BigQuery, Avanti Fellows Team worked on generating reports through BigQuery Routines to process data stored across the BigQuery dataset and cumulate it in a single table named plio_summary_stats on a daily basis.

Fetching reports in Glific to send via WhatsApp

As BigQuery is supported in Glific to periodically store data to help NGOs generate their custom reports, we at Glific used the same credentials that are used in the Avanti Fellows Glific instance to fetch the generated reports from the plio_summary_stats table in their Plio BigQuery instance with the help of flows.

The flows were then updated to invoke custom webhook through the Call-a-function node in Glific Flow-Editor.

We used two Call-a-function nodes:

  • Fetch reports: to fetch reports from Plio BigQuery instance corresponding to each teacher’s phone number and merging them into simpler units for the ease of process it further down the flow
  • Process reports A custom function that processes reports one at a time that will be sent to the user.

Sending reports on a daily basis over WhatsApp

  1. To periodically send reports on a daily basis we first created the above flow which at the end of the process_reports node sends a template message with fields as the Plio report returned from the previous node
  2. Next, we used Glific Triggers that allow sending recurring messages at a scheduled time to a collection of teachers.

Thus with the help of triggers, analytics_reports flow starts at the scheduled time, fetches the report, processes it, and sends it to all the teachers in Haryana Sankalp Teachers collection in the form of a Whatsapp message

Next,  Avanti Fellows are working on generating visually appealing reports in the form of  PDFs, covering a detailed analysis of the plios that are sent to students. These reports will then be uploaded to their Google Cloud Storage bucket automatically on a daily basis.

These stored reports will then be fetched from Glific and sent in the form of a template message with a report as an attachment via WhatsApp on a daily basis.