Step 3. Extract and prepare your data

Take a copy of the data you want to publish from your grants management system and get it ready to publish.

Extracting data from different grants management systems

The software that you use to collect and store your grants data is your grants management system. It might be a purpose-built grants management system, a configurable CRM like Salesforce or a bespoke system that was created for you by a software company.

Whatever system you use, you’ll need to take a copy of your grants data so that you can turn it into 360Giving data. (This is often called exporting or extracting data.) 

Choose your file format

Most publishers extract their grants data into a spreadsheet file like CSV (.csv), Excel Workbook (.xlsx) or Open Document (.ods). 

JSON format

It is also possible to publish 360Giving data as a JSON file. JSON format is used for exporting data directly from a grants management system as a feed. That means it can automatically update your 360Giving data for you whenever you use your grants management system. If you don’t have technical knowledge in your team, you will need support from a developer to set this up. 

Visit our technical reference on JSON schema for further details.

If you store your grants data in a database

If you use a grants management system provided by a software company, you should be able to set up and save a report that exports the information you need in a way that makes it easy to convert into 360Giving data. 

Funders have done this with many different grants management systems, including:

• Blackbaud Grantmaking
• Flexi-grant
• Benefactor
• SmartSimple
• Beacon CRM

With some configurable systems — such as Microsoft Dynamics and Salesforce — it may be possible to build part of the 360Giving publishing process directly into the system. Many funders who use Salesforce have set up their system to make it easier to share their 360Giving data. 

The rest of this guidance will focus on helping funders extract their data and prepare their 360Giving data in a spreadsheet, but you can find more information about directly exporting data from Salesforce in our reference section.

If you store your grants data in a spreadsheet

If you don’t have a grants management system and use spreadsheets to record your grants data instead, the steps to prepare your data will be similar to most other publishers. 

Without the option to create a report and export the data in the right format automatically, you will need to spend more time checking and re-formatting your data. You can also copy and paste it into one of our template spreadsheets to do this. 

Clean your data

When you extract raw data from your system, you might find that it isn’t well organised or it has gaps. There might be extra, unnecessary information in the spreadsheet, like titles and notes. The cells may have been changed in different ways, for example, colour-coded, or merged.

All these things can make it difficult for computer programs to read your data. That’s why it’s important to ‘clean’ your data before you start to work with it in the next step.  

How to clean your spreadsheet

• Use the first row for column headings. There shouldn’t be any rows above them.
• Use one row for each grant record.
• Make sure there aren’t any merged cells.
• Leave the cell blank if there’s no data to include. Remove any filler text, like ‘N/A’ or ‘Unknown’.
• Numbers in cells should just be numbers. Don’t use commas, dashes, or other symbols.
• Don’t hide any columns. If there is data you don’t want to publish, remove it from the spreadsheet. 
• If you have any notes or other information you would like to include with your data, put it in the Meta tab of the 360Giving spreadsheet.

This is also a good time to look through your data to see if there are any errors or gaps, or if anything has been recorded inconsistently, for example, an organisation’s name has been written two different ways. 

How to prepare your data

Once you’ve exported or copied data from your grant management system into a spreadsheet and cleaned it, you will need to prepare it to fit with the 360Giving Data Standard. This is also known as ‘formatting’ your data, because it’s about making sure it’s in the right format.

The way you extract and prepare your data will be slightly different depending on whether you are publishing data about grants to organisations or grants to individuals. If you give grants to both, we suggest you create a separate data file for each type.

Publishing in single or multiple files

Some funders publish all their grants data in a single file, while others split their data into separate files by year or grant programme. 

Think about what will work best for you.

• If you will be updating your data regularly, it might be easier to publish it in a single file with all your grants.
• If you update your data annually, you might prefer to create a new file for each year. Watch out for the same grant appearing in more than one year.
• The maximum recommended file size is 10MB in size, so depending on the volume of grants data you have, you may need to split it into several files.
• If you split your grants data into different files, you will need to publish each file separately. 

Whether your grants data is in a single file or multiple files, once published and included in GrantNav, all the data will appear together under your organisation’s name.

Grants to organisations: prepare your data

We recommend using one of our Conversion Spreadsheets to change the data in the file that you exported from your grants management system.

Conversion Spreadsheet 

Our Conversion Spreadsheet is an Excel file which aims to make it easier to format your grants data for the 360Giving Data Standard. 

You can enter the data exported from your grants management system into the Conversion Spreadsheet, and then formulas set up in the tool will convert your data into the right format for 360Giving.

Although you can tailor the template to meet your organisation’s particular needs, it includes three tabs by default:

• source_data This tab is for the grant information that needs to be formatted as 360Giving data. The source data is copied and pasted from the spreadsheet file exported from your grants management system or an existing spreadsheet that holds your data.
• fixed_data This tab is for the ‘fixed’ information that will be combined with your source data to create your 360Giving data. These fields typically include the currency, your own organisation name, your organisation identifier and your publisher prefix.
• 360_data The 360Giving headers that match up with the data you want to publish appear in the first row of this tab. Below them are formulas which combine the source data and fixed data from the first two tabs to create your formatted 360Giving data.

Because of the formulas, any changes in content of the data in the source_data or fixed_data tabs will automatically be picked up in the 360_data tab.

Grants to individuals: prepare your data

Our Data Preparation Templates for grants to individuals are Excel spreadsheets which can help you format your grants data for the 360Giving Data Standard. 

They work in a very similar way to the Conversion Spreadsheet above. You enter or copy the data exported from your grants management system into the source_data tab, and then formulas set up in the template will convert your data into the right format for 360Giving.

There are two Data Preparation Templates:

• Codelist Mapping. Use this one if your grants data includes your own categories which describe what the grant is for, i.e. the reason or the purpose of the grant.
• Manual Coding. Use this template if you don’t have your own categories describing the reason grants have been awarded. 

If you only have a few grants to publish

If you only award a few grants every year, you can still publish information about them as 360Giving data. We hope that you will, as your data will still contribute to a clearer picture of UK grantmaking.

You will find that the fastest way to get your data ready to publish is by using our Conversion Spreadsheet. You can enter your grants data into the source_data tab of the spreadsheet, and then formulas which we have set up for you will convert your data into the right format for 360Giving.