Data Use Query - Funding/activities related to specific health issues in country x with free-text search

Funding/activities related to specific health issue in country x with free-text search

Author of this query: IATI Secretariat

1. Query 

Activities responding to a particular disease

2. Description 

Source of query (hypothetical or actual): University, global health researcher

Intended use of the data (hypothetical or actual): Research on international funding of certain health issues/disease focus.

3. Steps to access and / or analyze data

Data needed: 

• Recipient countries
• Titles and descriptions of relevant activities
• Disbursements, expenditures and budgets for relevant activities

Data access tool(s) recommended: 

• IATI Datastore Search
• Country Development Finance Data

Steps suggested:

Part 1: Locate the disease relevant activities within a specific country

Using Datastore Search’s simple search engine, search within IATI activities by typing in the relevant keywords into the search box.

1. Visit Datastore Search and enter the relevant keywords into the search box.

• For example, if you want to find activities associated with diabetes, type in diabet* which will include ‘diabetic’ and ‘diabetes’ in the output. ˋ  * ˋ  represents zero or more characters can come before and after the given text – for example, ˋ  latin*ˋ  would match “latin”, “latina” and “latino”. In addition, note that the * characters are wildcards meaning other characters can also come before and after the given text.

2. Add recipient country to search query.

• If you want to refine your search for a specific country or region; for example, to find all activities containing the search term ‘diabetes’ in Burundi, you will need to add some additional filters to your query.
• To do this, select ‘Advanced’. You will see that the keyword search you just conducted has already been added to the query.
• Select ‘Add’ and then select ‘Boolean Grouping’ and select AND and the left parentheses. The AND means that the query will search for activities that contain the selected keyword and the selected recipient country.
• Select ‘Add’ and then select ‘Recipient Country Code’ and from the dropdown select the country code (e.g. LB for Lebanon).
• Select ‘Add’ and then select ‘Transaction Recipient Country Code’ and from the dropdown select the country code (e.g. LB for Lebanon). Select OR so that the query will search for activities that either contain the country code at the activity OR the transaction level.
• Select ‘Add’ and then select ‘Boolean Grouping’ and the right parentheses.

3. Run the query and download the file.

• Select Run to run the query. The resulting query should look like the screenshot below:

• The results page will display all the activities that meet your criteria. Under the Download options select ‘Excel’ and in the ‘Download Results File,’ Select ‘Activity’ and then ‘Download.’ The resulting Excel file will contain activities containing the keyword(s) and recipient country/ies you have chosen.

Part 2: Locate financial information for the activities identified in Datastore Search query

While financial information is present in the files downloaded from Datastore Search, it needs a lot of processing work, e.g. splitting transactions into their share per sector and/or country, to be able to use it. Rather than doing this work in Excel, pre-processed financial data can be pulled from the Country Development Finance Data (CDFD) tool and combined with the data in the file from Datastore Search with the relevant activities.

1. Download the relevant country file from the Country Development Finance Data tool. On the tool, select the country you are interested in from the dropdown and download the file. This file will contain all of the financial data for activities happening in the selected country.

2.  Filter this file to only include the activities that you found using Datastore Search. To do this, you will need to filter the file from CDFD to only include the IATI Identifiers that are in the file from Datastore Search.

• One option for doing this is to use a v-lookup function in Excel to search for the presence of each iati_identifier value from the Datastore Search file in the `IATI Identifier` column in the Country Development Finance Data tool’s output.
• Another option is to create a list of the identifiers in the Datastore Search file and paste this list into a new tab in the CDFD file. You will need to give the column the heading of ‘IATI Identifier’ and then you can use an Advanced Filter to search for only these activities within the data downloaded from CDFD.

3. After filtering for the relevant activities, you should have a final list of activities that are of interest for which you can now analyze the financial data.

Note: If you are interested in simultaneously searching files for multiple countries from CDFD, you can do so by downloading them in a zip file and then combining them using Excel Power Query. Additional details on this can be found in this Data Access How-To: Pulling pre-processed financial data for specific set of IATI activities

Part 3: Analyze the financial data for the activities of interest

The CDFD spreadsheets are designed to enable easy analysis in an Excel PivotTable. Below are some basic instructions on how to analyze budgets and spending after you have filtered the CDFD data to include only the activities of interest. For additional guidance on understanding and analyzing the data from CDFD, download the guidance here or watch the how-to videos here.

1. Creating a PivotTable

• Select cell A1.
• Go to the ‘Insert’ tab and select ‘PivotTable.’
• Excel will select all populated cells and default to insert the PivotTable in a New Worksheet. Click ‘Ok.’
• You will now have a PivotTable where you can analyze the data.

2. Spending by Reporting Organisation

• Add ‘Sum of Value (USD)’ to the Values box
• Filter ‘Transaction Type’ to only include ‘Disbursements’ and ‘Expenditures.’
• Add ‘Reporting Organisation’ to Rows box.
• To sort by largest spend, click the dropdown for ‘Row Labels,’  select ‘More Sort Options,’ select ‘Descending (Z to A) by’ and select ‘Sum of Value (USD)’ from the dropdown.
• To analyse the Reporting Organisations categorised by Reporting Organisation Type, in the Rows box, add ‘Reporting Organisation Type’ and then ‘Reporting Organisation’ below it.

3. Analyzing projected budgets by Reporting Organisation by Quarters for a particular year

• Filter ‘Transaction Type’ to only include ‘Budgets.’
• Add the ‘Calendar Year and Quarters’ to the Columns box.
• ‘Reporting Organisation’ to the Rows box.
• Repeat step 3 above to sort by largest budgets.

4. Result

List of activities in a specific country that address a specific health issue, e.g., diabetes, and the projected and actual spend of these activities.

5. Caveats / Considerations / Challenges

• Organisations report their spending to IATI according to varying timelines (e.g. monthly, quarterly, annual) which means that the completeness of the data varies depending on when the data is retrieved.
• As multiple organisations in the delivery chain can report their spending, this likely results in some double counting of resources.