How to merge Contacts with Flow Results outside RapidPro
March 10, 2016
This post is outdated. We can now easily export flow results and contact fields 1 file.
RapidPro provides you with a quick and real-time analysis of the responses to each variable from each flow. However, for some cases you may want to analyze the results further outside the system to evaluate differences in responses, visualize results, or create maps using other software.
Since many contact fields are not included in the exported flows results, we can use universally unique identifiers (UUIDs) - which are now provided in RapidPro - to easily link contacts and all their contact fields to their individual flow runs, i.e. responses so that they can be analyzed in an external system.
This post explains how to merge contact details with flow results using UUIDs.
Part 1: Export files
The first thing you need to do is export the contacts and flows results.
1. Click on the Contacts icon, select the group of interest, click the Gear icon, then click "Export" on the drop-down menu. You will see a message that RapidPro is preparing your export. You will receive an email at the address you provided when it is ready.
2. In your email inbox, you'll see an email that says "Your contacts export is ready" with a link that should start an automatic download. The downloaded file will include your contacts' UUIDs, Names, Addresses, and Custom Contact Fields. The file looks like this:
Note that a universally unique identifier (UUID in column A) is assigned to each contact. This UUID will allows to merge the contact information and the flow results later.
Export Flow Results
1. When your contacts interact with a flow, their responses are stored as flow results which you can access by selecting "Results" from the gear icon drop-down menu in the flow editor.
2. The results page will provide a "Download Results" button in addition to listing each contact that has interacted with the flow and the number of runs they've made. Passage through a flow from entrance to exit - and all activity that takes place in between - constitutes a run.
Runs comprise the rows that make up your results export spreadsheet. Columns will contain each contact's:
- phone number or other address used
- the time at which their run began
- the time at which their run ended
- and their responses to each of your questions
3. After clicking the "Download Results" button, a prompt will appear indicating that a link to the page where you can download your results has been sent to the email address attached to your account.
The downloaded flow results in this example look like this:
In this flow results file you will see multiple sheets: Runs, Contact and Messages:
The difference is this:
- The Runs sheet contains the responses for each run. If a contact goes through the flow multiple times, each run is added as a new row. In cases where a contact reports on regular basis, e.g. weekly attendance polls, we will see multiple results (rows) per contact.
- The Contacts sheet contain the responses of the most recent run. So here we see only one result (row) per contact, which is the latest one.
- The Messages sheet contains all the incoming and outgoing messages (interaction between RapidPro and contact) for each contact.
Part 1: Merging files
Now that we have both the contact information and flow results, we can merge the two files into one so that we can link contacts and all their contact fields to their individual responses.
To do so we can use the VLOOKUP function in Excel. To learn how to do that you can watch this useful video which explains in very simple steps how to merge data from two separate Excel sheets provided there is a unique identifier, which in our case is the UUID.
After merging the two files following the video instructions, the merged file looks like this:
In this example, I only added 2 new columns to the contacts sheet using the VLOOKUP function:
- Column J which was column D in the flow result sheet (Groups)
- Column K which was column G in the flow results sheet (School Open (Category))
In the merged sheet we can now see the contacts, their profile (groups they belong too, language, gender, role and district) along with their responses. Having this information together we can now easily evaluate differences in responses by role, location, etc. Or, if we have GPS coordinates in our contact fields or can merge them in, we can map the flow runs in a GIS software.
These same steps can be followed to conduct more complicated analysis for various use cases.
The templates which I have used in this example, including the VLOOKUP formula can be downloaded here:
Hope you found this post useful. Please do let us know in case of comments or questions.