Data Cleaning | Client data cleaning

Jayde Wood

Modified on: Fri, 31 May, 2024 at 4:12 PM

The best way to clean your client data is outlined here.

We recommend that you complete the below actions in the order specified, however, you do not need to do this all in one sitting. The cleaning can be done progressively.

#	Data cleaning action	How to
1	Review active and inactive clients	In Netsuite, export search [Data Clean Up] Clients - active and inactive Review and update the inactive column. Do not delete columns, however, you can remove rows if you wish. Send the completed file to netsuite@poolwerx.com.au The Evolution Team will upload the file and confirm when this has been completed.
2	Review client duplicates	In Netsuite, if you are using the classic view, navigate to lists > Mass update > Entity Duplicate Resolution. Use view PWX \| Client Duplicates view. This is intended to only be used as the view on the duplicate page - you cannot use this as a search to detect duplicates. Merge clients that are genuine duplicates, or indicate ones that are not duplicates. This process of merging clients is irreversible, so please take caution when submitting your choices. Below is what your duplicate page should look like. This Entity Duplicate Resolution page returns potential client duplicates based on: Company name First Name Last Name Email Records must have matching information in all of these fields to be returned as possible duplicates.
3	Prepare data for cleaning	The Evolution Team has prepared an “intelligent” excel spreadsheet that will assist with actions 4-6 below. The spreadsheet helps by: Adjusting first and last names to meet the data best practice format e.g removing symbols and unnecessary spaces Identifies phone numbers that do not comply with a standard 10-number format Identifies missing or incomplete data based on some of the best practice formats, for example, dummy, invalid characters, and spaces included in email addresses or incomplete fields However, can't account for examples such as the street name not being complete Ability to enter or adjust incomplete or missing data in one area for bulk upload. If you would like to take advantage of this opportunity please: Email netsuite@poolwerx.com.au asking for your data to be prepared for cleaning The Evo Team will prepare your data and spreadsheets The spreadsheets will be returned to you for cleaning Update and clean your data and return the complete spreadsheets to netsuite@poolwerx.com.au The Evo Team will bulk upload your cleaned data and confirm once completed.
4	Review clients with only one identifier This means capturing at least one additional identifier, on top of their name (i.e. email, phone, address).	In Netsuite, run search [Data Clean Up] Clients with only one identifier Capture at least one additional identifier, on top of their name The most important additional identifier is the phone. The second would be an email. Although, we do recommend asking for phone and email.
5	Review incomplete client addresses	In Netsuite, run search [Data Clean Up] Clients address not complete Update missing data, by: Editing the records individually OR exporting the search for bulk upload OR ask the Evolution Team to prepare your data (refer to PSC assistance below If you would like to export the search: Export the search [Data Clean Up] Clients address not complete Review and update missing or incorrect data. Do not delete columns, however, you can remove rows if you wish. Send the complete file to netsuite@poolwerx.com.au The Evolution Team will upload the file and confirm when this has been completed. Notes: Pooltrackr has confirmed that the tools will be able to pick up any of the incorrect entries in the system and match the data to other data even if they are not exactly the same. For example, if NetSuite has Cres and Vital has Crescent, it will be able to match these.
6	Confirm client data for correctness	Every time you interact with a client, either in-store or otherwise, we recommend confirming the data you have for them is correct. E.g no dummy email addresses or phone numbers

The below guides might be useful during this process, particularly while entering new data.

Please note: You do not need to correct your current data to meet these formats, this is for data entered moving forward.

Client cleaning FAQ's

Question	Answer
What are the matching rules between NetSuite and Vital client records	For matching to be successful, the tool requires two identifiers to match and merge the records. For example, client records in NetSuite will need the below identifiers to match with a Vital contact to merge: First name or Last name or Company Name and Email OR phone OR address NetSuite client data will always be prioritized over Vital.
What clients will be imported into the new systems?	Clients will only be imported into the new systems if contains: The client is active (NetSuite only); AND First name or last name / Company name; AND Has an additional identifier: Email OR Phone OR Address. OR The client is active (NetSuite only); AND First name or last name / Company Name / or email or phone or address; AND is linked to an active asset (all vital assets are active) OR The client is active (NetSuite only); AND First name or last name / Company Name / or email or phone or address; AND has transactional history
My client in NetSuite has two identifiers matching with a vital record, however, the addresses are different, what happens?	In such cases, the partial match is identified and assessed by a data specialist. However, if the third identifier is completely different and not just a data entry error, the records will not be matched. This ensures that only accurate and relevant data is migrated and merged between systems, helping to maintain data integrity and reduce errors.
My client in NetSuite has special characters and vital doesn’t. What happens here? E.g Jane and John smith vs Jane & John Smith	If there are additional matching identifiers such as phone or email, the records will still be matched and merged, even if there are differences in the use of special characters in names or addresses.
Does the process handle all sorts of different phone numbers e.g with spaces, without spaces, area codes etc?	Yes, the process can handle all types of phone numbers, regardless of their format, including those with spaces, without spaces, or with area codes, and can match them accordingly.
What happens to the information and transactions when client records are merged?	When merging client records, the master client record will receive all of the client's information, including relationships, communication, addresses, sales, marketing, financial data, and other subtab lists. The duplicate client record will be deleted during the merge process as it no longer holds any valuable information. NetSuite client data will always be prioritized over Vital data.
When client records are merged, will I lose any field data? E.g emails, addresses	When merging client records, whether field data will be lost depends on the status of the field on the master client record. If the field on the master record is blank, and data from the duplicate record fills it, then the data is added to the master client record. However, if the master field already has data, the field is not updated and the data from the duplicate record is not added.
Do I have to do the data cleaning in both NetSuite and Vital?	The primary focus for data cleaning is NetSuite, as it is the primary source of service and financial data. However, Vital contact records will also undergo data cleaning during their import to enable the water test component to go live. It is recommended that you check and correct any discrepancies, errors, or missing information in Vital client records, especially if you have an in-store client. This can help ensure the accuracy and completeness of the data. By performing data cleaning in both NetSuite and Vital, you can help ensure a successful migration of client data to the new system, with minimal errors and issues.
What will happen with inactive clients? Can I reactive inactive clients?	Inactive clients will not be included in the data deduplication and merge process. If you want to make inactive clients accessible in the new platform, you can activate them at any time. However, please note that if you reactivate a client after the migration has taken place, any associated data such as assets and service history will not be accessible in the new platform.
If the address information in Vital and NetSuite is slightly different (e.g. Vital has "Street" and NetSuite has "St"), will the records still be recognized as a match during the data migration process? Do I need to clean up the address information to ensure a match?	Yes, the data matching algorithm is designed to recognize slight variations in the address information and identify records as a match, even if the format or abbreviations used are slightly different. Therefore, you do not need to clean up or update the address information in Vital or NetSuite to ensure a match.
When entering address information for clients, which address field should I populate?	Refer to adding a valid address article in the Support Portal, which helps to understand the different formats of addresses.
What happens if I have all my addresses in field 2 rather than 1 currently?	If you have all your addresses in field 2 rather than 1, it is recommended that you take advantage of the Evo Team preparing your data. This involves moving address field 2 to field 1, which can help ensure that your data is consistent and accurate. Please refer to step 3 or 5 above for more information.
If our client address isn’t complete/has missing field data, can we mass update them?	The Evo Team can assist you in identifying missing client address data through the data cleaning process. Once identified, the team can mass update the missing data for you. Please note that it is important to ensure that the updated information is accurate and complete to avoid any potential errors in the new platform. Please refer to steps 3 or 5 above for more information.
When is a client record considered a duplicate?	A client record is considered a duplicate if two or more of its identifiers are identical to another record. However, if there are other identifiers that are conflicting (e.g. due to a data entry error), a data specialist will assess the records to determine if they can be matched during the initial deduplication and merge process. If the conflicting identifiers are totally different, the records will not be matched during the initial process. It is important to note that there will also be an ongoing merge and deduplication process that occurs in the backend of the new system for client records. This process helps ensure that any duplicate records that may have been missed during the initial data migration process are identified and merged, helping to maintain data accuracy and prevent duplicates in the future.