Migrating from spreadsheets to ActivityInfo for program and global level reporting with the Spotlight Initiative
Created in 2018, the EU-and UN-funded, Spotlight initiative is the world's largest targeted effort to end all forms of violence against women and girls. Functioning as an inter-agency pool fund, the Spotlight Initiative supports country and regional UN teams to track the effort of various implementing agencies, including the UN Women, UNDP, and UNICEF, who conduct different activities under common objectives.
Through these activities, the initiative delivers high impact results in more than thirty countries and regions across Africa, Asia, the Caribbean, Latin America and the Pacific. Spotlight Initiative uses a comprehensive model to address violence against women and girls by supporting the development of laws and policies, strengthening institutions and data collection systems while promoting gender equitable attitudes, providing quality services for survivors of violence and their families, and strengthening women's movements and civil society organizations.
On a programmatic level, the country and regional programmes receive country-level data from their implementing agencies which report coordinated results to the Spotlight Initiative Secretariat. The Spotlight Initiative works with both qualitative and quantitative data and performs baseline and annual data collection to facilitate monitoring and evaluation processes. The Spotlight Initiative secretariat has designed an Information Management system for results reporting and monitoring and evaluation of the progress.
The ActivityInfo team would like to thank Michelle Unda, Data Analyst for the Monitoring & Evaluation unit at the Spotlight Initiative. You can also watch the recording of the presentation on this topic.
The challenges with spreadsheets as a database
Initially, the Spotlight initiative developed an in-house database using Jotform and Google Sheets to facilitate country level reporting. The solution provided a custom database for every program and a separate global database that was used for data analysis and aggregation. Even though it was customizable and highly accessible for the team that worked with over a hundred people, it soon proved to be challenging.
The challenges included:
- Scalability and upkeep: There were over 30 country offices and it was becoming challenging to maintain the system as new programs needed to be added. The process of replicating the work for the reports needed on an annual basis soon became tedious and time consuming.
- Data security: The system wasn’t secure enough. Data could accidentally get tampered with when for example a link or a formula connecting a sheet to another sheet was deleted. The global office would then need to step in and apply corrections.
- Data analysis and visualization: The options offered by Google Sheets for data analysis and visualization were limited.
ActivityInfo as an alternative to spreadsheets
In the fourth year of the Spotlight initiative’s implementation, the global office decided to move away from the spreadsheet-based solution and selected ActivityInfo to fulfil its newly set objectives. The initiative wished to create a system to:
- Collect secondary data on program indicators by implementing countries
- Allow implementing counties to have access to the data collected
- Be able to do program and global level reporting
To migrate into ActivityInfo, all existent data had to be moved from the spreadsheet-based database to the new system built in ActivityInfo. For that, the Spotlight Initiative collaborated with the Customer Success team of ActivityInfo and ended up with an information management system that reflected the data collection needs of the initiative. Different data collection forms were created to correspond to baseline and annual questionnaires, and subforms were used to facilitate qualitative data collection too (e.g. list of policies which correspond to specific indicators) for baseline and annual reporting purposes.
Migrating to ActivityInfo
The global team of the Spotlight Initiative and ActivityInfo collaborated to migrate data of the past three years of work of the initiative across thirty countries. With a systematic and collaborative approach the migration was completed in time and new programs were added to ActivityInfo.
Even though the process was not always linear, as some actions were inter-dependent, the migration process included the following steps:
- Database design: Decide the scope of data collection and how existing system translated into a relational database in ActivityInfo
- Identify data sources for migration: Identify how existing data sources need to be restructured to correspond to the relational database schema
- Identify migration timeline: Coordinate with the Spotlight Initiative team, define priorities and how to communicate feedback
- Perform data migration: Data migration performed by the ActivityInfo team, with frequent meetings to solve challenges
- Validate and test data: Validate and test data entries. Validation was performed both by ActivityInfo (technical side) and Spotlight Initiative team (end result matched requirements).
To ensure a smooth collaborative process based on clear communication, the two parties defined roles and responsibilities for the migration process:
Spotlight Initiative team:
- Provision of data sources
- Decisions on when alterations to existing data was needed to correspond to the new database schema
- Decisions on appropriate approach when data inconsistencies arose in existing data sources
ActivityInfo team:
- Migration process
- Guidance to solve questions arisen during the migration
Shared responsibilities
- Sample data testing following migration
- Corrections during post migration period
Because the new database schema resulted in major data transformation, Power Query was used when possible to facilitate the process. When it wasn't possible to use it, due to the data format, manual data entry would be performed. The ActivityInfo importer facilitated data import and made it possible to leverage relevance and validation rules set in all data collection forms to ensure a harmonized, correct data structure.
Certain key recommendations that came out of this process were the following:
Migration timeline: Evaluate the migration timeline and validate existing data sources after the database design and testing phase. The new database structure might be very different from the previous structure and you might need to perform major restructuring on existing data which should be taken into account when planning the migration.
Audit log of actions: To make communication easier, keep an audit log of all the actions performed. Pre-existing data collection errors can create challenges when creating the new database so make sure that frequent meetings can help tackle such issues in the beginning.
Maintain frequent communication: Mitigate challenges that might arise post migration, especially if the system has gone live, by maintaining close communication between the two teams.
Allocate sufficient resources in the post migration period: Define how feedback will be communicated and handled in the post migration period and ensure there are enough resources allocated for that.
Conclusion
All in all, the Spotlight Initiative decided to move to ActivityInfo from a spreadsheet-based system to mitigate challenges related to upkeep, scalability, data security and data analysis so as to facilitate the work of the country and regional programmes’ reporting to the global office.
To achieve this, the Spotlight Initiative team collaborated with the ActivityInfo team to perform the migration of the data of the past three years. A new data model was developed to match the country and global level reporting requirements and a carefully planned migration strategy was implemented.
To ensure a successful migration, the Spotlight Initiative and the ActivityInfo team suggest frequent, clear, and close communication as well as careful planning and resource allocation before, during and after the migration.
The ActivityInfo team would like to thank Michelle Unda, Data Analyst for the Monitoring & Evaluation unit at the Spotlight Initiative. You can also watch the recording of the presentation on this topic.