Wednesday, December 1, 2010

Mobile Operator/Carrier detection using MaxMind – First Part – Database information

One important key targeting of mobile advertising networks, is the ability to target mobile campaigns for specific mobile operators/carrier. In order to it, we have to be able to detect the mobile operator of a given IP. I have been looking for quite some time (the passed 2 years) for a decent solution for mobile operator detection. The only decent solution (there are not much services like that) came from Quova. The problem with Quova, is that they don’t give you an API and some binary file (like most other geo/device targeting services). They insist you install dedicated server solution, that you have to query by HTTP, in order to get the information you want. If you have several machines, you have to start install Quova solution on each and every one of them.
Since we didn’t like Quova solution on the company that I work for (Mobile Ad Network), and we couldn’t find any other good solution, we decided on developing our own in-house solution. The solution is based on user IP and ISP and Organization data as it extracted from MaxMind geo service.
This is in general the detection processes:
  • Get client IP.
  • Use MaxMind to extract ISP and Organization from client IP.
  • Check what is the corresponding mobile operator/carrier for the given combination of ISP and Organization.
The solution is quite simple and neat. The only problem with it, is that it is needed to be maintained on a regular basis, since Geo information is being changed all the time.
In order to be able to identify mobile operators, we first need to have two tables containing the following information:
  • Mobile operators/carriers table, containing all mobile operators in the world. You can have a look at this post in order to see how I built the information in this table.
  • Mobile operators and their ISP and organization. This table maps between mobile operators and their corresponding ISPs and organizations. Building this table is a hard work involving data mining, research and continuing refinement and update process. During the time of this post, the data in this table is quite good, but not full (not all countries/mobile operators are mapped).
Note for:
  • The data in these tables is loaded to memory, since we need good performance.
  • MaxMind updates it’s ISP and Organization data on a monthly basis.
  • ISP and Organization is being changed all the time. The changes are not drastic, but they happen all the time, so data is needed to be maintained, and there is a need to keep refining the mapping between mobile operators/carriers and combinations of ISP and Organizations.
Let’s have a look at the structure of the mobile operators/carriers table. The table is called “carrier” and contains 3 columns:
  • carrier_id – The id of the mobile operator/carrier. This is not some universal id. It is an arbitrary id used only by the system.
  • country_code – The country code of the mobile operator/carrier.
  • name – The name of the mobile operator/carrier.
The SQL script that creates this table:
CREATE TABLE `carrier` (
`carrier_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`country_code` char(2) NOT NULL,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`carrier_id`)
) ENGINE=InnoDB;
This script contains both the scrip that creates “carrier” table as well as its data. It contains all the mobile operators in the world. This data has to be maintained (carriers are being added or merged from time to time), but not very often.

This is the structure of the table that maps ISPs and Organizations to mobile operators/carriers. The table is called: “carrier_mapping” and also contains 3 columns:
  • carrier_id – The id of the mobile operator/carrier, for which we would to like to map a combination of ISP and Organization.
  • isp – ISP corresponds to carried_id.
  • org – Organization corresponds to carrier_id.
The SQL script that creates this table:
CREATE TABLE `carrier_mapping` (
`carrier_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`isp` varchar(50) NOT NULL,
`org` varchar(50) NOT NULL,
PRIMARY KEY (`carrier_id`,`isp`,`org`),
CONSTRAINT `FK_carrier_mapping_carrier_id` FOREIGN KEY (`carrier_id`) REFERENCES `carrier` (`carrier_id`)
) ENGINE=InnoDB;
This script contains both the scrip that creates “carrier_mapping” table as well as its data. It contains a decent amount of mapping of most large mobile operators/carriers in the world. Note that the data here may not be so accurate in a few months or so.

7 comments:

  1. Amazing Article! Helped us a lot to re-fine our Carrier Targeting Capabilities here to MobFox. Thanks Guy!

    ReplyDelete
  2. Hi there,
    I really liked the article, thanks!
    We came across the same problem here at HUNT Mobile Ads, have used a similar aproach, and extender a bit too, would be nice to compare notes if you'd like.

    Thanks for sharing!

    ReplyDelete
  3. Hi Nicolas, thanks for the feedback. We have switched to Quova in order to be able to keep track after all the carrier changes and updates. They are not cheap but offer a decent service. Since we are working with Quova, probably our carrier string will be different than yours, which will make the comparison harder.

    ReplyDelete
  4. Hi Guy, that's a really interesting approach.

    However, aren't you concerned about false positives? For example if I take Spain, Vodafone is offering both 3G and ADSL. Same for Telefonica/Movistar. If the goal is to detect mobile carriers, you could confuse a user connected through Vodafone ADSL, which is definitely not on a mobile connection.

    I actually don't know if Vodafone is using different ISP for one and the other, maybe you know something I don't? :)

    In any case, thanks for your article.

    Ben.

    ReplyDelete
  5. Hi Ben, you are right. This solution doesn't distinguish between 3G and WiFi. As I mentioned above, we started using Quova to handle this issue.

    ReplyDelete
  6. Hi Guy, interesting post! Could you describe a bit more about the process you followed to produce the original carrier mappings? Also, it looks like the link to how you produced the mobile carriers table is gone, do you still have your notes regarding that list? Thanks!

    ReplyDelete
  7. Hi Nick,
    I fixed the link. Regarding the creation of the original list: it is not a simple thing that involves some data mining. What I usually is:
    - Take out a report for all the requests of some country. These are the columns I need: ISP, Organization (taken from Quova), number of requests.
    - Then I put the data in Excel and sort it by number of requests.
    - Usually, the places from which high number of requests are coming are ISPs/Carriers/Mobile Operators.
    - Then I try to figure out according to the ISP and Organization to which carrier these values should be mapped (I simply google the names of the ISP/Org and read about the companies).

    ReplyDelete