You are currently viewing Real-time project monitoring and visualization

Monitoring with Google Sheets

SurveyCTO allows you to publish incoming data directly from the server to Google Sheets (formerly known as Google Spreadsheets), so you can create dashboards that present up-to-the-minute summaries of your data. You can then use these dashboards to monitor the progress of your surveys, share real-time results with your team, and more.

We like Google Sheets for many reasons, because Google has expended considerable effort in making Sheets powerful, reliable, and an excellent collaboration tool. A lot of research, M&E, or data collection work is done as part of a team, and it can be incredibly valuable to have a central place where team members can all easily view progress and survey results. Google Sheets make that easy.

But project managers already have a lot to take care of, so we thought: why not create a monitoring dashboard template and share that with our users, so that they just have to edit it to fit specific fields in their survey forms? So that’s exactly what we did.

Actually, there are three templates we’re sharing here: a sample survey form, a sample dataset that publishes data from this form into a spreadsheet in Google Sheets, and a sample spreadsheet that shows some examples of tables and charts that might be used in a dashboard.

Sample Survey Form (click to download definition)

Sample Monitoring Dataset (click to download definition)

  1. The sample form is a short survey to collect data on whether patients in a particular region are able to seek health-care when needed and, if so, from which health facilities. The first part of the form also contains questions to track unsuccessful attempts to survey the respondent.
  2. On the server, we linked this form to a dataset, so that selected data collected using this form is published into the dataset as it is uploaded. The dataset, in turn, is linked to our spreadsheet in Google Sheets. As data is published into the dataset, this dataset publishes that data out to the spreadsheet.
  3. The spreadsheet contains three tabs. All raw data is published into the first tab. For easier reviewing, all summary tables and charts to monitor survey progress are contained in the second and third tabs respectively; the formulas in these second and third tabs just refer to the raw data in the first tab. As new data is published to the spreadsheet, the summary tables and charts are automatically updated. All you need to do to monitor progress is to review the dashboards in the second and third tabs whenever you want.

What summaries can this spreadsheet template provide?

The current template focuses on providing summaries so that you can easily monitor your team’s progress: a table that lists all respondents, the total number of times each respondent has been visited, and whether the respondent has been successfully surveyed; a table that shows the mean duration of surveys by enumerator; a chart that tracks completed surveys by enumerator and compares them to unsuccessful visits by enumerator; and a chart that summarizes the reasons a respondent visit was unsuccessful:

Monitoring with Google Sheets

Tables for Monitoring

However, the possibilities for dashboards and visualization are endless. You can create more complex summaries and tabulations in your spreadsheet – summarizing answer distributions for different multiple-choice questions, means of various numeric fields, tables that flag patients that missed their last required visit to a health facility, and more. The template is just meant to be a starting point, to give you an idea of the possibilities. You can edit, add, or remove elements as you require.

For those interested in taking a closer look, click here to see the live spreadsheet in Google Sheets; click “File…Make a copy” to make an editable copy within your own Google Drive, to use it as a template. Also, click here to download the sample form itself, which includes a number of comments to help explain the more complicated bits; upload that to your SurveyCTO console’s Forms tab to try it out. And, finally, click here to download the dataset definition, which takes incoming data from the sample form and publishes it to Google Sheets; unzip to get the .xml file, click “Create dataset” on your SurveyCTO console’s Datasets tab, say that you have a definition to upload, and upload the .xml file. If you set up the sample form and dataset, you’ll need to re-link the dataset to your own copy of the spreadsheet for it to publish to the right place. See the Datasets section of the SurveyCTO help for details.

UPDATE: read more about how to use Google Sheets for real-time M&E here in this newer post!