00:00:00 Introduction

Hello everyone, and my sincere apologies again about the network. If at any point you cannot hear me, please just indicate in the chat. Today we will discuss data preparation and analysis setup, followed by analysis and visualization tools in ActivityInfo. In between, we will showcase some demos of worked examples, and then we will conclude with best practices and a Q&A section.

00:00:34 Data preparation and analysis setup

Now, let's begin with data preparation and analysis setup. We will be using a scenario today to talk us through the session and to illustrate some examples. The scenario is basically a crisis intervention model for conflict-affected households. Our setting is Ethiopia, but these are all dummy data, so it does not reflect any real human beings.

In response to the conflict-related violence in Ethiopia, a crisis intervention model created a vulnerability assessment tool to prioritize households in need of assistance. The tool evaluates criteria including household size, dependency ratio, income loss, income loss duration, injuries, displacement, and various types of scores. As we will see when we showcase the demo, the scores derived from the criteria basically categorize households into three vulnerability brackets: highly vulnerable, moderately vulnerable, or not vulnerable at all. This determines to a large extent their eligibility for the assistance that we have to provide within the project. The aim of the model is to effectively allocate resources and support to the most affected persons within this conflict region.

In preparing your analysis, there are a list of factors to bear in mind. The first is one of the most important, which is to understand your data model.

00:02:45 Understanding the data model

Understanding your data model is the first and most critical step because it provides you the necessary groundwork for effectively exploring your data. It helps you easily interpret your data and, ultimately, it leads to more accurate and valuable insights. If you understand your data model, then you understand what you can really get from the data.

In this data model, we have a reference folder which has in it a caseworker form. This caseworker form contains details such as the names and contact information of the caseworkers involved in the assistance program. It also helps track the personnel responsible for providing support to specific households. Remember that the beneficiaries have been divided into several brackets. Based on this, it lets us know, for instance, who is in charge of vulnerable, highly vulnerable, or moderately vulnerable people.

The folder also contains a partner form. This component captures the name of the partner organization or organizations that are involved in providing assistance within this project. It helps track collaboration and partnerships that have been established to support the assistance program. Next, we have the vulnerability assessment tool. This is the main tool which is most important for us. This component tracks folks that require assistance. It includes aggregate records of each household's vulnerability, such as their socioeconomic status, living conditions, and other details, including the reference to the partner and the caseworker form that we had in the reference folder.

Attached to this vulnerability assessment tool—which is the parent form—we have a sub-form labeled the vulnerability score form. This sub-form provides detailed information about the vulnerability scores assigned to each household. It includes factors like the head of household name, ID, dependencies (e.g., number of family members, children, disabled people, or elderly people dependent on this person), marital status, income situation, displacement risk, and loss of income risk. The scores from here are aggregated because they help us prioritize assistance based on the severity of vulnerabilities.

00:06:44 Clarifying analysis objectives

Having understood your data model, you also want clarity. Clarification involves defining the scope and objective of your analysis. You can't start analysis without knowing why you want to undertake it. It is about clearly articulating what you hope to achieve through your analysis and what insights you aim to uncover. By clarifying your objectives, you can ensure that your analysis remains focused and aligned with the overarching goal of your project. For instance, do you want to undertake summarization (calculation of totals, averages, minimums, maximums), comparisons (actual versus target), differentiating between disaggregation categories, or understanding trends?

Next is identification. This entails recognizing the specific data elements or variables that are relevant for your analysis. This includes identifying key metrics, variables, measures to be summarized, and dimensions for disaggregation or filtering. For instance, you might want to get percentages, identify your numerator and denominator, and the attributes that would help you answer your analytical questions. By clearly identifying these factors, you can focus your analysis on the most pertinent information.

The last step is mapping. This is largely tied to understanding your data model but goes a step further. You need to understand the overall landscape of your data, including its sources, formats, and structure. It is about visualizing how different data sets are interconnected and how they contribute to your analysis. You should identify the inputs within the data model by locating relevant forms and fields. Next, you can assess if any transformation—as we would see with calculated tables—is required. Additionally, you can assess if you need to combine various forms or calculate certain ratios. By mapping your data, you can identify potential gaps or redundancies.

00:10:11 Tools for analysis: Pivot tables

Let's move into the tools that are relevant for our purpose here. In ActivityInfo, for analysis, we have tools like calculated fields, calculated measures, and calculated tables. To design reports, we use pivot tables. Pivot tables are powerful tools for data analysis, allowing you to summarize, compare, and identify patterns and trends in your data set more efficiently.

When designing reports with pivot tables, you can select relevant fields from forms, sub-forms, or reports, and apply measures. To these measures, you can apply dimensions to organize and analyze the data effectively. By summarizing data, a pivot table condenses large data cells into manageable summaries, making it easier for you to grasp insights very quickly. You can also make comparisons between different categories or time periods. Pivot tables facilitate deeper analysis, deeper insights, and understanding of your data.

00:12:41 Calculated fields

Expanding the analytical capabilities of pivot tables is where calculated measures, calculated tables, and calculated fields come in. Let's start with calculated fields. Calculated fields in ActivityInfo offer several benefits: they automate calculations, save time on repetitive tasks, ensure accuracy by minimizing errors, and provide real-time updates instantly reflecting changes in data.

Calculated fields in pivot tables and form design enable you to perform various calculations like additions, subtractions, multiplications, division (for instance, of indicators), as well as counts, distinct counts, and averages. You are able to apply logical functions and use geographic functions. Calculated fields empower you to conduct additional calculations on summarized data, ranging from basic arithmetic operations to more complex formulas. These calculations enhance insights and metrics derived from your existing data without modifying the original data set. Conversely, calculated fields in forms automate value calculation based on predefined logic, typically within your individual form records.

00:14:47 Calculated measures

Next, we have calculated measures. Calculated measures in ActivityInfo offer enhanced analytical capabilities by allowing you to go beyond the fields already present in your form. The measures enable the combination of data from different forms into a single measure, facilitating comprehensive analysis across data sets. You can utilize various types of aggregations simultaneously within a single measure, aggregate data at multiple levels, and apply explicit filters to perform calculations on specific subsets of data. Calculated measures are not constrained by row context, unlike calculated fields. This expands the range of analytical possibilities and provides deeper insights into your relational database.

Calculated measures support aggregation functions including SUMX (calculates the sum of an expression for each row in a table), AVERAGEX, COUNTX, COUNTDISTINCT, MEANX, and MAXX.

Calculated measures have served various purposes for our customers. For example, they help in assessing the effectiveness of policies, programs, or interventions. One example is indicator evaluation, such as assessing the effectiveness of policies regarding violence against women and girls by aggregating scores across different dimensions per country or region. For monitoring and reporting, you can employ calculated measures to track progress and outcomes, such as participant understanding or activity impact. You can also evaluate training programs by determining the percentage of participants who report an improved understanding. Finally, you can implement calculated measures to aggregate data from specific activities within different clusters, like combining WASH and Education clusters to assess the overall impact of interventions.

00:20:09 Calculated tables

After calculated measures come calculated tables. Calculated tables facilitate the transformation of data into a format that is suitable for analysis. It addresses potential misalignment between the data collection structure and the ideal analysis structure. These tables enable the restructuring of data from its original form to one that is more convenient for analysis purposes. While forms are designed for simplicity during data collection, calculated tables help you reorganize your data to achieve easier analysis.

ActivityInfo supports a number of table functions. For instance, we have ADDCOLUMNS, which allows you to add new calculated columns to existing tables based on specified expressions. There is also SELECTCOLUMNS to select specific columns, and FILTER to extract a subset of the table that meets a specific condition. We also have the UNION function, the SUMMARIZE function, and the PIVOTLONGER function.

The UNION function combines data from multiple sources into a new table. For example, in a scenario involving food distribution and medical outreach activities, the union function helps merge beneficiary data from both activities into a single table, enabling comprehensive analysis of beneficiaries across various initiatives.

The ADDCOLUMNS function allows you to append new calculated columns. For example, in a food distribution scenario, this function can be used to add a new "sex" column to the table by fetching gender information from a separate beneficiaries table using a lookup.

The SUMMARIZE function generates a summarized table based on specified groups. For instance, it can summarize the number of distinct beneficiaries from each activity, resulting in a new table where activity names are grouped and the count of distinct beneficiaries is provided for each group.

00:26:36 Demo: Calculated fields and measures

We have used calculated fields in many fields within this sample database. We use it to calculate the total vulnerability score, shelter score, education level score, and area score. All of these scores are made up of existing data sets within the form.

Let's look at the "standard of shelter" calculation. The syntax here checks if the shelter meets certain criteria. If you are living in a hut, a cave, a makeshift shelter, or a tent, it assigns a value of three. Otherwise, it assigns a value of zero. The IF statement is the conditional statement that evaluates whether the condition is true or false. At the end of the day, the score contributes to the total vulnerability score per household, which allows us to allocate resources accordingly.

We also have calculated measures. In our analysis, we have a calculated measure that essentially calculates the proportion of rows in the table where the total vulnerability is less than 50, relative to the total number of rows in the table. It achieves this by dividing the count of rows meeting the condition by the total number of rows. What we hope to achieve with this is calculating the percentage of vulnerable persons. In this example, we got 8%. If you check the table view, you would find that we have just one household designated as moderately vulnerable compared to the others.

00:34:09 Demo: Calculated tables

The calculated table here is generated by the PIVOTLONGER function. This expression helps us restructure our data for easier analysis. In our original table format, we had separate fields for children (0-18), the elderly, and persons with disabilities. The point of using PIVOTLONGER is to combine all our dependents—adults, elderly, children—and group them under one heading. This is motivated by the need to streamline our analysis.

Instead of having different rows for adults, elderly, and children, we have just one row: the dependents row. From this, we can tell, for instance, that beneficiary one has one adult, one elderly person, and one child. It condenses multiple columns representing different categories of dependents into a single column. It also consolidates counting, making it easier to calculate aggregate statistics on the dependents as a collective group.

00:38:10 Visualization features

There are key features and functionalities for visualization in ActivityInfo, encompassing various tools to present data effectively: charts, maps, reports, and report layouts. For real-time data visualization, ActivityInfo enables you to create reports that dynamically connect to your data, ensuring the visualization reflects the most current information available.

You can utilize pivot tables and a variety of charts—including bar charts, line charts, and pie charts—to analyze and present your data in real-time. This functionality empowers you to make informed decisions based on up-to-date insights.

You can also generate maps constructed using geographical point fields or by referencing the built-in geodatabase. Maps can utilize different base maps such as lights, streets, satellite, or population density, providing you with the option to visualize spatial data effectively.

00:40:24 Notebooks and dashboards

ActivityInfo facilitates the synthesis of multiple reports into a cohesive narrative using Notebooks. These notebooks serve as a collection of tables, charts, maps, text, and section headings, enabling you to merge individual analysis or visualization into a structured and coherent narrative.

Dashboards in ActivityInfo offer a condensed and compact layout compared to notebooks. They allow you to combine tables, charts, maps, and text in a grid format. You can design dashboards by adding components which can be further analyzed by returning to your analysis view. This layout enables administrators and decision-makers to merge individual analysis and visualizations, facilitating easy identification of issues at a glance.

00:42:03 Demo: Creating a dashboard

I will quickly show a bar chart here with caseworkers and cases assigned to them. If you have this bar chart displayed, you are able to tell the workload of each caseworker. As you can see, James Kizito has the highest number of cases, and Yusuf Valley has the least. You can easily make decisions on who to assign certain cases to. We also have a line chart that presents the total vulnerability score across a period of time, from March 2023 to March 2024. We also have a pie chart where we calculated the contribution of the long-term loss score to the total vulnerability score.

To create a dashboard from your table view, you go straight to "Analyze". From there, you can add a dashboard directly. You can add different components like a map, a text block, a pivot table, or a header block. Header blocks help you provide different dimensions or sections within your dashboard.

For example, if we want to add a count of all records as our measure, or add the sex of the household heads, the interaction with the platform is highly intuitive. When you click on "Done", you can resize the component to make sure your viewers can see everything. You can label it, and if you want to add another component, you do the same thing.

00:45:58 Best practices for report design

Before embarking on report design, it is essential to comprehend your data and the analysis objectives thoroughly. You should identify your metrics and the trends you want to know. Next, you want to streamline your reports by focusing on key messages and avoiding clutter on graphs and maps to effect significant trends and patterns. You also need to prioritize accessibility and user-friendliness to accommodate users with varying levels of expertise. Lastly, consider your intended audience for your report and use feedback to refine and enhance your report designs in terms of usability, relevance, and effectiveness.

It is also important to mention that reports can be personal (viewed only by the designer), shared (viewed by users with specific roles), or published (viewed by anyone). We often get a question regarding what happens when people do not have access to the data sets used to create the report but have access to the report itself. The simple answer is that even though they do not have access to the source data, they will have access to the reports and will see the records within the report as though they had access to the original data set.

00:48:47 Q&A session

Fay: Thank you, Victoria. One question we had was if we can see in practice how you create a dashboard.

Victoria: (Demonstrated in the previous section).

Participant (Noah): My question is a concern. We joined the meeting late, so I would like to request the host to share the document after the presentation.

Victoria: Yes, we will share it.

Participant (Julius): Mine is not a question, it's a request. I think we need a session specifically on how to design tables, pivots, dashboards, and reports visually. I've been using ActivityInfo for the last year, but mainly as a data repository because creating visuals is difficult. Even labeling a pivot or a bar graph is a bit difficult. So, I request a practical session on creating a dashboard.

Victoria: Thank you, Julius. We have previous sessions where some of this was covered, but we will consider having a separate session just for the practical aspects of how to do this. Faye will share a link to a form where you can indicate specifically the areas you find difficult so we can tailor materials to your needs.

Fay: In the end of the webinar, you will receive a satisfaction survey report. Please add your points in the replies. Thank you.