Cleaning up dirty mobile numbers on CiviCRM

CiviCRM logo

CiviCRM is an open source platform that sits alongside Drupal, as well as other popular content management systems. It is a Customer Relationship Management system, but because it has been developed primarily for the Civic Sector (not-for-profits, voluntary associations, etc), it is referred to as a 'Constituent' Relationship Management system instead.

The software provides a powerful contacts database, around which is built a range of components, some of the most useful being mass email and SMS services, event management, reports, memberships, and co-ordinating on-line campaigns.  

Over recent months I have been doing some work with Caroline Badley, who is based in Birmingham, and operates a 'Civi shop', using Drupal 7 and CiviCRM to provide campaign management services to a number of civic networks in the UK, with a combined constituency of tens of thousands.

One of the regularly tasks is importing new contacts into their systems, and any developer / site admin who has had the experience of large data imports will appreciate how messy customers can be with their data. One of the problems we have had is with phone numbers being formatted inconsistently, and with mobile numbers being recorded as land line numbers, and vice versa.  Normally, this wouldn't be such an issue, but when you are using the numbers to conduct mass SMS 'mailings', then it becomes very important that the mobile numbers are stored in the correct field, and that they have the correct international format.

There is an existing CiviCRM extension called Phone Number Validater, which will tell you which numbers on your database are improperly formatted, but it stops short of actually fixing them for you. Cleaning up the numbers manually in a spreadsheet before importing the data is very time consuming, so I wrote a short module, which cleans 'dirty' mobile phone numbers after they have been imported, by stripping out spaces, giving them the correct international format, and making sure they are categorised correctly.

The module is called CiviCRM Mobile Cleaner and can by reviewed here. Unfortunately it only works for UK mobile numbers, but the code could be adapted quite easily for other international formats if needed. It isn't 100% accurate, as it very occasionally confuses a land line number with a mobile number, but it will clean thousands of poorly formatted numbers in a matter of seconds, so this is a small price to pay.

If you would like any assistance installing or adapting this module for your own site, then please get in touch.