00:00:00 Introduction

Jeric: Thanks so much for that introduction, Faye. We will review our outline for today to start. We will spend a few minutes at the top of this webinar going over some general best practices around data migration, but then we will launch quite quickly into hearing from our guests today. We will hear first from Shirine from Missio Invest to hear about her insights from her experience migrating her data from an old system into ActivityInfo. Then we will hear from Michelle regarding her experience at the Spotlight Initiative as well. We will hear from Eliza, who was supporting Michelle closely as part of that.

So, we will hear a fair bit of insights from customers who have gone through the trenches and experienced data migration firsthand. I am sure that will be quite interesting for everyone here today. Then at the end, we will save some time for Q&A. So feel free to put your questions in the Q&A box, and we will be sure to address those questions at the end.

00:01:23 Best practices for data migration

Jeric: Without any further ado, let's get started with a brief discussion on best practices around data migration. At this point, you might have managed to get everyone on board to using a brand new data management system. You have now gotten everything set up. You have got your database. You have now come to a point where you are needing to migrate data into your new database from your old system. Now data migration might not be the most exciting part of your data management project. In fact, it could be the most painful one that you might ever be involved with. But, nonetheless, it is a critical piece to get right. A poorly run data migration project will likely result in data quality issues and unhappy users. But a data migration project that is done well will pave the way to long-term adoption of your new data management system.

Here at ActivityInfo, we have had the benefit of helping dozens of customers through data migration projects of various sizes, and we are happy to present to you today some things that we have learned along the way. First, let's start with some things to keep in mind before you even begin your data migration project. First, you will have to ask this question: What does your data currently look like? Within this, there are a couple of key pieces to review. First of all, it will be important to review the data entities. What are the different categories of data that exist in your old system? Reflect on what kind of data that is, whether it is master data, reference data, or metadata. You will need to review the structure that your data is stored in. Is it actually stored in a structured format, or is it more unstructured? Would you need to convert your data into a more structured way to fit into your new system?

Next, you need to think about the types of data that you have. For example, whether your data is stored as text, as quantity, or maybe as date fields. Within that, it will be important to keep in mind what format the data is in. For example, when you are thinking about date-related data, is your data stored as the full date, or is it capturing a specific month only? Finally, it is important to review the data model. That is to say, how are the different pieces of data connected to each other? What is the nature of the relationships between your different sets of data?

Having reviewed what your data currently looks like, the next step naturally will be to think about what structure your data needs to be in to be migrated into your new system. If you have migrated to a new data management system like ActivityInfo, you will need to think about and review very carefully what the data model looks like in this new system. How is the data expected to be connected with each other? From there, you will then need to understand what changes are required to this new data model. It could be that you are coming from a database that is already relational in nature, in which case you might have an easy time moving over those relationships into ActivityInfo. But in some other cases, perhaps you are working with Excel files or data scattered in different files, and there may not be a formal data model, in which case you will have to think more closely about how that will fit into this new data system.

Think about whether any transformations are required on your data. It will be important to make sure that you do those transformations in such a way that will generate a set of data that actually conforms with what is expected in your new system. You can think about where to do these transformations. It is possible that you could do those transformations in the source where your old data lives, maybe through an intermediary platform, or maybe directly within ActivityInfo. Depending on the kind of transformations that are required, you can think about which place is most appropriate or easiest to do those transformations. Once you have understood what transformations are needing to be done, then you can proceed with the actual preparations, which could be in the form of creating import templates that you can then upload into ActivityInfo. Or, if it is more complicated or involves a large set of data, you might choose to develop scripts. In either case, whichever tool you use for doing the import, those will need to conform with the expected data model.

Finally, one more set of questions you will need to ask before you begin is what resources you have. Identify what is required to get the whole data migration process done. Make sure you have talked to all the stakeholders involved and understood what their expectations are in terms of what needs to be migrated by when. From there, you can plan out your overall data migration approach. Decide whether you will go with a "big bang" or a "trickle-based" migration approach—essentially, whether you are going at it all in one go in a short amount of time or spreading it out over a longer period. We always like to recommend the latter option if you do have the benefit of having a bit more time, just to make sure that you are testing along the way and making time for any corrections that need to be made.

During the migration itself, the main question you want to ask here is: Is the migration running as expected? We advise that you start with some small-scale tests. Pause any routine data collection to minimize any disruption to actual data that needs to be submitted into the database. Make sure you verify your record counts. Actually make sure that the number of records that end up in your new system matches the number that you had in your old system. If there are any errors that you have identified, take the time to correct them immediately, and also keep track of what changes you needed to make to your data collection forms. Finally, keep stakeholders informed. If you need to change your timelines, it is important to keep everyone informed.

Once you have migrated all of your data into your new system, you will need to ask: Were all the data migrated as expected in the end? You want to assess the integrity of your entire dataset. Make sure that all of the data that you wanted to migrate actually ended up in your new database. Check to see whether all of the connections and references are properly set up. Check whether integrations are properly working, especially if you have other systems where this data needs to connect to. Make sure to verify that the data looks good from the perspective of your end users, ensuring that they have access to all of the data that they actually need to work with. Finally, it is important to not forget to report back to your key stakeholders and update them on the final outcome of your data migration project.

00:10:28 Missio Invest: Insights and lessons learnt

Shirine: Thanks to you and Faye for the very nice intro. I'll share my screen. So, "Moving House: Migrating Data into ActivityInfo." First, a couple of moments about who Missio Invest is and the context in which this data migration happened so that you have an idea of the environment that it was in. Missio Invest is a social impact fund that operates in Africa. This means we provide patient capital to social enterprises of the Roman Catholic Church in the sectors of agriculture, microfinance, health care, and education. We have a portfolio of $60 million and 60 loans right now in 10 countries.

In 2020, when I joined Missio Invest, we were using a platform called Preqin. It comes out of the finance sector and investment banking; it is a deal-making platform. We used it for the borrower pipeline and to capture impact data. But with Preqin, it was a rather unhappy relationship. In the year that I worked with them, the counterpart changed three times. There was no documentation on the platform. We couldn't edit or delete data. We couldn't change our data models. So as our organization was changing and growing, this model and the software couldn't support that growth. We couldn't download the data or write reports. We evaluated what was out in the finance market, and then we looked at M&E software. In the end, we decided to go with ActivityInfo.

I'm going to tell you a brief story. Six weeks ago, I actually moved house. The story has to do with data. In the move, my wardrobe, where I keep my clothes, got taken apart and then rebuilt here. The lovely man who helped in that move didn't do that good a job in rebuilding the wardrobe. As I was putting my clothes into it, it all fell down. All the shelves came down. Now at first, the idea of having to rebuild the wardrobe myself was not my idea of fun, but then I realized I can rebuild it in a way that serves me best. I want to argue that when you are migrating a data system, you have a chance to build it back better, just as I did with my wardrobe.

While this webinar is titled data migration, I think—and at least this was definitely the case for us at Missio Invest—that you are migrating more than data. You are migrating a system, and you are also migrating users into a new setup. I think any migration has three elements you need to consider: the wardrobe (system), the clothes (data), and the users.

Let's dive into the system. How do you rebuild your wardrobe? How big does it need to be? Where are the shelves going to be? It is a chance for a new beginning. You want to create a scalable solution that can change and adapt as your organization does, and relational databases are an ideal starting point for that. First of all, you want to redesign your database or your module with best practices in mind. For example, normalizing data means avoiding redundancy. The new reference fields in ActivityInfo are a great way to implement that. Use easy naming and be consistent about your naming conventions to make it easy for your users. Document what you do and why you came to the decisions that you made. Review the data model: how is your data going to interconnect? What is the hierarchy? What are the primary keys? We looked at all of the indicators that we got out of the old system and looked at each one to decide: Are we keeping it? Are we dropping it? Do we need to add more information?

ActivityInfo is a very flexible design environment. It is very easy to set up, delete, change, and change the hierarchy around. That is why I would encourage you to play with it and experiment with it a little bit before you go to the next step, which is data. We had excellent support by the team at BeDataDriven throughout.

Next point: Data. Would you have dirty clothes in your wardrobe? Things that you are never going to wear again, that don't fit you? Data, like clothes, needs to be tidied and verified. Make it tidy. Have you heard of Marie Kondo? Her advice when you reorganize your wardrobe is to make piles. Take all of your data out and have a real overview of what you have. Is it all there? Back up a version. Put it in a corner where you are never going to look at it again because as you are going through the process of migrating and cleaning, you are going to change things around. You need a version to refer back to in case something goes wrong.

Check for holes and consistency. Is your data consistent? Are the names spelled the same everywhere? In our case, the dates were in the US format, so that had to be changed. Are there duplicates in your data? Next came sorting the data, getting it to a form that we could upload into ActivityInfo. My advice here would be to already make sure that the names of your columns are similar to what you have in ActivityInfo and that the order of the columns is similar as well. It will make uploading much easier if you are using the import tool.

When importing, do your primary keys match? It will make your life easier if you don't have rules such as required fields and validation set during the import. At least for me, our database had a few holes because of how the previous tool was. I needed to be able to upload data with holes in it and then ask our country investment managers to go over it. If I had required fields and validations on that, I would have made it much more difficult to do that. Also, document as you go along. I was in Kenya for a workshop, got COVID, and everything stopped. When I came back, I wasn't entirely sure where I had stopped. It was really good to have the documentation that allowed me to pick it up where I had left off.

Now on to our users. You could argue that that is what it is all about in the end. You want your users to be competent in the tool that you are creating for them. The easier we make it for them to use the tool, the better acceptance, usage, data quality, and overall ownership will be. Consider user personas. Define a certain type of user, for example, data entry or a manager. Think through how they are going to interact with the database.

Regarding user training, I had several general introduction webinars. First was to introduce ActivityInfo as a tool, then to introduce the hierarchy, then to show them the data that had been in there. Then we did one-on-one training sessions. In those sessions, they had to drive. They had to share their screens, and they had the mouse. They had to go through their country data with me. By forcing them to drive—learning by doing—I hope that it was a much smoother transition for each of them.

I also sent out regular "how-to" emails at the beginning, tweaked to our particular use case. For example: "How do you set up a borrower in the pipeline?" or "How do you download ActivityInfo on your mobile phone?" I also had an AI working group throughout the data migration. This meant that ownership of decisions was spread throughout the team. I could just pop a question into the chat and ask the hive mind if I wasn't sure about a solution.

If I had to pick my big idea here, it is that you are migrating more than just data. You are migrating your system, your data, and your users. Keep all of that in mind as you are going on this journey.

00:29:03 The Spotlight Initiative: Insights and lessons learnt

Jeric: Great, Shirine. This was a fantastic presentation. I love the comparison with building a wardrobe. I would like to hand over the microphone to Eliza and Michelle, who are going to share their story for the Spotlight Initiative.

Michelle: Thank you, everyone. The Spotlight Initiative was created in 2017. It is the world's largest targeted effort to end all forms of violence against women and girls. It is an EU and UN-funded initiative. It differs from other organizations in that it functions similarly to interagency efforts. We have various sub-agencies working under us such as UN Women, UNDP, UNICEF, etc. This means that we receive country-level data from all these agencies who are working on different activities that all have the same objective. We deliver high-impact results in more than 30 countries and regions.

Prior to this, we used JotForm and Google Sheets. Because we are the first of this kind of interagency effort, we could not find a single system that was operating under all agencies. We resorted to using Google Sheets. At first, it was great because it was a custom database; we basically built it from the ground up. Unfortunately, it was not scalable. As we have over 30 programs and country offices, it was difficult to upkeep the system as the years went on. It involved replicating the same work over and over, essentially building a database every year. Additionally, it wasn't secure. Data could accidentally be tampered with. Data analysis and visualization were quite limited.

We decided we needed to look for different software, and this is where we came to ActivityInfo. We needed to collect secondary data on program indicators by the implementing countries, have access for implementing countries for further data collection, and have a system that could provide program and global level reporting. The type of data that we have is both quantitative and qualitative data points. We had our baseline information which we had to have migrated over, as well as previous annual data.

Eliza: I will take over regarding the specifics and how we approached data migration. When we started, Spotlight was entering the fourth year of implementation. This means in practice that we had to migrate three years of data to ActivityInfo across various indicators, quantitative and qualitative information, and 30 different countries. This was spread around different Google and Excel sheets that we needed to consolidate.

If we were to put it in a linear process—though it is hardly ever linear—we started with the database design. We determined what we wanted to succeed at. At the same time, we identified our data source. We identified the migration timeline, and this was part of our broader timeline to setting up the database up to the launch. We coordinated with the Spotlight team, especially Michelle, in order to prioritize and determine how we can communicate feedback. We needed to create templates for communication to handle corrections.

Then we started performing the data migration gradually according to the timeline. We prioritized our forms. It was a tight timeline back then. Michelle and the Spotlight team were validating on their side, and we were validating on our side. We were both performing the testing of the data. The migration was performed by the ActivityInfo team. On our side, we could verify the technical component very easily. On the Spotlight side, they could verify if everything makes sense contextually because they know the program.

We deemed it necessary to highlight our roles and responsibilities. The migration was performed by the ActivityInfo team. The Spotlight team provided all the necessary data sources and had the decision-making power when we needed to perform alterations or modifications of the data. We guided the sessions, holding weekly or even biweekly calls to solve all the questions that had arisen. Both teams performed the testing. This two-way testing has two different objectives: the technical component (everything works fine, no errors) and the context-wise component (checking if something is off based on program knowledge).

The new database had the same objective as the previous data collection but was quite different in structure. This means in practice that we needed to transform the previously existing data. In certain cases, this was possible by Power Query. In other instances, we had to perform manual data collection. The ActivityInfo importer facilitated a lot of the process.

In a nutshell, a few recommendations on our side: After the database design and testing, at the moment that we start the data migration, it is good to take a step back and reevaluate if the timeline still holds true or if we need to make any changes. Secondly, during this process, it is frequent that you encounter the need for an extra change in the form. Keep an audit log or a common file where you keep track of changes. This facilitates communication, especially if you have many changes on both sides.

Michelle: Yes, the timeline was most definitely one of the most important things to keep track of. For us, the timeline was very tight due to the volume of migration and our procurement processes. With the complexity of our program and having to restructure our previous database, the testing period actually coincided with the launch of ActivityInfo. While we had our tight timelines and obligations to our donors, at a certain point, we had to launch our forms for the reporting period.

A lot of the data migration errors can result in challenges in reporting. If you can do these reviews prior, that is essential. Frequent communication and accounted time function as a mitigation measure to those challenges. In addition, the post-migration period may yield corrections. It is recommended to allocate sufficient resources in the post-migration period and define how feedback will be communicated and handled.

00:47:11 Q&A Session

Jeric: Thank you very much, Michelle and Eliza. I would like to invite everyone to submit your questions using the Q&A box. I see one question here that I can address to Shirine. How would you communicate with stakeholders throughout the data migration process?

Shirine: In our case, I had invited everyone to be part of the AI working group. For me, the future users are definitely also stakeholders. The key decision-makers were always involved in milestone presentations. By always keeping everyone informed about progress at regular intervals and proactively making presentations to the team, we ensured everyone knew where we were. I was basically very open in communicating.

Jeric: I have another question here addressed to me: How do I need to formulate my data structures?

Jeric: I don't think there is a short way to answer that question. There are some considerations that you will need to factor into designing your data structure. You will need to think about your data workflows—how in practice you are planning to collect your data—and secondly, in what way will you utilize your data, what kind of analysis you will need to do, and how you will report on that data. Those two big considerations need to be factored into a data modeling exercise.

Jeric: I have a question addressed to both Shirine and Michelle from Simone Capolupo. One, was there resistance from the people who had to collect data in the field to following the changes brought by the data migration process? And two, did you migrate only impact/program data, or did you migrate other kinds of data such as financials?

Shirine: Two personas collect data in our case: the borrowers and Missio Invest internal managers. We had already been collecting data before, so there wasn't resistance per se to the change of the tool. We migrated predominantly impact data. We are also starting to use ActivityInfo for quarterly reports that contain some financials because a database has the advantage of easy aggregation, but most of our loan calculations are still handled separately.

Michelle: Prior to even engaging with ActivityInfo, we had gone out on an exercise to contact as many of our programs as we could to see what difficulties they were facing with our current reporting system. We kept those in mind when we were making the changes in ActivityInfo. Even after our first launch, we really didn't face any resistance. We did receive recommendations on what could be improved. Regarding the second part, our department of M&E does focus on impact data. We are considering looking into adding the financial side to ActivityInfo as well, but currently, we only have impact data.

Jeric: I see also one question about migrating data from other platforms such as Kobo or ODK and what procedures we should follow.

Jeric: ActivityInfo does allow for migrating data from other systems like Kobo and ODK. We do have an importer available that you can use to easily import data that you export out of other systems. So long as the dataset is in the structure that conforms with the forms that you have in ActivityInfo, you can use the import tool for that.

Jeric: I have a last question about scenarios where data was lost during data migration.

Shirine: That's why I made the recommendation at the beginning: Keep a copy somewhere. Put it in a corner. Don't touch it again.

Michelle: I can second that. Don't get rid of your old data. We'll just have it as an old file to refer to if there are any inconsistencies. We also recommended having a period after migration to do a final review, cross-checking between ActivityInfo and your old platform.