00:00:00 Introduction

Hello and welcome to today's webinar. BeDataDriven is the company that we all work for. It was founded with the mission to help social impact organizations 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 supports data collection, data management, and data analysis. It has all the tools you need to manage data across the entire data lifecycle in a single system. It provides web and mobile applications for data collection in the field or in the office, connected or disconnected. There are intuitive built-in tools and an expression language that help validate data at the point of entry, clean datasets, and import from other systems.

There is a robust cloud-based system architecture that ensures that the data stored in the system is always available without any need to manage hardware or cloud environments. There are data management capabilities to help organize the data and provide access to those that are permitted. Lastly, there are powerful analytic and data visualization tools that help you extract value from M&E data for better decision-making.

00:02:23 Recap of the previous session

This is the second session of our four-part webinar series titled "From LogFrame to Database," where we are exploring how to transform a LogFrame into a fully functional database. In our previous session, my colleague Eliza introduced how we can transform a MEAL plan into a data model using a tool called Miro. Today, we are going to take the next step, moving that data model into a database design using ActivityInfo. In the next session, we will focus on managing data within ActivityInfo, and finally, we will conclude with data analysis and reporting.

I would like to quickly revisit the program and highlight some key insights from our first session. This is a Food for Progress project by USDA, and we thank Partners of the Americas for allowing us to use this project as our case study. We are working with them to implement this project in Mauritania.

We have selected two specific indicators to focus on. The first one is the yield of targeted agricultural commodities among program participants. We need to calculate the yield per hectare and disaggregate this data by farm size, gender, and age. The second indicator is the number of individuals or farmers accessing agricultural-related finance as a result of USDA assistance. Here we will count the unique number of farmers, disaggregated by their type of financing.

Last time, we transformed this MEAL plan into a data model. We organized the data into a folder called 'Reference Data,' which includes the locations at all four administrative levels of Mauritania, as well as a form for field officers. Then we have our M&E form for project activities and indicators. We also have the data collection forms, which contain the registry of beneficiaries and the corresponding surveys.

00:08:51 From data model to database

Now that we have our data model ready, the big question is how do we actually move this into an information management system? Before we proceed, we need to ask ourselves a few important questions. First, review the data model. Is the data model complete, and does it reflect all program requirements? Have we identified all entities, relationships, and data flows? Does the data model align with the MEAL plan and indicators?

Second, we need to think about defining the database structure. How will we translate the data model into the database forms? This highly depends on the system we are using. With an integrated system like ActivityInfo, everything can live in the same platform. Have we identified the necessary fields and their data types? Is the database design scalable and adaptable to future needs? This is crucial for the humanitarian field because there is constant change.

Finally, we need to ensure data quality and compliance. Have we established measures to maintain data integrity to minimize data entry errors? Does the design comply with donor and organizational policies? For example, if we cannot capture confidential information, we need to use IDs instead.

00:12:08 Data hierarchy in ActivityInfo

Any information management system requires us to understand its core functionalities. Here is how ActivityInfo structures data. First, we have our database, the top-level container for all the data. Then we have folders, which are used to organize forms within a database. We have divided them into reference folders and data collection folders.

Then we have the forms, which are the cornerstone of our database. They represent tables and focus on specific entities like beneficiaries, farms, and locations. Then we have the records, which are individual entries within a form. For example, each beneficiary within the beneficiaries registry is its own record. Finally, they all have attributes, which are the fields.

00:14:20 Field types and relationships

ActivityInfo supports various field types to accommodate different data requirements. We have Serial Number, where a unique ID is generated by the system. We have Quantity for numerical values, Text for textual values, and Date fields. We have Single and Multiple selection for predefined lists of options. We have Attachments, which allows us to upload or capture photos directly in the field, and Signatures. We have Calculated fields used for computations.

Then we have subforms and reference fields. Both are used when linking to another form and are essential for establishing relationships. Reference forms are used when you have standard lists that are to be reused across multiple forms, such as locations. Subforms are used when we want to enforce a parent-child relationship, such as between activities and indicators. It is good when you capture recurring or repetitive information relating to a specific record.

Other functionality includes collection links. Since beneficiaries will self-register in this program, it is impractical to purchase an account for each one. Collection links allow you to collect data from external users without requiring them to have ActivityInfo accounts. Key features are ease of sharing and anonymous submissions. We also have app collection links which direct users to download the ActivityInfo app, enabling additional functionalities like saving drafts and using the database offline.

Offline mode enables users to collect and manage data in ActivityInfo without an internet connection. This is very important in remote areas. Key features are local storage, data entry, and review. Once you are back online, the data syncs automatically provided all validation rules are met.

00:20:07 Implementing data quality measures

At the heart of any effective information system is data quality. Ensuring high data quality involves several key components. Validation rules are constraints that prevent users from entering invalid data, such as numbers outside a specified range. Skip logic determines whether a question or field is relevant based on a previous answer, streamlining the data entry process.

Primary keys are unique identifiers for each record so that each entry is distinct and easily retrievable. Required fields must be filled out before a record can be saved. Additional configurations like read-only fields or default values help maintain data consistency.

In ActivityInfo, validation rules restrict what users are allowed to input. Key features are mandatory constraints, customizable conditions, and error messaging. For example, if we have an age field, we can ensure the age entered is between specific values. Relevance rules, or skip logic, determine whether a field is shown during data entry. For example, if a question is about pregnancy status, it does not make sense to ask this if the biological sex is male.

00:25:44 Live demonstration

We will now walk through how to implement these data quality measures and build our database in ActivityInfo. We recommend starting by building the reference data, meaning starting from left to right in our data model.

I will start by adding a blank database called 'SSPR Mauritania'. I will add my first folder, 'Reference data'. Inside that folder, I will add my first form, 'Admin 1'. I will add a text field for the name and make it a key field. I can also add a geographic point for coordinates.

Next, I will add the 'Admin 2' form. It has a name field and geographic points. To create the cascading relationship, I will add a reference field linking to 'Admin 1'. I will make this a key field as well. This ensures that when we select a location, we know which Admin 1 area it belongs to.

I will also add a form for field officers. I will use a text field for the name and reference 'Admin 2' to link the officer to a location. This creates a cascading relationship where selecting a location filters the available options based on the hierarchy we defined.

Now we will build the 'Collection Forms' folder and the 'Beneficiaries Registry'. I will add a name field and a single selection field for the beneficiary type (Individual or Cooperative). I will set 'Individual' as the default value. I will add a text field for the Cooperative name but apply a relevance rule so this field only appears if 'Cooperative' is selected.

For the date of birth, I will use a date field. I will add a validation rule using the formula editor to ensure the age is greater than or equal to 18. I will use the YEARFRAC function with TODAY() to calculate the age dynamically. I will also add a calculated field for 'Age Category' using an IF statement to automatically categorize beneficiaries as "18-29" or "30+" based on their date of birth.

Finally, I will add a subform for the agricultural forms. This establishes a parent-child relationship where each form record belongs to a specific beneficiary. I will also demonstrate how to hide the subform from the collection link entry if we want beneficiaries to self-register only their profile, while field officers add the detailed forms later.

00:43:43 Conclusion and next steps

To conclude, translating the data model into a functional database requires ensuring it reflects the data model accurately, implementing data quality measures like validation and relevance rules, and planning for scalability.

In the next webinar, we will focus on how we can practically manage data and the whole database spectrum. This includes facilitating access, modifying table views for different user groups, translating the database, performing data migration, detecting deduplication, managing users, and monitoring the database.

00:46:39 Q&A

Do you need to have the application installed for the offline version to work? Yes, you need to download the application on a specific device to use the offline mode.

How do you deal with data migration in ActivityInfo? We have importing features and a new application feature for migration. We will cover this in detail in the session on the 28th.

What are the unique advantages of using ActivityInfo over platforms like KoboCollect or ODK? ActivityInfo is a relational database platform. While Kobo and ODK are great for data collection, ActivityInfo covers the whole data lifecycle. The relational database concept allows for reference forms and subforms to link data entities together, managing data across all levels.

How can we join multiple subforms and create a table? This will be covered in the last session on analysis and reporting, where we will discuss calculated tables and measures.

Is there an option to import previously collected data? Yes, you can import data. As long as you have the key field, you can add new records or update existing ones.

Is it possible to have a common database for the country level to avoid duplication? Yes. This starts in the data model by streamlining information so different actors have access to a common place. You could have a table including different organizations to allow multiple stakeholders to access the system.

How do we use reference data that comes from an API? We can connect ActivityInfo to APIs using tokens to share data with external software like Excel, Power BI, or GIS.

To what extent can ActivityInfo deal with qualitative data? We can use multi-line text fields. We try to categorize options where possible. There are also formulas to search for specific words within text to group data.

Is it possible to share a link to the respondent where they key in their own data? Yes, this is the data collection link feature we discussed.

Can I connect ActivityInfo to different CRMs like Salesforce? Yes, generally this can be done through the API.

How to score in ActivityInfo? You can use calculated fields. For example, you can assign values to specific answers in a selection field (e.g., if 'Individual' return 1, if 'Cooperative' return 2) to create a scoring system.

Can we have a mechanism of data review before it reaches the server? Currently, there is no specific holding area before the server. However, you can use a 'reviewer-only' field to mark records as validated or pending, and set permissions so only validated records are used in reports or references.