John Snow linked cholera with unclean water
John Snow, linked cholera with unclean water.
Image from Wikimedia

Mapping is traditionally considered a tricky business but if the map is easy to understand it can save millions of peoples lives. See my friend Crile’s blog post about Cholera for more on this.

I’m not going to help yous save lives, but I hope to show you what you can do when you have some constraints.

The question: How many users are having slow page loading times due to their poor connection speeds ?

The constraints: I don’t have any budget, the site is in a change freeze, I need an idea of how big or small the problem is, I have one hour.

The resources: My brain, a log file for one day of visits to the site which contains the lat and long of users who have accessed the site, the windows office suite of products.

First clean your data up for mapping

Take a look at the data and make it easy for computers to read.

My source looked like this
[note I’ve masked the IP address and changed the latitude and longitude to obfuscate their identity]

00:45.8,66.249.79.XXX,”{“”pagename””:””Home”””,”ip:””66.249.71.XXX”””,”country_code:””US”””,”country_name:””United States”””,”region_code:””CA”””,”region_name:””California”””,”city:””Mountain View”””,”zip_code:””94043″””,”time_zone:””America/Los_Angeles”””,Latitude+37.00,Longitude-122.00,metro_code:807}”

And here it is tidied up with the columns I think I’ll need to use

00:45.8,66.249.79.XXX,Home,US,CA,Mountain View,37.00,-122.00

As you can see these columns are: time, ip address, page name, country code, region code, city name, latitude, longitude

Now I have tidy data what next ?

Step two with mapping data reduce the noise

So I selected only users in New Zealand to look at, mostly because I know the geography and I thought I would be able to guess from their location if they had fast or slow connections to the home page. My thought was that I would then be able to use the NZ stats as a sample to represent the whole because most users for this site are from NZ.

Next step was a google search for how to map latitude and longitude using excel. This lead me to the Excel Power Map option on the insert menu.

Excel insert Power map

It is pretty easy to use. So much so I got the following in a minute or two.


Excel power map plot


Step three remind yourself why you’re mapping the data

In my case I am trying to find out how much of the traffic to this site is from connections that are slow. But I can’t measure that (no money or time, and code freeze) so I’m going to infer slow connection speed from the location of the places users are coming to the site from.

By scanning the map I found 18 of the users were in rural locations where I would guess their connection speeds are slower than urban connections.

In my sample there were 243 connections total. So the answer to my question is 18/243 or about 7% of users might have slow connections.

Now this is very rough and relies on me assuming:

  • that rural connections are slower than urban ones

Step four confirm your first results

My next step would be two fold

  1. pick 10 more log files for random days of the week and do the same work on them to see if they also indicate 7% of users may be on slow connections.
    If the results are similar for all 10 random tests I’d then do the following step.
  2. match the usernames (these are available in the log files for most intranet systems) to the names of real staff and their departments or areas they work in. This is so that I can work out if they are staff that have a large impact on the organisation or not. This helps me prioritise how soon I should install a tool to measure user page load speed, and or gives me a list of people to survey on their experiences.

Intranet user location mapping with Excel conclusion

Well it took longer to write and prepare this blog post than to find out that 7% of the users who may have slow connections due to being in rural locations.

Seriously – intranet teams have too many things to do. So it is very important to be able to prioritise the things you work on. Sometimes that means knowing who the most important staff in an organisation are, and making sure the intranet serves them first. At other times it means being able to test a theory or work out how big or small a piece of work is. Ideally you use data to inform these decisions.

In this case we worked out that the problem likely impacts 7% of staff. The size of our issue. Our next step is to then work out if the 7% are super important to the organisation’s goals or not. Once we know these two things we have some idea of how useful it is to get more data via survey or with the installation of a page load tool.

I hope this helps you find the right things to work on 🙂


Geo map intranet users
Tagged on:                     

4 thoughts on “Geo map intranet users

  • March 8, 2016 at 3:40 am

    Excellent post Dorje. Looks like you’re putting me out of business…!


  • March 9, 2016 at 5:06 pm

    Do you mind if I quote a few of your articles as long as I provide credit and
    sources back to your website? My blog site is in the very same niche as yours and my visitors would truly benefit from some of the information you
    present here. Please let me know if this ok with you. Cheers!

  • March 10, 2016 at 9:12 am

    I couldn’t resist commenting. Very well written!

Comments are closed.