How custom DataStudio Reports can contribute to building foundations for lifelong learning
Akhilesh Negi
November 3, 2021

The most sustainable way to build the foundations of lifelong learning among children and young people of today is by reigniting intrinsic motivation at all levels of an education system” – STiR Education.

STiR Education is an international NGO that supports governments to employ practical strategies that promote positive behaviors in teachers and officials. And in turn, these build the foundations of lifelong learning in children. Starting in 2012 with 25 teachers in Delhi, STiR Education over the past eight years has reached 200,000 teachers and six million children in 69 Districts across India and Uganda.

With the Global pandemic due to Covid 19, NGOs working in the education sector were deeply impacted due to restrictions and novel challenges associated with the pandemic.

Thus many NGOs adopted technology solutions to carry out their day-to-day operations and promote effective learning through digitized programs. Similarly, STiR Education adapted Glific – a two-way communication platform to digitize their program to help governments in training teachers and officials.

With Glific, STiR Education targets to run its program in 3 states namely, Delhi, Tamil Nadu, and Karnataka. The program will be focusing on mentoring and coaching in a hierarchical manner on the content of the Learning Improvement Cycle (LIC), discussing the behavioral aspects of the meeting conducted and progress checks.

Overview of the hierarchy followed in their program

For the past few months, we have been working on setting up their program by designing, creating, and customizing flows. And through beta testing along with a few pilot teachers, we have finalized the flows and have been working on the next stage of the program i.e reporting.

For reporting, we decided to use a combination of BigQuery which is used in Glific for data warehousing for an organization, and DataStudio because of its seamless integration with BigQuery. 

As the reports need to be sent on a monthly basis and each report vary depending on the teacher’s/official’s role and district we came up with the below plan 

  • Identifying and creating data points within flows
  • Filtering data specific to teacher/official
  • Generating custom reports
  • Sending reports on a monthly basis

Identifying and creating data points within flows:  For data points, we used a combination of four things in Glific, namely Flow Results, Flow labels, Custom Webhooks, and Contact Fields.

  • Flow Results: responses stored as the user traverses through the flow stored separately in the database in table flow_results
  • Custom-Webhooks: to process response and return results based on responses that are further used down the flow
  • Contact Fields: to store details unique to a user like a role, district, name, priority
  • Flow labels: to label any incoming or outgoing message 

With flows in place, we updated flows with data points that can be captured as teacher/official traverse through flow as part of their meeting or feedback.

Filtering data specific to teacher/official 

For generating custom reports we first needed to filter data based on teacher attributes like teacher role, district, priority chosen to work on, and various other criteria, After researching for a suitable tool we decided to use the parameterized query in DataStudio,  which will take their phone number as a parameter, pass it in the URL for the report and run query with passed parameters in URL in a BigQuery instance to fetch data.

Thus a query to fetch data from the survey flow mentioned above and calculate average response weightage across teachers of the same district in BigQuery would look like

Generating custom reports:  Thus with a custom query, data specific to a teacher/official is filtered that can then be converted into visually appealing graphs using DataStudio.
Two main advantages of using parameterized query with DataStudio that suited our needs were:

  • Dynamic Reports which change based on the parameters passed. Thus a single report gets updated in real-time with changes in parameters. Making the same report reusable with new sets of parameters.
  • Real-Time Updates are just magical! Thus as soon as the survey is filled through WhatsApp, the data is stored in Glific first and then synced to BigQuery in the next minute. Once data has been updated in BigQuery, the reports get updated automatically

Sending reports on a monthly basis: The schedule for sending the monthly report consists of four parts:

  1. Making reports available from the first of the month and checking if teachers/officials request for a report on the first of every month
  2. Sending nudge messages to remind availability of a report on the 2nd of every month to the remaining teachers/officials who have not requested.
  3. A waiting period of 3 days till 6th of every month to measure the impact of nudge messages
  4. Sending reports to all remaining teachers/officials on the 6th of every month.

For sending these periodical reports and reminders, we used Glific Triggers that allow sending recurring messages at a scheduled time to a collection of teachers/officials.

Triggers were used in two parts:

  • For sending nudge messages in the form of pre-approved HSM templates, which are basically highly structured messages through flows.
  • For sending monthly report links, we used a flow that periodically sends report links to teachers/officials in the form of a message based on the collection they are in. The report links generated are dynamic in nature and get updated with the phone number of teachers/officials from flows when sent a message.

That was a short journey of how we leveraged DataStudio and BigQuery to send monthly reports from Glific to teachers/officials enrolled in their STiR program.

Now teachers/officials can work on their priorities over a month and fill surveys through WhatsApp on their phone and the reports are generated and delivered at the start of next month with all the metrics to help them evaluate the trends of the program.