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

[mattab]

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

[anonymous-matomo-user]

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…

[mattab]

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?

[anonymous-matomo-user]

@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

[anonymous-matomo-user]

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) { [ string(2) "de" "city"=> string(7) "Hamburg" [ float(53.55) "longitude"=> float(10) }

Piwik still displays UserCountry_country_ for new requests...

[anonymous-matomo-user]

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?

[mattab]

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

[anonymous-matomo-user]

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?

[anonymous-matomo-user]

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

[anonymous-matomo-user]

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.

[anonymous-matomo-user]

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)...

[mattab]

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.

[anonymous-matomo-user]

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.

[anonymous-matomo-user]

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?

[anonymous-matomo-user]

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"

:-(

[anonymous-matomo-user]

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.