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

Closed
mattab opened this Issue Jan 3, 2008 · 183 comments

Comments

Projects
None yet
9 participants
Owner

mattab commented Jan 3, 2008

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?
- [Download Piwik GeoIp Plugin](http://issues.piwik.org/attachments/5465/GeoIP.zip?format=raw)
- Unzip the plugin and copy the extracted directory “GeoIp” in the directory piwik/plugins/
- Configure the MaxMind database:
– Either download MaxMind GeoLite City or GeoIP City database from http://geolite.maxmind.com/download/geoip/database/GeoLiteCity.dat.gz
Uncompress this file and copy it in plugins/GeoIP/libs/GeoLiteCity.dat
– For more performance, it is recommanded to install the module apache mod_geoip.
Configure your Apache according to: http://www.maxmind.com/app/mod_geoip
- Enable GeoIP plugin in Piwik. Go to Piwik > Settings > Plugins > Geoip, click Enable

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 Zawadziski
- Matthieu Aubry
- Anthon Pang

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
- version 0.15:
– Updated for Piwik 1.3 (refactoring for upgrade.php)
– No match for country now set to ‘xx’ (unknown)
- version 0.16:
– fix bootstrap in geoipUpdateRows.php and clean-up package
– geoipUpdateRows.php – MYSQLI compatibility
– default to disk-based search; load into memory if multiple tracking requests
- version 0.17:
– fixes from peterb & mruge
- version 0.18: compatible with Piwik 1.6

Feedback

Please leave a comment if you have any feedback, suggestion, or bug report.
Keywords: third-party-plugin

Owner

mattab commented Apr 14, 2009

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?

Owner

mattab commented Jun 15, 2009

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

Owner

mattab commented Dec 9, 2009

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.

Owner

mattab commented Apr 12, 2010

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.

Owner

mattab commented Apr 29, 2010

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

Owner

mattab commented Jun 29, 2010

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.

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@www.climb2climb.be 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.

Owner

mattab commented Sep 6, 2010

From forum post GeoIP just show true country in "Locations & Provider" and it doesn't show true country in "Visitor Log", "world map" and "Live Visitors!"

Contributor

robocoder commented Sep 7, 2010

As you say, each database has its limitations. Each also has different operating characteristics (e.g., system requirements, memory requirement, disk space requierments, and lookup time). I proposed that users be able to pick and choose which one to use, if any.

Users generally expect/assume the existing country & continent columns to be geolocated. (We have a FAQ to explain why it isn't.) What we can do is that when the country/continent is unknown, the plugin fallback to what we do now when GeoIP isn't installed/activated, i.e., guess the country from the Accept-Language header; and if that fails, then write 'xx'.

lehrer commented Sep 16, 2010

Hi,

I am using piwik 1.0.

I get the following error (same as reported above a few times):
UserCountry_country_

How can I visualize the countries and continetns instead of UserCountry_country_ and UserCountry_continent_ respectively?

GL

Contributor

robocoder commented Sep 25, 2010

Estaban: download and install GeoIP, and apply the patch from Ian in comment:142

Contributor

robocoder commented Sep 25, 2010

Country-to-continent assignment is via a lookup table. In Piwik 1.0, Central America is lumped in with South America.

For Piwik 1.1, we're using the Maxmind GeoIP mapping with the exception of Central America (which they lump in with North America). We've added Antarctica and Central America as separate continental regions.

lehrer commented Oct 5, 2010

Replying to vipsoft:

Estaban: download and install GeoIP, and apply the patch from Ian in comment:142
Hi,

How do I apply the patch?
I'm not used to work with diff files.

Owner

mattab commented Nov 16, 2010

Note: before including GeoIP in trunk, GeoIP should be modified so that it records the new country and continent in place of the default one (columns location_*) , rather than in new columns location_geoip_ in the log_visit table.

Contributor

robocoder commented Nov 16, 2010

If this is a candidate for trunk, I advise renaming it to Geolocation, since "GeoIP" is a trademark. Also, by using a generic, vendor-neutral name, we would be able to add adapters for other geolocation methods/services/products.

Owner

mattab commented Nov 16, 2010

I think GeoIP should move to core. This will improve user experience and allow for better Piwik features in the future, such as mapping cities and regions in the world map.

I created a ticket with proposed ideas: #1823

Owner

mattab commented Mar 3, 2011

Published the new GeoIP version for Piwik 1.2 and more.

If you upgrade to Piwik 1.2, you can also apply the following patch rather than downloading the new plugin:

136c136
<                           count(distinct visitor_idcookie) as nb_uniq_visitors,
---
>                           count(distinct idvisitor) as nb_uniq_visitors,

177c176
<       $query = $archiveProcessing->queryConversionsBySegment("location_geoip_continent,location_geoip_country,location_geoip_city");
---
>       $query = $archiveProcessing->queryConversionsByDimension("location_geoip_continent,location_geoip_country,location_geoip_city");

Contributor

robocoder commented Mar 4, 2011

kip: the script expects to be run from within the same folder

cd /home/me/website.com/piwik/plugins/GeoIP/misc
php geoipUpdateRows.php 
Contributor

robocoder commented Mar 26, 2011

buzz: for performance, geolocation only happens on the first page of a new visit. You either have to wait out the session timeout, or clear your cookies to be treated as a new visitor.

Contributor

robocoder commented Apr 19, 2011

fixed in the updated GeoIP.zip package; bumped the version to 0.15

re: page load time of this trac ticket -- this ticket will be closed as soon as we move this plugin into the core distribution; see #1823

Contributor

robocoder commented Apr 19, 2011

Sorry, repackaged.

Contributor

robocoder commented Apr 20, 2011

Yes, it tries to load the geo database into memory. This is Maxmind's library so it is "by design".

The new geolocation plugin will offer more choices, esp for resource constrained environments.

Contributor

robocoder commented Apr 27, 2011

Remove references to GeoIP from your config.ini.php.

In your plugins/ folder, either remove the GeoIP folder, or re-upload the contents. (note: filenames may be case
sensitive)

svogt commented Apr 29, 2011

Installed GeoIP v15 into a Piwik 1.3 and the plugin seems to be working (I had a look into the new widget and they show the cities correctly). What currently isn't working is the Visitor-WorldMap which I thought should show the new stats as well. Am I missing something?

Contributor

robocoder commented Apr 29, 2011

SaschaVogt: that's because we haven't changed the GeoIP plugin to overwrite the standard columns

The GeoIP plugin is in maintenance mode as I'm currently working on its replacement in #1823.

Contributor

robocoder commented Apr 29, 2011

Minor update in 0.16:

  • fixed the bootstrap code in geoipUpdateRows.php so it shouldn't matter what directory you execute the script from; removed misc/tmp and misc/PIWIK_USER_PATH from the package
  • a variation of marcello.ceschia's fix in comment:62 - GeoIP will now default to a disk-based lookup (GEOIP_STANDARD); if there are subsequent requests (e.g., bulk update), it'll then load the .dat file into memory (using GEOIP_MEMORY_CACHE requires about 30MB at runtime)
  • bk's fix in comment:96 - which should be MYSQLI compatible

Replying to lc1:

as you can see, loc data of Leonding is correct, but data of Vienna isn't.
Has anybody an idea why?
piwik is accumulating the longitude and altitude by archieving. but o don't know what i have to change...

Did you ever find out what you need to change to avoid that problem? I have the same problem, so you might have some advice ..

Contributor

robocoder commented Jun 26, 2011

myam: disable eAccelerator -- it's not as stable as other opcode caches; yes, possible to query from a MySQL table but not planned for this plugin; for example, the new geolocation plugin in #1823 supports a table lookup for InfoDB's data

bavarian: I don't know where this is occurring; I'll see if it happens with the new plugin ...

controlc.de: make sure you're using the GeoLiteCity.dat (or GeoIPCity.dat); if you have mod_geoip installed, this plugin will use that instead, so make sure it's also using the city .dat file

Contributor

robocoder commented Jul 3, 2011

rafal: no because (1) GeoIP is not a core plugin, and (2) when we do move that functionality into core, it'll use the existing location_ fields.

Member

BeezyT commented Oct 5, 2011

In the upcoming release, there is an extension of the segmentation API which breaks the GeoIP plugin.

In Piwik_GeoIP::archiveDayAggregateVisits, udpate $labelSQL = 'log_visit.location_geoip_continent';

This will prevent the archiving process from failing.

I might add the new features soon, but maybe you want to have a look at the changes and do it yourself. If you want to see the way the new querying works, have a look at Piwik_ArchiveProcessing_Day::getSimpleDataTableFromSelect: you need to use $segment->getSelectQuery. Note that all fields have to use a table prefix.

Owner

mattab commented Oct 18, 2011

Attachment: 0.18
GeoIP.zip

Replying to matt:

From forum post GeoIP just show true country in "Locations & Provider" and it doesn't show true country in "Visitor Log", "world map" and "Live Visitors!"

Hi Matt, Can you please add this information in the top section "GeoIP Plugin" of the description and also add instructions for replacing respective widgets to the end of section "How to install?"? This will save people a lot of time troubleshooting. Maybe also cross-ref to #1823 to show users that integration into core is being worked on. Best regards, JJ

Member

gka commented Nov 6, 2011

After installing the plugin (0.18) on Piwik (1.6) no more visitors were logged. Deactivating the plugin fixed the bug. Maybe I missed something?

Member

BeezyT commented Apr 16, 2012

The plugin does not use $archiveProcessing->shouldProcessReportsForPlugin() in archiveDay and archivePeriod like all core plugins. When using segmentation, this causes serious performance overhead. Could you fix this?

Contributor

robocoder commented Jul 26, 2012

Reported in #3212: the GeoIP plugin does not support row evolution.

Member

gka commented Jul 27, 2012

I patched GeoIP.php to get it working with the new world map widget. The only thing I changed was to add a column for the GeoIP region code that is returned by the API but wasn't stored in the Piwik database. However the map needs this region code to fill those nice country region maps with colors :)

Member

gka commented Jul 27, 2012

Attachment: I patched GeoIP.php to get it working with the new world map widget. The only thing I changed was to add a column for the GeoIP region code that is returned by the API but wasn't stored in the Piwik database. However the map needs this region code to fill those nice country region maps with colors :)
GeoIP.php

Owner

mattab commented Oct 3, 2012

In the next 1.9 release, GeoIP will be included in Piwik by default. See #1823

Member

BeezyT commented Feb 11, 2014

You can write a custom plugin that hooks into Tracker.saveVisitorInformation, checks whether location_ip is in your subnet and overrides the other location_* attributes of the visitor information.

This ticket is not the right place for questions like this. If you want further free support, you can use our forums. If you want paid support (e.g. the development of such a plugin), you can contact services] or me via timo at piwik.org.

This issue was closed.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment