cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
%3CDIV%20class%3D%22lia-message-template-content-zone%22%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262584%22%20slang%3D%22en-US%22%3EPlotting%20the%20Physical%20Location%20of%20Members%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262584%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3EThe%20Project%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20communities%2C%20we%20have%20noticed%2C%20have%20communities%20within%20the%20community%3A%20local%20groups%20of%20users%20who%20might%20want%20to%20meet-up%20in%20person%20to%20share%20experiences%2C%20rather%20than%20always%20communicate%20on-line.%20We%20wanted%20to%20create%20such%20Groups%20but%20as%20the%20RapidMiner%20Community%20has%20grown%20organically%20over%20the%20last%2010%20years%2C%20it%20left%20us%20with%20the%20question%3A%20where%20are%20all%20those%20users%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20looked%20at%20the%20LSI%2F%20Labs%2F%20Geo%20report%20but%20that%20really%20didn't%20go%20to%20the%20detail%20we%20needed.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThat%20left%20us%20with%20the%20last%20IP%20Address%20added%20to%20the%20Metrics%2F%20User%20report%20and%20downloaded%20into%20Excel.%20While%20IP%20addresses%26nbsp%3Bare%20far%20from%20perfect%20in%20identifying%20individual%20users%20they%20can%20give%20accuracies%20of%20%3CA%20title%3D%22links%20out%22%20href%3D%22http%3A%2F%2Fwhatismyipaddress.com%2Fgeolocation-accuracy%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E80-95%25%20for%20location%3C%2FA%3E%20.%20%26nbsp%3BAnd%20on%20such%20large%20numbers%2C%20an%20indication%20would%20be%20enough%20to%20guide%20our%20Group%20strategy.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EGeo-Locate%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20task%201%20was%20to%20get%20geolocation%20from%20an%20IP%20Address.%20There%20are%20a%20number%20of%20free%20databases%20out%20there%20you%20can%20download%2C%20such%20as%20%3CA%20href%3D%22http%3A%2F%2Ffreegeoip.net%2F%3Fq%3D95.175.197.5%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EFreeGeoIp%3C%2FA%3E%2C%20but%20I'm%20not%20very%20techy%2C%20so%20I%20got%20a%20free%20data%20tool%20to%20do%20the%20work%20for%20me%20(of%20course%2C%20I%20used%20our%20own%20RapidMiner%20Studio%20product%2C%20but%20others%20are%20available%20%3CIMG%20class%3D%22emoticon%20emoticon-smileyhappy%22%20id%3D%22smileyhappy%22%20src%3D%22https%3A%2F%2Flithosphere.i.lithium.com%2Fi%2Fsmilies%2F16x16_smiley-happy.png%22%20alt%3D%22Smiley%20Happy%22%20title%3D%22Smiley%20Happy%22%20%2F%3E%20)%20.%20We%20read%20the%20data%20from%20our%20Metrics%20report%26nbsp%3Binto%20RMStudio%2C%20and%20used%20an%26nbsp%3B'Enrich%20Data%20by%20Web%20Service'%20operator%20to%20call%20out%20to%20the%20%3CA%20href%3D%22http%3A%2F%2Fipinfodb.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EIpinfodb%26nbsp%3BAPI%3C%2FA%3E.%20This%20is%20a%20free%20service%2C%20but%20you%20have%20to%20wait%2010%20minutes%20between%20calls%20or%20it%20gets%20annoyed.%20It%20returned%20the%20Country%2C%20State%2F%20Region%20and%20City%20for%20each%20IP%20Address%20in%20the%20file%20and%20appended%20it.%20Next%20I%20used%20a%20Split%20operator%20to%20make%20individual%20columns%20in%20Excel%20for%20Country%2C%20State%20and%20City.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Process%20looked%20like%20this.%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3E%3CIMG%20src%3D%22https%3A%2F%2Fimage-proxy.prod.aws.lcloud.com%2Fc9650d3cef060e2afef378cee53f9cce1af62587%2F687474703a2f2f656a68786234343632322e692e6c69746869756d2e636f6d2f74352f696d6167652f736572766572706167652f696d6167652d69642f37303969464538393338373637433236373834312f696d6167652d73697a652f6c617267653f763d76322670783d393939%22%20border%3D%220%22%20alt%3D%22proc.PNG%22%20title%3D%22proc.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3BPretty%20simple%2C%20even%20for%20me.%20And%20once%20created%20it%20can%20just%20be%20run%20over%20and%20over%20again%20for%20different%20chunks%20of%20community%20data.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EMap%20It!%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELooking%20at%20the%20output%20as%20a%20heatmap%20or%20bar%20chart%20was%20interesting%2C%20but%20ultimately%20you%20want%20to%20see%20this%20type%20of%20info%20as%20a%20real%20map%20-%20as%20in%20LSI.%20This%20last%20step%20to%20about%205%20minutes%20with%20the%20help%20of%20my%26nbsp%3BCEO%2C%20who%20was%2C%20coincidentally%20sat%20next%20to%20me.%20He%20suggested%20a%20tool%20called%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fcarto.com%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ECarto%3C%2FA%3E.%20I%20set%20up%20an%20account%2C%20read%20in%20the%20data%20and%20then%20we%20spent%20the%20remaining%204%20of%20our%205%20minutes%20selecting%20the%20right%20map%20background.%20There%20are%20lots%20of%20options%20to%20choose.%20Here's%20a%20map%20of%20our%20community%20by%20density.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3E%3CIMG%20src%3D%22https%3A%2F%2Fimage-proxy.prod.aws.lcloud.com%2Fd61fdcbfdd750f7c8aca0dbe2e3a921e1c14adca%2F687474703a2f2f656a68786234343632322e692e6c69746869756d2e636f6d2f74352f696d6167652f736572766572706167652f696d6167652d69642f37313169383830373736374643443135413239312f696d6167652d73697a652f6c617267653f763d76322670783d393939%22%20border%3D%220%22%20alt%3D%22694.png%22%20title%3D%22694.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EOf%20course%20you%20might%20want%20to%20do%20this%20in%20a%20tool%20like%20Tableau%20or%20Qlik%2C%20both%20of%20which%20have%20great%20geo-coding%20capabilities%20and%20will%20also%20allow%20for%20a%20whole%20bunch%20of%20other%20visualizations.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ESo%20what%20did%20we%20Learn%3F%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EActually%2C%20this%20simple%20process%20threw%20up%20a%20few%20surprises.%20Firstly%2C%20the%20greatest%20density%20of%26nbsp%3Bour%20members%20is%20in%20London.%20I%20would%20have%20put%20money%20on%20Dussledorf%20(where%20we%20started)%26nbsp%3Bor%20Boston%20(where%20our%20corp%20headquarters%20are%20%2B%20Harvard%2C%20MIT%20etc).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESecondly%2C%20there%20are%20significant%20hot%20patches%20of%20users%20in%20Schleswig-Holstein%20(still%20under%20investigation)%2C%20Singapore%20and%20Santiago%2C%20also%20in%20Bangalore%2C%20Dublin%20and%20Milan.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAll%20of%20this%20should%20help%20with%20setting%20up%20self-run%20local%20user%20groups.%20But%20now%20we%20want%20to%20extend%20this%20capability%20to%20look%20at%20where%20new%20registrants%20are%20coming%20from%2C%20and%20seeing%20the%20effectiveness%20of%20different%20campaigns%20being%20run%20in%20various%20geographies.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264134%22%20slang%3D%22en-US%22%3ERe%3A%20Plotting%20the%20Physical%20Location%20of%20Members%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264134%22%20slang%3D%22en-US%22%3E%3CP%3ENice%20one%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fcommunity.lithium.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F52690%22%20rel%3D%22noopener%20noreferrer%22%3E%40farrcycle%3C%2FA%3E%26nbsp%3B-%20thanks%20for%20sharing!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-264132%22%20slang%3D%22en-US%22%3ERe%3A%20Plotting%20the%20Physical%20Location%20of%20Members%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-264132%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fcommunity.lithium.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F52690%22%20rel%3D%22noopener%20noreferrer%22%3E%40farrcycle%3C%2FA%3E%26nbsp%3BBrilliant!%20%26nbsp%3BThanks%20for%20sharing%20the%20method%20and%20insights.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-262666%22%20slang%3D%22en-US%22%3ERe%3A%20Plotting%20the%20Physical%20Location%20of%20Members%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-262666%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20sharing!%20I%20like%20the%20idea%20of%20using%20this%20data%20to%20plan%20for%20local%20user%20groups.%20This%20could%20also%20possibly%20help%20with%20determining%20if%20separate%20language%20boards%20are%20needed%20for%20certain%20regions%20--%20if%20your%20community%20caters%20to%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%0A%3C%2FDIV%3E
Highlighted
Occasional Advisor
Occasional Advisor

Plotting the Physical Location of Members

The Project

 

Many communities, we have noticed, have communities within the community: local groups of users who might want to meet-up in person to share experiences, rather than always communicate on-line. We wanted to create such Groups but as the RapidMiner Community has grown organically over the last 10 years, it left us with the question: where are all those users? 

 

We looked at the LSI/ Labs/ Geo report but that really didn't go to the detail we needed. 

 

That left us with the last IP Address added to the Metrics/ User report and downloaded into Excel. While IP addresses are far from perfect in identifying individual users they can give accuracies of 80-95% for location .  And on such large numbers, an indication would be enough to guide our Group strategy. 

 

Geo-Locate

 

So task 1 was to get geolocation from an IP Address. There are a number of free databases out there you can download, such as FreeGeoIp, but I'm not very techy, so I got a free data tool to do the work for me (of course, I used our own RapidMiner Studio product, but others are available 🙂 ) . We read the data from our Metrics report into RMStudio, and used an 'Enrich Data by Web Service' operator to call out to the Ipinfodb API. This is a free service, but you have to wait 10 minutes between calls or it gets annoyed. It returned the Country, State/ Region and City for each IP Address in the file and appended it. Next I used a Split operator to make individual columns in Excel for Country, State and City. 

 

The Process looked like this.

proc.PNG

 Pretty simple, even for me. And once created it can just be run over and over again for different chunks of community data. 

 

Map It!

 

Looking at the output as a heatmap or bar chart was interesting, but ultimately you want to see this type of info as a real map - as in LSI. This last step to about 5 minutes with the help of my CEO, who was, coincidentally sat next to me. He suggested a tool called Carto. I set up an account, read in the data and then we spent the remaining 4 of our 5 minutes selecting the right map background. There are lots of options to choose. Here's a map of our community by density. 

 

694.png

Of course you might want to do this in a tool like Tableau or Qlik, both of which have great geo-coding capabilities and will also allow for a whole bunch of other visualizations. 

 

 

So what did we Learn?

 

Actually, this simple process threw up a few surprises. Firstly, the greatest density of our members is in London. I would have put money on Dussledorf (where we started) or Boston (where our corp headquarters are + Harvard, MIT etc).

 

Secondly, there are significant hot patches of users in Schleswig-Holstein (still under investigation), Singapore and Santiago, also in Bangalore, Dublin and Milan.  

 

All of this should help with setting up self-run local user groups. But now we want to extend this capability to look at where new registrants are coming from, and seeing the effectiveness of different campaigns being run in various geographies. 

3 Replies 3
Highlighted
Honored Contributor Honored Contributor
Honored Contributor

Re: Plotting the Physical Location of Members

Thanks for sharing! I like the idea of using this data to plan for local user groups. This could also possibly help with determining if separate language boards are needed for certain regions -- if your community caters to that.

----------------------------------

Lili McDonald
Senior Community Manager @ Udemy
Connect on LinkedIn
Reply
Loading...
Highlighted
Lithium Alumni (Retired) Lithium Alumni (Retired)
Lithium Alumni (Retired)

Re: Plotting the Physical Location of Members

@farrcycle Brilliant!  Thanks for sharing the method and insights. 

Reply
Loading...
Highlighted
Lithium Alumni (Retired) Lithium Alumni (Retired)
Lithium Alumni (Retired)

Re: Plotting the Physical Location of Members

Nice one @farrcycle - thanks for sharing!


Paolo Tagliaferri
Principal Technical Consultant

Lithium
Reply
Loading...