How BigQuery and WhatsApp is helping teachers with their students report in Haryana

Akhilesh Negi

OCTOBER 18, 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 7000+ registered users, Plio is actively being used by 2 other NGOs apart from Avanti Fellows.

With Glific, Avanti fellows envisioned to send 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 to help them measure the effectiveness of their Plios.

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 report 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 report 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.

Leave a Reply

%d bloggers like this: