Apprentice Hoots: Using Salesforce’s Data Import Wizard, Google Sheets and Salesforce reports to rollout a deep data cleanse making data accurate and consistent for great reporting

Ensuring data is as accurate as possible is extremely important for reporting purposes. A new customer who recently joined us had very messy data within their existing system. The challenges ranged from data which was put into text fields rather than fields specific to the data’s purpose, pick list values which were not selected, household contact data captured in the wrong contact and many other issues.  This made it impossible to report on with any degree of confidence, as the data was in the wrong place within the system, or simply missing. I have been working with this customer to build the structure of where the data will belong, and how all data within the system needs to be cleansed.

With lots of information missing within the system, a joint effort between Coacto and the customer was required to ensure the data was correct and could show real value. On top of this, instead of having the contact type on the contact record, this was stored in a campaign named after the contact type and all contacts that were of this type were campaign members. This happened because there was nowhere for this information to be stored in the system previously, so they had to improvise.

Creating a plan for the data cleanse

To plan for the customer’s data cleanse, we created an eight step process:

1. Confirm the customer is happy with the fields I have identified to cleanse / update within the system.
2. Due to the customer wanting to know each school the contact went to, they needed to provide us with a list of schools the contacts went to so we could put these into the system. These schools are a lookup field to an account record.
3. Extract the contacts from the system with the fields that need cleansing.
4. Extract contact type campaign and campaign members with their contact ID.
5. Merge the two data sets together and organise the data with instructions and validations to ensure when it is reviewed that it is filled in correctly.
6. Manually add any contacts that are not in the system and fill in any blank fields.
7. Cleanse the data for any duplications or incorrect data.
8. Import contacts with the updated data fields and relevant school back into the system.

Here is a visual representation of what the plan looked like: 

Please note: The customer’s name has been blanked out for confidentiality purposes.

Extracting existing data from the Salesforce system

Once we had a clear scope of the data that needed to be imported, I began to create two reports, one with the contacts and all the fields that needed to be cleansed i.e. the type field on the contact; the other with the type campaigns and the campaign members. On both of these reports I made sure to include the contact ID to map each record back to the correct record. I then exported both of these reports into corresponding spreadsheets.

Merging of documents

Using Google sheets vlookup formula to map the correct records together, I merged the data sets into one data set. I then merged the “type” field on the contact form with the “campaign name” field so all details were in one field. I could then organise the data with instructions and validations to ensure it is filled in correctly.

Cleansing the data

Now the data was out of the system and in an organised spreadsheet where all data was visible at once, this made it much easier for our customer to go through filling out all the blank fields and correcting any incorrect data. Although this was still a time consuming process, it would have taken much longer to click into each record and do this. As I do not know this unknown data unfortunately this is something I could not help with either. This was the stage where the customer also provided us with the list of the schools.

Importing the data back into Salesforce

Once the customer confirmed they are happy the data in the spreadsheet is correct, I imported the records into the system. Firstly, I needed to import the school’s data into the system. I used the Data Import Wizard’s new record import functionality to import all the schools into the accounts object. Next, I  imported the contacts. Since the contacts are already in the system, this required the Data Import Wizard’s update records functionality. I matched the contact by the Contact ID in case there were two contacts with the same name. I also matched the Current school / education provider lookup field to the account name to link the contact current school to find the school record within accounts.

Now this customer has a system with consistent, accurate data that they can report on and use with confidence, knowing that it is complete and up to date. We have added validation rules, required fields and popup reminders to ensure the data remains as accurate as possible.

If you think your system and data could benefit from a data cleanse, re-structure  or requires some rules to ensure data quality , please contact us here.

Owl see you later… 

Charlotte