Skip navigation

Clean All The Data

Posted by Gatormain Apr 29, 2019


We have a dirty data problem and quite a bit of data flows inbound (about 40k leads per month). As a fairly old organization with tons of homegrown systems, there has never been an effort to standardize and normalize across them. With it being difficult to allocate technical resources to work on updating at the source and cleaning up historical data in their systems, we have to evolve and use what we got, fix what we can, and move towards a path of cleaning data in our system. As we all know, it is hard to personalize and segment if there are disparities. You can't say 'Hi John' if the only name field in your database is their full name without actually splitting first and last from it, which we now do, along with many other updates.



To improve our data quality. This will first be known by lowering the amount of failed external calls (mostly from values not converted to what the CRM expects). Make it reusable.


Benchmark current state

Over a dozen failed external calls daily from Guid should contain 32 digits with 4 dashes (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx).



Create a program that everything has use. As we grow and find additional items to clean, we can add it to that program and all inbound contacts will be cleaned in the same way.


Currently, this is what it does:

  • Sets Lead Source Original and/or Lead Channel Original with the value in the Most Recent equivalent if the Original is null (Contact Washing Machine)

  • Populates multiple fields based on other fields for the CRM (Contact Washing Machine)

  • Convert all fields that require a GUID or other Value (Update Rules with Lookup Tables)

  • Double opt-in process - makes decisions based on current double opt-in status and country - sends to double opt in campaign if needed for marketing opt-in (Decisions, Picklist, Shared Filter, Contact Washing Machine, Campaign Canvas)

  • If they are in the USA, we use Zip Code to extract city and state - we created lookup tables that we refresh quarterly (Contact Washing Machine, Update Rules with Lookup Tables)

  • Extract First and Last Name from Full Name - almost every internal system asks for Full Name and not First/Last (Separate Program with Decisions, Contact Washing Machine)

  • If they are pre-defined as being a lead, we send them to the CRM program builder. This is primarily for high quality pages, like the ones with our Contact Us form, asking for a reach out.


This is what the canvas looks like:

Marketing Cloud Influence

I have used a lot of the courses in my day-to-day, depending on what I'm assisting with. For this implementation, Profile & Target was probably the most crucial. Multiple others have provided a positive impact for this and other initiatives.



After implementing some of our cleaning activities, we now only get a handful of failed external calls week, which is easier to manage but we are still working towards reaching a point to where no external calls fail. Majority of the fails are "internal errors", which is not because of data issue. We are continuing to run business as usual, while implementing more and more nurture campaigns to move contacts to different parts of the sales funnel. But our number one priority this year is integrating the data we need to be more targeted and personal for our users. With integrations, each time we need to take a hard look at the data mappings and work towards having our data consistent and correct.

Filter Blog

By date: By tag: