Data Quality check within a Data Extension from an interactive Form

Question

Hello everybody.

Full disclosure here: I am new to Marketing Cloud and this would be my first Journey that I've set-up, so I am going through a learning curve.

We are planning to send an interactive form to prospective Leads that have incomplete data (missing name, partial names, placeholder names, etc) via a Journey.

I am populating the source DE used in the journey via a SQL activity in Automation Studio.

I have created a target DE to receive the form submission data that also contain a few extra fields (i.e. *LeadId, FirstName, LastName, Email, StreetAddress, City, State, PostalCode, *WasUpdated, *DateUpdated). * = hidden fields.

I would like to trigger some validation to occur after each row is added to the DE. the validation would check the length of the supplied values LENGTH(FirstName) > 1 & LENGTH(LastName) > 1.

However, after adding a decision split it doesn't look like you can compare LENGTH of a string, only Contains, Equal, Null, Not Null, etc.

What is the best method to perform some basic data quality prior to an update of the respective Lead record in Sales/Service Cloud?

IF LEN(FirstName) > 1 UPDATE

ELSE do not update

IF LEN(LastName) > 1 UPDATE

ELSE do not update

Seems to be update all fields or no fields in the Lead activity within the Journey.

If the data that was provided is equal or worse than when we started, we probably don't want to update (and overwrite) what we already had.

What is the best method to do these checks? AMPscript? SQL Activty with an update on the DE to set boolean flags and decision based on these flags?

Mateusz Dąbrowski · Accepted Answer

As you want to use Interactive Email, you won't be able to leverage the best method - real-time validation at the submission and asking the user to fix the issues.

That leaves you with two (similar) options:

1. Automation Studio with Query Activity to do the validation checks and move only validated records from your target DE to a staging DE that will be then used by Script Activity to push valid data to Salesforce using AMPScript in SSJS trick.

2. Go straight to Script Activity in your Automation and validate using only SSJS.

The second option is less performant but can be more flexible in terms of validation rules (full regex, possibility to leverage 3rd party data for comparison using APIs, etc.). For small volumes, it might be better.

Mateusz Dąbrowski · Answer

Nah, thankfully, I'm not alone;  @Mathes Kanagarajan https://trailblazers.salesforce.com/profileView?u=0053A00000EKN0vQAH  and few others do a fantastic job here as well :)   As you said, Journey Builder is not suited for data cleansing/analysis. It has limited filtering/validation features, so unless Decision Splits works for the use case and hand (and you are not planning on developer Custom Journey Activity), you should consider another place to do it.    Apart from on-submit form validation that can be done with JavaScript on the Cloud Page or SSJS/AMPScript on a  Code Resource Backend , the Automation Studio is the tool to use.    Suppose you are dead set on the no-script approach. In that case, you can do it with SQL in Automation Studio that brings validated data to a Data Extensions that feeds into dedicated Journey Builder with Lead - or better Object - Activity. It will be less efficient but will allow you to not work with SSJS/AMPScript.    I would, however, recommend the AMPScript in SSJS approach within Script Activity, as it is a concise script that lets you have one asset to do the whole job within a single run.

Data Quality check within a Data Extension from an interactive Form

Journey Builder

Formation associée

Aide supplémentaire