Data Standardisation: Creating Sound Data Plans for CiviCRM

CRM Data plans refer to documenting agreements on representation, format, definition and structuring of data. Current and future CiviCRM users can utilise it to increase efficiency, knowledge creation and interoperability with other systems.

In order to optimise and future-proof the CRM system, the data structures, in addition to the data itself, need a good re-work. There are two main steps to the process:

• Reviewing data and operations, and setting the new structures
• Remodelling existing data to match the new structures

Create a Data Plan

1. Collate and list all data fields (names/ headings, descriptions, options) that are collected through all channels, formal, informal, paper, digital etc. onto a single repository.
2. Create a data dictionary. Consider adopting data standards relevant to your industry for ease of re-use and interoperability.
3. Define fields’ options. Quantifiable data is the key to turning data into information. Group the values of open-ended-fields to several distinct single or multiple choice options or ranges.
4. Merge multiple similar fields into one where appropriate. Eliminating data silos is an important benefit of a centralised CRM, capitalise on it.

Despite it being early days in terms of creating data architecture, CiviCRM users and future users, can start structuring the fields in CiviCRM fashion. I use templates such as this one:

If you have reporting requirements (such as for government funding) consider structuring the data to mirror them. Here is an example of one of many fields that the NSW State Government (Australia) requires service providers to report back on. This one describes living arrangements, and is perfectly good to upload to CiviCRM and use routinely, with the added bonus of collecting reporting data on the fly.

(Source:  Home and Community Care Program (HACC), National Minimum Data Set (MDS), USER GUIDE, Version 1.0 - 2014-15)

A document need not be very long. It can be as short as several pages. So long as it is very clear. Here’s an extract from one of my more minimalist data plans:

The result of uploading multiple similar fields is very limited knowledge creation bounded by form type. Merging the field is easy and will increase knowledge creation:

If, upon review, the “other description” field appears to collect repetitive description types, consider adding more options to the merged field.

Apply the New Structure to Existing Data

Review existing data and mould it to fit the new structure.

For data already in CiviCRM,

1. Create new custom fields,
2. Update the data for existing contacts using the batch update via profiles or exporting to CSV, and importing the new values back into CiviCRM (I must admit I'm a fan of the export import option),
3. Point any profile fields to the new field instead of the old, and
4. Disable obsolete fields (the data will remain on CiviCRM but not shown to users).

For new implementations, the data can be altered before the initial import as only the new custom fields would be created in CiviCRM.

Use absolute time values. Ask for graduation year, rather than how many years’ experience. Ask “When did you start playing the flute” rather than “how long have you been playing the flute”

I hope this helps! Please leave a comment or contact me. Thanks, Nili