00:00:04 Introduction

Thanks to everyone for joining us. Before we get to the webinar, I would like to take a couple of minutes to tell you a bit about ActivityInfo. BeDataDriven was founded with the mission to help social impact organizations do their work more effectively through the use of data. We do this by developing and helping organizations implement ActivityInfo. ActivityInfo is an end-to-end solution for M&E data management that is built on a relational data model. It has all the tools you need to manage data across the entire data lifecycle baked into one system.

It provides web and mobile applications for data collection in the field or in the office, connected or disconnected. It features intuitive built-in tools and expression languages to help validate data at the point of entry and clean data sets that might be inputted from other systems. It has a robust cloud-based system architecture that ensures that data stored in the system is always available without any need to manage hardware or cloud environments. It has robust data management capabilities to help organize data and provide access to those who are permitted to, and powerful analytic and data visualization tools that help you extract value from M&E data for better decision making.

00:02:24 Webinar series overview

Welcome to our webinar series. We are going to walk you through four different webinars in this series. The first one, which we are here for today, will explain how we can get from a project-specific MEAL plan to a data model step-by-step. For this purpose, we will use a case study provided by an ActivityInfo customer, Partners of the Americas. We will explore how a MEAL plan can be transformed into a data model, which is considered the key process for database design.

The next webinar, Session 2, will focus on how we can move from a data model to database design. Then we will move into how we can efficiently manage the data within a database, covering broader database and data management, facilitating access, and managing different parts of the database. Last but not least, on the 5th of December, we will go into data analysis and reporting, explaining how we can implement evidence-based decision making in real time. For the purpose of the webinar series, we will use ActivityInfo to explore best practices that can be implemented across any situation.

00:06:31 The database design process

Designing a database or adopting a database, either on a project level or across different projects and countries at an organizational level, always involves a process. Before we start, we need to have different requirements clear in our minds. We need to understand the flow of the project we implement, what we want to monitor, the data requirements, the different roles and responsibilities within our project, and the work plan. We need to be clear on what we will do on the ground to that specific end.

Then we move into the design phase. In the actual design phase, we configure all the different data collection forms, how the forms are associated with one another, any migration, data management roles, and permissions within the system. Testing is also an important part of the process, as is analysis and reporting. Here we configure the reports that we want to monitor and see throughout the implementation.

Then we move into the launch. We always have an internal rollout where we develop different training materials, train our end users, and conduct training of trainers if need be. Finally, there is adoption. Throughout this phase, we support our end users, perform optimizations in the system, and monitor how the system is actually being implemented. To this end, it is important to have someone who takes the lead on adopting such a system, someone responsible for the technical configuration and maintenance, and experts on different processes to provide their input.

00:09:04 Readiness and requirements

Before designing the database, we need to understand the specific project's processes and objectives, often called the theory of change or pathway of change. We need a clear understanding of the different data requirements, broadly speaking, the MEAL plan or MEL plan. We need an implementation plan detailing the specific activities we will implement and the specific indicators we will need to monitor and evaluate the process. We must also check compliance, whether donor compliance, country-level compliance, or global compliance policies.

Finally, we need to gather requirements from relevant stakeholders. Imagine having different system users as part of a database: people sitting in the office, people working in the field, maybe the donor, partners, or other stakeholders outside of the organization. We need to gather different requirements to create a data flow that describes those processes and specify the information we gather, meaning the specific data collection forms and relevant instructions.

00:11:07 Case study: Food for Progress

Thanks to Partners of the Americas, the specific case study will focus on the Food for Progress project. These are projects implemented in developing countries and emerging democracies. As part of this process, U.S. agricultural commodities donated to recipient countries are sold on the local market, and the proceeds are used to support agricultural, economic, or infrastructure development programs. Past Food for Progress projects have trained farmers in animal and plant health, helped improve farming methods, developed road and utility systems, established producer cooperatives, provided microcredit, and developed agricultural value chains.

Partners of the Americas implements a specific project in Mauritania. We have adapted this for the needs of the webinar to explain it within the limited time. The theory of change in this case has a long-term objective to provide a stable source of fruits and vegetables for Mauritanians. We aim to achieve this via two major objectives: increasing productivity in different commodities and creating a more sustainable agricultural sector. We have two intermediate results: accessing agriculture-related financing and using regenerative agricultural approaches. As a cross-cutting intermediate result, Partners of the Americas facilitates access to better support services for farmers.

In terms of the implementation process, we have two different service types: target commodities received by beneficiaries along with related financing, and access to regenerative agricultural approaches. We have taken the hypothesis that beneficiaries will self-register in the project, and then field teams will review and add respective information on top of that registration. In Mauritania, we monitor and disaggregate information across various administrative areas, up to the fourth administrative level.

00:15:25 The MEAL plan

A MEAL plan, or performance management plan, includes different statements and indicators. It defines how information will be collected, the method, frequency, who will collect information, who will respond, means of analysis, relevant disaggregations such as age or gender, and how we will use the information.

For this webinar, we will focus on two specific indicators to simplify the data model. Indicator number one is the yield of targeted agricultural commodities among program participants with USDA assistance. This corresponds to strategic objective number one. Yield is a measure of total production divided by the total number of hectares. We collect total production in tons and unit area planted in hectares. Disaggregations include farm size, gender, and age. Field staff collect this shortly after harvest from primary recipients, sampling based on farm and beneficiary records.

The second indicator is the number of individuals accessing agriculture-related financing as a result of USDA assistance. This is a unique count of individual farmers or cooperative suppliers that access financing. It tracks individuals and organizations that have benefited from cash loans, in-kind loans, equity loans, or other forms of debt-free finance. Field staff identify individuals and collect basic information via structured questionnaires.

00:19:30 Understanding the data model

A data model is a visual representation that helps us organize information into different tables depending on our objectives. It defines the type of information we collect under each data collection form and describes the relationships across those tables. The data model is important because it serves the need for data protection, defining who needs access to data and why. It ensures accuracy, reliability, consistency, and efficiency. By structuring the data, it becomes easier to store, retrieve, and manipulate.

The data model provides a blueprint on how data can be expanded. It facilitates data consolidation, especially for organizations with multiple data sources, and defines how systems are interconnected. It provides a common vocabulary and shared understanding for communication between different stakeholders. Ultimately, it simplifies complex systems to use data more efficiently and achieve monitoring, evaluation, learning, and accountability objectives.

00:21:44 Steps to transform MEAL plan to data model

To transform the MEAL plan into a data model, we first have some pre-work. We need to define implementation objectives, identify different users and integration requirements, identify master data or data collection forms, identify operational or reference data, and determine the type of analysis needed. Then, we transform this pre-work to create the data flow. The data flow dictates who collects information, who accesses information, and what type of information is involved.

Finally, we create the data model. This involves the identification of different forms or tables, different fields, unique identifiers to avoid duplicates, relationships, and the reports needed to perform timely evidence-based decision making.

In terms of implementation objectives, we wish to streamline data collection to reduce time needed for data cleaning, management, analysis, and reporting. We want to prove data currency and consistency and use analysis timely. We have identified different user groups: MEAL supervisors responsible for overview and design, MEAL officers who do surveys and manage reference data, project managers who need report access, and field teams who need access per location to add and edit records.

00:26:30 Structuring relationships in ActivityInfo

In ActivityInfo, we have two different ways to make associations between tables. The first way is using "Subform fields." For example, if activities are connected to multiple indicators, we use a subform to create a parent and child relationship. This is useful when it is not meaningful for a record (like an indicator) to exist without being connected to a parent record (like an activity).

The second way is using "Reference fields." These have two sub-objectives. First, for data we know in advance, called reference or operational data, such as administrative areas or project lists. We manage these centrally and want updates reflected across all forms. Second, when we want to share data across multiple forms to avoid redundancy. For example, if one team gathers beneficiary registration, the next team (field officers) can link to that existing beneficiary record when performing a survey, rather than collecting the same demographic information again.

00:30:20 Defining data flows

Data flow describes who the user is, what process they perform, and consequently, what access they need to specific data collection forms. MEAL officers manage reference data centrally and validate surveys, so they need access to those forms. Field officers register farms and conduct surveys, so they need access to farm registration and survey forms. The M&E supervisor has access across the whole database for analysis and management. The project manager needs access to master data and reports for overview and audit purposes.

We have taken the hypothesis that beneficiaries will complete a self-registration. Since they are not system users with passwords, we will generate a self-registration collection link for the beneficiary form. This link can be shared via field staff or QR codes. It is also important to consider whether data collection will happen via web or mobile application, and whether it needs to be offline or online, as this affects device procurement and instructions.

00:33:15 Visualizing the final data model

The final output is a visual data model. We have our reference data, including the four administrative levels (1, 2, 3, and 4) which are associated hierarchically. We have our projects, which have multiple activities and indicators. We also have a list of field officers connected to specific administrative areas.

Moving to the data collection forms, we have our beneficiaries. Because of the self-registration, beneficiaries are associated with the farms. The field staff receive the application in the system and complete the farm form. We then have the two different surveys. The survey for yield corresponds to a specific farm and a specific indicator to facilitate analysis. The second survey regarding financing links back to the individual beneficiary. This uses a reference field so we don't have to collect location, sex, or date of birth again; we simply build on top of the existing record.

Finally, we have the reports. The monitoring report has two data sources: beneficiaries and farms, allowing us to aggregate numbers on a monthly basis. The donor report uses the two different surveys as data sources to calculate cumulative progress to target and deviation from baseline. Including reports in the data modeling process serves as a reality check to ensure we have included all necessary information, such as specific disaggregations like gender.

00:38:58 Key takeaways

It is important to consider whether your project is ready. You must know which information you will gather, the process, and identify the different user groups. Have your objectives clear and consider the data collection processes—who is going to do what with the data. This goes hand-in-hand with roles and responsibilities, both at the macro level of system adoption and the micro level of data collection and analysis. The type of analysis and reports you want to generate serves as the final reality check to ensure everything will work smoothly. The data model is the key process for implementing a successful, sustainable information system.

00:40:40 Next session preview

In our next session on November 20th, we will look into how to translate the data model into a database design using ActivityInfo. This crucial step ensures that the data we collect is well-structured, easy to manage, and directly supports our reporting and analysis needs. We will cover topics like designing data collection forms, enabling offline data collection, ensuring data quality and consistency, and more.

00:41:30 Q&A session

Is a data model possible for qualitative indicator data? Yes. The data model gathers all information needs, not just quantitative data. While quantitative data is a frequent example because it is structured, the process also applies to feedback from beneficiaries in open text boxes, comments from field staff, key informant interviews, or focus group discussions. I would recommend including all data types in the data model to identify who will access what information and what data sources will be used for analysis.

How feasible is it to develop such a data model for a project MEAL plan with 20-25 indicators? It is quite feasible. 20-25 indicators is a sufficient and common number for a MEAL plan. If you account for a couple of weeks and a few meetings to get feedback and consolidate with stakeholders, the data model can be created. The smoothness of the process depends on the capacity and experience of the person leading it.

What are the unique keys used to link tables? Unique identifiers depend on the information within a form and the context. For beneficiaries, it might be a document ID, or if documents aren't available, a combination of name, date of birth, and phone number. In other cases, like quarterly surveys, uniqueness might be identified by the date/quarter of the survey combined with the beneficiary. It depends on the fields, the context, and the process.

Are two indicators sufficient to create a goal impact for food security? No. For simplicity in this webinar, I took a snapshot of two indicators to convey the message clearly. A real MEAL plan has many indicators depending on compliance and monitoring needs. The number of indicators should be identified by their use; if an indicator has a clear objective for donor reporting, monitoring, or advocacy, it is useful.

What platform is used for visualization? For the diagram, we used Miro, which is a general visualization software. For the actual reports and data collection discussed in the case study, we use ActivityInfo.

Poll Results: Regarding the sectors you work in, Health was prevalent at 41%, followed by Food Security and Social Protection. We also saw WASH, Education, Agriculture, and Nutrition. Regarding challenges, integrating data from multiple sources was the top challenge. The data model helps here by identifying integration points. Designing data collection forms and standardization is also a frequent challenge, requiring significant input and feedback from stakeholders. Generating reports and visualizations is another challenge, where the data model helps perform a reality check on whether the necessary information is being collected.