Deep Dive #2: Automated Supervision with Magpi

Published in

Magpi Mobile Data Collection

4 min readJun 22, 2016

Deep Dive Number Two: Automated Supervision

In our last Magpi Deep Dive we talked about using some of the advanced features of Magpi like Groups and Respond. In this one, we’ll talk about integrating Magpi with other web tools to create automated supervision processes that can drive data quality improvement.

Integrating the Web

All of us use many different cloud-based tools in our daily lives, sometimes for work and sometimes for personal activities. Tools like Dropbox, Gmail, LinkedIn, Facebook, Google Sheets (GSheets), and Microsoft Office 365 (the online version of the venerable Microsoft Office suite) let us be more productive without breaking the bank — in fact most of these services, like Magpi, are free or have a free version.

Well, now, with Zapier, normal people can connect all of these web tools without programming. The combinations — for example of Magpi and the analytic power of Google Sheets — allows for some amazing data quality activities.

Using Magpi and Google Sheets (GSheets) for Automated Supervision and Data Monitoring

The advent of point-and-click Zapier connectivity for web apps means that Magpi Enterprise users can — without programming — create a system that:

automatically flows incoming Magpi data into GSheets (or Excel, or Salesforce, or many other sites)
automatically analyzes and processes that data
automatically notifies sends out notifications to everyone who needs to know what’s happening with the data

Example: Counting Progress Towards a Data Collection Target

Let’s imagine that you’ve got field staff who are performing supervisory activities at remote worksites. These could be anything from drilling rigs to clinics — the important thing is that you’ve got personnel regularly collecting data there with Magpi.

If you’re the supervisor, you’d like to know if people are hitting their targets. Maybe they’re supposed to visit a certain number of facilities per month, or maybe it’s something else. Up until now, to know if this is happening, you’d have to either log into Magpi and “eyeball” the data — or else manually download the data into Excel and do some analysis. To do this kind of thing automatically would have required you to hire programmers.

Not any more.

How it Works

Depending on what type of account you have with Zapier, you can have it check your Magpi account for new data as often as every five minutes.

Step 1 — get Zapier to flow your data from Magpi to GSheets

Once you sign up for a Zapier account, you can search for “Magpi” to find some “zaps” (integrations) that work with Magpi. Here’s the one we need:

Zapier will lead you through the process of connecting your Magpi Enterprise account, and then specifying which form has the data that you want to pass to Google Sheets. Then you’ll need to specify your Google account, and which Google spreadsheet should receive the data.

The whole process takes about 5–10 minutes to set up, and the result is a Google Sheet that automatically updates with your new Magpi data:

Google Sheet automatically updating with Magpi data

Step 2 — Automatically Total Up the Data from Each Data Submitter

Now that you’ve got your data flowing to Google Sheets, you can apply functions similar to what you’d find in Excel. For this example, we created a second worksheet, and in the first column applied the unique() function to the data rows in the first worksheet:

=unique(RawCollectors)

This makes GSheets look at the raw data and identify how many unique data submitter/collector there are (in the “Created by” column).

In the second column, we used the countif() function to count how many data records there were for each unique data collector:

=countif(RawCollectors,UniqueCollectors)

And the result is a tablet of each data collector and how many records they’ve submitted:

Step 3 — Analyze the Data

At this point, you’ve got your data flowing into GSheets and automatically totaling up. You can use any formula, compare actual records collected to targets, etc:

Step 4 — Add Communications via Twilio

Just as Zapier helped you get data into Google Sheets, you can use Zapier to read the data in your GSheet and use that data to trigger messages. One example would be to use the Twilio messaging app to send SMS messages based on new data in the GSheet: