How Can We Help?

< Back
You are here:
Print
Table of Contents

Use of Location Types, Phone Types and Address Types

Summary

Over 30% of the 18,700 phone numbers are not correctly formatted. 1,388 mobile numbers are in the landline columns and 46 landline numbers are in the mobile columns.  In addition, 48 contacts have both a main and work phone number which needs to be rationalised. This can all be fixed up by exporting, transforming and then re-importing all phone numbers. As a result, one location type and two phone number types can be disabled in order to simplify data entry and reporting.

Current usage

CiviCRM, out of the box, has the following types (with little definition as the use of each – the name is meant to be sufficient, but between Main and Work there is confusion):

  • Location Types (that apply to both phones and street addresses):
    • Home
    • Work
    • Main
    • Billing
    • Other
  • Phone Types (that apply only to phones):
    • Phone
    • Mobile
    • Fax
    • Pager
    • Voicemail

The number of resultant distinct “fields” are:

  • As a phone number has both a location type and a phone type, there are 25 combinations (5 x 5).
  • A street address has 5 possibilities.

This gives 30 fields in each of Individual, Household and Organisation – thus a total of 90 fields. Subtypes are not considered yet.

At COTA Victoria, these are enabled (or rather not disabled) and, consequently, data has been collected into them.

The aim is to fit this to the needs of COTA Victoria. Thus, the following is proposed:

  • Location Types (that apply to phones and street addresses):
    • Home – retain
    • Work – retain
    • Main – discontinue and migrate all values over to Work
    • Billing – retain (this is mandatory in CiviCRM)
    • Other – retain
  • Phone Types:
    • Phone – retain (rename as Landline) – migrate all mobiles numbers to Mobile
    • Mobile – retain – migrate all non-mobile numbers to Landline
    • Fax – retain – there are about 150 of these
    • Pager – disable – there are none of these
    • Voicemail – disable – there are none of these

This would result in only 12 combinations (4 x 3) – down from 30 and the street address would have only 4 possibilities, giving 16 overall down from 30. Across the three Contact types this is 48 down from 90.

Phone number conventions

  1. Australian mobiles: 04xx xxx xxx or 05xx xxx xxx
  2. Australian non-mobiles: xx xxxx xxxx
  3. International: any text although it usually starts with “+
  4. Multiple phone numbers in the one field: use the conventions above and separate phone numbers with “;

Expected Outcomes

  • All phone numbers conform to the conventions above (at least for a moment in time)
  • Many fewer combinations of fields (for simpler processing)
  • Clarity over the use of phone number fields (no remaining confusion between Main and Work)

Applying the CleansePhoneNumber() function to the Work Phone field of 1,204 organisations (as a test) resulted in only 38 numbers (3%) not being able to be automatically transformed (cleansed) as per conventions 1 and 2 above, and the number in convention 4 was only 22 (2%). Convention 3 is effectively a “leave alone” process. A similar result could be expected in all of the other 90 fields. This is a massive time saver as compared to cleansing the phone numbers by hand.

Processing

Do the following for each Contact type:

  • Export all of the above fields for one Contact type including the Unique ID of the contact and its name – 32 fields in a .csv.
  • Copy/paste the contents of the .csv into the first sheet of an Excel workbook. In a second sheet, apply the CleansePhoneNumber() function (recently written and tested) to every value from the first sheet. In a third sheet, analyse the cleansed phone numbers to find out which mobile and landline numbers need to be swapped and how many main, fax, pager and voicemail numbers need to be migrated. In a fourth sheet, carry out the swaps and migrations. In a fifth sheet, display just the values that are required to be re-imported as Overwrite with no duplicate checking (match based on Unique ID). Copy/pasteSpecialValues the fifth sheet into a .csv file for importation.

Processing for street addresses has not yet been considered but would build on this work.

Considerations

  • The use of the Billing phone number may be discontinued based on the frequency of occurrence and the existing use in the SFL processing.
  • Subtypes are not yet considered. Only a very small number of contacts are such subtypes.
  • Phone number fields will be disabled, not deleted so that any existing reports continue to operate correctly.
  • If the Main is not empty, the Home is empty, the Work is not empty and Main is not equal to Work, then set Home to Main.
  • If the Main is not empty, the Home is not empty, the Work is empty and Main is not equal to Home, then set Work to Main.
  • For each of the four Locations: if the number in Mobile is a landline number and the Landline is empty then set Landline to Mobile and clear Mobile.
  • For each of the four Locations: If the number in Mobile is a landline number and the Landline is empty then set Landline to Mobile and clear Mobile.
  • An organisation should not have any Home entries (phone, email, website or address)

Analysis

Note that columns with zero totals have been omitted to allow this table to fit within the page.

Totals Home-Phone-Phone Home-Phone-Mobile Home-Phone-Fax Work-Phone-Phone Work-Phone-Mobile Work-Phone-Fax Main-Phone-Phone Main-Phone-Mobile Main-Phone-Fax Other-Phone-Phone Other-Phone-Mobile Other-Phone-Fax Billing-Phone-Phone Billing-Phone-Mobile Phone
Phone number transformed 6,774 1,614 133 17 1,153 133 148 355 15 1 67 8 1 3 1 3,125
Phone number not needing transformation 11,785 3,191 1,263 3 986 430 48 310 102 44 183 73 3 3 5,146
Total phone numbers 18,559 4,805 1,396 20 2,139 563 196 665 117 45 250 81 1 6 4 8,271
Number of clashes Work vs Main 48
Number of mobiles in wrong column 1,388
Number of non-mobiles in wrong column 45