In the second part of this series, we looked at dirty data and what you can do to mitigate it. But what if you’re not starting from the ground up?
What if your practice has been running for some years with no real data governance, and now it feels like your data is too far gone – too dirty – to do anything about it? Thankfully, Xplan has some handy tools to help you report on (and fix) your data in bulk.
Where to start? Knowing what's important to your business
It's important to be able to identify the information that is used and relied on in your business. There is no point in starting your cleanup with data that's rarely used.
Stick to what's important and once this is spic and span, look at the rest. For example, if your client base is primarily retirees or close to retirement, focus on cleaning up super and pension data rather than dependents.
Alternatively, if you see the majority of your client base as high-growth investor types with complex family or entity structures, start by ensuring dependents and estate planning details are up to date.
There are also those lesser-known but crucially important areas that will apply to all businesses and client types, such as:
- ensuring a client's entity status aligns with the client category
- removing clients marked as deceased from mailing lists
- checking outstanding review dates aren't as far back as 1994
Once you know what needs addressing, you need to determine what type of data you want to be able to find and then fix. For example, do you want to be able to find all the clients who are missing information/data in a specific field (such as their marital or health status) or do you want to find the clients who potentially have incorrect information against them?
Which way you answer will affect how we tackle the problem. Let's start with the first one: missing data.
Finding and updating what’s not there
The best way to find missing data is to set up advanced criteria searches on the fields that you wish to interrogate. From here, you can either bulk modify all the clients or you can export the field(s) into a CSV file and then reimport back into Xplan once the file has been updated. Let's see this in action:
1: Advanced search criteria
If you're looking for a field with missing data, it's best to use a search criteria with the field name is blank. Using our above examples, these would look as follows:
2: Running the search
You can either run the search straight from here, or you can save the criteria and load it whenever you want to run the search.
Alternatively, you can set up the above search criteria to run automatically:
- Scheduler: you can set an advanced search to be run automatically at regular intervals. For example, if you wish to run the search monthly or half yearly, to ensure your data isn't missing anything crucial.
- Xport report: as a secondary step to the schedule, you can then run an Xport report to give you the list of clients and the data fields that can be automatically emailed to you or available to download from your notification icon when you next log into Xplan.
Now that we've identified these clients, how do you update information quickly and easily? There are a couple of ways to do this without having to manually update each client profile in Xplan one at a time:
- Bulk modifying client data: if there's a field that only requires you to select an option of "Yes" or "No" in it, and you have all the clients you want to change to one of those options, you can perform a bulk modification on all of the clients in one go. Be mindful, however, that for some bulk modify actions, you will need to have specific capabilities.
- Importing data: if you want to update the clients with more specific data and don't want to (or can't) bulk modify, you could potentially use a CSV import file.
The easiest way to do this is to first export all the clients in the list with the field to update into a CSV file, then update the file and re-import this back into your Xplan site.
Remember that depending on the data and/or field, some imports may require you to select to “override” the existing information (which will remove anything already in the field) or “append” (which will add to what is already there without removing data).
Fixing what is not right
Sticking with our Victoria example, this would look like the following in an Advanced Search criteria set:
Once you have a result of the clients with any inconsistencies, you can then export the data into a CSV file (as above) and fix up any incorrect items. When re-uploading to Xplan, remember to ensure you use the correct option of either "override" or "append".
Important tips about CSV export and upload files
Tip 1: Save your original export file as a backup
Nothing is worse than something going wrong and you don't have a backup plan. Saving your export file as a backup before making any changes to it will ensure you have security if something inadvertently happens when you upload your update file. If it does, you can use the backup file to reinstate the data.
Tip 2: Be mindful when exporting and re-uploading group field data
If using the “override” function for any group field upload (such as the address instance above), you'll need to ensure you have exported all client data in the field first and then use the override upload function to re-upload all the data.
This will ensure that you've completely removed the existing data and uploaded all address information, not just the updated address(es). Using the “append” import function for group fields will duplicate the information, as it will not remove the existing data first.
Tip 3: Reuse the exported file name
When exporting data (especially group field data), make sure you keep the file name for the upload file, as this name directs the system to the correct field prefix. This way, you don't have to add the prefix to each column within the file itself.
Tip 4: Remove the entity name column before uploading
Most often than not, the first two columns in your export file are going to be the entity ID and the corresponding entity name. The name column is useful when you want to quickly search for a client or use it as a v-lookup for data you need to integrate from another source.
However, when a client’s name and preferred name are different in Xplan – "Robert" versus "Bob," for example – reimporting the name will override both fields and set the preferred name to the same as the entity name. To overcome this, remove the entity name column right before uploading the import file and only keep the entity ID along with the columns that need to be updated.
Tip 5: Be mindful of using data exported from other systems
In some instances, formatting can inadvertently be included in a source file that you might not necessarily see when copying and pasting into the upload file. For example, some systems might have additional characters included for spacing and tabs, which will result in too many spaces being uploaded into a field in Xplan.
To combat this, you can copy and paste the data into Notepad first. You will be able to easily identify any formatting issues and remove these before adding the data to your import file.
Tip 6: Adding clients to adviser group(s)
When uploading an import file, you have the option to "add clients to advisers' groups." If selected, this will add the client entities to the advisers’ user group(s). This can be helpful when you're creating new clients in bulk for advisers, although it can sometimes create havoc if you're updating a selection of clients and their associated adviser is part of multiple groups.
If the clients are only in one of the adviser’s groups, selecting to apply this option will add the clients to all groups the adviser is a member of. So be aware of the implications when using this option and use it carefully.
These approaches should serve you in most scenarios, and which one you use will depend on what data you’re looking at. Take it one step at a time and don't get overwhelmed; try to update what you can in bulk within Xplan first (for those clients who need simple one-off field changes to the same option) before moving onto exports and imports/file uploads (and mostly for updating group field data or multiple fields in one go).
Finally, if you are ever in doubt, remember to try your approach on a test client first. Don’t forget the old saying, "Measure twice, cut once": double-check your import file, check it again and then check once more before you upload it.
If you have any scenarios you’re not sure of, make sure to reach out to your support channels – they may have solutions which you haven’t thought of. And if you have any other reporting tips and tricks, please let us know!
Iress Contributor
