Understand Your Users by Mapping IPs

If you have a website or blog you often want to know where a subset of your visitors are in the world. You can get a rough approximation of this by geolocating the IP addresses you get from your logs. Geosheets has incorporated IP geolocation to make looking up IP geo information or mapping IP locations as simple as writing a single spreadsheet formula.

1. Create a Google spreadsheet with a column containing IP addresses of interest.

For sample data, you can copy this sheet containing IP addresses of public DNS servers: https://docs.google.com/spreadsheets/d/18nkaCD9uglSn3khusL5ZiDbmEO9KlgiCBIsmmBsNfe4/edit

2. Use the GEO_IP_GEOCODE to get the locations of each IP.

Use the formula =GEO_IP_GEOCODE(<range>) where <range> is the column that contains your IP addresses. If you're using sample spreadsheet, enter =GEO_IP_GEOCODE(A4:A22) into cell C4. The coordinates for each IP should populate in a moment.

3. (Optional) Look up the country code for each IP using GEO_IP_INFO.

You can do more than just look up the coordinates. The function GEO_IP_INFO accepts a range containing IP addresses and a second argument indicating what attribute you want to know about that IP, like "city", "country", or "zip". In cell D4 add the formula =GEO_IP_INFO(A4:A22, "country") to populate country codes.

4. Draw a map using GEO_MAP.

Use the GEO_MAP function to plot the IP addresses on a map. In A1 or any convenient cell, add the formula =GEO_MAP(A3:C22, "ips").

For the first argument pass the range of your data table including the coordinates column. For the second argument, pass any string to serve as a label for your map. Geosheets will assume that the locations you want to plot are either in a column called "Location" or the first column, otherwise you pass a third argument with the name of the column containing locations.

The finished map should look like this:

Full page map: https://www.geosheets.com/map/s:NVA24vb2/ips

The finished spreadsheet should look like this:

Leave a Reply

Your email address will not be published. Required fields are marked *