Transcript The Value of mHealth - Millennium Villages

Using Commcare Data in MVP
Detailed Reference
OUTLINE
BACKGROUND
RAW DATA

The Importance of Data (Slide 3)

Why Export Raw Data? (Slide 28)

The CommCare Software (Slide 4)

Exporting Raw Data: Cases (Slide 29)

Summary of Data Flow in the CommCare Platform (Slide 5)

Exporting Raw Data: Forms (Slides 30-31)

Key CommCareHQ Terminology (Slide 6)

Why Set Up a Custom Export (Slide 32)
ACCESSING COMMCARE DATA

How to Set Up a Custom Export (Slides 33-35)

Permissions for Data Access (Slide 7)

Where to Find Your Custom Export (Slide 36)

How to Manage User Access (Slide 8)

Excel Tips for Calculating Indicators (Slide 37)
TYPES OF COMMCARE REPORTS

Calculating Indicators from Raw Data (Slides 38-40)

Methods to Use CommCare Data (9)
CASE STUDY

Examples of Custom Reports (Slides 11-15)


Built-In Worker Monitoring Reports (Slide 16)
Using Maternal & Child Health Application to Identify Problem &
Inform Response (Slides 41-46)

Using Morbidity Data to Identify Problem & Inform Response
(Slides 47-49)

Worker Activity (Slides 17-18)

Daily Form Activity (Slide 19)

Submissions by Form (Slide 20)

Form Completion (Slide 21)

Case Activity (Slide 22)

Form Completion vs. Submission Trends (Slide 23)

Worker Activity Times (Slide 24)

Setting Up Email Delivery of CommCareHQ Reports (Slide 25)

Additional Monitoring Tools (Slides 26-27)
SUMMARY (Slide 50)
BACKGROUND
The Importance of Data
Data collected through mobile health technologies such as CommCare can
help improve health service delivery by:
 Providing real-time, immediate feedback on:
 The performance of community health workers (CHWs)
 The health status of the community
 Helping to identify challenges in service delivery and inform solutions
 Providing evidence-based support for operational decisions and health
sector programming
BACKGROUND
The CommCare Software
 Millennium Villages Project (MVP) uses CommCare as its primary mobile health
platform for monitoring its CHW program
 CommCare is a mobile phone application designed to serve as a decision guide
during household visits, a counseling aid, and a data collection tool
 CommCare’s web-based interface allows for real-time monitoring of CHWs’ activities
and community health status by health managers
 CommCare includes:






Easy to follow questionnaires with point-of-care decision making algorithms to ensure quality
visits and reduce data entry errors
Longitudinal monitoring of thousands of children, mothers, and households over long periods of
time
SMS-based visit reminders for CHWs
Mapping of cases via GPS
Multimedia for strengthened counseling (video, audio, image)
Capacity to produce real-time reports on a number of worker activity and health status reports
BACKGROUND
Summary of Data Flow in CommCare Platform
Mobile user
Web user
Cloud server
De-identified static
reports
Web user
BACKGROUND
Key CommCareHQ Terminology
Terminology
Description
Application
The complete CommCare executable that gets downloaded and installed on a phone. This is the application that will be used by the mobile user to submit
data to CommCareHQ. Example MVP applications include: Maternal and Child Health (MCH), Verbal Autopsy (VA), CHW Supervision
Form
The questionnaire that is filled out by the CHW. A form often corresponds to an action. In the MVP MCH application, there are 4 main form types – register
a new case, visit a case, close a case, and edit the registration information of a case. In the MVP VA application, there are 3 main forms - Verbal Autopsy
Neonatal, Verbal Autopsy Child, and Verbal Autopsy Adult
Case
Anything that is being tracked by the CommCare system over time, often an individual or a household
Submission
Completion Time
Received Time
Mobile User
Web User
Group
A form that has been sent to the CommCare server, which is dependent on internet access
Time at which a form is actually completed, as indicated when reaching the end of the form questionnaire and pressing “save and exit”
Time at which a completed form has been received by CommCareHQ, which is dependent on internet access
Someone who completes the forms in CommCare on a mobile device - typically a CHW, VA Specialist, or CHW Supervisor
Someone who uses CommCareHQ to manage the project or view reports – typically a CHW Manager, Health Manager or eHealth Manager
A collection of users that can be manually classified into “groups” by geographic area (i.e. parish)
ACCESSING COMMCARE DATA
Permissions for Data Access
 Ask yourself: Should this user have access to
patient-level health data?
 If not, grant access only to aggregate-level or de-identified
reports
 Access to the various reports can and should be
customized based on whether this user should
have access to patient-level health data
 Appropriate permissions can be set when creating
new web user accounts on CommCareHQ
ACCESSING COMMCARE DATA
How to Manage User Access
 Select “Users” in the CommCare toolbar
and “Web Users & Roles”
To assign permissions to a user:
 Add a user by clicking the “+Invite New
user” button
 Enter the e-mail address of the user you
want to invite
 Assign the permissions/level of access
for the user using the “Role” dropdown
To edit permissions of a role:
 Scroll down on the “Web Users & Roles”
page to the “Roles” section
 Click on “Edit Role.” You can rename the
project role if you wish
 Check off the permissions you want to
give to the role. Click “Save”
https://confluence.dimagi.com/display/commcarepublic/CommCareHQ+Web+Users
TYPES OF COMMCARE REPORTS
Methods to Use CommCare Data
 CommCare mobile data submissions can be used to generate a variety
of accessible, user-friendly reports to monitor CHW performance and
community health status, including:
 Worker Activity reports available on CommCareHQ for all CommCare projects
 Commissioned custom reports programmed to be available on CommCareHQ
 Other custom collections of indicators or visualizations generated from data
submissions in Excel or by uploading submissions to a third-party analytics tool
TYPES OF COMMCARE REPORTS
MVP Data Reports
Data Tools
Definition
Pros
Cons
Examples
Custom
Reports
(General)
•
Reports generated by manipulating
CommCare raw data exports in Excel
•
or by uploading CommCare
•
submissions into a third-party
analytics tool
•
Provides the most flexibility for defining indicators and
•
generating visualizations
•
Can be easily integrated with qualitative observations
Can be e-mailed, which facilitates data consumption in
areas were internet connectivity is too unreliable to
•
load CommCare reports regularly
Static report
Aggregate level data only
•
Requires staff with strong
knowledge in Excel or other
•
data analytics tool
Can be time consuming to
develop
Custom Reports
(CommCareHQ)
Data aggregated at the CHW-level for •
performance analysis and to protect •
patient data privacy. These were
custom designed by the MVP team
•
with Dimagi at the outset of the
project specifically for the MVP
Maternal & Child Health application
Updated in real-time and generated automatically
Can be e-mailed, which facilitates data consumption in
areas where internet connectivity is too unreliable to •
load CommCare reports regularly
Health Coordinator Report recommended for overall
indicator tracking; CHW Manager Report
recommended for individual performance tracking
Can have data errors if
there is an issue with how
report is programmed
Worker
Activity
Reports
CommCareHQ report that comes with
•
each CommCare project. This is useful
•
for monitoring worker activity – i.e.
number of CommCare submissions
per day by CHW
Updated in real-time and generated automatically
Can be e-mailed, which facilitates data consumption in •
areas where internet connectivity is too unreliable to
load CommCare reports regularly
Limited to worker activity
indicators only
•
•
•
Health Coordinator Report
CHW Manager Report
Verbal Autopsy Report
•
•
•
•
•
•
Worker Activity
Daily Form Activity
Submissions by Form
Form Completion
Case Activity
Form Completion vs.
Submission Trends
Worker Activity Times
•
•
Raw Data
Exports
CSV or Excel files that can be
exported from any CommCare
project. For MVP, these raw exports
contain all submissions entered by
CHWs into CommCare
•
•
Most flexibility for defining indicators and creating
visualizations
Useful for looking at case-specific information (i.e.
Who are the specific U5s who did not receive a CHW
visit last week?)
Most accurate and detailed because viewing CHW
submissions directly
•
•
Quarterly Health Program
Status Report (Microsoft Excel)
Monthly CHW Report Card
(Microsoft Word)
Large spreadsheets can be
cumbersome to use and
time-consuming to analyze N/A
Large files can take a long
time to download
TYPES OF COMMCARE REPORTS
Examples of Custom Reports in MVP

Health Coordinator Report on CommCareHQ
 Aggregated health indicators. Available in real-time on CommCareHQ
Note: These reports are not automatically available on CommCareHQ. Dimagi, the company that owns CommCare, programmed them for MVP at additional cost.
TYPES OF COMMCARE REPORTS
Examples of Custom Reports in MVP

CHW Manager Report on CommCareHQ
 Health indicators organized by CHW. Available in real-time on CommCareHQ
Note: These reports are not automatically available on CommCareHQ. Dimagi, the company that owns CommCare, programmed them for MVP at additional cost.
TYPES OF COMMCARE REPORTS
Examples of Custom Reports in MVP

CommCare Verbal Autopsy Report on CommCareHQ
 Indicators on types of deaths that are occurring, including social and medical factors
contributing to deaths. Available in real-time on CommCareHQ
Note: This report is not automatically available on CommCareHQ. It was developed by the MVP eHealth team.
TYPES OF COMMCARE REPORTS
Examples of Custom Reports in MVP

Health Sector Report
 Static cross-site health indicator performance, generated monthly in Microsoft Excel
Note: These reports are not automatically available on CommCareHQ. MVP staff designed these reports.
TYPES OF COMMCARE REPORTS
Examples of Custom Reports in MVP

CHW Report Card
 Selection of CHW performance indicators, generated monthly in Microsoft Word
Note: These reports are not automatically available on CommCareHQ. MVP staff designed these reports.
TYPES OF COMMCARE REPORTS
Built-In Worker Monitoring Reports on CommCareHQ
 1. Select “Reports” in the toolbar
For quick performance assessment of CHWs:
•
•
•
•
•
•
•
Worker Activity
Daily Form Activity
Submissions by Form
Form Completion Time
Case Activity
Form Completion vs. Submission Trends
Worker Activity Times
https://confluence.dimagi.com/display/commcarepublic/Worker+Monitoring+Reports
TYPES OF COMMCARE REPORTS
(1) Worker Activity, Pt 1
Worker Activity Report shows a summary of form and case activity by user or group. Can be used to identify:
 Users not using the phone (no forms sent in)
 Amount of work done (number of forms sent in)
 Follow-up rates with beneficiaries
Number of forms sent
in time period
Date of last form sent
(or “None” if no forms sent)
Forms sent on average in time
period of same length
TYPES OF COMMCARE REPORTS
(1) Worker Activity, Pt 2
New contacts
registered
Number of contacts being
visited in date range
Child, pregnant woman, or
household closed (child now
over 5 yrs, woman no longer
pregnant, death, moved, etc.)
Total number of
cases per user
*% of contacts visited could be greater than 100% if there were contacts who were closed, but also visited in the time frame
% of contacts
visited*
TYPES OF COMMCARE REPORTS
(2) Daily Form Activity
This report displays daily form submissions organized by date and by user. This report allows
the manager to see exactly when CHWs are working (submitting forms), and when they are
able to submit the forms to CommCareHQ.
Filter by completion time or submission time
Daily number of forms completed or submitted per CHW
TYPES OF COMMCARE REPORTS
(3) Submissions by Form
This report displays daily form submissions organized by date and by user. This report allows
the manager to see exactly when CHWs are working (based on when they are completing
forms), and when they are able to submit the forms to CommCareHQ.
Daily number of forms submitted per CHW, per form
TYPES OF COMMCARE REPORTS
(4) Form Completion
This report displays the average completion times for a specific form, per user, over a given
time period. This allows an understanding of how long a CHW is taking to complete a form on
their phone. Completion time can be an important red flag to investigate performance; if
CHWs are completing a form very quickly, then they may not be using the form correctly.
Average: the average completion time
for the selected form
Standard Deviation: the
standard deviation of this
users' completion times
Shortest: the shortest completion
time for the selected form
No. of forms: the total number
of forms submitted in the
selected time period
Longest: the longest completion
time for the selected form
TYPES OF COMMCARE REPORTS
(5) Case Activity
This report displays the number and percentage of open cases (i.e. child, household, pregnant
women) that have been modified over a given time period. This helps identify whether the
CHWs are routinely visiting their clients up to your expectation.
TYPES OF COMMCARE REPORTS
(6) Form Completion vs. Submission Trends
This report displays a log of all submissions, comparing submission time to completion time.
Long delays between completion and submission times could indicate connectivity issues or
errors with a mobile user phone’s date configuration.
Filter by module and
by the type of form
For each of the selected form that the CHW submitted, one can see the
completion time, submission time, and time difference between the two
TYPES OF COMMCARE REPORTS
(7) Worker Activity Times
This report is a graphical representation of the form completion or submission trends over the
entire duration of the project. Y axis: displays each day of the week. X axis: displays the hours
of the day. This can also be filtered by specific CHWs, as well as by a specific date range.
Filter by module and by the type of form
TYPES OF COMMCARE REPORTS
Setting Up Email Delivery of CommCareHQ Reports

Click on “Reports”  “My Scheduled Reports”

Click the large green button “Create a New Scheduled Report”

Select the saved report(s) you would like to schedule as e-mails by clicking on the button next to the report name

Select the interval, day, time, recipient e-mail addresses, and whether you want an Excel report attached

Click on “Submit”
Tip: You can also generate an email report at any time by applying the desired filters in CommCareHQ and then selecting the “Email
Report” button.
https://confluence.dimagi.com/display/commcarepublic/Managing+Saved+and+Scheduled+Email+Reports
TYPES OF COMMCARE REPORTS
Additional Monitoring Tools

“Manage Deployments” – Use these reports to support troubleshooting

Track which CHWs have updated to most recent version of CommCare


Click on “Reports” in CommCare Header”Application Status” under “Manage Deployments”
Investigate if form submissions are not making it into the automated reports because of an error

Click on “Reports” in CommCare Header ”Raw forms, errors, and duplicates” under “Manage Deployments”
https://confluence.dimagi.com/display/commcarepublic/Manage+Deployment+Reports
TYPES OF COMMCARE REPORTS
Additional Monitoring Tools

“Inspect Data” – Use these reports to access specific
details on CHW submissions without having to export
the raw data in Excel
 View complete patient history
 Click on “Reports” in CommCare Header”Case List”
under “Inspect Data”
 To find a specific user’s or group’s cases, type the
name into the “Groups or Users” box in the filter
 Review forms submitted by CHWs within a
designated time period
 Click on “Reports” in CommCare Header”Submit
History” under “Inspect Data”
 Review by group or individual CHW
 Worker Activity Reports will link to these tools too
https://confluence.dimagi.com/display/commcarepublic/Data+Inspection
RAW DATA
Why Export Raw Data?
 Raw data provides case-specific information, categorizing submitted
data by patient instead of aggregated information by CHW
 Raw data is the first place to look if any issues with custom or
CommCareHQ reports are suspected
 Remember raw data is organized in the export as follows:
 For form exports: Each row is a visit/individual form submission by a CHW
 NOT unique because an export may include multiple visits/records for the same person
 For case exports: Each row is a case (i.e. a unique household, child, or pregnant
woman)
https://confluence.dimagi.com/display/commcarepublic/Data+Export+Overview
RAW DATA
Exporting Raw Data: Cases
1. Select “Data” in the heading
2. Select export cases
3. If desired, filter for a specific group
(i.e. geographic area, type of user)
5. Download Export
4. Select “all open” (only active
contacts or “all” (includes closed
contacts)
RAW DATA
Exporting Raw Data: Forms
1. Select “Data” in the heading
2. Select export forms
3. If desired, filter by group (i.e. geographic
area, CHW supervision groups, type of user)
RAW DATA
Exporting Raw Data: Forms
4. Download the form data you want
Tip: To avoid a separate export for each form type, on the right-hand side of the screen, check the box to
include the desired forms in a bulk export. This is not recommend when network connectivity is poor.
RAW DATA
Why Set Up a Custom Export?
Sometimes, there is too much data! Downloading full raw data exports can take longer than desired and
can lead to possible errors when analyzing. If you know what information you need, you can specify for
CommCare to only output those fields.
Forms: https://confluence.dimagi.com/display/commcarepublic/Form+Data+Export
Case: https://confluence.dimagi.com/display/commcarepublic/Case+Data+Export
RAW DATA
How to Set Up a Custom Export
1. Select “Data” in the heading
2. Select export forms
RAW DATA
How to Set Up a Custom Export
3. Create Custom Export
RAW DATA
How to Set Up a Custom Export
4. Check which data fields you would like to include in your export. You can
reorder fields by dragging and dropping fields. You can also rename fields
5. At bottom of
page, press
“Create”
RAW DATA
Where to Find Your Custom Export
Download your custom export to Excel:
1.
Return to “Data””Export Forms”
2.
Find the name of the Custom Export
3.
Download your Custom Export
1
2
3
RAW DATA
Excel Tips for Calculating Indicators from Raw Data Exports
HOW TO:


Filter

When to use: to narrow down the data in your spreadsheet, allowing you to view only the information you need

Additional resources on filtering in Excel
Pivot Table

When to use: to quickly summarize and analyze large amounts of data in lists and tables—independent of the original data
layout in the spreadsheet—by dragging and dropping columns to different rows, columns, or summary positions


Additional resources on creating a pivot table in Excel
V-lookup

When to use: to centrally locate data from multiple Excel sheets or hard-to-see location; need to match associated data (i.e.
when comparing # of children visited by a CHW this month with the # children the CHW is responsible for, use the V-lookup
function with CHW name as the “Lookup Value”)

Command: =VLOOKUP()

Additional resources on using V-lookup in Excel
RAW DATA
Calculating Indicators from Raw Data Tools
Example 1: Number of Rapid Diagnostic Tests (RDTs) positive for malaria per CHW:
1.
Export FormsChild List ModuleVisit: download as CSV file
1.
In Excel, remove duplicates for form|case|@case_id This is using Excel 2008 for Mac; other Excel versions may slightly
differ. Column names may differ slightly if full export vs. custom export is used
2.
Data tabPivot Table Report
This is what exports to Excel from CommCare.
Note: This example is based on the structure
of the MVP MCH application.
RAW DATA
Calculating Indicators from Raw Data
4.
5.
6.
Creating an Excel Pivot Table, place form|meta|username in rows. Place form|case|@case_id in values and then place
form|patient_available|referral_follow_on|rdt_result in values as well
Right click form|patient_available|referral_follow_on|rdt_result headerField SettingsSubtotals Custom: Count.
Hide all items but Positive
Copy and paste the CHWs who have found +RDTs and their number of +RDTs in another Excel sheet (titled “Working”)
CHWs with 0 + RDTs will not be included in Pivot Table
RAW DATA
Calculating Indicators from Raw Data
7.
In another table with all CHW names, write a VLOOKUP formula to search in “Working” sheet how many +RDT cases
each CHW has. Replace N/A with 0
*Note: The Excel file pictured here is created from what
CommCare exports. In a new sheet of the exported
CommCare Excel file, create columns for the health
indicators of interest for each CHW and use the Pivot Table
function to find the corresponding values for each CHW.
CASE STUDY
Using Maternal & Child Health Application to Identify Problem
& Inform Response
Case Study – Appropriate Treatment of Malaria:
More severe malaria cases were coming to the health center than during similar times of the year. After
using the MCH application to access and data by CHW, it was noted that some CHWs found children with
positive Rapid Diagnostic Tests for malaria (+RDTs), but not all the CHWs were treating children with +RDTs
with coartem as per protocol. Upon further questioning of the CHWs, the local health team learned that
CHWs in several parishes had run out of coartem stock, prompting a replenishment of supplies.
Additionally, CHWs were given refresher training on the importance of timely referral of malaria cases to
health centers and reporting of stockouts.
CASE STUDY
Using Maternal & Child Health Application to Identify Problem
& Inform Response
Why the MCH Application was Important:
The MCH application documented that some CHWs were diagnosing malaria but not treating malaria,
which was leading to more severe cases of malaria coming to the health center. Data collected by the
application also allowed the health management team to see which CHWs were not providing appropriate
treatment to child malaria cases and to follow-up with these CHWs. Upon further investigation, the health
management team realized that the government stocks of coartem needed to be replenished. Although
CHWs are supposed to report low stock levels, they do not always do so in a timely manner. Without the
MCH application, the reason for an increase in severe child malaria cases may not have been wellunderstood and the problem may not have been resolved so quickly.
CASE STUDY
Using Maternal & Child Health Application to Identify Problem
& Inform Response
How readily accessible reports can be used to quickly identify a problem:

Health Coordinator Report can be used to identify general issues (i.e. performance on malaria
treatment indicator is low)

CHW Manager Report can be used to identify which specific CHWs have not provided treatment for
their U-5 cases during the time period
Finally, if further investigation is necessary, raw data can be used to identify specific cases that did not
receive treatment.
CASE STUDY
Using Maternal & Child Health Application to Identify Problem
& Inform Response
(Continuing from Calculating Indicators from Raw Data example)
8)
In Excel Pivot Table, remove form|patient_available|referral_follow-on|rdt_result and place
form|patient_available|medication_type in column
9)
Right click form|patient_available|medication_type headerField SettingsSubtotals Custom:
Count. Hide all medications that do not include coartem
CASE STUDY
Using Maternal & Child Health Application to Identify Problem
& Inform Response
10)
Copy and paste the total count of coartem medications administered by each CHW in another Excel
sheet (titled “Working.” if CHW has not given any coartem, the CHW’s name will not be included in
the table produced by Pivot Table)
11)
In the column next to the existing table with CHWs who have found +RDTs, write VLOOKUP formula
to search how much coartem each CHW has. Replace N/A with 0
12)
Divide the # +RDTs by # coartem for each CHW
CASE STUDY
Using Maternal & Child Health Application to Identify Problem
& Inform Response
13)
In table with all CHW names, write VLOOKUP formula to search the percentage of +RDTs treated with
coartem. Replace N/A with --
14)
Select this column’s cells, right click on cells, and “Format Cell”NumberCategory: Percentage to
show percentage
15)
Highlight any CHW that did not treat 100% +RDT cases with coartem
CASE STUDY
Using Morbidity Data to Identify Problem & Inform Response
Case Study – Midwife Performance:
In one MVP site, several neonatal deaths were occurring around one health center. After using the
CommCare Verbal Autopsy application to access and analyze Verbal Autopsy (VA) data on these deaths, it
was noted that the same midwife was responsible for these mothers. Upon interviewing these mothers
further, it was found that that the mothers were afraid of this particular midwife, and therefore avoiding
the health center until too late or opting to go to a traditional healer instead. The midwife was found to be
aggressive, unresponsive and intimidating to the mothers, leading mothers to delay seeking care and
ultimately leading to neonatal mortalities.
CASE STUDY
Using Morbidity Data to Identify Problem & Inform Response
How the VA Data Contributed to the Solution:
Trends are typically hard to notice through paper forms, which are tedious to aggregate. Electronic VA data
made it possible to see that the same midwife played a role in each of the neonatal deaths, permitting the
management team to take appropriate action. Without the VA data, factors contributing to the deaths
may have been incorrectly attributed to medical or other reasons rather than a preventable social cause.
CASE STUDY
Using Morbidity Data to Identify Problem & Inform Response
1.
“Data” in headingExport Forms
If desired, filter by group
2.
Export Full FormsVA Specialist ApplicationVerbal AutopsyDownload form of choice
Basic mortality information can also be found by exporting the Child Close forms (Export VisitsChild ListClose Form,
download CSV). This data is also included in a vital statistics section in the MVP custom reports created by Dimagi.
SUMMARY
 By facilitating real-time data monitoring, CommCare can help
management teams identify trends in CHW performance or community
health status, and inform a response
 CHWs enter data into the CommCare application on their mobile
phones, which is uploaded to a cloud server. A CommCareHQ user can
then access and download the data for analysis
 It is important to consider what type of reporting tool will help your
team reach their program goals, as well as who should have access to
different levels of data
 Data collected with CommCare can be accessed and analyzed through a
variety of tools:




Raw data: full records of case-specific information in CSV or Excel
CommCareHQ custom reports: automated reports with data aggregated by CHW or for the entire project
Other custom reports: manual, static reports generated from CommCare submissions
CommCareHQ Worker Activity Reports: automated reports to monitor CHW frequency of CHW submissions