Ticket #45 (new New feature)

Opened 3 years ago

Last modified 13 days ago

Plugin using GeoIP to accurately detect visitor countries, cities, and continent

Reported by: matt Owned by:
Priority: normal Milestone: Third Party Piwik Plugins
Component: New Plugin Keywords:
Cc: Eli-D, mikael, craig, anthony, grigri.74, a2b, zeta, qnox, vipsoft, konus, piwika, Maui, frag-titus, alracer, neroZ, ilexius, tec, DaSilva, jeb1217, mike5464, EgonO Sensitive: no

Description (last modified by matt) (diff)

GeoIp Plugin

  • When installed, the plugin will detect more accurately the country of your visitors using the MaxMind database.
  • The existing reports for country and continent will be replaced by the more accurate GeoIp report in your Piwik interface.
  • Piwik will also detect the city of your visitors. You can see all cities by clicking on the country row in the table.
  • You can apply the GeoIp data to all your previous visits and re-generate your reports, see below.

Requirements

You need at least Piwik 0.2.33 to run the GeoIp plugin.

How to install?

How to apply the GeoIp to all your previous visits to fix your previous reports

You can apply GeoIP precise localization on your past data:

  • run geoipUpdateRows.php script, from command line (or from the browser, but risk of timing out if your database is large)
  • wait a few minutes until all your visitors data from the piwik_log_visit table have been processed.
  • you now need to force Piwik to re-process all the past reports: drop all piwik_archive_* tables in your Piwik database. Piwik will automatically create them and re-process the reports.
  • You should see Visitors countries and cities in your Visitors>Location report.

Authors

  • Mikael Letang
  • Maciej Zawadziński
  • Matthieu Aubry

Changelog

  • version 0.7: cleaned up code, compatible with Piwik 0.2.33, improved documentation
  • version 0.8: fixed bug in geoIpUpdateRows.php (thanks looztra)
  • version 0.9: all fields now NULLable to make sure that insert still work fine after plugin is disabled
  • version 0.10: compatibility with 0.2.35 after API change in the AddWidget function
  • version 0.11: fixing bug with country detection
  • version 0.12: updated for auto loader
  • version 0.13: Compatible with Piwik 0.6
  • version 0.14:
    • Fixed geoipUpdateRows script which was failing with errors. It also now checks that logged in user is Super User, and that the GeoIP plugin is enabled.
    • Fixed memory leak when using visitor generator.
    • Fixed .htaccess was blocking users from executing the script with the browser.
    • Added .htaccess to allow to execute the script directly from the browser.
    • Fixed Sparkline distinct countries

Feedback

Please leave a comment if you have any feedback, suggestion, or bug report.

Attachments

GeoIp Example.png Download (53.6 KB) - added by matt 17 months ago.
Screenshot of Piwik with GeoIp enabled
patch_GeoIP_0.7_google_map_link Download (2.5 KB) - added by poudro 17 months ago.
Patch_GeoIP_us_can_region.zip Download (6.1 KB) - added by duylaiabc 15 months ago.
precise region or state for US and Canada visitors
geoip_us_can.png Download (14.7 KB) - added by duylaiabc 15 months ago.
GeoIP - compatible with Piwik before 0.6.zip Download (17.8 KB) - added by matt 4 months ago.
ONLY use for Piwik < 0.6
GeoIP.zip Download (18.5 KB) - added by matt 2 months ago.
GeoIP plugin for Piwik >= 0.6

Change History

  Changed 17 months ago by matt

  • cc Eli-D, mikael, craig, anthony, grigri.74, a2b, zeta, qnox, vipsoft, konus, piwika, Maui, greenman, frag-titus, alracer, neroZ, ilexius, tec, DaSilva, jeb1217, mike5464, EgonO added
  • milestone set to Third Party Piwik Plugins

Changed 17 months ago by matt

Screenshot of Piwik with GeoIp enabled

  Changed 17 months ago by DaSilva

  Changed 17 months ago by matt

  Changed 17 months ago by matt

  • description modified (diff)

  Changed 17 months ago by rembrand

Changed 17 months ago by poudro

  Changed 17 months ago by poudro

Thanks for the wonderful work on this plugin, it's really great!

Just wanted to add my little contribution with a little feature I use and thought I would share.

By applying the patch 'patch_GeoIP_0.7_google_map_link' I just uploaded to the original tree it adds links to google maps with the location of cities in the visitor countries dropdown...

  Changed 17 months ago by mike5464

  Changed 17 months ago by matt

  • description modified (diff)

  Changed 17 months ago by matt

mike5464 and rembrand, the plugin should show "Unknown" when the visitor couldn't be located. It will only work well from the upgrade and not fix previous visits. Do you still experience the issue few days after the upgrade?

  Changed 17 months ago by matt

  • description modified (diff)

  Changed 17 months ago by matt

in reply to: ↑ 25 ; follow-up: ↓ 75   Changed 17 months ago by matt

follow-up: ↓ 32   Changed 17 months ago by matt

in reply to: ↑ 24   Changed 17 months ago by matt

  • cc greenman removed

in reply to: ↑ 30   Changed 17 months ago by mvanlaar

  Changed 17 months ago by mvanlaar

  Changed 16 months ago by zoc

  Changed 16 months ago by matt

  • description modified (diff)

  Changed 16 months ago by matt

  Changed 16 months ago by poudro

  Changed 16 months ago by matt

  Changed 16 months ago by matt

  • description modified (diff)

  Changed 16 months ago by poudro

  Changed 16 months ago by FJRA

  Changed 15 months ago by rembrand

  Changed 15 months ago by fuero

@rembrand:

You're using mod_geoip, right? Here's an excerpt from my apache conf:

<IfModule mod_geoip.c>

GeoIPEnable On GeoIPEnableUTF8 On GeoIPOutput Env GeoIPDBFile /path/to/GeoLiteCity.dat MemoryCache # for mod_proxy's X-Forwarded-For GeoIPScanProxyHeaders On

</IfModule>

Furthermore, check permissions on GeoLiteCity.dat, try chown <apacheuser> /path/to/GeoLiteCity.dat

  Changed 15 months ago by rembrand

wrote a small php script to test the Funktion of Geoip: 

<html>
<body>
<?php
 if( !empty($_SERVER['GEOIP_COUNTRY_CODE']) )
                {
                $locationInfo = array();
                $locationInfo['country_code'] = (isset($_SERVER['GEOIP_COUNTRY_CODE'])) ? strtolower($_SERVER['GEOIP_COUNTRY_CODE']) : self::$defaultLocationInfo['country_code'];
                $locationInfo['city'] = (isset($_SERVER['GEOIP_CITY'])) ? utf8_encode($_SERVER['GEOIP_CITY']) : self::$defaultLocationInfo['city'];
                $locationInfo['latitude'] = (isset($_SERVER['GEOIP_LATITUDE'])) ? round($_SERVER['GEOIP_LATITUDE'],4) : self::$defaultLocationInfo['latitude'];
                $locationInfo['longitude'] = (isset($_SERVER['GEOIP_LONGITUDE'])) ? round($_SERVER['GEOIP_LONGITUDE'],4) : self::$defaultLocationInfo['longitude'];

                }


var_dump($locationInfo);
?>
</body>
</html>

Output : array(4) { country_code?=> string(2) "de" city?=> string(7) "Hamburg" latitude?=> float(53.55) longitude?=> float(10) }

Piwik still displays UserCountry_country_ for new requests...

  Changed 15 months ago by mike5464

  Changed 15 months ago by duylaiabc

I've extended the GeoIP plugin to track in detail (In the US and Canada) which state or region the visitors are from, by adding an extra column to the database (region) and making minor changes to the code. The region can be query from the maxmind db. I was wondering if this feature can be made public?

Changed 15 months ago by duylaiabc

precise region or state for US and Canada visitors

  Changed 15 months ago by matt

duylaiabc, can you please post a screenshot of how the reporting look like?

Changed 15 months ago by duylaiabc

  Changed 15 months ago by duylaiabc

  Changed 15 months ago by marcello.ceschia

it tried to get location_geoip_latitude, location_geoip_longitude from api.
I added this two columns to archiveDayAggregateVisits sql query and added 

if(!isset($this->interestByCountryAndCity[$row['location_geoip_country']][$row['location_geoip_city']]['loc']))
				$this->interestByCountryAndCity[$row['location_geoip_country']][$row['location_geoip_city']]['loc']=array('lat' => $row['location_geoip_latitude'], 'lon' => $row['location_geoip_longitude']);

But the result is, most of the data have wrong lat and long values. E.g.:

	<row>
		<label>Vienna</label>
		<nb_visits>2</nb_visits>
		<nb_actions>11</nb_actions>
		<max_actions>11</max_actions>
		<sum_visit_length>330</sum_visit_length>

		<bounce_count>0</bounce_count>
		<nb_visits_converted>1</nb_visits_converted>
		<loc>
				<lat>96,4</lat>
				<lon>32,7334</lon>
		</loc>
		<sum_daily_nb_uniq_visitors>2</sum_daily_nb_uniq_visitors>
	</row>
	<row>
		<label>Leonding</label>
		<nb_visits>1</nb_visits>
		<nb_actions>17</nb_actions>

		<max_actions>17</max_actions>
		<sum_visit_length>721</sum_visit_length>
		<bounce_count>0</bounce_count>
		<nb_visits_converted>1</nb_visits_converted>
		<loc>
				<lat>48.2667</lat>

				<lon>14.2500</lon>
		</loc>
		<sum_daily_nb_uniq_visitors>1</sum_daily_nb_uniq_visitors>
	</row>

as you can see, loc data of Leonding is correct, but data of Vienna isn't.
Has anybody an idea why?

  Changed 14 months ago by matt

  • summary changed from Plugin for a precise geo localization to Plugin using GeoIP to accurately detect visitor countries, cities, and continent

  Changed 14 months ago by mike5464

  Changed 14 months ago by dzirt

  Changed 14 months ago by Zithromax

  Changed 14 months ago by dzirt

  Changed 14 months ago by zeta

  Changed 14 months ago by rbroen

  Changed 14 months ago by purewhite

follow-up: ↓ 59   Changed 14 months ago by barry_van_dijk

in reply to: ↑ 58 ; follow-ups: ↓ 60 ↓ 61   Changed 14 months ago by simone

in reply to: ↑ 59   Changed 14 months ago by barry_van_dijk

in reply to: ↑ 59 ; follow-up: ↓ 63   Changed 14 months ago by dheller

follow-ups: ↓ 76 ↓ 136   Changed 14 months ago by marcello.ceschia

for people with limited memory you can replace 

$this->geoIpDb = geoip_open($geoIPDataFile, GEOIP_MEMORY_CACHE);

into 

$this->geoIpDb = geoip_open($geoIPDataFile, GEOIP_STANDARD);

in GeoIP.php

in reply to: ↑ 61   Changed 14 months ago by simone

follow-up: ↓ 67   Changed 14 months ago by barry_van_dijk

  Changed 14 months ago by dkartik

follow-up: ↓ 70   Changed 13 months ago by Rugmonster

in reply to: ↑ 64   Changed 13 months ago by Rugmonster

  Changed 13 months ago by vipsoft

  • description modified (diff)

  Changed 13 months ago by alivenk

in reply to: ↑ 66   Changed 13 months ago by shi

  Changed 13 months ago by evanism

  Changed 13 months ago by domtop

  Changed 13 months ago by Smith

  Changed 13 months ago by Smith

in reply to: ↑ 29   Changed 12 months ago by eweber

Replying to matt:

Replying to mvanlaar:

Is it possible in future version also build a version that can lookup againt a mysql table with the data from citylite?? The file version is very slow under load. And allowing more executing time isn't (imho) a solution.

for more performance the best solution is to install the Mod GEOIP on your apache server. geoip lookups will be lightning fast!

I tested this with the MySQL version of the maxmind database (http://ipinfodb.com/ip_database.php) and modified the plugin to perform a mysql lookup instead of the binary database and ran the update script several times. A lookup via MySQL takes about 25% longer than the current version and the mysql tables take up about 300 MB. So it's no benefit whatsoever.

in reply to: ↑ 62   Changed 12 months ago by anush

in reply to: ↑ description   Changed 9 months ago by DaSilva

Is this plugin still in development? I would like to use the data from this plugin to show the visitors on Google Maps (or another map service)...

  Changed 9 months ago by matt

  • sensitive unset

DaSilva, you are welcome to contribute to the plugin. Ideally, you would post a screenshot and a link to your example piwik with the plugin installed so we can have a look and provide Feedback. When your work is done, we can consider including it in the GeoIp plugin.

  Changed 8 months ago by janelo

in reply to: ↑ description ; follow-up: ↓ 81   Changed 8 months ago by anamela

in reply to: ↑ 80 ; follow-ups: ↓ 82 ↓ 86   Changed 8 months ago by thibaut

in reply to: ↑ 81   Changed 8 months ago by zoc

Replying to thibaut:

So if anybody has the possibility to craft a GeoIP version (or a working fix) that would run with 0.5.4 I (as many others I'm sure) would greatly appreciate !

I don't know what your problem is, but I have been using this plugins for months, and it is still working with 0.5.4 on my server.

  Changed 8 months ago by vipsoft

  • priority changed from major to normal
  • type changed from Bug to New feature

  Changed 8 months ago by theodorius123

I have all the time the "unknown" problem and sometimes "UserCountry_country_--" - but most of the time the country-detection works fine. I tried everything :( Same with o.5.1 - so I installad a complete new 0.5.4 with the actual GeoIP from this page and downloaded a new database-file. I followed the instruction one by one. I am sure everything is in the right directory.

Has someone some steps I could try?

  Changed 8 months ago by theodorius123

in reply to: ↑ 81 ; follow-ups: ↓ 87 ↓ 88   Changed 8 months ago by anamela

in reply to: ↑ 86 ; follow-up: ↓ 93   Changed 8 months ago by anamela

in reply to: ↑ 86 ; follow-up: ↓ 91   Changed 7 months ago by theodorius123

Replying to anamela:

Now I am using (@theodorius123 that should work for you, too!): {{{ if(!defined('PIWIK_INCLUDE_PATH')) { define('PIWIK_INCLUDE_PATH', '../../..'); } if(!defined('PIWIK_USER_PATH')) { define('PIWIK_USER_PATH', '../../..'); } }}}

Then running the script from the command line SHOULD FUNCTION.

yes! Thank you! :)

mmh. this is not realy working for me. 

857 rows to process in piwik2_log_visit...

Fatal error: error traversing database - perhaps it is corrupt? in /users/theo/www/piwik/plugins/GeoIP/libs/geoip.inc on line 422

and instead of cities i see all the time "Unknown"

:-(

  Changed 7 months ago by laggi

After installing everything except this step "For more performance, it is recommanded to install the module apache mod_geoip. Configure your Apache according to:" piwik tracks nothing anymore. After deactivation everythings works fine.

I changed all my previous reports. I've changed the PIWIK_INCLUDE_PATH to my local path in the geoipUpdateRows.php. After that change i could update all my old reports and it works fine. But the problem now is that I couldn't track any new visitor.

  Changed 7 months ago by laggi

in reply to: ↑ 88 ; follow-up: ↓ 92   Changed 7 months ago by familienfreund

in reply to: ↑ 91   Changed 7 months ago by familienfreund

in reply to: ↑ 87   Changed 7 months ago by john435

  Changed 7 months ago by heinetz

  Changed 7 months ago by heinetz

  Changed 6 months ago by bk

php 5.2 + mysql 5.0.X SLES 10.3 distribution. GeoIP v0.12, piwik 0.5.4 GeoIP.php: updateExistingVisitsWithGeoIpData does not work for me. It only updates one record per each block on limit. Pretty much "while ( $row = $stmt->fetch() )" works for one record only. Changed above line 346 "while ( $row = $stmt->fetch() )" to these two lines. $allRows = $stmt->fetchAll(); foreach ($allRows as $row) works fine after the change.

By the way, using geoipUpdateRows.php script from command line only.

  Changed 5 months ago by renelux

Is it possible that in the archived data the latitude and longitude will be stored in the database. Would like to use this for a maps plugin.

  Changed 5 months ago by sveniboy70

  Changed 5 months ago by matt

  • description modified (diff)

  Changed 5 months ago by matt

In Piwik 0.6, there is a backward incompatible API change in the archiving code.

All GeoIP users will need to upgrade to the latest GeoIP pluginafter they upgrade Piwik to 0.6. Piwik will automatically disable the GeoIP plugin during the upgrade to 0.6.

  Changed 5 months ago by blair

Hi, I have recently installed the latest version of Piwik .5.5 and it all works fine. I then installed Geoip and again it worked except I get the following problems...

Country (GeoIP)

UserCountry_country_ 218

and

Continent (GeoIP)

UserCountry_continent_ 218

I have looked through this thread and do not see a solution? Is there one and if so what can be done?

regards,

Blair

in reply to: ↑ description ; follow-up: ↓ 103   Changed 4 months ago by e.marguin

Hi,

I developed some kind of patch to store regions more than city (I find it more practice). You can find it here :  Region display patch. Explanation in french (sorry) here :  Region display explanations.

Please feel free to tell me if you see something wrong in this patch.

in reply to: ↑ 102   Changed 4 months ago by mike3050

Replying to e.marguin:

Hi, I developed some kind of patch to store regions more than city (I find it more practice). You can find it here :  Region display patch. Explanation in french (sorry) here :  Region display explanations. Please feel free to tell me if you see something wrong in this patch.

Hello,its possible to find any English version of this? Also it's possible to redirect users from mobile/smart phones to a different page? THanks, Mike

follow-up: ↓ 105   Changed 4 months ago by e.marguin

ok i'll just try to translate here the main things (i'm sorry by advance for my poor english)...

The geoip module gives us information about the city of visitors. But often we do not know this city because it's too small. That's why I prefer to display regions, like in the other web analyzers tools.

The patch give upper stores the region name in db instead of the city name.

To install it, just extract it into the plugin/GeoIP/ folder. It do not update old datas. It's technically possible but I didn't need it. If anyone really wants me to write this script, I can.

Eric

in reply to: ↑ 104   Changed 4 months ago by mike3050

Replying to e.marguin:

ok i'll just try to translate here the main things (i'm sorry by advance for my poor english)... The geoip module gives us information about the city of visitors. But often we do not know this city because it's too small. That's why I prefer to display regions, like in the other web analyzers tools. The patch give upper stores the region name in db instead of the city name. To install it, just extract it into the plugin/GeoIP/ folder. It do not update old datas. It's technically possible but I didn't need it. If anyone really wants me to write this script, I can. Eric

ok,thank you ERIC. I will give it a try.

  Changed 4 months ago by matt

mike3050, please don't add links to your websites in the trac comments, thank you.

  Changed 4 months ago by leonardof

  Changed 4 months ago by tino

  Changed 4 months ago by scara

  Changed 4 months ago by scara

Changed 4 months ago by matt

ONLY use for Piwik < 0.6

  Changed 4 months ago by matt

  Changed 4 months ago by TulipVorlax

  Changed 4 months ago by TulipVorlax

  Changed 4 months ago by TulipVorlax

  Changed 4 months ago by scara

  Changed 4 months ago by scara

Hi,
I'm happily (== both from app&sys perspectives) running Piwik 0.6.1 + GeoIP using the DB file w/o mod_geoip. Found a minor (at this time) issue: Country numbers don't match with Continent numbers for Europe, where the sum is always minor than the number obtained by summing all the single countries, see below. I'm wondering if this is a bug in the code or in the lookup table.

Country (GeoIP)
Country Unique visitors
Italy 997
Unknown 66
United States 5
Ireland 3
Great Britain 3
Poland 2
Bulgaria 1
Russia 1
Switzerland 1
Germany 1


Continent (GeoIP)
Continent Unique visitors
Europe 959
Unknown 66
North America 5

follow-up: ↓ 118   Changed 3 months ago by snowdiver

in reply to: ↑ 117 ; follow-up: ↓ 119   Changed 3 months ago by snowdiver

Replying to snowdiver:

I experience the problem, that the GeoIP data is always saved as "unknown" in the piwik_log_visit table without any city labels, latitude etc. I installed the plugin according to your instructions above. GeoIP ver 0.13, piwik ver 0.6.1 GeoIPCity (May 2010) Is there any hint to fix this problem? Thanks

EDIT: Problem solved. The apache module is installed on my server, but doesn't provide enough geo-information. Switching the module-search off fixed this problem!

in reply to: ↑ 118 ; follow-up: ↓ 120   Changed 3 months ago by theodorius123

Replying to snowdiver:

EDIT: Problem solved. The apache module is installed on my server, but doesn't provide enough geo-information. Switching the module-search off fixed this problem!

sorry, i am not that fit with all these server, php, etc stuff. i am using piwik and geoip and have the same "unknown" problem. but i dont have an own server, my homepage is on a freehoster-server (bplaced.net). is it possible to do what you did when i am using a free-hoster??

thanks! theo

in reply to: ↑ 119   Changed 3 months ago by snowdiver

Replying to theodorius123:

Replying to snowdiver:

EDIT: Problem solved. The apache module is installed on my server, but doesn't provide enough geo-information. Switching the module-search off fixed this problem!

sorry, i am not that fit with all these server, php, etc stuff. i am using piwik and geoip and have the same "unknown" problem. but i dont have an own server, my homepage is on a freehoster-server (bplaced.net). is it possible to do what you did when i am using a free-hoster?? thanks! theo

My homepage is also hosted on bplaced.net servers, so we met the same problem. It is just a small modification of the GeoIP.php file. Just remove or comment lines 230-234 to force the plugin to use the GeoLiteCity.dat file instead of the Apache Module, which in the bplaced.net case does not provide information about the city of a visitor.

  Changed 3 months ago by todem

  Changed 2 months ago by mbeach1

  Changed 2 months ago by scara

  Changed 2 months ago by mbeach1

  Changed 2 months ago by leonardof

The most frequent "city" in my stats is "unknown". Any suggestion for a better geoip database? I'm concerned only with Brazil, FWIW.

  Changed 2 months ago by matt

  • description modified (diff)

  Changed 2 months ago by matt

  • description modified (diff)

  Changed 2 months ago by matt

  • description modified (diff)

  Changed 2 months ago by matt

All users experimenting issues with the geoipUpdateRows.php script, we have fixed a few bugs reported by users in this thread. Check out the new GeoIP 0.14 version.

If you still have issues, please report here, we'll do our best to fix it.

Users that are seeing a lot of 'unknown' countries and cities, this is probably due to the low coverage of the Free IP to country database provided by MaxMind. If anyone knows a better DB, let us know! You can alternatively buy the MaxMind pro DB, but I haven't tested how accurate this DB is.

  Changed 2 months ago by matt

  • description modified (diff)

Changed 2 months ago by matt

GeoIP plugin for Piwik >= 0.6

  Changed 2 months ago by leonardof

How do I know if a database is in the file format for GeoIP?

  Changed 2 months ago by Sharpless

I seem to have some problem with the geoipUpdateRows.php script. What I can see, nothing happens when I run the script, all older data is just NULL, newer data from the same IP is however marked correctly.

  Changed 2 months ago by Beatgarantie

btw: could it be that the geo-ip colums stay in the db after plugin deactivation? Is this bahavior wanted?

  Changed 6 weeks ago by mike5464

With the last version, i still get this error.

Internal Server Error

The server encountered an internal error or misconfiguration and was unable to complete your request.

Please contact the server administrator, postmaster@… and inform them of the time the error occurred, and anything you might have done that may have caused the error.

More information about this error may be available in the server error log.

  Changed 3 weeks ago by IanBezanson

I'm trying to run the latest version of this plugin on Piwik 0.9 and all I seem to be getting is UserCountry_country_ as Blair noted in Comment #101. Has anyone found a solution for this?

in reply to: ↑ 62   Changed 13 days ago by vinzentt

I had a problem of exit without error and no log of my visits, i followed this: Replying to marcello.ceschia:

for people with limited memory you can replace {{{ $this->geoIpDb = geoip_open($geoIPDataFile, GEOIP_MEMORY_CACHE); }}} into {{{ $this->geoIpDb = geoip_open($geoIPDataFile, GEOIP_STANDARD); }}} in GeoIP.php

and it worked, on a dedicated server with 2Go of ram ...

Note: See TracTickets for help on using tickets.