IP Geolocation with Alteryx
IP Geo Locators have been around for a while and are very useful when we want determine the geographical location of a given IP address. The list of websites that provides this type of service is long and varies in quality. In Alteryx we have a large range of geospatial tools that allow us to do magnificent spatial analysis, but pinpointing IP addresses has so far been out of the spotlight in Alteryx.
Now before we dive into doing this in Alteryx, a small disclaimer will be appropriate first. No service available, paid or free, will be able to provide us 100% accurate data. This does not mean that every location based on an IP will be a little off, but rather that some of the IP we want to locate are not traced back to the correct location. There are two reasons for this. First of all, users could be using a proxy or software that hides the IP, hence giving us false locations. Secondly, not all IPs are traceable in detail such as all the way down to zip code level. In general, the more aggregated answers we get from the location services the higher accuracy. Hence the more aggregated (country level) the more accurate. The less aggregated (exact street number) the less accurate.
Now lets get to it! The only information we really need to go is a list of the IP addresses we would like to determine the locations for. For this purpose I will use an excel file containing two columns: Name and IP address.
We input this file to Alteryx and connect it to the IP Gelocater Macro, which I will go through in a bit. You will notice that the IP Geolocater only needs to have the IP address as an input. The output of the Macro will contain all the new geographical information, such as country, region, address ect, which is then joined on to the original data set, to provide full information. The image below shows the short workflow.
Now lets see what happens inside the IP Geolocater Macro. As mentioned, the only column inputted to the macro is merely the field with our IP addresses. To get all the sweet sweet geographical information, we use Alteryx's Download tool to contact two online services:
- Google Maps API
First we create a url to request the FreegeoIP API for information based on the IP. The Alteryx request is formatted as seen below:
The request returns the following fields:
So far so good. Now that our dataset contains longitude and latitude information, we can perform a new request towards the Google Maps API. We generate a new URL and send the request, asking the Google Maps API to return the address at the given coordinates. The request is formatted as shown below:
//maps.googleapis.com/maps/api/geocode/xml?latlng="+ [your latitude] +","+ [your longitude] +"&sensor=false
This request returns us a range of information about the address such as house number, street name ect. The most interesting field is the "formatted" address, which contains the full address description. This is the only field we will use from the Google Maps API.
Our data set now contains a long range of geographical data that is lastly carried to our macro output.
Since the two APIs we use are free services they come with some limitations in regards to how many request we can send. FreegopIP.net has a limit of max 10.000 queries per hour, whereas Google Maps has a limit of 2.500 requests pr day for their free service, but gives the possibility to enroll in their pay-as-you-go service.
The result our workflow with the 8 names and IP addresses provides us the following output:
This concludes our rundown of the IP Geolocater macro. I have attached
- 1) The macro itself
- 2) A workflow using the macro
- 3) An analytical Alteryx app, where users can upload their own files that contains IP addresses (be it CSV, Excel ect.) and get the geographical information returned.