You need to have clean data to upload your grants or project information to ImpactMapper. This takes considerable attention to detail, can be time-consuming, and needs to be done manually. But don't worry, we've put together this guide to help you, and you can always reach out to our team with any questions.
You will often be copy-pasting data and entering information by hand, both of which are prone to errors. If you're not careful, you could delete data or have mismatched rows and columns, creating an inaccurate dataset!
We recommend you do this as a team, to double (or even triple-check!) each other's work. You might also find tools like OpenRefine useful for helping you resolve common issues in large datasets. Don't clean your data if you're in a hurry - it'll probably just cause more trouble than it’s worth!
So here's a step-by-step method to clean all of your CSV data according to our template.
Examine the file
You'll need to export your information as a CSV (comma-separated values) file in order to import it to ImpactMapper. It's easy to do this in Excel, Google, or any other spreadsheet software.
Step 1: Open your CSV file.
Step 2: Check the formatting so you can easily view all of your information. Sometimes CSV files have wrapped text, which can make it hard to work with. Expand rows or columns so you can check for any errors, and ensure consistency. This will make it much easier to clean!
Step 3: Make sure that your column headers match the headers we use on ImpactMapper (see our template here). If not, please copy them over so they match; they'll be rejected at the upload stage otherwise! Here's a list of the column headers we use to sort data:
Step 4: Make sure there is no 'hanging' data - i.e., extra rows of data not attached to a grantee, grant, or project.
Step 5: Make sure all cells are filled in with information in the CSV. If you import incomplete data it will be very hard to catch later on. This also means you won’t get a consistent analysis across all of your portfolios, grantees, projects, etc. For greater efficiency and more data reliability later on, please take the time to ensure you start with a complete dataset.
You may need to go back to your colleagues or partners to double check information entered. We recommend that you do so even if all your fields are populated, just in case!
Step 6: Make sure any data fields with multiple answers are separated by semicolons. If they're separated by commas only, they may not import properly. If one of your project uses multiple strategies (like advocacy and capacity building), you want to make sure all of this is captured in your data file.
Our team will also review your CSV data when it's sent to us, to check for common inaccuracies. If we find any, we'll send it back to you for updates and cleaning. Since we don't know your work as well as you do, we will only check for common data cleaning errors, not the details of your projects, grants, or any other information you upload. It's your responsibility to make sure all of it is accurate, up-to-date, and complete.
- To be able to quickly analyze data across multiple portfolios and grant-making years, we recommend you include the Portfolio and Cohort Columns in your spreadsheet.
The Portfolio column is very useful if you are working on different grant-making or investment portfolios, issues, or projects around which you want to analyze data and outcomes. It is a great shorthand for selecting data into a project for analysis, and for visualizing financial and outcome data.
The Cohort column is helpful if you want to analyze your grant-making data across years or time cohorts. This is an important difference, which can help you separate your work by calendar and fiscal year, and also by multi-year projects . You can enter 2017, 2018, 2019, and 2020 as cohorts, for example, so you can run an analysis of funding amounts over time (and perhaps even across Portfolios!)
The next article in our Data Cleaning Series is 'Check for Common Errors'
Other articles in this series:
If you have any further questions about cleaning, preparing, and uploading your data to ImpactMapper, check out the rest of our articles here, or get in touch via the chat icon below.
We will also host webinars sharing best practices for keeping your data clean and up to date. Keep checking this page for the latest, and subscribe to our newsletter!