Download Data from UNComtrade.org with Alteryx

For a lot of sourcing/trading companies, analyze the international trade data could potentially bring some insights to their strategy development. Apart from doing the as-is analysis on the internal data, one can bring in external data such as UNComtrade (https://comtrade.un.org/) to understand the worldwide trading situation.

If you go to the website, there are a few ways of downloading data. Of course you can download the csv one by one on the website interface.

Wait, what if I want to download more than 200 types of commodities? Yes, you can probably hire an intern to do that...Or, you can download it through the API with Alteryx.

First of all, you need to understand what is an API. Basically it is an interface for client machine to query the server and the server will return based on the query. Just treat it like asking and answering questions. If you are interested in knowing more about the mechanism, google it (especially the authentication part which is not covered here). So you will have to prepare the questions for the server.

Normally, the website will provide related documentations on the usage of the API (https://comtrade.un.org/data/doc/api/#DataRequests). Read it and understand the components.

e.g. http://comtrade.un.org/api/get?type=C&freq=M&px=HS&cc=848180&r=826&ps=2013&fmt=json

Since I am not here to explain the API, I will switch back to Alteryx. How does it work in Alteryx to download something from a web? The key is the "download" tool.

It allows you to make queries in Alteryx. You will be able to get a large string set which is not yet ready for any analysis. You need to restructure it.

There are two common data types available, xml and json, either one there is another tool to extract the data. Then you will have a more structural data. Identify the pattern and build a workflow for it because it will always be in that structure if you are using similar queries.

This is a sample I did for UNComtrade. You can download the sample result here.

So now you can prepare your queries and Alteryx will download it for you one by one. Just press the "play" and wait for your beautiful data set done! : )

Note:

1. The API itself usually have limitation if you use it for free. For example, UNComtrade allows 100 calls per hours only. You may need to subscribe for unlimited access and the cost varies.

2. The API may consist of authentication process which is not covered here and simplified the whole discussion. Feel free to contact me if you want to know more, I have some more examples on getting through the authentications in Alteryx.

3. The public API is not 100% stable so it might skip some queries for no reason. Do sample check on the results from time to time.

4. To better use API queries with Alteryx, it involves some other advanced concepts such as Alteryx Macro. I will write another post for it later.