One exercise that you’d like to do is find out which countries your visitors are coming from. There are several reasons for doing this:<\/p>\n
1) You might want to tailor your content to that particular demographic.<\/p>\n
2) If you have lots of visitors from a particular country, you might want to consider adding a version of your website in that particular language.<\/p>\n
3) If your site has IP-based targeting for ads (programs such as Adsense have an IP-targeting component), this will help you understand why, or why not, your users are clicking on your ads.<\/p>\n
The way I did it was to download a flat file that include IP-to-country mapping data from the link found here<\/a>. This is a free database. There are other versions that claim to be more accurate, but they charge a fee. For my purposes, the free version is sufficient.<\/p>\n I first loaded this file into a database and then use a table join to lookup the country code from the IP number. This turned out to be a time-consuming exercise. Then, I remembered that in data warehousing, you want to do as much of your data transformation outside of the database as possible. Applying that principle, I moved the country lookup portion into the perl processing routine prior to loading the data into the database. This move proved to be an excellent time-saver.<\/p>\n Below I show the perl code for matching IP address with country code. There are 3 basic steps:<\/p>\n 1. Read IP\/Country mapping file<\/a>.<\/p>\n 2. Convert IP address to IP number<\/a>.<\/p>\n 3. Find country code based on IP number<\/a><\/p>\n The code for each step is shown below:<\/p>\n <\/a>1. Read IP\/country mapping file<\/u><\/p>\n open (IN1,’ip2country.txt’); $i=0;
\n## ip2country.txt is the file that stores the
\n## IP number\/country mapping data. Assuming the
\n## following format: IP_START,IP_END,COUNTRY_CODE.<\/p>\n
\nwhile (<IN1>) {
\nchomp;
\n@ips = split (“,”);
\n$ip_start[$i] = $ips[0];
\n$ip_end[$i] = $ips[1];
\n$ip_country2[$i] = $ips[2];
\n$i++;
\n}<\/p>\n