Powered by OKFN
A walkthrough of the IATI Datastore Query Builder
| What is it? | A “query builder” – a form where the user specifies what data is wanted from the IATI registry, presses “submit” and a CSV with the selections starts automatically downloading. Magic! |
| Who is it useful for? | People wanting to download custom CSV files of IATI data: you can get data per reporting organisation, by sector, by recipient country, all in CSV format. It might need cleaning afterwards, but it’s a good 1st step! |
| Give me an example of what I can get here… | All of the activities that take place in Bangladesh, that are published to IATI, by donor organisation, in a CSV. |
| Where can I go for further guidance? | User Guide |
Step by step walkthrough
You should be met with something like the screen above when you click on the http://datastore.iatistandard.org/query/ .
The top three questions – Format, Repeat Rows, and Choose Sample Size are all mandatory.
Choose Format:
Here, you can choose the level of granularity you want in the CSV you will get. The easiest way to illustrate this is by looking at the data:
Let’s say we’re interested in what Action Aid UK is doing in Bangladesh.
If we select ‘One Activity per row’, ‘No” to repeating Rows and ‘50 rows’ sample size, then we would select ‘Action Aid UK’ in Reporting Organisation, and Bangladesh in the Country option. The screen would look something like this:
We’ll get a link to a dataset that looks like this:
For comparison, now do the same (selecting ‘Action Aid UK’ and ‘Bangladesh’ as above) but instead of selecting One Activity per row,, choose One Transaction per row.
This looks something like:
And one last time – compare it to the data you get when selecting One Budget per row, which will give you:
So maybe we can now see the differences already: essentially, One Activity per row is showing activities, or projects, carried out by the reporting organisation.
One Transaction per row is much more granular: it has information on every transaction, or transfer of money, between donor organisation to implementing organisation (in this case, Action Aid UK to Action Aid Bangladesh), and each transaction has an identifier, so it can, in theory, be tracked further.
One budget per row is higher level, and more aggregated: it shows from which date range the budget from the donor organisation is open, or running. It shows how much money is spent in total on certain projects or budgets.
With the Choose Country – this can be either the country in which the activities are taking place, or the country of the donor organisation.
As with the IATI Registry, described above, you can also choose which fields you select- only the top three are actually mandatory. In general, this is probably the best way to get data to then work with afterwards- though usually, it will require some cleaning first. (See module, Cleaning IATI data with OpenRefine.)
Have a play!