Mapping SupportBee's Users around the World

Our customers come from all around the world, as a matter of fact, they come from 79 different countries, and login to SupportBee almost everyday. So we had decided to share brief stories about our awesome users, and put up a page about them on

With just a little bit of programming, a little bit of Google Sheets and some Sketch design we created a rough map of where our users login from everyday.

Here are the steps we follwed to build the map:

1. Fetch the Last Login IPs of all the Users

This step is pretty simple, and straight forward. One of the most popular Rails authentication library - Devise, saves the current_sign_in_ip, and last_sign_in_ip in the User model. We mapped the last_sign_in_ip into an array.

2. Write a small script to fetch the Country for each IP

For this we wrote a small script, and used’s API to fetch the country for each IP, and exported that data into a CSV file. Below is the code for that.

require 'csv'
require 'faraday'

def fetch_and_export_countries
  filename = "tmp/country_data.csv"

  countries = {}
  ips = [,,,,,, ...]
  ips.each_slice(140) do |slice|
    slice.each do |ip| "Fetching for #{ip}"
      response = connection.get do |req|
        req.url "{ip}"

      body = JSON.parse(response.body) "#{ip} is from #{body["country"]} \n"
      if countries.has_key? body["country"]
        countries[body["country"]] += 1
        countries[body["country"]] = 1

    # Requires sleeping because
    # only allows 150 requests per minute "Sleeping 1 minute ..."
    sleep 1.minute
  end, "wb") do |csv|
    countries.each do |item|
      csv << item
  end "Done!"

def connection
  @conn ||= do |faraday|
    faraday.request  :url_encoded             # form-encode POST params
    faraday.response :logger                  # log requests to STDOUT
    faraday.adapter  Faraday.default_adapter  # make requests with Net::HTTP

We used the Faraday gem for making a HTTP call. The keys in the countries hash are all the countries, and values are the number of IPs from that country. only allows 150 requests per IP per minute for their free usage, so we put the process to sleep for 1 minute after every 140 IPs, keeping 10 as a buffer.

3. Create a GEO Chart with Google Sheets

Once we had the country data exported into a CSV file, we imported that into Google Sheets. Below is a screenshot of how the data looked. Using that we created a GEO Chart (also in the screenshot) of the number of users logging in from each country.

Google Chart

4. Create a good looking map using the data from Step 3

The last step was to refine the map created from GEO Chart, and create a simple map of where our Users login to SupportBee from. Following is the final map we created to use on the Customers Page.

SB World

blog comments powered by Disqus
Pranav Singh
Pranav Singh